This post is more of a note-to-self. These are the useful T-SQL statements which can be incredibly useful in development and troubleshooting
SET STATISTICS IO ON
Turn on the IO statistics for statements run after that until set to OFF explicitly. We then switch to Messages tab to see how many IO operations were done on each table.
SET STATISTICS TIME ON
Find out about the statements were executed: which statements, its texts, how many reads (logical), how many time was spent on CPU and how many time was spent total
SELECT text , execution_count , total_logical_reads / execution_count AvgReads , total_worker_time / execution_count AvgWorkersTime , total_elapsed_time / execution_count AvgElapsedTime FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle)
Get the index fragmentation level, order by the most fragmented ones:
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, ind.name AS IndexName, indexstats.index_type_desc AS IndexType, indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent > 30 ORDER BY indexstats.avg_fragmentation_in_percent DESC
To drop the cached execution plan of a stored procedure, which is very useful to check if that specific stored procedure is suffering from parameter sniffing problem or not:
EXEC sp_recompile dbo.<name of the stored procedure>
