Archive

Posts Tagged ‘DMO’

Determining Estimated Completion Time from sys.dm_exec_requests

November 21, 2012 2 comments

According to Books Online, the following list of commands will generate a percent complete and an estimated completion time in the sys.dm_exec_requests DMO:

Percent Complete Commands

You can use this to determine approximately how long a backup, restore or DBCC command will run. This query will get you a nicely formatted time value.

[sourcecode language=”SQL”]
USE master
GO

SELECT
CASE
WHEN estimated_completion_time < 36000000
THEN ‘0’
ELSE ”
END
+ RTRIM(estimated_completion_time/1000/3600)
+ ‘:’ + RIGHT(‘0’ + RTRIM((estimated_completion_time/1000)%3600/60), 2)
+ ‘:’ + RIGHT(‘0’ + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining],
percent_complete,
*
FROM sys.dm_exec_requests
WHERE percent_complete > 0
[/sourcecode]

Categories: DMO, , T-SQL, Uncategorized

Calculating IO Deltas Using sys.dm_io_virtual_file_stats

August 2, 2012 Leave a comment

In a previous post, I showed how to calculate deltas for cumulative values from sys.dm_os_performance_counters. At the end of that post, I mentioned that the counter values are reset when the instance restarts, which can result in negative delta values. For the purposes of that script I did not care about negative values.

However, we are using cumulative values from sys.dm_io_virtual_file_stats to generate a report in Reporting Services, and the negative values are mucking up our graphs. The script I used to solve the problem is listed below. As usual, I hope you can put the ideas contained to use.

[sourcecode language=”SQL” wraplines=”false”]
USE DUDDBA_MASTER
GO

— Declare variables and create temp tables
DECLARE @sqlstr NVARCHAR(4000)
DECLARE @servername VARCHAR(40)
DECLARE @totalrows INT
DECLARE @currentrow INT
DECLARE @status INT
DECLARE @oldreadcnt BIGINT
DECLARE @newreadcnt BIGINT
DECLARE @newdate DATETIME
DECLARE @olddate DATETIME
DECLARE @mindate DATETIME
DECLARE @instancename VARCHAR(40)
DECLARE @tempstartup DATETIME
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);
DECLARE @startupdateout DATETIME

CREATE TABLE #serverlist
(servername VARCHAR(40) NOT NULL)

CREATE TABLE #IODATA
(instancename VARCHAR(8)
,databasename VARCHAR(100)
,runtime DATETIME
,readcount BIGINT
,bytesread BIGINT
,writecount BIGINT
,byteswritten BIGINT)

CREATE TABLE #IODATANEW
(databasename VARCHAR(100)
,runtime DATETIME
,readcount BIGINT
,bytesread BIGINT
,writecount BIGINT
,byteswritten BIGINT)

CREATE TABLE #IODATAOLD
(databasename VARCHAR(100)
,runtime DATETIME
,readcount BIGINT
,bytesread BIGINT
,writecount BIGINT
,byteswritten BIGINT)

— Initialize the @newdate and @olddate variables for the inner loop
SET @newdate = CONVERT(DATE,GETDATE())
SET @olddate = DATEADD(DD,-1,@newdate)

— Build a list of linked servers for the outer loop, excluding non-2008 instances
INSERT INTO #serverlist
SELECT ‘DUL’ + s1.DPC_SQL_CLUSTER_NM
FROM DUT_POD_SQL_SERVER s1
INNER JOIN DUT_POD_SQL_VERSION s2
ON s1.DPC_SQL_CLUSTER_NM = s2.SVR_HOST_NM
WHERE s2.SVR_SQL_VERSION_TXT LIKE ‘%10.%’

— Store the @@ROWCOUNT value from the insert statement to control the outer loop
SET @totalrows = @@ROWCOUNT
SET @currentrow = 1

— Execute the outer loop once for each linked server
WHILE @currentrow <= @totalrows
BEGIN

— Pull the first linked server name from the table
SET @servername = (SELECT TOP 1 servername FROM #serverlist)

— Run the inner loop from the current date until 2012-01-01
WHILE @olddate > ‘2012-01-01’
BEGIN
— Build the dynamic sql string
SET @sqlstr =

— The DUT_FILE_IO table contains cumulative IO metrics from sys.dm_io_virtual_file_stats and is populated every 5 minutes
— The statement below pulls the values for the current day at midnight into #IODATANEW excluding non-user databases
INSERT INTO #IODATANEW
SELECT FIO_DATABASE_NM
,FIO_RUN_TS
,SUM(FIO_NBR_READS_CNT) AS [Total Reads]
,SUM(FIO_BYTES_READ_CNT) AS [Total Bytes Read]
,SUM(FIO_NBR_WRITES_CNT) AS [Total Writes]
,SUM(FIO_BYTES_WRITE_CNT) AS [Total Bytes Written]
FROM ‘ + @servername + ‘.duddba.dbo.DUT_FILE_IO
WHERE DATEPART(HH,FIO_RUN_TS) = 00
AND DATEPART(MI,FIO_RUN_TS) = 00
AND DATEPART(SS,FIO_RUN_TS) = 00
AND CONVERT(DATE,FIO_RUN_TS) = ‘ + ”” + CONVERT(VARCHAR(30),@newdate,120) + ”” +

AND FIO_DATABASE_NM NOT IN (”DUDDBA”,”DUDREPO”,”DUDDUMMY”,”DUDTRACE”,”master”,”model”,”msdb”)
GROUP BY FIO_DATABASE_NM, FIO_RUN_TS
ORDER BY FIO_RUN_TS DESC, FIO_DATABASE_NM

— The statement below pulls the values for the previous day at midnight into #IODATAOLD excluding non-user databases
INSERT INTO #IODATAOLD
SELECT FIO_DATABASE_NM
,FIO_RUN_TS
,SUM(FIO_NBR_READS_CNT) AS [Total Reads]
,SUM(FIO_BYTES_READ_CNT) AS [Total Bytes Read]
,SUM(FIO_NBR_WRITES_CNT) AS [Total Writes]
,SUM(FIO_BYTES_WRITE_CNT) AS [Total Bytes Written]
FROM ‘ + @servername + ‘.duddba.dbo.DUT_FILE_IO
WHERE DATEPART(HH,FIO_RUN_TS) = 00
AND DATEPART(MI,FIO_RUN_TS) = 00
AND DATEPART(SS,FIO_RUN_TS) = 00
AND CONVERT(DATE,FIO_RUN_TS) = ‘ + ”” + CONVERT(VARCHAR(30),@olddate,120) + ”” +

AND FIO_DATABASE_NM NOT IN (”DUDDBA”,”DUDREPO”,”DUDDUMMY”,”DUDTRACE”,”master”,”model”,”msdb”)
GROUP BY FIO_DATABASE_NM, FIO_RUN_TS
ORDER BY FIO_RUN_TS DESC, FIO_DATABASE_NM

— The insert statement below calculates the delta values for the IO metrics collected
— The series of CASE statements accounts for the possibility of a negative value
— If the delta value is positive, it is used
— If it is negative, the raw value is used
— This is not the ideal solution, because it does not account for the cumulative values
— prior to the restart but it is good enough for the purposes of the reports we are running

INSERT INTO #IODATA
SELECT ‘ + ”” + SUBSTRING(@servername,4,8) + ”” +’,n.databasename, n.runtime,
CASE
WHEN ((n.readcount – o.readcount) > 0)
THEN
(n.readcount – o.readcount)
ELSE
n.readcount
END,
CASE
WHEN ((n.bytesread – o.bytesread)/1045876 > 0)
THEN
(n.bytesread – o.bytesread)
ELSE
n.bytesread
END,
CASE
WHEN ((n.writecount – o.writecount) > 0)
THEN
(n.writecount – o.writecount)
ELSE
n.writecount
END,
CASE
WHEN ((n.byteswritten – o.byteswritten)/1045876 > 0)
THEN
(n.byteswritten – o.byteswritten)/1045876
ELSE
n.byteswritten
END
FROM #IODATANEW n
INNER JOIN #IODATAOLD o
ON n.runtime = ‘ + ”” + CONVERT(VARCHAR(30),@newdate,120) + ”” +
‘AND n.databasename = o.databasename
WHERE o.runtime = ‘ + ”” + CONVERT(VARCHAR(30),@olddate,120) + ”” +

— Execute the dynamic SQL string
EXEC(@sqlstr)

–Reset the new and old date variables
SET @newdate = @olddate
SET @olddate = DATEADD(DD,-1,@olddate)

— Clear out the new and old temp tables
DELETE FROM #IODATANEW
DELETE FROM #IODATAOLD

END

— Insert the results for each linked server into the permanent table
INSERT INTO DUT_FILE_IO_DELTA
SELECT * FROM #IODATA

— Delete the current linked server from the #serverlist table
DELETE FROM #serverlist WHERE servername = @servername

— Increment the loop counter
SET @currentrow = @currentrow + 1

— Clear out the #IODATA table
DELETE FROM #IODATA

— Decrement the new and old date variables
SET @newdate = CONVERT(DATE,GETDATE())
SET @olddate = DATEADD(DD,-1,@newdate)

END

— Clean up the temp tables at the end of the script
DROP TABLE #serverlist
DROP TABLE #IODATA
DROP TABLE #IODATAOLD
DROP TABLE #IODATANEW

[/sourcecode]

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

Tracking and Calculating Wait Times – Part II

July 3, 2012 1 comment

In my last post, I described the process we use to collect wait information on our systems. Because SQL Server stores cumulative values, the raw wait time data is not very useful. The following script shows how to pull daily data and calculate the deltas.

This script was written for a vended application that has been experiencing slow response times. The vendor asked for wait time information to see if there was correlation between increased wait times and the slow down.

[sourcecode language=”SQL” wraplines=”false”]
USE [Your Database Name]
GO

— Create the necessary temp tables and variables

CREATE TABLE #waits
(waittype VARCHAR(100)
,waitdate DATETIME
,waittime BIGINT)

CREATE TABLE #waitsnew
(waittypenew VARCHAR(100)
,waitdatenew DATETIME
,waittimenew BIGINT)

CREATE TABLE #waitsold
(waittypeold VARCHAR(100)
,waitdateold DATETIME
,waittimeold BIGINT)

CREATE TABLE #waitsdelta
(waittypedelta VARCHAR(100)
,waitdatedelta DATETIME
,waittimedelta BIGINT)

DECLARE @olddate DATETIME
DECLARE @newdate DATETIME
DECLARE @totalcount INT
DECLARE @oldcount INT
DECLARE @newcount INT
DECLARE @loopcount INT

— initialize the @loopcount variable
SET @loopcount = 1

— populate the #waits temp table with data for the date range desired
— DATEDIFF(dd, 0, WAT_CAPTURE_DT)) returns the date without the time
— because the wait time value is cumulative, the max value will be the
— total count for the day
— exclude system waits using WHERE clause from Jonathan Kehayias’ blog post
— http://www.simple-talk.com/sql/performance/a-performance-troubleshooting-methodology-for-sql-server/

/****** Script for SelectTopNRows command from SSMS ******/
INSERT INTO #waits
SELECT WAT_WAIT_TYPE_NM, DATEADD(dd, 0, DATEDIFF(dd, 0, WAT_CAPTURE_DT)), MAX(WAT_WAIT_TM_MS_NBR) FROM [Your Database Name].[dbo].[Your Table Name]
WHERE WAT_CAPTURE_DT > ‘2012-06-01’
AND WAT_WAIT_TYPE_NM NOT IN — filter out additional irrelevant waits
( ‘SLEEP_TASK’, ‘BROKER_TASK_STOP’, ‘BROKER_TO_FLUSH’,
‘SQLTRACE_BUFFER_FLUSH’,’CLR_AUTO_EVENT’, ‘CLR_MANUAL_EVENT’,
‘LAZYWRITER_SLEEP’, ‘SLEEP_SYSTEMTASK’, ‘SLEEP_BPOOL_FLUSH’,
‘BROKER_EVENTHANDLER’, ‘XE_DISPATCHER_WAIT’, ‘FT_IFTSHC_MUTEX’,
‘CHECKPOINT_QUEUE’, ‘FT_IFTS_SCHEDULER_IDLE_WAIT’,
‘BROKER_TRANSMITTER’, ‘FT_IFTSHC_MUTEX’, ‘KSOURCE_WAKEUP’,
‘LAZYWRITER_SLEEP’, ‘LOGMGR_QUEUE’, ‘ONDEMAND_TASK_QUEUE’,
‘REQUEST_FOR_DEADLOCK_SEARCH’, ‘XE_TIMER_EVENT’, ‘BAD_PAGE_PROCESS’,
‘DBMIRROR_EVENTS_QUEUE’, ‘BROKER_RECEIVE_WAITFOR’,
‘PREEMPTIVE_OS_GETPROCADDRESS’, ‘PREEMPTIVE_OS_AUTHENTICATIONOPS’,
‘WAITFOR’, ‘DISPATCHER_QUEUE_SEMAPHORE’, ‘XE_DISPATCHER_JOIN’,
‘RESOURCE_QUEUE’ )
AND WAT_WAIT_TM_MS_NBR > 0
GROUP BY WAT_WAIT_TYPE_NM, DATEADD(dd, 0, DATEDIFF(dd, 0, WAT_CAPTURE_DT))
ORDER BY 2, 3 DESC

— set the loop limit equal to the number of rows returned above
SET @totalcount = @@ROWCOUNT

— run a loop to calculate the delta value for each day
WHILE @loopcount <= @totalcount
BEGIN

— select the minimum date from the #waits table
SELECT @olddate = MIN(waitdate)
FROM #waits

— select the next date from the #waits table
SET @newdate = DATEADD(dd,1,@olddate)

— insert the row for the old date into #waitsold
INSERT INTO #waitsold
SELECT * FROM #waits
WHERE waitdate = @olddate

— set @oldcount equal the row count
SET @oldcount = @@ROWCOUNT

— insert the row for the new date into #waits
INSERT INTO #waitsnew
SELECT * FROM #waits
WHERE waitdate = @newdate

— join #waitsnew and #waitsold, inserting the wait type, date
— and delta between the new and old times into #waitsdelta
INSERT INTO #waitsdelta
SELECT n.waittypenew, n.waitdatenew, (n.waittimenew – o.waittimeold)
FROM #waitsnew n
INNER JOIN #waitsold o
ON n.waitdatenew = @newdate
AND n.waittypenew = o.waittypeold
WHERE o.waitdateold = @olddate

— delete the old data from #waits
DELETE FROM #waits
WHERE waitdate = @olddate

— reset the old and new dates to the next pair of values
SET @olddate = @newdate
SET @newdate = DATEADD(DD,1,@newdate)

— increment the @loopcount
SET @loopcount = @loopcount + @oldcount

END

— select the delta values from #waitsdelta
SELECT waittypedelta, waitdatedelta, (waittimedelta) AS waittimedelta
FROM #waitsdelta
ORDER BY waittypedelta, waitdatedelta

— clean up temp tables
DROP TABLE #waits
DROP TABLE #waitsold
DROP TABLE #waitsnew
DROP TABLE #waitsdelta
[/sourcecode]

One thing to keep in mind is that the wait time values are zeroes out when the instance restarts. So if the instance restarts, you will end up with negative values for your wait times. Because I am running this as an ad hoc query, I can work around this possibility. If you wanted to automate the process, I would suggest taking this into consideration.

I learned a lot working on this problem, and I hope someone out there can make use of it.

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