#15 (24 August, 2021)
Use sp_helpindex system stored procedure to list out all the indexes on a table.
#14 (19 August, 2021)
Have a need to clear wait statistics? Execute DBCC SQLPERF(‘sys.dm_os_wait_stats’, CLEAR);
#13 (18 August, 2021)
Use the RESTORE VERIFYONLY option to verify that the backup set is complete and the entire backup is readable.
#12 (17 August, 2021)
Change the database compatibility level to the latest version to take advantage of the newest performance improvements of the database engine.
#11 (13 August, 2021)
Enable Lock Pages in Memory (Windows Policy). Recommended best practice for most SQL deployments.
#10 (10 August, 2021)
Use FILLFACTOR while creating indexes to reduce the occurrence of fragmentation.
#9 (6 August, 2021)
Want to add line numbers in SSMS? Menu > Tools > Options > Text Editor > All Languages > General. Check the ‘Line numbers’ box.
#8 (5 August, 2021)
Wish to see the progress of Backup/Restore? Use WITH STATS N along with BACKUP/RESTORE command. N is the percentage interval.
#7 (4 August, 2021)
Traversing through the XML execution plan requires XQuery skills. Sys.dm_exec_text_query_plan DMF gives the text version of the XML execution plan. A simple LIKE search can help find what is needed.
#6 (2 August, 2021)
Using INDEXED VIEW in a subquery? Use along with NOEXPAND hint to avoid expanding the Indexed View. Increases performance.
#5 (30 July, 2021)
Use SET NOCOUNT ON in Stored Procedures to reduce network traffic and to boost performance.
#4 (29 July, 2021)
Want to open MS Docs from SSMS? Either use keyboard shortcut Ctrl + Alt + R or Menu > View > Other Windows > Web Browser
#3 (28 July, 2021)
Avoid executing Dynamic SQL using EXEC, instead use sp_executesql to avoid SQL Injection.
#2 (27 July, 2021)
Consider changing the default value of Cost Threshold of Parallelism (CTP). Given today’s hardware, the volume of data, the complexity of workloads, 5 is a low value.
#1 (26 July, 2021)
Order of columns in index definition matters. The order of columns in the SELECT list does not matter.