auto stats not working sql 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

auto stats not working sql 2005

Author

Topic

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-12-29 : 10:51:49

The auto stats not working

I have both Auto Update Statistics and Auto Update Statistics Asynchronously set to True

Created a little test table.
USE [TEST]

GO

/****** Object: Table [dbo].[CUSTOMER] Script Date: 12/29/2007 10:42:49 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[CUSTOMER](

[Customer_Id] [nchar](10) NOT NULL,

[Customer_Name] [nvarchar](1000) NULL,

[Customer_Address] [nvarchar](1000) NULL,

[Customer_Address1] [nchar](1000) NULL,

CONSTRAINT [PK_CUSTOMER] PRIMARY KEY CLUSTERED

(

[Customer_Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

--Created an insert table

DECLARE @COUNT INT

SET @COUNT = 1

WHILE @COUNT <= 1000

begin

insert into CUSTOMER (CUSTOMER_ID, CUSTOMER_NAME)

VALUES (@COUNT, '12345678901234567890')

SET @COUNT = @COUNT + 1

END

I then look at Tables then statistics the statistics are empty so i fire update statistics and see 1000 rows in here.

I run again the insert script

DECLARE @COUNT INT

SET @COUNT = 1001

WHILE @COUNT <= 2000

begin

insert into CUSTOMER (CUSTOMER_ID, CUSTOMER_NAME)

VALUES (@COUNT, '12345678901234567890')

SET @COUNT = @COUNT + 1

END

Look again at statistics it does not have 2000 rows in here.

If i do select * from CUSTOMER where CUSTOMER_ID = '2000' then go checks statictics it works.

I was under the impression that when you do insert or delete, update then the statistics are fired.

The sys.sysindexes rowmodctr shows the 1000 rows.

I checked the conditions that sql fires if the no of rows int able > 6 and < 500 then updates when 500 mods made.

Also if row > 500 auto update done when 500 = 20% are added

So both are met.

Anyone other any other suggestions about the auto stats ?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-12-29 : 12:20:28

>>>I was under the impression that when you do insert or delete, update then the statistics are fired.

Not necessarily. SQL Server has some algorithm of updating the statistics when "it thinks" its time.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-29 : 14:41:46

And this is why you should also schedule an update stats to run. We update stats nightly.

Here's what we use (and I wrote):
http://weblogs.sqlteam.com/tarad/archive/2006/08/14/11194.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-12-29 : 16:05:34

just be careful not to update stats at the wrong time. the wrong time would be during peak hours (obviously) and when data in your tables is not representative of the data they actually contain. for example, if you do bulk data loads you don't want to schedule a stats collection when a table is empty.

-ec

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-12-31 : 08:22:43

In SQL 2005 is the usage counts maintained a better and was thinking that DB_CHECKDB it will tell you when to do DBCC UPDATEUSAGE.
so perhaps a output script to check then only need to do this if reported?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-31 : 11:04:33

DBCC CHECKDB does not tell you when to do DBCC UPDATEUSAGE. The commands have nothing in common.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-12-31 : 11:49:18

Its new in SQL 2005
In earlier versions of SQL Server, the values for the per-table and per-index row count and page counts can become incorrect. Under certain circumstances, one or more of these values might even become negative. In SQL Server 2005, these values are always maintained correctly. Therefore, databases that are created on SQL Server 2005 should never contain incorrect counts; however, databases that are upgraded to SQL Server 2005 might. This is not a corruption of any data stored in the database. DBCC CHECKDB has been enhanced to detect when any one of these counts becomes negative. When negative counts are detected, the DBCC CHECKDB output contains a warning and a recommendation to run DBCC UPDATEUSAGE to correct the issue. Although it may appear as if upgrading the database to SQL Server 2005 has caused this issue, the incorrect counts were present before the upgrade procedure.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-31 : 13:38:33

That only helps you if the counts are negative.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources