?
Solved

update statistics for all table in all SQL server 2005 database

Posted on 2013-01-21
13
Medium Priority
?
583 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 200 total points
ID: 38800447
use DBNAME
go

EXEC sp_updatestats
GO
0
 
LVL 69

Expert Comment

by:Scott Pletcher
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1600 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:Scott Pletcher
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
 
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:Scott Pletcher
Scott Pletcher earned 200 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

771 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