Archive
Determining Estimated Completion Time from sys.dm_exec_requests
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:
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.
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
Calculating IO Deltas Using sys.dm_io_virtual_file_stats
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.
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
Tracking and Calculating Wait Times – Part II
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.
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
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.