SQL Performance

DBA In A Box we save you money
SQL server database monitoring, troubleshooting, and analyzing software with tools designed to scan, alert, and investigate SQL server failures, performance, SOX compliance, replication failures and much more.

Home
About Us
Screen Shots
Contact Us
Downloads

SQL Scripts
Useful scripts
Auto Manage Indexes
Auto Reindex
Database Mirroring
Check Security

Performance: This lists of queries can assist you in improving database performance

Show SPIDS using Resources: this job takes two snapshots back to back and compares them together. The differences are output. Execute this several times during a crisis to determine what spid may be slowing down the server.

Declare
@datEnd     datetime,
@datStart     datetime,
@iSpidMax     integer,
@sCmdMax     varchar(128),
@sSql          varchar(128),
@divisor     numeric(10,4)

Set NoCount On
-- Take the first Snapshot
Select 1 As SnapNum, spid, kpid, blocked,
    waittime, dbid, uid, cpu,
    physical_io, memusage, login_time, last_batch,
    ecid, status, hostname, program_name,
    cmd, net_address, loginame, GetDate() As SnapTime
Into #SnapShot
From master..sysprocesses
Where uid >= 0

WaitFor Delay '00:00:03'

-- Take the Second Snapshot
Insert Into #SnapShot
Select 2 As SnapNum, spid, kpid, blocked,
    waittime, dbid, uid, cpu,
    physical_io, memusage, login_time, last_batch,
    ecid, status, hostname, program_name,
    cmd, net_address, loginame, GetDate() As SnapTime
    From master..sysprocesses
    Where uid >= 0

Select @datStart = Min( SnapTime ) From #SnapShot Where SnapNum = 1
Select @datEnd = Max( SnapTime ) From #SnapShot Where SnapNum = 2
print DateDiff( ms, @datStart, @datEnd )

-- Get the difference between the 2 snapshots
-- Only select those where snapshot2 CPU > snapshot1 for same SPID.
Select spid,
    Sum( Case SnapNum When 2 Then waittime Else -waittime End ) * 0.1 As WaitPercent,
    Sum( Case SnapNum When 2 Then cpu Else -cpu End ) * 0.1 As CpuPercent,
     sum( case snapNum when 2 then cpu else -cpu END) As Cpu,
    Sum( Case SnapNum When 2 Then physical_io Else -physical_io End ) As DeltaIo
Into #PerfDelta
From #SnapShot
Group By spid
Having Sum( Case SnapNum When 2 Then cpu Else -cpu End ) > 0

-- Get the sum of all Cpu data to comput percent usage

select @Divisor = sum(Cpu) from #PerfDelta
update #PerfDelta
set CpuPercent = (Cpu / @Divisor) * 100

-- Get the command for the top few.
Create Table #DbccOutput ( spid integer, EventInfo varchar(256) )
Create Table #DbccOutputTemp ( EventType varchar(256), Parameters integer, EventInfo varchar(256))
Declare MyCursor Cursor For
Select Top 10 spid From #PerfDelta Order By CpuPercent Desc

Open MyCursor
Fetch Next From MyCursor Into @iSpidMax
While @@Fetch_Status = 0
Begin
Set @sSql = 'DBCC inputbuffer(' + Convert( varchar(3), @iSpidMax ) + ')'
Insert Into #DbccOutputTemp Exec ( @sSql )
Insert Into #DbccOutput ( spid, EventInfo )
    Select @iSpidMax As spid, EventInfo From #DbccOutputTemp
Delete #DbccOutputTemp
Fetch Next From MyCursor Into @iSpidMax
End
Close MyCursor
Deallocate MyCursor

-- Show the results for the cpu hogs.
Set NoCount Off
Select PD.CpuPercent,
    PD.spid, Convert( varchar(25), SS.loginame ),
    DO.EventInfo As Command, SS.program_name,
    SS.hostname,
    ( Select name From master..sysdatabases Where dbid = SS.dbid ),
    SS.status, SS.login_time, SS.last_batch,
    PD.DeltaIo, SS.cmd, SS.net_address
From #PerfDelta As PD Join #SnapShot As SS
      On PD.spid = SS.spid
    Left Join #DbccOutput As DO
      On PD.spid = DO.spid
Where SS.ecid = 0
    And SS.SnapNum = 2
Order By CpuPercent Desc

Drop Table #DbccOutput
Drop Table #DbccOutputTemp

lblEnd:
Drop Table #SnapShot
Drop Table #PerfDelta

Top 100 cached plans by Total Run Time. This sql script will show you poorly performing queries using the most time on your servers.

select top 100 A.database_name as [Database Name] , A.object_name as [Object Name],
A.statement as [Query],
A.total_run_time_in_seconds [Tot. Run Time in seconds],
case when A.FromStart = -1 then ''
                else substring(A.statement,FromStart,1000)
                END as [From Clause],
case when A.WhereStart = -1 then ''
                else substring(A.statement,WhereStart,1000)
                END as [Where Clause],
A.statement_recompiles [Recompiles], A.creation_time [Created], A.last_execution_time [Last Executed],
A.execution_count [Execute Count], A.avg_run_time_in_seconds [Avg Run Time in Seconds],
A.avg_cpu_time_in_seconds [Avg. CPU Time in Seconds], A.avg_wait_time_in_seconds [Avg. Wait Time in Seconds],
A.avg_physical_reads [Avg Physical Reads], A.avg_logical_reads [Avg. Logical Reads],
A.avg_logical_writes [Avg. Logical Writes], A.total_CPU_time [Tot. CPU Time], A.total_CPU_wait_time [Tot. CPU Wait Time],
A.total_physical_reads [Tot. Physical Reads], A.total_logical_reads [Tot. Logical Reads],
A.total_logical_writes [Tot. Logical Writes]
from
(SELECT
      CASE WHEN st.dbid = 32767 THEN 'resourceDb'
                                WHEN st.dbid IS NULL THEN 'NA'
                                ELSE db_name(st.dbid) END as database_name,
   CASE WHEN st.dbid IS NULL THEN 'NA' ELSE object_schema_name(st.objectid, st.dbid) END as object_schema_name,
   CASE WHEN st.dbid IS NULL THEN 'adhoc' ELSE object_name(st.objectid, st.dbid) END as object_name,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)) as statement,
PATINDEX('%from%',SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2))) - 1 as FromStart,
PATINDEX('%where%',SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2))) - 1 as WhereStart,
(qs.plan_generation_num - 1) as statement_recompiles,
   qs.creation_time,
   qs.last_execution_time,
   qs.execution_count,
   cast((qs.total_elapsed_time / qs.execution_count) as numeric(25,6)) / 1000000 as avg_run_time_in_seconds,
                cast(qs.total_elapsed_time as numeric(25,6)) / 1000000 as total_run_time_in_seconds,
   cast((qs.total_worker_time / qs.execution_count) as numeric(25,6)) / 1000000 as avg_cpu_time_in_seconds,
   cast(((qs.total_elapsed_time - qs.total_worker_time) / qs.execution_count) as numeric(25,6)) / 1000000 as avg_wait_time_in_seconds,
   (qs.total_physical_reads / qs.execution_count) as avg_physical_reads,
   (qs.total_logical_writes / qs.execution_count) as avg_logical_writes,
   (qs.total_logical_reads / qs.execution_count) as avg_logical_reads,
   qs.total_worker_time as total_CPU_time,
   (qs.total_elapsed_time - qs.total_worker_time) as total_CPU_wait_time,
   --qs.total_clr_time,
   qs.total_physical_reads,
   qs.total_logical_writes,
   qs.total_logical_reads
FROM
   sys.dm_exec_query_stats qs
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
--where st.dbid = db_id()
) A
order by
total_run_time_in_seconds DESC

Top 100 cached plans by Disk I/O. This will show you poorly performing queries impacting the drives the most.

select top 100 A.database_name as [Database Name] , A.object_name as [Object Name],
A.statement as [Query],
A.avg_io_per_execute [AVG IO per Execute],
case when A.FromStart = -1 then ''
                else substring(A.statement,FromStart,1000)
                END as [From Clause],
case when A.WhereStart = -1 then ''
                else substring(A.statement,WhereStart,1000)
                END as [Where Clause],
A.statement_recompiles [Recompiles], A.creation_time [Created], A.Last_execution_time [Last Executed],
A.execution_count [Execute Count], A.avg_run_time_in_seconds [Avg Run Time in Seconds],
A.avg_cpu_time_in_seconds [Avg. CPU Time in Seconds], A.avg_wait_time_in_seconds [Avg. Wait Time in Seconds],
A.avg_physical_reads [Avg Physical Reads], A.avg_logical_reads [Avg. Logical Reads],
A.avg_logical_writes [Avg. Logical Writes], A.total_CPU_time [Tot. CPU Time], A.total_CPU_wait_time [Tot. CPU Wait Time],
A.total_physical_reads [Tot. Physical Reads], A.total_logical_reads [Tot. Logical Reads],
A.total_logical_writes [Tot. Logical Writes], A.total_run_time_in_seconds [Tot. Run Time in seconds]
from
(SELECT
      CASE WHEN st.dbid = 32767 THEN 'resourceDb'
                                WHEN st.dbid IS NULL THEN 'NA'
                                ELSE db_name(st.dbid) END as database_name,
   CASE WHEN st.dbid IS NULL THEN 'NA' ELSE object_schema_name(st.objectid, st.dbid) END as object_schema_name,
   CASE WHEN st.dbid IS NULL THEN 'adhoc' ELSE object_name(st.objectid, st.dbid) END as object_name,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)) as statement,
PATINDEX('%from%',SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2))) - 1 as FromStart,
PATINDEX('%where%',SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2))) - 1 as WhereStart,
(qs.plan_generation_num - 1) as statement_recompiles,
   qs.creation_time,
   qs.last_execution_time,
   qs.execution_count,
   cast((qs.total_elapsed_time / qs.execution_count) as numeric(25,6)) / 1000000 as avg_run_time_in_seconds,
                cast(qs.total_elapsed_time as numeric(25,6)) / 1000000 as total_run_time_in_seconds,
   cast((qs.total_worker_time / qs.execution_count) as numeric(25,6)) / 1000000 as avg_CPU_time_in_seconds,
   cast(((qs.total_elapsed_time - qs.total_worker_time) / qs.execution_count) as numeric(25,6)) / 1000000 as avg_wait_time_in_seconds,
   (qs.total_physical_reads / qs.execution_count) as avg_physical_reads,
   (qs.total_logical_writes / qs.execution_count) as avg_logical_writes,
   (qs.total_logical_reads / qs.execution_count) as avg_logical_reads,
   qs.total_worker_time as total_CPU_time,
   (qs.total_elapsed_time - qs.total_worker_time) as total_CPU_wait_time,
                cast (((qs.total_logical_reads + qs.total_logical_writes) / qs.execution_count) as numeric(25,6)) as avg_io_per_execute,
      qs.total_physical_reads,
   qs.total_logical_writes,
   qs.total_logical_reads
FROM
   sys.dm_exec_query_stats qs
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st) A
order by
[AVG IO Per Execute] DESC

Top 100 cached plans by Average CPU. This will show you poorly performing queries impacting cpu the most.

select top 100 A.database_name as [Database Name] , A.object_name as [Object Name],
A.statement as [Query],
A.avg_cpu_time_in_seconds [Avg. CPU Time in Seconds],
case when A.FromStart = -1 then ''
                else substring(A.statement,FromStart,1000)
                END as [From Clause],
case when A.WhereStart = -1 then ''
                else substring(A.statement,WhereStart,1000)
                END as [Where Clause],
A.statement_recompiles [Recompiles], A.creation_time [Created], A.Last_execution_time [Last Executed],
A.execution_count [Execute Count], A.avg_run_time_in_seconds [Avg Run Time in Seconds],
A.avg_wait_time_in_seconds [Avg. Wait Time in Seconds],
A.avg_physical_reads [Avg Physical Reads], A.avg_logical_reads [Avg. Logical Reads],
A.avg_logical_writes [Avg. Logical Writes], A.total_CPU_time [Tot. CPU Time], A.total_CPU_wait_time [Tot. CPU Wait Time],
A.total_physical_reads [Tot. Physical Reads], A.total_logical_reads [Tot. Logical Reads],
A.total_logical_writes [Tot. Logical Writes], A.avg_io_per_execute [AVG IO per Execute],
A.total_run_time_in_seconds [Tot. Run Time in seconds]
from
(SELECT
      CASE WHEN st.dbid = 32767 THEN 'resourceDb'
                                WHEN st.dbid IS NULL THEN 'NA'
                                ELSE db_name(st.dbid) END as database_name,
   CASE WHEN st.dbid IS NULL THEN 'NA' ELSE object_schema_name(st.objectid, st.dbid) END as object_schema_name,
   CASE WHEN st.dbid IS NULL THEN 'adhoc' ELSE object_name(st.objectid, st.dbid) END as object_name,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)) as statement,
PATINDEX('%from%',SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2))) - 1 as FromStart,
PATINDEX('%where%',SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2))) - 1 as WhereStart,
(qs.plan_generation_num - 1) as statement_recompiles,
   qs.creation_time,
   qs.last_execution_time,
   qs.execution_count,
   cast((qs.total_elapsed_time / qs.execution_count) as numeric(25,6)) / 1000000 as avg_run_time_in_seconds,
                cast(qs.total_elapsed_time as numeric(25,6)) / 1000000 as total_run_time_in_seconds,
   cast((qs.total_worker_time / qs.execution_count) as numeric(25,6)) / 1000000 as avg_CPU_time_in_seconds,
   cast(((qs.total_elapsed_time - qs.total_worker_time) / qs.execution_count) as numeric(25,6)) / 1000000 as avg_wait_time_in_seconds,
   (qs.total_physical_reads / qs.execution_count) as avg_physical_reads,
   (qs.total_logical_writes / qs.execution_count) as avg_logical_writes,
   (qs.total_logical_reads / qs.execution_count) as avg_logical_reads,
   qs.total_worker_time as total_CPU_time,
   (qs.total_elapsed_time - qs.total_worker_time) as total_CPU_wait_time,
                cast (((qs.total_logical_reads + qs.total_logical_writes) / qs.execution_count) as numeric(25,6)) as avg_io_per_execute,
      qs.total_physical_reads,
   qs.total_logical_writes,
   qs.total_logical_reads
FROM
   sys.dm_exec_query_stats qs
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st) A
order by
[Avg. CPU Time in Seconds] DESC

Home | About Us | Screen Shots | Contact Us | Downloads