The Daily Dose Blog

May 08

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, […]

Apr 02

Finding Databases That Have Not Been Accessed (And An Adventure with ChatGPT)

By 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, […]

Feb 20

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 […]

Nov 13

New SQL 2022 Server and Database Roles and Permissions

By 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 […]

Oct 01

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 […]

Jun 12

Stop Jobs That Run Too Long

By 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 […]

Apr 11

Replication Slow at Subscriber Due to Missing Indexes

By 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 […]

Oct 12

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, […]

Aug 01

Monitoring Log Shipping Using T-SQL

By Lori Brown | Log Shipping , SQL Administration , SQL Server

Log Shipping has been around a long time and it looks like it will continue to be useful and therefor supported. Since I still find Log Shipping being used, let’s talk about a T-SQL way to monitor it.

1 2 3 … 22 Next »

The Daily Dose

The Prescription for SQL Server