[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How can I delete statistics on a specific table.

Posted on 2007-07-31
12
Medium Priority
?
1,907 Views
Last Modified: 2008-05-29
We have a web based application.  This application has a table which tracks the person records that the currently logged on user has access to.  Every 10 minutes a scrub action is performed on this table to clear the inactive sessions.  This table has insert, update and delete actions being constantly performed on it.

The majority of our installs run on Oracle.  In Oracle, it was determined that performance drastically improves when we delete the statistics on this table so that the Cost Based Optimizer does not attempt to use the out-dated statistics to choose the execution plan.  Basically, statistics become outdated after such a short period of time that it is detrimental to performance.

SQL server seems to handle statistics drastically different from what I have seen.  I'm not sure if I understand this 100% quite yet, but I believe that statistics are performed on a per index basis.

How do I delete stats on all indexes for a specific table?

Is there any reason that having stats on this particular table would have a positive effect on performance given the scenario above?

If one of our smaller SQL server installs were to use the Auto Update Statistics option, is there a way we can prevent stats from executing on this table?
0
Comment
Question by:dlalonde1
  • 6
  • 5
12 Comments
 
LVL 10

Expert Comment

by:aesmike
ID: 19603483
analyze table <your table> delete statistics
0
 
LVL 6

Expert Comment

by:gjutras
ID: 19603515
exec sp_dboption dbname,'auto update statistics',off

will turn off the autostats feature for a table
0
 
LVL 6

Expert Comment

by:gjutras
ID: 19603527
sorry thats for the whole db
you want
UPDATE STATISTICS tablename with NORECOMPUTE
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 6

Expert Comment

by:gjutras
ID: 19603533
and to turn it back on if you want:
exec sp_autostats <table_name>, <stats_flag>, <index_name>
where stats_flag is on or off
0
 
LVL 6

Expert Comment

by:gjutras
ID: 19603536
check out http://support.microsoft.com/kb/195565 for more info
0
 

Author Comment

by:dlalonde1
ID: 19604204
analyze table <your table> delete statistics

resulted in this:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'STATISTICS'.
0
 
LVL 6

Expert Comment

by:gjutras
ID: 19608476
analyze table <your table> delete stats

should do
0
 
LVL 6

Expert Comment

by:gjutras
ID: 19608525
never mind, don't use the analyze command, stick to the 2 forms I wrote earlier.
0
 

Author Comment

by:dlalonde1
ID: 19609394
analyze does not work for me!  I finally got the statistics to drop using the following command:

drop statistics TABLENAME._WA_Sys_COLUMNID_31F82575

You can see all the table names and the index names by executing this:

SELECT
   (LEFT(o.name,20)) 'Table',
   (LEFT(i.name,40)) 'Index Name',
   STATS_DATE(i.id, i.indid) 'Statistics Date',
   ISDATE(STATS_DATE(i.id, i.indid))
FROM sysobjects o left outer join sysindexes i
 on o.id = i.id
WHERE i.name like'_WA_Sys%' and o.name='TABLENAME'

Can anyone answer my main question: How do I delete stats on all indexes for a specific table?  All that has to be done is to use some transact-sql to loop through these tables executing a delete statement on each of them, but I'm not well versed in transact-sql so I haven't quite figured that out yet.  

My second question is more of an opinionated topic, but it would be nice to hear if anybody has any opinions on this.

Correct me if I am wrong, but I believe that the NORECOMPUTE flag only matters if you have "Auto Update Statistics" checked.  If you are manually executing statistics, I think it will override this setting and compute statistics.  I think this answers my third question.
0
 

Author Comment

by:dlalonde1
ID: 19611536
Here's an example of what I'm trying to do, only I'm a Transact-SQL noob and I don't know what I'm doing.  I get an error stating "Server: Msg 170, Level 15, State 1, Procedure drop_stats_tablename, Line 12  Line 12: Incorrect syntax near '@SQL'."

CREATE PROC drop_stats_tablename (@dbName SYSNAME) AS
 -- SET NOCOUNT ON
 DECLARE @SQL VARCHAR(300), @idxname SYSNAME
 SET @SQL = 'SELECT i.name '
 SET @SQL = @SQL + 'FROM ' + @dbName + '.dbo.sysindexes i '
 SET @SQL = @SQL + 'INNER JOIN ' + @dbName + '.dbo.sysobjects o '
 SET @SQL = @SQL + 'ON i.id = o.id '
 SET @SQL = @SQL + 'WHERE i.name like ''_WA_Sys%'' '
 SET @SQL = @SQL + 'and o.name = ''TABLENAME'''
 DECLARE RS CURSOR FOR @SQL
 OPEN RS
 FETCH NEXT FROM RS INTO @idxname
 WHILE (@@FETCH_STATUS = 0)
  BEGIN
   SET @SQL = 'DELETE STATISTICS TABLENAME.' + @idxname
   EXEC (@SQL)
   FETCH NEXT FROM RS INTO @idxname
  END
 CLOSE RS
 DEALLOCATE RS
GO
0
 

Accepted Solution

by:
dlalonde1 earned 0 total points
ID: 19612608
I was able to fix the code so that I can now scrub all the statistical collections for a particular table.  Now, it may not be the most efficient code, but it does the job.

Usage: drop_stats_tablename <dbname>
 
CREATE PROC drop_stats_tablename (@dbName SYSNAME) AS
 SET NOCOUNT ON
 DECLARE @SQL VARCHAR(300), @idxname SYSNAME
 SET @SQL = 'INSERT INTO #index_temp '
 SET @SQL = @SQL + 'SELECT i.name '
 SET @SQL = @SQL + 'FROM ' + @dbName + '.dbo.sysindexes i '
 SET @SQL = @SQL + 'INNER JOIN ' + @dbName + '.dbo.sysobjects o '
 SET @SQL = @SQL + 'ON i.id = o.id '
 SET @SQL = @SQL + 'WHERE i.name like ''_WA_Sys%'' '
 SET @SQL = @SQL + 'and o.name = ''tablename'''
 IF OBJECT_ID('#index_temp') IS NULL drop table #index_temp
 CREATE TABLE #index_temp (idxname varchar(100))
 EXEC (@SQL)
 DECLARE RS CURSOR FOR SELECT idxname FROM #index_temp
 OPEN RS
 FETCH NEXT FROM RS INTO @idxname
 WHILE (@@FETCH_STATUS = 0)
  BEGIN
   SET @SQL = 'DROP STATISTICS tablename.' + @idxname
   EXEC (@SQL)
   FETCH NEXT FROM RS INTO @idxname
  END
 CLOSE RS
 DEALLOCATE RS
 drop table #index_temp
GO
0
 

Author Comment

by:dlalonde1
ID: 19612667
I still have 2 unanswered components of my original post:

Is there any reason that having stats on this particular table would have a positive effect on performance given the scenario above?

If one of our smaller SQL server installs were to use the Auto Update Statistics option, is there a way we can prevent stats from executing on this table?

(Note gjutras suggestion on NORECOMPUTE option for updating statistics, but even with this flag set, I still have statistical collections on my table.  I think this option just prevents automatic updating of outdated statistics)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question