SQL Server – Performance Top Queries by Total CPU Time

select top 10 rank() over(order by total_worker_time desc,sql_handle,statement_start_offset) as row_no
,       (rank() over(order by total_worker_time desc,sql_handle,statement_start_offset))%2 as l1
,       creation_time
,       last_execution_time
,       (total_worker_time+0.0)/1000 as total_worker_time
,       (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime]
,       total_logical_reads as [LogicalReads]
,       total_logical_writes as [logicalWrites]
,       execution_count
,       total_logical_reads+total_logical_writes as [AggIO]
,       (total_logical_reads+total_logical_writes)/(execution_count + 0.0) as [AvgIO]
,   case when sql_handle IS NULL
then ''
else ( substring(st.text,(qs.statement_start_offset+2)/2,(case when qs.statement_end_offset = -1        then len(convert(nvarchar(MAX),st.text))*2      else qs.statement_end_offset    end - qs.statement_start_offset) /2  ) )
end as query_text
,       db_name(st.dbid) as database_name
,       st.objectid as object_id
from sys.dm_exec_query_stats  qs
cross apply sys.dm_exec_sql_text(sql_handle) st
where total_worker_time > 0
order by total_worker_time  desc

List current SQL Server tempdb statements

SELECT ssu.session_id,
(ssu.internal_objects_alloc_page_count + sess_alloc) as allocated,
(ssu.internal_objects_dealloc_page_count + sess_dealloc) as deallocated
, stm.TEXT
from sys.dm_db_session_space_usage as ssu, 
sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS stm ,
(select session_id, 
sum(internal_objects_alloc_page_count) as sess_alloc,
sum (internal_objects_dealloc_page_count) as sess_dealloc
from sys.dm_db_task_space_usage group by session_id) as tsk
where ssu.session_id = tsk.session_id
and ssu.session_id >50
and ssu.session_id = req.session_id
and ssu.database_id = 2
order by allocated DESC

How to check for SQL ghost records

Use the sys.dm_db_index_physical_stats view and check the ghost_record_count column.
The Ghost record count  is the row count in the index leaf level marked for deletion, but not yet deleted by the database engine

Ghost_record_count is the ghost record count awaiting removal by the ghost cleanup task in the allocation unit.

Version_Ghost_record_count  is the ghost records count sustained by a snapshot isolation transaction in an allocation unit.

SELECT
db_name(database_id),
object_name(object_id),
ghost_record_count,
version_ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(N'db_name'), OBJECT_ID(N'table_name'), NULL, NULL , 'DETAILED');

Calculate the Size of Index

SELECT ix.[name] AS IndexName,
SUM(ps.[used_page_count]) * 8 AS IndexSize_KB
FROM sys.dm_db_partition_stats AS ps
JOIN sys.indexes AS ix
ON ps.[object_id] = ix.[object_id]
AND ps.[index_id] = ix.[index_id]
GROUP BY ix.[name]