| |
 |
|
Display SQL Server session hogs
SQL Server Tips by Donald Burleson |
This is one of the many SQL
Server scripts to display SQL Server session details from the book "High
Performance SQL Server DBA".
It has been correctly stated that databases would perform
just fine if no users were ever allowed to log into them. Alas, this is never
the case, so the SQL Server DBA will need to be able to identify the workload
hogs on the server and figure out why they are using more resources than they
should. A general rule of thumb is: if any user session is found using more
than 25% of any resource (CPU, I/O, etc.), their SQL and usage patterns should
be examined in more detail.
To start, it is a good idea to get a quick overview of how
many sessions have been logged into SQL Server, along with counts of how many
are active and inactive. The sess_count
query below will accomplish this task:
select active_processes
=
(select
count(*)
from
master..sysprocesses
where
status = 'runnable'),
inactive_processes =
(select
count(*)
from
master..sysprocesses
where
status <> 'runnable'),
system_processes =
(select
count(*)
from
master..sysprocesses) -
See
code depot for full script
master..sysprocesses
where
(program_name is not null and
program_name not like 'SQLAgent%' and
program_name <> ''))
The results will look like:
active_processes inactive_processes
system_processes
---------------- ------------------ ----------------
1 25 15
From there, the DBA should pinpoint sessions using the
lion's share of resources, as it is not uncommon for 10% of the session base to
be causing severe pain for the other 90% of those trying to use SQL Server.
There are two ways to view this type of data. A good way to accomplish this is
to break out session usage by percentage, making it impossible for session hogs
to escape notice. The following procedure,
up_wl_session_hogs , makes this
easy to accomplish:
IF
OBJECT_ID('up_wl_session_hogs') IS NOT NULL
BEGIN
DROP PROCEDURE up_wl_session_hogs
IF OBJECT_ID('up_wl_session_hogs') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE up_wl_session_hogs >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE up_wl_session_hogs >>>'
END
go
create procedure up_wl_session_hogs
AS
set nocount on
create table #sess_hogs (loadtype varchar(50) NULL,
spid int NULL,
username sysname NULL,
pctused decimal(5,2) NULL)
insert into #sess_hogs
select
top 1 'Top I/O Process',
spid,
user_name = suser_sname(sid),
pct_io_used = convert(decimal(17,2),(100 *
(convert(decimal(17,2),physical_io) /
(select
convert(decimal(17,2),case sum(physical_io) when 0 then 1
else sum(physical_io)
end)
from
master..sysprocesses))))
from
master..sysprocesses
order by
4 desc
insert into #sess_hogs
select
top 1 'Top CPU Process',
spid,
user_name = suser_sname(sid),
pct_cpu_used = convert(decimal(17,2),(100 *
(convert(decimal(17,2), cpu) /
(select
convert(decimal(17,2),case sum(cpu) when 0 then 1 else
sum(cpu) end)
from
master..sysprocesses))))
from
master..sysprocesses
order by
4 desc
insert into #sess_hogs
select
top 1 'Top Memory Process',
spid,
user_name = suser_sname(sid),
pct_mem_used = convert(decimal(17,2),(100 *
(convert(decimal(17,2),memusage) /
(select
convert(decimal(17,2),case sum(memusage) when 0 then 1
else sum(memusage) end)
from
master..sysprocesses))))
from
master..sysprocesses
order by
4 desc
insert into #sess_hogs
select
top 1 'Top Transaction Process',
spid,
user_name = suser_sname(sid),
pct_tran_used = isnull(convert(decimal(17,2),
(100 *
(convert(decimal(17,2),open_tran) /
(select
case when convert(decimal(17,2),sum(open_tran)) = 0 then 1
end
from
master..sysprocesses)))),0)
from
master..sysprocesses
order by
4 desc
select
loadtype,
spid,
username,
pctused
See
code depot for full script
drop table #sess_hogs
go
IF OBJECT_ID('up_wl_session_hogs') IS NOT NULL
PRINT '<<< CREATED PROCEDURE up_wl_session_hogs >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE up_wl_session_hogs >>>'
go
Figure 7.5 is a representation of the query looking for
session resource hogs.
Figure 7.5:
Finding Session Hogs.
Sessions on dynamic systems having accumulated more than 25%
of one resource are candidates for further examination. Of course, large batch
job processes that come and go at predefined intervals may not be a problem, but
consistently connected sessions exhibiting high resource usage are another
matter.
Of course, with SQL Server, it is easy to get the standard
session analysis that gives a quick overview of what each session is doing. The
sess_activity query provides everything
needed to acquire more details on each process connected to SQL Server:
select
spid,
loginame = rtrim(loginame),
windows_user = rtrim(nt_username),
database_name = db_name(dbid),
status = status,
program_name = rtrim(program_name),
mem_bytes = memusage * 8,
cpu,
physical_io,
blocked =
See
code depot for full script
command = cmd,
login_time,
last_batch,
hostprocess,
net_address
from
master..sysprocesses
order by
1
This is one of the many SQL Server
scripts to display SQL Server session details from the book "High
Performance SQL Server DBA".
|
|
|
and include the URL for the page.
