Archive

Archive for the ‘SQL Server Agent’ Category

Tracking and Calculating Wait Times – Part I

June 20, 2012 1 comment

Several weeks ago, I took a call from a vended application investigating slowness that occurred on a regular basis on Wednesdays. Members of my team had looked at the performance of the app from a SQL Server perspective during the slow period and could not find anything out of line. The app had also worked with the Windows, Storage and Network teams, and no red flags were found. The vendor had asked if we could provide information about SQL Server wait times.

The data they were interested in is available from sys.dm_os_waiting_tasks, but the wait time values are cumulative so the raw values are not very useful. Fortunately, one of my former co-workers (I owe him a beer) set up a process that created the table scripted below:

USE [Your Database Name]
GO

/****** Object:  Table [dbo].[Your Table Name]    Script Date: 06/18/2012 21:27:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Your Table Name]
([WAT_WAIT_TYPE_NM] [nvarchar](120) NOT NULL,
 [WAT_WAITING_TASKS_NBR] [bigint] NOT NULL,
 [WAT_WAIT_TM_MS_NBR] [bigint] NOT NULL,
 [WAT_MAX_WAIT_TIME_MS_NBR] [bigint] NOT NULL,
 [WAT_SIGNAL_WAIT_TIME_MS_NBR] [bigint] NOT NULL,
 [WAT_CAPTURE_DT] [datetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Your Table Name] ADD  DEFAULT (getdate()) FOR [WAT_CAPTURE_DT]
GO

The process runs the following stored procedure hourly from an agent job:

USE [Your Database Name]
GO
/****** Object:  StoredProcedure [dbo].[Your Procedure Name]    Script Date: 06/18/2012 21:28:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Your Procedure Name]
AS
SET NOCOUNT ON ;

        INSERT INTO [dbo].[DUT_WAIT_STATS] (
                        WAT_WAIT_TYPE_NM,
                        WAT_WAITING_TASKS_NBR,
                        WAT_WAIT_TM_MS_NBR,
                        WAT_MAX_WAIT_TIME_MS_NBR,
                        WAT_SIGNAL_WAIT_TIME_MS_NBR,
                        WAT_CAPTURE_DT)

		SELECT  [wait_type],
		        [waiting_tasks_count],
				[wait_time_ms],
                [max_wait_time_ms],
		        [signal_wait_time_ms],
				GETDATE()
		FROM sys.dm_os_wait_stats

GO

I would recommend using a process like this to provide the ability to troubleshoot wait times historically as it is simple to set up and is very lightweight. We are storing 6 months’ worth of data and the table is less than 4mb in size.

This gave me the data I was looking for, but because the wait times are cumulative I still needed to calculate the deltas. I will cover the process I put together in my next post.

Reading the xp_readerrorlog Using a WHILE Loop

April 13, 2012 3 comments

Last week, I needed to search through all of the SQL Server error logs on an instance. I have used xp_readerrorlog before, using the information found here. We retain 99 log files, which is the maximum number of files that can be kept. I needed to search through all of these files, so I put together the following script. This is another example of using a WHILE loop in gathering information from SQL Server.

USE master
GO

-- Create temp table to hold the output of the error logs

CREATE TABLE #errorlog
(errdate DATETIME
,errproc SYSNAME
,errtext VARCHAR(4000))

-- Declare and initialize the @loopcount variables

DECLARE @loopcount INT
SET @loopcount = 1

-- Loop through the error logs
-- We keep the 99 logs, which the maximum
-- Change the loop limit accordingly

WHILE @loopcount <= 99
BEGIN

	-- Insert the results of xp_readerrorlog into the temp table
	
	INSERT INTO #errorlog
	
	-- You can find the parameters for xp_readerrorlog at the following link
	-- http://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/
	-- The parms I use below are as follows:
	-- @loopcount - file number, with 0 being the current file
	-- 1 - indicates the SQL error log - a 2 would query the SQL Agent log
	-- 'DELETE' - a search argument
	
	EXEC xp_readerrorlog @loopcount, 1, 'DELETE'
	
	-- Increment your loop counter
	
	SET @loopcount = @loopcount + 1
END

-- Select the results of your loop
-- Alternately, you can dump the entire log file and then use WHERE predicates to 
-- select what you are interested in

SELECT * FROM #errorlog

-- Clean up your temp table
DROP TABLE #errorlog

T-SQL Tuesday – Extended Events in SQL Server 2012

April 9, 2012 Leave a comment

I started as a SQL Server DBA in November of 2007 and attended my first PASS Summit in 2008. While there, I saw Jerome Halmans present on Advanced Troubleshooting with SQL Server Extended Events. It was one of my favorite presentations of the summit and I wanted to start using Extended Events in my environment. However, every time I tried to use them, the overhead of getting them set up was greater than their usefulness. I found myself using other troubleshooting tools that I was more familiar with.

Despite these difficulties, I remained interested in using Extended Events. Several months ago, I downloaded Jonathan Kehayias’ ( blog | twitter) SQL Server 2008 Extended Events SSMS Addin but couldn’t get any momentum going.

That brings me to my most useful feature in SQL Server 2012. This week’s Brent Ozar PLF newsletter contained a link to Bob Dorr’s article on SQL Server 2012: RML, XEvent Viewer and Distributed Replay. I just started playing with Extended Events in 2012 today and I am really excited about it.

They are now integrated into Management Studio, removing the manual scripting that was necessary to use them previously. The interface looks and feels a lot like SQL Profiler, but Extended Events has much less overhead than a Profiler trace. And they are one of Thomas LaRock’s ( blog | twitter ) top 3 things you should learn in SQL Server 2012.

I will be at the SQL Skills immersion event on internals and performance next week, but Extended Events is on the top of my list when I get back.

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.