November 5, 2016 at 2:32 pm
Hello All,
I need help on this execution plan. I recently made some changes to many of my tables. In my original setup which unfortunately i don't have any execution plans for , I used a field called "DatabaseKey" VARCHAR(50) in the join. For multiple reasons i replaced it with a new column called DatabaseID INT which was added to each table. They effectively do the same thing by identifying the customer the data came from. Whether naive or i was just ignorant I went into each index and replaced the column DatabaseKey for DatabaseID and did a rebuild on all indexes. I have updated all of the stats and even cleared the procedure cache. However, where I was expecting a slight bump in performance i have got a horrible result. I'm struggling to figure out why the estimates for row count are so bad. i.e 300K rows actual to 400 estimated in the plan. In the plan everything seems to be doing a index seek ( i thought this good) but where it took less than a minute for a 5 million row insert before is now past 15 minutes. Very lost and in need of help.
If anyone needs to see the query and schema let me know and I can IM it to you. The execution plan is attached. Thanks !
***SQL born on date Spring 2013:-)
November 5, 2016 at 4:45 pm
Are the DatabaseID columns enforced as foreign key constraints, or are they just indexes? Since they are being used as foreign keys, you want to ensure that they are enforced. The optimizer takes this into account. If they are already foreign keys, make sure they are using the WITH CHECK option. What about the other join criteria. Are they also part of the foreign key constraint? Are they part of the index?
The issues are all over the place on statistics. [XI_Procedure_ProceduresID] is estimating 16,0603 rows and returning 545. [IX_DimInsuranceCarrier_InsuranceCarrierSourceID_DatabaseID] is estimating 1039 and returning 87. I suspect the keys on your columns are only on DatabaseID, but you're searching both the DatabaseID and these secondary columns. They'll work better if the secondary column is included in the key. Also, you should look at the distribution of both column, DatabaseID and the secondary one to ensure that you use the more evenly distributed one for the first column. This is because only the first column is in the statistics histogram.
Also, @vDatabaseID is a local variable. This means that you're getting average return values across the statistics based on the individual statistics, not actual values as if this was a parameter which takes advantage of parameter sniffing. You might, maybe (huge caution here), try doing a recompile on the query to ensure that you get the @vDatabaseID value sniffed. It could easily explain why it thinks it's returning 485 rows and is actually returning 38,000. The average value across your histogram is 485, but the value you're using this time is 38,000.
Also, just so you know, the optimizer timed out. This can come from two sources generally. First, the query is just complex. Second, there are inadequate objects for the optimizer to use, such as indexes and constraints. The optimizer can't limit choices fast enough because of these missing objects, so it does more work and then timeouts, resulting in a sub-optimal plan.
Interestingly, looking through the plan, I see a lot more skew where it over-estimated the number of values as opposed to under estimating. However, it does look like the over-estimation is the number one issue.
That's about all I have after a short look-through.
One more thing. All that aggregation, you might benefit from a clustered columnstore (although, on 2012 there are lots of restrictions on how they behave).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 5, 2016 at 5:31 pm
Hi Grant,
One of your biggest fans here. Currently on Chapter 4 of your 2012 query performance tuning book. So much to digest here. No Foreign key constraints as of yet. Did not know about that. I can't review tonight but I will be digging through this in more in depth early in the morning. (Wife has me doing family time in a few)
Thanks so much for the reply !
***SQL born on date Spring 2013:-)
November 6, 2016 at 3:14 am
Get in touch if you have questions. Hopefully the book is helpful.
I do think you're looking at the variable causing average values instead of specific ones to get a less than optimal plan, but you'd have to investigate the statistics to be sure about that. I do think enforced referential constraints may help though. Gives the optimizer more tools.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 6, 2016 at 9:15 am
Hi Grant,
So with the foreign key constraints I am have trouble testing that one out because I can't currently do "Cross-database foreign key references are not supported."
However next week I will be condensing the db's into one and will give it another try.
I did have a oh, duh moment when I hit page 109 of your book when you talk about the importance of column order on the index. I started to review many of my indexes and on some I had the order back wards. This is a EDW environment so there is always two columns used in the joins DatabaseID and the tables Primary Key SourceID from the customers production environment. The sourceID is highly selective while i only have about 18 different databaseID's. Sooooo, I reordered those ones and reran my queries and got almost a 50% increase in performance. While the estimates are still off they are not near what they were before. I think there is still a lot of room for improvement but a 50% increase in performance made me very happy.
Going to try and finish chapter 4 today looking for more nuggets. I also read one of your blogs last night and will reread to make sure i understand everything correctly about the foreign keys and performance.
Thanks Again !
***SQL born on date Spring 2013:-)
November 6, 2016 at 10:04 am
Oh cross database? I didn't even notice that. Sorry. That's what happens when you don't drill down and pay careful attention. Yeah, no cross-database foreign keys.
You might, might, see a performance improvement if you took each of the queries in the batch and put them into individual procedures. Then the DatabaseID value will be a parameter which might make the estimates more accurate (although, it can cause plans that don't always work well too). However, I'd probably still recommend that anyway. You'll get each one creating a plan when it's called instead of all at once. If you then work any IF/THEN logic in, the compiles are all individualized. Makes the code easier to read too (no real performance help, but if it helps your performance, that helps the databases performance too).
Happy you got the jump in performance. All this is entertaining and fun (although frustrating when the boss is breathing down your neck).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 6, 2016 at 1:54 pm
thomashohner (11/5/2016)
Hello All,I need help on this execution plan. I recently made some changes to many of my tables. In my original setup which unfortunately i don't have any execution plans for , I used a field called "DatabaseKey" VARCHAR(50) in the join. For multiple reasons i replaced it with a new column called DatabaseID INT which was added to each table. They effectively do the same thing by identifying the customer the data came from. Whether naive or i was just ignorant I went into each index and replaced the column DatabaseKey for DatabaseID and did a rebuild on all indexes. I have updated all of the stats and even cleared the procedure cache. However, where I was expecting a slight bump in performance i have got a horrible result. I'm struggling to figure out why the estimates for row count are so bad. i.e 300K rows actual to 400 estimated in the plan. In the plan everything seems to be doing a index seek ( i thought this good) but where it took less than a minute for a 5 million row insert before is now past 15 minutes. Very lost and in need of help.
If anyone needs to see the query and schema let me know and I can IM it to you. The execution plan is attached. Thanks !
If you look carefully at your execution plan, a lot of your sorts and hash matches have woefully underestimated row counts. Some of your Index Seeks are executing more than 160,000 times! That's at least 3 times worse than a single scan on a 160,000 row table.
I don't know what the original execution plan looked like but I'm thinking that it, too, was pretty bad and in need of a possible rewrite if not some sweat loving care. As for the indexes, I think I'd drop the ones being used (except for the CIs that are ever increasing and unique) and start over. You also have a lot of "<>" in your join criteria and that's not helping either.
The reason the old way may have been faster is simply because index or table scans are going to be more efficient than 160,000 seeks in a single operator.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 6, 2016 at 2:07 pm
Update removed the local variable and used the straight param from the procedure input. Estimates got even better. Current first pass run time 25 minutes 45 million rows total. Prior to changes 3.5 hours for same run. Still looking at tweaks but huge improvement !!
Thanks Grant !
***SQL born on date Spring 2013:-)
November 6, 2016 at 2:09 pm
Thanks Jeff, i'll keep looking odd thing is I only have one <> 0 in the joins. Unfortunately I have not figured out away around it.
edit: I lied I have two not <> the not equal to 'N' I can get around with a IN(), but the <> 0 I will have too think harder about.
***SQL born on date Spring 2013:-)
November 6, 2016 at 2:45 pm
thomashohner (11/6/2016)
Thanks Jeff, i'll keep looking odd thing is I only have one <> 0 in the joins. Unfortunately I have not figured out away around it.edit: I lied I have two not <> the not equal to 'N' I can get around with a IN(), but the <> 0 I will have too think harder about.
I'd take those out for troubleshooting and reworking the indexes. Also, it's not just the one query you need to look at. They all have similar problems (not including the <>).
To keep your head from exploding, peel just one potato at a time. Pick the biggest (resource usage query and fix it first. Keep in mind that it may be better to quickly isolate the core rows from one or two (possibly joined)tables for the query in a Temp Table and then join to that.
The key will be fixing those 3 index seeks that are right next to each other that feed nested loops and each execute between 160,000 and 190,000 times each. I drop all the NCI's in that whole leg and start over.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 7, 2016 at 6:22 am
I'm at the MVP Summit, so my responses are going to be slow & short for the rest of the week.
Sounds like real improvements have been made. Exploring the new plan would be in order to see if we're still seeing the same problems.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 7, 2016 at 9:52 am
What's already been said, but also:
Try forcing a join or two. You know that the join introducing Patientvisitprocs should be a hash join because of the volumes, and PatientVisit should ideally be the top input. In fact there are one or two options I’d try just to see how they affect the plan shape and the estimates, but I’d start with this one:
FROM Visanalyticshouse.Dimension.Patient Dp -- (32,198 / 1,061,130) few columns
INNER loop JOIN Patientvisit Pv -- (91,481 / 8,205,250) index keys (Databaseid, Patientsourceid), few columns
ON Dp.Databaseid = pv.Databaseid
AND Dp.Patientsourceid = Pv.Patientprofileid
INNER hash JOIN Patientvisitprocs Pvp -- (246,394 / 19,206,000) index keys (DatabaseID, Patientvisitid), many columns
ON Pv.Patientvisitid = Pvp.Patientvisitid
AND Pv.DatabaseID = Pvp.DatabaseID
INNER JOIN Batch B -- (11,737 / 574,326)
ON B.Batchid = Pvp.Batchid
AND B.DatabaseID = Pvp.DatabaseID
...
...
WHERE dp.DatabaseID = @vDatabaseID
Indexes to support this version might be keyed as follows:
Patient: (Databaseid, Patientsourceid) + INCLUDE columns
Patientvisit: (Databaseid, Patientprofileid) + INCLUDE columns
Patientvisitprocs: (Databaseid, Patientvisitid) + INCLUDE columns
Note that whilst you lose specificity by putting Databaseid as first key column, you might well gain by clumping target pages together.
Couple of extra pointers.
When you create the indexes, check the plan to ensure you’ve INCLUDE’d all the columns needed by the query.
Check that the indexes are used in the way you have anticipated and adjust if necessary.
Use SSMS2016, which displays “rows read” with compatible server versions (we’re on 2012). Very handy.
You may well find that with new indexes in place, the join hints can be dropped.
Keep an eye on optimiser timeouts. Often, all it takes is a hint or two to sufficiently restrict the search space.
Try commenting out a bunch of those lookups:
, ISNULL(Pro.Proceduresid, 0)
, Primaryinsurancecarrier = ISNULL(Di.Insurancecarrierid, 0)
, Financialclass = ISNULL(Fc.Financialclassid, 0)
, Responsible = ISNULL(Prov.Providerid, 0)
, Facility = Fac.Facilityid
, Company = Com.Companyid
-just to simplify the query.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 12, 2016 at 9:58 am
Hello guys,
I have tried several of the suggestions with mixed results. After finishing chapter 4 I think I am going to take Jeff's advice and start from scratch.
I'm not sure if this is the right approach but I'm going to start by adding a Clustered Index on each of the staging tables. I'll use a not exist to check for dup inserts from change tracking to keep it from failing on insert. For the incremental loads I don't think it will be much of a hit on the inserts. But on full loads I'll drop and rebuild. In the query plan above all of the tables in the Dimension schema have PK's while those that are not in that schema are just heaps with NCI's. They are very large heaps and when I was reading chapter 4 it made me think that even though I had the NCI's that they were probably still expensive because it was just a heap table.
I'll give it a try this next week and post back my results. So far this has been a very eye opening experience. You guys are fantastic!:-)
***SQL born on date Spring 2013:-)
November 12, 2016 at 4:03 pm
When you're writing your NOT EXISTS to check for duplicates, I'd test the performance of it against an EXCEPT. I've found that many times, the EXCEPT is faster. Just make sure you're SELECTing like data types.
I think you're on the right track with starting over to get rid of the Index Seeks that execute thousands of times.
December 3, 2016 at 8:21 am
Hello All,
Sorry this took a bit but I started over with all of the indexing. It led to much better results I added PK's to each of the stage tables. I also am testing out different fill factor changes. I'm in a EDW so right now I'm testing with 100. I have a much improved execution plan. My total execution time has remained the same however the rows processed has increased overall by 20 million now (65+ million) so I think the new changes are having a impact. I'm sure there is more that can be done. Attached is the latest execution plan.
Also I have added Explicit Trans with TRY CATCH, however another team member is asking if that will cause a performance hit. Any truth to that ? I'm doing it for data integrity and error handling. Just checking to see if I'm wrong on this part or if I should just leave this process implicit.
Thanks as always. Still learning !
***SQL born on date Spring 2013:-)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply