Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Oct 15, 2008

Check your SQL Server using Windows PowerShell - Part 7

By Muthusamy Anantha Kumar aka The MAK

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM