Find Most Executed Queries

This DMV will show the actual queries sorted by highest execution count since the last time SQL Server was restarted. These queries could be either adhoc or queries that reside within stored procedures. These are important because often times optimizing these will yield huge results in performance. In order to find specific procedures that have been executed a lot, refer to the Most Executed Procedures DMV. SELECT TOP 50         qs.execution_count         ,OBJECT_NAME(objectid)         ,query_text = SUBSTRING(                     qt.text,                     qs.statement_start_offset/2,                              (CASE WHEN qs.statement_end_offset = -1                         THEN len(CONVERT(nvarchar(MAX), qt.text)) * 2                         ELSE qs.statement_end_offset                         END - qs.statement_start_offset)/2)         ,qt.dbid         ,dbname = db_name(qt.dbid)         ,qt.objectid FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY qs.execution_count DESC Continue reading ...

Find Most Blocked Queries

While this query will expose the queries that are most blocked, it is somewhat of an guestimate. What shows up from this query is the procedures whose wait times cannot be explained by looking at the total CPU cycles. While this will mostly infer blocking, it doesn’t necessarily guarantee that’s the issue. It can also show procedures that suffer from other wait types (disk, network, clr, parallelism, etc). SELECT     OBJECT_NAME(objectid)     ,BlockTime = total_elapsed_time - total_worker_time     ,execution_count     ,total_logical_reads FROM sys.dm_exec_query_stats qs CROSS apply sys.dm_exec_sql_text(qs.sql_handle) ORDER BY total_elapsed_time - total_worker_time DESC

Find Query Reuse (Execution Plan Reuse)

-- find counts of query re-use SELECT * FROM ( SELECT *, (SELECT object_name(objectid) FROM sys.dm_exec_sql_text(p.plan_handle)) AS SNAME  FROM sys.dm_exec_cached_plans p  WHERE usecounts <= 1 --and objtype != 'adhoc' --and objtype != 'prepared' ) t WHERE SNAME IS NOT NULL  ORDER BY usecounts, size_in_bytes DESC Continue reading ...

Find Table Fragmentation

DECLARE @DATABASE   VARCHAR(255) DECLARE @TableName  VARCHAR(255) DECLARE @IndexName  VARCHAR(255) SET @DATABASE   = 'SQLServerPlanet' SET @TableName  = 'Users' SET @IndexName  = NULL SELECT     avg_fragmentation_in_percent     ,page_count FROM sys.dm_db_index_physical_stats (     DB_ID(@DATABASE)     ,OBJECT_ID(@TableName)     ,OBJECT_ID(@IndexName)     ,NULL     ,NULL ) Or you can still do it the old fashioned way. Just substitute the name of the table or index below. It should not be in quotes.   –Just the table DBCC SHOWCONTIG (tablename) –Table with the index DBCC SHOWCONTIG (tablename, indexname) –Do not block anything (run during prod hours) DBCC SHOWCONTIG (tablename, indexname) WITH FAST –(2005)  

Disable All SQL Server Jobs

CREATE TABLE #Job_Names (  Job_Name SYSNAME NOT NULL ) INSERT INTO #Job_Names SELECT name FROM msdb.dbo.sysjobs ORDER BY name DECLARE @job_name SYSNAME DECLARE @job_id UNIQUEIDENTIFIER DECLARE disable_jobs CURSOR FOR SELECT Job_Name FROM #Job_Names SET @job_id = NULL OPEN disable_jobs FETCH NEXT FROM disable_jobs INTO @job_name WHILE @@FETCH_STATUS = 0 BEGIN  EXEC msdb.dbo.sp_verify_job_identifiers [email protected]_name', [email protected]_id', @job_name OUTPUT, @job_id OUTPUT  EXEC msdb.dbo.sp_update_job @job_id, @enabled = 0    SET @job_id = NULL  FETCH NEXT FROM disable_jobs INTO @job_name END CLOSE disable_jobs DEALLOCATE disable_jobs DROP TABLE #Job_Names Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!