The WordPress.com stats helper monkeys prepared a 2012 annual report for this blog.
Here’s an excerpt:
600 people reached the top of Mt. Everest in 2012. This blog got about 2,100 views in 2012. If every person who reached the top of Mt. Everest viewed this blog, it would have taken 4 years to get that many views.
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
Manjunathcbhat's Blog 