Check Statistics

SELECT ss.name AS SchemaName
       , st.name AS TableName
       , s.name AS IndexName
       , STATS_DATE(s.id,s.indid) AS 'Statistics Last Updated'
       , s.rowcnt AS 'Row Count'                    
       , s.rowmodctr AS 'Number Of Changes'  
       , CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS DECIMAL(28,2)) * 100.0)
                           AS DECIMAL(28,2)) AS '% Rows Changed'
       , 'update statistics ' + ss.name + '.' + st.name + ' ' + s.name as Command
FROM sys.sysindexes s
INNER JOIN sys.tables st ON st.[object_id] = s.[id]
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
WHERE s.indid > 0          -- non-clustered indexes
and s.rowcnt >= 1000       -- want at least 1000 rows
ORDER BY '% Rows Changed' DESC;

Heaps with PRIMARY KEY NONCLUSTERED

-- IDENTIFY PRIMARY KEYS THAT ARE NOT CLUSTERED
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT QUOTENAME(SCHEMA_NAME([t].[schema_id])) + '.' + QUOTENAME([t].[name]) AS [Table] ,
 QUOTENAME(OBJECT_NAME([kc].[object_id])) AS [IndexName] ,
 CAST((SUM([a].[total_pages]) * 8 / 1024.0 ) AS DECIMAL(18,2))AS [IndexSizeMB] ,
 sum(p.rows) as NumRows
FROM [sys].[tables] [t]
INNER JOIN [sys].[indexes] [i]
ON [t].[object_id] = [i].[object_id]
INNER JOIN [sys].[partitions] [p]
ON [i].[object_id] = [p].[object_id]
 AND [i].[index_id] = [p].[index_id]
INNER JOIN [sys].[allocation_units] [a]
ON [a].[container_id] = CASE WHEN [a].[type] IN ( 1, 3 ) THEN [p].[hobt_id]
 WHEN [a].[type] = 2 THEN [p].[partition_id]
 END
INNER JOIN [sys].[key_constraints] AS [kc]
ON [t].[object_id] = [kc].[parent_object_id]
WHERE (
 [i].[name] IS NOT NULL
 AND OBJECTPROPERTY([kc].[object_id], 'CnstIsNonclustKey') = 1 --Unique Constraint or Primary Key can qualify
 AND OBJECTPROPERTY([t].[object_id], 'TableHasClustIndex') = 0 --Make sure there's no Clustered Index, this is a valid design choice
 AND OBJECTPROPERTY([t].[object_id], 'TableHasPrimaryKey') = 1 --Make sure it has a Primary Key and it's not just a Unique Constraint
 AND OBJECTPROPERTY([t].[object_id], 'IsUserTable') = 1 --Make sure it's a user table because whatever, why not? We've come this far
 )
GROUP BY [t].[schema_id] ,
 [t].[name] ,
 OBJECT_NAME([kc].[object_id])
ORDER BY SUM([a].[total_pages]) * 8 / 1024.0 DESC;

Raj's Currently Running Plus Job name

SELECT getdate() as dt,

ss.session_id,

db_name(sysprocesses.dbid) as dbname,

er.status as req_status,

ss.login_name,

cs.client_net_address,

ss.program_name,

program_name_new = 

CASE LEFT(ss.program_name, 29)

              WHEN 'SQLAgent - TSQL JobStep (Job '

              THEN 'SQLAgent Job: ' + (SELECT name FROM msdb..sysjobs sj WHERE substring(ss.program_name, 32, 32) = (substring(sys.fn_varbintohexstr(sj.job_id), 3, 100))) + ' - ' + SUBSTRING(ss.program_name, 67, len(ss.program_name)-67)

             ELSE ss.program_name

       END , 

sysprocesses.open_tran,

er.blocking_session_id,

ss.host_name,

ss.client_interface_name,

[eqp].[query_plan] as qplan,

SUBSTRING(est.text,(er.statement_start_offset/2)+1,

CASE WHEN er.statement_end_offset=-1 OR er.statement_end_offset=0 

THEN (DATALENGTH(est.Text)-er.statement_start_offset/2)+1 

ELSE (er.statement_end_offset-er.statement_start_offset)/2+1

END) as req_query_text,

er.granted_query_memory,

er.logical_reads as req_logical_reads,

er.cpu_time as req_cpu_time,

er.reads as req_physical_reads,

er.row_count as req_row_count, 

er.scheduler_id,

er.total_elapsed_time as req_elapsed_time,

er.start_time as req_start_time,

er.percent_complete,

er.wait_resource as wait_resource,

er.wait_type as req_waittype,

er.wait_time as req_wait_time,

wait.wait_duration_ms as blocking_time_ms,

lock.resource_associated_entity_id,

lock.request_status as lock_request_status,

lock.request_mode as lock_mode,

er.writes as req_writes,

sysprocesses.lastwaittype,

fn_sql.text as session_query,

ss.status as session_status,

ss.cpu_time as session_cpu_time,

ss.reads as session_reads,

ss.writes as session_writes,

ss.logical_reads as session_logical_reads,

ss.memory_usage as session_memory_usage,

ss.last_request_start_time,

ss.last_request_end_time,

ss.total_scheduled_time as session_scheduled_time,

ss.total_elapsed_time as session_elpased_time,

ss.row_count as session_rowcount

FROM sys.dm_exec_sessions ss 

INNER JOIN sys.dm_exec_connections cs 

ON ss.session_id = cs.session_id 

OUTER APPLY

fn_get_sql(cs.most_recent_sql_handle) as fn_sql

INNER JOIN sys.sysprocesses 

ON sys.sysprocesses.spid = cs.session_id

LEFT OUTER JOIN sys.dm_exec_requests [er] 

ON er.session_id = ss.session_id

OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]

OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp]

LEFT OUTER JOIN 

sys.dm_os_waiting_tasks wait 

ON er.session_id = wait.session_id

and wait.wait_type like 'LCK%' and 

er.blocking_session_id = wait.blocking_session_id

LEFT OUTER JOIN sys.dm_tran_locks lock 

ON lock.lock_owner_address = wait.resource_address

and lock.request_session_id = er.blocking_session_id

WHERE ss.status != 'sleeping'