DallasDBAs.com

SQL Server Database Consulting

SQL

SQL Server Wait Stats

by Leave a Comment

Everything in SQL Server is waiting for something…this is by design. And most people never think about it…

Until your application is waiting to complete its request, sell the product or provide “the report” to the CEO!

Most SQL Server performance issues come down to one thing: waiting. Every time a query slows down, SQL Server records what it was waiting on. These wait stats are one of the best diagnostic tools for DBAs, sysadmins, and developers – but only if you know how to use them.

What Are Wait Stats?

SQL Server tracks the time spent waiting on resources like CPU, memory, disk, or locks. Think of them as traffic reports – some delays are normal, while others indicate serious bottlenecks. If your database is slow, wait stats tell you where to start looking.

How to Read Wait Stats

A simple query can reveal where your server is spending the most time:

SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms 
FROM sys.dm_os_wait_stats 
ORDER BY wait_time_ms DESC;

Key columns:

  • wait_type – The specific type of wait that might be affecting performance
  • waiting_tasks_count – Number of times this wait has occurred
  • wait_time_ms – Total time spent waiting (cumulative since last clear)

 

Common Wait Types and What They Mean:

A table of various SQL Server Wait types and possible fixes

 

Many other waits exist, but these are some of the most common. Microsoft provides a full reference here.

SQL Skills has the best list of wait types and things to know I’ve ever seen. Go there and read before changing anything. MANY recorded waits are benign and not worth looking into.

Finding and Fixing Performance Issues: A guide for you to try in your development environment

  1. Run the wait stats query to capture the current state.
  2. Identify the top wait type and correlate it with system metrics. Research it
  3. Apply a tuning change (indexing, parallelism adjustments, memory tuning).
  4. Re-run the query to compare results.

This process provides a clear before-and-after snapshot of how performance improves.

The Bottom Line

Wait stats don’t tell you exactly what’s broken, but they show where to investigate. The key is to correlate them with query execution plans, server configuration, and system resource usage. Tracking these waits over time can also help spot performance trends before they become critical issues.

SQL Server is always waiting for something. The question is – what are you going to do about it? What are you waiting for?!

Thanks for reading!

–Kevin

Get the first month of Pocket DBA® FREE from Dallas DBAs!

 

Code That Writes Code

by Leave a Comment

“Work Smarter, not Harder”

We’ve all heard it before, pretty much any job, anywhere.

In our DBA slice of the IT world, this is very relevant to how we manage SQL Servers and the databases on them.

From scripts to check for Bad Things™, to writing efficient code.

In this video, I show a simple way to get SQL Server generate scripts you can run.  In this case I needed to run the same command against a large number of databases. Running it manually, or worse, doing it in the GUI is just not an option.  Enjoy!

The code that I will now use as a starter script:

-- Don't run this if you don't understand it 100%

Select 'ALTER Database ['+[name]+'] set Page_Verify Checksum, recovery Full, auto_shrink off;
go'
From master..sysdatabases
Where DBID > 4

Thanks for reading!

Kevin3NF

Top 3 Upgrade & Migration Mistakes

by Leave a Comment

This is a guest post by another friend of Dallas DBAs – Brendan Mason (L|T)

Upgrading and migrating databases can be a daunting exercise if it’s not something you practice regularly. As a result, upgrades routinely get dismissed as “not necessary right now” or otherwise put off until there is not really another option. Maybe the physical hardware the instance was running on had a motherboard melt down, or perhaps the vendor of your ERP software is forcing your hand by no longer supporting that near decade old version of SQL Server. Or maybe you’re running a current version but find yourself along for the ride when the application team decides they’re moving from on-premise to the cloud.

Everyone wants upgrades and migrations to go smoothly, particularly the folks on the business side of the house who control budgets for outside help. As consultants, we get called in to help with these projects a lot. I have personally been involved in hundreds of database migrations as both an employee and a consultant. It has exposed me to a lot of different scenarios, good and bad. In this post, I’m going to talk about the top 3 mistakes I see companies make when it comes to SQL Server upgrades and migrations.

#1 – Deciding on the solution before analyzing the goals

It seems that everyone thinks they need AlwaysOn Availability Groups these days, whether they know what it is they do or what the alternatives are. Most often, a kick-off meeting for a migration project starts with, “We need you to set up our new Availability Group so that we have multiple copies of the database and never have another outage. We have VMs built and ready for you to install SQL Server on.” This constitutes being proactive to a fault. Sometimes the entire architecture needs to be redone when we tell them that Availability Groups won’t solve their particular use case or give them what they were led to believe, or that their budget simply won’t accommodate Enterprise Edition. Getting too far ahead of yourself can also cost you extra money. I have seen companies go out and buy physical hardware for their new environment, only to discover during our conversation that the processors they paid extra for have more cores than the SQL Server Standard Edition instance they want to use will be able to utilize. It adds insult to injury when I tell them that Microsoft will expect them to license all those cores for SQL Server even when the lone instance won’t be using them all.

In a recent example of a pre-determined solution that was incompatible with the upgrade goals, a client wanted to perform an in-place upgrade of their SQL Server 2012 instance running on Windows Server 2012. They didn’t want to upgrade the operating system and had correctly noted that SQL Server 2019 was not supported on Windows Server 2012. Based on this, they had decided that 2017 Standard would work for them and they just wanted some outside expertise to help with the upgrade process. It wasn’t until we were days away from the upgrade that they shared that Transparent Data Encryption (TDE) was a must-have feature that they needed to enable post-upgrade. This is an easy misinterpretation for someone not well versed in SQL Server editions and features to make. TDE had been an Enterprise Edition feature until 2019 when it was finally included with Standard. The client had been looking at the edition comparison for 2019 and didn’t realize that TDE was not available in Standard edition for the earlier versions.

Another common one I see is the decision being made up front to use a database Platform-as-a-Service (PaaS) solution. I’m a big fan of PaaS in general, but there can be a lot of gotchas – especially when it involves migrating a legacy database.

Remember, we’re here to help guide you during the planning phases, not just the execution, so I recommend engaging early in the process. The more details you share about what problems you’re trying to solve and what you want to get out of your upgrade/migration project, the more likely it is that you’ll land with a cost-effective solution that does exactly what you need.

#2 – Not having an adequate testing plan

You can do everything right as far as sizing your new environment and choosing the right SQL Server edition to meet your requirements, but if you don’t actually test before migrating, you’re taking a huge risk. Inevitably, there is almost always a forgotten dependent application or an old stored procedure that uses an obscure piece of syntax that’s no longer supported. There are also changes made to the database engine with each new version that usually improve query performance, but occasionally cause regressions for certain queries and workloads. If the testing plan is to just “click around” and make sure the application still functions, you can easily miss issues that can escalate under full user load.

Part of testing should be collecting baseline performance metrics. You can’t effectively compare performance of the upgraded databases without a baseline from the source. You can and should also take this opportunity to perform some basic performance tuning prior to upgrading. There have been several cases where I have been able to recommend a reduction in the number of CPU cores and associated cost for the migration just by looking at accurate baseline performance metrics and/or making a few minor performance tweaks.

Another often overlooked part of testing is the usual myriad of connected services and applications. Database Mail, SSIS, SSRS, linked servers, etc. all need to be validated during testing to avoid surprises at go-live.

Note that a solid testing plan is not a replacement for an equally solid rollback plan. You don’t want to be trying to come up with one on the fly. Once a SQL Server database is upgraded to a newer version, you can’t back it up and restore it back to the older version. That means that if you discover a major issue post-upgrade, you need to either work through it, roll back to the database backup that was taken immediately prior to upgrading, or have a process in place to reconcile the deltas.

#3 – Not taking advantage of the new version

Every new version of SQL Server comes with improvements to performance, security, and functionality. You’re paying for these things, so you might as well use them, right? Unfortunately, clients can often be reluctant to take full advantage of these enhancements as part of an upgrade project. Those who wait to upgrade until the SQL Server version they’re running is about to go out of support tend to just want to get upgraded with as little change as possible. However, you’re missing out on a lot of benefits when you take that approach. Everyone will have a slightly different situation that dictates which new features warrant trying out, but there are a few general recommendations I have for getting more value from your upgraded SQL Server instance:

Query Store – When upgrading from a SQL Server version prior to 2016, I suggest enabling Query Store after the upgrade. It’s a valuable tuning tool (the so-called “flight recorder”) and you don’t want to wait until you really need it to turn it on.

Compatibility level – In order to take advantage of all the latest enhancements and Query Optimizer fixes, your databases should be at the latest compatibility level. Leaving them at their legacy setting post-upgrade is the “good enough” approach, but if you’re doing proper testing, you might as well test changing the compatibility level while you’re at it. There are tools that will analyze database code and queries and identify incompatibilities, so there’s not a good excuse to not at least test this. Unlike upgrading the database itself, changing the compatibility level is not a one-way operation and it’s a trivial change to go back, if necessary. Bonus points if you enable Query Store prior to changing the compatibility level – it can help identify performance regressions and target individual queries that might need attention. Just always be sure to follow vendor requirements if the database is used by third-party software.

Keep in mind that I’m not advocating that you do these things on go-live night as part of the actual upgrade. I’ve simply witnessed too many upgrades that ended with the database online and passing all the smoke tests, but with no further effort devoted to maximizing value gained from the upgrade.

Wrapup

There you have it – Those are the top 3 mistakes I see during upgrades and migrations. This by no means comprehensive and I have purposely left out some especially cringeworthy experiences to protect the guilty. Upgrading versions or moving to new hardware or platforms doesn’t have to be painful. In fact, with good processes and procedures it should be so predictable and straightforward that it becomes a regular part of keeping your systems up to date.

 

ED: Please feel free to leave some comments in the ummm…comments section!

Analyzing an Upgrade after the fact

by Leave a Comment

We had a major upgrade to our main application recently, so I thought I would share some of the “thoughts from the aftermath.”

The good:  We gave the application back to the business 1/2 of a business day earlier than expected.   Win.

The bad:  We were hoping to give it back a full day earlier

The ugly: we wasted a full day having to rollback and start over

Some lessons learned:

  • Full backups ahead of time are a great idea – check
  • Restore scripts for those backups are a great idea – check
  • Using SQLCMD to apply vendor .sql files is a great idea – check
  • Forgetting the -I parameter in SQLCMD is a bad idea (not -i, but -I for Quoted Identifiers, which is false by deafult in SQLCMD, but True in SSMS)
  • Not having a full setup lab environment to test everything – bad
  • Using the default settings in SSDT Schema Compare – bad
  • Re-ordering column order in tables for cosmetic reasons – bad (this one is on the vendor…we had to re-write some code that broke)
  • KrispyKreme donuts in the war room followed by a huge box of cupcakes – bad.  Didn’t affect the upgrade but I ate too much 😉

Those are the big items.

This post inspired by Tim Mitchell‘s post today : Studying Failures

Feel free to share your deployment/upgrade brilliance or derp moments in the comments below.

Kevin3NF

 

Restore all databases at once

by 5 Comments

We are doing a major upgrade this weekend, so like any good DBA, I have planned for a full backup the night before and needed the ability to quickly restore if it goes sideways and needs to roll back.

The catch is that there are close to 300 identical databases involved.

This is easy enough to do if you know where info is stored in MSDB related to the last backup.

I’ll just leave the code I used right here for your amusement:

Use MSDB;
go
 
--generate the list
Select
    max(backup_start_date) as Backup_Date,
    database_name,
    Max(physical_device_name) as physical_device_name
Into
    #lastFullBackup
From
    msdb..backupset bs
    join msdb..backupmediafamily bmf
        on bs.media_set_id = bmf.media_set_id
Where 1=1
    and backup_start_date > getdate() -1
    and type = 'D'
Group By
    database_name
Order By
    database_name
 
--Create the restore commands
Select
'restore database ['+database_name+'] From disk = ''' +physical_device_name+''' With replace, recovery'
From
    #lastFullBackup
Where
    database_name like '%MyDB%[0-9]'  -- database1, database2, etc.
 
--Cleanup
Drop table #lastFullBackup

There are probably much easier or prettier ways to do this, but I’m only 2 cups of coffee in thus far, and one of them got cold…

If any of the above doesn’t make sense, please drop a comment. It works on my system and should be valid for SQL 2000 – 2016…the system tables referenced have not changed.

Have a bright, shiny day full of puppies or muscle cars….whichever you prefer!

Kevin3NF

Home Blog About Privacy Policy