 |
|
SQL Server workload script
SQL Server Tips by Burleson Consulting |
This is one of the many SQL
Server Windows scripts to display database workload, from the book "High
Performance SQL Server DBA".
SQL Server, unlike Oracle, is a shared environment in which
many databases compete for memory and background process (lazy writer, etc.)
attention. This being the case, it is smart to identify if any databases are
using the lion’s share of resources. Pinpointing these workload hogs can help
direct the DBA to the root cause of any sluggishness exhibited by SQL Server.
For databases, the process begins by a review of overall
resource consumption and activity. In this process, the DBA is looking for any
databases that seem to stand out from the others in terms of overall usage and
dynamics. On the resource usage front, a good query to use for this process is
the dbusage_overview.sql query:
select
a.name,
connections = (select
count(*)
from
master..sysprocesses b
where
a.dbid = b.dbid),
blocked_users = (select
count(*)
from
master..sysprocesses b
where
a.dbid = b.dbid and
blocked <> 0),
total_memory = isnull((select sum(memusage)
from
master..sysprocesses b
where
a.dbid = b.dbid),0),
total_io = isnull((select sum(physical_io)
from
master..sysprocesses b
where
a.dbid = b.dbid),0),
total_cpu = isnull((select sum(cpu)
from
master..sysprocesses b
where
a.dbid = b.dbid),0),
total_waittime = isnull((select sum(waittime)
from
master..sysprocesses b
where
a.dbid = b.dbid),0),
dbccs = isnull((select count(*)
from
master..sysprocesses b
where
a.dbid = b.dbid and
upper(b.cmd) like '%DBCC%'),0),
bcp_running = isnull((select count(*)
from
master..sysprocesses b
where
a.dbid = b.dbid and
upper(b.cmd) like '%BCP%'),0),
backup_restore_running = isnull((select count(*)
from
master..sysprocesses b
where
a.dbid = b.dbid and
upper(b.cmd) like '%BACKUP%' or
upper(b.cmd) like '%RESTORE%'),0)
from
master.dbo.sysdatabases a
See
code depot for full script
Figure 7.2 is a representation of the results of the SQL
Server workload analysis.
Figure 7.2:
Sample output showing overview of database
resource usage.
The query does a nice job of showing which databases are
currently responsible for the most resource usage. For example, Figure 7.2
shows that the MASTER database has experienced the most wait time, while the
MSDB database has used the most CPU.
After performing the above query, the DBA can then try to
get a handle on the types of activities that have gone on in each database by
issuing the dbactivity_overview query:
select DB =
a.instance_name,
'DBCC Logical Scans' = a.cntr_value,
'Transactions/sec' = (select d.cntr_value
from
master..sysperfinfo d
where
d.object_name = a.object_name and
d.instance_name = a.instance_name and
d.counter_name = 'Transactions/sec'),
'Active Transactions' = (select case when i.cntr_value < 0 then 0
else i.cntr_value end
from
master..sysperfinfo i
where
i.object_name = a.object_name and
i.instance_name = a.instance_name and
i.counter_name = 'Active Transactions'),
'Bulk Copy Rows' = (select b.cntr_value
from
master..sysperfinfo b
where
b.object_name = a.object_name and
b.instance_name = a.instance_name and
b.counter_name = 'Bulk Copy Rows/sec'),
'Bulk Copy Throughput'= (select c.cntr_value
from
master..sysperfinfo c
where
c.object_name = a.object_name and
c.instance_name = a.instance_name and
c.counter_name = 'Bulk Copy
Throughput/sec'),
'Log Cache Reads' = (select e.cntr_value
from
master..sysperfinfo e
where
e.object_name = a.object_name and
e.instance_name = a.instance_name and
e.counter_name = 'Log Cache Reads/sec'),
'Log Flushes' = (select f.cntr_value
from
master..sysperfinfo f
where
f.object_name = a.object_name and
f.instance_name = a.instance_name and
f.counter_name = 'Log Flushes/sec'),
'Log Growths' = (select g.cntr_value
from
master..sysperfinfo g
where
g.object_name = a.object_name and
g.instance_name = a.instance_name and
g.counter_name = 'Log Growths'),
'Log Shrinks' = (select h.cntr_value
from
master..sysperfinfo h
where
h.object_name = a.object_name and
h.instance_name = a.instance_name and
h.counter_name = 'Log Shrinks')
from
master..sysperfinfo a
where
a.object_name like '%Databases%' and
See
code depot for full script
Figure 7.3 is a representation of the results of the above
query showing an overview of SQL Server database activity.
Figure 7.3:
Overview of SQL Server database activity.
This is one of the many SQL Server
scripts to display activity & workloads from the book "High
Performance SQL Server DBA".
