Showing posts with label statistics. Show all posts
Showing posts with label statistics. Show all posts

Tuesday, March 27, 2012

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

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 SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1sp_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 SQLMonster.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 SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/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 SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1|||could you point me to a script that automagically does all that for me?
Hilary Cotter wrote:
>use UPDATE STATISTICS 'tablename', or drop statistics
>tablename.statisticsname Followed by a create 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 SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/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 SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/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 .

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/Forum...server/200610/1sp_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/Forum...server/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/Forum...server/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/Forum...server/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/Forum...server/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 .

dropping all statistics

Hi,
I'm wondering if there is a command I can use to drop all system & user
created statistics on a table? And then I can loop through the database and
get rid of all the statistics for the entire database. Thus, if it's not a
command but some SQL code then I can use that too.
I'm running some upgrade scripts for my application and it often fails on
dropping or altering tables based on statistics it doesn't know about.
Thanks,
matt
Try below. Untested, just wrote it. Change the PRINT to EXEC(@.sql) to actually execute the DROP
statements:
DECLARE @.tblname sysname, @.statname sysname, @.sql nvarchar(2000)
DECLARE c CURSOR FOR
SELECT object_name(id), name FROM sysindexes WHERE INDEXPROPERTY(id, name, 'IsStatistics') = 1
OPEN c
FETCH NEXT FROM c INTO @.tblname, @.statname
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = 'DROP STATISTICS [' + @.tblname + '].[' + @.statname + ']'
PRINT @.sql
FETCH NEXT FROM c INTO @.tblname, @.statname
END
CLOSE c
DEALLOCATE c
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"matt" <matt@.discussions.microsoft.com> wrote in message
news:6784F0CC-E641-45C8-A3B6-355C537E1FEC@.microsoft.com...
> Hi,
> I'm wondering if there is a command I can use to drop all system & user
> created statistics on a table? And then I can loop through the database and
> get rid of all the statistics for the entire database. Thus, if it's not a
> command but some SQL code then I can use that too.
> I'm running some upgrade scripts for my application and it often fails on
> dropping or altering tables based on statistics it doesn't know about.
> Thanks,
> matt
|||Thanks! That works perfectly. All I needed was the index property to check
for stats, but I'll take the script. All worked well.
Matt
"Tibor Karaszi" wrote:

> Try below. Untested, just wrote it. Change the PRINT to EXEC(@.sql) to actually execute the DROP
> statements:
> DECLARE @.tblname sysname, @.statname sysname, @.sql nvarchar(2000)
> DECLARE c CURSOR FOR
> SELECT object_name(id), name FROM sysindexes WHERE INDEXPROPERTY(id, name, 'IsStatistics') = 1
> OPEN c
> FETCH NEXT FROM c INTO @.tblname, @.statname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.sql = 'DROP STATISTICS [' + @.tblname + '].[' + @.statname + ']'
> PRINT @.sql
> FETCH NEXT FROM c INTO @.tblname, @.statname
> END
> CLOSE c
> DEALLOCATE c
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "matt" <matt@.discussions.microsoft.com> wrote in message
> news:6784F0CC-E641-45C8-A3B6-355C537E1FEC@.microsoft.com...
>
>

dropping all statistics

Hi,
I'm wondering if there is a command I can use to drop all system & user
created statistics on a table? And then I can loop through the database and
get rid of all the statistics for the entire database. Thus, if it's not a
command but some SQL code then I can use that too.
I'm running some upgrade scripts for my application and it often fails on
dropping or altering tables based on statistics it doesn't know about.
Thanks,
mattTry below. Untested, just wrote it. Change the PRINT to EXEC(@.sql) to actual
ly execute the DROP
statements:
DECLARE @.tblname sysname, @.statname sysname, @.sql nvarchar(2000)
DECLARE c CURSOR FOR
SELECT object_name(id), name FROM sysindexes WHERE INDEXPROPERTY(id, name, '
IsStatistics') = 1
OPEN c
FETCH NEXT FROM c INTO @.tblname, @.statname
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = 'DROP STATISTICS [' + @.tblname + '].[' + @.statname + ']'
PRINT @.sql
FETCH NEXT FROM c INTO @.tblname, @.statname
END
CLOSE c
DEALLOCATE c
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"matt" <matt@.discussions.microsoft.com> wrote in message
news:6784F0CC-E641-45C8-A3B6-355C537E1FEC@.microsoft.com...
> Hi,
> I'm wondering if there is a command I can use to drop all system & user
> created statistics on a table? And then I can loop through the database a
nd
> get rid of all the statistics for the entire database. Thus, if it's not
a
> command but some SQL code then I can use that too.
> I'm running some upgrade scripts for my application and it often fails on
> dropping or altering tables based on statistics it doesn't know about.
> Thanks,
> matt|||Thanks! That works perfectly. All I needed was the index property to check
for stats, but I'll take the script. All worked well.
Matt
"Tibor Karaszi" wrote:

> Try below. Untested, just wrote it. Change the PRINT to EXEC(@.sql) to actu
ally execute the DROP
> statements:
> DECLARE @.tblname sysname, @.statname sysname, @.sql nvarchar(2000)
> DECLARE c CURSOR FOR
> SELECT object_name(id), name FROM sysindexes WHERE INDEXPROPERTY(id, name,
'IsStatistics') = 1
> OPEN c
> FETCH NEXT FROM c INTO @.tblname, @.statname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.sql = 'DROP STATISTICS [' + @.tblname + '].[' + @.statname + ']
'
> PRINT @.sql
> FETCH NEXT FROM c INTO @.tblname, @.statname
> END
> CLOSE c
> DEALLOCATE c
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "matt" <matt@.discussions.microsoft.com> wrote in message
> news:6784F0CC-E641-45C8-A3B6-355C537E1FEC@.microsoft.com...
>
>

dropping all statistics

Hi,
I'm wondering if there is a command I can use to drop all system & user
created statistics on a table? And then I can loop through the database and
get rid of all the statistics for the entire database. Thus, if it's not a
command but some SQL code then I can use that too.
I'm running some upgrade scripts for my application and it often fails on
dropping or altering tables based on statistics it doesn't know about.
Thanks,
mattTry below. Untested, just wrote it. Change the PRINT to EXEC(@.sql) to actually execute the DROP
statements:
DECLARE @.tblname sysname, @.statname sysname, @.sql nvarchar(2000)
DECLARE c CURSOR FOR
SELECT object_name(id), name FROM sysindexes WHERE INDEXPROPERTY(id, name, 'IsStatistics') = 1
OPEN c
FETCH NEXT FROM c INTO @.tblname, @.statname
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = 'DROP STATISTICS [' + @.tblname + '].[' + @.statname + ']'
PRINT @.sql
FETCH NEXT FROM c INTO @.tblname, @.statname
END
CLOSE c
DEALLOCATE c
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"matt" <matt@.discussions.microsoft.com> wrote in message
news:6784F0CC-E641-45C8-A3B6-355C537E1FEC@.microsoft.com...
> Hi,
> I'm wondering if there is a command I can use to drop all system & user
> created statistics on a table? And then I can loop through the database and
> get rid of all the statistics for the entire database. Thus, if it's not a
> command but some SQL code then I can use that too.
> I'm running some upgrade scripts for my application and it often fails on
> dropping or altering tables based on statistics it doesn't know about.
> Thanks,
> matt|||Thanks! That works perfectly. All I needed was the index property to check
for stats, but I'll take the script. All worked well.
Matt
"Tibor Karaszi" wrote:
> Try below. Untested, just wrote it. Change the PRINT to EXEC(@.sql) to actually execute the DROP
> statements:
> DECLARE @.tblname sysname, @.statname sysname, @.sql nvarchar(2000)
> DECLARE c CURSOR FOR
> SELECT object_name(id), name FROM sysindexes WHERE INDEXPROPERTY(id, name, 'IsStatistics') = 1
> OPEN c
> FETCH NEXT FROM c INTO @.tblname, @.statname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.sql = 'DROP STATISTICS [' + @.tblname + '].[' + @.statname + ']'
> PRINT @.sql
> FETCH NEXT FROM c INTO @.tblname, @.statname
> END
> CLOSE c
> DEALLOCATE c
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "matt" <matt@.discussions.microsoft.com> wrote in message
> news:6784F0CC-E641-45C8-A3B6-355C537E1FEC@.microsoft.com...
> > Hi,
> >
> > I'm wondering if there is a command I can use to drop all system & user
> > created statistics on a table? And then I can loop through the database and
> > get rid of all the statistics for the entire database. Thus, if it's not a
> > command but some SQL code then I can use that too.
> >
> > I'm running some upgrade scripts for my application and it often fails on
> > dropping or altering tables based on statistics it doesn't know about.
> >
> > Thanks,
> > matt
>
>