Troubleshooting slow Running Queries in SQL
1) Whenever slow running query comes in to picture we will follow the following technique.
1) Whenever slow running query comes in to picture we will follow the following technique.
We need to identify if this issue is at h/w or s/w level
Most of the times the issue will be with SQL Server (s/w level) if there are no issues at s/w level then we will make use of perfmon counters to identify the long running queries .
Eg: if it is a cpu issue then we can find it out using the following counters
1) %processor time 2)%privileged time (kernel issue)or (H/w issue) 3)%user time (sql or s/w issue)
If the processor time====privileged time then it is h/w issue
If the processor time====user time then it is s/w issue.
1) First we need to identify is there any blocking or locks on databases.(if any then we will check with user or application team and getting permission to kill those process ids) . we can find the blocking info using below query.
Most of the times the issue will be with SQL Server (s/w level) if there are no issues at s/w level then we will make use of perfmon counters to identify the long running queries .
Eg: if it is a cpu issue then we can find it out using the following counters
1) %processor time 2)%privileged time (kernel issue)or (H/w issue) 3)%user time (sql or s/w issue)
If the processor time====privileged time then it is h/w issue
If the processor time====user time then it is s/w issue.
1) First we need to identify is there any blocking or locks on databases.(if any then we will check with user or application team and getting permission to kill those process ids) . we can find the blocking info using below query.
select * from sys.sysprocesses where blocked<>0
2)if there are any deadlocks then we will find that information using error logs because we have enabled 1204 trace flag.
3) We will check for fragmentation levels as it impacts the performance of the query
Select * from sys.dm_db_index_physical_stats(db_id(‘dbname’),object_id(‘objectname’),indexed,partitionedid,mode)
Select * from sys.dm_db_index_physical_stats(db_id(‘india’),object_id(‘tb1’),NULL,NULL,’DETAILED’)
4)We will check for outdated statistics using dbcc show _statistics ,
5) if w are not able to find out any abnormalities then we need to find what is the query being executed which we can found it using traces----these traces will run as jobs in our environment so we can find it using
select * from fn_trace_gettable(‘path,’default’)
(we need to select RPC:completed, SP:stsmtcompleted, SQL:BatchStarting, SQL:BatchCompleted) then we will find out longest running query duration.
6) After getting the query we will check for the indexes if any index is missing then we will intimate the app team to create that index.
