Post date: Apr 15, 2010 4:38:37 PM
http://wiki.lessthandot.com/index.php/Getting_your_Buffer_cache_hit_ratio_in_SQL_Server_2005_and_up
Getting your Buffer cache hit ratio in SQL Server 2005 and up
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 AS BufferCacheHitRatio FROM sys.dm_os_performance_counters aJOIN (SELECT cntr_value,OBJECT_NAME FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio base' AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON a.OBJECT_NAME = b.OBJECT_NAMEWHERE a.counter_name = 'Buffer cache hit ratio'AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'
Display stored procedures with the highest average CPU time in SQL Server
SELECT TOP 50 * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName,execution_count,s2.objectid, (SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 , ( (CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement, s1.total_worker_time/s1.execution_count AS AverageCPUTime FROM sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x WHERE sql_statement NOT like 'SELECT * FROM(SELECT coalesce(object_name(s2.objectid)%'and OBJECTPROPERTYEX(x.objectid,'IsProcedure') = 1and exists (SELECT 1 FROM sys.procedures sWHERE s.is_ms_shipped = 0and s.name = x.ProcName )ORDER BY AverageCPUTime DESC
Display the 50 most used stored procedures in SQL Server
SELECT TOP 50 * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName,execution_count,s2.objectid, (SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 , ( (CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement, last_execution_time FROM sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x WHERE sql_statement NOT like 'SELECT * FROM(SELECT coalesce(object_name(s2.objectid)%'and OBJECTPROPERTYEX(x.objectid,'IsProcedure') = 1and exists (SELECT 1 FROM sys.procedures sWHERE s.is_ms_shipped = 0and s.name = x.ProcName )ORDER BY execution_count DESC