Tag Archives for " SQLServer "

Using EXCEPT and INTERSECT to Validate SQL Server Query Tuning

By Jeffry Schwartz | Query Tuning , SQL Server

This article explains how the EXCEPT and INTERSECT T-SQL operators can help compare query results when tuning SQL Server queries, stored procedures, and views. Recently, a customer requested that I optimize several SSRS financial reports that frequently took between one and three minutes to complete. These reports were driven by complex code involving user-defined functions, […]

Continue reading

Heap Table Maintenance

By Lori Brown | Helpful Scripts , Maintenance , SQL Maintenance , SQL Server

I have a customer who let me know that some of their tables had a large amount of unused space in them.  They were wondering if I could get them to release the space.  After doing some investigation, I found that all tables with the huge amount of unused space were heap tables. I advised […]

Continue reading

Monitor for Blocked Process Records and Deadlocking using Extended Events

By Lori Brown | Extended Events , SQL Administration , SQL Server

Here is a way to set up an extended events session that can be used to collect blocked processes and deadlock records.  I use similar code to collect this type of data each day and have a job that pulls the data into tables which can be used to evaluate the resources that are being […]

Continue reading

Using Extended Events to Find Statement Causing a Job to Run Long or Hang

By Lori Brown | Extended Events , Helpful Scripts , Jobs , Performance Tuning , SQL Administration , SQL Server

On one of the servers that we monitor, it was reported that periodically a specific job would seem to hang and take forever to complete.  I was asked to set up something to help find exactly what statement in the job that was the root cause of the hang.  Since the issue only occurred intermittently, […]

Continue reading

Backup and Restore Databases with CHECKSUM and VERIFYONLY

By Lori Brown | Backup and Restore , SQL Administration , SQL Maintenance , SQL Server

I found out that I have been missing something from our regular database backups that I had no idea that I should have been using all along.  I know about verifying your backup file and have incorporated into our standard maintenance routines one that will periodically test backups by restoring using VERIFYONLY.  However, I totally […]

Continue reading

Adding a Missing Index Killed Performance

By Jeffry Schwartz | Indexes , Missing Indexes , Performance Tuning , Query Tuning , SQL Server

SQL Server 2017 suggested an index to improve performance, so we added it in a development environment. The “improvement” made the query run almost 50 TIMES longer than the original! See how we fixed this issue.

Continue reading

Use DDL Triggers to get Instance and Database Change Notifications

By Lori Brown | Helpful Scripts , SQL Administration , SQL Server

I have been working on some improvements to some of the regular ways we monitor for important changes.  We always have to be on the lookout for unexpected changes being made in the SQL instances that we monitor since often times we are not the only team who has sysadmin access to the instance.  We […]

Continue reading

Should I Replace My FOR XML PATH String Merges with String_agg?

By Jeffry Schwartz | Function , Performance Tuning , Query Tuning , SQL Development , SQL Server

If you are looking for major performance gains, the answer is no.  However, if you are using SQL Server 2017 and developing new code, you should consider using the new String_agg function that is available on SQL Server 2017 because code that uses this function is so much more readable and easier to write and […]

Continue reading

Troubleshoot SLEEP_TASK Wait Type

By Ginger Daniel | Miscellaneous , Performance Tuning , Query Tuning , SQL Development , SQL Server

Jobs on a SQL server can take milliseconds or minutes to complete depending on the size and complexity of your query and also the SQL environment on which it’s executing.  It’s important to have a baseline or at least be familiar with the normal amount of time your SQL jobs take to execute.   For a […]

Continue reading