Archive

Archive for March, 2012

Calculating Deltas for sys.dm_os_performance_counters, Part II

March 23, 2012 Leave a comment

In my last post, I showed how to calculate the values of per-second metrics from the sys.dm_os_performance_counters DMO. In this post, I will show how to save those metrics and calculate the page reads/sec value for different time frames.

For this demonstration, I created a table with the following definition:


CREATE TABLE pageread_history
(insertdate DATETIME
,pagereads BIGINT)

I then created a job, scheduled to execute every minute, to run the following code:

INSERT INTO pageread_history
SELECT GETDATE(), cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page reads/sec'

Finally, in order to generate some non-trivial values, I executed the following code:


USE primenumbers
GO

-- Clear the buffer cache before each execution
DBCC DROPCLEANBUFFERS

-- Select every row from a million-row table
SELECT * FROM primenumbers

-- Execute the query 100 times
GO 100

This query will force SQL Server to read all of the pages in the table from disk every time the query executes, generating page read metrics. DBCC DROPCLEANBUFFERS requires SA access and clears the buffer cache, forcing physical reads. It should NOT be run on a production system or any system on which you care about performance. I ran this on my localhost in order to generate some page read volume.

To calculate page reads/sec using the values stored in the table, I created the following stored procedure:

USE testdb
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Frank Gill
-- Create date: 2012-03-18
-- Description:	Calculate page reads/sec based on input date values
-- =============================================
ALTER PROCEDURE DUP_CALCULATE_PAGE_READS_SEC
	-- @mindate and @maxdate will be passed in on execution and @pagereadssec will be output
	@mindate DATETIME, 
	@maxdate DATETIME,
	@pagereadssec INT OUTPUT
AS
BEGIN

	SET NOCOUNT ON;
	
	-- Declare variables
	DECLARE @maxval BIGINT
	DECLARE @minval BIGINT
	DECLARE @deltaval INT
	DECLARE @deltasec INT
	DECLARE @tablemaxdate DATETIME
	DECLARE @errormessage VARCHAR(500)
	
	-- If @maxdate or @mindate are greater than the max date in the table, NULL will be returned
	-- To check for this, select the MAX date value out of the table
	SELECT @tablemaxdate = MAX(insertdate)
	FROM pageread_history
	
	-- If @maxdate or @mindate are greater than @tablemaxdate, raise an error and output the MAX date value
	IF (@mindate > @tablemaxdate) OR (@maxdate > @tablemaxdate)
		BEGIN
			SET @errormessage = 'Both @mindate and @maxdate must be greater than ' + CONVERT(VARCHAR(20),@tablemaxdate,121)
			RAISERROR (@errormessage,
						16,
						1)	

	-- Pull the first value from a datetime greater than or equal to @mindate	
	SELECT TOP 1 @minval = pagereads
	FROM pageread_history
	WHERE insertdate >= @mindate
	
	-- Pull the first value from a datetime greater than or equal to @maxdate
	-- If @maxdate or @mindate are greater than the max date in the table, NULL will be returned
	SELECT TOP 1 @maxval = pagereads
	FROM pageread_history
	WHERE insertdate >= @maxdate

	-- Calculate the delta value with the counts returned from the table
	SET @deltaval = @maxval - @minval

	-- Calculate the number of seconds elapsed
	SET @deltasec = DATEDIFF(SS, @mindate, @maxdate)

	-- Calculate page reads/sec
	SET @pagereadssec = @deltaval / @deltasec

END
GO

While I’ve used page reads in the previous examples, this code can be modified to calculate any of the per-second metrics returned from sys.dm_os_performance_counters. I hope you can use the ideas presented here.

Categories: DMO, PerfMon, SQL Server, T-SQL Tags: ,

Calculating Deltas for sys.dm_os_performance_counters, Part I

March 21, 2012 1 comment

In the environment I work we collect a number of performance metrics from the sys.dm_os_performance_counters DMO. This data is useful for troubleshooting and historical analysis. Analyzing this data presents a challenge because SQL Server stores any metric measured per second as a cumulative counter that is reset when the instance starts up. To gather meaningful information from the data, you must take the delta between two measurements taken at different times and divide by the seconds elapsed between the two measurements.

I was reminded of this after reading an excellent article by Jonathan Kehayias on the debate over buffer cache hit ratio and page reads/sec. Here is example of calculating deltas using page reads/sec as an example:

USE master
GO

-- Declare variables

DECLARE @maxval BIGINT
DECLARE @maxdate DATETIME
DECLARE @minval BIGINT
DECLARE @mindate DATETIME
DECLARE @deltaval INT
DECLARE @deltasec INT
DECLARE @pagereadssec INT

-- Collect the mininum values for page reads/sec and run date

SELECT @minval = cntr_value, @mindate = GETDATE()
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page reads/sec'

-- Wait for 1 minute
WAITFOR DELAY '00:01:00'

-- Collect the maximum value for page reads and run date
SELECT @maxval = cntr_value, @maxdate = GETDATE()
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page reads/sec'

-- Calculate the number of page reads in the time elapsed
SET @deltaval = @maxval - @minval

-- Calculate the number of seconds elapsed
SET @deltasec = DATEDIFF(SS, @mindate, @maxdate)

-- Calculate page reads/sec
SET @pagereadssec = @deltaval / @deltasec

-- Select the minimum value, maximum value and the three calulated values to confirm the process is working correctly
-- This illustrates the cumulative nature of the sys.dm_os_performance_counters values

SELECT @maxval AS [Maximum Value], 
@minval AS [Minimum Value], 
@deltasec AS Seconds, 
@deltaval AS [Page Reads], 
@pagereadssec AS [Page Reads/Sec]

Here is an example of the results from an extremely active instance:

Instance Max Val Min Val Sec Page Reads Page Reads/Sec
myinst 20494050250 20494046388 60 3862 64

From these results, you can see how large the cumulative counters for these metrics can get and why delta calculations are so important. In my next post, I will show how to save these values historically and a procedure for calculating the per-second value over different time frames.

Categories: DMO, PerfMon, SQL Server, T-SQL Tags: ,

SQL Server 2012 Launch

March 17, 2012 Leave a comment

I attended the SQL Server 2012 launch this past Thursday. Thanks to Dave Paulson, Ross LoForte, Ron Martin and the rest of the folks at Microsoft for putting on such a great event. Some of the highlights from the launch:

Ross’s demo of AlwaysOn

It was the first time Id seen it in action and it is impressive. I received answers to some questions about the listener, and I hope to get it up and running in a lab environment this week.

A better understanding of the ColumnStore index

Data warehousing is one of things on my learning list for this year, so the information Dave Paulson provided should come in handy.

Tempdb on a local drive

We are testing a lot of new products and platforms in our environment this year: VMWare, Microsoft’s Data Consolidation Appliance, and SQL Server 2012. This is another item to put on the list. I am looking forward to running performance comparisons of tempdb on our existing SAN versus a local drive.

Reminders of SQL Server 2008 features

Dave Paulson’s presentation on Mission Critical features contained a section on SQL Server 2008 features, including Policy-Based Management and Resource Governor. I have done some initial research into these features, but this will work to implement them this year.

Infonomics

The highlight of the event was the keynote given by Doug Laney of Gartner. The topic was infonomics, or methods of treating information as an asset. Having worked in information technology for 13 years with the last five in database administration, I understand the value of the data I work with. But I’d never given thought to that data as an asset on a balance sheet.

Laney talked about four ways to determine the value of information:

  1. Replacement value – How much would you have to pay to replace your data?
  2. Revenue generation – How does your data contribute to the bottom line?
  3. Market value – How much can you sell your data for on the open market?
  4. Ransom – How much would you pay if your data was held hostage?

It was an extremely thought-provoking presentation, and I’ve started conversations with my leadership about bringing these ideas into our organization. Gartner is releasing two white papers in the coming weeks, and I am looking forward to reading them. I will post more on the topic after reading.

Using Dynamic T-SQL In A WHILE Loop

March 15, 2012 1 comment

In my last post, I covered using xp_cmdshell to check the status of the SQL Server Agent service. I also mentioned my environment consists of more than 200 instances of SQL Server. The challenge I faced when developing this process was finding a way to check the Agent service on all instances from a central location. Fortunately, we have a central repository using linked servers to collect performance metrics. I will show how to use these linked servers to check Agent status. You can find the complete stored procedure code at the show source link at the bottom of the post. I will step through this code line by line.

The repository database has a table that contains all of the instances in the environment. The naming standard for linked servers in the environment is DUL + instance name. The following block of code selects all of the active instances names and prefixes them with DUL, inserting them into a temp table:

set @currentrow = 1

INSERT #serverlist
SELECT 'DUL' + dpc_sql_cluster_nm
FROM duddba_master.dbo.DUT_POD_SQL_SERVER
WHERE dpc_inuse_ind = 1
AND dpc_ismonitored_ind = 1

SET @totalrows = @@rowcount

The dpc_inuse_ind and dpc_ismonitored_ind flags allow instances to be excluded from monitoring in the event of maintenance. Setting @totalrows to the @@rowcount returned from the insert provides an upper limit for the loop that will run in the next step.

WHILE @currentrow <= @totalrows
BEGIN 

SET @servername = (SELECT TOP 1 servername FROM #serverlist)
SET @currentrow = @currentrow + 1

SET @sqlstr = @servername + + '...xp_cmdshell ''tasklist'''

INSERT INTO #processes
EXEC (@sqlstr)

SELECT @rowcount = COUNT(*) FROM #processes
WHERE processlist LIKE '%sqlagent%'

INSERT INTO #status
VALUES
(SUBSTRING(@servername,4,8), @rowcount)

DELETE FROM #serverlist WHERE servername = @servername
DELETE FROM #processes 

END

The WHILE loop executes the code between the begin and end once for each row in the temp table, starting with @currentrow set to 1.

WHILE @currentrow <= @totalrows

The next two lines select the first instance out of the temp table, write it to variable @servername and increment the @currentrow counter.

SET @servername = (SELECT TOP 1 servername FROM #serverlist)
SET @currentrow = @currentrow + 1

The next three lines build a dynamic SQL string using an xp_cmdshell command to generate a list of services running on the instance. This dynamic SQL string is executed and the results are inserted into temp table #processes.

SET @sqlstr = @servername + + '...xp_cmdshell ''tasklist'''

INSERT INTO #processes
EXEC (@sqlstr)

Next, variable @rowcount is populated with the count of SQL Agent processes running on the machine. A non-zero count indicates the process is running.

SELECT @rowcount = COUNT(*) FROM #processes
WHERE processlist LIKE '%sqlagent%'

In order to prevent an alert when the instance is starting up, the create date for tempdb and the datetime value from 5 minutes earlier are captured.

SELECT @crdate = crdate FROM master.sys.sysdatabases
WHERE name = 'tempdb'

SELECT @startup = DATEADD(minute,-5,getdate())

If the rowcount is 0 and the instance has been up for more than 5 minutes, an e-mail will be sent indicating the instance SQL Agent is not running on the instance. The REPLACE statement strips the DUL prefix from the instance name.

IF (@rowcount = 0 AND (@sixmin > @crdate))
BEGIN
DECLARE @body1 VARCHAR(100)
DECLARE @subject1 VARCHAR(100)
DECLARE @servermail VARCHAR(8)
SET @servermail = REPLACE(@servername,'DUL','')
SET @body1 = 'SQL Server Agent is Down on '+@servermail+ ' - Please Check'
SET @subject1 = 'SQL Server Agent is Down on ' + @servermail
EXEC msdb.dbo.sp_send_dbmail @recipients=henry@skreebydba.com',
@profile_name = 'DBASQLServerPublicProfile',
@subject = @subject1,
@body = @body1,
@body_format = 'HTML' ;
END 

The final code in the loop deletes the current instance name from the #serverlist table and clears out the #processes temp table.

DELETE FROM #serverlist WHERE servername = @servername
DELETE FROM #processes 

END 

Once the loop completes, the temp tables are cleaned up and the procedure finishes.

 
DROP TABLE #processes
DROP TABLE #serverlist

The combination of dynamic SQL within a loop is an extremely powerful tool. This example provides a template we’ve used for many different tasks in the enviroment. I hope you can put these ideas to use.

USE [DUDMON]
GO

/****** Object: StoredProcedure [dbo].[DUP_CHECK_SQL_AGENT] Script Date: 03/06/2012 11:40:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[DUP_CHECK_SQL_AGENT]
as

/*
DUP_CHECK_SQL_AGENT – Checks to verify that SQL Server Agent is running on all of the PODS servers. Queries the sysprocesses table to see if
the SQL Server Agent process is running. If it is not, an e-mail will be sent.
Parameters: None

Dependent procedures and Tables: None
Created by - Frank Gill
Last Updated by - Frank Gill
Last Updated - 17 May 2010
Version - 1
*/
SET NOCOUNT ON

BEGIN TRY
-- Declare local variables
DECLARE @servername VARCHAR(40)
DECLARE @sqlstr VARCHAR(2000)
DECLARE @totalrows INT
DECLARE @currentrow INT
DECLARE @running INT
DECLARE @rowcount TINYINT

CREATE TABLE #processes
(processlist VARCHAR(4000))

CREATE TABLE #serverlist
(servername VARCHAR(40) not null)

CREATE TABLE #status
(linkedserver VARCHAR(8)
,[status] TINYINT)

set @currentrow = 1
-- Build list of valid linked servers
INSERT #serverlist
SELECT 'DUL' + dpc_sql_cluster_nm
FROM duddba_master.dbo.DUT_POD_SQL_SERVER
WHERE dpc_inuse_ind = 1
AND dpc_ismonitored_ind = 1


SET @totalrows = @@rowcount
-- Begin looping through the list of linked server values
WHILE @currentrow <= @totalrows
BEGIN 
--select @currentrow as CURRENTROW

SET @servername = (SELECT TOP 1 servername FROM #serverlist)

SET @currentrow = @currentrow + 1
SET @sqlstr = @servername + + '...xp_cmdshell ''tasklist'''

SELECT @sqlstr

INSERT INTO #processes
EXEC (@sqlstr)

SELECT @rowcount = COUNT(*) FROM #processes
WHERE processlist LIKE '%sqlagent%'

SELECT @crdate = crdate FROM master.sys.sysdatabases
WHERE name = 'tempdb'

SELECT @startup = DATEADD(minute,-5,getdate())

IF (@rowcount = 0 AND (@sixmin > @crdate))
BEGIN
--SELECT @servername AS [Server name]
--SELECT 'This stuff is broke!!!'
DECLARE @body1 VARCHAR(100)
DECLARE @subject1 VARCHAR(100)
DECLARE @servermail VARCHAR(8)
SET @servermail = REPLACE(@servername,'DUL','')
SET @body1 = 'SQL Server Agent is Down on '+@servermail+ ' - Please Check'
SET @subject1 = 'SQL Server Agent is Down on ' + @servermail
EXEC msdb.dbo.sp_send_dbmail @recipients='fgill@allstate.com',
@profile_name = 'DBASQLServerPublicProfile',
@subject = @subject1,
@body = @body1,
@body_format = 'HTML' ;
END 

DELETE FROM #serverlist WHERE servername = @servername
DELETE FROM #processes 

END 

DROP TABLE #status
DROP TABLE #processes
DROP TABLE #serverlist

END TRY

BEGIN CATCH 

INSERT INTO dbo.DUT_ERROR_LOG
SELECT 
@servername,
@status,
getdate(),
CURRENT_USER AS CurrentUser,
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;

END CATCH
GO

Checking SQL Server Agent Status Using xp_cmdshell

March 6, 2012 1 comment

I work in a clustered environment running more than 200 instances of SQL Server 2008. Several years ago, we began having problems with the SQL Server Agent service failing after an instance failed over. We had already developed a process to check the health of the SQL Server service, so I set out to add an Agent check to it.

Doing some web searching, I came across a number of links that suggested running the following query to determine if SQL Server Agent was running:

SELECT COUNT(*) 
FROM master.dbo.sysprocesses 
WHERE program_name = N'SQLAgent - Generic Refresher'

In the tests I ran, this worked just fine. I put it into a procedure, rolled it out to our test environment and let it run for a week, where we had no issues. It caught a number of times that the Agent was down and provided no false positives. At this point I rolled the proc out to production.

Several weeks later, we started receiving messages that the SQL Agent was down on an instance. Management Studio and Windows Failover Cluster Management showed the process was up and running. We were able to access Agent jobs and metadata, and jobs were running successfully. I queried the dbo.sysprocesses DMO on the instance and did not get a hit for ‘SQL Agent – Generic Refresher’.

This led me to open a ticket with Microsoft to find out if they could tell me why there was no row in the sys.processes DMO when the process was running. If they couldn’t do that, I was hoping they could tell me a better way to check status programmatically. I was told that querying the sys.processes table was not recommended and that they could not provide me with any other suggestions.

At that point, other things took precedence until two weeks ago, when we started receiving messages the Agent was down on an instance. Taking a look at the sys.processes table, there was no row for ‘SQLAgent – Generic Refresher’.

The instance that was failing was excluded from the job and I worked on another method of checking the Agent service. Since the last time I looked at this issue, I gained experience using xp_cmdshell in T-SQL code. This led me to use the following command:

EXEC xp_cmdshell 'tasklist'
GO

This code returns a list of all of the processes running on the machine. By inserting the results of this code into a temp table, you can run a select to check for the existence of the SQL Agent process. That code looks like this:

USE master
GO

CREATE TABLE #processes
(processlist VARCHAR(4000))

INSERT INTO #processes
EXEC xp_cmdshell 'tasklist'

SELECT * FROM #processes
WHERE processlist LIKE '%sqlagent%'

DROP TABLE #processes

Here are the query results:

processlist
SQLAGENT.EXE 9896 Services 0 38,316 K

I’ve put that block of code into a procedure that loops through all of the instances in our environment checking the status of SQL Server Agent. If the check fails, an e-mail is sent out to the DBA team. I will cover this procedure in my next post.