MS Sql 70-461: Chapter 14
Les 1
Query optimization problems and the query optimizer
Op eenvoudige queries na, kan een query op vele manieren worden uitgevoerd. Het aantal manieren hangt van de complexiteit van de query.
Sql Server kan een join op verschillende manieren uitvoeren. Het kan dan gebruik maken van de volgende algoritmes
- Nested loops
- Merge
- Hash
- Bitmap Filtering Optimized Hash (ook wel Star Join optimization genoemd)
Onderstaande afbeelding geeft aan hoe Sql Server een query uitvoert:
Alle stappen op execution na, worden uitgevoerd door de relational engine. De relational engine is een intern component die op logisch niveau werkt. De daadwerkelijke uitvoering van een query wordt uitgevoerd door de execution engine van de relational engine samen met de storage engine. De storage engine voert de fysieke handelingen uit. In het kort:de Query Optimizer (QO) moet een transformatie doen van logische naar fysieke operatoren. De QO kan enkel physical operators gebruiken die de storage engine kan uitvoeren. Voorbeeld: een logical operator kan een join operator zijn, een fysieke operator kan een Merge Join operation zijn.
Het resltaat van de laatste fase is de gewenste result set. Daarnaast kan ook het eecution plan worden gecahed, zodat de QO bij een volgende keer niet alle fases opnieuw hoeft te doorlopen. Sql Server compileert de code naar binary code voordat deze wordt uitgevoerd, daarom worden gecompileerde plans gecached.
De Sql Server Query Optimizer is cost-based optimizer. Hij wijst een nummer (de cost) toe aan elk mogelijk plan. Een hogere cost betekent een complex plan en een complex plan betekent een langzame query. In theorie zou Sql Server eerst alle mogelijke plannen moeten samenstellen en vervolgens het snelste plan moeten gebruiken. De search space voor een query is de set van alle mogelijke execution plans. Omdat het aantal plans groeit met de complexiteit van een query is het onmogelijk om alle mogelijke plannen te genereren. De QO balanceert daarom tussen de kwaliteit van een plan en de tijd die nodig is voor de optimalisatie. Om deze reden kan de QO niet altijd het allerbeste plan garanderen.
Sql Server berekent de cost van een operatie door het agoritme te bepalen die gebruikt wordt door een fysieke operator en door te schatten hoeveel rijen verwerkt moeten worden. Het schatten van het aantal rijen wordt ook wel cardinality estimation genoemd. De cost geeft de fysieke resources aan zoals aantal disk I/O, CPU tijd en geheugen dat nodig is voor het uitvoeren van de query.
Voor het schatten van het aantal rijen maakt de QO gebruik van optimizer statistics. Sql Server houdt statistieken bij over het aantal rijen en over de verdeling van het aantal rijen over key values van een index voor elke index.
Als een plan wordt gecached, wordt deze geparameteriseerd opgeslagen (= execution context). Dit betekent dat een plan herbuikt kan worden en aangeroepen kan worden met verschillende parameters.
Plannen die in de cache staan kunnen ook obsolete worden als de metadata veranderd in de database. Bijvoorbeeld: als een index wordt toegevoegd op een tabel of als een constraint wordt aangepast.
Soms moet de QO de cardinality estimation raden omdat hij dit niet kan 100% kan bepalen op basis van de parameters. Dit probleem wordt parameter sniffing genoemd.
Mogelijke scenario's waardoor een verkeerd plan wordt gekozen door de QO:
- De search space van de execution plan was te groot
- Statistische informatie ontbreekt of is niet up to date.
- Een cached plan is niet optimaal voor de huidige parameter value
- Parameter sniffing leidt tot onnauwkeurige cardinality estimation
- De QO onder- of overschat de cost van een algoritme die is geimplementeerd in een fysieke operator.
- Als de hardware wordt veranderd.
Sql Server extended events, Sql trace en Sql Server profiler
Sql Server Extended Events is een lighweigt performance monitoring systeem. Hiermee kan data van Sql Server gekoppeld worden met data van het Operating System en applicaties.
Sql Server audit die een DBA biedt met lichtgewicht auditing is ook gebaseerd op Extended Events.
Sql Trace is een intern Sql Server mechanisme voor het verkrijgen van events. Sql trace is depricated in toekomstige versie van Sql Server. Traces kunnen gemaakt worden met een set van Sql Server systm SPs. Traces kunnen handmatig worden aangemaakt of door Sql Server Profiler UI.
Sql Server Profiler is een applicatie welke dient als een UI voor Sql trace. Nadelen van het gebruik van de profiler zijn:
- De impact van de monitoring wordt verhoogd op de Sql Server instance
- Als de profiler gebruikt wordt over het netwerk dan moeten alle events over het netwerk verstuurd worden, wat andere netwerk operaties vertragen
- De events worden in een grid getoond, wat veel resources kan opnemen als veel events worden opgevangen
Omdat de profiler en Sql Trace depricated zijn en in de toekomst niet meer kunnen worden gebruikt, dient gebruik gemaakt te worden van Extended Events.
Les 2
SET Session options
Sql Server slaat data op in pages. Een page is een fysieke eenheid (unit) op de schijf binnenin de Sql Server database. De grootte van een page vastgesteld op 8,192 bytes, of 8kb. Een page behoort tot één object, zoals een tabel index of indexed view. Pages zijn verder gegroepeerd in in logische groepen van van 8 groepen. Dit worden extents genoemd. Een extent is mixed als de pages van deze extent bij verschillende objecten horen. Een extent is uniform als alle pages van de extent horen bij hetzelfde object.
Het doel bij het optimaliseren van een query om disk I/O te verlagen. Dit betekent dat geprobeerd moet worden om Sql Server zo min mogelijk pages te laten lezen.
Het aantal pages dat per tabel wordt benaderd door queries kan worden bekeken door statistics op ON te zetten. Dit kan op session level met het SET STATISTICS IO command:
DBCC DROPCLEANBUFFERS;
SET STATISTICS IO ON;
SELECT * FROM Sales.Customers;
SELECT * FROM Sales.Orders;
Met DBCC DROPCLEANBUFFERS wordt de data van de cache verwijderd. Sql Server cached data naast queries en execution plans.
De betekenis van de STATISTICS command is als volgt:
- Scan count
- Het aantal index of tabel scans die zijn uitgevoerd
- Logical reads
- Het aantal pages die zijn gelezen van de data cache. Als een volledige tabel wordt gelezen geeft dit getal een schatting van de tabel grootte.
- Physical reads
- Het aantal pages dat is gelezen vanaf de disk. Dit aantal is lager dan het actuele aantal pages omdat veel pages worden gecached.
- Read-ahead reads
- Het aantal pages dat Sql Server vooruit leest
- Lob logical reads
- Het aantal large object (LOB) pages dat is gelezen van de data cache. LOBs zijn kolommen met type (N)VARCHAR(MAX), VARBINARY(MAX), TEXT, NTEXT, IMAGE, XML, of grote CLR types, incl CLR spatial types GEOMETRY en GEOGRAPHY.
- Lob physical reads
- Het aantal large object-type pages die zijn gelezen van disk
- Lob read-ahead reads
- Het aantal large object pages die door Sql Server vooruit zijn gelezen.
Execution plans
De meest uitgebreide informatie over hoe een query wordt uitgevoerd kan verkregen worden door het execution plan te analyseren. Sql Server kent estimated en actual plans. Bij een estimated plan wordt de queryniet uitgevoerd. Beide plannen kunnen verkregen worden in tekst, xml of grafisch. SSMS presenteert de XML op een grafische wijze. Tekst presentaties zijn depricated en worden verwijderd in toekomstige versies.
Commando's voor plans zijn
- Voor tekst
- SET SHOWPLAN_TEXT en SET SHOWPLAN_ALL voor estimated plans voor tekst
- SET STATISTICS PROFILE voor actuel plans
- XML plans
- SET SHOWPLAN_XML voor estiamted plans
- SET STATISTICS XML voor actual plans
De grafische weergave kan in de UI van SSMS worden ingesteld voor zowel estiamted als actual plans.
Een gafisch execution plan wordt gelezen van boven naar beneden en van rechts naar links. De dikte van de pijlen corresponderen met het aantal rijen die worden doorgegeven van operator naar operator.
Les 3
Sql Server moniteert zichzelf constant en verzameld informatie die nuttig is voor het monitoren van de health van een instance, vindt problemen zoals ontbrekende indexes en optimaliseert queries. Deze informatie kan benaderd worden d.m.v. dynamic management objects (DMOs). Deze objecten bevatten dynamic management views en dynamic management functions. Alle DMOs zitten in de sys system schema en beginnen met dm_. Sommige DMOs bevatten informatie over de huidige staat van de instance en anderen bevatten informatie cumulatief vanaf de start van een instance.
Introduction to Dynamic Management Objects
Bij het troubleshooten van performance zijn DMOs erg handig. Veel data die nodig is, is reeds verzameld. Deze bruikbare informatie kan met normale queries worden opgevraagd uit de DMOs. DMOs zijn in geen enkele database gematerializeerd; het zijn virtuele objecten die toegang geven tot de data die Sql Server verzamelt in het geheugen.
het belangrijkste nadeel van een DMO is als een Sql Server onlangs is herstart. Dan is de cumulatieve informatie niet meer bruikbaar.
The most important DMOs for query tuning
DMOs zijn gegroepeerd in vele categorieen. Voor het analyseren van query performance zijn de meest bruikbare groepen:
- Sql Server Operating System (SQLOS)-related DMOs: De SQLOS beheert OS resources die specifiek voor Sql Server zijn
- Execution-related DMOs: Deze DMOs bieden inzicht in queries die zijn uitgevoerd, inclusief de query tekst, execution plan, aantal keren uitgevoerd en meer
- Index-related DMOs: Deze DMOs bieden informatie over het gebruik van indexes en ontbrekende indexen.
Met de 'sys.dm_os_sys_info' SQLOS-related dynamic management view kan basis informatie worden opgehaald over de instance. Voorbeeld:
SELECT cpu_count AS logical_cpu_count,
cpu_count / hyperthread_ratio AS physical_cpu_count,
CAST(physical_memory_kb / 1024. AS int) AS physical_memory__mb,
sqlserver_start_time
FROM sys.dm_os_sys_info;
Met de kolom 'sqlserver_start_time' kan bepaald worden of het zin heeft om de cumulatieve informatie te analyseren omdat deze kolom de tijd aangeeft wanneer Sql Server is gestart.
De DMO 'sys.dm_os_waiting_tasks' geeft informatie over de sessies die nu aan het wachten zijn op iets. Het kan bijv. voorkomen dat de sessie geblocked wordt door een andere sessie vanwege locking. Deze DMO zou vervolgens gejoined kunnen worden met 'sys.dm_exec_sessions' om informatie op te halen over de user, host en applicatie die aan het wachten zijn. Met de 'is_user_process' flag van de 'sys.dm_exec_session' DMO kunnen system sessions weggefilterd worden:
SELECT S.login_name, S.host_name, S.program_name,
WT.session_id, WT.wait_duration_ms, WT.wait_type,
WT.blocking_session_id, WT.resource_description
FROM sys.dm_os_waiting_tasks AS WT
INNER JOIN sys.dm_exec_sessions AS S
ON WT.session_id = S.session_id
WHERE s.is_user_process = 1;
De DMO 'sys.dm_exec_session' geeft informatie over de huidige requests die worden uitgevoerd. Het bevat een kolom 'sql_handle', welke een hash map is van de T-Sql batch tekst die wordt uitgevoerd. Deze handle kan gebruikt worden om de complete tekst van de batch op te halen m.b.v. de execution-related 'sys.dm_exec_sql_text' dynamic management function die deze handle als parameter accepteert.
De volgende query toont informatie over de huidige requests, de waits, de tekst van de Sql, de user, host en applicatie info:
SELECT S.login_name, S.host_name, S.program_name,
R.command, T.text,
R.wait_type, R.wait_time, R.blocking_session_id
FROM sys.dm_exec_requests AS R
INNER JOIN sys.dm_exec_sessions AS S
ON R.session_id = S.session_id
OUTER APPLY sys.dm_exec_sql_text(R.sql_handle) AS T
WHERE S.is_user_process = 1;
Veel informatie over uitgevoerde queries kan worden opgehaald uit de 'sys.dm_exec_query_stats' DMO. Er kan informatie opgehaald worden over disk I/O per query, CPU gebruik per query, gebruikte tijd per query en meer. Met 'sys.dm_exec_sql_text' kan ook de tekst van de query worden opgehaald.
Onderstaande query toont 5 queries die de meeste logical disk I/O gebruikte samen met de query tekst:
SELECT TOP (5)
(total_logical_reads + total_logical_writes) AS total_logical_IO,
execution_count,
(total_logical_reads/execution_count) AS avg_logical_reads,
(total_logical_writes/execution_count) AS avg_logical_writes,
(SELECT SUBSTRING(text, statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX),text)) * 2
ELSE statement_end_offset
END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY (total_logical_reads + total_logical_writes) DESC;
Met behulp van onderstaande DMOs kunnen ontbrekende indexen gevonden worden:
- sys.dm_db_missing_index_details,
- sys.dm_db_missing_index_columns,
- sys.dm_db_missing_index_groups,
- sys.dm_db_missing_index_group_stats
Reacties
Een reactie posten