What’s filling up tempdb?

If you’ve ever received an alert that tempdb was running out of space, you probably want to know what session/query is consuming tempdb.  There are two ways tempdb gets consumed.  User tempdb usage and system tempdb usage. User temdpb usage is related to creating and populating @table variables or #temporary tables and explicitly populating them. […]

Read More

Plan guides made easy

Plan guides are a useful tool for changing an execution plan when despite your best efforts the optimizer just isn’t coming up with the optimal plan.  This becomes useful for situations where you can’t make code changes to a query or stored procedure.  People are sometimes hesitant to use them due to the complexity in […]

Read More

Tuning Substring Query Without Changing the Query

 Introduction The blog shows how to tune a query using substring in the WHERE clause. The tuning solution is to use indexing on a computed column. Performance Issue A General Ledger financial report execution in Financial system takes a long time finishing more than 4 hours. Performance Analysis The first step in the performance tuning […]

Read More

How to change your default transaction isolation level in SSMS

It’s not uncommon for me to see support professionals running SELECT queries against production databases that cause major performance issues due to shared locks.  By default SQL Server Management Studio (SSMS) has its transaction isolation level set to READ COMMITTED.  If your support users only have read access to your database I would suggest having […]

Read More

Something you should know about query timeouts and sys.dm_exec_query_stats

If you’re looking for your worst performers by querying sys.dm_exec_query_stats, be aware queries that are timing out (i.e. “Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding”) do not factor into the statistics shown in sys.dm_exec_query_stats.  I’ll show an example of this. Example For this example […]

Read More