A Fine Slice Of SQL Server
Kendra Little describes the nested loop operator in execution plans:
One of the first things we often learn when we’re looking at plans is that SQL Server uses estimates. And sometimes, those estimates are wrong. At first glance, this looks really wrong– it estimated 11.5 rows, and actually got 20,825 rows!
Read the whole thing to see how nested loop estimates which seem way off might not actually be.
Comments closedPedro Lopes mentions that the latest version of Live Query Stats in SSMS 2016 has some improvements:
Below the finished query. Again, overall query execution cannot go over 100%, but at the operator level, percentages are shown as the real ratio between actual and estimated rows, with no caps.
So you can see how the actual rows from the clustered index scan on PhoneNumberType table was 14500% above estimations, and how a series of severe misestimations are coming from the bottom right area of the plan (where actual is not even 1% of estimated rows), worsening as it goes up in the nodes.
I think this makes Live Query Stats a better tool for query analysis. I haven’t used it much in production, but this makes me want to give it another try.
Comments closedBrent Ozar has a guide on troubleshooting parameter sniffing:
Parameter sniffing fixes are based on your career progression with databases, and they go like this:
1. Reboot the server! – Junior folks panic and freak out, and just restart the server. Sure enough, that erases all cached execution plans. As soon as the box comes back up, they run rpt_Sales for China because that’s the one that was having problems. Because it’s called first, it gets a great plan for big data – and the junior admin believes they’ve fixed the problem.
2. Restart the SQL Server instance – Eventually, as these folks’ careers progress, they realize they can’t go rebooting Windows all the time, so they try this instead. It has the same effect.
If a reboot can’t fix the problem, I’m out of ideas…
By the way, I second Brent’s recommendation of Erland’s query plan article. Erland doesn’t publish frequently, but when he does it’s worth the wait.
Comments closedGrant Fritchey gives us a potential nightmare scenario with Query Store:
Along comes our aggressive junior DBA who decides that there are “too many” indexes on the server. No, I don’t know what that means either, but they evidently read it on the internet or something so they drop the index we created before:
DROPINDEXTransactionOccurredWhenNCIONWarehouse.StockItemTransactions;What now happens to our lovely execution plan and the plan forcing? We’ll take a look at two events in Extended Events, sql_statement_recompile and query_store_plan_forcing_failed. Nothing happens immediately on dropping the index. The plans associated with that object, if any, are marked as invalid in the cache. The next time we call the query it’s going to recompile and we can see the event:
The result is a bit happier than I would have expected; I was looking forward to a “and the world came crashing down” conclusion.
2 CommentsBrent Ozar has time to kill and a 72-core box on which to kill it:
For example, when I run it with 10,000 joins:
- Msg 8631, Level 17, State 1, Line 1
- Internal error: Server stack limit has been reached. Please look for potentially deep nesting in your query, and try to simplify it.
When I drop it down to a much more realistic 5,000 joins:
- Msg 4074, Level 16, State 1, Line 1
- Client drivers do not accept result sets that have more than 65,535 columns.
Ah! Okay, that’s fair. (That’s also two error messages I’ve never seen before. Probably a good thing.) Alright, let’s take out the SELECT * and replace it with SELECT p1.* and see what happens:
This is a fun read.
Comments closedJack Li explains when you might not get a result back for query_plan in sys.dm_exec_cached_plans:
When you first execute a batch or stored procedure which may contain multiple statements. Not every statement will be compiled when you first use the procedure. If there are some code paths that result in the statement not being executed, SQL Server may choose not to compile that statement in a small set of scenarios. In other words, some statements will only be compiled upon first execution. So far, I have found the following two scenarios that a statement will not be compiled (deferred compilation) if code path result in that statement being skipped.
Statement involving temp table. Data could have been changed when the statement is actually executed. So it doesn’t make sense to compile right off the beginning.
Statement that has option (recompile) attached to it We need to recompile that statement every time anyway. If we don’t use, it why compile in advance?
Read the whole thing.
Comments closedGinger Grant explains several problems with nested views:
In addition to the performance reasons, there are other reasons not to use nested views. Supporting and maintaining a nested views can be a nightmare. If there is an issue with the accuracy of the data, finding the problem is just that much harder. And what about when you go to fix the problem? Then you need to test all of the places the view is called. I worked with some code recently where a view was created on a set of data just to create 3 new values based up 3 case statements. That view was nested many levels below where the field was actually called. I know people from a development background look at nested views as modular development. Don’t. TSQL is not the same as object oriented code.
Read the whole thing. She also has helpful links digging further into the topic.
Comments closedKenneth Fisher wants to compare two query plans using SQL Server 2016:
One of the new/not so new features that I’ve been playing with off and on is the ability to compare two query plans. I’m still relatively new at performance tuning and I frequently like to compare the query plans from before and after any changes I make. Historically I’d bring up both plans next to each other and walk through them section by section. You can see how this method would be a bit of a pain, particularly for large plans or plans that have changed quite a bit. But SSMS 2016 can now help us out.
Lets say for example I’m working on the view [AdventureWorks2014].[HumanResources].[vEmployee]. I decide that the OPTION FAST will speed the overall performance up.
If you can’t get SQL Sentry Plan Explorer installed on your machine for whatever reason, this is a viable alternative.
Comments closedRob Farley digs into what happens with a seek operation:
Let’s go back to our original query, looking for address types 2, 4, and 5, (which returns 2 rows) and think about what’s going on inside the seek.
I’m going to assume the Query Engine has already done the work to figure out that the Index Seek is the right operation, and that it has the page number of the index root handy.
At this point, it loads that page into memory, if it’s not already there. That’s the first read that gets counted in the execution of the seek. Then it locates the page number for the row it’s looking for, and reads that page in. That’s the second read.
But we often gloss over that ‘locates the page number’ bit.
The upshot is rather interesting: in certain edge cases, an uglier query can be better than an easier-to-understand query. If you do this, however, you definitely want to document it; otherwise, you’ll leave the next maintainer (which could be you!) confused.
Comments closedThe customer has lots of waits on RESOURCE_SEMAPHORE_QUERY_COMPILE. To troubleshoot this, we have to look from two angles. First, did customer have many queries needing large amount of compile memory? Secondly, was it possible that other components used too much memory, causing the threshold lowered? In other words, if SQL Server had enough memory, those queries requiring same amount of compile memory would not have been put to wait.
We used this query and captured for several iterations of data to confirm that server didn’t have queries that required large amount of compile memory per se.
It’s nice to have this trick up your sleeve when you simply can’t get a better query in place.
Comments closed