SELECT DISTINCT objname = object_name(p.object_id) FROM sys.partitions p JOIN sys.dm_tran_locks t1 ON p.hobt_id = t1.resource_associated_entity_id
Archive of entries posted on May 2009
Find Queries Using Most CPU
SELECT TOP 5 object_name(objectID) ,[Avg CPU Time] = total_worker_time/execution_count ,execution_count ,Plan_handle ,query_plan FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) ORDER BY total_worker_time/execution_count DESC;
Find Most Executed Queries
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
Find Most Blocked Queries
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
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 [...]
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 ‘@job_name’, ‘@job_id’, @job_name [...]
SQL Server Endpoints
Endpoints are essentially web services that expose database access over HTTP. Aside from architectural design decisions, these are useful is that your application development team does not have to have management studio access to SQL Server in order to begin development. The WSDL generated shows the parameters required for the endpoint. There are a few [...]
Reporting Services Scale-Out Setup with Kerberos Delegation
A common configuration for SQL Reporting Services is to use a scale-out setup. The reason for this is the performance of the rendering (or pagination) of the reports is relatively processor intensive (at the time of this article SQL Server 2005) Along with this setup comes an intrinsic problem. By default, SQL Reporting Services uses [...]
Find User Connection Count
– Show users with highest connections SELECT login_name, session_count FROM( SELECT login_name ,COUNT(session_id) AS session_count FROM sys.dm_exec_sessions GROUP BY login_name ) t ORDER BY session_count desc In SQL Server 2008 you can also find out how many connections have been created to SQL Server since the last time it got restarted: SELECT @@Connections
