Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Unanswered: Where, oh where, have my little statistics gone?

    Hi all,

    As part of my automagical nightly index maintenance application, I am seeing a fairly regular (3-4 failures out of 5 attempts per week) failure on one particular table in my database. The particular line which seems to be failing is this one:

    DBCC SHOWCONTIG (WON_Staging_EPSEst) WITH FAST, TABLERESULTS, ALL_INDEXES

    The log reports the following transgression(s):
    Code:
    Msg 2767, Sev 16: Could not locate statistics 'WON_Staging_EpsEst' in the system catalogs. [SQLSTATE 42000]
    Msg 0, Sev 16:  [SQLSTATE 01000]
    Msg 0, Sev 16: -------------------- Simple ReIndex for [WON_Staging_EpsEst].[IX_WON_Staging_EpsEst] [SQLSTATE 01000]
    Msg 2528, Sev 16: DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]
    Msg 0, Sev 16:  [SQLSTATE 01000]
    Msg 0, Sev 16: -------------------- Post-Maintenance Statistics Report for WON_Staging_EpsEst [SQLSTATE 01000]
    Msg 0, Sev 16: Statistics for WON_Staging_EpsEst, WON_Staging_EpsEst [SQLSTATE 01000]
    Msg 2528, Sev 16: DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]
    Msg 0, Sev 16: Statistics for WON_Staging_EpsEst, IX_WON_Staging_EpsEst [SQLSTATE 01000]
    Msg 2768, Sev 16: Statistics for INDEX 'IX_WON_Staging_EpsEst'. [SQLSTATE 01000]
    Updated              Rows                 Rows Sampled         Steps  Density                  Average key length      
    -------------------- -------------------- -------------------- ------ ------------------------ ------------------------
    Aug  3 2007  3:22AM  674609               674609               196    2.0958368E-4             8.0
    
    (1 rows(s) affected)
    This table is dropped and recreated each day during a data import job. After the table is recreated and repopulated with data (using a bulk import from a flat file), the index is also recreated using the following code:
    Code:
    CREATE  INDEX [IX_WON_Staging_EpsEst] 
    ON [dbo].[WON_Staging_EpsEst](OSID, [Year], Period)
    ON [PRIMARY]
    Yet more often than not, that evening, when the index maintenance job runs, it fails with the aforepasted messages complaining of being unable to find table/index statistics.

    Worth noting, perhaps, is that this same process is used on roughly 10 data staging tables in this database each day, and none of the other tables fail during the index maintenance job.

    Also worth noting, perhaps, is that this IDENTICAL table/code is processed in exactly the same way on TWO other servers, and the failure has not occured in any of the jobs on those other two servers (these other two servers are identical mirrors of the one failing, and contain all the same data, indicies, and everything else.

    Any thoughts, suggestions for where to look, or unrestrained abusive comments regarding my ancestry?

    Thanks!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I don't suppose there is an update statistics type of job running after the other loads, is there? The auto-update statistics is nice, but it can be fooled. Have a look at the STATS_DATE function. Can you incorporate that to run just before the DBCC SHOWCONTIG and see if statistics exist just before the error?

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Thanks for the suggestion. There really "should not be" any different functionality between the other databases on this machine (or across the three mirrored machines, for that matter).

    I can't for the life of me think of what is different on this server.

    I'll give this a try though! at least it may show me/us something that will lead to a solution down the road a day or two.

    BTW, interestingly enough, the reindex attempt takes place at 6:00am, and the import job that clobbers and reacreates the table takes place at 8:00 am.

    We were noticing that all we have to do (when whomever comes in on support that day) is re-run the index maintenance job manually and it works just fine. This is likely because the load job that runs at 8am appears to update the index statistics for the table. That is the time that I get returned to me when I (just now) did the STATS_DATE for the table in QA.

    Anyway, I'll run the STATS_DATE before the reindexing job in the morning and see what happens on Monday.

    Thanks for the assistance and suggestion!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  4. #4
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    The statistics are there at the end of the workday, but gone in the morning at 6am!!!? Hmmm...gremlins??? The only thing that happens to the database between those two times is a full database backup at about 11PM.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just a quick thought (ok, actually 2).
    1) Why are you dropping the table and re-creating -- surely a truncate would give you the same effect more efficiently?

    2) After your create table statement, stick a simple GO "command". I've sometimes had problems where the table is not recognised if it is created in the same "batch" of script.

    Hope this helps!
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Sean
    re-run the index maintenance job manually and it works just fine.
    Sounds like a permissions issue?
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Quote Originally Posted by TallCowboy0614
    This table is dropped and recreated each day during a data import job. After the table is recreated and repopulated with data (using a bulk import from a flat file), the index is also recreated using the following code:
    Drop a table, drop the statistics. The Auto-Statistics routine apparently does not have time, or the inclination to recreate them for some reason. As George said, try using truncate, instead. I just did a quick test, and it appears to leave the statistics intact. The only problem is that the indexes may think you are loading a full table. Well, that's what a test environment is for, eh?

  8. #8
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    OK, before I modify things (and again, I am flummoxed that the problem is only happening on one of the three (identical) servers, and it happens to be the brand-spankin-new one (though this is an HP and the others are Dell, dammit - I'm sure it's not related, but I felt compelled to mention it anyway ), here is the result of the statistics date info that I inserted into the job on Friday. This is from the run that failed this morning:
    Code:
    Index Name                 Statistics Date
    --------------------------------------------------
    WON_Staging_EpsEst         (null)
    IX_WON_Staging_EpsEst      2007-08-03 08:02:02.683
    _WA_Sys_Date_0D357924      2007-08-03 08:02:03.747
    _WA_Sys_Year_0D357924      2007-08-03 08:02:03.870
    _WA_Sys_Period_0D357924    2007-08-03 08:02:04.013
    _WA_Sys_EpsEst_0D357924    2007-08-03 08:02:04.183
    
    (6 rows(s) affected)
    This is the same information that is present after the second run of the job that works - well, the timestamps are different, but all are there and the table reference at the top is NULL also.

    BTW, why is the table name the first entry in the output of this command? There is not explicit index with the same name as the base table. The only explicit index is the one listed second in the output above, the IX_ one.

    I'll go ahead and change the drop to a truncate, there is no reason for the drop rather than a truncate except for convention in the code here.

    BTW, the same code is used for every database in our server farm. WITH the drop/recreate stuff there as well. Of all the databases we have here, on all the servers, this database on this server is the only one that fails, and it consistently fails on the same table only.

    Within this same database there are around 8 other staging tables in use, and all are created daily using the drop/recreate scheme, and all use exactly the same maintenance script each night. Again, this is the only table upon which the maintenance job fails.

    I don't think it's a premissions issue, Geo, because the job that fails is the same one that is re-run a second time and works just fine. The only difference is that the first run of the maintenance job fails when it is run at 6am, against the table that was dropped/recreated the previous day at 8am. The second run of the maintenance job (launched, incidentally, using the SQL Agent, as is the first) is successful (every time) and runs against the table that was dropped and recreated during the current day at 8am.

    6am Mon - index maintenance job runs, fails
    8am Mon - drop/recreate/import job runs OK
    10am Mon - index maintenance job runs, successful
    6am Tue - index maintenance job runs, fails
    ...
    ...
    ...
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  9. #9
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    ah-HA!!!

    I just found out that
    Code:
    DBCC SHOW_STATISTICS (won_staging_epsest, won_staging_epsest)
    results in exactly the same error being reported in the maintenance script! Which is...
    Code:
    Server: Msg 2767, Level 16, State 1, Line 1
    Could not locate statistics 'won_staging_epsest' in the system catalogs.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    So why does my indexing script think there is a index with the same name as the base table, especially since then I look at the "Design table->Properties->Indexes/Keys" option for the base table in Enterprise Manager, and only see the one IX_ index shown?
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    In SQL 2000, there is always an "index" on a table. Index ID 0, to be precise. Does your script not filter that out? You may also want to have a look at index ID 255, which is usually the table name with a "t" appended to the name. This is where text/image data is stored.

  11. #11
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Oh, this is fun! I think I am getting closer!

    Bear with me as I learn something new (though I have not yet decided what the logic behind the new knowledge is).

    While reading up on statistics, I came upon a notation in BOL that mentioned that a clustered index must be created on a VIEW before a non-clustered can be created. For whatever reason, the index being created on this table is being created as non-clustered. Going off on a tangent prompted by the notation in BOL, I got to thinking about the non-clustered index on this table.

    Since this a very static table (it is dropped, recreated, bulk-loaded, indexed, but then never changed at all) I got to wondering why the index being created is not clustered? I mean, isn't a "reference" or static table such as this one of the basic the primary candidates for a clustered index?

    So anyway, I change the index to a clustered index, and Viola', the mystery index with the same name as my base table disappears from the results of the troubleshooting query!
    Code:
    Index Name                Statistics Date
    -------------------------------------------------- 
    IX_WON_Staging_EpsEst     2007-08-06 12:08:23.057
    _WA_Sys_Date_2E966CEF     2007-08-06 12:08:23.290
    _WA_Sys_Year_2E966CEF     2007-08-06 12:08:23.477
    _WA_Sys_Period_2E966CEF   2007-08-06 12:08:23.667
    _WA_Sys_EpsEst_2E966CEF   2007-08-06 12:08:23.870
    
    (5 row(s) affected)
    Hmmmm...So obviously I have more reading to do to figure out whassup, but at least I know why the "extra index" was being shown for this table.

    Still doesn't explain to me why this table maintenance job was failing always on this table and always on this server, when there are two duplicate servers that ran fine each day (the same table contained an unclustered index on both of them also).

    Also, there is one other job on this server that bulk-loads data into 3 such staging tables - - all with similar structure (table with one non-clustered index) - - that also run fine every day and on all three mirror servers.

    Worth noting, perhaps, is that the other databases that have staging tables such as this, happen to all use a clustered primary key index. On this job that is failing and the other one that had non-clustered indexes it was necessary to use an index rather than a primary key because of the possibility of duplicate key issues. So on the myriad other jobs that I was previously using as a "well, it works on these tables" do not offer me any backup in this case (although there is still the mystery of the 3 mirror servers, and the fact that this problem happens only on one of THEM).

    Anyway, just wanted to provide some input into what I have seen so far. I will implement the code for tomorrow's job using the clustered index creation and see how that goes.

    Meanwhile, if anyone can shed some light on what I am seeing, I'd appreciate it and will be keeping an eye out here while perusing BOL for more info.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  12. #12
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by MCrowley
    In SQL 2000, there is always an "index" on a table. Index ID 0, to be precise. Does your script not filter that out? You may also want to have a look at index ID 255, which is usually the table name with a "t" appended to the name. This is where text/image data is stored.
    I think this jibes with what I posted just above also. a different way to say the same thing? Maybe this index is the "clustered" index that "must" be on a table if there is no other one? Just shooting in the dark at this point, but perhaps shooting nearby if not on-target

    My script doesn't filter that out, no. But it does not fail on other servers either...so that part still confuses me.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  13. #13
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Actually, my maintenance code DOES check for IndexID 0
    Code:
    Status = CASE 	WHEN IndexId = 0 					-- heap
    				THEN 9 						-- eliminate from consideration
    			WHEN IndexId = 1 					-- clustered index
    				THEN	CASE WHEN LogicalFrag < 10 
    							THEN 9			-- eliminate from consideration
    						WHEN CountPages < 10000
    							THEN 4 			-- reindex 
    						ELSE 1 				-- defrag
    					END
    			WHEN IndexId BETWEEN 2 AND 254 				-- nonclustered index
                              .
                              .
                              .
    			ELSE 9 							-- IndexId = 255 for text column pointer
    so the issue may be that this index is even getting selected for processing as opposed to why it is causing the failure!!!

    BTW, between you, BOL, and this code, I now understand why every table needs indexID 0 - which is the heap-o-data itself. So I guess I can go home now, I have learned my new "something" for the day!

    Thanks much for helping guide me to a new discovery, dudes!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  14. #14
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    OK, I guess you want the bloody details.

    In SQL Server, a table with no clustered indexes is referred to as a heap. As a heap, it has no particular order on disk. If you put a clustered index on a table, all of these rows are put in order on the disk. The bottom (or leaf) pages of the index are in fact the data pages of the table. This ordering is reflected in the order that a "select *" query generally returns data from this table to you, and is sometimes used as a crutch by lesser experienced programmers ("What do you mean? The data ALWAYS comes back in that order."). So, if a heap, and a clustered indexed table are so similar, how are they represented in the system tables?

    Very similarly.

    In fact, index ID 0 indicates a heap table, while index ID 1 indicates a clustered indexed table. You will also find that no table has both index ID 0 and 1. Also, no table has neither index ID 0 or index ID 1. In fact, in a heap, SQL Server adds a "row number" to each row (composed of databaseid, fileid, pageid, and slotid) so a non-clustered index can uniquely identify each row. In a clustered indexed table the clustered index key is used to identify rows. This is why views need to have the clustered index created first. So they have something to reference.

    Now, you know you need to name a clustered index, but what about a heap? SQL Server defaults to (and in fact enforces) the heap name to be same as the table name. And why not, after all. You know no two heaps will be named the same that way, right?

    I was interrupted a number of times while writing this, so if it is confusing, let me know. I am sure Pat has sniped me several times by now, as well.

  15. #15
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Mr. C!!! Great explanation!!! I was able to glean some of your points from documentation, either online or in BOL, but this post is very concise and understandable. Thanks very much! I especially liked the point about the result pattern of returned data in the SELECT * FROM... since I was at a loss for explaining the order of data returned in some troubleshooting exercises over the years. I've seen it, but could not explain away the differences in order of the returned data at times.

    Hopefully creating the tables with a clustered index has gotten me past my failure point, but still I can't explain why it failed the first time, but not the second time the job was run each day. Also why the functionality was different among tables set up the same way (the other staging tables in this and other databases). Primarily, I am still concerned about why my switch statement allowed the heap to be processed at all during the reindex/defrag maintenance job.

    I suppose I could leave the table structure the same way on the new server and put some troubleshooting code into the proc on that server. At least then, if reproducable, I can figure out why the heap is being selected for processing when the case statement in the reindex job specifically sets that indexID to be skipped.

    Regardless, I do appreciate the insight provided in this thread! Thanks!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •