May 22, 2011 at 3:44 pm
I have a query which is taking more than 30 mins to execute. I am attaching the execution plan along with the actual query. The table where it shows 70% cost is huge about 200gb +. The column (edl ntext)) used in the case statement has a lot of data. Can someone suggest an index or may be help me in writing this query in a better way? I am using sql 2005 std edition with SP3+ CU 10.
May 22, 2011 at 3:55 pm
Please provide table def and index def for the tables involved.
At a first glance it looks like there's a missing index to support the t.rid = c.rid join (among others).
What scares me: did you really implement indexes as per the DTA advice on multi million row tables? (at least that's what some index names indicate)
May 22, 2011 at 4:47 pm
LutzM (5/22/2011)
Please provide table def and index def for the tables involved.At a first glance it looks like there's a missing index to support the t.rid = c.rid join (among others).
What scares me: did you really implement indexes as per the DTA advice on multi million row tables? (at least that's what some index names indicate)
I have attached the indexes on the table(Cov) and also the schema. In the indexes script the one's commented out are currently disabled.Btw, i have a very similar database with same schema but different data and it is doing index seek using the same index, so i am guessing it is because of the data.
May 22, 2011 at 5:26 pm
I think the biggest issue is the clustered index scan of the cov table.
It might help to add a computed persisted column to get rid of the CASE expression on the edl column. This would help to reduce the amount of data to be used in that query.
I probably would try an index on rid, fpc, id include(new_computed_column).
May 22, 2011 at 6:27 pm
LutzM (5/22/2011)
I think the biggest issue is the clustered index scan of the cov table.It might help to add a computed persisted column to get rid of the CASE expression on the edl column. This would help to reduce the amount of data to be used in that query.
I probably would try an index on rid, fpc, id include(new_computed_column).
Thanks. I am not sure what do u mean by "computer persisted column". Do you have an example pleasE?
May 22, 2011 at 11:49 pm
http://www.google.com/search?q=computed+persisted+column
In most cases a computed column can be indexed without needing to persist it.
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
May 23, 2011 at 10:17 am
GilaMonster (5/22/2011)
http://www.google.com/search?q=computed+persisted+columnIn most cases a computed column can be indexed without needing to persist it.
The reason for suggesting the column as being persisted was mainly driven by the (assumed) improved performance compared to always checking for the ntext column being NULL.
On a second thought I guess it's not required since SQL Server most probably is rather fast in dertermine whether a column is NULL or not, regardless of this column being of an LOB data type...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply