sp_updatestats sql2005

Microsoft SQL Server

When I run this against some of my databases I see

wa_

type indexes not updated.


Are these from people doing ad-hoc queries where no index is used?


How do you clean this up and detect where indexes could be applied. There
are a lot of tables where users just connect with Access and run queries.
Hi
-- Here is some very handy query to find out age of index / statistics.

--Just re-write the following query by mentioning desired Table_Name :

SELECT

'Index Name' = ind.name,

'Statistics Date' = STATS_DATE(ind.object_id, ind.index_id)

FROM

SYS.INDEXES ind

WHERE

OBJECT_NAME(ind.object_id) = 'Table_Name'

---WHERE OBJECTPROPERTY(object_id,'IsMSShipped')=0







DECLARE @tablename varchar(80)

DECLARE @SQL AS NVARCHAR(200)

DECLARE TblName_cursor CURSOR FOR

SELECT name FROM sys.tables

OPEN TblName_cursor

FETCH NEXT FROM TblName_cursor

INTO @tablename

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQL = 'UPDATE STATISTICS dbo.[' + @TableName + '] WITH FULLSCAN ' ---+
CONVERT(varchar(3), @sample) + ' PERCENT'

EXEC sp_executesql @statement = @SQL

FETCH NEXT FROM TblName_cursor

INTO @tablename

END

CLOSE TblName_cursor

DEALLOCATE TblName_cursor