Useful query
Create function to check SQL instance CPU load
create function check_cpu_load ()
returns table
as
return
(
SELECT SQLProcessUtilization,
100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization
FROM (
SELECT
record.value('(./Record/@id)[1]','int')AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]','int')AS SystemIdle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int')AS SQLProcessUtilization
FROM (
SELECTtop 10 CONVERT(XML, record)AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type =N'RING_BUFFER_SCHEDULER_MONITOR' order by timestamp desc)AS x
) AS y
)
go
select SQLProcessUtilization,OtherProcessUtilization fromdba_admin.dbo.check_cpu_load()
Check SQL Server CPU status
Check CPU bottleneck
/* Monitor the SQL Server schedulers using the sys.dm_os_schedulers view to see if the number of runnable tasks is typically nonzero.
A nonzero value indicates that tasks have to wait for their time slice to run;
high values for this counter are a symptom of a CPU bottleneck.
You can use the following query to list all the schedulers and look at the number of runnable tasks.
*/
select scheduler_id, current_tasks_count,runnable_tasks_count
from sys.dm_os_schedulers
where scheduler_id < 255
/*
The following query gives you a high-level view of which currently cached batches or procedures are using the most CPU.
The query aggregates the CPU consumed by all statements with the same plan__handle (meaning that they are part of the same batch or procedure).
If a given plan_handle has more than one statement, you may have to drill in further to find
the specific query that is the largest contributor to the overall CPU usage.
*/
select top 50
sum(qs.total_worker_time) as total_cpu_time,
sum(qs.execution_count) as total_execution_count,
count(*) as number_of_statements,
s.text
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.plan_handle) s
group by s.text
order by sum(qs.total_worker_time) desc
/*
When you use the sys.dm_exec_query_optimizer_info DMV, you can get a good idea of the time SQL Server spends optimizing.
If you take two snapshots of this DMV, you can get a good feel for the time that is spent optimizing in the given time period.
*/
/*
Viewing the total number of optimizations, How many optimizations are performed?
*/
SELECT occurrence AS Optimizations
FROM sys.dm_exec_query_optimizer_info
WHERE counter = 'optimizations';
/*
Average elapsed time per optimization, What is the average elapsed time per optimization?
*/
SELECT ISNULL(value,0.0) AS ElapsedTimePerOptimization
FROM sys.dm_exec_query_optimizer_info
WHERE counter = 'elapsed time';
/*
Fraction of optimizations that involve subqueries, What fraction of optimized queries contained a subquery?
*/
SELECT
(SELECT CAST(occurrence AS float) FROM sys.dm_exec_query_optimizer_info WHERE counter = 'contains subquery')
/
(SELECT CAST(occurrence AS float) FROM sys.dm_exec_query_optimizer_info WHERE counter = 'optimizations')
AS ContainsSubqueryFraction;
/*
The following sample query gives you the top 50 stored procedures that have been recompiled.
*/
select sql_text.text
, last_execution_time
, plan_generation_num
, execution_count
,total_worker_time
,total_elapsed_time
,last_elapsed_time
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where plan_generation_num >1 –and sql_text.text not like '%SQL diagnostic%'
order by plan_generation_num desc
–order by last_execution_time desc
/*
the query determines which query is using the most cumulative CPU
*/
select top 50
s.text
,db_name(s.dbid) as db_name
,qs.total_worker_time
,qs.total_elapsed_time
,qs.plan_generation_num
,qs.last_worker_time
,qs.execution_count
,qs.creation_time
,qs.total_physical_reads
,qs.last_elapsed_time,max_elapsed_time
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.plan_handle) as s
order by qs.total_worker_time desc
Check IO stats on database files
–-check datafile IO stall stats
select
db_name(fs.database_id) as database_name
,fs.file_id
,mf.physical_name
,io_stall_read_ms
,num_of_reads
,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as'avg_read_stall_ms'
,io_stall_write_ms
,num_of_writes
,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as'avg_write_stall_ms'
,io_stall_read_ms + io_stall_write_ms as io_stalls
,num_of_reads + num_of_writes as total_io
,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) asnumeric(10,1)) as 'avg_io_stall_ms'
from sys.dm_io_virtual_file_stats(null,null) as fs
inner join sys.master_files as mf on fs.database_id=mf.database_id andfs.file_id=mf.file_id
order by avg_io_stall_ms desc
Check spinlock stats
– for SQL Server 2005
CREATE TABLE dba_admin.dbo.SpinlockStats(
[name] NVARCHAR(30) NOT NULL,
[collisions] BIGINT NOT NULL,
[spins] BIGINT NOT NULL,
[spins_per_collision] FLOAT NOT NULL,
[sleep_time] BIGINT NOT NULL,
[backoffs] BIGINT NOT NULL
)
INSERT INTO dba_admin.dbo.SpinlockStats EXEC ('DBCC SQLPERF(”spinlockstats”)')
GO
select * from dba_admin.dbo.SpinlockStats order by spins desc
– for SQL Server 2008 and later
SELECT * INTO ##TempSpinlockStats FROM sys.dm_os_spinlock_stats
WHERE [collisions] > 0
ORDER BY [name];
GO
select * from ##TempSpinlockStats
Check the current active requests (blocking sessions) in the databases
– Check the current active requests. This is one of my favorite queries. use it a lot at work.
select
a.blocking_session_id
,b.text,a.session_id
,a.start_time
,a.status
,a.command,db_name(a.database_id) as database_name
,object_name(b.objectid,b.dbid) as object_name,a.user_id
,c.login_name
,c.host_name
,a.wait_time
,a.last_wait_type
,d.resource_description
,a.cpu_time
,a.total_elapsed_time
,a.writes
,a.reads
,a.logical_reads
,a.granted_query_memory
,a.nest_level,a.row_count
,a.transaction_isolation_level
from sys.dm_exec_requests as a
outer apply sys.dm_exec_sql_text(sql_handle) as b
left join sys.dm_exec_sessions as c on a.session_id=c.session_id
left join sys.dm_os_waiting_tasks as d on a.session_id=d.session_id
where a.session_id>50
order by a.session_id
– in case the blocking session is not showing up in the result set of the above query. try using sysprocesses.
select b.text, a.* from master.dbo.sysprocesses as a cross applysys.dm_exec_sql_text(sql_handle) as b
where spid=12685
– kill 2310
– sp_who2 active
Selectively rebuild or reorganize indexes
/*Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes.
The following example automatically reorganizes or rebuilds all partitions in a database that have an average fragmentation over 10 percent. Executing this query requires the VIEW DATABASE STATE permission. This example specifies DB_ID as the first parameter without specifying a database name. An error will be generated if the current database has a compatibility level of 80 or lower. To resolve the error, replace DB_ID() with a valid database name. For more information about database compatibility levels, see sp_dbcmptlevel
*/
– Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
– Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
– and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
– Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
– Open the cursor.
OPEN partitions;
– Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count(*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
– 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.'
+ @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON '
+ @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION='+ CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N'Executed: ' + @command;
END;
– Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
– Drop the temporary table.
DROP TABLE #work_to_do;
GO
Check index stats
– check index usage stats
SELECT
OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID =S.INDEX_ID
WHERE object_name(s.object_id)='Your_Table_Name'
– check index operations stats
SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
A.LEAF_INSERT_COUNT,
A.LEAF_UPDATE_COUNT,
A.LEAF_DELETE_COUNT
FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS(NULL,NULL,NULL,NULL) A
INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = A.[OBJECT_ID] AND I.INDEX_ID =A.INDEX_ID
WHERE OBJECT_NAME(A.[OBJECT_ID])='Your_Table_Name'
– check index physical stats
SELECT OBJECT_NAME(i.object_id) AS TableName,i.name ASIndexName,indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL,NULL, 'DETAILED')indexstats
INNER JOIN sys.indexes i ON i.object_id = indexstats.object_id AND i.index_id =indexstats.index_id
where indexstats.avg_fragmentation_in_percent>50
order by tablename
Unused indexes
/* scripts below is not accurate,The premise is that an index that does not appear in SYS.DM_DB_INDEX_USAGE_STATS is not in use. This will of course be true, but there may be many more indexes that are not used properly but still appear in this DMV. You say that the reason to drop unused indexes is because they reduce the performance of inserts and updates, but when this actually occurs, i.e. a single update is written to the table data pages and also the index data pages, then this is logged in the SYS.DM_DB_INDEX_USAGE_STATS DMV.
This inefficient use of the index that you are trying to locate is the actual cause of it not being identified by the query.
Any indexes shown by the query below are not in use but are also not doing any harm to performance, although they do obviously take up drive space.
*/
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
INDEXNAME = I.NAME,
I.INDEX_ID,
Index_type=I.type_desc,
I.is_disabled,
O.create_date,
O.modify_date
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1 AND I.INDEX_ID NOT IN
(
SELECT S.INDEX_ID FROM SYS.DM_DB_INDEX_USAGE_STATS S
WHERE S.OBJECT_ID = I.OBJECT_ID AND I.INDEX_ID = S.INDEX_ID AND DATABASE_ID =@dbid
)
–ORDER BY OBJECTNAME,I.INDEX_ID,INDEXNAME ASC
order by o.create_date desc,objectname
GO
/* the query below returns indexes affect db performance */
select
object_name(s.[object_id]) as tablename,
i.name as indexname,
i.type_desc,
o.create_date,
o.modify_date,
last_user_update,
s.User_seeks,
last_user_seek,
user_scans,
last_user_scan,
user_lookups,
Last_user_lookup,
user_updates
from sys.dm_db_index_usage_stats s
join sys.indexes i on s.object_id = i.object_id and s.index_id = i.index_id
join sys.objects o on o.object_id=i.object_id
where OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1 and user_scans +user_lookups + user_seeks = 0
order by tablename
