How SQL Server compiles, optimizes, and executes individual queries.
573 associated posts292 primary posts
I’ve been told that an attendee at the PASS Summit pre-conference event asked about using dynamic SQL to get around some of the problems with filtered indexes. I’m not entirely sure what the question was, but it did give me the opportunity to play around with filtered indexes and write some simple demo code to illustrate just the kind of shenanigans that you can get up to.
What Developers Need to Know About Designing Databases
https://twitter.com/SQLintheWild/status/393386854000492545
One of my favorite tips, and I never thought of it that way. Code is agile, and databases are brittle. It seems easy to refactor the database in the beginning, but as your app grows and more stuff interfaces with it, life gets a lot harder.
The questions came fast and furious in one of my recent TempDB webcasts, so here's the ones I wasn't able to answer during the live session:
Q: Virtualized OS, SAN, no dedicated LUNs, most likely scenario with no gotchas, theoretically: dump TempDB with everything else all on one virtual volume, including logs -- or split to separate virtual volumes and separate from other databases and also from logs? (Matthew N.)
Let me ask you a question: do you want to boss the SQL Server query optimizer around?
If you answered no: good. You’re willing to let the query optimizer do its job, which is to find the least expensive way to run a query, as quickly as possible.
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…
When you query sys.dm_os_wait_stats, or check your waits with sp_Blitz® or sp_BlitzFirst®, one of your biggest wait types is probably CXPACKET. Let's talk about what's really going on. Our SQL Server Stand-In: a Class. Pretend that you're a schoolteacher in a classroom with a couple dozen students. You've got a class project that you need to…
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!)
Imagine you work for a zoo, and you need to ship an animal to another zoo. If you are told one will be a mouse and one will be an elephant, you will need to handle them differently. Elephants get moved by truck, while mice can be put in a box and driven or flown.
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.
Aggregate functions are convenient - they solve a business need and they make development easy. Unfortunately, not all business requirements are so easy to solve. Let's look at one example: rolling averages. The Rolling Average A rolling average is a simple concept; an average is computed over a fixed subset of data. Rolling average calculations…
When someone brings me a query to tune, I start with a few questions:
How long does it take to run now? I'm looking for a time at first, and later as I progress through tuning, I'll measure it more scientifically with logical reads and CPU cycles.
I’m curious. I like to know how things work. I have to read the user manual of everything I buy so I know all of its features. I like clicking all the buttons in applications I work with. However, SQL Server Management Studio (SSMS) has so many I haven’t gotten through all of them. At SQL Saturday #118 Wisconsin earlier this year, I was watching a presentation and the presenter clicked “Include Client Statistics”.
When you send your beautifully hand-crafted organic T-SQL statement to SQL Server, the database engine takes a moment to appreciate the poetry of your work. Before it turns your request into results, it has to build an execution plan. SQL Server considers: Which tables it needs to join together What subqueries it needs to execute…
http://youtu.be/2c33WStd94s
Want to help us shape our training? Take 30 seconds and answer 5 questions.
What are the costs when you ask SQL Server to recompile a statement or a stored procedure each time it runs? In this 30 minute talk Kendra Little explains the impact on query execution-- and on your SQL Server instance-- of forcing recompilation. This talk is appropriate for DBAs and Developers who have a working knowledge of execution plans in SQL Server.
What’s slowing down production? It’s easy to find out in 60 seconds by running a simple plan cache query. See, in order to turn your beautifully written query into a set of results, SQL Server needs a plan – an execution plan. It caches these plans in memory along with statistics like the number of…
https://www.youtube.com/watch?v=yW-Re4TJcJ0
If you want to learn more about the different join algorithms, consult Wikipedia for an introduction to the basic material and links to additional information:
Data masking, data scrambling, and just plain old obfuscation: these are ways to get developers access to production-quality data for testing purposes without actually giving them real production data. It's much harder than it looks, though. The Easy Part: Obfuscating Data Developers love working with production data. Today's privacy-concerned companies aren't quite so keen on…
If you're only indexing to make reads faster, you need to think again. Indexes can make every operation in the database faster, even deletes. The Problem: Deletes are Very Slow I was lazily researching new development techniques one day when I received an email from a client asking why deletes could be slow. I rattled…
