Best Practices Week

Monitoring

Them: It’s slow again!

DBA: What does slow mean? Slow compared to what?

How can you tell if something is actually slow unless you can define what normal performance is?

Generally speaking, SQL Server performance conversations revolve around queries or application response time. End users have very little tolerance for slow apps and that can be measured in seconds more often than not. Slow backups or other maintenance can cause issues as well…running over the normal time and using resources needed by other functions.

How do you monitor this?

Built into SQL Server, there a ton of Dynamic Management Views, Job History, and the much-maligned Activity Monitor in SSMS. The DMVs are a rich source of information on everything from index usage to query performance to information about the SQL Server services at the instance level.

If you are like me, querying the dmvs is a bit of a challenge as there are times when they need JOINs or Cross Apply to another dmv to give a complete picture. This is where I encourage you to NOT reinvent the wheel.? Instead, download and learn the FREE tools from Glenn Berry, Brent Ozar and Erik Darling. They are not the only contributors in the SQL Server Family but they are very, very good. I use some of these tools every day.

For a RIGHT NOW issue that is happening, I love sp_whoisactive from Adam Machanic. I have used this so many times to tell a client what the offending query was, that I owe Adam a truck of beer. This can be run as a job and logged to a table so you can look backwards as needed.

If you are fortunate enough to have budget for a commercial tool, I highly recommend Solarwinds Database Performance Analyzer. This shows query performance information down to the minute level, 30 days of history that persists through a reboot and can also show counters for hardware resources.?

Note: I am not affiliated with Solarwinds

In SQL Server 2016 and above, Query Store is amazing at helping to identify bad query plans. There’s a learning curve involved, but its worth it. The Alerting system that has been in SQL Server for ages can give you a heads up as well.

What other ways do you monitor and research your SQL Server performance??

My recent LinkedIn post(s):

Best Practices - Backup and Recovery

Best Practices - Maintenance

Best Practices - Performance

Interesting stuff I read this week:

Applebee's date night pass sells out after website crashes | 9news.com Curious how many of these were actually sold.

Wayfair cuts targeted remote staff | LinkedIn – another week, another bashing of remote worker

Microsoft hits $3 Trillion market cap, lays off 8% of gaming staff. Hopefully not my son-in-law

We have bandwidth for 3 new Pocket DBA? clients. Let’s chat.

SQL tidBITs:

SQL Server Management Studio had a different name before it was originally released. Do you know it? Not Enterprise Manager ??

Sponsored by: Coffee!

Share with your friends…5929 subscribers can’t be wrong! Can they? Nah…