Stored Procedure to Get Expensive Queries

As a DBA we often need to get information on various Queries, One of them is how expensive it is. A query can be judged as expensive on various criteria’s like Long Running, Memory Utilization, CPU utilization etc.

I came up with this Stored Procedure which gives you Expensive Queries based on criteria’s like Duration, Memory, CPU, Read etc.

USE [master]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_GetExpensiveQueries]’) AND type in (N’P’, N’PC’))
DROP PROCEDURE [dbo].[usp_GetExpensiveQueries]
GO
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_GetExpensiveQueries]
@Limit AS INT,
@Database_Name AS VARCHAR(255),
@Expense_Counter INT
AS
/*Variables Used
1. @Limit — No of Recoreds to be Retrieved in Int
2. @Database_Name — Database Name for which the Expense queries needs to be Retrieved in Varchar
3. @Expense_Counter — Criteria on Which the Query Expense needs to judged
Refer Below for Expense Counter
End Variables Used*/
/*Expense Counter
DurTimeAvgMin 0
CPUTimeAvgMin 1
TotalCPUTime 2
TotalDurTime 3
NoPhysicalReads 4
AvgNoPhyscialReads 5
NoLogicalReads 6
AvgNoLogicalReads 7
*/
SELECT Database_name,QueryText,ProcBatTest,PlanGenerationNumber,ExecutionCount,DurTimeAvgMin,
CPUTimeAvgMin,TotalCPUTime,TotalDurTime,NoPhysicalReads,AvgNoPhyscialReads,NoLogicalReads,AvgNoLogicalReads
FROM
(
SELECT TOP (@Limit) DB_NAME(CONVERT (INT, epa.value)) AS [Database_Name],
SUBSTRING(est.text, (eqs.statement_start_offset/2)+1,
        ((CASE eqs.statement_end_offset
         WHEN -1 THEN DATALENGTH(est.text)
         ELSE eqs.statement_end_offset
         END – eqs.statement_start_offset)/2) + 1) AS QueryText,
est.text AS ProcBatTest,        
eqs.plan_generation_num AS PlanGenerationNumber,
eqs.execution_count AS ExecutionCount,
(eqs.total_worker_time/1000) AS TotalCPUTime,
(((eqs.total_worker_time/1000)/eqs.execution_count)/3600) AS CPUTimeAvgMin,
(eqs.total_elapsed_time/1000) AS TotalDurTime,
(((eqs.total_elapsed_time/1000)/eqs.execution_count)/3600) AS DurTimeAvgMin,
eqs.total_physical_reads AS NoPhysicalReads,
(eqs.total_physical_reads/eqs.execution_count) AS AvgNoPhyscialReads,
eqs.total_logical_reads AS NoLogicalReads,
(eqs.total_logical_reads/eqs.execution_count) AS AvgNoLogicalReads,
eqs.last_execution_time AS LastExecutionTime
FROM SYS.DM_EXEC_QUERY_STATS eqs
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(sql_handle) est
CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(plan_handle) eqp
CROSS APPLY SYS.DM_EXEC_PLAN_ATTRIBUTES(eqs.plan_handle) epa
WHERE attribute = ‘dbid’ 
AND DB_NAME(CONVERT (INT, epa.value)) = @Database_Name) x
–and qs.last_execution_time > ‘2011-08-09 17:29:33.750’
–If we want to get queries executed greater than some time
–and (((qs.total_elapsed_time/1000)/qs.execution_count)/3600) >= 2
ORDER BY
–Seems to be Problem with Order By working on the same
–Order By Fixed
CASE
WHEN @Expense_Counter = 0 THEN DurTimeAvgMin
WHEN @Expense_Counter = 1 THEN CPUTimeAvgMin
WHEN @Expense_Counter = 2 THEN TotalCPUTime
WHEN @Expense_Counter = 3 THEN TotalDurTime
WHEN @Expense_Counter = 4 THEN NoPhysicalReads
WHEN @Expense_Counter = 5 THEN AvgNoPhyscialReads
WHEN @Expense_Counter = 6 THEN NoLogicalReads
WHEN @Expense_Counter = 7 THEN AvgNoLogicalReads
END DESC
GO

Advertisements

Stored Procedure to Purge MSDB Job History

Purging your MSDB Job History is sometime very important which helps you read the Jos history in GUI very easily and keeps which only required number of days. You can add this in your Maintenance Plan or using a SSIS Package. I found one of the way a Stored Procedure to do the same. This Procedure accepts a Parameter @DaysToPurge. This is the number of days back for which you want to keep the History. I have used the existing MS SP’s for this tasks.

 

–Drop stored procedure if it already exists
USE [YourDatabaseName]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_PurgeMSDBHistory]’)
AND type in (N’P’, N’PC’))
DROP PROCEDURE [dbo].[usp_PurgeMSDBHistory]
GO
–Create Procedure
–Parameter @DaysToPurge int — This is the number of days back data to be kept
CREATE PROCEDURE usp_PurgeMSDBHistory
@DaysToPurge as int
AS
BEGIN
DECLARE @VarDate DATETIME
DECLARE @DTP as INT
SET @DTP = @DaysToPurge
SELECT @VarDate = CAST((GETDATE() – @DTP) AS DATETIME)
EXEC MSDB.DBO.sp_delete_backuphistory @VarDate
EXEC MSDB.DBO.sp_purge_jobhistory  @oldest_date=@VarDate
EXECUTE MSDB..sp_maintplan_delete_log NULL,NULL,@VarDate
END
GO

Thanks and Regards,

Manjunath C Bhat.

Suggestions and comments are always welcome.