Query to Update statistics

Question

Post reply

Query to Update statistics

vamshi.sql

SSCommitted

Points: 1725
More actions
May 3, 2011 at 7:56 am

#389425

Hi All,
can anyone let me know the query to update all the statistics in a specific database.Its very urgent.please help me on this..
Thanks in advance,
vamshi.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

vamshi.sql SSCommitted Points: 1725 More actions · Answer 1

i need that query which has to be work on SQL server 2005

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

exec sp_MSforeachtable 'UPDATE STATISTICS [?] WITH FULLSCAN'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Cute way of doing it without loops or sp_MSForeachTable

You may want to change the sampling rate.

Also keep in mind that this takes ± 30 minutes to run on my 18 GB DB. And all things considered we have a very well tuned server / san. even if it seems underpowered (1 xeon CPU, 2 cores, 4 GB RAM)!

DECLARE @Exec VARCHAR(MAX)

SELECT @Exec = ''

SELECT @Exec = 'UPDATE STATISTICS dbo.[' + CONVERT(VARCHAR(200),name) + '] WITH FULLSCAN ; ' + CHAR(13) + CHAR(10) + @Exec FROM sys.tables ORDER BY name DESC

PRINT LEN(@Exec)

PRINT @Exec

EXEC(@Exec)

vamshi.sql SSCommitted Points: 1725 More actions · Answer 4

Thank you very much.It is working perfectly..

DECLARE @Exec VARCHAR(MAX)

SELECT @Exec = ''

SELECT @Exec = 'UPDATE STATISTICS dbo.[' + CONVERT(VARCHAR(200),name) + '] WITH FULLSCAN ; ' + CHAR(13) + CHAR(10) + @Exec FROM sys.tables ORDER BY name DESC

PRINT LEN(@Exec)

PRINT @Exec

EXEC(@Exec)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

As I said it's a cute way to do it. My first answer was foreachtable but Gail had come up with it... 😉

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 6

you can use SQL Server BI Development Tool with the help of SSIS package as shown in the attachements

Regards,

Syed Jahanzaib Bin Hassan

MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog

http://www.aureus-salah.com

My Blog
www.aureus-salah.com

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

I've always preffered to stay away from maint. plans. They've been too buggy, unreliable and hard if not impossible to adjust to very specific needs.