• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 589
  • Last Modified:

update statistics for all table in all SQL server 2005 database

Dear all,

Any script for me to update statistics for all table in all database?

I want to do it all in one file.

I am using SQL server 2005.

DBA100.
0
marrowyung
Asked:
marrowyung
  • 5
  • 4
  • 3
  • +1
3 Solutions
 
marrowyungAuthor Commented:
what is the command to check the latest statistic information of all table of all database ?

Is the following script working fine in your environment without any problem (like delete/modify any existing data)

-- Update Statistics without NTEXT and Image Data Type:

SET NOCOUNT ON
DECLARE @columnname VARCHAR(MAX)
DECLARE @tablename SYSNAME
DECLARE @statsname SYSNAME
DECLARE @sql NVARCHAR(4000)
DECLARE @NAME VARCHAR(MAX)
declare @i INT
declare @j INT
create table #temp
(
tablename varchar(1000),
statsname varchar(1000),
columnname varchar(1000)
)
insert #temp(tablename,statsname,columnname)
SELECT DISTINCT
OBJECT_NAME(s.[object_id]),
s.name AS StatName,
COALESCE(@NAME+ ', ', '')+c.name
FROM sys.stats s JOIN sys.stats_columns sc ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id
JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
JOIN INFORMATION_SCHEMA.COLUMNS D ON D.[COLUMN_NAME]= C.[NAME]
JOIN sys.partitions par ON par.[object_id] = s.[object_id]
JOIN sys.objects obj ON par.[object_id] = obj.[object_id]
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND D.DATA_TYPE NOT IN('NTEXT','IMAGE')
create table #temp1
(
id int identity(1,1),
tablename varchar(8000),
statsname varchar(8000),
columnname varchar(8000)
)
insert #temp1(tablename,statsname,columnname)
select tablename,statsname,stuff(
(
    select ','+ [columnname] from #temp where
statsname = t.statsname for XML path('')
),1,1,'')
from (select distinct tablename,statsname from #temp )t
SELECT @i=1
SELECT @j=MAX(ID) FROM #temp1
WHILE(@I<=@J)
BEGIN
SELECT @statsname = statsname from #temp1 where id = @i
SELECT @tablename = tablename from #temp1 where id = @i
SELECT @columnname = columnname from #temp1 where id = @i
SET @sql = N'UPDATE STATISTICS '+QUOTENAME(@tablename)+QUOTENAME(@statsname)+space(1)+'WITH FULLSCAN'
--PRINT @sql
EXEC sp_executesql @sql
SET @sql = N'UPDATE STATISTICS '+QUOTENAME(@tablename)+QUOTENAME(@statsname)+space(1)+'WITH FULLSCAN'
--PRINT @sql
EXEC sp_executesql @sql

SET @i = @i+1
END
DROP TABLE #temp
DROP TABLE #temp1

-- Update Statistics with NTEXT and Image Data Type:

SET NOCOUNT ON
DECLARE @columnname VARCHAR(MAX)
DECLARE @tablename SYSNAME
DECLARE @statsname SYSNAME
DECLARE @sql NVARCHAR(4000)
DECLARE @NAME VARCHAR(MAX)
declare @i INT
declare @j INT
create table #temp
(
tablename varchar(1000),
statsname varchar(1000),
columnname varchar(1000)
)
insert #temp(tablename,statsname,columnname)
SELECT DISTINCT
OBJECT_NAME(s.[object_id]),
s.name AS StatName,
COALESCE(@NAME+ ', ', '')+c.name
FROM sys.stats s JOIN sys.stats_columns sc ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id
JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
JOIN INFORMATION_SCHEMA.COLUMNS D ON D.[COLUMN_NAME]= C.[NAME]
JOIN sys.partitions par ON par.[object_id] = s.[object_id]
JOIN sys.objects obj ON par.[object_id] = obj.[object_id]
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND D.DATA_TYPE  IN('NTEXT','IMAGE')
create table #temp1
(
id int identity(1,1),
tablename varchar(8000),
statsname varchar(8000),
columnname varchar(8000)
)
insert #temp1(tablename,statsname,columnname)
select tablename,statsname,stuff(
(
    select ','+ [columnname] from #temp where
statsname = t.statsname for XML path('')
),1,1,'')
from (select distinct tablename,statsname from #temp )t
SELECT @i=1
SELECT @j=MAX(ID) FROM #temp1
WHILE(@I<=@J)
BEGIN
SELECT @statsname = statsname from #temp1 where id = @i
SELECT @tablename = tablename from #temp1 where id = @i
SELECT @columnname = columnname from #temp1 where id = @i
SET @sql = N'UPDATE STATISTICS '+QUOTENAME(@tablename)+QUOTENAME(@statsname)
--PRINT @sql
EXEC sp_executesql @sql
SET @i = @i+1
END
DROP TABLE #temp
DROP TABLE #temp1


they just work DB by DB, I have to repeat a lot of time for all DB, so do it using one script is much better.
0
 
James MurrellProduct SpecialistCommented:
use DBNAME
go

EXEC sp_updatestats
GO
0
 
Scott PletcherSenior DBACommented:
While easy enough to write such a script, it's an awful idea to blindly rebuild all stats, in that it could hurt performance a lot.  So naturally you really shouldn't do that.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Anthony PerkinsCommented:
And again, consider downloading and installing Ola Hallengren script:
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Your script then becomes as simple as:
C. Update statistics on all user databases

EXECUTE dbo.IndexOptimize
@Databases = 'ALL_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL'


Or:
D. Update modified statistics on all user databases

EXECUTE dbo.IndexOptimize
@Databases = 'ALL_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'
0
 
Scott PletcherSenior DBACommented:
So that you can then do something potentially terrible for performance a lot more easily :-) .
0
 
marrowyungAuthor Commented:
cs97jjm3,

by "use DBNAME
go

EXEC sp_updatestats
GO "

I have to repeat that for all user DBs and this make me repeat a lot of time when no choice.


ScottPletcher,

cs97jjm3's command is not going to hurt the performance ?

acperkins,

you seems keep using that tools from that link for a long time, you so happy with the perfomance.
0
 
Anthony PerkinsCommented:
cs97jjm3's command is not going to hurt the performance ?
I believe Scott's point is that you do not want to update all the stats on all the databases, only the ones that need it.  Otherwise it will take a very long time and it could potentially hurt performance if runs into an active time of the day.
0
 
Scott PletcherSenior DBACommented:
And updatestats uses sampling (only some small % of the rows are actually read).  When you rebuild an index you get a fullscan: every value is read.  So you could end up replacing 100% accurate statistics that were just updated with a faulty 20% (or whatever subset) that happens to be inaccurate.

Statistics are vital, but you need to understand what they are before you update them: that sounds reasonable, right?

If somebody offered to "glerknick" your house, wouldn't you want to know what they were actually doing BEFORE you went ahead and did it?
0
 
marrowyungAuthor Commented:
Dear all,

any way to know the current statistics information ? the script here: http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html already automatically found out which one is out of date ?

any script to show that out for all database ?
0
 
Anthony PerkinsCommented:
any script to show that out for all database ?
And again, if you look at Ola Hallengren's  script you can set it to update all modified stats in all databases, see my comment here http:#a38801942
0
 
marrowyungAuthor Commented:
From the script get from http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

What is xp_ss_delete command for ?

how about
1)sp_delete_backuphistory?
2) why sometimes update tmpFileGroups and sometimes update @tmpFileGroups, what is the different ?

Why in the script, we have " -- Delete old backup files" section" ?
0
 
marrowyungAuthor Commented:
0
 
Anthony PerkinsCommented:
I am afraid I am going to have to pass, you still have too many open questions (12)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now