January 17, 2019 at 5:24 am
Hi experts ,
I am getting the following error in production environment.
[Msg 8623, Level 16, State 1, Line 1
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.]
We have UAT environment were the same Query works ,UAT environment is exact copy of Prod and all the tables have same no of rows.
Now the business wants to know why the Query is not working in production environment,
Can you guys tell me what all parameters should i check.
Note :-
1) We have fixed this issue by tunning the Query in Production Environment.
2) Sql server Version
Microsoft SQL Server 2012 (SP3-GDR) (KB4019092) - 11.0.6251.0 (X64)
Jul 7 2017 07:14:24
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )
January 17, 2019 at 7:01 am
Ooh, that's very rare. I'd love to see the query and the execution plan from UAT.
In all likelihood, there are differences in settings between your UAT and production. Not simply that the database is the same with the same number of rows, but the physical boxes are different or the vms are different, server settings or database settings are different. All sorts of things from ANSI connection settings to Max Degree of Parallelism to Cost Threshold for Parallelism and a ton of others can affect the generation of execution plans. Don't assume things are the same. Check everything.
"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
January 17, 2019 at 9:44 am
Hi,
Please find the attached UAT execution plan . I am not a dba can you please provide me a list of things that i should check.
January 17, 2019 at 12:38 pm
Wow.
Well, I can see how a server had a problem. So, in the query, you have 10 objects. In the execution plan you have... more than 10. So, I guess this means you're pulling all this data from views. On a hunch, those views call views which call views which join to views... you get the idea.
First thing, stop doing that. SQL as a language isn't real good with code reuse. So when people try to treat views like objects in code and just glue them all together, it leads to stuff like this.
You have two issues here. You absolutely need to simplify this query. I'd suggest, as a first pass, maybe breaking down the views and everything to see if you can simply query the data you want directly from tables. If not, try loading data into temporary tables and then joining it together. This is going to be a very difficult nut to crack. It's going to take a long time and a lot of effort to figure this out.
The other issue is why the query caused an error on the server. That has to be a server setting. One has more memory than the other. One has a different cost threshold. All the stuff I mentioned before. You have to check it all.
Another issue you have is that the statistics are out of date (or possibly it can't use them). The estimates and actuals are all over the map. The final row count is 79, but the estimated number of rows is 18,000. That's a pretty wide disparity. Within the plan itself I can see that the table [ParisUAT_04].[stgsystem].[ts_rev_exp_sum_20180930].[XIE0ts_rev_exp_sum_20180930] thinks that it has 37 rows and actually has 17,000. That's a very wide disparity again, in the opposite direction. So, probably, on one, or both, servers, you need to update the statistics.
Although, it's entirely possible that updated statistics are the problem. If the row counts were wildly different on the servers, one could compile a workable plan and the other might not.
Hopefully that gives you a little bit to work with. If I come up with more, I'll post it.
"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
January 17, 2019 at 12:42 pm
One more thing, because you seem to be reusing views, the data is getting duplicated and move multiple times. Those views are the first candidates for loading into a temporary table.
Is this data partitioned by month and you're combining all the partitions? Cross partition queries are notorious for this kind of problem. Are you partitioning for performance reasons (if so, I don't think you're getting it)? Partitioning should generally only be done for data management reasons in order to avoid this sort of issue.
"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
January 17, 2019 at 3:18 pm
Except for needing to use the actual underlying tables instead of nested views (if that is what is actually happening) you may want to look at creating indexed views to pre-aggregate the data for reporting purposes.
January 18, 2019 at 12:33 am
arvind.patil 98284 - Thursday, January 17, 2019 9:44 AMHi,
Please find the attached UAT execution plan . I am not a dba can you please provide me a list of things that i should check.
The query is too bad.
But not as bad as underlining table structure.
The problem is - the view ts_rev_exp_sum unites too many tables, and the query repeats the calls to this view 4 times, multiplying the UNION's by 4.
The ultimate fix would be to ditch "monthly" tables and put all the data into a single big table with a clustered index on the date which appears in the name of those "monthly" tables.
But I guess it's too far reach, so let's see what "second best" option you've got here.
First, this CASE WHEN TSUM.day_id = 20180930 THEN looks silly in presence of this: WHERE (TSUM.day_id = 20180930) .
Get rid of the useless CASE statements.
Second, you have this common check: AND TSUM.item_cd IN (
SELECT DISTINCT item_cd
FROM tc_std_item_gl
)
It's commonly ineffective.
Replace it with AND EXISTS (
SELECT * FROM tc_std_item_gl gl
WHERE gl.item_cd TSUM.item_cd)
And the last, but most important - get rid of the UNION's.
You have only 1 condition which is different in different parts of the query: AND (TSUM.source_cd LIKE 'me%')AND (TSUM.source_cd NOT LIKE 'me%')AND (TSUM.source_cd LIKE 'me-misc%')
The 3rd condition overlaps the 1st one. All the records with TSUM.source_cd starting with 'me-misc%' will be accounted as both Order_id = 2 and order_id = 3
Looks like a logical error to me. Please check if it's correct.
These 3 conditions must be used in CASE statements:CASE WHEN TSUM.source_cd LIKE 'me-misc%' THEN ''
WHEN TSUM.source_cd LIKE 'me%' THEN Prod.product_title
WHEN TSUM.source_cd NOT LIKE 'me%' THEN ''
ELSE NULL
END AS product_title,
CASE WHEN TSUM.source_cd LIKE 'me-misc%' THEN Prod.mpm_number
WHEN TSUM.source_cd LIKE 'me%' THEN ''
WHEN TSUM.source_cd NOT LIKE 'me%' THEN ''
ELSE NULL
END AS MPM_number,
..... - it seems no changes in following two items
CASE WHEN TSUM.source_cd LIKE 'me-misc%' THEN 3
WHEN TSUM.source_cd LIKE 'me%' THEN 2
WHEN TSUM.source_cd NOT LIKE 'me%' THEN 1
ELSE NULL
END AS order_id
The 4th part of the UNION query appears to be a "total" line for all the records included into the query.
If this my assumption is correct you better implement it using GROUP BY TSUM.source_cd,TSUM.day_id, c.source_schedule_date_time
WITH ROLLUP
This will build the aggregation from the records returned by the query, without re-scanning base tables once more.
Hope it helps.
The code above is not tested, and not even thoroughly checked.
Please verify it for possible logical or any other kind of mistakes.
_____________
Code for TallyGenerator
January 18, 2019 at 11:52 am
Grant Fritchey - Thursday, January 17, 2019 12:38 PMWow.Well, I can see how a server had a problem. So, in the query, you have 10 objects. In the execution plan you have... more than 10. So, I guess this means you're pulling all this data from views. On a hunch, those views call views which call views which join to views... you get the idea.
First thing, stop doing that. SQL as a language isn't real good with code reuse. So when people try to treat views like objects in code and just glue them all together, it leads to stuff like this.
I have to echo those sentiments. This is truly one of the most beautiful execution plans I have seen that demonstrates how much of a bad idea it is to use nested views.
Simplify the query. Simplify the views.
You may find that a divide and conquer approach is a far better approach (load this piece of data, then that piece and so on until everything glues together quickly and nicely). That sometimes works for miserably ugly execution plans like this.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 18, 2019 at 7:19 pm
Grant and Jason, can you point to that thing in the execution plan which suggests there is at least one nested view in the picture?
_____________
Code for TallyGenerator
January 22, 2019 at 6:08 am
Sergiy - Friday, January 18, 2019 7:19 PMGrant and Jason, can you point to that thing in the execution plan which suggests there is at least one nested view in the picture?
I'm rethinking my original take a bit. I suspect it's partitioned views that are being reused. However, the reason I think there are views at all is because of the repeated patterns of multiple objects not referenced within the query. I suspected first that they were nested views because of the repetition of the pattern (joining and/or calling the same view from other views frequently looks like that).
I haven't look at the entire thing in lots of detail, but I saw repeated table names all based off a month, so, that's why I've shifted from nested views to partitioned views. Although, it still could be both, nested and partitioned views. Without seeing the details, it's hard to know for certain. That's why I used weasel words like "hunch" and "guess" when suggesting the possible problem. I can't see everything, so I'm just going off what evidence I have in front of me.
"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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply