SQL Server Performance

"nonclustered, statistics, auto create located"

Discussion in 'Performance Tuning for DBAs' started by nupe02, Mar 12, 2004.

  1. nupe02 New Member

    I have a table with over 200 "_WA_Sys_..." (nonclustered, statistics, auto create located) indexes/statistics the system had created from turning on "Auto Create Statistics". I would very much like to remove them from my table because I believe its doing more harn than good, I can't see why almost each table having over a hundred of these System created stats on them. Is there a clean way to remove them in stead of just deleting them from sysindexes where name like "_WA_....).
  2. Twan New Member

    drop statistics is the command you want

  3. satya Moderator

    Dropping Statistic: [dbo].[Customers].[_WA_sys_test]<br /> <br />If Exists (Select * from sysobjects where id = object_id('dbo._trace_spDeleteAllStats')<br /> and sysstat & 0xf = 4)<br /> Drop Proc dbo._trace_spDeleteAllStats<br />Go<br /><br />CREATE PROC dbo._trace_spDeleteAllStats<br /><br />/********************************************************************<br />* Copyright 2001, Intrinsic Design, Inc. All rights reserved.<br />* Filename: _trace_spDeleteAllStats.sql<br />* Object Name: _trace_spDeleteAllStats<br />********************************************************************<br />* Description: Returns statistical information for all tables<br />*<br />********************************************************************<br />* Parameter Name Description<br />* --------------------- ------------------------<br />*<br />********************************************************************/<br />AS<br />BEGIN<br />SET NOCOUNT ON<br /><br /> Declare @nvcTblNm sysname ,<br /> @siUID smallint ,<br /> @nvcUserNm nvarchar(256) ,<br /> @iObjectID int ,<br /> @nvcIndexNm sysname ,<br /> @nvcSQL nvarchar(204<img src='/community/emoticons/emotion-11.gif' alt='8)' />,<br /> @nvcFullName nvarchar(800)<br /><br /> Declare curTables Cursor For<br /> Select uid ,<br /> id,<br /> name<br /> From sysobjects<br /> Where type in ('U','V')<br /> and name not like '_trace_%'<br /> Order By name<br /> FOR READ ONLY<br /><br /> Open curTables<br /><br /> Fetch Next From curTables<br /> into @siUID, @iObjectID, @nvcTblNm<br /><br /> While @@FETCH_STATUS = 0<br /> Begin<br /> Declare curStats cursor For<br /> Select name<br /> From sysindexes<br /> Where id = @iObjectID<br /> and indid &gt; 0<br /> and indid &lt; 255<br /> and (status & (64 | 838860<img src='/community/emoticons/emotion-11.gif' alt='8)' />) &gt; 0<br /> and name like '_WA_Sys_%'<br /> Order By indid<br /> FOR READ ONLY<br /><br /> Open curStats<br /><br /> Fetch Next From curStats<br /> into @nvcIndexNm<br /><br /> While @@fetch_status &gt;= 0<br /> Begin<br /><br /> Select @nvcFullName = '[' + user_name(@siUID) + '].[' + @nvcTblNm + '].[' + @nvcIndexNm + ']'<br /> Print 'Dropping Statistic: ' + @nvcFullName<br /><br /> Select @nvcSQL = 'Drop Statistics ' + @nvcFullName<br /><br /> Exec sp_executesql @nvcSQL<br /><br /> Fetch Next From curStats<br /> into @nvcIndexNm<br /> End<br /> Deallocate curStats<br /><br /> Fetch Next From curTables<br /> into @siUID, @iObjectID, @nvcTblNm<br /> End<br /><br /> Close curTables<br /> Deallocate curTables<br /><br /> Return 0<br /><br />END<br /><br /> <br /><br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of knowledge sharing.</font id="size1"></font id="teal"></center>

Share This Page