June 15, 2012 at 6:25 am
Hi Everyone,
I hope you can all help me with a problem I have got.
We have a third party warehouse system installed and running on our SQL 2005 server. We have always had performance issues and until recently this has got a lot worst. Sadly the company who installed the third party warehouse system didn't put together a maintenance plan etc and are very unwilling to do any maintenance.
I've managed to identify a SQL Query which is taking an age to run and seems to be clogging up the SQL Server. I have had a look at the execution plan and noticed that there are a number of Clustered Indexes with costs ranging from 33% - 97%.
I'd be very grateful if someone could advise how to bring the cost down.
Thanks
June 15, 2012 at 6:42 am
Please post table definitions, index definitions and execution plan as a .sqlplan file, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
A picture of the exec plan is not that useful, most of the info in the plan is in the properties and tooltips.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 15, 2012 at 6:49 am
Sorry about that.
I've attached the SQL Plan files this is all one query.
June 15, 2012 at 7:09 am
Table definitions and index definitions please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 15, 2012 at 7:32 am
What's the best way to post this information?
June 15, 2012 at 7:44 am
Just the create table and create index (or alter table ... add constraint) statements. In an attachment if they're huge. Otherwise just inline (using the [ code ] blocks
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 15, 2012 at 8:04 am
Trying to break down the query from the execution plan, but it's too big. It's not all contained there.
You're getting an optimizer timeout on this query, so you can't trust the plan. Basically, it needs to be simplified. All the correlated sub-queries and the hundreds of columns, the use of DISTINCT on every query which is an aggregation function, using READPAST and NOLOCK hints, all these are very standard problematic T-SQL code smells. I can't even get down to the JOIN or the WHERE clause of the main query and I know you've got issues with the overall approach here. I'd suggest re-engineering the query. Break it down into pieces and work with the business to identify what's actually needed. On a guess you have 90+ columns being returned here. I suspect it's a single query that is serving multiple purposes in the code because it's unlikely that all that information is displayed on a single screen for the users.
Since these are estimated plans, it's hard to tell if your statistics are up to date or not. But the good news is, the optimizer thinks it can get at the data primarily through seeks, which suggests that you have good indexes in place... possibly.
That's the first execution plan.
The second query seemed ok except for the use of READPAST & NOLOCK hints. NOLOCK seems like the "run faster" switch for SQL Server. In fact, it leads to missing rows, extra rows and bad data. So I'd be very careful when using it. REaDPAST also leads to bad data since it simply skips rows that have a lock on them. While these may seem to make things faster, they also make them radically less accurate.
Looking at the third & fourth queries, the pattern is the same. Your problem, based on what you've provided, is in that first query. Based on what you've given us, I'd be hard pressed to give you a quick and easy fix. Based on what I can see, I think you've got a ton of work to clean that up. Sorry.
"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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply