Test SQL Server with Windows Powershell - Part 7

Understanding test SQL server with Windows powershell - part 7 is easier when the main points are organized clearly. This guide brings together the essential details and practical considerations.

Test SQL Server with Windows PowerShell - Part 1 Test SQL Server with Windows PowerShell - Part 2 Test SQL Server with Windows PowerShell - Part 3 Test SQL Server with Windows PowerShell - Part 4 Test SQL Server with Windows PowerShell - Part 5 Test SQL Server with Windows PowerShell - Part 6

How Test SQL Server with Windows Powershell - Part 7 Works

Muthusamy Anantha Kumar aka The MAK

. com - In part 6, I showed you how to check the database status information about the size of the database, and in this section we will introduce you how get that information on TOP 10 queries based on CPU performance. Step 1 Type or copy and paste the following code into the file C: CheckSQLServerChecktopqueries. ps1. Function checktopqueries ( [string] $ servername ) { $ SqlConnection = New-Object System. Data. SqlClient. SqlConnection $ SqlCmd = New-Object System. Data. SqlClient. SqlCommand $ SqlAdapter = New-Object System. Data. SqlClient. SqlDataAdapter $ DataSet = New-Object System. Data. DataSet $ SqlConnection. ConnectionString = "Server = $ servername; Database = master; Integrated Security = True" $ SqlCmd. CommandText = " If LEFT (convert (varchar (100), SERVERPROPERTY ('productversion')), 1) print ('9', '1') Begin Select Top 10 case when sql_handle IS NULL Then '' Else (substring (st. text, (qs. statement_start_offset + 2) / 2, (case when qs. statement_end_offset = -1 Then len (convert (nvarchar (MAX), st. text)) * 2 Else qs. statement_end_offset End - qs. statement_start_offset) / 2)) End as query_text , creation_time, last_execution_time , rank () over (order by (total_worker_time + 0.0) / Execution_count desc, Sql_handle, statement_start_offset) as row_no , (rank () over (order by (total_worker_time + 0.0) / Execution_count desc, Sql_handle, statement_start_offset))% 2 as l1 , (total_worker_time + 0.0) / 1000 as total_worker_time , (total_worker_time + 0.0) / (execution_count * 1000) As [AvgCPUTime] , total_logical_reads as [LogicalReads] , total_logical_writes as [LogicalWrites] , execution_count , total_logical_reads + total_logical_writes as [AggIO] , (total_logical_reads + total_logical_writes) / (execution_count + 0.0) as [AvgIO] , db_name (st. dbid) as db_name , st. objectid as object_id From sys. dm_exec_query_stats qs Cross apply sys. dm_exec_sql_text (sql_handle) st Where total_worker_time> 0 Order by (total_worker_time + 0.0) / (execution_count * 1000) End Else Begin Print 'Server phiên b?n không ph?i SQL Server 2005 ho?c trên. cannot query TOP queries' End " $ SqlCmd. Connection = $ SqlConnection $ SqlAdapter. SelectCommand = $ SqlCmd $ SqlAdapter. Fill ($ DataSet) | out-null $ dbs = $ DataSet. Tables [0] $ dbs $ SqlConnection. Close () } Step 2 Append to the file C: CheckSQLServerCheckSQL_Lib. ps1 the following code. ../checktopqueries. ps1 Now file C: CheckSQLServerCheckSQL_Lib. ps1 will have pinghost, checkservices, checkhardware, checkOS, checkHD, checknet, checkinstance, Checkconfiguration and checkdatabases as shown below. #Source all the relate to functions CheckSQL ../PingHost. ps1 ../checkservices. ps1 ../checkhardware. ps1 ../checkOS. ps1 ../checkHD. ps1 ../checknet. ps1 ../checkinstance. ps1 ../checkconfiguration. ps1 ../checkdatabases. ps1 ../checktopqueries. ps1 Note: This CheckSQL_Lib. ps1 file will be updated from the source of the new script, such as checktopqueries. ps1. Step 3 Append to the file C: CheckSQLServerCheckSQLServer. ps1 the following code. Write-host "Top 10 Queries based on CPU Usage." Write-host "…" Checktopqueries $ instancename | select-object query_text, AvgCPUTime | format-table CheckSQLServer. ps1 will become #Objective: To check various status of SQL Server #Host, instances and databases. #Author: MAK #Date Written: June 5, 2008 Param ( [string] $ Hostname, [string] $ instancename ) $ global: errorvar = 0 ../CheckSQL_Lib. ps1 Write-host "Checking SQL Server." Write-host "…" "Write-host" Write-host "Arguments accepted: $ Hostname" Write-host "…" Write-host "Pinging the host machine" Write-host "…" Pinghost $ Hostname If ($ global: errorvar -ne "host not reachable") { Write-host "Check Windows services on the host related to SQL Server" Write-host "… " Checkservices $ Hostname Write-host "Checking hardware Information." Write-host "…" Checkhardware $ Hostname Write-host "Checking OS Information." Write-host "…" CheckOS $ Hostname Write-host "Checking HDD Information." Write-host "…" CheckHD $ Hostname Write-host "Checking Network Adapter Information." Write-host "…" Checknet $ Hostname Write-host "Checking Configuration information." Write-host "…" Checkconfiguration $ instancename | format-table Write-host "Checking Instance property Information.`." Write-host "…" Checkinstance $ instancename | format-table Write-host "Checking the SQL Server databases." Write-host "Checking Database status and size." Write-host "…" Checkdatabases $ instancename | format-table Write-host "Top 10 Queries based on CPU Usage." Write-host "…" Checktopqueries $ instancename | select-object query_text, AvgCPUTime | format-table } Note: The CheckSQLServer. ps1 file will be updated with new conditions and new parameters in later sections of this series. The source will load the functions listed in the script file and make it available during the entire PowerShell session. In this case, we get the source from a script, which is derived from many other scenarios. Step 4 Now let's execute the script, CheckSQLServer. ps1, using 'PowerServer3' as an argument and Powerserver3SQL2008 as a second argument as shown below. ./CheckSQLServer. ps1 PowerServer3 PowerServer3SQL2008 We will get the results shown below (refer to Figure 1.0). Result . . . . Check Top 10 Queries based on CPU Usage. … WARNING: "AvgCPUTime" column does not fit into the display and ?ã ???c g? b?. Query_text ---------- Select top 2. Select top 2. UPDATE [Notifications] WITH (TABLOCKX). Select name from master. dbo. sysdatabases Select @dbsize = sum (convert (bigint, case when status & 64 = 0 then size else 0 end)). Select @dbsize = sum (convert (bigint, case when status & 64 = 0 then size else 0 end)). Select @configcount = count (*). UPDATE [Event] WITH (TABLOCKX). Select @confignum = configuration_id, @prevvalue = convert (int, isnull (value, value_in_use)). Update [Notifications] set [ProcessStart] = NULL, [ProcessHeartbeat] = NULL, [Attempt] = [Attemp.

Figure 1.0

Step 5 Let's execute the script on the computer that doesn't exist as shown below. ./CheckSQLServer. ps1 TestServer testserver The results are as shown below (refer to Figure 1.1)

Figure 1.1

Conclude In this article, we have demonstrated how to query the Top 10 queries executed on the SQL Server instance of CPU performance. Download scripts for this section.