March 22, 2011 at 4:25 am
Hi,
I am using SQL-2008 standard edition. I have a query that takes a lot of time to run(25 seconds) and although it fetches just 1200 records,it makes my server busy. I am attaching the query, execution plan and the ddl design for the view from which the query reads the data.I have rebuild the indexes, updated the statistics but it doesn't help in the performance.Can someone please advice me on this.
Thanks
Chandan
March 22, 2011 at 4:30 am
March 22, 2011 at 4:43 am
Dave Ballantyne (3/22/2011)
Hi ,please see this link
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Thanks,i will take a look on this now.Btw,could you see the execution plan for this query.Last time too you had helped me by seeing execution plan where you pointed the convert predicates and explained the importance of the table structure.This time things look more complex for me.:-(
March 22, 2011 at 4:50 am
Dave,
I had a look on Gail's article but my query does not have any parameters.It has fixed select query and the execution plan does not vary as there are no parameters.
March 22, 2011 at 4:54 am
Dave,
One more thing. The DTA does not advise anything.Currently the select is being fired in a view.Do you think modifying it to hit the base table can help me somehow.
thanks
Chandan
March 22, 2011 at 5:10 am
Looking at the code, this is a query against a view. What does the view code look like?
You're getting a scan on one table, overall, the system is processing 35000 rows to return 1200. I'd focus on that Filter operation to try to move the access of data from the end of the processing to the beginning, meaning, see if you can't identify better indexes to help out the optimizer.
"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
March 22, 2011 at 5:30 am
Ok ,i thought that as there is some (X =Something or X= NULL) logic it may be being built dynamically.
The amount of rows returned is not really a relevant point , much more revelant is how much data sqlserver has had to process to return the required rows.
All things considered ,its not a *bad* plan ...
How much work is taking place in the scalar udfs ? They generally perform poorly and the cost is hidden to the plan and optimizer.
March 22, 2011 at 5:43 am
Grant Fritchey (3/22/2011)
Looking at the code, this is a query against a view. What does the view code look like?You're getting a scan on one table, overall, the system is processing 35000 rows to return 1200. I'd focus on that Filter operation to try to move the access of data from the end of the processing to the beginning, meaning, see if you can't identify better indexes to help out the optimizer.
Grant,
I have included the ddl for the view in the attachment along with execution plan. do you think chaning the order of the conditions in where clause will help in this regard?
March 22, 2011 at 6:21 am
chandan_jha18 (3/22/2011)
Grant Fritchey (3/22/2011)
Looking at the code, this is a query against a view. What does the view code look like?You're getting a scan on one table, overall, the system is processing 35000 rows to return 1200. I'd focus on that Filter operation to try to move the access of data from the end of the processing to the beginning, meaning, see if you can't identify better indexes to help out the optimizer.
Grant,
I have included the ddl for the view in the attachment along with execution plan. do you think chaning the order of the conditions in where clause will help in this regard?
In general, no, that doesn't help. With excessively complicated plans, it can because when the optimizer is timing out, it might not have explored every possiblity of reording the criteria. In your case, you're getting FULL optimization, so I don't think it will help you at all. Based on what I can see, it's the scalar functions and indexing that you should focus on. I don't see anything that jumps out as scary bad beyond those two points.
"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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply