November 11, 2011 at 12:42 pm
did it get progressively slower, or did it just happen all of a sudden?
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
November 11, 2011 at 12:46 pm
SQLRNNR (11/11/2011)
did it get progressively slower, or did it just happen all of a sudden?
It happened suddenly; it was completing daily in a few seconds until one day at the start of Nov. it took 2 hours.
It has been taking 2 or more hours every day since the start of the month with the exception of a couple of days here and there when it still completed in seconds.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 11, 2011 at 12:49 pm
The main problem here is lack of consistency.
How can I expect the developer to fix this, if it cannot be reproduced in the dev environment?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 11, 2011 at 12:52 pm
Very odd. When you updated stats, did you do it with fullscan? (Saw recently that a stat update with fullscan caused a query to run slower - just lobbing a guess there).
You also forced a recompile, correct?
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
November 11, 2011 at 12:53 pm
Marios Philippopoulos (11/11/2011)
The main problem here is lack of consistency.How can I expect the developer to fix this, if it cannot be reproduced in the dev environment?
You can't - totally an admin type task based on the results:cool:
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
November 11, 2011 at 2:47 pm
SQLRNNR (11/11/2011)
Very odd. When you updated stats, did you do it with fullscan? (Saw recently that a stat update with fullscan caused a query to run slower - just lobbing a guess there).You also forced a recompile, correct?
Yes, update-stats is done daily with fullscan. The downside of this is that it forces recompilation of perfectly normal exec plans as well.
My understanding is that any programming module referencing updated tables/indexes will be recompiled next time around (at statement level).
I forced a sproc recompile as well.
Needless to say, the SQL does not exactly fall within best practices.
There are a couple of views (not indexed views) joined with other tables, and, although this should be the same behind the scenes, joining a non-indexed view with normal tables just doesn't seem right.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 11, 2011 at 3:58 pm
Does the stored procedure have any parameters? Check the compile value for the parameters in the execution plan on dev/production. It's possible that it's a parameter sniffing issue. Generally this would be resolved by marking the SP for compile which you've already done though.
A full scan will give the query optimizer better statistics to work from, but even with accurate statistics the query optimizer can get it very wrong. Maybe it's optimized the query based on the initial parameter input, using statistics on those parameters to generate what it thinks to be the most optimum plan. In this case maybe it's assuming one one of the parameters is more selective than actually is.
It might help to post the query an execution plan here (maybe also with some DDL). As mentioned previously, view the query plan (actual plan) in SQL Sentry plan explorer. 18 billion actual rows from the nested loops is likely to be at the heart of the performance problem. It might be worth trying a hash or merge join hint. I prefer to avoid hints as a rule, but it can be useful to see if it makes a difference to performance.
Joining views with tables can be ok and they don't need to be indexed. The query optimizer will generally do a good job - the view get's combined with the query in a single execution plan. Different editions of SQL server can behave very differently with regards to indexed views though. Maybe this is the reason for the performance discrepancy? A noexpand hint might be required on standard edition. Dev edition is equivalent to enterprise edition which could explain the discrepancy. Also check that both have the same updates applied etc.
Check carefully the execution plans between the dev server and production to see where the differences lie.
Also, are any missing indexes reported. Any no join predicates?
DBA Dash - Free, open source monitoring for SQL Server
November 12, 2011 at 4:53 am
wiseman1982 (11/11/2011)
Does the stored procedure have any parameters? Check the compile value for the parameters in the execution plan on dev/production. It's possible that it's a parameter sniffing issue. Generally this would be resolved by marking the SP for compile which you've already done though.A full scan will give the query optimizer better statistics to work from, but even with accurate statistics the query optimizer can get it very wrong. Maybe it's optimized the query based on the initial parameter input, using statistics on those parameters to generate what it thinks to be the most optimum plan. In this case maybe it's assuming one one of the parameters is more selective than actually is.
It might help to post the query an execution plan here (maybe also with some DDL). As mentioned previously, view the query plan (actual plan) in SQL Sentry plan explorer. 18 billion actual rows from the nested loops is likely to be at the heart of the performance problem. It might be worth trying a hash or merge join hint. I prefer to avoid hints as a rule, but it can be useful to see if it makes a difference to performance.
Joining views with tables can be ok and they don't need to be indexed. The query optimizer will generally do a good job - the view get's combined with the query in a single execution plan. Different editions of SQL server can behave very differently with regards to indexed views though. Maybe this is the reason for the performance discrepancy? A noexpand hint might be required on standard edition. Dev edition is equivalent to enterprise edition which could explain the discrepancy. Also check that both have the same updates applied etc.
Check carefully the execution plans between the dev server and production to see where the differences lie.
Also, are any missing indexes reported. Any no join predicates?
No, procedure has no parameters.
Dev server is on developer edition.
Prod server is on Enterprise Edition.
They are both on the same build, 10.0.4000.
No missing indexes are reported.
Thank you for the input!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 12, 2011 at 5:15 am
Please upload the actual execution plan.
November 12, 2011 at 5:15 am
SQLRNNR (11/11/2011)
Very odd. When you updated stats, did you do it with fullscan? (Saw recently that a stat update with fullscan caused a query to run slower - just lobbing a guess there).
In fear of having another myth started, please note that this would be an extreme edge case, but yes, possible.
November 12, 2011 at 6:24 am
Ninja's_RGR'us (11/12/2011)
Please upload the actual execution plan.
I would love to do that, and I do understand the need for it. However, I will first have to check with my manager before posting the plan.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 12, 2011 at 6:57 am
Are the execution plans on the dev and live environments the same?
November 13, 2011 at 5:13 am
_Beetlejuice (11/12/2011)
Are the execution plans on the dev and live environments the same?
Plans are quite different.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 13, 2011 at 6:23 am
Marios Philippopoulos (11/13/2011)
_Beetlejuice (11/12/2011)
Are the execution plans on the dev and live environments the same?Plans are quite different.
Good, that leaves only a few 100 options on the table. How is it coming to be able to share those?
December 6, 2011 at 3:09 pm
is there any updates on this. I am facing something similar in my project, I tried everything mentioned in this thread but still not able to repro the defect in my dev environment. :crazy:
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply