Get Answers for Paul Randal’s Survey FAST!

You may call it ‘lazy’ but I call it ‘efficiently gathering quality results’.

Last week Paul Randal (twitter) blogged and asked people to take part in a survey asking what the top Wait Type on their systems are.  Since I have 100s of SQL Servers and I wanted to give him an answer for each one. And because I’m lazy…  I modified Paul’s (or Glen’s [ blog | twitter ] ) query to include a ServerName column then Select’ed the results of the query on my local machine into a table. Once I had the table set up I fired up PowerShell and used the same type of script that I showed off in today’s Hey Scripting Guy post. The only difference being that I swapped out the query and table name of course.

CREATE TABLE [dbo].[TopWaitTypes](
   [InstanceName] [nvarchar](128) NOT NULL,
   [WaitType] [nvarchar](60) NOT NULL,
   [Wait_S] [decimal](14, 2) NULL,
   [Resource_S] [decimal](14, 2) NULL,
   [Signal_S] [decimal](14, 2) NULL,
   [WaitCount] [bigint] NOT NULL,
   [Percentage] [decimal](4, 2) NULL
) ON [PRIMARY]

Make sure to read to the bottom for the significantly less code version!

foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\Development | where {$_.Mode -ne "d"} )
{
$dt=invoke-sqlcmd -query "WITH Waits AS
    (SELECT
        wait_type,
        wait_time_ms / 1000.0 AS WaitS,
        (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
        signal_wait_time_ms / 1000.0 AS SignalS,
        waiting_tasks_count AS WaitCount,
        100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
        ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT IN (
        'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
        'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
        'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
        'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
        'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
        'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
     )
SELECT @@ServerName AS 'ServerName',
     W1.wait_type AS WaitType,
     CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
     CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
     CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
     W1.WaitCount AS WaitCount,
     CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage
FROM Waits AS W1
INNER JOIN Waits AS W2
     ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold" -ServerInstance $RegisteredSQLs.ServerName -database master
Write-DataTable -ServerInstance "Win7NetBook" -Database CentralInfo -TableName TopWaitTypes -Data $dt
}

After reading Chad Miller’s (Blog|Twitter) excellent post yesterday though, I decided to combine our approaches.  If you haven’t done so already, you’ll need to download Chad’s invoke-sqlcmd2 and Write-DataTable functions and load them up in your environment.  You’ll also need to store the edit the query from Paul’s blog to add a ServerName column and then store it in a .sql file in the working directory of your PowerShell session.  Once that’s all done if you haven’t given up and closed your browser you can use a piece of code like this to gather up all of this information from all of the server you have in your Registered Servers list. 

For safety’s sake when I put out scripts like this I always make sure to dive down into the \Development branch of my Registered Servers list so that you will always be able to start somewhere ‘safe’ when you run this for the first time.  Or just get a big red nasty error message if you don’t have a “Development” Registered Servers Group

Here’s the code that I came up with for me:

foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\Development | where {$_.Mode -ne "d"} )
{
$dt=invoke-sqlcmd2 -ServerInstance $RegisteredSQLs.ServerName -database master -InputFile ./PaulAndGlensWaitQuery.sql -As 'DataTable'
Write-DataTable -ServerInstance "Win7NetBook" -Database CentralInfo -TableName TopWaitTypes -Data $dt
}

Paul had mentioned that “The free survey system only allows a single vote per IP address – if you have any other results, send them in email (mailto:paul@SQLskills.com?Subject=Wait stats) or attach a comment below.” so I plan to send him an email with the top wait from each of my servers, minus the server name of course.

I Hope this helps start some ideas in your head about how you can leverage PowerShell in your environment!

By the way…  I got the idea for leveraging the Registered Servers piece from an old Buck Woody ( blog | twitter ) post so pretty much none of this is my code, I just put it all together.  And THAT is why I love twitter!