Post reply

Automatic Statistics not being created

  • February 17, 2005 at 9:11 am

    #109695

    Thought I'd toss in a problem I'm having. Last resort before I open a call with Microsoft.

    We've got a productive database with a few tables. One table contains 200'000+ rows (STAMM_TEXT) and another table containing 281'000+ rows (DOC_FILES). The STAMM_TEXT table keeps losing its internal statistics somehow, whereas the DOC_FILES table isn't even creating automatic statistics.

    Here the table definition of the DOC_FILES table.

     CREATE TABLE [DOC_FILES] (
  [LFDNR] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [MANDT] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [BEZUG] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
  [FILENAME] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
  [FILEEXT] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [FILEDATA] [image] NULL 
&nbsp ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 GO
  CREATE  INDEX [DOC_FILESPOSID] ON [DOC_FILES]([FILENAME]) WITH  FILLFACTOR = 90 ON [PRIMARY]
 GO
  CREATE  INDEX [DOC_FILESMANDT] ON [DOC_FILES]([MANDT]) WITH  FILLFACTOR = 90 ON [PRIMARY]
 GO
  CREATE  UNIQUE  INDEX [DOC_FILESLFDNR] ON [DOC_FILES]([LFDNR]) WITH  FILLFACTOR = 90 ON [PRIMARY]
 GO

    ...and the definition for the STAMM_TEXT table:

     CREATE TABLE [STAMM_TEXT] (
  [LFDNR] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
  [MANDT] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [POSID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [LFDDOK] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [SPRACHE] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [TBSART] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [BENENNUNG] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [DELET] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [USERID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [DATST] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [TXTAUS] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [TXTSTAT] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [TXTIDX] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
&nbsp ON [PRIMARY]
 GO
  CREATE  INDEX [STAMM_TEXTBELEGID] ON [STAMM_TEXT]([LFDDOK]) WITH  FILLFACTOR = 90 ON [PRIMARY]
 GO
  CREATE  INDEX [STAMM_TEXTUSERID] ON [STAMM_TEXT]([USERID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
 GO
  CREATE  INDEX [STAMM_TEXTTBSART] ON [STAMM_TEXT]([TBSART]) WITH  FILLFACTOR = 90 ON [PRIMARY]
 GO
  CREATE  INDEX [STAMM_TEXTSPRACHE] ON [STAMM_TEXT]([SPRACHE]) WITH  FILLFACTOR = 90 ON [PRIMARY]
 GO
  CREATE  INDEX [STAMM_TEXTMANDT] ON [STAMM_TEXT]([MANDT]) WITH  FILLFACTOR = 90 ON [PRIMARY]
 GO
  CREATE  INDEX [STAMM_TEXTPOSID] ON [STAMM_TEXT]([POSID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
 GO

    I ran the DBCC SHOW_STATISTICS command on all the indices in this talbe to gain some statistical information and it turned out that queries on the STAMM_TEXT table will probably all result in table scans being performed. 🙂 I obtained this information by dividing the 'Rows Sampled' through the 'All Density' results returned by the DBCC command. This gives you an average of what will happen with a select statement containing a where clause which triggers the index to be used. (In one index the number of results returned would have been for example between 1 and 11963.05894316 rows).

    I then started capturing the statements with the profiler to determine what was actually going on in the background. It turned out that most of the statements were running against one index. The application started slowing down after one week and was crawiling after 8-10 days. I manually updated the statistics for this index using SQL Query Analyzer and sampling 100% of the data in this table. This was a partial solution.

    I'm now running an T-SQL Statement as an SQL Job which updates the statistics for all the indices in this table on a weekly basis.

    My question: Why are the statistics losing their effect on the queries?

    There are no massive inserts occuring ( I know this, because I'm logging the rowcount of this table every 4 hours into a separate table) and no massive deletes (shouldn't affect the statistics anyway). The autostatistics are turned on and everything should be running just dandy.

    I even ran a profiler trace for some time (30 minutes while a document generator was accessing the table) and thought the index tuning wizard would pop up with some new fancy index, but nothing. Nada! Nothing to optimize.

    The DOC_FILES table is the absolute opposite issue. Here SQL Server hasn't even created statistics for all the indices. The SQL statements running against this table are performing index scans though.

    Thanks for chewing on this.

    I gathered some of the tips and tricks for performance tuning from Ken England's book 'Microsoft SQL Server 2000 - Performance Optimization and Tuning Handbook'. other information was gathered from Microsoft's Site.

    Greets from Switzerland


    __________________________________
    Searching the KB articles is like
    picking your nose. You never know
    what you'll find.

  • February 18, 2005 at 2:35 am

    #543028

    Check Autostats db-setting !

    sql7 and sql2k pre sp3 exec sp_dboption N'yourdb', N'auto create statistics', N'true'  --

    sql2k sp3 : ALTER DATABASE yourdb SET AUTO_CREATE_STATISTICS ON

    Also check sp_autostats [ @tblname = ] 'table_name'

        [ , [ @flagc = ] 'stats_flag' ]

        [ , [ @indname = ] 'index_name' ]

     in books online.

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • February 18, 2005 at 2:54 am

    #543034

    Sorry, I forgot to write that autostatistics were already turned on.

    Here the results for the sp_dboption on the database itself:

    The following options are set:     

    -----------------------------------

    torn page detection

    db chaining

    ANSI null default

    auto create statistics

    auto update statistics

    sp_autostats 'STAMM_TEXT' returns:

    Index Name                   AUTOSTATS Last Updated
[PK_STAMM_TEXT]              ON        2005-02-18 03:19:38.973
[STAMM_TEXTBELEGID]          ON        2005-02-18 03:19:39.863
[STAMM_TEXTUSERID]           ON        2005-02-18 03:19:41.147
[STAMM_TEXTTBSART]           ON        2005-02-18 03:19:42.270
[STAMM_TEXTSPRACHE]          ON        2005-02-18 03:19:43.537
[STAMM_TEXTMANDT]            ON        2005-02-18 03:19:44.083
[STAMM_TEXTPOSID]            ON        2005-02-18 03:19:45.160
[_WA_Sys_BENENNUNG_3D5E1FD2] ON        2005-02-18 03:19:45.380
[_WA_Sys_TXTAUS_3D5E1FD2]    ON        2005-02-18 03:19:45.600
[_WA_Sys_DATST_3D5E1FD2]     ON        2005-02-18 03:19:45.880
[_WA_Sys_TXTSTAT_3D5E1FD2]   ON        2005-02-18 03:19:46.113
[_WA_Sys_TXTIDX_3D5E1FD2]    ON        2005-02-18 03:19:46.333
[__TEST_JOHN]                ON        2005-02-18 03:19:46.787

    sp_autostats 'DOC_FILES' returns:

    Index Name                   AUTOSTATS Last Updated

[PK_DOC_FILES]               ON        2005-02-18 03:09:04.303
[DOC_FILESPOSID]             ON        2005-02-18 03:09:05.083
[DOC_FILESMANDT]             ON        2005-02-18 03:09:05.943
[DOC_FILESLFDNR]             ON        2005-02-18 03:09:07.257
[_WA_Sys_FILEEXT_7B905C75]   ON        2005-02-18 03:09:07.473

    Back to square one. Thanks nevertheless for pointing out the sprocs.


    __________________________________
    Searching the KB articles is like
    picking your nose. You never know
    what you'll find.

  • February 18, 2005 at 3:14 am

    #543036

    Here is a nice article regarding SQL Server UPDATE STATISTICS Tips :

    http://www.sql-server-performance.com/statistics.asp

    Maybe it helps in solving this problem.

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • February 18, 2005 at 5:02 am

    #543050

    Have look at the system statistical indexes, they may be a clue to aditional indexes being needed.

    What is the fillfactor and pad index %'s.

     

  • July 28, 2005 at 12:46 am

    #577778

    @alzdba: Thanks for the tip. I had a look around.

    @athugar: Where would I find the system statistical indexes?

    I'm still having the same problem. Strange thing is, if I run the scheduled job on a Sunday morning to udpate all the statistics, then the users start complaining by Monday afternoon, that the performance on the database is waning. I can then run the same scheduled job that ran on Sunday and everything is just perfect. On Friday everybody starts complaining again.

    I'd like to point out that the application in question is 'per se' not the fastest ever developed, but the database side is all I can change/modify/optimise at the moment.

    Sometimes it just makes me want to ...

    I'll get there eventually.


    __________________________________
    Searching the KB articles is like
    picking your nose. You never know
    what you'll find.

  • July 28, 2005 at 12:50 am

    #577780

    Can you post the job (create ddl) you are runing to "correct" the stats ?

    btw : e.g. [_WA_Sys_BENENNUNG_3D5E1FD2] is an autogenerated system statistic.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

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

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