Dropping and recreating all statistics

I am having deadlock problems and I have noticed that on one table dropping
the statistics and recreating them helped. Is there a script or an easy way
to do this
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
sp_updatestats is what you're looking for.
On some servers I regularly rebuild all indexes (which also updates the
stats) and then issue DBCC FREEPROCCACHE to completely refresh the system.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||use UPDATE STATISTICS 'tablename', or drop statistics
tablename.statisticsname Followed by a create statistics
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ghunter via droptable.com" <u4529@.uwe> wrote in message
news:672d4e349b451@.uwe...
>I am having deadlock problems and I have noticed that on one table dropping
> the statistics and recreating them helped. Is there a script or an easy
> way
> to do this
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200610/1
>
|||I have tried that and then ran exec sp_helpstats and it showed no statistics
where on the table. What I want to do is delete all statistics on all tables
and them create them on all indexes and colums. I have 10,000 tables so I am
looking for a script.
Paul Ibison wrote:
>sp_updatestats is what you're looking for.
>On some servers I regularly rebuild all indexes (which also updates the
>stats) and then issue DBCC FREEPROCCACHE to completely refresh the system.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
|||could you point me to a script that automagically does all that for me?
Hilary Cotter wrote:[vbcol=seagreen]
>use UPDATE STATISTICS 'tablename', or drop statistics
>tablename.statisticsname Followed by a create statistics
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
|||sp_updatestats.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||sp_updatestats will run UPDATE STATISTICS on all tables. It won't create
stats on tables.
The Indexes should all have stats which will get updated using DBCC
DBREINDEX.
If you want to check that the stats have been updated, run DBCC
SHOW_STATISTICS:
eg: DBCC SHOW_STATISTICS ('District','pk_district')
The first column is called 'Updated' and has the date and time of the
update.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Maybe I am not making my question clear enough. Does sp_updatestats delete
and recreate the statistics. And what does sp_updatestats create statistics
on.
I was not aware that update stats would create statistics on something but
that it would only update them. and on top of that it will only update them
if they are out of date.
Paul Ibison wrote:
>sp_updatestats.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
|||sp_updatestats runs UPDATE STATISTICS on all user tables in the current
database - as far as I know this means that the statblob column is updated
in sysindexes. It doesn't create statistics on columns where there weren't
any previously existing. For indexes there'll already be statistics, and
there'll also be stats for those columns which have been explicitly created
(CREATE STATISTICS). If the key values for indexes have changed a lot or if
there is a significant change in the amount of data in the table then
updating the statistics is useful, and also recompiling the associated
stored procedures whose plans will be inaccurate.
HTH,
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
sql