PIVOT and dm_os_performance_counters

Dm_os_performance_counters is a great Data Management View that I use quite often.  You can find a ton of information that can help you identify performance bottlenecks in your system.  It can be used to view Page Life Expectancy, Free list stalls/sec, Lazy writes/sec, Batch Requests/sec, SQL Compilations/sec,  SQL Re-Compilations/sec, Cursor Requests/sec, Checkpoint pages/sec as well many other key data points.  All of which can play a role in the effective identification of performance bottlenecks.

First of all, let’s cover a bit of background on this DMV.  It has been around since SQL Server 2008 and will work in Azure SQL Database as well as Azure SQL Data Warehouse.  Although in the Azure SQL Data Warehouse it has a slightly different name, sys.dm_pdw_nodes_os_performance_counters.  The use of the DMV requires view server state permissions. There are a number of columns that are important, object_name and counter_name are just a couple.  There is a column named cntr_type column.  This is the column that will help us identify the type of each counter.

There are five counter types, although we will only discuss two of them here.  You can find more information on the other types here, link.  The first one has a counter type value of 27269576. This counter type is cumulative since the last time the SQL Server service was started.  An example of this type of counter is Batch Request/sec.

The other counter type have a value of 65792.  This counter type is the value as it stands right now.  An example of this is Page Life Expectancy, which changes constantly as normal activity takes place on the server.

This post focuses on the counter type that is cumulative.  The challenge that this counter type presents is that although it may be a value greater than 0, it is something that may not be happening at this point in time. This is what we need to determine, is this still happening or did it happen in the past.

Free List Stalls/sec is an example of this counter type.  This counter is one of several that can help us determine if there is memory pressure on the server. If the current value is 30,000 and the service was last restarted 3 months ago, we don’t really know when these took place.  So what we want to do is determine if they are still happening.  This is where the PIVOT can help.  If you simply pull from the DMV you will get a single row for many of these types of counters.  Even if you use the WAITFOR to capture the value multiple times and place it into a table, it will look something like this.  Notice each row is 10 seconds apart.

I think this presents a few challenges, the biggest being how can I easily tell if the event is still happening.  Another challenge is when you have two counters that should be looked at as a pair, like Batch Requests/sec  and SQL Compilations/sec.  I want to be able to easily look at the data and see the values as they change over a period of time. I want it to look something like this.  The counter names on the left and the times of capture along the top.  As you can see, in this example the capture of the data took place every 10 seconds.

This can be accomplished by using the PIVOT command. The first thing we do is to declare a counter variable and a table to store the values.

DECLARE @iCount AS SMALLINT
CREATE TABLE #CounterValues
( Counter_name VARCHAR(50)
, Currentvalue INT
, DateTimeOfCapture DATETIME)

Then set the counter variable to 1, although you can set this to 0 if you like.  This variable will server as our counter so the WHILE knows when to stop.

SET @iCount = 1

The next step is to write the code to capture the counter values and insert the data it the temporary table created above.  Because we need to capture the values over a period of time, the WAITFOR DELAY is used.  In this case the delay is 10 seconds, although you can change this to suit your needs. Of course, don’t forget to increment the counter variable. You will need to determine what counters you would like to capture.  Notice in the WHERE clause, we are looking for an instance_name of ” or ‘_total’.  This will allow the code to only capture one row for each counter.  The number 10 is the number of times we want to capture the counter values.  If you want to capture the data more frequently, simpley modify the number of seconds in the WAITFOR DELAY.  Here is link to my post in this topic, WAITFOR.

WHILE @iCount < 10
BEGIN

INSERT INTO #CounterValues
SELECT Counter_Name
, cntr_value
, GETDATE()
FROM sys.dm_os_performance_counters
WHERE counter_name IN
( ‘Free list stalls/sec’
, ‘Lazy writes/sec’
, ‘Batch Requests/sec’
, ‘SQL Compilations/sec’
, ‘SQL Re-Compilations/sec’
, ‘Cursor Requests/sec’
, ‘Checkpoint pages/sec’)
AND instance_name IN (,‘_total’)

WAITFOR DELAY ’00:00:10′

SET @icount = @icount + 1
END

Then we need to declare two variables, one to hold column names and the other to execute the dynamic SQL.

DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX);

Now we can into the meat of the PIVOT.  We want to capture the column names from the time of each cycle of data capture above.

SET @cols = STUFF((SELECT DISTINCT ‘,’ + QUOTENAME(
CONVERT(VARCHAR(40),LEFT(CONVERT(CHAR(40),DateTimeOfCapture, 109) , 20))) AS DateTimeOfCapture
FROM #CounterValues
ORDER BY DateTimeOfCapture ASC
FOR XML PATH(”), TYPE
).value(‘.’, ‘NVARCHAR(MAX)’)
,1,1,)

The above code will allow us to use the time of data capture as column headers as seen below.

If you print the @cols variable it will look something like this. You can see a series of dates with times separated with a comma. These values will now become the column names for our final result set.

[Mar 19 2019 11:17:00],[Mar 19 2019 11:17:10],[Mar 19 2019 11:17:20],[Mar 19 2019 11:17:30],[Mar 19 2019 11:17:40]

Now that we have our column names, let’s finishing up with the PIVOT.  Because the column names are created dynamically, we will need to use Dynamic SQL to complete it.

SET @query = ‘SELECT Counter_Name,’ + @cols + ‘ from
(
SELECT Counter_Name, currentvalue, LEFT(CONVERT(CHAR(40),DateTimeOfCapture, 109) , 20) AS TimeOfCapture
FROM #CounterValues
) x
PIVOT
(
SUM(Currentvalue)
FOR TimeOfCapture IN (‘ + @cols + ‘)
) p ‘

EXECUTE(@query)

If you use these blocks of code together you will get what I think is the desired result. Notice the times across the top and the counter name on the left.

Here is a link to the complete code, PIVOTDMV.

Thank you for stopping by my blog and I hope you will find this useful!

sp_helptext

If you need to see the definition of an object you have several choices.  First, you could right click on the object and generate the script.  Another option you have is to use sp_helptext.  This system stored procedure can be used on any version of SQL Server since 2008, this includes Azure SQL Database and Azure SQL Data Warehouse.  This stored procedure can be used to obtain the definition of a number of objects, however it does not work on all object types.

According to the Microsoft documentation you can use this on the following object types:

        • User-defined rule
        • Default
        • Unencrypted Transact-SQL stored procedure
        • User-defined Transact-SQL function
        • Trigger
        • Computed column
        • CHECK constraint
        • View
        • System object such as a system stored procedure

The procedure has two parameters, @objname and @columnname.  The first parameter, @objname is the name of the object you are seeking to get the definition.  The second parameter, which is optional,  can be used to get the definition of a computed column.

When using the stored procedure you must have the rights to view definitions of objects.

We will start with the obvious, the first parameter, the name of the object.  There just a few rules regarding this parameter.  First of all, the object must exist in the current database.  If the object is not in the dbo schema, you will need to include the schema and include single quotes like below.

sp_helptextsales.SalesOrderDetail

If you include the schema in the object name and do not add the quotes you will get following error.

sp_helptext dbo.uspGetBillOfMaterials

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘.’.

If the object is in the dbo scheme it will look like this, single quotes not needed.  Although quotes are not required, it will also not fail if you include them.

sp_helptext uspGetBillOfMaterials

When you execute the above statement you will get these results.  Sometimes it might be nice to see it in the grid, however I find it better to send the results to text instead.  It is easier to copy to the clipboard.

When you send the results to text, SQL Server will add the word text at the top, this is actually the column name of the result set.

 

I really like to use sp_helptext when I have multiple objects that I need script. However, there is a bit of a catch.  If you try to run it without the GO keyword between you will receive this error.

The second parameter only works on calculated columns.  Below you will find the code.  Notice that the column name is not in quotes.  It will work either way, with or without quotes.

sp_helptext @objname = N’Sales.SalesOrderHeader‘, @columnname = salesordernumber ;

This is the results of the above statement:

(isnull(N’SO’+CONVERT([nvarchar](23),[SalesOrderID]),N’*** ERROR ***’))

If you attempt to run this against a column that is not a calculated column you will get this error:

Msg 15646, Level 16, State 1, Procedure sp_helptext, Line 75 [Batch Start Line 0]
Column ‘orderdate’ is not a computed column.

Just a few additional comments.  Sp_helptext will not work on encrypted objects.

This next topic may be in a future blog post, however, I did want to mention there as well.  There is another option. This code will return the definition of the uAddress trigger.

USE AdventureWorks2014;
GO
SELECT OBJECT_DEFINITION (OBJECT_ID(N‘Person.uAddress’)) AS ‘Trigger Definition’;
GO

When you run this, you will see the definition of the trigger.

Thank you for visiting my blog.