Diagnosing instance performance through waits, resources, monitoring, and baselines.
405 associated posts180 primary posts
This week, I'm demoing SQL Server 2019 features that I'm really excited about, and they all center around a theme we all know and love: parameter sniffing.
If you haven't seen me talk about parameter sniffing before, you'll probably wanna start with this session from SQLDay in Poland. This week, I'm going to be using the queries & discussion from that session as a starting point.
When you first hear about it, SQL Server's In-Memory OLTP sounds like a new feature that's hard to implement, requires a lot of schema changes, and takes planning and downtime.
I'm here to tell you it's simply not true, and you can do it in just 3 steps. Here's how:
Everybody tells you to capture baselines - but what exactly are you supposed to capture? Which Perfmon counters matter? How do you track which data files are growing, and which ones are slow? How can you track which queries are using the most resources? All you have to do is install the free, open source…
Update March 19: Microsoft has since acknowledged a hidden limit, then documented it, then raised it - but it’s still disappointingly slow.
In my last post, I explored how fast a $5,436/mo Azure SQL DB Hyperscale could load data. I'd had a client who was curious about spinning up their dev environment up there to see how query plans might look different. Well, as long as I was running this test, I thought - "How does this compare with Azure SQL DB?"
A client asked, "How quickly could we spin up a full copy of our database in the new Azure SQL DB Hyperscale?" Their database size wasn't too far off from the 340GB Stack Overflow database, so I decided to migrate that to Hyperscale to see how the experience went. Hyperscale is Microsoft's intriguing competitor to Amazon Aurora. Hyperscale…
(Cross-posted from the PASS Blog.)
Years ago, my first go-to tool for performance tuning was Performance Monitor: it was available everywhere, worked in all versions, and gave me a lot of useful information without paying for a monitoring tool. Since then, I've moved on - wait stats are a much better starting point - but there are still 3 good Perfmon counters that I rely on.
This week, Brent, Erik, Tara, and Richie discuss whether you need to premake a destination database in order to run sp_databaserestore, restoring production statistics on dev servers to simulate productions behavior, dealing with duplicate SPNs, tools for detecting all SQL Servers in a production environment, SQL performance monitoring, dealing with very large indexes, and Brent's recent cruise.
Gosh Darn
Now, look, it's no secret that I think Query Store is cool. Heck, I went and wrote sp_BlitzQueryStore so you can examine it similarly to how sp_BlitzCache looks at the plan cache.
For a variety of reasons, though, we run into people who can't or won't turn it on, or who had to turn it off.
This week, Tara and Richie discuss deletes on heaps, Docker, using containers in production environment, advice for new DBA, downgrading server hardware, agent jobs, setting SQL Memory Min and Max to the same value, how to prove CPU pressure to management using the Power BI report using sp_Blitz, corruption, patching, backups, sargability, and more.
You've got an Azure SQL DB, and your queries are going slow. You're wondering, "Am I hitting the performance limits? Is Microsoft throttling my queries?"
There's an easy way to check: run sp_BlitzFirst. sp_BlitzFirst is our free performance health check stored procedure that analyzes a lot of common performance issues and then gives you a prioritized list of reasons why your server might be slow right now.
When you need to find which queries are blocking other queries, your decision comes down to when the blocking happened. Is it happening now, recently, or coming up soon? During a live emergency, start with sp_WhoIsActive. Adam Machanic's excellent free sp_WhoIsActive replaces Activity Monitor, sp_who, and sp_who2, and it's way more powerful. The documentation is extensive -…
When I look at a SQL Server, one of the first things I check is, "How much memory does this thing have relative to the amount of data we're hosting on here?" I've long used some seat-of-the-pants numbers, but armed with data from SQL ConstantCare® users who opted into public data sharing, let's do a little deeper analysis.
This week, Tara, Erik, and Richie discuss using Docker in dev environment, in-place upgrades, disaster recovery, the minimum amount of CPU and RAM for new SQL Server VMs, troubleshooting long-running SQL agent jobs, dbcc checkdb running longer than normal, and more.
Here’s the video on YouTube:
This week, Tara and Richie discuss offline file database and log backup solutions, VSS database backups, monitoring products, parameterization, page latch waits, SQL ConstantCare™, and Availability Groups on 2017 Standard Edition.
Here’s the video on YouTube:
This week, Brent, Tara, and Richie discuss splitting large database files across multiple files, security auditing solutions, what Brent is currently teaching at the client location, SQL Server in containers, heap index maintenance, contained databases, extended events, the difference between a junior and Senior DBA, page file sizing, and more.
Here’s the video on YouTube:
SQL Server, thanks for coming today. Have a seat.
Yes, this might seem odd - there are a lot of us here.
During our Critical Care® sessions with clients, we often see unused indexes (reads=0) with high writes. Sometimes these unused indexes have very high writes. If an index is never being used to help with performance but SQL Server is having to maintain it for INSERT, UPDATE and DELETE operations, then why keep the index around?
When you run a query, SQL Server guesses how much memory you're going to need for things like sorts and joins. As your query starts, it gets an allocation of workspace memory, then starts work. Sometimes SQL Server underestimates the work you're about to do, and doesn't grant you enough memory. Say you're working with…
Raise Your Hand If
You've ever wanted to play a prank on your co-workers, but just didn't have a any ideas that didn't involve exploding Hot Pockets.
Now you have something even less safe than molten cheese squirts!
This week, Brent, Erik, Tara, and Richie discuss syncing logins and database data via log shipping, monitoring page life expectancy, cumulative update issues, when to change MAXDOP, doing SQL Server install on AWS, connecting to SQL Server instance on a virtual box from the desktop where the virtual box is running, and what real DBAs drink.
