Select only stand alone statistics to drop
-
I have a script that will drop all statistics and indexes. Our database has proprietary indexes that I will not be able to replace. However, I need to drop all stand alone statistics. Is there a way to filter out the ones I need?
-
how can an index be proprietary? any index created on a table will show up using sp_helpindex <tablename>, right? any index created can be scripted out, and the same goes for statistics.
on any table you can run sp_helpstats <tablename> and get the list of all statistics which referenced that table id. the statistics which start with _WA_Sys_ are the ones SQL Server created automatically as part of it's execution plans. statistics that do not start with _WA_Sys_ are probably the statistics you are referencing.
anyway, this script "Drop table indexes, constraints and statistics":
http://www.sqlservercentral.com/scripts/contributions/213.asp
could probalby be adapted to what you want, or simply use the search to search scripts for indexes or statistics and see what other users have contributed.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
What I mean is that they have manually built indexes that make our database faster, so I can't drop them. However, they said they have no stand alone statistics, of which I need to drop. I was hoping for a field that said "Statistic" or "Index". That way I could drop and rebuild stats with a script that runs regularly and just update the indexes.
What started all this was 2 stats that noone knows who built that got in the way on our updates. I am thinking they may also be causing some other problems we are experiencing. Either way a clean house is an efficient house.
-
The script below uses a cursor nested within a cursor to get the job done.
in this case it drops ALL statistics, leaving indexes alone.
SET NOCOUNT ON
--ONE OF THOUSE HORRIBLE CURSOR WITHIN A CURSOR THINGS:
DECLARE @TABLENAME VARCHAR (30),
@STATNAME VARCHAR(1024),
@SQLSTRING VARCHAR(2000),
@OUTERFETCH INT
BEGIN
DECLARE TCURSOR CURSOR FOR
SELECT NAME FROM SYSOBJECTS WHERE XTYPE='U'; --ALL USER TABLES
CREATE TABLE #TEMPSTATS(STATISTICS_NAME VARCHAR(1024),STATISTICS_KEYS VARCHAR(1024))
OPEN TCURSOR
FETCH NEXT FROM TCURSOR INTO @TABLENAME
SET @OUTERFETCH=@@FETCH_STATUS
WHILE @OUTERFETCH = 0
BEGIN
PRINT 'CHECKING TABLE :' + @TABLENAME
TRUNCATE TABLE #TEMPSTATS
INSERT INTO #TEMPSTATS
EXEC SP_HELPSTATS @OBJNAME=@TABLENAME
DECLARE STATISTICSCURSOR CURSOR FOR SELECT DISTINCT STATISTICS_NAME FROM #TEMPSTATS
OPEN STATISTICSCURSOR
FETCH NEXT FROM STATISTICSCURSOR INTO @STATNAME
WHILE @@FETCH_STATUS = 0
BEGIN
--ALL STATISTICS; DO YOU WANT TO DIFFERENTIATE AGAINST _WA_Sys_ STATISTICS?
SET @SQLSTRING = 'DROP STATISTICS ' + @TABLENAME + '.' + @STATNAME + ''
PRINT @SQLSTRING
EXEC (@SQLSTRING)
FETCH NEXT FROM STATISTICSCURSOR INTO @STATNAME
END
CLOSE STATISTICSCURSOR
DEALLOCATE STATISTICSCURSOR
FETCH NEXT FROM TCURSOR INTO @TABLENAME
SET @OUTERFETCH=@@FETCH_STATUS
END
CLOSE TCURSOR
DEALLOCATE TCURSOR
DROP TABLE #TEMPSTATS
END
i wrapped this into an sproc, stuck it in master, and it works for all databases as well... simply add
create procedure sp__dropstatistics as
to the top of the code
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thank you Lowell.
Now I just have to load it on my test machine, reverse engineer it to figure out how you did it.
-
How's this for reverse engineering :
Select Object_name(id) as TableName, 'DROP STATISTICS [' + Object_name(id) +'].[' + name + ']' from dbo.SysIndexes where indid > 0 and indid 0 ORDER BY Object_name(id)
--64 | 8388608 is for user and system stats
paste the 2nd column in QA and execute (drops ALL STATISQUES)
-
Remi's knowing the nuances of allowable indid and status values lets him use that to find the drop statements; you'd still have to wrap that into a cursor to get it to actually drop the statistics for you.
Remi did you have a source for knowing that AND-ing the status value returned a statistinc and not an index? if you had a source could you share? little nuances like that make better coding,
thanks
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
This is still personal preference :
1 - Cursor = £@£½¤¢¤[¼¢8(*?%(*?/$ £¢¤£¢¤
however I agree that they have their utility once in a while... especially hwen doing admin stuff like this.
2 - Automated procs like these make me shiver because I'm not the only person who has dbo access on the server and I've seen those other guy run procs without knowing what they really do and I frankly don't expect them to understand what an advanced stored proc like that does on the first read. Also I've been known to run the wrong proc on more than 1 occasion, so I prefer to run these types of procs manually so that I can control what affects performance on the server (either from running the proc or the side effects of the proc).
3 - this line of code is all I needed to get me started :
EXEC SP_HELPSTATS
Open EM, expand master, stored procs and find SP_HELPSTATS and read the code. It's well commented and easy to understand. From that and what I already knew from SysIndexes it was pretty easy to build the command list query (I have worked a lot on systables a few months back... I remembered that stats are indexes that have a status that skyrockets well into the millions and that their name always starts with "_WA_Sys_". However I have never created user stats which seem that have a much lower status number).
-
Forgot to mention this :
indid >>
1 = Clustered index
>1 = non clustered
255 = used if the table as binary or text field(s)
0 = no clustered index
[EDITED]
0 = HEAP
-
Lowell's script produced these errors
Cannot DROP the index 'hind_697105574_1A_5A_6A_56A_75A_8A_14A_24A_53A_68A_74A' because it is not a statistics collection.
DROP STATISTICS Serial.hind_697105574_56A
Server: Msg 3739, Level 11, State 1, Line 1
however Remi's didn't. I am releading the database to test again. It seems you are using the same drop statement just differnet select statements, correct? It's these "hind_" lines which are giving me the most trouble.
-
This doesn't seem to be a system stat, maybe it's a user stat that the system is using... or some index type that I don't know.
Are you sure that my version dropped it? Or did it not select it in the list?
[Edited]
Now that I think of it it's maybe just a user stat that has been dropped by a developper while the proc was running or something like that... would make more sens.
-
i found out that hind_ items are hypothetical indexes that are used by the index tuning wizard, they are supposed to be auto-removed when the tuning is done; see:
http://support.microsoft.com/kb/293177
The hypothetical indexes created by the Index Tuning Wizard start with a name of "hind_%" and should not exist after the tuning has finished; they should all be removed.
I think wrapping Remi's code in a cursor is a better idea, but the bandaid for what i wrote is to wrap the inner cursors workload in an if statement:
IF LEFT(@STATNAME,5) <> 'HIND_'
BEGIN
--ALL STATISTICS; DO YOU WANT TO DIFFERENTIATE AGAINST _WA_Sys_ STATISTICS?
SET @SQLSTRING = 'DROP STATISTICS ' + @TABLENAME + '.' + @STATNAME + ''
PRINT @SQLSTRING
EXEC (@SQLSTRING)
END
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Actually it would be better to use my query because I query SysIndexes only once and would use only 1 cursor. While your current code queries SysIndexes for every table in you db... I don't know how often you indend to run this... but I expect that this version would take a lot more time (5/10/?? fold).
-
I found the problem!!!!
Article Id number 293177.
The people who wrote our database program ran a lot of Index Tuning Wizards. These temporary stats never left and are creating update loops on my server. Guess how many...over 50! It's those stats with the hind_% name.
I want to thank you two. I have learned much today.
-
I see you found it first
However, neither script dropped it.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply