Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 594
  • 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
 
marrowyungSenior Technical architecture (Data)Author 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
 
marrowyungSenior Technical architecture (Data)Author 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
 
marrowyungSenior Technical architecture (Data)Author 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
 
marrowyungSenior Technical architecture (Data)Author 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
 
marrowyungSenior Technical architecture (Data)Author Commented:
0
 
Anthony PerkinsCommented:
I am afraid I am going to have to pass, you still have too many open questions (12)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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