SQLScholar
Programmer
Hi all,
This keeps on coming up on profiler - using quite abit of time.
Few questions from a Profiler novice.
Firstly - what is it?
Secondly - why does it talk about replication? There is no replication currently on this server? It has been tested on this server in the past though.
Lastly - why does it take so long?
Many thanks
Dan
----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss
Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------
This keeps on coming up on profiler - using quite abit of time.
Code:
SET LOCK_TIMEOUT 20000 SET IMPLICIT_TRANSACTIONS OFF if @@TRANCOUNT > 0 COMMIT TRANSACTION
SET Language us_english
SET ANSI_WARNINGS OFF
SET CURSOR_CLOSE_ON_COMMIT OFF
SET QUERY_GOVERNOR_COST_LIMIT 0
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
Select value, @@max_connections from master..syscurconfigs where config = 103 if @@rowcount = 0 select 0, @@max_connections select -1 Select value from master..syscurconfigs where config = 1535 if @@rowcount = 0 select 0
if (select isnull(object_id('tempdb..#CPU_Monitor'), 0)) = 0 begin Create table #CPU_Monitor (spid int, cpu_time int) insert into #CPU_Monitor select spid, cpu from master..sysprocesses where cpu > 0 end
if (select isnull(object_id('tempdb..#CPU_Processes'), 0)) = 0 begin Create table #CPU_Processes (spid int, cpu_time int, program_name varchar(255), hostname varchar(255), username varchar(255)) end
if (select isnull(object_id('tempdb..#processcpu'), 0)) = 0 begin create table #processcpu (spid smallint, cpu int) insert into #processcpu select spid, cpu from master..sysprocesses where cpu > 0 end
if (select isnull(object_id('tempdb..#already_reported'), 0)) = 0 begin CREATE TABLE #already_reported (job_id UNIQUEIDENTIFIER NOT NULL, job_name sysname not null, current_running_step INT NOT NULL, current_retry_attempt INT NOT NULL,no_of_seconds_running int not null, started_at datetime NULL, this_entry_datetime datetime not null) end
if (select isnull(object_id('tempdb..#TableDetails'), 0)) = 0 begin Create table #TableDetails (DBID smallint, DBName sysname, TableName varchar(128), TableID int, DataSizeKB int, IndexSizeKB int, TextSizeKB int, IndexID int, NumRows int, TableType char(2), TableOwner varchar(128))
end
select cast((@@cpu_busy % 2147483647) as int), @@idle, @@io_busy, @@pack_received, @@pack_sent, @@packet_errors, @@total_read, @@total_write, @@total_errors, @@connections
select 'sysProcessDetails' select (select count(*) from master..sysprocesses p where p.cpu > 0 and not exists (select * from #processcpu t where t.spid = p.spid and p.cpu <= t.cpu)), sum(case when blocked > 0 then 1 else 0 end), sum(case when open_tran > 0 then 1 else 0 end), sum(case when ISNULL(net_address, '') <> '' then 1 else 0 end), sum(case when ISNULL(net_address, '') = '' then 1 else 0 end), count(distinct(net_address)) - 1 from master..sysprocesses
select 'start io stats'
declare @command varchar(2048), @spid int, @program_name varchar(255), @hostname varchar(255), @username varchar(255), @dbname varchar(255), @tabid int, @lock_type smallint
select 'start blocking check' declare extract_too_much_block insensitive cursor for select p.spid, p.program_name, p.hostname, p.loginame, replace(db_name(ISNULL(convert(int,l.rsc_dbid),-1)),char(39),char(39)+char(39)), ISNULL(l.rsc_objid,-1), ISNULL(convert(int,l.req_mode),-1) from master..sysprocesses p (nolock) FULL JOIN master..sysprocesses b (nolock) on p.spid = b.blocked LEFT OUTER JOIN master..syslockinfo l (nolock) on b.spid = l.req_spid and l.req_status in (2,3) where p.blocked = 0 and p.program_name not like 'SQLAgent%' and p.program_name not in ('DiagnosticMan', 'SQL PerfMon') and b.blocked <> 0 and b.waittime > 150000 for read only Open extract_too_much_block fetch next from extract_too_much_block into @spid, @program_name, @hostname, @username, @dbname, @tabid, @lock_type while @@fetch_status = 0 begin select @command = 'select ''blocking info'', ''' + convert(varchar(5), @spid) + ''',''' + rtrim(@program_name) + ''',''' + rtrim(@hostname) + ''',''' + rtrim(@username) + ''',''' + rtrim(@dbname) + ''',''' + convert(varchar(20), @tabid) + ''',''' + convert(varchar(5), @lock_type) + ''' dbcc inputbuffer(' + convert(varchar(5),@spid) + ')' exec (@command) fetch next from extract_too_much_block into @spid, @program_name, @hostname, @username, @dbname, @tabid, @lock_type End Close extract_too_much_block deallocate extract_too_much_block
select 'start DB check' truncate table #TableDetails
declare @dbid smallint, @mode smallint, @status integer, @status2 integer, @log_size varchar(20), @total_db_size varchar(20), @cmptlevel int, @category int, @dumptrdate varchar(18)
if (select isnull(object_id('tempdb..#disk_drives'), 0)) = 0 begin create table #disk_drives (drive_letter char(1), unused_size int) insert into #disk_drives exec master..xp_fixeddrives end
if (select isnull(object_id('tempdb..#intermediate_sysfiles'), 0)) = 0 create table #intermediate_sysfiles (driveletter char(1), filetypeflag tinyint, maxsize integer, size integer, growth integer) else truncate table #intermediate_sysfiles
if (select isnull(object_id('tempdb..#grouped_devices'), 0)) = 0 create table #grouped_devices (driveletter char(1), filetypeflag tinyint, maxsize integer, size integer, growth integer, expansion dec(12,0)) else truncate table #grouped_devices
declare read_db_status insensitive cursor for select name, dbid, mode, isnull(convert(integer,status),-999) from master..sysdatabases d (nolock) for read only
set nocount on open read_db_status fetch read_db_status into @dbname, @dbid, @mode, @status while @@fetch_status = 0 begin if HAS_DBACCESS (@dbname) = 1 and @mode = 0 and DATABASEPROPERTY(@dbname, 'IsInLoad') = 0 and DATABASEPROPERTY(@dbname, 'IsSuspect') = 0 and isnull(DATABASEPROPERTY(@dbname, 'IsInRecovery'),0) = 0 and isnull(DATABASEPROPERTY(@dbname, 'IsNotRecovered'),0) = 0 and DATABASEPROPERTY(@dbname, 'IsOffline') = 0 and isnull(DATABASEPROPERTY(@dbname, 'IsShutDown'),0) = 0 begin select @command = 'select ''Database'', ''' + replace(@dbname,char(39),char(39)+char(39)) + ''', ' + convert(varchar(10),@status) + ', ' + convert(varchar(10),@mode) + ', Allocated_DBSize = (select isnull(sum(convert(dec(12,0),size) * 8), 0) from [' + replace(@dbname,char(93),char(93)+char(93)) + ']..sysfiles (nolock) where status&1000000 = 0), Actual_DBSize = (sum(convert(dec(12,0),used)) * 8) from [' + replace(@dbname,char(93),char(93)+char(93)) + ']..sysindexes (nolock) where indid in (0, 1, 255)'
execute(@command)
select @command = 'truncate table #intermediate_sysfiles insert into #intermediate_sysfiles select upper(substring(filename, 1, 1)) as driveletter, case when status&1000000 = 0 then 1 else 0 end as datafile, maxsize, size, growth from [' + replace(@dbname,char(93),char(93)+char(93)) + ']..sysfiles
truncate table #grouped_devices insert into #grouped_devices select driveletter, filetypeflag, maxsize, size, growth, 0 from #intermediate_sysfiles where maxsize <> -1
UNION select driveletter, filetypeflag, -1, sum(size), sum(growth), 0 from #intermediate_sysfiles where maxsize = -1 and growth <> 0 group by driveletter, filetypeflag
UNION select driveletter, filetypeflag, -1, sum(size), 0, 0 from #intermediate_sysfiles where maxsize = 0 or (maxsize = -1 and growth = 0) group by driveletter, filetypeflag
update #grouped_devices set expansion = isnull(CASE when maxsize > 0 and (convert(dec(12,0),maxsize)*8)-(convert(dec(12,0),size)*8) <= (convert(dec(12,0),d.unused_size)*1024) then (convert(dec(12,0),maxsize)*8)-(convert(dec(12,0),size)*8)
when maxsize = 0 or (maxsize = -1 and growth = 0) then 0 else (convert(dec(12,0),d.unused_size)*1024) end , 0) from #disk_drives d where driveletter = upper(d.drive_letter)
select case when filetypeflag = 0 then ''Log Expansion'' else ''DB Expansion'' end, max(expansion) from #grouped_devices group by filetypeflag'
execute(@command)
end else begin single_user_db: if @mode = 0 if DATABASEPROPERTY(@dbname, 'IsOffline') = 0 and isnull(DATABASEPROPERTY(@dbname, 'IsShutDown'),0) = 1 select 'Suspect', @dbname, @status, @mode else select 'Loading/ExLock DB', @dbname, @status, @mode else select 'Not Accessible', @dbname, @status, @mode end fetch read_db_status into @dbname, @dbid, @mode, @status End Close read_db_status deallocate read_db_status
select 'get log size' DBCC SQLPERF (LOGSPACE) Select 'end of DB check'
select 'get perf counters' select lower(RTRIM(counter_name)), cntr_value from master..sysperfinfo where object_name = 'SQLServer:SQL Statistics' and LOWER(counter_name) in ('batch requests/sec', 'sql compilations/sec', 'sql re-compilations/sec') if @@rowcount = 0 select 'no counters exist'
select 'get proccache' DBCC ProcCache
if (select count(*) from #TableDetails) > 0 begin select 'get table details' select * from #TableDetails order by DBName, TableName, TableOwner end
select 'get replication status'
declare @distributor varchar(255), @DistributionDB varchar(255), @retcode int, @count int, @checkdistributed tinyint, @checksubscribed tinyint
select @checkdistributed = 1, @checksubscribed = 1
exec @retcode = sp_helpdistributor @distributor = @distributor OUTPUT, @distribdb = @DistributionDB OUTPUT
if @@error <> 0 OR @retcode <> 0 OR @distributor IS NULL begin
if @DistributionDB IS NULL begin select 0,0, @@servername + ' does not have replication installed' select @checkdistributed = 0, @checksubscribed = 0 end
else begin select @distributor = @@servername select 1,0, @@servername + ' has replication running locally' end end
else begin if @distributor <> @@servername begin
select 1,1, @@servername + ' publishes/subscribes to server ' + @distributor select @checksubscribed = 0 end
else begin if @DistributionDB IS NULL begin select 1,1, @@servername + ' is a distributor/subscriber only'
select @checkdistributed = 0 end else select 1,0, @@servername + ' is running replication locally' end end
if @checksubscribed = 1 begin declare DistributionDBList insensitive cursor for select name from master..sysdatabases (nolock) where (category & 16 = 16) for read only Open DistributionDBList fetch DistributionDBList into @DistributionDB while @@fetch_status = 0 begin
exec('if (select count(*) from ' + @DistributionDB + '..sysobjects where name in
(''MSrepl_transactions'',''MSsubscriptions'',''MSdistribution_history'', ''MSrepl_commands'')) <> 4
select ''~skip subscription~'' ') if @@rowcount = 0 begin select 'get subscription latency' exec('select ''' + @DistributionDB + ''', isnull(max(datediff(second,
t.entry_time, getdate())),0) from [' + @DistributionDB + ']..MSrepl_commands rc,
[' + @DistributionDB + ']..MSrepl_transactions t, [' + @DistributionDB + ']..MSsubscriptions
s where s.subscriber_id >= 0 and t.publisher_database_id = s.publisher_database_id
and rc.publisher_database_id = s.publisher_database_id and s.status = 2 and
t.xact_seqno = rc.xact_seqno and t.xact_seqno > (select isnull(max(h.xact_seqno), 0)
from [' + @DistributionDB + ']..MSdistribution_history h where h.agent_id = s.agent_id)
and rc.article_id = s.article_id and ((rc.xact_seqno >= s.subscription_seqno and (rc.type & 0x80000000) <> 0x80000000) or
rc.xact_seqno = s.subscription_seqno)') select 'get non-subscribed transactions' exec ('select article_id,
DelivCmdsInDistDB, UndelivCmdsInDistDB from [' + @DistributionDB + ']..MSdistribution_status') end fetch DistributionDBList into @DistributionDB End Close DistributionDBList Deallocate DistributionDBList end if @checkdistributed = 1 begin select 'get non-distributed transactions'
exec master..sp_replcounters end
select 'end replication details'
select 'Bombed Jobs' declare @instance int select @instance = max(instance_id) from msdb..sysjobhistory where run_status not in (2, 4) select 'instance', isnull(@instance,0) select 'Job Name' = j.name, h.step_name, h.sql_message_id, h.sql_severity, 'datetime' = convert(varchar(20),convert(datetime,convert(varchar(20), h.run_date) + ' ' +substring(right(replicate(0,6)+convert(varchar(6),h.run_time),6),1,2)+':'+substring(right(replicate(0,6)+convert(varchar(6),h.run_time),6),3,2)+':'+substring(right(replicate(0,6)+convert(varchar(6),h.run_time),6),5,2)), 113), 'Command Part 1' = convert(nvarchar(255),substring(s.command, 1, 255)), 'Command Part 2' = convert(nvarchar(255),substring(s.command, 256, 255)), 'Message Part 1' = convert(nvarchar(255),substring(h.message, 1, 255)), 'Message Part 2' = convert(nvarchar(255),substring(h.message, 256, 255)) from msdb..sysjobhistory h, msdb..sysjobsteps s, msdb..sysjobs j, msdb..syscategories c where h.job_id = s.job_id and h.step_id = s.step_id and j.job_id = h.job_id and c.category_id = j.category_id and s.on_fail_action = 2 and h.run_status = 0 and c.name in ('TSQL','CmdExec~','[Uncategorized (Local)]','Database Maintenance') and DATEDIFF(day,convert(datetime,convert(varchar(20), h.run_date)), GETDATE()) < 6 and h.instance_id = (select max(h1.instance_id) from msdb..sysjobhistory h1 where h1.job_id = s.job_id and h1.step_id = s.step_id and h1.run_status = 0 and h1.instance_id between 65045 and @instance) OPTION(ROBUST PLAN)
select 'Long Jobs' if isnull(object_id('tempdb..#sqlagent_enum_jobs'), 0) <> 0 truncate table #sqlagent_enum_jobs else CREATE TABLE #sqlagent_enum_jobs (job_id UNIQUEIDENTIFIER NOT NULL, last_run_date INT NOT NULL, last_run_time INT NOT NULL, next_run_date INT NOT NULL, next_run_time INT NOT NULL, next_run_schedule_id INT NOT NULL, requested_to_run INT NOT NULL, request_source INT NOT NULL, request_source_id sysname NULL,running INT NOT NULL, current_step INT NOT NULL, current_retry_attempt INT NOT NULL, job_state INT NOT NULL) if isnull(object_id('tempdb..#running_jobs'), 0) <> 0 truncate table #running_jobs else CREATE TABLE #running_jobs (job_id UNIQUEIDENTIFIER NOT NULL, current_step INT NOT NULL, current_retry_attempt INT NOT NULL, next_run_datetime datetime NOT NULL) INSERT INTO #sqlagent_enum_jobs EXECUTE master..xp_sqlagent_enum_jobs 1, sa if @@rowcount > 0 begin INSERT INTO #running_jobs select job_id, current_step, current_retry_attempt, convert(datetime,convert(varchar(20), next_run_date) + ' ' + substring(right(replicate(0,6)+convert(varchar(6),next_run_time),6),1,2)+':'+substring(right(replicate(0,6)+convert(varchar(6),next_run_time),6),3,2)+':'+substring(right(replicate(0,6)+convert(varchar(6),next_run_time),6),5,2)) from #sqlagent_enum_jobs where running = 1 and request_source = 1 if @@rowcount > 0 begin declare @current_datetime datetime select @current_datetime = GETDATE() insert into #already_reported select j.job_id, j.name, r.current_step, r.current_retry_attempt, datediff(ss,r.next_run_datetime, getdate()), r.next_run_datetime, @current_datetime from msdb..sysjobs j, msdb..syscategories c, #running_jobs r where r.next_run_datetime is not null and j.job_id = r.job_id and c.category_id = j.category_id and c.name in ('TSQL','CmdExec~','[Uncategorized (Local)]','Database Maintenance') and not exists (select * from #already_reported a where a.job_id = r.job_id and a.started_at = r.next_run_datetime) and datediff(ss,r.next_run_datetime, getdate()) > 5 and datediff(ss,r.next_run_datetime, getdate()) > (select run_duration * 2 from msdb..sysjobhistory h where h.job_id = r.job_id and h.run_status = 1 and h.step_id = 0 and h.instance_id = (select max(h1.instance_id) from msdb..sysjobhistory h1 where h1.job_id = h.job_id and h1.run_status = 1 and h1.step_id = 0)) select job_name, current_running_step, current_retry_attempt, no_of_seconds_running, started_at from #already_reported where this_entry_datetime = @current_datetime End End
select 'System Information' select counter_name, isnull(sum(convert(dec(15,0),cntr_value)),0) from master..sysperfinfo (nolock) where (Lower(object_name) = Lower('SQLServer:buffer manager') and Lower(counter_name) like 'buffer cache hit ratio%') or (Lower(object_name) = Lower('SQLServer:cache manager') and Lower(counter_name) like 'cache hit ratio%' and Lower(instance_name) <> '_total') or (Lower(object_name) = Lower('SQLServer:databases') and Lower(counter_name) = 'log flushes/sec' and Lower(instance_name) <> '_total') or (Lower(object_name) = Lower('SQLServer:memory manager') and Lower(counter_name) in ('target server memory(kb)', 'total server memory (kb)')) or (Lower(object_name) = Lower('SQLServer:access methods') and Lower(counter_name) in ('page splits/sec','full scans/sec', 'table lock escalations/sec', 'worktables created/sec', 'workfiles created/sec')) or (Lower(object_name) = Lower('SQLServer:locks') and Lower(counter_name) = 'lock waits/sec' and Lower(instance_name) <> '_total') or (Lower(object_name) = Lower('SQLServer:buffer manager') and Lower(counter_name) in ('page reads/sec', 'page writes/sec', 'lazy writes/sec','checkpoint pages/sec', 'checkpoint writes/sec', 'readahead pages/sec', 'page lookups/sec', 'page requests/sec', 'page life expectancy')) group by counter_name select 'End System Info' if exists(select * from master..sysprocesses where open_tran <> 0) select 'Check For Open Transaction', 'true' else select 'Check For Open Transaction', 'false'
select 'Database Statistics' select counter_name, isnull(cntr_value,0), instance_name from master..sysperfinfo (nolock) where Lower(object_name) = Lower('SQLServer:databases') and Lower(counter_name) in ('transactions/sec', 'log flushes/sec', 'log bytes flushed/sec', 'log flush waits/sec', 'log cache reads/sec', 'log cache hit ratio', 'log cache hit ratio base') and Lower(instance_name) <> '_total' order by instance_name select 'End DB Statistics',0,'~<:end DB stats:>~'
select 'IsClustered' select ServerProperty('IsClustered')Few questions from a Profiler novice.
Firstly - what is it?
Secondly - why does it talk about replication? There is no replication currently on this server? It has been tested on this server in the past though.
Lastly - why does it take so long?
Many thanks
Dan
----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss
Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------