Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Reindexing requires recompile of Stored procedures Expand / Collapse
Author
Message
Posted Friday, May 23, 2008 12:42 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:48 PM
Points: 4,388, Visits: 9,506
Unless you are using 'sp_updatestats' or something similar that only updates statistics on those objects that need to be updated. Running that right after rebuilding indexes will skip your just rebuilt indexes because they don't need to be updated.

BTW - I am still trying to find out where the default sampling value is kept. When you run UPDATE STATISTICS with no qualifier (i.e. FULLSCAN, SAMPLE, etc...) - the statistics are supposed to be updated using the default sampling. I was under the impression that this would be what the statistics were built with the last time - but may not be?

From BOL - statistics on indexes are created using the FULLSCAN option. But if we update the statistics later it does not unless we specifically include the FULLSCAN option? That is really the question...

Jeff


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #506105
Posted Friday, May 23, 2008 4:03 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:19 AM
Points: 4,320, Visits: 6,113
Jeffrey Williams (5/23/2008)
Unless you are using 'sp_updatestats' or something similar that only updates statistics on those objects that need to be updated. Running that right after rebuilding indexes will skip your just rebuilt indexes because they don't need to be updated.

BTW - I am still trying to find out where the default sampling value is kept. When you run UPDATE STATISTICS with no qualifier (i.e. FULLSCAN, SAMPLE, etc...) - the statistics are supposed to be updated using the default sampling. I was under the impression that this would be what the statistics were built with the last time - but may not be?

From BOL - statistics on indexes are created using the FULLSCAN option. But if we update the statistics later it does not unless we specifically include the FULLSCAN option? That is really the question...

Jeff

Jeffrey Williams (5/23/2008)
Unless you are using 'sp_updatestats' or something similar that only updates statistics on those objects that need to be updated. Running that right after rebuilding indexes will skip your just rebuilt indexes because they don't need to be updated.

BTW - I am still trying to find out where the default sampling value is kept. When you run UPDATE STATISTICS with no qualifier (i.e. FULLSCAN, SAMPLE, etc...) - the statistics are supposed to be updated using the default sampling. I was under the impression that this would be what the statistics were built with the last time - but may not be?

From BOL - statistics on indexes are created using the FULLSCAN option. But if we update the statistics later it does not unless we specifically include the FULLSCAN option? That is really the question...

Jeff


1) Are you sure that sp_updatestats only updates stats that need it? a) if autoupdate is on this would be pointless since they would already be done presumably and b) I looked at the code for sp_updatestats and it seems to do everything. There was use of stats_ver_current(@table_id, @ind_id), but I can't find code for that anywhere. Perhaps that is what you were referring to?

NOTE: per BOL here ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/b86a88ba-4f7c-4e19-9fbd-2f8bcd3be14a.htm, "If you prefer not to update all statistics by running sp_updatestats". That implies that sp_updatestats does all stats.

and yet here: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/919158f2-38d0-4f68-82ab-e1633bd0d308.htm, " This updates statistics only when they are required. " Not very clear!! :)

2) I am sure there is an algorythm somewhere that details out the logic on what percentage of rows/data is sampled for update stats call, but I can't find it at the moment.



Oh, while checking around for some clarification for this topic I stumbled across this: http://blogs.msdn.com/queryoptteam/archive/2006/07/21/674350.aspx. Quite amazing that you can 'falsify' the stats stuff and thus force the optimizer into a particular type of plan!! Gosh can I think of some uses for that.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #506164
Posted Friday, May 23, 2008 6:14 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:48 PM
Points: 4,388, Visits: 9,506

1) Are you sure that sp_updatestats only updates stats that need it? a) if autoupdate is on this would be pointless since they would already be done presumably and b) I looked at the code for sp_updatestats and it seems to do everything. There was use of stats_ver_current(@table_id, @ind_id), but I can't find code for that anywhere. Perhaps that is what you were referring to?

NOTE: per BOL here ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/b86a88ba-4f7c-4e19-9fbd-2f8bcd3be14a.htm, "If you prefer not to update all statistics by running sp_updatestats". That implies that sp_updatestats does all stats.

and yet here: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/919158f2-38d0-4f68-82ab-e1633bd0d308.htm, " This updates statistics only when they are required. " Not very clear!! :)

2) I am sure there is an algorythm somewhere that details out the logic on what percentage of rows/data is sampled for update stats call, but I can't find it at the moment.



Oh, while checking around for some clarification for this topic I stumbled across this: http://blogs.msdn.com/queryoptteam/archive/2006/07/21/674350.aspx. Quite amazing that you can 'falsify' the stats stuff and thus force the optimizer into a particular type of plan!! Gosh can I think of some uses for that.


Yep, I am with you - it is a bit confusing. When I reviewed 'sp_updatestats' I found that it checks whether or not there have been rows modified (rowmodctr). If so, it updates the statistics for that table, if not - it gets skipped. I tested this and it does indeed skip tables that have not had any activity.

Now - from everything I have been able to find, the only way to insure that statistics are rebuilt using a full scan is to issue an UPDATE STATISTICS WITH FULLSCAN (or rebuild the index) and turn off auto update stats. If auto update stats kicks in - it rebuilds the statistics using the default sampling rate (which I still don't know what that value is).

But, if you have not hit an auto update stats situation for a table - and you run sp_updatestats with the RESAMPLE option or UPDATE STATISTICS WITH RESAMPLE, then those statistics will be rebuilt with the previous sampling rate. That means that statistics built when an index was created/rebuilt will be updated with a full scan - and any statistics that were created manually with a full sampling rate would also be built with a full scan.

Basically - it does not look like you can insure that stats are updated with a full scan all the time.

Jeff


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #506178
Posted Friday, October 23, 2009 1:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 18, 2012 1:30 AM
Points: 2, Visits: 3
Did you figure what caused the 'CA' error? I see that this thread changes track a bit and the original 'CA' error issue is not discussed further. I have the same error reported for our product. When my process is run in parallel with index rebuild online, then I get the 'CA' error.

I am eager to know if you had overcome the problem.If so, please let us know how.It will be of immense help. Thanks again
Post #808152
Posted Friday, October 23, 2009 2:37 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:46 AM
Points: 67, Visits: 301
My memory is a little foggy and we have upgraded to Sql 2008. But from what I remember I had to break the MP up into individual Databases and that solved our issue. Originally I had set the MP to do all our User databases. Everything worked OK and then I had a database that was removed from production so I went back into the MP and removed the database from there. But every time the MP ran it continued to look for the deleted DB. I decided it was easier to do a MP for each individual DB and from that point on I no longer saw the errors I had encountered before. Sorry I can't be of more help but as I said we upgraded our servers and I have not had any problems since I changed the Maintenance Plan...


Post #808165
Posted Monday, November 2, 2009 4:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 18, 2012 1:30 AM
Points: 2, Visits: 3
We raised the 'CA' error with MS and they seem to have acknowledged that this is a bug on SQL Server. Very strange that no one has raised it before.
Post #812650
Posted Friday, August 16, 2013 9:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 12:11 PM
Points: 1, Visits: 10
Thank you for what NOT to do. But, what is the best practice then please?
Post #1485277
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse