May 20, 2008 at 12:07 pm
Sounds good, I will use the "OBJECTPROPERTY(id,'IsUserTable') = 1" filter in my query.
Thanks for the recommendations!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 22, 2008 at 10:28 am
GilaMonster (5/20/2008)
Be aware that sysindexes is a compatibility view, is deprecated and will be dropped in future versions. Also,
Books Online
In SQL Server 2005, rowmodctr is not fully compatible with earlier versions.In earlier versions of SQL Server, the database engine maintained row-level modification counters. In SQL Server 2005, such counters are maintained at the column level. Therefore, the rowmodctr column is calculated and produces results that are similar to the results in earlier versions, but are not exact.
If I switch from sysindexes to sys.indexes, I have no access to information as to how many rows in the index have been modified since the latest update. I need this information to update the stats of only those indexes that need it, as I employ the (expensive) FULLSCAN option with the Stats Update.
It looks like I need to explore using sp_updatestats with the RESAMPLE option, as suggested earlier.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 22, 2008 at 11:21 am
If I switch from sysindexes to sys.indexes, I have no access to information as to how many rows in the index have been modified since the latest update. I need this information to update the stats of only those indexes that need it, as I employ the (expensive) FULLSCAN option with the Stats Update.
It looks like I need to explore using sp_updatestats with the RESAMPLE option, as suggested earlier.
If you look at sp_updatestats you will see that it uses both sys.indexes and sys.sysindexes to identify the statistics to be updated.
However, I am not sure why you still think you need to roll your own. If you use sp_updatestats[/s] with or without the RESAMPLE option, the statistics will be updated appropriately.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 22, 2008 at 11:30 am
jeff.williams3188 (5/22/2008)
If I switch from sysindexes to sys.indexes, I have no access to information as to how many rows in the index have been modified since the latest update. I need this information to update the stats of only those indexes that need it, as I employ the (expensive) FULLSCAN option with the Stats Update.
It looks like I need to explore using sp_updatestats with the RESAMPLE option, as suggested earlier.
If you look at sp_updatestats you will see that it uses both sys.indexes and sys.sysindexes to identify the statistics to be updated.
However, I am not sure why you still think you need to roll your own. If you use sp_updatestats[/s] with or without the RESAMPLE option, the statistics will be updated appropriately.
Jeff
I have used sp_updatestats on its own before and the stats was not updated WITH FULLSCAN. That caused us some severe performance problems involving large (multi-million-row) tables.
After running "UPDATE STATISTICS ... WITH FULLSCAN" on the offending indexes the problem was cleared.
So sp_updatestats proved ineffective in that case.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 22, 2008 at 12:45 pm
That is interesting - since everything I can find says that statistics on indexes will be rebuilt with a full scan.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 22, 2008 at 1:25 pm
jeff.williams3188 (5/22/2008)
That is interesting - since everything I can find says that statistics on indexes will be rebuilt with a full scan.Jeff
I ran "dbcc show_statistics" on some of the indexes affected by sp_updatestats:
The Updated column showed last night's date (the date the sp_updatestats job was run).
However, the Rows Sampled value was much smaller than the Rows value, meaning that FULL SCAN was not employed.
You may want to test this out on your system. I should mention that this was observed in a SQL-2000 instance. I don't know if it's different in SQL 2005.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply