SQLServerWiki

“The Only Thing That Is Constant Is Change”

some useful queries!

Posted by database-wiki on July 4, 2012

1. Checking the stat_updated date by running the below query:

— Get stats_date for all db’s

PRINT ”

PRINT ‘==== STATS_DATE and rowmodctr for indexes in all databases ====’

EXEC master..sp_MSforeachdb @command1 = ‘

PRINT ””

PRINT ”– STATS_DATE and rowmodctr for [?].sysindexes –”’,

  @command2 = ‘

use [?]

select db_id() as dbid,

  case

    when indid IN (0, 1) then convert (char (12), rows)

    else (select rows from [?].dbo.sysindexes i2 where i2.id =  i.id and i2.indid in (0,1)) — ”-”

  end as rowcnt,

  case

    when indid IN (0, 1) then rowmodctr

    else convert (bigint, rowmodctr) + (select rowmodctr from [?].dbo.sysindexes i2 where i2.id =  i.id and i2.indid in (0,1))

  end as row_mods,

  case rows when 0 then 0 else convert (bigint,

    case

      when indid IN (0, 1) then convert (bigint, rowmodctr)

      else rowmodctr + (select convert (bigint, rowmodctr) from [?].dbo.sysindexes i2 where i2.id =  i.id and i2.indid in (0,1))

    end / convert (numeric (20,2), (select case convert (bigint, rows) when 0 then 0.01 else rows end from [?].dbo.sysindexes i2 where i2.id =  i.id and i2.indid in (0,1))) * 100)

  end as pct_mod,

  convert (nvarchar, u.name + ”.” + o.name) as objname,

  case when i.status&0x800040=0x800040 then ”AUTOSTATS”

    when i.status&0x40=0x40 and i.status&0x800000=0 then ”STATS”

    else ”INDEX” end as type,

  convert (nvarchar, i.name) as idxname, i.indid,

  stats_date (o.id, i.indid) as stats_updated,

  case i.status & 0x1000000 when 0 then ”no” else ”*YES*” end as norecompute,

  o.id as objid ,  i.status

from [?].dbo.sysobjects o, [?].dbo.sysindexes i, [?].dbo.sysusers u

where o.id = i.id and o.uid = u.uid and i.indid between 1 and 254 and o.type = ”U”

order by pct_mod desc, convert (nvarchar, u.name + ”.” + o.name), indid

GO

 

PRINT ‘End time: ‘ + CONVERT (varchar, GETDATE(), 126)

PRINT ‘Done.’

GO

TOP CPU consumption:

select top 50 

    sum(qs.total_worker_time) as total_cpu_time, 

    sum(qs.execution_count) as total_execution_count,

    count(*) as  number_of_statements, 

    qs.plan_handle 

from 

    sys.dm_exec_query_stats qs

group by qs.plan_handle

order by sum(qs.total_worker_time) desc

Replication distribution metadata row count

Select db_id() as dbid,

  case

    when indid IN (0, 1) then convert (char (12), rows)

    else (select rows from dbo.sysindexes i2 where i2.id =  i.id and i2.indid in (0,1)) — ”-”

  end as rowcnt,

  case

    when indid IN (0, 1) then rowmodctr

    else convert (bigint, rowmodctr) + (select rowmodctr from dbo.sysindexes i2 where i2.id =  i.id and i2.indid in (0,1))

  end as row_mods,

  case rows when 0 then 0 else convert (bigint,

    case

      when indid IN (0, 1) then convert (bigint, rowmodctr)

      else rowmodctr + (select convert (bigint, rowmodctr) from dbo.sysindexes i2 where i2.id =  i.id and i2.indid in (0,1))

    end / convert (numeric (20,2), (select case convert (bigint, rows) when 0 then 0.01 else rows end from dbo.sysindexes i2 where i2.id =  i.id and i2.indid in (0,1))) * 100)

  end as pct_mod,

  convert (nvarchar, u.name + ‘.’ + o.name) as objname,

  case when i.status&0x800040=0x800040 then ‘AUTOSTATS’

    when i.status&0x40=0x40 and i.status&0x800000=0 then ‘STATS’

    else ‘INDEX’ end as type,

  convert (nvarchar, i.name) as idxname, i.indid,

  stats_date (o.id, i.indid) as stats_updated,

  case i.status & 0x1000000 when 0 then ‘no’ else ‘*YES*’ end as norecompute,

  o.id as objid , rowcnt, i.status

from dbo.sysobjects o, dbo.sysindexes i, dbo.sysusers u

where o.id = i.id and o.uid = u.uid and i.indid between 1 and 254 and o.type = ‘U’

order by pct_mod desc, convert (nvarchar, u.name + ‘.’ + o.name), indid

GO

Running the disributor from the command line:

distrib.exe -Subscriber [test] -SubscriberDB [Subscriber] -Publisher [test]
-Distributor [test] -DistributorSecurityMode 1 -Published [Publisher] -Output
C:\output.txt -OutputVerboseLevel 3

Database mirroring data’s to collect:

Collect/Confirm information:

=====================

1. Please confirm when new principal goes into single user mode, if the new principal may be DB00 or DB01. That means this issue happened on both database if the database becomes principal.

2. After new principal goes into single user mode, please provide the status of new mirror and witness database.

3. Please provide latest time to occur this issue.

Collect/Confirm information:

=====================

1. When principal goes into to single user mode, if there exists any error message? If so, please provide it and the screenshot of error message.

2. Run the following query to get the partner and witness server as well as mirrored databases.  Note if the witness server is a named instance, this query will return the server name but not the instance name.

select db_name(database_id),mirroring_partner_instance, mirroring_witness_name from sys.database_mirroring where mirroring_guid is not null

3. Connect to each server returned by the query in step #1 and run this in MASTER:

select @@servername as server, convert(varchar(26), getdate(), 121) as [date],3 as [version]

go

print ‘– SQL Server service startup account’

EXEC master.dbo.xp_instance_regread  ‘HKEY_LOCAL_MACHINE’, ‘SYSTEM\CurrentControlSet\Services\MSSQLServer’, ‘ObjectName’

go

print ‘– sys.database_mirroring’

select * from sys.database_mirroring where mirroring_guid is not null

go

print ‘– sys.database_mirroring_witnesses’

select * from sys.database_mirroring_witnesses

go

if exists (select * from sys.database_mirroring_endpoints where connection_auth = 4)

begin

print ‘– Endpoint Owner and Information using Certificates’

select c.string_sid,c.certificate_id, c.name, c.principal_id, c.pvt_key_encryption_type_desc,

‘date valid cert’=case when getdate() between c.start_date and c.expiry_date then 1 else 0 end,

c.start_date, c.expiry_date,

dme.name, dme.endpoint_id, dme.role_desc, dme.connection_auth_desc, dme.encryption_algorithm_desc,

sp.name, sp.type_desc,

te.principal_id, te.state_desc, te.port, te.ip_address

from sys.database_mirroring_endpoints dme inner join sys.tcp_endpoints te on dme.endpoint_id = te.endpoint_id

inner join sys.server_principals sp on sp.principal_id = dme.principal_id

inner join sys.certificates c on c.certificate_id = dme.certificate_id

print ‘– Granted permissions on Endpoint using Certificates’

select c.string_sid,c.certificate_id, c.name, c.principal_id, c.pvt_key_encryption_type_desc,

‘date valid cert’=case when getdate() between c.start_date and c.expiry_date then 1 else 0 end,

c.start_date, c.expiry_date,

dp.name, dp.type_desc, sp.grantee_principal_id, spee.name, spee.type_desc,

sp.grantor_principal_id, sp.permission_name, sp.state_desc

from sys.server_permissions sp inner join sys.database_mirroring_endpoints dme on sp.major_id = dme.endpoint_id

inner join sys.server_principals spee on sp.grantee_principal_id = spee.principal_id

inner join master.sys.database_principals dp on spee.sid = dp.sid

inner join sys.certificates c on c.principal_id = dp.principal_id

end

else

begin

print ‘– Endpoint Owner and Information NOT using Certificates’

select  te.state_desc, te.port, te.ip_address, dme.name, dme.endpoint_id, dme.role_desc, dme.connection_auth_desc, dme.encryption_algorithm_desc,

sp.principal_id, sp.name from sys.server_principals sp

inner join sys.database_mirroring_endpoints dme on sp.principal_id = dme.principal_id

inner join sys.tcp_endpoints te on dme.endpoint_id = te.endpoint_id

print ‘– Granted permissions on Endpoint NOT using Certificates’

select  sp.grantee_principal_id, spee.name, spee.type_desc,

sp.grantor_principal_id, spor.name, spor.type_desc,

sp.permission_name, sp.state_desc,  dme.endpoint_id

from sys.server_permissions sp right outer join sys.database_mirroring_endpoints dme on sp.major_id = dme.endpoint_id

inner join sys.server_principals spee on sp.grantee_principal_id = spee.principal_id

inner join sys.server_principals spor on sp.grantor_principal_id = spor.principal_id

end

go

print ‘– sys.database_recovery_status’

select ‘dbname’=db_name(rs.database_id), rs.* from sys.database_recovery_status rs

inner join sys.database_mirroring m on rs.database_id = m.database_id and m.mirroring_guid is not null

go

print ‘– sys.databases (log_reuse_wait_info)’

select ‘dbname’=db_name(d.database_id), d.database_id, d.log_reuse_wait, d.log_reuse_wait_desc from sys.databases d

inner join sys.database_mirroring m on d.database_id = m.database_id and m.mirroring_guid is not null

go

print ‘– log backups for mirroring databases’

select b.* from msdb.dbo.backupset b

inner join sys.database_mirroring m on b.database_name = db_name(m.database_id) and m.mirroring_guid is not null

where type = ‘L’

order by backup_finish_date desc

go

print ‘– dbcc tracestatus (-1)’

dbcc tracestatus(-1)

go

dbcc traceon (3604,1495,-1)

go

declare @dbid varchar(30)

declare @dbname nvarchar(128)

declare @status sql_variant

declare @useraccess sql_variant

declare c cursor for select database_id from sys.database_mirroring where mirroring_guid is not null

open c

fetch next from c into @dbid

while (@@fetch_status <> -1)

begin

print ‘====================================================’

print ‘– DBCC DBTABLE (‘ + @dbid + ‘)’

exec (‘dbcc dbtable (‘ + @dbid + ‘)’)

print ”

set @dbname = db_name(@dbid)

set @status = DATABASEPROPERTYEX(@dbname,’Status’)

set @useraccess = DATABASEPROPERTYEX(@dbname,’UserAccess’)

print ‘====================================================’

print ‘– DBCC OPENTRAN FOR DBID ‘ + @dbid + ‘ [‘+ @dbname + ‘]’

if @Status = N’ONLINE’ and @UserAccess != N’SINGLE_USER’

dbcc opentran(@dbname)

else

print ‘Skipped: Status=’ + convert(nvarchar(128),@status)

+ ‘ UserAccess=’ + convert(nvarchar(128),@useraccess)

fetch next from c into @dbid

end

close c

deallocate c

print ‘====================================================’

print ‘– DBCC RESOURCE’

DBCC RESOURCE

go

dbcc traceoff (3604,1495,-1)

go

print ‘– IPCONFIG /ALL’

exec master.dbo.xp_cmdshell ‘ipconfig /all’

go

4. Collect MPS Reports from all the partners (Principal, Mirror, Witness)

To create a MPS Report, please visit the following web site: http://www.microsoft.com/downloads/details.aspx?FamilyId=CEBF3C7C-7CA5-408F-88B7-F9C79B7306C0&displaylang=en and look for mpsreports_x86.exe or mpsreports_x64.exe. Download it and run it on the problematic SQL box. Choose the following categories: General and SQL and other Data Stores (MDAC)

When the collection finishes, please choose “Save the result”. Send the generated .cab file via the FTP workspace to me.

SQLNEXUS DATA REPORT:

/*—————————————————————-

——————————————————————

—  ReadTrace_LeanMeanVersion                                 —

——————————————————————

—  Script to reproduce the output of the original ReadTrace  —

—  utility to provide greater detail for problem resolution  —

——————————————————————

—————————————————————-*/

 

–Use PerfAnalysis

Use SQLNexus

Set Nocount On;

 

print ”

print ‘###  Interesting Events                                ###’

print ‘##########################################################’

select convert(nvarchar(30),te.name) as Event

      , count(tIE.EventID) as Occurrences

      , tIE.Error

from ReadTrace.tblInterestingEvents tIE

join master.sys.trace_events te on (tIE.EventID = te.trace_event_id)

group by te.name, tIE.Error

order by te.name, count(tIE.EventID) desc

 

 

print ”

print ‘###  Rollup – Top 10 Batches by Attentions             ###’

print ‘##########################################################’

select top(10) vBU.CompletedEvents as Executions

      , vBU.AttentionEvents

      , convert(nvarchar(20),vBU.AvgDuration) as AvgDuration

      , convert(nvarchar(20),vBU.AvgReads) as AvgReads

      , convert(nvarchar(20),vBU.AvgWrites) as AvgWrites

      , convert(nvarchar(20),vBU.AvgCPU) as AvgCPU

      , vBU.HashID

      , tUB.NormText

from ReadTrace.vwBatchUtilization vBU

join ReadTrace.tblUniqueBatches tUB on (vBU.HashID = tUB.HashID)

order by vBU.AttentionEvents desc, vBU.CompletedEvents desc

 

———————————————————————————-

 

print ”

print ‘###  Average – Top 10 Batches by AvgDuration           ###’

print ‘##########################################################’

select top(10) vBU.CompletedEvents as Executions

      , vBU.Duration

      , convert(nvarchar(20),vBU.AvgDuration) as AvgDuration

      , vBU.Reads

      , convert(nvarchar(20),vBU.AvgReads) as AvgReads

      , vBU.Writes

      , convert(nvarchar(20),vBU.AvgWrites) as AvgWrites

      , vBU.CPU

      , convert(nvarchar(20),vBU.AvgCPU) as AvgCPU

      , vBU.HashID

      , tUB.NormText

from ReadTrace.vwBatchUtilization vBU

join ReadTrace.tblUniqueBatches tUB on (vBU.HashID = tUB.HashID)

where vBU.Duration <> 0

order by vBU.AvgDuration desc

 

print ”

print ‘###  Average – Top 10 Batches by AvgReads              ###’

print ‘##########################################################’

select top(10) vBU.CompletedEvents as Executions

      , vBU.Duration

      , convert(nvarchar(20),vBU.AvgDuration) as AvgDuration

      , vBU.Reads

      , convert(nvarchar(20),vBU.AvgReads) as AvgReads

      , vBU.Writes

      , convert(nvarchar(20),vBU.AvgWrites) as AvgWrites

      , vBU.CPU

      , convert(nvarchar(20),vBU.AvgCPU) as AvgCPU

      , vBU.HashID

      , tUB.NormText

from ReadTrace.vwBatchUtilization vBU

join ReadTrace.tblUniqueBatches tUB on (vBU.HashID = tUB.HashID)

where vBU.Reads <> 0

order by vBU.AvgReads desc

 

print ”

print ‘###  Average – Top 10 Batches by AvgWrites             ###’

print ‘##########################################################’

select top(10) vBU.CompletedEvents as Executions

      , vBU.Duration

      , convert(nvarchar(20),vBU.AvgDuration) as AvgDuration

      , vBU.Reads

      , convert(nvarchar(20),vBU.AvgReads) as AvgReads

      , vBU.Writes

      , convert(nvarchar(20),vBU.AvgWrites) as AvgWrites

      , vBU.CPU

      , convert(nvarchar(20),vBU.AvgCPU) as AvgCPU

      , vBU.HashID

      , tUB.NormText

from ReadTrace.vwBatchUtilization vBU

join ReadTrace.tblUniqueBatches tUB on (vBU.HashID = tUB.HashID)

where vBU.Writes <> 0

order by vBU.AvgWrites desc

 

print ”

print ‘###  Average – Top 10 Batches by AvgCPU                ###’

print ‘##########################################################’

select top(10) vBU.CompletedEvents as Executions

      , vBU.Duration

      , convert(nvarchar(20),vBU.AvgDuration) as AvgDuration

      , vBU.Reads

      , convert(nvarchar(20),vBU.AvgReads) as AvgReads

      , vBU.Writes

      , convert(nvarchar(20),vBU.AvgWrites) as AvgWrites

      , vBU.CPU

      , convert(nvarchar(20),vBU.AvgCPU) as AvgCPU

      , vBU.HashID

      , tUB.NormText

from ReadTrace.vwBatchUtilization vBU

join ReadTrace.tblUniqueBatches tUB on (vBU.HashID = tUB.HashID)

where vBU.CPU <> 0

order by vBU.AvgCPU desc

 

———————————————————————————-

 

print ”

print ‘###  Rollup – Top 10 Batches by Duration               ###’

print ‘##########################################################’

select top(10) vBU.CompletedEvents as Executions

      , vBU.Duration

      , convert(nvarchar(20),vBU.AvgDuration) as AvgDuration

      , vBU.Reads

      , convert(nvarchar(20),vBU.AvgReads) as AvgReads

      , vBU.Writes

      , convert(nvarchar(20),vBU.AvgWrites) as AvgWrites

      , vBU.CPU

      , convert(nvarchar(20),vBU.AvgCPU) as AvgCPU

      , vBU.HashID

      , tUB.NormText

from ReadTrace.vwBatchUtilization vBU

join ReadTrace.tblUniqueBatches tUB on (vBU.HashID = tUB.HashID)

order by vBU.Duration desc

 

print ”

print ‘###  Rollup – Top 10 Batches by Reads                  ###’

print ‘##########################################################’

select top(10) vBU.CompletedEvents as Executions

      , vBU.Duration

      , convert(nvarchar(20),vBU.AvgDuration) as AvgDuration

      , vBU.Reads

      , convert(nvarchar(20),vBU.AvgReads) as AvgReads

      , vBU.Writes

      , convert(nvarchar(20),vBU.AvgWrites) as AvgWrites

      , vBU.CPU

      , convert(nvarchar(20),vBU.AvgCPU) as AvgCPU

      , vBU.HashID

      , tUB.NormText

from ReadTrace.vwBatchUtilization vBU

join ReadTrace.tblUniqueBatches tUB on (vBU.HashID = tUB.HashID)

order by vBU.Reads desc

 

print ”

print ‘###  Rollup – Top 10 Batches by Writes                 ###’

print ‘##########################################################’

select top(10) vBU.CompletedEvents as Executions

      , vBU.Duration

      , convert(nvarchar(20),vBU.AvgDuration) as AvgDuration

      , vBU.Reads

      , convert(nvarchar(20),vBU.AvgReads) as AvgReads

      , vBU.Writes

      , convert(nvarchar(20),vBU.AvgWrites) as AvgWrites

      , vBU.CPU

      , convert(nvarchar(20),vBU.AvgCPU) as AvgCPU

      , vBU.HashID

      , tUB.NormText

from ReadTrace.vwBatchUtilization vBU

join ReadTrace.tblUniqueBatches tUB on (vBU.HashID = tUB.HashID)

order by vBU.Writes desc

 

print ”

print ‘###  Rollup – Top 10 Batches by CPU                    ###’

print ‘##########################################################’

select top(10) vBU.CompletedEvents as Executions

      , vBU.Duration

      , convert(nvarchar(20),vBU.AvgDuration) as AvgDuration

      , vBU.Reads

      , convert(nvarchar(20),vBU.AvgReads) as AvgReads

      , vBU.Writes

      , convert(nvarchar(20),vBU.AvgWrites) as AvgWrites

      , vBU.CPU

      , convert(nvarchar(20),vBU.AvgCPU) as AvgCPU

      , vBU.HashID

      , tUB.NormText

from ReadTrace.vwBatchUtilization vBU

join ReadTrace.tblUniqueBatches tUB on (vBU.HashID = tUB.HashID)

order by vBU.CPU desc

 

———————————————————————————-

 

print ”

print ‘###  Individual – Top 10 Unique Batches by Duration    ###’

print ‘##########################################################’

select top(10) tB.StartTime as StartTime

      , tB.EndTime

      –, tB.SPID

      , tB.Duration

      , tB.Reads

      , tB.Writes

      , tB.CPU

      , convert(nvarchar(30),(select top(1) sTF.TraceFileName from ReadTrace.tblTraceFiles sTF where sTF.FirstSeqNumber < tB.StartSeq order by sTF.TraceFileName desc)) as StartingTraceFile –trace file for start of batch

      , convert(nvarchar(30),(select top(1) eTF.TraceFileName from ReadTrace.tblTraceFiles eTF where eTF.FirstSeqNumber < tB.EndSeq order by eTF.TraceFileName desc)) as EndingTraceFile –trace file for end of batch

      , tB.HashID

      , tUB.NormText

from ReadTrace.tblBatches tB

join ReadTrace.tblUniqueBatches tUB on (tB.HashID = tUB.HashID)

order by tB.Duration desc

 

print ”

print ‘###  Individual – Top 10 Batches by Reads              ###’

print ‘##########################################################’

select top(10) tB.StartTime as StartTime

      , tB.EndTime

      –, tB.SPID

      , tB.Duration

      , tB.Reads

      , tB.Writes

      , tB.CPU

      , convert(nvarchar(30),(select top(1) sTF.TraceFileName from ReadTrace.tblTraceFiles sTF where sTF.FirstSeqNumber < tB.StartSeq order by sTF.TraceFileName desc)) as StartingTraceFile –trace file for start of batch

      , convert(nvarchar(30),(select top(1) eTF.TraceFileName from ReadTrace.tblTraceFiles eTF where eTF.FirstSeqNumber < tB.EndSeq order by eTF.TraceFileName desc)) as EndingTraceFile –trace file for end of batch

      , tB.HashID

      , tUB.NormText

from ReadTrace.tblBatches tB

join ReadTrace.tblUniqueBatches tUB on (tB.HashID = tUB.HashID)

order by tB.Reads desc

 

print ”

print ‘###  Individual – Top 10 Batches by Writes             ###’

print ‘##########################################################’

select top(10) tB.StartTime as StartTime

      , tB.EndTime

      –, tB.SPID

      , tB.Duration

      , tB.Reads

      , tB.Writes

      , tB.CPU

      , convert(nvarchar(30),(select top(1) sTF.TraceFileName from ReadTrace.tblTraceFiles sTF where sTF.FirstSeqNumber < tB.StartSeq order by sTF.TraceFileName desc)) as StartingTraceFile –trace file for start of batch

      , convert(nvarchar(30),(select top(1) eTF.TraceFileName from ReadTrace.tblTraceFiles eTF where eTF.FirstSeqNumber < tB.EndSeq order by eTF.TraceFileName desc)) as EndingTraceFile –trace file for end of batch

      , tB.HashID

      , tUB.NormText

from ReadTrace.tblBatches tB

join ReadTrace.tblUniqueBatches tUB on (tB.HashID = tUB.HashID)

order by tB.Writes desc

 

print ”

print ‘###  Individual – Top 10 Batches by CPU                ###’

print ‘##########################################################’

select top(10) tB.StartTime as StartTime

      , tB.EndTime

      –, tB.SPID

      , tB.Duration

      , tB.Reads

      , tB.Writes

      , tB.CPU

      , convert(nvarchar(30),(select top(1) sTF.TraceFileName from ReadTrace.tblTraceFiles sTF where sTF.FirstSeqNumber < tB.StartSeq order by sTF.TraceFileName desc)) as StartingTraceFile –trace file for start of batch

      , convert(nvarchar(30),(select top(1) eTF.TraceFileName from ReadTrace.tblTraceFiles eTF where eTF.FirstSeqNumber < tB.EndSeq order by eTF.TraceFileName desc)) as EndingTraceFile –trace file for end of batch

      , tB.HashID

      , tUB.NormText

from ReadTrace.tblBatches tB

join ReadTrace.tblUniqueBatches tUB on (tB.HashID = tUB.HashID)

order by tB.CPU desc

 

—————————————————————————–

print ”

print ‘###  Plans – Top 10 Discrepancies Between Estimated and Actual      ###’

print ‘#######################################################################’

print ‘~~~  Note: StepNumber is the number of the step in the plan and     ~~~’

print ‘~~~  only relates to other StepNumbers with the same SequenceNumber ~~~’

print ‘~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~’

print ”

select top(10) tPR.seq as SequenceNumber

      , tPR.RowOrder as StepNumber

      , ABS((tPR.Rows * tPR.Executes) – (tPR.EstimateRows * tPR.EstimateExecutes)) as Factor

      , tPR.Rows

      , tPR.EstimateRows

      , tPR.Executes

      , tPR.EstimateExecutes

      –, tP.SPID

      , tP.DOP

      , tP.PlanHashID

      , tUB.NormText

—    , tUB.OrigText

from ReadTrace.tblPlanRows tPR

join ReadTrace.tblPlans tP on (tP.Seq = tPR.Seq)

join ReadTrace.tblBatches tB on (tP.BatchSeq = tB.BatchSeq)

join ReadTrace.tblUniqueBatches tUB on (tB.HashID = tUB.HashID)

order by ABS((tPR.Rows * tPR.Executes) – (tPR.EstimateRows * tPR.EstimateExecutes)) desc

      , tPR.seq asc

      , tPR.RowOrder asc

 

—————————————————————————–

print ”

print ‘###  Captured Profiler Events                          ###’

print ‘##########################################################’

select convert(nvarchar(30),tc.name) as Category, convert(nvarchar(30),te.name) as Event

from master.sys.trace_events te

join master.sys.trace_categories tc on (tc.category_id = te.category_id)

where te.trace_event_id in (select EventID from ReadTrace.tblTracedEvents)

order by tc.name

 

————————————————————————————

print ”

print ‘###  Plans – Query Plan Worst Reads                                 ###’

print ‘#######################################################################’

print ”

select tUPR.PlanHashID

      , tUPR.RowOrder

      , tUPR.Rows

      , tUPR.Executes

      , tUPR.StmtText

      , tUPR.StmtID

      , tUPR.NodeID

      , tUPR.Parent

      , tUPR.PhysicalOp

      , tUPR.LogicalOp

      , tUPR.Argument

      , tUPR.DefinedValues

      , tUPR.EstimateRows

      , tUPR.EstimateIO

      , tUPR.EstimateCPU

      , tUPR.AvgRowSize

      , tUPR.TotalSubtreeCost

      , tUPR.OutputList

      , tUPR.Warnings

      , tUPR.Type

      , tUPR.Parallel

      , tUPR.EstimateExecutions

from ReadTrace.tblUniquePlanRows tUPR

where tUPR.PlanHashID in

      (select tP.PlanHashID from ReadTrace.tblPlans tP where tP.BatchSeq =

            (select top(1)tB.BatchSeq from ReadTrace.tblBatches tB where tB.HashID =

                  (select top(1) vBU.HashID from ReadTrace.vwBatchUtilization vBU where vBU.Reads <> 0 order by vBU.AvgReads desc)

            order by tb.BatchSeq desc)

      )

order by tUPR.PlanHashID, tUPR.RowOrder  

 

print ”

print ‘###  Plans – Query Plan Worst Duration                              ###’

print ‘#######################################################################’

print ”

select tUPR.PlanHashID

      , tUPR.RowOrder

      , tUPR.Rows

      , tUPR.Executes

      , tUPR.StmtText

      , tUPR.StmtID

      , tUPR.NodeID

      , tUPR.Parent

      , tUPR.PhysicalOp

      , tUPR.LogicalOp

      , tUPR.Argument

      , tUPR.DefinedValues

      , tUPR.EstimateRows

      , tUPR.EstimateIO

      , tUPR.EstimateCPU

      , tUPR.AvgRowSize

      , tUPR.TotalSubtreeCost

      , tUPR.OutputList

      , tUPR.Warnings

      , tUPR.Type

      , tUPR.Parallel

      , tUPR.EstimateExecutions

from ReadTrace.tblUniquePlanRows tUPR

where tUPR.PlanHashID in

      (select tP.PlanHashID from ReadTrace.tblPlans tP where tP.BatchSeq =

            (select top(1)tB.BatchSeq from ReadTrace.tblBatches tB where tB.HashID =

                  (select top(1) vBU.HashID from ReadTrace.vwBatchUtilization vBU where vBU.Duration <> 0 order by vBU.AvgDuration desc)

            order by tb.BatchSeq asc)

      )

order by tUPR.PlanHashID, tUPR.RowOrder  

 

 

print ”

print ‘###  Plans – Query Plan Worst CPU                                   ###’

print ‘#######################################################################’

print ”

select tUPR.PlanHashID

      , tUPR.RowOrder

      , tUPR.Rows

      , tUPR.Executes

      , tUPR.StmtText

      , tUPR.StmtID

      , tUPR.NodeID

      , tUPR.Parent

      , tUPR.PhysicalOp

      , tUPR.LogicalOp

      , tUPR.Argument

      , tUPR.DefinedValues

      , tUPR.EstimateRows

      , tUPR.EstimateIO

      , tUPR.EstimateCPU

      , tUPR.AvgRowSize

      , tUPR.TotalSubtreeCost

      , tUPR.OutputList

      , tUPR.Warnings

      , tUPR.Type

      , tUPR.Parallel

      , tUPR.EstimateExecutions

from ReadTrace.tblUniquePlanRows tUPR

where tUPR.PlanHashID in

      (select tP.PlanHashID from ReadTrace.tblPlans tP where tP.BatchSeq =

            (select top(1)tB.BatchSeq from ReadTrace.tblBatches tB where tB.HashID =

                  (select top(1) vBU.HashID from ReadTrace.vwBatchUtilization vBU where vBU.CPU <> 0 order by vBU.AvgCPU desc)

            order by tb.BatchSeq desc)

      )

–group by tUPR.PlanHashID, tUPR.RowOrder

order by tUPR.PlanHashID, tUPR.RowOrder

The row was not found at the subscriber:

Trigger:

CREATE TRIGGER TRG_ListingSearch_AUDIT

ON [dbo].[ListingSearch]

FOR DELETE,INSERT,UPDATE

AS

DECLARE @ACT CHAR(6)

DECLARE @DEL BIT

DECLARE @INS BIT

DECLARE @SQLSTRING VARCHAR(2000)

Declare @command nvarchar(255)

 

SET @DEL = 0

SET @INS = 0

 

IF EXISTS (SELECT TOP 1 1 FROM DELETED) SET @DEL=1

IF EXISTS (SELECT TOP 1 1 FROM INSERTED) SET @INS = 1

 

IF @INS = 1 AND @DEL = 1 SET @ACT = ‘UPDATE’

IF @INS = 1 AND @DEL = 0 SET @ACT = ‘INSERT’

IF @DEL = 1 AND @INS = 0 SET @ACT = ‘DELETE’

 

IF @INS = 0 AND @DEL = 0 RETURN

 

IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[ListingSearch_AUDIT]’) AND OBJECTPROPERTY(ID, N’ISUSERTABLE’) = 1)

BEGIN

      — CREATE A MEMORY TABLE CONTAINING THE FIELDS AND TYPES OF THE TABLE

      DECLARE @MEMTABLE TABLE

      (

            ID INT IDENTITY

            ,COLUMNAME SYSNAME

            ,TYPENAME VARCHAR(20)

      )

      — INSERT THE COLUMNAMES AND THE DATATYPES

      INSERT @MEMTABLE

            (COLUMNAME,TYPENAME)

            SELECT NAME,TYPE_NAME(XTYPE)

            FROM SYSCOLUMNS

            WHERE ID = OBJECT_ID(‘[dbo].[ListingSearch]’)

            ORDER BY COLID

 

      DECLARE @CUR INTEGER

      DECLARE @MAX INTEGER

      DECLARE @SQLSTR AS VARCHAR(8000)

      DECLARE @CURCOL SYSNAME

      DECLARE @COLTYPE AS VARCHAR(10)

 

      — SETUP VARIABLES

      SET @SQLSTR = ”

      SET @CUR=1

      SELECT @MAX = MAX(ID) FROM @MEMTABLE

 

      — LOOP EVEY FIELD

      WHILE @CUR <= @MAX

      BEGIN

            — GET VALUES FROM THE MEMTABLE    

            SELECT @CURCOL = COLUMNAME,@COLTYPE = TYPENAME FROM @MEMTABLE WHERE ID = @CUR

            IF @COLTYPE = ‘INT’ OR @COLTYPE = ‘BIGINT’ OR @COLTYPE=’UNIQUEIDENTIFIER’

                  — WE DO WANT TO COPY INT/BIGINT/UNIQUEIDENTIFIER FIELDS BUT IF THEY ARE AN

                  — IDENTITY OR A ROWGUIDCOLUMN WE DO NOT WANT TO COPY THAT ATTRIBUTES

                  SET @SQLSTR = @SQLSTR + ‘ CAST(‘+@CURCOL + ‘ AS ‘+@COLTYPE+’) AS [‘ + @CURCOL +’] ‘

            ELSE

                  — ANOTHER FIELD DO NOTHING JUST COPY IT AS IT IS

                  SET @SQLSTR = @SQLSTR + ‘ ‘+@CURCOL + ‘ AS [‘ + @CURCOL +’] ‘

            IF @CUR <= @MAX – 1 SET @SQLSTR=@SQLSTR + ‘,’

            SET @CUR = @CUR + 1

      END

      — ADD THE AUDIT FIELDS

      SET @SQLSTR = @SQLSTR +’,CAST(”      ” AS CHAR(6)) AS TRG_ACTION,CAST(GETDATE() AS DATETIME) AS TRG_DATE’

      — SET UP THE SELECT FOR CREATING THE AUDIT TABLE

      SET @SQLSTR = ‘SELECT TOP 0 ‘ + @SQLSTR + ‘ INTO [DBO].[ListingSearch_AUDIT] FROM [DBO].[ListingSearch]’

      EXEC(@SQLSTR)

      Set @SQLSTR=’alter table [DBO].[ListingSearch_AUDIT] add SPID smallint,Application_name varchar(1000),HostName nvarchar(128),command nvarchar(255)’

      EXEC(@SQLSTR)

     

END

 

CREATE TABLE #InputBuffer ([eventtype] NVARCHAR(30), [parameters] INT, [eventinfo] NVARCHAR(255))

INSERT INTO #InputBuffer exec(‘DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS’)

SELECT @command=eventinfo from #InputBuffer

 

IF @ACT = ‘INSERT’ INSERT [DBO].[ListingSearch_AUDIT] SELECT *,’INSERT’ ,GETDATE(),@@SPID,App_name(),host_name(),@command FROM INSERTED

IF @ACT = ‘DELETE’ INSERT [DBO].[ListingSearch_AUDIT] SELECT *,’DELETE’ ,GETDATE(),@@SPID,App_name(),host_name(),@command FROM DELETED

IF @ACT = ‘UPDATE’ INSERT [DBO].[ListingSearch_AUDIT] SELECT *,’UPDATE’ ,GETDATE(),@@SPID,App_name(),host_name(),@command FROM INSERTED

 

drop table #InputBuffer

creating table and populating with the data for test

create table emp

(

empid int not null,

empname varchar(10) not null

)

Declare @i int

set @i=1

while (@i<=1000)

begin

insert into emp (empid,empname)

select @i,@i

set @i=@i+1

end

Replication distribution metadata row count.

Select db_id() as dbid,

case

when indid IN (0, 1) then convert (char (12), rows)

else (select rows from dbo.sysindexes i2 where i2.id =  i.id and i2.indid in (0,1)) — ”-”

end as rowcnt,

case

when indid IN (0, 1) then rowmodctr

else convert (bigint, rowmodctr) + (select rowmodctr from dbo.sysindexes i2 where i2.id =  i.id and i2.indid in (0,1))

end as row_mods,

case rows when 0 then 0 else convert (bigint,

case

when indid IN (0, 1) then convert (bigint, rowmodctr)

else rowmodctr + (select convert (bigint, rowmodctr) from dbo.sysindexes i2 where i2.id =  i.id and i2.indid in (0,1))

end / convert (numeric (20,2), (select case convert (bigint, rows) when 0 then 0.01 else rows end from dbo.sysindexes i2 where i2.id =  i.id and i2.indid in (0,1))) * 100)

end as pct_mod,

convert (nvarchar, u.name + ‘.’ + o.name) as objname,

case when i.status&0x800040=0x800040 then ‘AUTOSTATS’

when i.status&0x40=0x40 and i.status&0x800000=0 then ‘STATS’

else ‘INDEX’ end as type,

convert (nvarchar, i.name) as idxname, i.indid,

stats_date (o.id, i.indid) as stats_updated,

case i.status & 0x1000000 when 0 then ‘no’ else ‘*YES*’ end as norecompute,

o.id as objid , rowcnt, i.status

from dbo.sysobjects o, dbo.sysindexes i, dbo.sysusers u

where o.id = i.id and o.uid = u.uid and i.indid between 1 and 254 and o.type = ‘U’

order by pct_mod desc, convert (nvarchar, u.name + ‘.’ + o.name), indid

GO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: