By Jeff Horner | SQL Administration , SQL Server
One of the simplest yet most impactful performance optimizations for SQL Server is relocating the tempdb system database to a fast, dedicated disk—especially if that disk is temporary or ephemeral. This is a common practice in cloud environments and high-performance database systems where tempdb churn is high and doesn’t require persistence across reboots. In this […]
Continue readingBy 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 readingBy Jeff Horner | Expert , SQL Administration , SQL Server , SQLRX
I have a client that has thousands of databases. They asked for a script that would tell them which databases had not been accessed in the last 90 days. I thought this would be an easy ask because we could just look at the index usage stats DMV and see which databases had no seeks, […]
Continue readingBy 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 readingBy Lori Brown | Security , SQL Server
Well…..I just learned about these and thought that it would be good to understand them a little more and have some links to read more about them. I honestly don’t have a lot of SQL 2022 servers in our customers SQL estate, so this has flown under the radar for me. This will be an […]
Continue readingBy 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 readingBy Lori Brown | Helpful Scripts , Jobs , SQL Administration , SQL Server
Since I seem to run across lots of questions about how to make sure that no jobs get in the way of ETL or other stressful data loads, I thought that I should post some ways you can stop jobs. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-stop-job-transact-sql?view=sql-server-ver16 I have always done this by having a monitoring job that executes on a […]
Continue readingBy Lori Brown | Helpful Scripts , Indexes , Replication , SQL Server
Problem Have you ever had transactional replication mysteriously start showing significant latency at a subscriber server? If so, check to see if the primary keys from the publisher database are missing on the subscriber database! Replication was showing long latency while the publisher and subscriber servers were not heavily utilized. Microsoft generated stored procedures that […]
Continue readingBy 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