Drop Column statistics

Are you facing suddenly poor performance, try to recreate Columns statistics. SQL Server are using these statistics to decide which execution-plan would be the best.

You always have to run this script when you are upgrading your SQL server from one version to another.


DECLARE ALLNAME CURSOR FOR
Select
t.name FROM sys.stats
INNER JOIN sys.tables t
ON
sys.stats.object_id= t.object_id
WHERE
sys.stats.auto_created <> 0

DECLARE AllStats CURSOR FOR
Select
sys.stats.name FROM sys.stats
INNER JOIN sys.tables t
ON
sys.stats.object_id= t.object_id
WHERE
sys.stats.auto_created <> 0

DECLARE @StatName NVARCHAR(512)
DECLARE @Name NVARCHAR(512)

OPEN AllStats
OPEN ALLNAME

FETCH next FROM AllStats INTO @StatName
FETCH next FROM AllNAME INTO @Name

WHILE @@FETCH_STATUS= 0
BEGIN
EXEC ('drop statistics '+ @Name+'.'+ @StatName)
Print ('drop statistics '+ @Name+'.'+ @StatName)
FETCH NEXT FROM AllStats INTO @StatName
FETCH NEXT FROM AllNAME INTO @Name
END
CLOSE AllStats
CLOSE ALLNAME
DEALLOCATE ALLNAME
DEALLOCATE AllStats
GO

Dette indlæg blev udgivet i SQL Server - scripts og tagget , , . Bogmærk permalinket.