SQL Server IMP Queries for DBA
Useful Queries
To get the fragmentation of the indexes
To generate the alter index rebuild scripts use below
select 'ALTER INDEX [' + i.name +'] on '+OBJECT_NAME(s.object_id)+' REBUILD WITH (ONLINE = ON)',objname = OBJECT_NAME(s.object_id),s.object_id,index_name= i.name,index_type_desc, avg_fragmentation_in_percentfrom sys.dm_db_index_physical_stats(null,null,null,null,null) as sjoin sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id where avg_fragmentation_in_percent>30order by avg_fragmentation_in_percent desc, page_count desc;
Which DB CPU utilized the most.
WITH DB_CPU_STATS_ON_INSTANCEAS(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]FROM sys.dm_exec_query_stats AS qsCROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] FROM sys.dm_exec_plan_attributes(qs.plan_handle)WHERE attribute = N'dbid') AS F_DBGROUP BY DatabaseID)SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],DatabaseName, [CPU_Time_Ms], CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]FROM DB_CPU_STATS_ON_INSTANCEWHERE DatabaseID > 4 AND DatabaseID <> 32767 ORDER BY row_num OPTION (RECOMPILE);
Top queries executed.
SELECT TOP 50 ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ,TextData = qt.text ,DiskReads = qs.total_physical_reads -- The worst reads, disk reads ,MemoryReads = qs.total_logical_reads --Logical Reads are memory reads ,Executions = qs.execution_count ,TotalCPUTime = qs.total_worker_time ,AverageCPUTime = qs.total_worker_time/qs.execution_count ,DiskWaitAndCPUTime = qs.total_elapsed_time ,MemoryWrites = qs.max_logical_writes ,DateCached = qs.creation_time ,DatabaseName = DB_Name(qt.dbid) ,LastExecutionTime = qs.last_execution_time FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY qs.total_worker_time DESC;
CPU Usage queries.
Top IO Queries.
select q.[text],SUBSTRING(q.text, (highest_cpu_queries.statement_start_offset/2)+1, ((CASE highest_cpu_queries.statement_end_offset WHEN -1 THEN DATALENGTH(q.text) ELSE highest_cpu_queries.statement_end_offset END - highest_cpu_queries.statement_start_offset)/2) + 1) AS statement_text, highest_cpu_queries.total_worker_time, highest_cpu_queries.total_logical_reads, highest_cpu_queries.last_execution_time, highest_cpu_queries.execution_count, q.dbid, q.objectid, q.number, q.encrypted, highest_cpu_queries.plan_handlefrom (select top 50 qs.last_execution_time, qs.execution_count, qs.plan_handle, qs.total_worker_time, qs.statement_start_offset, qs.statement_end_offset, qs.total_logical_reads from sys.dm_exec_query_stats qs order by qs.total_worker_time desc) as highest_cpu_queries cross apply sys.dm_exec_sql_text(plan_handle) as qorder by highest_cpu_queries.total_logical_reads desc;
Top IO Queries.
select SUBSTRING(st.text,(qs.statement_start_offset/2)+1, ((case statement_end_offset when -1 then DATALENGTH(st.text) else qs.statement_end_offset end - qs.statement_start_offset)/2) +1) as statement_text, qs.total_logical_reads, qs.total_physical_reads, qs.execution_count from sys.dm_exec_query_stats as qs cross apply sys.dm_exec_sql_text(qs.sql_handle) as st order by qs.total_logical_reads desc, qs.execution_count desc;
by DB
select serverproperty('MachineName') 'machine_name',isnull(serverproperty('InstanceName'),'mssqlserver') 'instance_name',@@SERVERNAME 'sql_server_name',DB_NAME(mf.database_id) 'database_name',mf.name 'logical_name',mf.physical_name 'physical_name',left(mf.physical_name,1) 'disk_drive',mf.type_desc 'file_type',mf.state_desc 'state',case mf.is_read_onlywhen 0 then 'no'when 1 then 'yes'end 'read_only',convert(numeric(18,2),convert(numeric,mf.size)*8/1024) 'size_mb',divfs.size_on_disk_bytes/1024/1024 'size_on_disk_mb',case mf.is_percent_growthwhen 0 then cast(convert(int,convert(numeric,mf.growth)*8/1024) as varchar) + ' MB'when 1 then cast(mf.growth as varchar) + '%'end 'growth',case mf.is_percent_growthwhen 0 then convert(numeric(18,2),convert(numeric,mf.growth)*8/1024)when 1 then convert(numeric(18,2),(convert(numeric,mf.size)*mf.growth/100)*8/1024)end 'next_growth_mb',case mf.max_sizewhen 0 then 'NO-growth'when -1 then (case mf.growth when 0 then 'NO-growth' else 'unlimited' end)else cast(convert(int,convert(numeric,mf.max_size)*8/1024) as varchar)+' MB'end 'max_size'
,divfs.num_of_reads,divfs.num_of_bytes_read/1024/1024 'read_mb',divfs.io_stall_read_ms
,divfs.num_of_writes,divfs.num_of_bytes_written/1024/1024 'write_mb',divfs.io_stall_write_ms
from sys.master_files as mfleft outer join sys.dm_io_virtual_file_stats(null,null) as divfson mf.database_id=divfs.database_id and mf.file_id=divfs.file_id;
RUNNING Queries.
select text, SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offsetWHEN -1 THEN DATALENGTH(st.text)ELSE qs.statement_end_offsetEND - qs.statement_start_offset)/2) + 1) AS statement_text,* from sys.dm_exec_requests qscross apply sys.dm_exec_sql_text(sql_handle) stcross apply sys.dm_exec_query_plan(plan_handle);
Lock Quries.
SELECTdb.name DBName,tl.request_session_id,wt.blocking_session_id,OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,tl.resource_type,h1.TEXT AS RequestingText,h2.TEXT AS BlockingTest,tl.request_modeFROM sys.dm_tran_locks AS tlINNER JOIN sys.databases db ON db.database_id = tl.resource_database_idINNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_addressINNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_idINNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_idINNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_idCROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2GO
Backup Checks.
SELECT DB.name AS Database_Name,MAX(DB.recovery_model_desc) AS Recovery_Model,MAX(BS.backup_start_date) AS Last_Backup,MAX(CASE WHEN BS.type = 'D'THEN BS.backup_start_date END)AS Last_Full_backup,SUM(CASE WHEN BS.type = 'D'THEN 1 END)AS Count_Full_backup,MAX(CASE WHEN BS.type = 'L'THEN BS.backup_start_date END)AS Last_Log_backup,SUM(CASE WHEN BS.type = 'L'THEN 1 END)AS Count_Log_backup,MAX(CASE WHEN BS.type = 'I'THEN BS.backup_start_date END)AS Last_Differential_backup,SUM(CASE WHEN BS.type = 'I'THEN 1 END)AS Count_Differential_backup,MAX(CASE WHEN BS.type = 'F'THEN BS.backup_start_date END)AS LastFile,SUM(CASE WHEN BS.type = 'F'THEN 1 END)AS CountFile,MAX(CASE WHEN BS.type = 'G'THEN BS.backup_start_date END)AS LastFileDiff,SUM(CASE WHEN BS.type = 'G'THEN 1 END)AS CountFileDiff,MAX(CASE WHEN BS.type = 'P'THEN BS.backup_start_date END)AS LastPart,SUM(CASE WHEN BS.type = 'P'THEN 1 END)AS CountPart,MAX(CASE WHEN BS.type = 'Q'THEN BS.backup_start_date END)AS LastPartDiff,SUM(CASE WHEN BS.type = 'Q'THEN 1 END)AS CountPartDiffFROM sys.databases AS DBLEFT JOINmsdb.dbo.backupset AS BSON BS.database_name = DB.nameWHERE ISNULL(BS.is_damaged, 0) = 0-- exclude damaged backups GROUP BY DB.nameORDER BY Last_Backup desc;
Index Usage Stats.
select objname = OBJECT_NAME(s.object_id),s.object_id,index_name= i.name,index_id = i.index_id,user_seeks, user_scans, user_lookupsfrom sys.dm_db_index_usage_stats as sjoin sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id
Waiting tasks
select wt.session_id, wt.exec_context_id, wt.wait_duration_ms, wt.wait_type, wt.blocking_session_id, wt.resource_address, wt.resource_description, s.program_name, st.text, sp.query_plan, s.cpu_time cpu_time_ms, s.memory_usage*8 memory_usage_kbfrom sys.dm_os_waiting_tasks wt join sys.dm_exec_sessions s on s.session_id=wt.session_id join sys.dm_exec_requests r on r.session_id=s.session_id outer apply sys.dm_exec_sql_text(r.sql_handle) st outer apply sys.dm_exec_query_plan(r.plan_handle) spwhere s.is_user_process=1order by wt.session_id, wt.exec_context_id;
Wait events on db level.
SELECT getdate() as 'Run_Time' --script running time , wait_type --wait type ,waiting_tasks_count , CAST(wait_time_ms / 1000. AS DECIMAL(12, 2)) AS wait_time_s --saniye cinsinden bekleme zamaný , CAST(100. * wait_time_ms / SUM(wait_time_ms) OVER() AS DECIMAL(12, 2)) AS pct --toplam beklemeye oranýFROM sys.dm_os_wait_statsWHERE wait_type NOT IN ('BROKER_TASK_STOP','Total','SLEEP','BROKER_EVENTHANDLER','BROKER_RECEIVE_WAITFOR', 'BROKER_TRANSMITTER','CHECKPOINT_QUEUE','CHKPT,CLR_AUTO_EVENT','CLR_MANUAL_EVENT','KSOURCE_WAKEUP','LAZYWRITER_SLEEP', 'LOGMGR_QUEUE','ONDEMAND_TASK_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','RESOURCE_QUEUE','SERVER_IDLE_CHECK', 'SLEEP_BPOOL_FLUSH','SLEEP_DBSTARTUP','SLEEP_DCOMSTARTUP','SLEEP_MSDBSTARTUP','SLEEP_SYSTEMTASK','SLEEP_TASK', 'SLEEP_TEMPDBSTARTUP','SNI_HTTP_ACCEPT','SQLTRACE_BUFFER_FLUSH','TRACEWRITE','WAIT_FOR_RESULTS','WAITFOR_TASKSHUTDOWN', 'XE_DISPATCHER_WAIT','XE_TIMER_EVENT','WAITFOR')ORDER BY 4 DESC
DB Size from backup.
DECLARE @startDate datetime;SET @startDate = GetDate();
SELECT PVT.DatabaseName, PVT.[0], PVT.[-1], PVT.[-2], PVT.[-3], PVT.[-4], PVT.[-5], PVT.[-6], PVT.[-7], PVT.[-8], PVT.[-9], PVT.[-10], PVT.[-11], PVT.[-12]FROM(SELECT BS.database_name AS DatabaseName,DATEDIFF(mm, @startDate, BS.backup_start_date) AS MonthsAgo,CONVERT(numeric(10, 1), AVG(BF.file_size / 1048576.0)) AS AvgSizeMBFROM msdb.dbo.backupset as BSINNER JOINmsdb.dbo.backupfile AS BFON BS.backup_set_id = BF.backup_set_idWHERE NOT BS.database_name IN('master', 'msdb', 'model', 'tempdb')AND BF.[file_type] = 'D'AND BS.backup_start_date BETWEEN DATEADD(yy, -1, @startDate) AND @startDateGROUP BY BS.database_name,DATEDIFF(mm, @startDate, BS.backup_start_date)) AS BCKSTATPIVOT (SUM(BCKSTAT.AvgSizeMB)FOR BCKSTAT.MonthsAgo IN ([0], [-1], [-2], [-3], [-4], [-5], [-6], [-7], [-8], [-9], [-10], [-11], [-12])) AS PVTORDER BY PVT.DatabaseName;
Inventory/ details of all the databases.
select getdate() Date_Collected ,serverproperty('MachineName') 'Machine_Name' ,isnull(serverproperty('InstanceName'),'mssqlserver') 'Instance_Name' ,@@SERVERNAME 'Sql_Server_Name' ,SERVERPROPERTY('productversion') Product_Version ,SERVERPROPERTY ('productlevel') Product_Level ,SERVERPROPERTY ('edition') 'Edition' ,d.name 'database_name' ,suser_sname(d.owner_sid) 'owner' ,ls.cntr_value as [log_size_kb] ,lu.cntr_value as [log_used_kb] ,lp.cntr_value as [percent_log_used] ,ds.cntr_value as [data_files_size_kb]from sys.databases d left outer join sys.dm_os_performance_counters as lu on lu.instance_name=d.name and lu.counter_name like N'Log File(s) Used Size (KB)%' left outer join sys.dm_os_performance_counters as ls on ls.instance_name=d.name and ls.counter_name like N'Log File(s) Size (KB)%' and ls.cntr_value > 0 left outer join sys.dm_os_performance_counters as lp on lp.instance_name=d.name and lp.counter_name like N'Percent Log Used%' left outer join sys.dm_os_performance_counters as ds on ds.instance_name=d.name and ds.counter_name like N'Data File(s) Size (KB)%'order by d.name;
select serverproperty('MachineName') 'machine_name' ,isnull(serverproperty('InstanceName'),'mssqlserver') 'instance_name' ,@@SERVERNAME 'sql_server_name' ,d.name 'database_name' ,suser_sname(d.owner_sid) 'owner' ,d.compatibility_level ,d.collation_name ,d.is_auto_close_on ,d.is_auto_shrink_on ,d.state_desc ,d.snapshot_isolation_state ,d.is_read_committed_snapshot_on ,d.recovery_model_desc ,d.is_auto_create_stats_on ,d.is_auto_update_stats_on ,d.is_auto_update_stats_async_on ,d.is_in_standby ,d.page_verify_option_desc ,d.log_reuse_wait_desc ,ls.cntr_value as [log size (kb)] ,lu.cntr_value as [log used (kb)] ,lp.cntr_value as [percent log used] ,ds.cntr_value as [data file(s) size (kb)]from sys.databases d inner join sys.dm_os_performance_counters as lu on lu.instance_name=d.name and lu.counter_name like N'Log File(s) Used Size (KB)%' inner join sys.dm_os_performance_counters as ls on ls.instance_name=d.name and ls.counter_name like N'Log File(s) Size (KB)%' and ls.cntr_value > 0 inner join sys.dm_os_performance_counters as lp on lp.instance_name=d.name and lp.counter_name like N'Percent Log Used%' inner join sys.dm_os_performance_counters as ds on ds.instance_name=d.name and ds.counter_name like N'Data File(s) Size (KB)%'order by d.name