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