Share The Tips, Help The Community!

#35 (22 December, 2021)
Are you applying a TSQL function on the attribute on the left-hand side of the relational operator in the WHERE clause? Maybe the query is losing its SARGABILITY.

#34 (15 December, 2021)
A safe bet: Set the server level MAXDOP to the number of cores per NUMA node.

#33 (14 December, 2021)
Migrating from an older version of SQL Server to the latest version? Carefully consider changing the database compatibility level to leverage the engine enhancements.

#32 (13 December, 2021)
Update statistics with FULLSCAN to compute statistics by scanning all the rows in the table. Use wisely.

#31 (9 December, 2021)
SQL Server cannot perform a seek operation on the index if the LIKE clause predicate begins with wild characters (%, [ ], _).

#30 (8 December, 2021)
Use NOT EXISTS instead of NOT IN at a WHERE condition that uses a subquery to check for data existence.

#29 (1 December, 2021)
Use sp_helpdb to retrieve the high-level overview of a specified database or all the databases.

#28 (30 November, 2021)
Use the ISNUMERIC function to determine whether an expression is a valid numeric type or not.

#27 (29 November, 2021)
Wish to enable word wrap feature in SSMS? Menu > Tools > Options > Text Editor > All Languages. Check the ‘Word Wrap’ box.

#26 (26 November, 2021)
Use sp_updatestats to execute UPDATE STATISTICS on all tables in a database.

#25 (23 November, 2021)
Run DBCC CHECKDB to check the logical and physical integrity of all the objects in a database.

#24 (15 November, 2021)
Use the new LEAST function (supports Azure SQL DB/MI at the moment) to find the minimum value from a list of expressions.

#23 (12 November, 2021)
To get the last known actual execution plan from the plan cache, use new DMF sys.dm_exec_query_plan_stats (plan_handle).

#22 (11 November, 2021)
Use WITH COPY_ONLY option to take intermittent full backup without affecting the regular sequence of backups.

#21 (10 November, 2021)
Enable instant file Initialization (IFI) to speed up the data files growth.

#20 (09 November, 2021)
Use the new GREATEST function (supports Azure SQL DB/MI at the moment) to find the maximum value from a list of expressions.

#19 (08 November, 2021)
To get the status (Enabled/Disabled) of all the trace flags for a session, use DBCC TRACESTATUS();

#18 (03 November, 2021)
To perform statement-level recompile, use OPTION (RECOMPILE) hint along with the query.

#17 (01 November, 2021)
Need to delete a large volume of data at a time (let’s say 100K)? Do it in smaller chunks (1K) in a loop to reduce the impact on the log file.

#16 (27 October, 2021)
Use WITH COMPRESSION while taking database backup. The output file size will be relatively small. Useful to transfer the backups over the network. This may consume extra CPU cycles.

#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.