Troubleshoot a Stored Procedure

Hello SQL Folks – I observed multiple times when a stored procedure in your SQL Server instance is running slow and creating typical performance problems. Sometimes it feels a bit hard to troubleshoot it and situation become worse when it’s a case of nested stored procedure OR SP itself contains thousand lines of code.

To simplify this problem, I wrote a below query which will help you to identify, only time consuming part OR problematic part of that SP with its execution plan, moreover this is the only execution plan for time consuming part of that SP and not a full execution plan of SP. In the below query output column name as text filter is a column which shows different T-SQL\batches that runs inside a Stored Procedure. Also it shows Avg. run time, Last run time and min. run time of each T-SQL\batch.

By using below script it will make your life easier to concentrate only on problematic part instead to troubleshoot full SP.

 

Select top 50

qs.plan_handle

, st.text

,substring (st.text, (qs.statement_start_offset/2)+1,

((case qs.statement_end_offset

when 1 then datalength(st.text)

else qs.statement_end_offset

end qs.statement_start_offset)/2) + 1) as text_filters

,(qs.total_worker_time)/1000.0 as [Total CPU (ms)]

,(qs.total_worker_time/qs.execution_count)/1000.0 as [Avg CPU Time (ms)]

,(qs.total_elapsed_time)/1000.0 as [Total Duration (ms)]

,(qs.total_elapsed_time/qs.execution_count)/1000.0 as [Avg Duration (ms)]

,(qs.last_elapsed_time)/1000.0 as [Last Duration (ms)]

,(qs.min_elapsed_time)/1000.0 as [Min Duration (ms)]

,(qs.max_elapsed_time)/1000.0 as [Max Duration (ms)]

,qs.last_execution_time as [Last Execution]

,execution_count as [#Executions]

,cast (qp.query_plan as xml) as Query_Plan

from sys.dm_exec_query_stats as qs

cross apply sys.dm_exec_sql_text (qs.sql_handle) as st

cross apply sys.dm_exec_text_query_plan (qs.plan_handle, qs.statement_start_offset,

qs.statement_end_offset) as qp

where st.text like ‘% <Stored_Procedure_Name> %’

order by [Total CPU (ms)] desc

 

Hope it will help you. Stay tune for more upcoming interesting solutions on complex SQL performance issues.

Cheers – Gaurav

Advertisements

Implementing Resource Governor to limit the CPU usage by Backup Compression

HI SQL Folks : As we all know that backup compression is resource intensive operation, to get rid of this problem we can implement Resource Governor (RG) in our environment and RG can take care of job to limit the CPU on it. I’m showing this demo on m\c which is having multiple CPU’s on it. I’m using SQL Server 2008 R2 build 10.50.4266 on win 2008 R2 SP1.

1. If we run the backup compression w\o RG then it consumes 90 % + of total CPU. I ran it and below is the Perfmon output (You need to select two counters in permon name as SQL Server : resource Pool stats and SQL Server : Workload group stats) As you can see in below that a Blue Line (CPU Usage by default group) is consuming most of the CPU.

             RG0

2.  Now I’m going to create and configure Resource Governor by using below scripts :

———————————————–

 Step 1: Create Resource Pool

———————————————–

CREATE RESOURCE POOL pSlow

WITH (MIN_CPU_PERCENT=0,

MAX_CPU_PERCENT= 30,

MIN_MEMORY_PERCENT=0,

MAX_MEMORY_PERCENT=30)

GO

———————————————–

Step 2: Creating Workload Group

———————————————–

CREATE WORKLOAD GROUP gSlow

USING pSlow

—————————————————

Step 3: Creating user defined function which routes the workload to the appropriate workload group.

—————————————————

/*In this example we are checking SUSER_NAME() and making the decision of Workgroup selection.

We can use other functions such as HOST_NAME(), APP_NAME(), IS_MEMBER() etc. */

CREATE FUNCTION f1()

RETURNS SYSNAME WITH SCHEMABINDING

BEGIN

 

DECLARE @val sysname

if ‘UserSlow’ = SUSER_SNAME()

SET @val = ‘gSlow’;

else

SET @val = ‘default’;

return @val;

END

GO

—————————————————

Create logins, assign function and make changes effective

—————————————————

CREATE LOGIN UserSlow WITH PASSWORD=’UserSlowPwd’ , CHECK_POLICY=OFF

GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.f1)

GO

ALTER RESOURCE GOVERNOR RECONFIGURE

3. Resource Governor has been setup at server level, Now I need to make a job that will take a backup with compression option :

Before taking a backup of database, we need to add that login as user in peculiar database (for which we’re taking the backup) and need to assign the permission :

Use <db_name>

go

sp_addrolemember  ‘db_backupoperator’, ‘UserSlowPwd’

go

 

Backup database <db_name>

Go

to disk = ‘D:\abc.bak’

with compression

Save this script say as SQLQuery2.sql and make a SQL job and put below command in job to execute this script

sqlcmd -S <Server_Name>-U UserSlow -P UserSlowPwd -i “I:\Perf Data By Gaurav\SQLQuery2.sql”

4. Since I assigned 30 % CPU to this RG i.e. whenever any query running by this peculiar login (UserSlow) then engine will only assign 30 % of single CPU to it.

Beauty of the RG is, if no workload is there on the system then it can maximum utilize a single CPU that is assigned to it (i.e. if 4 CPU’s are there and out of them 1 CPU is assigned to RG then it an use 90 % of that single CPU, it’ll not touch other 3 CPU’s), if workload is there then it will use only 30 % of single CPU that is assigned to it.

To prove this point I ran a backup compression job (now job is using RG that’s having limit on CPU) and created an initial small load on system. As you can in below perfmon values, default load that I created; highlighted in blue line ; using 30 % of CPU but since load is less, my RG is consuming 70 % of total CPU.

RG1

5. Now I increased the load on system by running below script in query analyzer :

set nocount on

declare @i int

declare @s varchar(100)

 set @i = 100000000

 while @i > 0

begin 

select @s = @@version;

set @i = @i – 1;

 end

and as you can see in below reading that RG started to release CPU to other queries. Now RG (highlighted with yellow) is consuming less than 60 % CPU (pre. was using 70 %) and other load started to consuming 45 % CPU (pre. was using 30 %)

RG2

6. To prove a point that RG is using a separate CPU, I fired below query :

select r.session_id, CONVERT(NCHAR(20), wg.name) as group_name, t.scheduler_id,r.status

from sys.dm_exec_requests r join sys.dm_os_tasks t on r.task_address = t.task_address

join sys.dm_resource_governor_workload_groups wg on r.group_id = wg.group_id

where r.session_id > 50

In below output of the query you can see, RG group “gSlow” is using different scheduler (CPU no. 3) and other workload are using CPU 0\1\2.

RG4

Hope it’ll  help someone. Thanks.

Script to identify heavily used tables and indexes in your database

Hi SQL Server Folks – There are many situation comes when we have a requirement in SQL Server to identify heavily used indexes in a database OR heavily used tables in a database. Below are the queries that can help you to find these things in SQL 2005\2008\R2.

Query to find heavily used indexes in your database :

Use database_name
go

SELECT CAST(OBJECT_NAME(usg.OBJECT_ID) AS VARCHAR(30)) AS ‘Table’,
CAST(idx.name AS VARCHAR(30)) AS ‘Index’,
usg.user_seeks + usg.user_scans + usg.user_lookups AS ‘Reads’,
usg.user_updates AS ‘Writes’
FROM sys.dm_db_index_usage_stats AS usg
INNER join sys.indexes AS idx
ON usg.OBJECT_ID = idx.OBJECT_ID
and idx.index_id = usg.index_id
WHERE OBJECTPROPERTY(usg.OBJECT_ID,’isusertable’) = 1
ORDER BY ‘Table’;
go

Query to find heavily used tables in your database :

Use database_name
go

IF OBJECT_ID(‘tempdb..#Temp’) IS NOT NULL
DROP TABLE #Temp
GO

CREATE TABLE #Temp
(TableName NVARCHAR(255), UserSeeks DEC, UserScans DEC, UserUpdates DEC)
INSERT INTO #Temp
EXEC sp_MSForEachDB ‘USE [?]; IF DB_ID(”?”) = 6
BEGIN
SELECT DB_NAME() + ”.” + object_name(b.object_id), a.user_seeks, a.user_scans, a.user_updates
FROM sys.dm_db_index_usage_stats a
RIGHT OUTER JOIN [?].sys.indexes b on a.object_id = b.object_id and a.database_id = DB_ID()
WHERE b.object_id > 100
END’

SELECT TableName as ‘Table Name’, sum(UserSeeks + UserScans + UserUpdates) as ‘Total Accesses’,
sum(UserUpdates) as ‘Total Writes’,
CONVERT(DEC(25,2),(sum(UserUpdates)/sum(UserSeeks + UserScans + UserUpdates)*100)) as ‘% Accesses are Writes’,
sum(UserSeeks + UserScans) as ‘Total Reads’,
CONVERT(DEC(25,2),(sum(UserSeeks + UserScans)/sum(UserSeeks + UserScans + UserUpdates)*100)) as ‘% Accesses are Reads’,
SUM(UserSeeks) as ‘Read Seeks’, CONVERT(DEC(25,2),(SUM(UserSeeks)/sum(UserSeeks + UserScans)*100)) as ‘% Reads are Index Seeks’,
SUM(UserScans) as ‘Read Scans’, CONVERT(DEC(25,2),(SUM(UserScans)/sum(UserSeeks + UserScans)*100)) as ‘% Reads are Index Scans’
FROM #Temp
GROUP by TableName
HAVING sum(UserSeeks + UserScans) > 0
ORDER by sum(UserSeeks + UserScans + UserUpdates) DESC
DROP table #Temp

Note :

This script makes use of the sys.dm_db_index_usage_stats SQL Dynamic Management View. The values returned by this DMV do not persist beyond server restarts. This means the information it gathers is only valid since the last SQL Server restart or (less likely) database attach. So, if you just re-started your database server you are not going to get good numbers. Also, this information is kept in cache and is subject to memory pressure flushing, not a likely scenario but possible.

The script will gather index information from which one can infer table access information. It uses the sp_MSForEachDB stored procedure to run through all databases on the instance, places the information in a temporary table, sums the values for index reads and writes, does a bunch of fancy math, rolls it up to the table level and returns the results to show reads, writes, percent of each and the type of index read.

Hope you’ll find this as useful. Thanks.

What is Hypothetical Index and how to delete it

Hi Folks,

In this post I’m going to tell you what is Hypothetical Index and how to remove them :

When Database Engine Tuning Advisor runs on a database, it makes some recommendations. For all the recommendations it actually creates the objects on the database. The recommendations can be Indexes, Stats etc. They are called Hypothetical Indexes/Statistics. If Database Engine Tuning Advisor is closed normally, it ensures that all the objects that it had created are cleaned up. If for some reason, it does not exit normally, then these objects will still remain on the database.

Since these objects are hypothetical, they can be removed manually. These objects’ name will start with “_dta”. They can also be identified with the below query.

select * from sys.indexes where is_hypothetical = 1

To delete these type of indexes use below command :

USE <My Database>
go

DROP INDEX  <Schema_Name> . <Table_Name> . <Index_Name>
go

Cheers :
Gaurav Shrivastav

Query to Find out Exact Duplicate Indexes in SQL Server

Hi Folks,

Below is the query to find out exact duplicate indexes in a database. This query finds exact matches. The indexes must have the same key columns in the same order, and the same included columns but in any order. These indexes are sure targets for elimination. The only caution would be to check for index hints.

 

— exact duplicates
with indexcols as
(
select object_id as id, index_id as indid, name,
(select case keyno when 0 then NULL else colid end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by keyno, colid
for xml path(”)) as cols,
(select case keyno when 0 then colid else NULL end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by colid
for xml path(”)) as inc
from sys.indexes as i
)
select
object_schema_name(c1.id) + ‘.’ + object_name(c1.id) as ‘table’,
c1.name as ‘index’,
c2.name as ‘exactduplicate’
from indexcols as c1
join indexcols as c2
on c1.id = c2.id
and c1.indid < c2.indid
and c1.cols = c2.cols
and c1.inc = c2.inc;

Hope it’ll be usefull !!

Cheers,
Gaurav Shrivastav
 

Query to find out Un-used Indexes in SQL Server

Hi Folks,

You all knows that to improve the seraching mechanism in SQL Server indexes plays a Vital role but sometimes to improve the performance its necessary to delete OR offline the existing indexes. The Indexes that are not frequently used OR a table that is having more writes compare than Reads then you should delete those indexes OR offline them , Becuase whenever you are performing a write statement then SQL Server also updates the index pages which requires resources to do that and its time consuming, result in bad Performance. Below is the query that will guide you to find out un-used indexes.

 

SELECT
sch.name + ‘.’ + t.name AS [Table Name],
i.name AS [Index Name],
i.type_desc,
ISNULL(user_updates,0) AS [Total Writes],
ISNULL(user_seeks + user_scans + user_lookups,0) AS [Total Reads],
s.last_user_seek,
s.last_user_scan ,
s.last_user_lookup,
ISNULL(user_updates,0) – ISNULL((user_seeks + user_scans + user_lookups),0) AS [Difference],
p.reserved_page_count * 8.0 / 1024 as SpaceInMB
FROM sys.indexes AS i WITH (NOLOCK)
LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s WITH (NOLOCK) ON s.object_id = i.object_id  AND i.index_id = s.index_id  AND s.database_id=db_id()  AND objectproperty(s.object_id,’IsUserTable’) = 1
INNER JOIN sys.tables AS t WITH (NOLOCK) ON i.object_id = t.object_id
INNER JOIN sys.schemas AS sch WITH (NOLOCK) ON t.schema_id = sch.schema_id
LEFT OUTER JOIN sys.dm_db_partition_stats AS p WITH (NOLOCK) ON i.index_id = p.index_id and i.object_id = p.object_id
WHERE (1=1)
–AND ISNULL(user_updates,0) >= ISNULL((user_seeks + user_scans + user_lookups),0) –shows all indexes including those that have not been used
–AND ISNULL(user_updates,0) – ISNULL((user_seeks + user_scans + user_lookups),0)>0 –only shows those indexes which have been used
–AND i.index_id > 1 — Only non-first indexes (I.E. non-primary key)
–AND i.is_primary_key<>1 — Only those that are not defined as a Primary Key)
–AND i.is_unique_constraint<>1 — Only those that are not classed as “UniqueConstraints”.
ORDER BY [Table Name], [index name]

 

Hope it will be usefull for you !!!

Thanks.

Top Queries in the SQL that is consuming most of the memory of Server

Hi SQL Server Guys,

It has been many times happens when performance of the SQL Server goes down, Then we are trying to find out out the cause of this. Frankly say there can be multiple issues for that which can cause for performance but one area is that where we can find out what are the queries that is currently running in the SQL Server and they only causing the problem.

Below are the queries that help you to find out memory contentation in SQL Server.

/* This query returns back the queries that use the most IO. This can mean that either the query is reading from disk more than usual or occupying and utilizing a large amount of buffer cache. These are typical symptoms of queries that do not have the proper indexesor queries that simply read a lot of data.*/

/**********************************************************

* Top procedures memory consumption per execution

* (this will show mostly reports & jobs)

***********************************************************/

SELECT TOP 100 *FROM

( SELECT DatabaseName =DB_NAME(qt.dbid)

,ObjectName  =OBJECT_SCHEMA_NAME(qt.objectid,dbid)+‘.’+OBJECT_NAME(qt.objectid, qt.dbid)

,DiskReads  =SUM(qs.total_physical_reads)   — The worst reads, disk reads

,MemoryReads  =SUM(qs.total_logical_reads)    –Logical Reads are memory reads

,Executions =SUM(qs.execution_count)

,IO_Per_Execution   =SUM((qs.total_physical_reads + qs.total_logical_reads)/ qs.execution_count)

,CPUTime    =SUM(qs.total_worker_time)

,DiskWaitAndCPUTime =SUM(qs.total_elapsed_time)

,MemoryWrites =SUM(qs.max_logical_writes)

,DateLastExecuted   =MAX(qs.last_execution_time)

FROM sys.dm_exec_query_statsAS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)AS qt

GROUP BY DB_NAME(qt.dbid),OBJECT_SCHEMA_NAME(qt.objectid,dbid)+‘.’+OBJECT_NAME(qt.objectid, qt.dbid)

)T ORDERBY IO_Per_Execution DESC

/**********************************************************

* Top procedures memory consumption total

* (this will show more operational procedures)

***********************************************************/

SELECT

TOP 100 *FROM

(

SELECT DatabaseName =DB_NAME(qt.dbid)

,ObjectName =OBJECT_SCHEMA_NAME(qt.objectid,dbid)+‘.’+OBJECT_NAME(qt.objectid, qt.dbid)

,DiskReads  =SUM(qs.total_physical_reads)   — The worst reads, disk reads

,MemoryReads  =SUM(qs.total_logical_reads)    –Logical Reads are memory reads

,Total_IO_Reads     =SUM(qs.total_physical_reads + qs.total_logical_reads)

,Executions =SUM(qs.execution_count)

,IO_Per_Execution   =SUM((qs.total_physical_reads + qs.total_logical_reads)/ qs.execution_count)

,CPUTime    =SUM(qs.total_worker_time)

,DiskWaitAndCPUTime =SUM(qs.total_elapsed_time)

,MemoryWrites =SUM(qs.max_logical_writes)

,DateLastExecuted   =MAX(qs.last_execution_time)

FROM

sys.dm_exec_query_statsAS qs

CROSS

APPLYsys.dm_exec_sql_text(qs.sql_handle)AS qt

GROUP

BYDB_NAME(qt.dbid),OBJECT_SCHEMA_NAME(qt.objectid,dbid)+‘.’+OBJECT_NAME(qt.objectid, qt.dbid)

)

T ORDERBY Total_IO_Reads DESC

/**********************************************************

* Top adhoc queries memory consumption total

***********************************************************/

SELECT

TOP 100 *FROM

(

SELECT DatabaseName =DB_NAME(qt.dbid)

,QueryText  = qt.text

,DiskReads  =SUM(qs.total_physical_reads)   — The worst reads, disk reads

,MemoryReads  =SUM(qs.total_logical_reads)    –Logical Reads are memory reads

,Total_IO_Reads     =SUM(qs.total_physical_reads + qs.total_logical_reads)

,Executions =SUM(qs.execution_count)

,IO_Per_Execution   =SUM((qs.total_physical_reads + qs.total_logical_reads)/ qs.execution_count)

,CPUTime    =SUM(qs.total_worker_time)

,DiskWaitAndCPUTime =SUM(qs.total_elapsed_time)

,MemoryWrites =SUM(qs.max_logical_writes)

,DateLastExecuted   =MAX(qs.last_execution_time)


FROM

sys.dm_exec_query_statsAS qs

CROSS

APPLYsys.dm_exec_sql_text(qs.sql_handle)AS qt

WHERE

OBJECT_SCHEMA_NAME(qt.objectid,dbid)+‘.’+OBJECT_NAME(qt.objectid, qt.dbid)ISNULL

GROUP

BYDB_NAME(qt.dbid), qt.text,OBJECT_SCHEMA_NAME(qt.objectid,dbid)+‘.’+OBJECT_NAME(qt.objectid, qt.dbid)

)

T ORDERBY Total_IO_Reads DESC

/**********************************************************

* Top adhoc queries memory consumption per execution

***********************************************************/

SELECT

TOP 100 *FROM

(

SELECT DatabaseName =DB_NAME(qt.dbid)

,QueryText  = qt.text

,DiskReads  =SUM(qs.total_physical_reads)   — The worst reads, disk reads

,MemoryReads  =SUM(qs.total_logical_reads)    –Logical Reads are memory reads

,Total_IO_Reads     =SUM(qs.total_physical_reads + qs.total_logical_reads)

,Executions =SUM(qs.execution_count)

,IO_Per_Execution   =SUM((qs.total_physical_reads + qs.total_logical_reads)/ qs.execution_count)

,CPUTime    =SUM(qs.total_worker_time)

,DiskWaitAndCPUTime =SUM(qs.total_elapsed_time)

,MemoryWrites =SUM(qs.max_logical_writes)

,DateLastExecuted   =MAX(qs.last_execution_time)


FROM

sys.dm_exec_query_statsAS qs

CROSS

APPLYsys.dm_exec_sql_text(qs.sql_handle)AS qt

WHERE

OBJECT_SCHEMA_NAME(qt.objectid,dbid)+‘.’+OBJECT_NAME(qt.objectid, qt.dbid)ISNULL

GROUP

BYDB_NAME(qt.dbid), qt.text,OBJECT_SCHEMA_NAME(qt.objectid,dbid)+‘.’+OBJECT_NAME(qt.objectid, qt.dbid)

)

T ORDERBY IO_Per_Execution DESC

 

Hope it will be usefull.