Solved

update statistics for all table in all SQL server 2005 database

Posted on 2013-01-21
13
551 Views
Last Modified: 2013-03-05
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
Comment
Question by:marrowyung
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 1

Author Comment

by:marrowyung
ID: 38800437
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
 
LVL 31

Assisted Solution

by:James Murrell
James Murrell earned 50 total points
ID: 38800447
use DBNAME
go

EXEC sp_updatestats
GO
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38801488
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 400 total points
ID: 38801942
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38803117
So that you can then do something potentially terrible for performance a lot more easily :-) .
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38803874
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38805515
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 50 total points
ID: 38805880
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
 
LVL 1

Author Comment

by:marrowyung
ID: 38808485
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38808495
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
 
LVL 1

Author Comment

by:marrowyung
ID: 38809293
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
 
LVL 1

Author Comment

by:marrowyung
ID: 38952555
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38956079
I am afraid I am going to have to pass, you still have too many open questions (12)
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now