Script To Monitor Status Of Update Statistics Operation

I tried to write a script to monitor the progress of update statistics operation. .

This script receives the SPID of the connection running this operation. It has two versions for SQL 2000 & 2005. In my box I found script for SQL 2000 runs fine for SQL 2005.

/*************************************************

*                INSTRUCTION                     *

**************************************************

1. Please open a new query analyzer window to run update statistics with full scan.

      sp_MSForEachTable 'Update Statistics ? WITH FULLSCAN'

2. Please note the associated spid with that query analyzer window from the bottom right. This spid will

      appear along with your log in name.

3. Now, please open another Query analyzer window and make sure you are connected to the SAME DATABASE that

      you are going to update the statistics.

4. Paste the script given below and create the stored procedure Stat_Prog_2000 or Stat_Prog_2005 as per the

      SQL SERVER Version.

5. Now you can pass the spid (from the query window opened in step 1) to the stored procedure to monitor the

      progress. This stored procedure will print the progress (in %) at that point of time.

*/

--------------------------------------------------

/* SQL SERVER 2005 */

use database_name

go

if(object_id('stat_prog_2005') is not null) drop procedure stat_prog_2005

go

create procedure stat_prog_2005

@spid int

as

declare @starttime as datetime;

--declare @spid as int;

--set @spid=54;

set @starttime= (select top 1 last_batch from master..sysprocesses where spid=@spid);

if(@starttime is null)

begin

      print 'Invalid spid. please check at the bottom right side of the query analyzer window running update statistics statement for correct spid.';

      return;

end

print 'SQL SERVER 2005: This script will show the progress of the update statistics with full scan for this database only.';

declare @kpid as bigint;

set @kpid = (select top 1 kpid from master..sysprocesses where spid=@spid)

if(@kpid =0)

begin

      print 'Session completed or not running.';

      return;

end

declare @blocked int;

set @blocked = (select count(*) from sys.sysprocesses where spid=@spid and blocked <>0 and blocked<>@spid)

if(@blocked <>0)

begin

      print replicate('*',60)

      print 'This supplied spid (' + convert(varchar(10),@spid) + ') is blocked.'

      select 'Blocked by spid = ' + convert(varchar(10),spid) + ', Login Time: ' + convert(varchar(30),login_time) +

      ', Program Name: ' + rtrim(program_name) + ', Login Name: ' + rtrim(loginame) from sys.sysprocesses  where spid

      in (select blocked from sys.sysprocesses where spid=@spid and blocked <>0 and blocked<>@spid)

      print replicate('*',60)

      return;

end

declare @totalstat as int

set @totalstat = (

select

      count(*)

from

      sys.stats, sys.objects

where

      stats_date(sys.stats.object_id,sys.stats.stats_id) is not null

      and sys.stats.object_id=sys.objects.object_id

      and sys.objects.type='u' );

declare @completecount as bigint;

declare @prevval as bigint;

set @completecount=(

      select count(*)

      from

            sys.stats, sys.objects

      where

            stats_date(sys.stats.object_id,sys.stats.stats_id) is not null

            and stats_date(sys.stats.object_id,sys.stats.stats_id) >= @starttime

            and sys.stats.object_id=sys.objects.object_id

            and sys.objects.type='u' );

print convert(varchar(10), (@completecount * 100)/@totalstat) + ' % statistics update complete.';

go

--EXEC stat_prog_2005 52

--------------------------------------------------------------------

--------------------------------------------------------------------

/* SQL SERVER 2000 */

use pubs

go

if(object_id('stat_prog_2000') is not null) drop procedure stat_prog_2000

go

create procedure stat_prog_2000 

@spid int

as

declare @starttime as datetime;

set @starttime= (select top 1 last_batch from master..sysprocesses where spid=@spid);

if(@starttime is null)

begin

      print 'Invalid spid. please check at the bottom right side of the query analyzer window along with your login name running update statistics statement for correct spid.';

      return;

end

print 'SQL SERVER 2000: This script will show the progress of the update statistics with full scan for this database only.';

declare @kpid as bigint;

set @kpid = (select top 1 kpid from master..sysprocesses where spid=@spid)

if(@kpid =0)

begin

      print 'Session completed or not running.';

      return;

end

declare @blocked int;

set @blocked = (select count(*) from master..sysprocesses where spid=@spid and blocked <>0 and blocked<>@spid)

if(@blocked <>0)

begin

      print replicate('*',60)

      print 'This supplied spid (' + convert(varchar(10),@spid) + ') is blocked.'

      select 'Blocked by spid = ' + convert(varchar(10),spid) + ', Login Time: ' + convert(varchar(30),login_time) +

      ', Program Name: ' + rtrim(program_name) + ', Login Name: ' + rtrim(loginame) from master..sysprocesses  where spid

      in (select blocked from sys.sysprocesses where spid=@spid and blocked <>0 and blocked<>@spid)

      print replicate('*',60)

      return;

end

declare @totalstat as int

set @totalstat = (

select count(*) from sysindexes, sysobjects

where stats_date(sysindexes.id, sysindexes.indid ) is not null

and sysindexes.id=sysobjects.id

and sysobjects.type='u');

declare @completecount as bigint;

declare @prevval as bigint;

set @completecount=(

      select count(*) from sysindexes, sysobjects

where stats_date(sysindexes.id, sysindexes.indid ) is not null

and stats_date(sysindexes.id, sysindexes.indid ) >= @starttime

and sysindexes.id=sysobjects.id

and sysobjects.type='u');

print convert(varchar(10), (@completecount * 100)/@totalstat) + ' % statistics update complete.';

go

--exec stat_prog_2000 54