When you identify that parameter sniffing is a problem, you need to test whether implementing 'recompile' hints will help plan quality. (Not sure what parameter sniffing is? Learn from this blog post or this 50 minute free video.) You must decide: what hint or command do you use, and where do you put it? What trade-offs are you making when it comes to being able to performance tune your SQL Server in the future?
On September 24, we published a blog post that we were looking for a new employee. We didn't advertise on any job listing sites (even though there are some really good ones out there). We tweeted about it a few times, but most of our publicity was that single post. We received more than 70 applications…
This question came up in the pre-show chat for our weekly webcast: when you rebuild a nonclustered index offline, does it impact only the nonclustered index? Or does it impact the entire table? Showing is more fun than telling! Let's take a look. First I restore AdventureWorks to slow storage Sometimes slower storage is handy.…
If SQL Server's Transactional Replication was an animal, it would be a cockroach. It's not pretty, but it's resourceful and strong. Once you get even a small amount of replication in your environment it starts spreading everywhere. And if it gets out of control-- well, in that case you've got no choice but to pretty…
Editors Note: this post was updated in June 2014 to link to an online index rebuild bug that can cause corruption. Once up on a time, there was a database server with 500GB of data and a heavy read workload of dynamic queries. Data was updated frequently throughout the day and index tuning was a…
SQL Server needs to have a reasonably correct estimate of how many rows your query is going to handle. If it doesn't, things can go terribly, terribly wrong. Most often, the problem is that SQL Server under estimates its work. In this case, the query optimizer thinks it has just a little bit of work…
You’d love to know how to make your SQL Servers more highly available, but you’re just not sure what “clustering” is all about. We can help! In this 30 minute video Kendra Little shows you how failover clusters make your SQL Servers easier to manage. She uses diagrams and drawings to teach you the strengths and weaknesses of failover clusters and shows why clustering is becoming even more critical for high availability and disaster recovery.
I'm going to tell you a secret. Index tuning is complicated, but it's something you can become great at. You just need to practice it regularly.
Here's that one step: stop thinking index tuning is a problem for Future You.
I came across an interesting case recently where queries were failing in a high transaction SQL Server. We knew that queries were occasionally failing with error severity 20 because SQL Server Agent alerts were periodically firing. However, those built in alerts don't capture exactly what has failed-- they just say "Something failed big. Good luck with that."
Recently, Jes asked the team an index tuning question: "If a query has an index hint in it, will the optimizer ever suggest a missing index for that query?"
I immediately loved the question because I'd never really thought about it before. I typically think of index hints as being a very risky game and avoid them whenever I can-- after all if someone drops the index you've hinted, any query hinting a non-existent index will start to fail. (That's a really bad day!)
sp_BlitzIndex® just can't keep a secret. That's a feature, not a bug. I wrote sp_BlitzIndex® specifically so that it would rat out some things that SQL Server is doing behind the scenes to secretly help you out-- because it's really better if you know about it. What are secret columns? SQL Server needs a unique…
Some query hints sound too good to be true. And, unfortunately, usually they aren't quite as magical as they might seem.
Frustration with unpredictable execution times
People often learn about parameter sniffing when query execution times stop being predictable. Occasionally you'll hear about a stored procedure taking much longer than normal, but the next time you look, it might be faster.
When it comes to indexes, SQL Server is really helpful. It lets you see what indexes queries are asking for both in execution plans, and missing index dynamic management views ("DMVs"). I like to look at the DMV missing index requests using sp_BlitzIndex®.
When you look at missing index requests, it's always important to remember one of the biggest things: these missing index requests won't ever ask for or recommend a specific clustered index.
Key length matters in SQL Server indexes.
It's a best practice to keep your index keys as narrow as possible, and SQL Server enforces a maximum key length of 900 bytes on most "normal" clustered and nonclustered indexes.
I'll never think "I've seen it all" when it comes to SQL Server-- there's just always someone waiting around the corner who's found a weird new thing to do with it. But there are some things I really wish I could stop finding.One of those things I hate finding is bad fillfactor settings. Fillfactor can…
We write a lot about best practices, we teach customers best practices, and we publish popular references for best practices about all sorts of things like performance monitoring, SQL Server Setup, and Availability Groups. But the internet is a sea of information and many SQL Server instances have unique needs. How does a SQL developer…
Developers and database administrators frequently clash on major questions about how to scale application performance. Are they just being stubborn, or is one side really right? Jeremiah and I recently gave a talk on this topic to the AtlantaMDF User group and we wanted to open this debate to the world. Someone's Right and Someone's…
Developers frequently make a big mistake: they tune out on discussions of storage performance and IO. Don't fall into this trap! Understanding how storage impacts SQL Server performance helps you tune the right parts of your code and focus on what really matters. In this 30 minute video, Microsoft Certified Master Kendra Little demonstrates why developers need to understand the IO impact on writes and reads in SQL Server. She'll show you how simple configuration changes can sometimes save hundreds of hours of developer time.
One of the many best practices for SQL Server's table partitioning feature is to create "extra" empty partitions around your data. This is explained in SQL Server Books Online in the page on altering a Partition Function: Always keep empty partitions at both ends of the partition range to guarantee that the partition split (before…
There's a secret to learning about SQL Server's table partitioning feature: you need to get your hands on some code and really play with it in a test environment.
In this tutorial, Kendra will walk you through test scripts that set up a sample table partitioning environment. These scripts demonstrate gotchas to look out for and the cool features that can dramatically change your database's performance.
