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