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.