April 18, 2012 at 2:52 pm
I have been struggling with this for some time and have not been adding an index to a table. Curious if anyone thinks I should add an index that could be 4GB in size based on information I can provide. This is for 1 stored procedure to be faster, but when it's slow it can slow other operations down greatly as it pulls data into cache or writes to TempDB. Query must function in manner it does.
Situation:
Query - used to take 25 minutes to run, then tuned to 11 minutes average run time by putting maxdop to 1 and breaking into table variable for results and then joining to another table (had tested with many other scenarios but this was the least costly and quickest, with best execution plan to date).
Beast of a query on volumes that need to have storage added if an index is added.
We are due for space upgrade and not on SAN but DAS and need more rack space (side point but important because index space would be costly on this table for storage).
sp_spaceused on table is:
rowsreserveddataindex_sizeunused
53508975 12676096 KB7596784 KB5071960 KB7352 KB
Multiple joins, must sift through millions of records to compare old values to new values.
Execution plan has a 90% cost on an index spool (eager spool).
The only reason to add the index would be for this one query, and likely slow everything else down considering the number of updates and insert to the table (would need a good padding on the index as well as a massive rebuild/reorg plan). The query is run a few to ten times per day an causes extensive IO_completion and IO_Latch wait as t pulls from disk and writes to disk on a tempDB that is in a bad place. The Index Spool pulls out all 53508975 records in the table.
Any ideas on taming a beast with the info I provided or any questions to get more data for beast taming?
April 18, 2012 at 3:29 pm
An index spool is a temporary index built in TempDB because the optimiser really needed an index on that column but there wasn't one.
Without seeing the exec plan, it's hard to say for sure, but the presence of an index spool is a strong indication that an index is needed. At least test one out.
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
April 18, 2012 at 4:14 pm
When looking at the execution plan, clicking on the index spool, and viewing properties - is it the output list that should be used to construct the index, or is the seek predicate range expression or range columns? I had been looking for where to pull the index from for this type of operation.
April 18, 2012 at 5:10 pm
Could you post the exec plan please (the .sqlplan file)? I don't feel comfortable advising you on details without seeing the plan
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
April 19, 2012 at 8:44 am
I wouldn't mind doing so but I don't have a host for the file at the moment and trying to paste a 249KB, 5005 line XML output of the plan into here was causing a bit of crashing. Crashed IE when I tried to format for readability (then just formatted in VS2005 using Edit>Advanced>Format Document) but pasting in here with a preview first crashed Chrome. Any recommendations would bypass a testing environment and likely not take place until after we re-rack our DAS/NAS with more space, and would be appreciated.
April 19, 2012 at 8:59 am
No, no, no, please don't paste XML. That would be horrid to try and deal with here. I'm personally not keen on reading exec plans raw.
Save the plan as a .sqlplan file, zip it if it's large and attach the file to the forum post. No hosts necessary.
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
April 19, 2012 at 9:20 am
Should be in Zip.
April 19, 2012 at 12:00 pm
Sorry to be picky, but could you post the actual plan please, not the estimated?
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
April 19, 2012 at 12:43 pm
Oddly (to me) that was the plan from cache, from production, after running:
SELECT query_plan
FROM sys.dm_exec_cached_plans moo
OUTER APPLY sys.dm_exec_query_plan(moo.plan_handle) AS bilbo
JOIN sys.objects obj ON obj.object_id = bilbo.objectid
WHERE obj.name = 'gRevAdjCharges';
- then took that result and formatted and put into .sqlplan file. Sorry about that, thought the cached plan would not be an estimated plan and give good production info. I was trying to avoid the overhead if I could just scrub the cached plan quickly.
Actual plan attached.
Side note because this to me is great and can't believe I overlooked it (Thank you so VERY much).
I looked through and did see estimates only and not actual info. This does explain something else for me though, if cached plans are just estimated, of why I have not been getting what I wanted out of the cached plan information. Never thought to look if it was an estimated plan.
Awesome to finally have this other thing figured out! Already helped me greatly there, just now. I'd been looking to scrub parameter values from cached plans that were being saved as estimated plans and not scrubbing the actual execution plan.
Been looking for this little tid-bit o' good:
<ParameterList>
<ColumnReference Column="@inRRCodeID" ParameterRuntimeValue="(28)" />
<ColumnReference Column="@inCustAcctRecStaffID" ParameterCompiledValue="(0)" ParameterRuntimeValue="(0)" />
<ColumnReference Column="@dtEndDate" ParameterCompiledValue="'2012-05-19 12:35:08.000'" ParameterRuntimeValue="'2012-05-19 12:35:08.000'" />
<ColumnReference Column="@dtStartDate" ParameterCompiledValue="'2010-04-20 12:35:08.000'" ParameterRuntimeValue="'2010-04-20 12:35:08.000'" />
<ColumnReference Column="@inStepTypeRevAdj" ParameterRuntimeValue="(875)" />
<ColumnReference Column="@inCustInvTITID" ParameterRuntimeValue="(18)" />
<ColumnReference Column="@inHawbTITID" ParameterRuntimeValue="(10)" />
</ParameterList>
April 19, 2012 at 12:48 pm
Oops, plan attached.
April 19, 2012 at 12:52 pm
matt.newman (4/19/2012)
Oddly (to me) that was the plan from cache, from production, after running:
A plan from cache is a plan without run-time information, ie an estimated plan. Plans in cache can't have run-time info within them, if the plan was executed 20 times, which execution's run-time info would be included?
Awesome to finally have this other thing figured out! Already helped me greatly there, just now. I'd been looking to scrub parameter values from cached plans that were being saved as estimated plans and not scrubbing the actual execution plan.
The only parameter value you'll find in the plan cache is the parameter that the plan was initially compiled with. Any other is run-time info and hence not stored within the cached plan. Actual parameter values, like actual executions and actual row counts are only found in the actual plan, and that's not cached anywhere.
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
April 19, 2012 at 12:55 pm
You can retrieve the actual plan for your session, check out this article for next time:
Viewing the Execution Plan of a Running Query in SQL Server
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 19, 2012 at 12:58 pm
Ok, first thing. Lose the nolocks. http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
Can you post the entire query? There's a fragment in the plan, but it's just a fragment and I suspect a part of the problem is a filter that's very high up the tree, filters 150 000 rows down to 18.
Edit: Actually, is there any chance you could post the entire procedure?
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
April 19, 2012 at 1:05 pm
opc.three (4/19/2012)
You can retrieve the actual plan for your session, check out this article for next time:
That gets an estimated plan, not an actual plan. The only way to get an actual execution plan is to run SET STATISTICS XML ON before running the query (which is what management studio does) or to use profiler and get the showplan xml event, but that has to be running before the query starts.
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
April 19, 2012 at 1:13 pm
GilaMonster (4/19/2012)
opc.three (4/19/2012)
You can retrieve the actual plan for your session, check out this article for next time:That gets an estimated plan, not an actual plan. The only way to get an actual execution plan is to run SET STATISTICS XML ON before running the query (which is what management studio does) or to use profiler and get the showplan xml event, but that has to be running before the query starts.
Hmmm...so no way to get the actual plan without running the query to completion? Typing this out I suppose that would make sense otherwise how would the "actual number of rows" be populated...if so, bummer...thanks
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply