Post date: Nov 4, 2011 9:58:34 AM
-- Visible online = schemaläggarna-- hidden online = monitorer-- när transaction startar så sparas den gamla versionen i tempdb så att andra kan läsa den gamla versionen medans transactionen håller på. Om transactionen ångras så tas kopian tillbaka-- vid buld load så loggas endast att vi tar plats på disk, inte för varje inserts som görs.-- DBCC - database controle console - amvänds för att kolla konsistensen på databasen. Innehåller kommandon som gör att vi kan titta på tex filemanagern, flasha cachen m.m-- förändringar ligger kvar i minnet som dirty pages. commitade saker hamnar i transactionsloggen. Vid checkpoints så skrivs dirty pages ut till disk. Vid stor minneslast kan dirty pages skrivas ut på disk trots att de inte är commitade.-- sys.dm_exec_requests visar alla saker som körs just nu-- när det kommer en request så skapas en task. tasken körs av en worker som schemaläggs av en schemaläggare.-- Allt minne hämtas ur en memory node. -- ad-hoc queries cachas numera, inte pbara stored proceuderes. adhoc queries kan även parameteriseras autmatiskt för att kunna återanvända query plans.-- Läs artikel av Tibo Karachi om skillnaden mellan stored procedures och ad-hoc queries.-- Ad hoc queries bör skrivas på ett speciellt sätt för att kunna utnytja query plan cache.-- memory clerks hålle reda på de olika minnetilldelningen för de olika komponenterna.-- single page allocation är sker som är tagna från buffer pool 8k i taget-- milti page allocation är saker som är tagna från buffer pool mera minne i taget-- performace counters innehåller information om olika performanc counters, tx hur länge en sida beräknas ligga kavr i minnet.-- man kan justera förhållandet hur mycket minne som tillåts sparas i plan cache jämfört med hur mycket som finns kvar till data cache-- Återkommer ???-- visar massa information om hur minnet användsdbcc memorystatus -- resource guvernor kan gör så att vi styr minnet mellan grupper av "grupper". Man anger i % hur mycket minne och processor gruppen ska få. Sedan gör man en funktion som avgör vlke grupp man hamnar i. Tex kan en applkkation skicka med sitt namn så att den på så vis hamnar i en grupp.-- den hanterar inte dsk access. Man måste alltså ändå testa om resource governor löser ens problem om det stora beskymret är disk io.-- grupperna skapar man med IMPORTANCE som kan säga till schemaläggaren hur pass viktigt gruppen är och där med justera schemaläggningen.-- resource group = 'default' är den grupp där allt annat som inte är gruperat skall hamna.-- om man gör denna manuella konfiguration så kommer sql server inte att sno minne till annat, även om det inte används.-- man kan ange min och max för minne och processor.-- Funktionen där man specar vilken grupp som connection skall hamna in kan vara väldigt specifik så att tex någon superanvändare får tillgång till mer kräm i servern.-- Resource governor finns i vilka versioner av sql server 2008??? återkommer-- Exempelvis så gör man en grupp som används av rapportverktyget så att den inte tar upp hela serverns kapacitet när det finns viktiga transactioner som måste köras samtidigt.select APP_NAME()select SUSER_NAME()select SUSER_SNAME() -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- SQL SERVER METADATA-- de gamla metadatatabellerna är nu gömda, nu finns istället ett gäng vyer som man ska använda istället-- de nya kallas kompetability views. Fungerar inte exakt som de gjorde förut.select name from sysobjects -- connecta från commandoprompten--sqlcmd -s servernamn -e -a-- vyer som det står sys. framför kallas katalogviews-- sys.objects innehåller alla stored procedues, tabeller, primar keys, funktioner, defaults.-- sys.allocation_units är alla pages som har nån struktur. man kan se vilket object som använder -- sys.partitions, här knyter vi ihop ett partition id med ett object id. Man kan här se var ett object ligger på disk.select * from sys.partitions -- sys.databases, information om databaserna. Kompabilitet anger om man anser kör en gammal databas på en nyare server. Observer att det kanske ändå inte går att köra en gammal databas på en ny server beroende på syntax med mera-- sys.exec_query_stats - statistik om vilka frågor som körts och statistik kring dessa. Innehåller sql_handle som är en pekare till en execution plan. Vi kan alltså kontrollera här vilka frågor som gått långsamt och sedan undersöka vilken plan det varselect * from sys.dm_exec_query_stats -- innehåller alla cachade planerselect * from sys.dm_exec_cached_plans select * from sys.syscomments -- hämtar objectdefinitionen för ett objectselect OBJECT_DEFINITION(object_id('sys.objects'))-- visar vilka request som körs just nu. här kan man tex se vilka requests som blockar en annan request. se även sp_who2.select * from sys.dm_exec_requests -- hitta information om alla index i en databas, och information om dessa index, fragmentering etc.select * from sys.dm_db_index_physical_stats(1, null, null, null, null)-- här kan vi se hur våra index används.select * from sys.dm_db_index_operational_stats(1, null, null, null)select object_definition(object_id('sys.dm_exec_request'))-- DMV dynamic management objects, är oftas vyer men det finns även funktioner-- information schemas är en gammal ansistandard för att ha metadata om databasen.-- Anger hur databasen skall lämna metadata. Kan köras mot andra databashanterare som db2, oracle etc.select * from INFORMATION_SCHEMA.TABLESselect SERVERPROPERTY ('Servername')select DATABASEPROPERTY('master', 'Recovery')select GETDATE() -- visar 3.3333delars milisekunderselect SYSDATETIME() -- högre upplösningselect CURRENT_TIMESTAMP -- returnerar en getdate()?select @@ERROR -- innehåller senaste felet om ett sådant uppstått. Nu kan vi istället använda try catchselect @@IDENTITY -- senaste identity insert som jag har gjortselect scope_identity() -- senaste identity som jag har gjort i detta scopeselect IDENT_CURRENT('tabell') -- senaste utgivna identity som ligger i tabellen-- Identities nollställs inte vid transactioner eller vid borttagning av rader.-- gamla systemstoredprocedures. Skriv aldrig egna som heter SP_ sp_who2 sp_lock -- information om låsselect * from sys.dm_tran_locks -- ersätter sp_lock-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- ---- EXTENDED EVENTS-- låter oss titta in i sql servers interna motorer. Där finns massa monitorering.-- med extended events kan vi ta del av denna monitoreringselect * from sys.dm_xe_packages -- innehåller alla paket för extended events.select * from sys.dm_xe_objects -- innehåller alla object som vi kan monitorera. dessa object kan emitera events.select * from sys.dm_xe_object_columns where object_name = 'page_split' -- visar vilka kolumner i XMLen som finns för objectet page_split.-- eftersom det finns så himla många olika object som kan kasta ur sig events. Det var för många för att skapa scheman för varje object och deras events så man valde att skicka ut xml istället.select * from sys.dm_xe_objects select * from sys.dm_xe_map_values where name = 'wait_types' -- alla wait typesselect * from sys.dm_xe_objects where object_type = 'target'-- med hjälp av denna information kan vi göra extended events som kan spara information om tex 'statement_complete'-- detta sätt att kolla vad servern gör är mindre störende för servern än när man kör Trace.-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- ---- Database architecture-- Filer fysiska filer på disk .mdf(.ndf heter oftast den andra filen), logfiler .ldf-- Lägg data och logg på olika diskar-- Ett object läggs på en filegroup, en filegroup läggs på en datafil-- En filegroup kan läggas på många datafiler.-- man vill ha så mycket datafiler som det bara går förutsatt att man har många jäkla diskar.-- Hellre ganska små diskar än en jättestor.-- I den stora världen så kan man tänka sig att man lägger en datafil på ett eget raidset och därmed har massor med raidset.-- det finns alltid minst en filegruop, standardfilegruopen heter PRIMARY-- Filegroups-- med hjälp av filegroups kan man välja på vilken fil och därmed disk en tabell skall ligga-- Man lägger ett object på en viss filegroup som ligger på en eller flera datafiler-- används även för partitionering-- om man har en massive tabell så skulle man kunna lägga den på egen disk-- extents består utav 8 st Pages.-- varje Page är 8k, dvs varje extent är 64k-- object delar inte på sidor, men de kan finnas på samma extent(de kallas då mixed extent)-- Global allocation map GAM, shared .... SGAM.-- i början av varje fil så ligger en GAM och sedan en SGAM som tar upp en page. Där kan servern då se om det finns lediga plater i extentet.-- när sql server behöver en ny sida på disk så kollar den i free space och kolalr vilka extent det finns plats på.-- Allocation units, set av pages för en single partition av en tabell eller index.-- IAM - Index allocation map.select * from sys.database_files -- filer på disk, man kan se hur mycket databasen skall växa åt gången. Så vill man inte ha det man vill hellre speca direkt hur mycket disk den ska ta och ange i procent hur mycket den skall växa.-- det kostar att låta databasen växa automatiskt. Man vill hellre direkt speca hur mycket som databasen förväntas ta. Man slipper då mycket fragmenteringsproblem.-- Använd helst inte db-shrink etc. select * from sys.databases -- alla databaserdbcc TRACEON(3604) -- startar "trace" för att kunna köra nedanstående querydbcc PAGE(adventureworks, 1, 0, 3) -- databas, vilken sida, vilken slot på sidan, 3 = ge mig mycket information)-- detta exempel hämtade file headern-- PFSdbcc page(adventureworks, 1, 1, 3) -- -- GAMdbcc page(adventureworks, 1, 2, 3) -- säger vad som är allokerat eller inte-- SGAMdbcc page(adventureworks, 1, 3, 3) -- -- compare page för att kolla om saker är uppdaterade eller intedbcc page(adventureworks, 1, 6, 3) -- dbcc page(adventureworks, 1, 7, 3) -- --boot pagedbcc page(adventureworks, 1, 8, 3) -- dbcc ind(adventureworks, 'HumanResources.Department', 1 )-- hämtar indexinformation vart ett object ligger på en sida-- sidorna i ett index länkar till varandra via m_prevPage och m_nextPage-- visar var på disk som raderna ligger i tabellenselect sys.fn_physlocFormatter (%%physloc%%) as rid, * from Person.CountryRegion dbcc traceoff (3604)-- saker man kan hitta på disk --row data --Lob data --row overflow data -- data som sväller över 8k hamnar i eget lager. SQl server sparar då en pekar till där det överflödade datat ligger. -- Row overflow kan va en rejäl performance killer, men gör det möjligt att spara stora object i sql server. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- ---- FILESTREAM -- förut fick man spara stora object med Image-datatypen.-- med filestream kan man istället speca att man ska ha en special del av databasen som egentligen bara är disk.-- man säger då att detta är en filestreampartition.-- man kan då skriva filer på disk precis som en vanlig fil fast filen hamnar som en vanlig fil på disken.-- det blir som en katalog på disken. Man måste fortfarande skapa objected från databasen för att databasen skall kunna hålla ordning, fast överiga applikationer kan komma åt filen.-- vid restore så kommer även all data i filestream att tas med i backupen.exec sp_configure filestream_Access_level 2 -- 2 = man kan då skriva från sql server och direkt på disken-- när man specar sin tabell så specar man vilka kolumner skall hamna i filestream via nyckelorder FILESTREAM--create table bilder (picture varbinary(max) FILESTREAM null)-- man kan söka ut filnamnet via select Picture.Filename(), man får då ut UNCpath. Filnamnet har blivit en GUID-- Man kan få full text indexes på dessa filestreams så man får sökbara dokument i sin database.-- COMPRESSION-- anges på tabellnivå.-- man kan vinna prestanda genom att använda compression. Det kan göras på rad och/eller Page-nivå-- row compression skall man köra nsätan alltid.-- page compression funkar bäst när man har mycket läs och lite skriv.-- compression gör att mindre data behöver läsasskrivas från disk. man sparar då io men det kostar lite CPU. CPu är sällan flaskhalsen i en sevrver.-- man sparar mest på stora tabeller, man kan förlora om det handlar om en liten tabell som används väldigt ofta.alter table Person Rebuildwith(data_compression = page, online = on) -- online on = folk kan ställa fårgor till tabellen undertiden sp_spaceused 'Person' -- snyggt kommando för att ta fram hur mycket storlek på disk en tabell tar.-- tipps-- om man kör 100 inserts så kan det vara bra att göra det i en transaction för då kommer inte varje insert att kommitas var för sig.-- TABLE OCH INDEXSTRUKTURER-- heap, clustrat index, non clustered index.-- HEAP. Indexid för en heap är alltid nollselect * from sys.indexes-- iam page pekar på sidorna som ingår. man får där ut File ID, page ID, Slot ID. Detta kallas för Row Identifier RID.-- För heap är inget sorterat. Det finns endast ett läge då man vill ha en heap. Och det är när man vill skriva massor med data men man kommer mycket sällan att läsa ifrån det. Det går dock att alltid skriva sekvetiellt på ett klustrat index få kan man få nästan samma prestanda.-- KLUSTRAT INDEX-- man har en root page som pekar ned på intermediate levels som pekar på leaf levels. På leaf levels ligger själva datat.-- För att hitta alla personer mellan två värden i vår klustrade idex så blir performance väldigt bra då det endast behöver traversera genom sidorna (eftersom det är sorterat)-- Detta kallas för Range-- Seek används om vi vet ett värde och vi behöer leta upp det i klustrade indexet.-- Vilken kolumn ska man clustra på? Den kolumn som man använder för att joina väldigt mcket på. ELLER att man har en tabell där man söker mycket RANGE-sökningar så ska ma lägga indexet på den kolumnen.-- om man gör mest uppslag på foreign keys så skall man lägga clustret på det.-- per lägger ofta klustrade indexet på primary key som är en identity-- Skall ej vara en kolumn som ändras mycket, inte alls hellst. Stabila nycklar är en viktig faktor.---- ICKEKLUSTRADE INDEX-- index id > 1-- man har en root page som pekar ner på intermediate level som pekar på Datapages(Via RID) av heapen ELLER till nyckeln på det klustrade indexet.-- Med fördel så är det klustrade indexet nyckel ganska kort eftersom det dubliceras ute i de klustrade indexena.-- ex -- klustered på ID-- ickeklustered på name-- sök på name between s och t-- Den går in i det ickeklustrade indexet och samlar första som börjar på s och tar en range tills den har den sista som börjar på T.-- nu har vi ett gäng pekar som pekar på det klustrade indexet. för varje pekare så måste den nu göra lookups i det klustrade indexet för att hitta resten av datat.-- om man har indexstrukturen på detta sätt så blir det i detta fall kass prestanda pga alla lookups.-- om man enligt samma exempel skall göra en sökning på person som heter kvist. Då sker en lookup i det ickeklustrade indexet för att hitta Kvist, där pekar det vidare till det klustrade indexet för att hitta resten av datat.-- genom att använda INCLUDE så kan man få upp all data i det klustrade indexet som behövs. det kommer då gå åt mycket mer utrymme, men om frågan kräver det så kommer man spara massor med prestanda. Detta kan man göra på frågor som körs väldigt ofta.-- När ett index har all data som efterfrågas så kalls det COVERED QUERY.-- Nyckeln för det ickeklustrade indexet får endast vara 16 kolumner, eller 900 bytes.-- skillnaden med att köra include mot att lägga in dem i indexet är att dom endast hänger med i trädet, indexet är inte sorterat på de övriga kolumnerna och det går inte att använda indexet för att söka efter restrerande kolumner.-- Om man anger flera kolumner i ett index så måste man ställa frågorna så att den första kolumnen är med i vilkoret för frågan. Resten av kolumnerna i indexet är endast sökbara om man får träff på första kolumnen i indexet.-- OLTP -- helst väldigt få index-- OLAP -- helst väldigt många index.-- PARTITIONER-- innebär att man delar upp tabellen i flera filer. detta mha partition scheme.-- man kan skapa ett ickeklustrat index på samma partition scheme. Då kommer partitionsnyckeln in som en extra kolumn i indexet.---- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- DATABASE OPTIONSalter database adventureworks set recovery full-- get info sp_Dboption adventureworks select * from sys.databases -- tipps, använd inte sql-collations då de endast är till för compabilitet.-- isstandby -- använd på databasen som tar emot data vid mirroring eller logshipping.-- single_user -- när man sätter till detta mode så får alla jobba vidare tills dom stänger, men inga nya får komma in. WITH OPTION ??? så kan man slänga ut alla användare direkt. Single_user används när man behöver göra databasunderhåll-- read_only -- sätts vid olap. Ingen låshantering. Då får man bra läsprestanda, men ingen kan förändra datat.-- man kan sätta read only på partitioner så att historik_partitionen blir endast läsbar. Då behöver man inte ta backup varje dag på historiken då den ändå inte förändras.-- auto_update_Statistics_async on -- genom att sätta denna till on så skapas statestiken asyncront.-- oarameterization forced, då kommer plan cachen att hanteras så att alla frågor blir paramaeteriserade.-- recovery bulk load -- om man ska köra bulk insert måste man sätta på bulk load. Bör endast göras i single user mode i produktionsfönster annars påverkas alla andra tabeller med.-- om man har två databaser, staging och prod. Då kan man bulkladda till staging och sedan får man lite bättre prestanda när man läser från staging till prod än att läsa in direkt till prod från fil(eftersom man vill ha full recovery mode on)