Lies, Damned Lies And Statistics – Part I

why not come out and say it: SQL Server 7 and 2000 does not randomly sample rows, it samples the entire set of rows in given pages (which are probably selected)

so if there is any correlation with page location, the sample is seriously deficient.

to account for the statblob/statstream storage format that allows a max of 200 steps, in certain cases, I have created separate tables for generating identities in certain ranges, so large customers get one range of ids, medium another, and small a third. the identity from the special tables are then used in the actual Customer table. MS might help by formalizing this.

Elisabeth,

We set Auto Update Statistics Asynchronously to True. Does this change anything of you analysis?

Thanks

Chris

Elisabeth,

We have Auto Update Statistics Asynchronously set to TRUE. Can you cemment on this please?

Thanks

Chris

Thanks Elisabeth.

We do not have auto create stats set so for the automatic updating it would only be on table/indexes that actually have stats. I have a few that return NULL from the stats_date function even though I am using a sysadmin account. What would make this show as NULL?

Chris

Elisabeth,

I used your query to look at our tables and indexes and saw some NULL dates. Looking in BOL it does say that the Stats_Date function may not return valid data because of permissions but I was running with an account with sysadmin authority so it cannot be that. Any ideas?

Thanks

Chris

PS. I posted this question yesterday but it has not appeared. I had the same problem with my previous post.

Elisabeth,

What would cause the stats_date function to return NULL when I am running the query as a sysadmin? We have auto update stats but do not have auto create stats set.

Thanks

Chris

Elisabeth,

The symptoms you listed match up almost exactly with what I'm seeing in my production environment.  It appears that running DBCC FREEPROCCACHE relieves the symptoms for a short period of time.  Can you confirm if running this would temporarily relieve the symptoms you listed above?

Thanks,

Thanks Elisabeth. I will try updating the ones with NULL values and see what the select shows me.

Chris

Elisabeth,

Thank you very much for the response.  I am running SQL Server 2005 Enterprise x64 Edition SP3 + CU1.  I too squirm when I think about having to run it on a scheduled basis, but right now it's the only way I can keep the system healthy.  I will try switcing to dropping the plans and seeing how it goes.  I have also opened a ticket with Microsoft to get a little more help with the issue.  We do run a pretty large number of ad-hoc queries that are not re-used.  Do you know of any options on the connection or command objects which would tell SQL Server not to bother caching a query plan?

Thanks again for the info and the links.

Jeff Banschbach

Hi Elisabeth. Your blog was very timely. I needed to update statistics for tables that had chnaged enough to upset performance but not enough to make auto stats update the statistics. I suspect the indexes are poor too but that is another story. Anyway I took some of your ideas, improved them and made them work for SQL 2000. Perhaps it would have been better to just run sp_updatestats but not as educational. The code below is "self documenting" and seems to work in general. I did get a divide by zero for one database but it worked in about 30 OK. You have to run it twice because when the clustered indexes have rowmodctr reset by update statistics it makes other indexes meet criteria.

/***************************************************************************************************

Description:   SQL Server 2000 script to identify indexes that might need a manual update stats

              and execute it. This is to avoid the situation where auto stats fails to update

              resulting in performance degradation.

              This script relies on sysindexes.rowcnt having the correct value which is not generally true

              unless you routinely run:

              dbcc updateusage('database name') with count_rows

              Inspired by:

http://sqlblog.com/blogs/elisabeth_redei/archive/2009/03/01/lies-damned-lies-and-statistics-part-i.aspx

Change History:

Ver Date         Developer        Change

1.0 19-Mar-2009  Sean Elliott     Created

***************************************************************************************************/

set nocount on

declare @nrc_mask int

declare @Row int

declare @MaxRow int

declare @sql varchar(255)

create table #all_indexes

(

  MyId         int   identity,

  OwnerName    sysname,

  TableName    sysname,

  IndexName    sysname

)

insert #all_indexes (OwnerName, TableName, IndexName)

select sus.name, sob.name, sid.name

from sysindexes sid

join sysobjects sob on sob.id = sid.id

join sysusers sus on sus.uid = sob.uid

where sid.id > 100

and   sid.indid > 0

and   sid.indid < 255

and (sid.status & 64) = 0   -- filters autostats for columns i.e. _WA_xxxxx stats - see sp_helpindex

order by sob.name, sid.name

set @MaxRow = (select count(*) from #all_indexes)

set @Row = 1

-- Run dbcc updateusage for each index seperatley to minimize blocking

while @Row <= @MaxRow

begin

  set @sql = (select 'dbcc updateusage(0, [' + OwnerName + '.' + TableName + '], [' + IndexName + ']) with count_rows' from #all_indexes where MyId = @Row)

  print @sql

  exec(@sql)

  set @Row = @Row + 1

end

drop table #all_indexes

-- see code for sp_autostats

set @nrc_mask = 16777216

create table #candidates

(

  MyId              int identity,

  OwnerName         sysname,

  TableName         sysname,

  IndexName         sysname,

  StatsDate         datetime,

  AutoUpdateStats   char(3),

  RealRowCount      bigint,

  RealRowModCtr     int,

  ChangedRowsRatio  float

)

insert #candidates

(

  OwnerName,

  TableName,

  IndexName,

  StatsDate,

  AutoUpdateStats,

  RealRowCount,

  RealRowModCtr,

  ChangedRowsRatio

)

select   sus.name as OwnerName,

        sob.name AS tblName,

        sid.name as IdxName,

        stats_date(sid.id, sid.indid) as StatsDate,

        case (sid.status & @nrc_mask)

           when @nrc_mask THEN 'OFF'

           else 'ON'

        end as AutoUpdate,

        case

           when sid.indid <= 1 then sid.rowcnt

           else (select mix.rowcnt from sysindexes mix where mix.id = sid.id and mix.indid <= 1)

        end as real_rowcnt,

        case

           when sid.indid <= 1 then sid.rowmodctr

           -- Real rowmodctr for non clustered index is sum of that for clustered plus non clustered

           else sid.rowmodctr + (select mix.rowmodctr from sysindexes mix where mix.id = sid.id and mix.indid <= 1)

        end as real_rowmodctr,

        case

           when sid.indid <= 1 then cast(sid.rowmodctr as float) / cast(sid.rowcnt as float)

           else cast((select mix.rowmodctr from sysindexes mix where mix.id = sid.id and mix.indid <= 1) + sid.rowmodctr as float) /

                cast((select mix.rowcnt from sysindexes mix where mix.id = sid.id and mix.indid <= 1) as float)

        end as ChangedRowsRatio

from sysindexes sid

join sysobjects sob on sob.id = sid.id

join sysusers sus on sus.uid = sob.uid

where  sid.id > 100

and    sid.indid > 0

and    sid.indid < 255

-- and    sid.rowcnt >= 500   -- different algorithm used when <500 rows in table but "so what"

and    sid.rowcnt > 0      -- avoid divide by zero but also filters autostats for columns i.e. _WA_xxxxx stats

-- 5% - tune as required

and    cast(sid.rowmodctr as float) / cast(sid.rowcnt as float) > 0.05

order by tblName, IdxName

select * from #candidates where AutoUpdateStats = 'OFF'

if @@rowcount > 0

begin

  print '************************************************************'

  print '***** WARNING ***** Some tables have auto stats switched off'

  print '************************************************************'

end

select * from #candidates

select @MaxRow = count(*) from #candidates

if @MaxRow > 0

begin

  set @Row = 1

  while @Row <= @MaxRow

  begin

     set @sql = (select 'update statistics [' + OwnerName + '].[' + TableName + '] [' + IndexName + ']' from #candidates where MyId = @Row)

     print @sql

     exec(@sql)

     set @Row = @Row + 1

  end

end

drop table #candidates

Lies damned lies and statistics - Part II In previous post I listed situations where your statistics

Very nice, well covered topic.

Brings out the best as to what what science & blogging is about:

Upon returning to the article, I found out that duplicate entries were being returned.  Corrected by amending the join clause:

SELECT

ssi.id

, object_name(ssi.id) AS objectName

, ssi.name as indexName  --2012-01-05 added by Daniel Adeniji

, stats_date(ssi.id,ssi.indid) as StatsDate

, ssi.indid

, ssi.rowcnt

, ssi.rowmodctr

, cast(ssi.rowmodctr as decimal)/cast(ssi.rowcnt as decimal) as ChangedRowsRatio

, ss.no_recompute AS IsAutoUpdateOff

FROM sys.sysindexes ssi

left join sys.stats ss

ON  ssi.id = ss.object_id

AND ssi.name = ss.name

WHERE ssi.id > 100

AND indid > 0

AND ssi.rowcnt > 500

AND ssi.rowcnt > 0   --2012-01-05 added by Daniel Adeniji

AND (ssi.rowmodctr/ssi.rowcnt) > 0.15 -- enter a relevant number

ORDER BY stats_date(ssi.id,ssi.indid) asc

Hi Elizabeth,

tried running your script and found that #dbccStat table was missing two columns:

, FilterExp nvarchar(200)

, UnFilteredRows int

were they recently added to the output of DBCC SHOW_STATISTICS?