Get table row count for all tables in a database


SELECT o.name, ddps.row_count
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id
WHERE i.index_id < 2
AND o.is_ms_shipped = 0
ORDER BY row_count DESC

Queries taking longest elapsed time in SQL Server


SELECT TOP 100 qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds
, qs.total_elapsed_time / 1000000.0 AS total_seconds
, qs.execution_count
, 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) AS individual_query
, o.name AS object_name
, DB_NAME(qt.dbid) AS database_name 
FROM sys.dm_exec_query_stats qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN 
sys.objects o ON qt.objectid = o.object_id
WHERE qt.dbid = DB_ID()
ORDER 
BY average_seconds DESC;

Script for monitor blocking in SQL Server

select loginame,cpu,memusage,physical_io,* from master..sysprocesses a where exists ( select b.* from master..sysprocesses b where b.blocked > 0 and b.blocked = a.spid ) and not exists ( select b.* from master..sysprocesses b where b.blocked > 0 and b.spid = a.spid )order by spid

T-SQL Query to find currently running jobs in SQL Server


SELECT  J.name as Running_Jobs,
  JA.Start_execution_date As Starting_time,
        datediff(ss, JA.Start_execution_date,getdate()) as [Has_been_running(in Sec)]
FROM msdb.dbo.sysjobactivity JA<a name="more"></a>
JOIN msdb.dbo.sysjobs J
ON J.job_id=JA.job_id
WHERE job_history_id is null
      AND start_execution_date is NOT NULL
ORDER BY start_execution_date