Sometimes I want to run a bunch of statements (like updating a bunch of stats or re-indexing a number of indexes) and I want to reduce the chance that these statements will interfere with other processes in the database. I created a script which loops over each statement to be executed and then checks to see if the database is currently in use (has any "Runnable" processes and if it does, waits until all "Runnable" processes have completed, then runs the statement and then starts over with the next statement.

I know that for many this won't be of value, because your systems are too busy for this to make sense and I also know that a new process could start while I'm running one of my statements, so this isn't fool-proof by any means.

-- ========================================================================
-- Author:  Eric Zierdt
-- Create date: 9/20/2016
-- Description: Checks database for running processes and Runs 
--    series of statements when free and emails when complete
-- ========================================================================


/*********************************************************************
 **  CODE TO CREATE THE TABLE TO STORE THE QUERIES YOU WANT TO RUN **
 *********************************************************************/
/*
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..##TABLE'))
BEGIN
 DROP TABLE ##TABLE
END
CREATE TABLE ##TABLE (ID INT IDENTITY(1,1), Code VARCHAR(MAX),Complete BIT DEFAULT 0)
INSERT INTO ##TABLE
( Code )
VALUES
('UPDATE STATISTICS [Table] [Stat] WITH FULLSCAN')
*/
SET NOCOUNT ON

/*****************************************************************************************
 **  CREATING ##StartTime TABLE TO BE USED IN REMAINING TIME ESTIMATES IN OTHER QUERY. **
 **  SETTING TO GLOBAL TEMP TABLE SO ACCESSABLE IN ANOTHER WINDOW      **
 *****************************************************************************************/
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..##StartTime'))
BEGIN
 DROP TABLE ##StartTime
END
CREATE TABLE ##StartTime (StartTime DATETIME)
INSERT INTO ##StartTime VALUES  ( GETDATE() )
PRINT 'Start Time: ' + CONVERT(VARCHAR(25), GETDATE(), 101) + ' ' + CONVERT(VARCHAR(25), GETDATE(), 108)
GO

/*****************************************************************************************
 **  CREATING ##StartTime TABLE TO BE USED IN REMAINING TIME ESTIMATES IN OTHER QUERY. **
 **  SETTING TO GLOBAL TEMP TABLE SO ACCESSABLE IN ANOTHER WINDOW      **
 *****************************************************************************************/
DECLARE @ID INT
  ,@SQL VARCHAR(MAX)
  ,@StartTime DATETIME = GETDATE()
  ,@EndTime DATETIME
WHILE 
(
 SELECT COUNT(1)
 FROM ##TABLE
 WHERE Complete = 0
) >= 1
BEGIN 
 WHILE 
 (
  /**************************************************************
   **  CHECKING TO SEE IF DATABASE HAS NO 'RUNNABLE' PROCESSES **
   **************************************************************/
  SELECT  COUNT(1)
  FROM    sys.sysprocesses S
  WHERE   status = 'runnable'
    AND DB_NAME(S.dbid) = DB_NAME()
    AND S.spid <> @@SPID
 ) > 0   
 BEGIN   
  WAITFOR DELAY '00:00:05';  ---CHANGE TO MEET YOUR NEEDS; THIS IS SAYING, RETRY EVERY 5 SECONDS TO SEE IF DATABASE IS FREE TO DO WORK
 END
 /*************************************************
  ** NO 'RUNNING' PROCESSES DETECTED, RUN QUERY **
  *************************************************/
 SELECT TOP 1 @ID=ID, @SQL=Code FROM ##TABLE WHERE Complete = 0
 EXEC(@SQL)
 UPDATE ##TABLE SET Complete = 1 WHERE ID = @ID
END

/*********************************************
 ** SET END TIME AND PRINT RUN TIME STATS **
 *********************************************/
SET @EndTime = GETDATE()
PRINT 'End Time: ' + CONVERT(VARCHAR(25), @EndTime, 101) + ' ' + CONVERT(VARCHAR(25), @EndTime, 108)
PRINT 'Run Time: ' + CONVERT(VARCHAR, DATEADD(ms,DATEDIFF(ms,@StartTime,@EndTime),0),108)

/*************
 ** CLEANUP **
 *************/
DROP TABLE ##TABLE
DROP TABLE ##StartTime

/*********************************************
 ** SEND EMAIL NOTIFICATION OF COMPLETION **
 *********************************************/
DECLARE @buffer_memory_used_MB FLOAT
  ,@Body VARCHAR(MAX)
  ,@Subject VARCHAR(255) = 'Auto Run Script Complete'
  ,@To VARCHAR(512) = ''
SET @Body = '
The script has completed processing queries.
Run Time: ' + CONVERT(VARCHAR, DATEADD(ms,DATEDIFF(ms,@StartTime,@EndTime),0),108) + ' '

EXEC msdb.dbo.sp_send_dbmail 
   @recipients = @To
 , @subject = @Subject
 , @body = @Body 
 , @body_format = 'HTML'

In another window I will often run this on occasion to get some stats and an idea of how long it will run for:

DECLARE @Completed FLOAT
  ,@CompletedStatements FLOAT
  ,@Total FLOAT
  ,@TotalStatements FLOAT
  ,@PercentComplete FLOAT 
  ,@StartDate DATETIME

SELECT @StartDate=StartTime FROM ##StartTime
DECLARE @RunTime BIGINT = DATEDIFF(SECOND,@StartDate,GETDATE())

SELECT @CompletedStatements = COUNT(1) FROM ##Table WHERE fixed = 1
SELECT @TotalStatements = COUNT(1) FROM ##Table
SELECT @Completed = SUM([RowCount]) FROM ##Table WHERE fixed = 1
SELECT @Total = SUM([RowCount]) FROM ##Table
SELECT @PercentComplete = 100*(@Completed/@Total)
DECLARE @SecondPerRecord FLOAT = CAST(@RunTime AS FLOAT)/@Completed
DECLARE @EstRemainingSeconds BIGINT = @SecondPerRecord*(@Total-@Completed)
SELECT @Total TotalRecords, @Completed CompletedRecords, @TotalStatements as TotalStatements, @CompletedStatements as CompletedStatements, @PercentComplete [% Complete], @RunTime RunTimeSeconds, @SecondPerRecord SecondPerRecord, @EstRemainingSeconds EstRemainingSeconds
  ,@EstRemainingSeconds/60.0 EstRemainingMins, @EstRemainingSeconds/3600.0 EstRemainingHours

Please comment below and let me know if you found value in this post.

Eric

Get Fast Rowcount

I came up with this little gem a while ago, I use it when I'm trying to get a rowcount on a large table, where it's so large that it takes a long time for a simple SELECT COUNT(1) FROM xxx to return data.

DECLARE @TableName sysname
SET @TableName = 'TableName'

SELECT SUM(row_count) AS [RowCount]
FROM sys.dm_db_partition_stats
WHERE object_id=OBJECT_ID(@TableName)   
AND (index_id=0 or index_id=1);

Script - Email on Job Completion

Sometimes I have long running agent jobs (like say a backup job) and I don't want to do work on the server while the specific job is running; so I've come up with a quick and easy script using the sp_send_dbmail ability to monitor for the job completion and then email me. I've done similar things with some manual tasks that take a while (index reindex or update stats) so I am notified when the script completes.

-- ===============================================
-- Author:  Eric Zierdt
-- Create date: 3/3/2016
-- Description: Emails upon agent job completion
-- ===============================================
DECLARE @JOB_NAME SYSNAME = N'AdventureWorks Backup'; -- NAME OF AGENT JOB
 
WHILE EXISTS
(     
 SELECT  1
 FROM    msdb.dbo.sysjobs_view job
 JOIN msdb.dbo.sysjobactivity activity ON job.job_id = activity.job_id
 JOIN msdb.dbo.syssessions session ON session.session_id = activity.session_id
 JOIN ( 
    SELECT   MAX(agent_start_date) AS max_agent_start_date
    FROM     msdb.dbo.syssessions
   ) session_max ON session.agent_start_date = session_max.max_agent_start_date
 WHERE   activity.run_Requested_date IS NOT NULL
   AND activity.stop_execution_date IS NULL
   AND job.name = @JOB_NAME 
   --AND activity.start_execution_date > CAST(GETDATE() AS DATE)
) 
BEGIN      
     WAITFOR DELAY '00:00:45'; -- SET HOW OFTEN YOU WANT YOUR JOB TO CHECK
END 

DECLARE @buffer_memory_used_MB FLOAT
  ,@Body VARCHAR(MAX)
  ,@Subject VARCHAR(255) = 'Job Complete'
  ,@To VARCHAR(512) = 'user@email.com' -- INSERT YOUR EMAIL ADDRESS HERE
SET @Body = '
The monitored job has completed running.'

EXEC msdb.dbo.sp_send_dbmail 
   @recipients = @To
 , @subject = @Subject
 , @body = @Body 
 , @body_format = 'HTML'