November 17, 2012 at 12:28 pm
A new development:
As previously stated, the "trans" and "asset" tables are subscribed tables
I re-initialized the subscription this morning
Now the query appears to be back to normal run time
(no changes in table definition [indexes, etc.] compared to before the purge/etc)
My understanding is that a re-initialization does the following:
- Drops and re-creates the table on the subscriber side
- Bulk inserts into the subscribed table
- Re-creates indexes, constraints, etc.
So what is the difference between doing all of the above, versus just running a maintenance plan to rebuild all the table's indexes?
I am suspending the weekly index rebuild/update stats for now -- I don’t want the table to get fragmented again, but I also don’t want this performance degradation to resurface. Also, a re-initialization of this subscription would be too time-consuming and disruptive to do on a regular basis.
Thoughts?
November 17, 2012 at 12:55 pm
jgenovese (11/17/2012)
"One other thing would really help, the actual execution plan."This IS the ACTUAL execution plan
"Also, do you really drop the temporary table as soon as it is populated as it appears in the code you posted?"
NO -- I extracted the query from the stored proc in which it resides
As for this being an actual execution plan, no it isn't. All that is shown in the plan are estimates. If this were the actual execution plan there would be actual counts shown in addition to the the estimates.
November 17, 2012 at 1:02 pm
jgenovese (11/17/2012)
A new development:As previously stated, the "trans" and "asset" tables are subscribed tables
I re-initialized the subscription this morning
Now the query appears to be back to normal run time
(no changes in table definition [indexes, etc.] compared to before the purge/etc)
My understanding is that a re-initialization does the following:
- Drops and re-creates the table on the subscriber side
- Bulk inserts into the subscribed table
- Re-creates indexes, constraints, etc.
So what is the difference between doing all of the above, versus just running a maintenance plan to rebuild all the table's indexes?
I am suspending the weekly index rebuild/update stats for now -- I don’t want the table to get fragmented again, but I also don’t want this performance degradation to resurface. Also, a re-initialization of this subscription would be too time-consuming and disruptive to do on a regular basis.
Thoughts?
Takes me back to what I originally stated. After rebuilding the indexes you rebuilt the the statistics on those indexes using a sample size intead of a full scan. This means that by rebuilding the statistics on those indexes, you made them less accurate than there were immediately following the index rebuild, which essentially rebuilds the statistics for the index using a full scan.
November 17, 2012 at 1:16 pm
the stats which were rebuilt were COLUMN statistics
I thought table index rebuild only updated TABLE statistics (fullscan)
Are you trying to tell me that this is not the case, i.e. table index rebuild updates both table AND column stats at 100%?
November 17, 2012 at 1:19 pm
jgenovese (11/17/2012)
the stats which were rebuilt were COLUMN statisticsI thought table index rebuild only updated TABLE statistics (fullscan)
Are you trying to tell me that this is not the case, i.e. table index rebuild updates both table AND column stats at 100%?
An index rebuild rebuilds the statistics on the columns for the index just as a statistics rebuild with a full scan.
November 17, 2012 at 1:23 pm
Another FYI. I haven't used maintenance plans in years to maintain my indexes or satistics. I developed my own processes using T-SQL to manage these in a more intelligent manner. Maintenance plans are a shot gun method that isn't always the best.
There are also several routines out there on the web that you can get to do the same thing if you are interested. Unfortunately I don't have the links to them. Perhaps others reading this do have the links and can provide them for you.
November 17, 2012 at 1:27 pm
okay, that's not what I've heard elsewhere (re: column stats) but so be it
try this plan -- I generated it via "Include Actual Execution Plan" (vs sys.dm_exec_text_query_plan, from which I got the other plans)
November 17, 2012 at 2:16 pm
stepping out, will be back online Monday, thank you for your help today
my apologies for the runaround on the query plan -- I thought sys.dm_exec_text_query_plan yielded an actual, apparently that's not the case
November 17, 2012 at 3:13 pm
jgenovese (11/17/2012)
my apologies for the runaround on the query plan -- I thought sys.dm_exec_text_query_plan yielded an actual, apparently that's not the case
Definition of an 'actual plan' is a plan with run-time information in it (actual row counts, actual executions). Plans pulled from cache have no run-time information in them (why would they), and hence are 'estimated plans', plans without run-time information, only estimated, compile-time information (estimated row count, estimated executions).
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
November 20, 2012 at 11:34 am
Any luck with the plan?
On a related note, I am trying to pass as a query hint the XML from what I believe to be the plan that the query was using before purge
I can see the plan just fine as a sqplan, but when I try to retrive the XML, the "StatementText" gets truncated. If I try to paste into the hint the missing part of the statement from the original query, I get this at run time" "XML parsing: line 104, character 17, well formed check: no '<' in attribute value"
I suspect there is some limit to the length of "StatementText", on both the retrieval and the use in a query option -- is there a way to circumvent that limit? Or, can the complete XML live apart from the query option (for ex. a text file) and still be referenced by the query option?
November 20, 2012 at 1:26 pm
Does this run from a stored proc with parameters? Is parameter sniffing a problem here? Perhaps add an OPTION(OPTIMIZE FOR if params are used and see what shakes out?
November 20, 2012 at 1:44 pm
All:
This was literally a "switch-flip" -- the query run time doubled exactly right after I made the changes noted in my original post
(note: on that day I rebuilt indexes but NOT stats -- update stats came 2 weeks later and made no difference either way)
No other operations took place on either database that day
We need to track down what changed such that the query run time doubled, before we start making SQL changes
Did I do something wrong that day?
November 20, 2012 at 2:46 pm
jgenovese (11/20/2012)
All:This was literally a "switch-flip" -- the query run time doubled exactly right after I made the changes noted in my original post
(note: on that day I rebuilt indexes but NOT stats -- update stats came 2 weeks later and made no difference either way)
No other operations took place on either database that day
We need to track down what changed such that the query run time doubled, before we start making SQL changes
Did I do something wrong that day?
Have dug into it. One thing I noticed was the orders of magnatude difference between actual counts and estimated count (much lower) which indicates possible problem with statistics since the paln may not be the best for the number of rows.
Not messing with the query though since you haven't provided any DDL for the tables, sample data, expected results. Also not sure if we have all the code involved in the query.
November 20, 2012 at 3:15 pm
thats all the code for the misbehaving query, moot point anyway as no code is changing until we figure out what went wrong that day
November 20, 2012 at 6:17 pm
jgenovese (11/20/2012)
thats all the code for the misbehaving query, moot point anyway as no code is changing until we figure out what went wrong that day
Rechecked what you posted, sorry, for some reason I thought there were some UDF's in the main code. The only other piece of code not provided is the code for ParseIdListToTable.
Still, we need the DDL for the tables, including index definitions, some sample data for each of the tables that is representative of the problem domain (SAMPLE data, not real data), and expected results based on the sample data.
Sorry, but I'm not spending my time deciphering the information from the sp_helps.txt file. As one of many volunteers on this site, I have better things to do with my time considering it would be easier for you to just generate what is required.
Viewing 15 posts - 16 through 30 (of 56 total)
You must be logged in to reply to this topic. Login to reply