SQL Server Performance issues- From where to start in crises

Leave a comment (0) Go to comments

Performance is the area where a operational DBA spends around 40 % of his/her time and out of this 40 %, 70 % time is used to figure out, what is wrong. To reduce this time, here are query handy SQL statements which will give you some startup where you should to deal with performance  issues.

These are few very basic queries, which should be used, to know,where is the problem

—-This Query will gives you the detailed information about waiting tasks

select a.spid,b.name,a.cmd,a.memusage,a.cpu,a.program_name,a.nt_username,a.loginame,a.hostname,a.status ,


from master.dbo.sysprocesses
a,master.dbo.sysdatabases b
where a.dbid=b.dbid and blocked<>0

/*

This Query will gives you the which database is using the most CPU cycles. In case you
are hosting more than 1 databases on your server and you wanted to
know, which database is most resources */

—— More Used DB

select db_name(dbid), count(dbid) as processes ,sum(cpu) as usage from
sysprocesses group by dbid order by usage desc


—–This Query will gives you the which database is causing the most Blocking.

—  More Blocked DB

select db_name(dbid), count(dbid) as processes ,sum(cpu) as usage from
sysprocesses where blocked != 0 group by dbid order by usage desc

—– Gives the process that causes deadlock

select a.spid,b.name,a.cmd,a.memusage,a.cpu,a.program_name,a.nt_username,a.loginame,a.hostname,a.status

from
master.dbo.sysprocesses a,master.dbo.sysdatabases b

where a.dbid=b.dbid and spid in (select blocked from
master.dbo.sysprocesses where
blocked<>0)

go

—– Processes running for more than 10 Mins

Select sp.spid, sd.name, sp.waittime/(1000
* 60) as wait_time_mins ,

sp.cpu, sp.memusage, sp.login_time, sp.cmd, sp.program_name,
sp.nt_username, sp.loginame,

sp.hostname, sp.status, sp.waitresource,
sp.blocked
from sysprocesses sp, sysdatabases sd

where sp.dbid = sd.dbid and sp.status = ‘runnable’ and
sp.waittime/(1000 * 60) >=
10


—–Gives the waiting tasks

select a.spid,b.name,a.cmd,a.memusage,a.cpu,a.program_name,a.nt_username,a.loginame,a.hostname,a.status

from
master.dbo.sysprocesses a,master.dbo.sysdatabases b where a.dbid=b.dbid and
blocked<>0

go


—– Processes with Runable status

Select sp.spid, sd.name, sp.cmd, sp.memusage, sp.cpu, sp.login_time, sp.program_name,
sp.nt_username, sp.loginame,

sp.hostname, sp.status, sp.waitresource,
sp.waittime from sysprocesses sp, sysdatabases sd

where sp.dbid = sd.dbid and sp.status = ‘runnable’


—– Processes running more than 30 min (All databases)

Select sp.spid, sd.name, sp.cmd, sp.memusage, sp.cpu, sp.login_time, sp.program_name,
sp.nt_username, sp.loginame,

sp.hostname, sp.status, sp.waitresource,
sp.waittime from sysprocesses sp, sysdatabases sd

where sp.dbid = sd.dbid and datediff(minute, sp.login_time, getdate()) >= 30 and sp.status = ‘runnable’

go


—– Processes running more than 30 min

Select sp.spid, sd.name, sp.cmd, sp.memusage, sp.cpu, sp.login_time, sp.program_name,
sp.nt_username, sp.loginame,

sp.hostname, sp.status, sp.waitresource,
sp.waittime from sysprocesses sp, sysdatabases sd

where sp.dbid = sd.dbid and datediff(minute, sp.login_time, getdate()) >= 30 and sp.status = ‘runnable’


———- SQL Server Performance Counters

select * from sysperfinfo
where object_name in
(‘SQLServer:General
Statistics’
,‘SQLServer:Locks’,‘SQLServer:Databases’,‘SQLServer:Access
Methods’
,‘SQLServer:SQL Statistics’,‘SQLServer:Memory
Manager’
)

and
counter_name in (‘User
Connections’
,‘Lock Requests/sec’,‘Lock
Timeouts/sec’
,‘Number of Deadlocks/sec’,‘Average Wait Time
(ms)’
,

‘Active
Transactions’
,‘Transactions/sec’,‘Full
Scans/sec’
,‘Page
Splits/sec’
,‘SQL
Compilations/sec’
,‘SQL
Re-Compilations/sec’
,

‘Lock
Memory (KB)’

)

Notes :

* There might be a possibility that this query will give you error, this is because in this page single quote (‘) is displayed as (‘), which is a different character, please this (‘) in case required.

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near ‘‘’.

** These query are mostly applies to SQL Server 2000 but all will work with 2005 and 2008. In SQL Server 2005 and 2008, we can use DMV to get more info.

EOF - SQL Server Performance issues- From where to start in crises, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.