Check your SQL Server using Windows PowerShell – Part 7

Part I of this series illustrated the
first and most important check on SQL Server–how to ping the host. Part 2 examined how to check all of the
windows services related to SQL Server and part 3 discussed how to check the
hardware and operating system information. Part 4 illustrated how to get hard disk
and network adapter information from the host machine. Part 5 demonstrated how we can connect
to SQL Server and how we could query SQL Server related properties and
configuration. Part 6
illustrated on how to check database status and database size information. This
article discusses how to get information on the TOP 10 queries based on the CPU
usage.

Step 1

Type or
copy and paste the following code to C:\CheckSQLServer\Checktopqueries.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) in (‘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 version is not SQL Server 2005 or above. Can”t 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 C:\CheckSQLServer\CheckSQL_Lib.ps1 with the
following code.


. ./checktopqueries.ps1

Now C:\CheckSQLServer\CheckSQL_Lib.ps1 will have pinghost,
checkservices, checkhardware, checkOS, checkHD, checknet, checkinstance,
Checkconfiguration and checkdatabases as shown below.


#Source all the functions relate to 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 will be updated with sourcing
of new scripts, such as checktopqueries.ps1.

Step 3

Append
C:\CheckSQLServer\CheckSQLServer.ps1 with the following code.


Write-host “Checking Top 10 Queries based on CPU Usage.”
Write-host “………………………..”
checktopqueries $instancename |select-object query_text, AvgCPUTime |format-table

The 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 “Checking 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 SQL Server databases…..”
Write-host “Checking Database status and size…..”
Write-host “………………………..”
checkdatabases $instancename |format-table
Write-host “Checking Top 10 Queries based on CPU Usage.”
Write-host “………………………..”
checktopqueries $instancename |select-object query_text, AvgCPUTime |format-table
}

Note: This
CheckSQLServer.ps1 will be updated with new conditions and parameters in future
installments of this article series as well.

Sourcing basically loads
the functions listed in the script file and makes it available during the
entire PowerShell session. In this case, we are sourcing a script, which in
turn is going to source many scripts.

Step 4

Now let’s execute the
script, CheckSQLServer.ps1, by passing “PowerServer3” host as the argument and
Powerserver3\SQL2008 instance as the second argument as shown below.


./CheckSQLServer.ps1 PowerServer3 PowerServer3\SQL2008

You will get the results shown
below. [Refer Fig 1.0]

Results






Checking Top 10 Queries based on CPU Usage.
………………………..
WARNING: column “AvgCPUTime” does not fit into the display and was removed.
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…

Checking Top 10 Queries based on CPU Usage results

Figure 1.0

Step 5

Now let’s execute the
script on a machine that doesn’t exist, as shown below.


./CheckSQLServer.ps1 TestServer testserver

The results are shown
below. [Refer Fig 1.1]

result of executing script on machine that does not exist

Figure 1.1

Conclusion

This is
the seventh part of the article series “Check
your SQL Server using Windows PowerShell”
. The article
illustrated how to query the Top 10 queries executed on the SQL Server instance
by CPU usage.

Download the scripts for this article.

»


See All Articles by Columnist
MAK

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles