System Statistical Functions, Stored Procedures, and the DBCC SQLPERF Command - Microsoft SQL Server 2012 Performance Tuning - page 61

Cur.TotalConnections AS CurrentConnections
,Cur.StatDateTime AS CurrentStatDateTime
,Prev.TotalConnections AS PreviousConnections
,Prev.StatDateTime AS Previous_StatDateTime
,Cur.TotalConnections - Prev.TotalConnections AS
ConnectionsIncreamentedBy
,DATEDIFF(millisecond, Prev.StatDateTime, Cur.StatDateTime)
AS ConnectionsIncreamentedIn
FROM cteStatistics AS Cur
LEFT OUTER JOIN cteStatistics AS Prev
ON Cur.ID = Prev.ID + 1
How it works...
We first created a table called dbo.tbl_ServerHealthStatistics . Before creating the
table, the script checks for its existence with the help of the OBJECT_ID() function. The
script creates the table only if OBJECT_ID() returns NULL and the table does not exist.
This will ensure that the same script can be executed multiple times without any problem.
When this script is executed for the first time, it will create the table. In all the subsequent
executions, the IF condition will not be satisfied and table creation logic will be skipped.
The next batch in the script gathers server health statistics data with the INSERT…SELECT
statement. In the SELECT statement, multiple system statistical functions are called and the
result set is inserted into table dbo.tbl_ServerHealthStatistics .
Finally, we query the table dbo.tbl_ServerHealthStatistics , which contains the
collected statistics. Because we need to compare the values for a row with the values of its
previous row, we are using a Common Table Expression (CTE) here by declaring it with the
WITH syntax. After creating CTE, we use the two instances of CTE in our query by using
LEFT JOIN . The first instance is aliased as Cur , which becomes the left table of the join
and represents the current rows. The second instance is aliased as Prev , which becomes
the right table of the join and represents the previous rows. This is achieved using the join
condition Cur.ID = Prev.ID + 1 . You can see that we derived the difference between
the connections, and time interval between a row and its previous row, so that we can tell
how many new connections have been created in what time frame.
Note how we convert the time values of TotalCPUBusyTime , TotalCPUIdleTime ,
and TotalIOBusyTime columns from tick to microseconds. We multiply these values by
Timeticks . While multiplying, we need to watch out for results for which the value falls
beyond the integer limit. This is the reason why we convert one of the operands ( TimTicks )
by multiplication to BIGINT , to avoid arithmetic overflow.