Check Active Queries and Related Details Many times DBAs need to check currently active queries on the DB servers and other details like estimated completion time, blockings, query plan, query text, host name, user login details etc. Following query generate such detail for active queries.… Read More
Expensive Queries of a database Following Query will find the expensive queries on specific database on SQL Instance. select DB_NAME(s.dbid) DBNAME , t.Text, spid from sysprocesses s CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t where DB_NAME(s.dbid) like ‘Database_Name%’ Thank you for giving your valuable time… Read More
AlwaysOn SQL Agent Alerts Please find below script to create SQL Server Agent Alerts. Please note that operator name should be changed according to your SQL operator. The below script will provide one output, you will have to execute the out again to create the… Read More
Capture SQL Server Important Details Please find the below script to capture SQL Server Important Details before upgrade, migration etc. PRINT ‘***************** Server Name ********************’ SET NOCOUNT ON select @@SERVERNAME go PRINT ‘***************** Version ********************’ SET NOCOUNT ON select @@version go PRINT ‘***************** License Information********************’… Read More
Find the Database Restore History On SQL Instance, at times we should know what DB’s got restored on specific instance or want to know when specific DB got restored. Below query will help to achieve that. SELECT * FROM MSDB..RESTOREHISTORY ORDER BY RESTORE_DATE DESC … Read More
Find Percent Completion of Process This script is to find the percent of completion of backup, restore, DBCC commands etc. SELECT req.session_id, sqltext.TEXT, req.status, req.command, req.wait_type, req.wait_time, (req.estimated_completion_time/1000/60) as est_min, req.percent_complete FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext join sys.dm_exec_sessions ses on ses.session_id =… Read More
Find Objects and Row count of each table in Database create table #tablesize (name nvarchar(120),rows char(11),reserved varchar(18), data varchar(18),index_size varchar(18),unused varchar(18)) go insert into #tablesize exec sp_msforeachtable @command1= ‘exec sp_spaceused ”?”’ select * from #tablesize go drop table #tablesize Thank you for giving your… Read More
Find Number Of Sessions On Each DatabaseFind Number Of Sessions On Each Database select COUNT(1)as Number_Of_Connections, DB_NAME(dbid) as Database_Name from sysprocesses group by dbid order by 1 desc Thank you for giving your valuable time to read the above information. Please click here to subscribe for… Read More
Find MAX DOP Value of SQL Instance select case when cpu_count / hyperthread_ratio > 8 then 8 else cpu_count / hyperthread_ratio end as optimal_maxdop_setting from sys.dm_os_sys_info; Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates… Read More
CPU_Usage_History of Past 4 hours DECLARE @ts_now bigint SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info SELECT top 240 record_id, EventTime, CASE WHEN system_cpu_utilization_post_sp2 IS NOT NULL THEN system_cpu_utilization_post_sp2 ELSE system_cpu_utilization_pre_sp2 END AS system_cpu_utilization, CASE WHEN sql_cpu_utilization_post_sp2 IS NOT NULL THEN sql_cpu_utilization_post_sp2 ELSE sql_cpu_utilization_pre_sp2 END… Read More
