December 17, 2003 at 2:10 pm
Can I safely delete rows from sysindexes where the names start with _WA and hind_? I believe these are all auto generated statistics that will simply be created again. I realize that there will be a performance degredation until they are rebuilt. I am hoping to eliminate this as a possible reason for me getting errors when I run DBCC CHECKTABLE and DBCC CHECKDB.
This post is related to others I have posted today, but I feel it is enough of a change of subject to have it separate from the other posts.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
December 17, 2003 at 2:17 pm
Well, I guess you know what Microsoft says about direct statements against system tables.
To make really sure you can also use
INDEXPROPERTY (...,'IsStatistics')
INDEXPROPERTY (...,'IsAutoStatistics')
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 17, 2003 at 2:21 pm
That I have heard and usually only change system table directly when I can't find another way to do what I need to do.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
December 17, 2003 at 2:22 pm
Now there is a bold suggestion. Would you consider deleting records manually for a system table as a "good practice" or one of those "bad practices".
My normal response is never manually do anything to a system table. Find a Microsoft supported method to manipulate the system tables.
I seem to remember there was a way to delete those column statistics, but wouldn't you know it I can't remember right now.
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http:www.sqlservercentral.com/bestof/purchase.asp
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 17, 2003 at 2:25 pm
You need use DROP STATISTICS to drop statistics on the table and DBCC SHOW_STATISTICS to display the current distribution statistics for the specified target on the specified table.
December 17, 2003 at 2:32 pm
Thanks, I am looking at DBCC SHOW_STATISTICS now, unfortunately I don't think I can get it to show me the statistics for all statistics at once.
I will never claim changing system tables is a good practice and will say that it shouldn't be done until you are confident you know what the end result will be which is why I started this thread, I am not confident enough that simply deleting them directly from that system table will not cause unwanted problems. I now have another option in this case. You should also be prepared to face the consequences if your change causes worse problems which can include making tables and/or databases unusable and unrecoverable or worse (I know because I have experimented on many occassions in a dev environment to see what would happen).
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
December 17, 2003 at 2:35 pm
Backup and backup before making critial changes or changes you do not feel comfortable.
Edited by - allen_cui on 12/17/2003 2:35:56 PM
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply