· 8 years ago · Nov 24, 2017, 04:54 AM
1<#
2.Synopsis
3Perform a SQL Azure Performance Assessment
4.Description
5This script automates the process outlined here https://www.brentozar.com/archive/2014/04/collecting-detailed-performance-measurements-extended-events/ for people using Microsoft Azure SQL Databases
6
7Prerequisites
8SqlServer
9AzureRM.Storage
10AzureRM.SQL
11
12* Connect to a Azure storage account $StorageAccount
13* Check for the existence of $container
14* Create it if it doesn't exist
15* Prompt to delete the files if it does exist
16* Create a SAS Token for $Container
17* Connect to Database $SourceDatabaseName on $DatabaseServer
18* Create a Master Key in DB $SourceDatabaseName
19* Create a credential in DB $SourcedatabaseName using SASToken
20* Create a Extended Event Session writing to $container on $StorageAccount
21* Start the event session
22* After $DurationMins minutes Stop the event session
23* Check for the existince of the Azure Database $AnalysisDatabaseName
24* if $AnalysisDatabaseName doesn't exist create a new database on the same Azure SQL server as $SourceDatabaseName
25*Create a Master Key in DB $AnalysisDatabaseName
26* Create a credential in DB $AnalysisDatabaseName using SASToken
27* Create tables (xevents,waits and query_stats) in the DB $AnalysisDatabaseName
28* Retrieve the list of blobs in $Container on $StorageAccount, these are extended event traces
29* Build and execute SQL Statement to import the contents of the extended event traces into the xevents table in $AnalysisDatabaseName
30* Populate the waits and query_stats table based off the contents of the xevents table in $AnalysisDatabaseName
31* Run the analysis query on $AnalysisDatabaseName and store the result in a variable
32* Prompt to delete the database, if not scale it down
33* If $OutputFile is set, write the result to an excel spreadsheet at $Outputfile
34
35.Parameter Subscription
36The name of the Azure Subscription that $SourceDatabaseName and $DatabaseServer reside
37.Parameter DatabaseServer
38Azure SQL Database Server to connect to
39.Parameter SourceDatabaseName
40Azure SQL Database to gather data from for analysis
41.Parameter AnalysisDatabaseName
42Azure SQL Database to perform the analysis in, we want this to be different to $SourceDatabaseName so as to not impact performance
43.Parameter StorageAccount
44Azure Storage Account to write SQL Server Extended Events too
45.Parameter Container
46Azure Storage Account container on $StorageAccount to write SQL Serevr Extended Events
47.Parameter DurationMins
48Number of minutes to collect extended event data for
49.Parameter Perms
50Permissions for the SASToken, leave as default
51.Parameter OutputFile
52If present the results of the analysis will be output to an excel file with this path/name
53
54.Example
55.\Start-AzureSQLPerformanceAssessment.ps1 -Subscription CLS -DatabaseServer ikq20yxyde -SourceDatabaseName MEl0201-CLS-PROD -AnalysisDatabaseName MEL0201-Analysis -StorageAccount mel0201log -OutputFile c:\temp\MEL0201-CLS-PROD-Analysis.xlsx
56#>
57Param(
58 [parameter(Mandatory=$true)]
59 [string]$Subscription,
60 [parameter(Mandatory=$true)]
61 [string]$DatabaseServer,
62 [parameter(Mandatory=$true)]
63 [string]$SourceDatabaseName,
64 [parameter(Mandatory=$true)]
65 [string]$AnalysisDatabaseName,
66 [parameter(Mandatory=$true)]
67 [string]$StorageAccount,
68 [parameter(Mandatory=$false)]
69 [string]$Container="extended-events",
70 [parameter(Mandatory=$false)]
71 [Int]$DurationMins=5,
72 [parameter(Mandatory=$false)]
73 [string]$Perms="rwl",
74 [parameter(Mandatory=$False)]
75 $OutputFile
76
77)
78
79#Requires -version 5 -Modules Azure,AzureRM.Profile,AzureRM.Sql,AzureRM.Storage
80# Get-AzureRmSQLDatabase seems to be case sensitive.
81$SourceDatabaseName = $SourceDatabaseName.ToUpper()
82$AnalysisTier="P15"
83
84Write-Output "Checking Azure Accesss"
85 try {
86 Select-AzureSubscription -SubscriptionName $Subscription -ErrorAction Stop
87 } catch {
88 Add-AzureAccount
89 }
90
91 try {
92 Select-AzureRmSubscription -SubscriptionName $Subscription -ErrorAction Stop
93 } catch {
94 Add-AzureRmAccount -SubscriptionName $Subscription
95 }
96
97# Find Storage Account and Key
98Write-output "Locate storage account Key for $StorageAccount"
99$armStorage = Find-AzureRmResource -ResourceType "Microsoft.Storage/storageAccounts" -ResourceNameEquals $StorageAccount
100if ($armStorage -ne $null) {
101 try {
102 $StorageKey = (Get-AzureRmStorageAccountKey -StorageAccountName $StorageAccount -ResourceGroupName $armstorage.ResourceGroupName)[0].Value
103 } catch {
104 write-error "Unable to retrieve ARM storage key for $StorageAccount"
105 exit
106 }
107} else {
108 If ((Test-AzureName -Storage $StorageAccount)) {
109 try {
110 $StorageKey = (Get-AzureStorageKey -StorageAccountName $StorageAccount -ea Stop).Primary
111 } catch {
112 write-error "Unable to retrieve ASM Storage Key for $StorageAccount"
113 exit
114 }
115 } else {
116 Write-error "Unable to locate ASM storage account $StorageAccount"
117 }
118}
119If (!$StorageAccount -and !$StorageKey) {
120 write-error "No Storage account key set"
121 exit
122}
123Write-Output "Create Azure storage context for $storageAccount"
124try {
125 $StorageContext = New-AzureStorageContext -StorageAccountName $StorageAccount -StorageAccountKey $StorageKey
126} catch {
127 Write-Error "Unable to create storage context"
128}
129
130# Check Storage Container
131Write-Output "Check $storageaccount for container $Container"
132if (!(Get-AzureStorageContainer -Name $Container -Context $StorageContext)) {
133 try {
134 write-output "Container $container doesn't exist, creating.."
135 New-AzureStorageContainer -Name $Container -Context $StorageContext -Permission Off
136 Start-Sleep -Seconds 30
137 } catch {
138 Write-Error "Unable to create container $Container on storage account $StorageAccount"
139 exit
140 }
141} else {
142 # Storage Container already exists, we probably want to clean it up
143 $Blobs = Get-AzureStorageBlob -Container $Container -Context $StorageContext
144 if ($blobs.count -ge 0) {
145 $response = Read-Host -Prompt "There are $($Blobs.count) trace files in $container would you like to delete them? [y/n]"
146 If ($response -eq "y") {
147 Write-Output "Removing trace files from $container"
148 $Blobs | Remove-AzureStorageBlob
149 } else {
150 Write-output "You have opted to not remove trace files, this could prevent this process from succeeding"
151 }
152 }
153}
154
155
156
157# Check Database Server
158Write-Output "Check subscription for database server $DatabaseServer"
159try {
160 $armServer = Find-AzureRmResource -ResourceType "Microsoft.Sql/servers" -Name $DatabaseServer
161} catch {
162 write-error "Unable to locate Azure SQL Server $DatabaseServer"
163 exit
164}
165
166# Check Database
167Write-Output "Check subscription for database $SourceDatabaseName"
168try {
169 $SrcDB = Get-AzureRmSqlDatabase -ResourceGroupName $armserver.ResourceGroupName -DatabaseName $SourceDatabaseName -ServerName $DatabaseServer
170} catch {
171 write-error "Unable to locate Azure SQL Database $SourceDatabaseName on $DatabaseServer $($_.Exception.Message)"
172 exit
173}
174
175# Create a Sas Policy
176
177$StartTime = (Get-Date).ToUniversalTime()
178$ExpireTime = $StartTime.AddHours($DurationMins*2)
179
180$policySasToken = " ? "
181
182
183If ((Get-AzureStorageContainerStoredAccessPolicy -Container $Container -Context $StorageContext -Policy $policysastoken -ea SilentlyContinue)) {
184 Write-Output "SAS Policy $policysastoken Already Exists, removing..."
185 Remove-AzureStorageContainerStoredAccessPolicy -Container $Container -Context $StorageContext -Policy $policysasToken
186}
187Write-Output "Create SAS Policy $policysastoken"
188try {
189 $p = New-AzureStorageContainerStoredAccessPolicy -Container $Container -Context $StorageContext -Permission $Perms -StartTime $StartTime -ExpiryTime $ExpireTime -Policy $policySasToken
190} catch {
191 write-error "Unable to Create Container SAS Policy $)$_.Exception.Message)"
192 exit
193}
194$StorageContext = New-AzureStorageContext -StorageAccountName $StorageAccount -StorageAccountKey $StorageKey
195
196Write-Output "Create SASToken"
197$tokenName = "sas$storageAccount".tolower()
198try {
199 $SASToken = New-AzureStorageContainerSASToken -Name $Container -Context $StorageContext -Policy $policySasToken -Protocol HttpsOnly
200} catch {
201 write-Error "Unable to create Container SAS token on Storage Account $storageaccount container $Container"
202 exit
203}
204Write-output "SAS token: $SASTOKEN"
205Import-Module SQLServer,Janison.KeyVault
206
207
208$ServerFQDN = $DatabaseServer+".database.windows.net"
209# Create a connection string
210
211$SrcConnectionString = "Data Source={0};Initial Catalog={1};;Authentication=Active Directory Integrated" -f $ServerFQDN,$SourceDatabaseName
212
213$CreateKeyQuery = @"
214IF NOT EXISTS
215(SELECT * FROM sys.symmetric_keys
216 WHERE symmetric_key_id = 101)
217BEGIN
218CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$((New-Guid).Guid.Replace("-","#"))'
219END
220"@
221
222Try {
223 Write-Output "Adding Master Key to $SourceDatabaseName"
224 $result = Invoke-SqlCmd -Connectionstring $SrcConnectionString -Query $CreateKeyQuery
225} catch {
226 Write-error "Adding master key failed $($_.Exception.Message)"
227 exit
228}
229
230$CredentialName = "https://{0}.blob.core.windows.net/{1}" -f $StorageAccount,$Container
231$CreateCredQuery = @"
232IF EXISTS (SELECT * from sys.database_scoped_credentials where name='{0}')
233BEGIN
234 DROP DATABASE SCOPED CREDENTIAL [{0}]
235END
236CREATE DATABASE SCOPED CREDENTIAL [{0}] -- this name must match the container path, start with https and must not contain a trailing forward slash.
237WITH IDENTITY='SHARED ACCESS SIGNATURE' -- this is a mandatory string and do not change it.
238, SECRET = '{1}'
239"@ -f $CredentialName,$SASToken.Replace('?','')
240
241
242try {
243 Write-output "Adding Database Scoped credentails for $CredentialName to $SourceDatabaseName"
244 $result = Invoke-SqlCmd -Connectionstring $SrcConnectionString -Query $CreateCredQuery
245} catch {
246 write-error "An Error occured while trying to add Database Scoped Credential $($_.Exception.Message)"
247 exit
248}
249
250$CreateEventSessionSQL = @"
251-- Adapted From https://gist.github.com/peschkaj/05009bc84473041be714
252if exists(select name from sys.database_event_sessions where name='query_performance')
253BEGIN
254 DROP EVENT SESSION [query_performance] ON DATABASE
255END
256
257CREATE EVENT SESSION [query_performance] ON DATABASE
258ADD EVENT sqlos.wait_info(
259 ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text)
260 WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
261ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1),collect_statement=(1)
262 ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id)
263 WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
264ADD EVENT sqlserver.sql_statement_completed(
265 ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id)
266 WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0))))
267ADD TARGET package0.event_file(SET filename=N'{0}/query_performance.xel',max_file_size=(64),max_rollover_files=(5),metadatafile=N'{0}/query_performance.xem')
268WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
269"@ -f $CredentialName
270
271try {
272 Write-host "Adding Event Session query_performance"
273 $result = Invoke-SqlCmd -Connectionstring $SrcConnectionString -Query $CreateEventSessionSQL
274} catch {
275 write-error "An Error occured while trying to add event session $($_.Exception.Message)"
276 exit
277}
278
279$StartEventCollectionSQL = "ALTER EVENT SESSION query_performance ON DATABASE STATE = START"
280
281$StopEventCollectionSQL = "ALTER EVENT SESSION query_performance ON DATABASE STATE = STOP"
282
283
284try {
285 Write-Output "Starting Event Session"
286 $result = Invoke-SqlCmd -Connectionstring $SrcConnectionString -Query $StartEventCollectionSQL
287} catch {
288 Write-error "An error occured while starting the Event Session $($_.Exception.Message)"
289 exit
290}
291
292Start-Sleep -Seconds ($DurationMins*60)
293
294try {
295 Write-Output "Stopping Event Session"
296 $result = Invoke-SqlCmd -Connectionstring $SrcConnectionString -Query $StopEventCollectionSQL
297 Write-Output "Event session Stopped"
298} catch {
299 Write-error "An error occured while stopping the Event Session $($_.Exception.Message)"
300 exit
301}
302
303
304try {
305 Write-Output "Checking for Analysis Database"
306 $AnalysisDatabase = Get-AzureRmSqlDatabase -DatabaseName $AnalysisDatabaseName -ServerName $DatabaseServer -ResourceGroupName $armServer.ResourceGroupName
307} catch {
308 write-Output "$AnalysisDatabaseName does not exist and will be created"
309}
310
311if ($AnalysisDatabase -eq $null) {
312 Write-Output "Creating Analysis database $AnalysisDatabaseName on $DatabaseServer as $AnalysisTier"
313 try {
314 $AnalysisDatabase = New-AzureRmSqlDatabase -DatabaseName $AnalysisDatabaseName -ServerName $DatabaseServer -ResourceGroupName $armServer.ResourceGroupName -Edition Premium -RequestedServiceObjectiveName $AnalysisTier
315 } catch {
316 Write-Output "An error occured while creating analysis database $($_.Exception.message)"
317 exit
318 }
319} else {
320
321 If ($AnalysisDatabase.Edition -eq "Standard") {
322 $answer = Read-Host -prompt "Analysis database $AnalysisDatabaseName is running Standard Edition, would you like to scale to $AnalysisTier? Y/N"
323 if ($answer -in ("y","yes")) {
324 write-host "Initiating scale up of $AnalysisDatabaseName"
325 try {
326 Set-AzureRmSqlDatabase -DatabaseName $AnalysisDatabaseName -ServerName $DatabaseServer -ResourceGroupName $armServer.ResourceGroupName -RequestedServiceObjectiveName $AnalysisTier -Edition Premium
327 } catch {
328 $check = Read-Host "Unable to scale $AnalysisDatabaseName database to $AnalysisTier. Please look into this and Type READY when it is resolved"
329 If ($check -cne "READY") { exit }
330
331 }
332 }
333 }
334
335}
336
337$AnalysisConnectionString = "Data Source={0};Initial Catalog={1};Authentication=Active Directory Integrated;Connection Timeout=1800" -f $ServerFQDN,$AnalysisDatabaseName
338
339Try {
340 Write-Output "Adding Master Key to $AnalysisDatabaseName"
341 $result = Invoke-SqlCmd -Connectionstring $AnalysisConnectionString -Query $CreateKeyQuery
342} catch {
343 Write-error "Adding master key to $AnalysisDatabaseName failed $($_.Exception.Message)"
344 exit
345}
346
347try {
348 Write-output "Adding Database Scoped credentails for $CredentialName to $AnalysisDatabaseName"
349 $result = Invoke-SqlCmd -Connectionstring $AnalysisConnectionString -Query $CreateCredQuery
350} catch {
351 write-error "An Error occured while trying to add Database Scoped Credential $($_.Exception.Message)"
352 exit
353}
354
355
356$CreateProcessingTablesSQL = @"
357-- Adapted From https://gist.github.com/peschkaj/05009bc84473041be714
358IF OBJECT_ID('xevents') IS NULL
359CREATE TABLE xevents (
360 event_time DATETIME2,
361 event_type NVARCHAR(128),
362 query_hash decimal(38,0),
363 event_data XML
364);
365
366IF OBJECT_ID('waits') IS NULL
367CREATE TABLE waits (
368 event_time DATETIME2,
369 event_interval DATETIME2,
370 query_hash DECIMAL(38,0),
371 query_plan_hash DECIMAL(38,0),
372 session_id INT,
373 client_hostname NVARCHAR(MAX),
374 database_name NVARCHAR(MAX),
375 statement NVARCHAR(MAX),
376 wait_type NVARCHAR(MAX),
377 duration_ms INT,
378 signal_duration_ms INT
379);
380
381
382IF OBJECT_ID('query_stats') IS NULL
383CREATE TABLE query_stats (
384 event_time DATETIME2,
385 event_interval DATETIME2,
386 query_hash DECIMAL(38,0),
387 query_plan_hash DECIMAL(38,0),
388 session_id INT,
389 client_hostname NVARCHAR(MAX),
390 database_name NVARCHAR(MAX),
391 statement NVARCHAR(MAX),
392 duration_ms INT,
393 cpu_time_ms INT,
394 physical_reads INT,
395 logical_reads INT,
396 writes INT,
397 row_count INT
398);
399
400TRUNCATE TABLE xevents;
401TRUNCATE TABLE waits;
402TRUNCATE TABLE query_stats;
403
404"@
405try {
406 Write-output "Creating Processing tables in $AnalysisDatabaseName"
407 $result = Invoke-SqlCmd -Connectionstring $AnalysisConnectionString -Query $CreateProcessingTablesSQL
408} catch {
409 write-error "An Error occured while creating processing tables $($_.Exception.Message)"
410 exit
411}
412
413
414
415$ImportBlobSQl = @"
416-- Adapted From https://gist.github.com/peschkaj/05009bc84473041be714
417INSERT INTO xevents (event_time, event_type, query_hash, event_data)
418SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP), x.event_data.value('(event/@timestamp)[1]', 'datetime2')) AS event_time,
419 x.event_data.value('(/event/@name)[1]', 'nvarchar(max)'),
420 x.event_data.value('(event/action[@name="query_hash"])[1]', 'decimal(38,0)'),
421 x.event_data
422FROM sys.fn_xe_file_target_read_file ('{0}/{1}', null, null, null)
423 CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) as x ;
424
425"@
426
427#$Container = Get-AzureStorageContainer -Name $containerName -Context $sctx
428$blobs = Get-AzureStorageBlob -Container $Container -Context $StorageContext
429
430foreach ($blob in $blobs) {
431 $ImportSQL += $ImportBlobSQL -f $CredentialName,$blob.Name
432
433}
434try {
435 Write-output "Importing Data into in $AnalysisDatabaseName"
436 $result = Invoke-SqlCmd -Connectionstring $AnalysisConnectionString -Query $ImportSQL -QueryTimeout 1800
437} catch {
438 write-error "An Error occured while Importing Extended Event Data $($_.Exception.Message)"
439 exit
440}
441
442$ExtractWaitsAndQueryStatsSQL = @"
443-- From https://gist.github.com/peschkaj/05009bc84473041be714
444INSERT INTO waits
445SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP), x.event_data.value('(event/@timestamp)[1]', 'datetime2')) AS event_time,
446 DATEADD(MINUTE, DATEDIFF(MINUTE, 0, event_time), 0) AS event_interval,
447 x.event_data.value('(event/action[@name="query_hash"])[1]', 'decimal(38,0)') AS query_hash,
448 x.event_data.value('(event/action[@name="query_plan_hash"])[1]', 'decimal(38,0)') AS query_plan_hash,
449 x.event_data.value('(event/action[@name="session_id"])[1]', 'int') AS session_id,
450 x.event_data.value('(event/action[@name="client_hostname"])[1]', 'nvarchar(max)') AS client_hostname,
451 x.event_data.value('(event/action[@name="database_name"])[1]', 'nvarchar(max)') AS database_name,
452 x.event_data.value('(event/action[@name="sql_text"])[1]', 'nvarchar(max)') AS statement,
453 x.event_data.value('(event/data[@name="wait_type"]/text)[1]', 'nvarchar(max)') AS wait_type,
454 x.event_data.value('(event/data[@name="duration"])[1]', 'int') AS duration_ms,
455 x.event_data.value('(event/data[@name="signal_duration"])[1]', 'int') AS signal_duration_ms
456FROM xevents AS x
457WHERE x.query_hash > 0
458 AND x.event_type = 'wait_info'
459
460
461INSERT INTO query_stats
462SELECT
463 DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP), x.event_data.value('(event/@timestamp)[1]', 'datetime2')) AS event_time,
464 DATEADD(MINUTE, DATEDIFF(MINUTE, 0, event_time), 0) AS event_interval,
465 x.event_data.value('(event/action[@name="query_hash"])[1]', 'decimal(38,0)') AS query_hash,
466 x.event_data.value('(event/action[@name="query_plan_hash"])[1]', 'decimal(38,0)') AS query_plan_hash,
467 x.event_data.value('(event/action[@name="session_id"])[1]', 'int') AS session_id,
468 x.event_data.value('(event/action[@name="client_hostname"])[1]', 'nvarchar(max)') AS client_hostname,
469 x.event_data.value('(event/action[@name="database_name"])[1]', 'nvarchar(max)') AS database_name,
470 x.event_data.value('(event/data[@name="statement"])[1]', 'nvarchar(max)') AS statement,
471 x.event_data.value('(event/data[@name="duration"])[1]', 'int') AS duration_ms,
472 x.event_data.value('(event/data[@name="cpu_time"])[1]', 'int') AS cpu_time_ms,
473 x.event_data.value('(event/data[@name="physical_reads"])[1]', 'int') AS physical_reads,
474 x.event_data.value('(event/data[@name="logical_reads"])[1]', 'int') AS logical_reads,
475 x.event_data.value('(event/data[@name="writes"])[1]', 'int') AS writes,
476 x.event_data.value('(event/data[@name="row_count"])[1]', 'int') AS row_count
477FROM xevents AS x
478WHERE x.query_hash > 0
479 AND (x.event_type = 'sp_statement_completed' OR x.event_type = 'sql_statement_completed')
480"@
481
482try {
483 Write-output "Exracting waits and query stats into in tables in $AnalysisDatabaseName"
484 $result = Invoke-SqlCmd -Connectionstring $AnalysisConnectionString -Query $ExtractWaitsAndQueryStatsSQL -QueryTimeout 900
485} catch {
486 write-error "An Error occured while extracting waits and query stats $($_.Exception.Message)"
487 exit
488}
489
490$AnalysisQuerySQL = @"
491-- From https://gist.github.com/peschkaj/05009bc84473041be714
492WITH statement_ntile_cte AS (
493 SELECT DISTINCT
494 event_interval,
495 query_hash,
496 query_plan_hash,
497 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY duration_ms) OVER (PARTITION BY query_plan_hash, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, event_time), 0)) AS duration_50th,
498 PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY duration_ms) OVER (PARTITION BY query_plan_hash, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, event_time), 0)) AS duration_75th,
499 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY duration_ms) OVER (PARTITION BY query_plan_hash, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, event_time), 0)) AS duration_90th,
500 PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY duration_ms) OVER (PARTITION BY query_plan_hash, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, event_time), 0)) AS duration_95th,
501 PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY duration_ms) OVER (PARTITION BY query_plan_hash, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, event_time), 0)) AS duration_99th,
502
503 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY cpu_time_ms) OVER (PARTITION BY query_plan_hash, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, event_time), 0)) AS cpu_time_50th,
504 PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY cpu_time_ms) OVER (PARTITION BY query_plan_hash, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, event_time), 0)) AS cpu_time_75th,
505 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY cpu_time_ms) OVER (PARTITION BY query_plan_hash, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, event_time), 0)) AS cpu_time_90th,
506 PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY cpu_time_ms) OVER (PARTITION BY query_plan_hash, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, event_time), 0)) AS cpu_time_95th,
507 PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY cpu_time_ms) OVER (PARTITION BY query_plan_hash, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, event_time), 0)) AS cpu_time_99th,
508
509 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY physical_reads) OVER (PARTITION BY query_plan_hash, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, event_time), 0)) AS physical_reads_50th,
510 PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY physical_reads) OVER (PARTITION BY query_plan_hash, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, event_time), 0)) AS physical_reads_75th,
511 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY physical_reads) OVER (PARTITION BY query_plan_hash, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, event_time), 0)) AS physical_reads_90th,
512 PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY physical_reads) OVER (PARTITION BY query_plan_hash, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, event_time), 0)) AS physical_reads_95th,
513 PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY physical_reads) OVER (PARTITION BY query_plan_hash, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, event_time), 0)) AS physical_reads_99th,
514
515 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY logical_reads) OVER (PARTITION BY query_plan_hash, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, event_time), 0)) AS logical_reads_50th,
516 PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY logical_reads) OVER (PARTITION BY query_plan_hash, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, event_time), 0)) AS logical_reads_75th,
517 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY logical_reads) OVER (PARTITION BY query_plan_hash, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, event_time), 0)) AS logical_reads_90th,
518 PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY logical_reads) OVER (PARTITION BY query_plan_hash, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, event_time), 0)) AS logical_reads_95th,
519 PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY logical_reads) OVER (PARTITION BY query_plan_hash, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, event_time), 0)) AS logical_reads_99th,
520
521 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY writes) OVER (PARTITION BY query_plan_hash, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, event_time), 0)) AS writes_50th,
522 PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY writes) OVER (PARTITION BY query_plan_hash, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, event_time), 0)) AS writes_75th,
523 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY writes) OVER (PARTITION BY query_plan_hash, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, event_time), 0)) AS writes_90th,
524 PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY writes) OVER (PARTITION BY query_plan_hash, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, event_time), 0)) AS writes_95th,
525 PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY writes) OVER (PARTITION BY query_plan_hash, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, event_time), 0)) AS writes_99th
526 FROM query_stats
527),
528statement_analyzed_cte AS (
529 SELECT sc.query_hash,
530 sc.query_plan_hash,
531 event_interval,
532 sc.statement,
533 SUM(duration_ms) AS total_duration_ms,
534 AVG(duration_ms) AS average_duration_ms,
535 COALESCE(STDEV(duration_ms), 0) AS stdev_duration_ms,
536 MIN(duration_ms) AS min_duration_ms,
537 MAX(duration_ms) AS max_duration_ms,
538 SUM(cpu_time_ms) AS total_cpu_time_ms,
539 AVG(cpu_time_ms) AS average_cpu_time_ms,
540 COALESCE(STDEV(cpu_time_ms), 0) AS stdev_cpu_time_ms,
541 MIN(cpu_time_ms) AS min_cpu_time_ms,
542 MAX(cpu_time_ms) AS max_cpu_time_ms,
543 SUM(physical_reads) AS total_physical_reads,
544 AVG(physical_reads) AS average_physical_reads,
545 COALESCE(STDEV(physical_reads), 0) AS stdev_physical_reads,
546 MIN(physical_reads) AS min_physical_reads,
547 MAX(physical_reads) AS max_physical_reads,
548 SUM(logical_reads) AS total_logical_reads,
549 AVG(logical_reads) AS average_logical_reads,
550 COALESCE(STDEV(logical_reads), 0) AS stdev_logical_reads,
551 MIN(logical_reads) AS min_logical_reads,
552 MAX(logical_reads) AS max_logical_reads,
553 SUM(writes) AS total_writes,
554 AVG(writes) AS average_writes,
555 COALESCE(STDEV(writes), 0) AS stdev_writes,
556 MIN(writes) AS min_writes,
557 MAX(writes) AS max_writes
558 FROM query_stats sc
559 GROUP BY sc.query_hash, sc.query_plan_hash, sc.statement, event_interval
560),
561query_stats AS (
562SELECT sac.query_hash,
563 sac.query_plan_hash,
564 sac.statement,
565 sac.event_interval,
566
567 sac.total_duration_ms,
568 sac.average_duration_ms,
569 sac.stdev_duration_ms,
570 sac.min_duration_ms,
571 sac.max_duration_ms,
572 snc.duration_50th,
573 snc.duration_75th,
574 snc.duration_90th,
575 snc.duration_95th,
576 snc.duration_99th,
577
578 sac.total_cpu_time_ms,
579 sac.average_cpu_time_ms,
580 sac.stdev_cpu_time_ms,
581 sac.min_cpu_time_ms,
582 sac.max_cpu_time_ms,
583 snc.cpu_time_50th,
584 snc.cpu_time_75th,
585 snc.cpu_time_90th,
586 snc.cpu_time_95th,
587 snc.cpu_time_99th,
588
589 sac.total_physical_reads,
590 sac.average_physical_reads,
591 sac.stdev_physical_reads,
592 sac.min_physical_reads,
593 sac.max_physical_reads,
594 snc.physical_reads_50th,
595 snc.physical_reads_75th,
596 snc.physical_reads_90th,
597 snc.physical_reads_95th,
598 snc.physical_reads_99th,
599
600 sac.total_logical_reads,
601 sac.average_logical_reads,
602 sac.stdev_logical_reads,
603 sac.min_logical_reads,
604 sac.max_logical_reads,
605 snc.logical_reads_50th,
606 snc.logical_reads_75th,
607 snc.logical_reads_90th,
608 snc.logical_reads_95th,
609 snc.logical_reads_99th,
610
611 sac.total_writes,
612 sac.average_writes,
613 sac.stdev_writes,
614 sac.min_writes,
615 sac.max_writes,
616 snc.writes_50th,
617 snc.writes_75th,
618 snc.writes_90th,
619 snc.writes_95th,
620 snc.writes_99th
621FROM statement_analyzed_cte sac
622 JOIN statement_ntile_cte AS snc ON sac.query_plan_hash = snc.query_plan_hash
623 AND sac.event_interval = snc.event_interval
624)
625SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(qs.statement)), NCHAR(13), ' '), NCHAR(10), ' '), NCHAR(9), ' '), ' ','<>'),'><',''),'<>',' ') AS QueryText,
626 qs.event_interval,
627
628 qs.total_duration_ms,
629 qs.average_duration_ms,
630 qs.stdev_duration_ms,
631 qs.min_duration_ms,
632 qs.max_duration_ms,
633 qs.duration_50th,
634 qs.duration_75th,
635 qs.duration_90th,
636 qs.duration_95th,
637 qs.duration_99th,
638
639 qs.total_cpu_time_ms,
640 qs.average_cpu_time_ms,
641 qs.stdev_cpu_time_ms,
642 qs.min_cpu_time_ms,
643 qs.max_cpu_time_ms,
644 qs.cpu_time_50th,
645 qs.cpu_time_75th,
646 qs.cpu_time_90th,
647 qs.cpu_time_95th,
648 qs.cpu_time_99th,
649
650 qs.total_physical_reads,
651 qs.average_physical_reads,
652 qs.stdev_physical_reads,
653 qs.min_physical_reads,
654 qs.max_physical_reads,
655 qs.physical_reads_50th,
656 qs.physical_reads_75th,
657 qs.physical_reads_90th,
658 qs.physical_reads_95th,
659 qs.physical_reads_99th,
660
661 qs.total_logical_reads,
662 qs.average_logical_reads,
663 qs.stdev_logical_reads,
664 qs.min_logical_reads,
665 qs.max_logical_reads,
666 qs.logical_reads_50th,
667 qs.logical_reads_75th,
668 qs.logical_reads_90th,
669 qs.logical_reads_95th,
670 qs.logical_reads_99th,
671
672 qs.total_writes,
673 qs.average_writes,
674 qs.stdev_writes,
675 qs.min_writes,
676 qs.max_writes,
677 qs.writes_50th,
678 qs.writes_75th,
679 qs.writes_90th,
680 qs.writes_95th,
681 qs.writes_99th
682FROM query_stats AS qs
683ORDER BY qs.event_interval ASC, qs.total_duration_ms DESC
684OPTION (RECOMPILE) ;
685
686
687
688
689
690
691
692
693
694
695
696 WITH waits_analyzed AS (
697 SELECT query_hash,
698 query_plan_hash,
699 event_interval,
700 statement,
701 wait_type,
702 SUM(duration_ms) AS total_duration_ms,
703 AVG(duration_ms) AS average_duration_ms,
704 COALESCE(STDEVP(duration_ms), 0) AS stdev_duration_ms,
705 MIN(duration_ms) AS min_duration_ms,
706 MAX(duration_ms) AS max_duration_ms,
707 SUM(signal_duration_ms) AS total_signal_duration_ms,
708 AVG(signal_duration_ms) AS average_signal_duration_ms,
709 COALESCE(STDEVP(signal_duration_ms), 0) AS stdev_signal_duration_ms,
710 MIN(signal_duration_ms) AS min_signal_duration_ms,
711 MAX(signal_duration_ms) AS max_signal_duration_ms
712 FROM waits
713 GROUP BY query_hash,
714 query_plan_hash,
715 event_interval,
716 statement,
717 wait_type
718),
719waits_ntile_cte AS (
720 SELECT DISTINCT
721 query_hash,
722 query_plan_hash,
723 event_interval,
724 statement,
725 wait_type,
726 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY duration_ms) OVER (PARTITION BY query_hash, query_plan_hash, event_interval, statement, wait_type) AS duration_50th,
727 PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY duration_ms) OVER (PARTITION BY query_hash, query_plan_hash, event_interval, statement, wait_type) AS duration_75th,
728 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY duration_ms) OVER (PARTITION BY query_hash, query_plan_hash, event_interval, statement, wait_type) AS duration_90th,
729 PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY duration_ms) OVER (PARTITION BY query_hash, query_plan_hash, event_interval, statement, wait_type) AS duration_95th,
730 PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY duration_ms) OVER (PARTITION BY query_hash, query_plan_hash, event_interval, statement, wait_type) AS duration_99th,
731 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY signal_duration_ms) OVER (PARTITION BY query_hash, query_plan_hash, event_interval, statement, wait_type) AS signal_duration_50th,
732 PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY signal_duration_ms) OVER (PARTITION BY query_hash, query_plan_hash, event_interval, statement, wait_type) AS signal_duration_75th,
733 PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY signal_duration_ms) OVER (PARTITION BY query_hash, query_plan_hash, event_interval, statement, wait_type) AS signal_duration_90th,
734 PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY signal_duration_ms) OVER (PARTITION BY query_hash, query_plan_hash, event_interval, statement, wait_type) AS signal_duration_95th,
735 PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY signal_duration_ms) OVER (PARTITION BY query_hash, query_plan_hash, event_interval, statement, wait_type) AS signal_duration_99th
736 FROM waits
737),
738wait_rank AS (
739 SELECT query_hash, query_plan_hash, event_interval, statement,
740 SUM(total_duration_ms) r
741 FROM waits_analyzed
742 GROUP BY query_hash, query_plan_hash, event_interval, statement
743),
744waits AS (
745 SELECT wc.query_hash,
746 wc.query_plan_hash,
747 wc.event_interval,
748 wc.statement,
749 wc.wait_type,
750
751 wc.total_duration_ms ,
752 wc.average_duration_ms,
753 wc.stdev_duration_ms,
754 wc.min_duration_ms,
755 wc.max_duration_ms,
756 wnc.duration_50th,
757 wnc.duration_75th,
758 wnc.duration_90th,
759 wnc.duration_95th,
760 wnc.duration_99th,
761
762 wc.total_signal_duration_ms,
763 wc.average_signal_duration_ms,
764 wc.stdev_signal_duration_ms,
765 wc.min_signal_duration_ms,
766 wc.max_signal_duration_ms,
767 wnc.signal_duration_50th,
768 wnc.signal_duration_75th,
769 wnc.signal_duration_90th,
770 wnc.signal_duration_95th,
771 wnc.signal_duration_99th
772 FROM waits_analyzed wc
773 JOIN waits_ntile_cte as wnc ON wc.query_hash = wnc.query_hash
774 AND wc.query_plan_hash = wnc.query_plan_hash
775 AND wc.event_interval = wnc.event_interval
776 AND wc.statement = wnc.statement
777 AND wc.wait_type = wnc.wait_type
778 JOIN wait_rank AS wr ON wc.query_hash = wr.query_hash
779 AND wc.query_plan_hash = wr.query_plan_hash
780 AND wc.event_interval = wr.event_interval
781 AND wc.statement = wr.statement
782)
783SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(w.statement)), NCHAR(13), ' '), NCHAR(10), ' '), NCHAR(9), ' '), ' ','<>'),'><',''),'<>',' ') AS QueryText,
784 w.event_interval,
785 w.wait_type,
786
787 w.total_duration_ms AS wait_total_duration_ms,
788 w.average_duration_ms AS wait_average_duration_ms,
789 w.stdev_duration_ms AS wait_stdev_duration_ms,
790 w.min_duration_ms AS wait_min_duration_ms,
791 w.max_duration_ms AS wait_max_duration_ms,
792 w.duration_50th AS wait_duration_50th,
793 w.duration_75th AS wait_duration_75th,
794 w.duration_90th AS wait_duration_90th,
795 w.duration_95th AS wait_duration_95th,
796 w.duration_99th AS wait_duration_99th,
797
798 w.total_signal_duration_ms AS signal_wait_total_duration_ms,
799 w.average_signal_duration_ms AS signal_wait_average_duration_ms,
800 w.stdev_signal_duration_ms AS signal_wait_stdev_duration_ms,
801 w.min_signal_duration_ms AS signal_wait_min_duration_ms,
802 w.max_signal_duration_ms AS signal_wait_max_duration_ms,
803 w.signal_duration_50th AS signal_wait_duration_50th,
804 w.signal_duration_75th AS signal_wait_duration_75th,
805 w.signal_duration_90th AS signal_wait_duration_90th,
806 w.signal_duration_95th AS signal_wait_duration_95th,
807 w.signal_duration_99th AS signal_wait_duration_99th,
808
809 w.query_hash,
810 w.query_plan_hash
811FROM waits w
812ORDER BY w.event_interval ASC, w.total_duration_ms DESC
813OPTION (RECOMPILE) ;
814"@
815
816
817try {
818 Write-output "Performing Analysis of Data on $AnalysisDatabaseName"
819 $result = Invoke-SqlCmd -Connectionstring $AnalysisConnectionString -Query $AnalysisQuerySQL -QueryTimeout 900 -OutputAs DataSet
820} catch {
821 write-error "An Error occured while extracting waits and query stats $($_.Exception.Message)"
822 exit
823}
824Write-output "Analysis is complete"
825
826
827
828$response = Read-Host -Prompt "Would you like to remove analysis database $AnalysisDatabaseName [y/n]:"
829if ($response -eq "y") {
830 Remove-AzureRmSqlDatabase -ServerName $DatabaseServer -DatabaseName $AnalysisDatabaseName -Force -ResourceGroupName $armserver.ResourceGroupName
831} else {
832 try {
833 Write-output "Scale down database $AnalysisDatabaseName"
834 Set-AzureRmSqlDatabase -ServerName $DatabaseServer -DatabaseName $AnalysisDatabaseName -ResourceGroupName $armserver.ResourceGroupName -Edition Standard -RequestedServiceObjectiveName S0
835 } catch {
836 Write-Error "Failed to scale $analysisdatabase down $($_.Exception.Message)"
837 }
838}
839if ($OutputFile) {
840 $ExcelArgs = @{
841 AutoSize = $true
842 Path = $OutputFile
843 FreezeTopRow = $true
844 TitleFillPattern = 'Solid'
845 TableStyle = 'Light9'
846 }
847 if (Test-Path -Path $OutputFile) {
848 $newname = "$OutputFile.$(Get-Date -f "yyyyMMddHHmmss")"
849 write-Output "Found file at $OutputFile renaming to $newname"
850 Rename-Item -Path $OutputFile -NewName $newname
851 }
852 $result.Tables[0] | select * -ExcludeProperty RowError,RowState,Table,ItemArray,HasErrors | Export-Excel @ExcelArgs -WorkSheetname "query_stats" -TableName "Query_Stats"
853 $result.Tables[1] | select * -ExcludeProperty RowError,RowState,Table,ItemArray,HasErrors | Export-Excel @ExcelArgs -WorkSheetname "wait_stats" -TableName "Wait_Stats" #-IncludePivotTable -PivotRows wait_type -PivotData @{wait_stdev_duration_ms="sum"} -IncludePivotChart -ChartType PieExploded3D
854 write-host "Analysis written to $OutputFile"
855} else {
856 $result | select * -ExcludeProperty RowError,RowState,Table,ItemArray,HasErrors | ft -AutoSize
857}
858$result.Dispose()