Post reply

What is this _WA_Sys_tbl_59063A47 index

  • August 16, 2005 at 7:18 pm

    #89505

    I query sysindexes and see some unexpected indexes entries like _WA_Sys_tbl_59063A47 with indid>1. I checked Objecet Browser in query Analyzer, cannot see any indexes or constraints/trigger/dependencies like that.  I then did the following test :

    1) created two tables in database Pubs

    create table pubs.dbo.tmp_wmsdb1_fsquprod (server_db varchar(64) null,tbl varchar(32) null,rows int null)

    create table pubs.dbo.tmp_wmsdb1_fptr (server_db varchar(64) null,tbl varchar(32) null,rows int null)

    2) isnert a few rows in both tables

    3) check sysindexes, there is no this _WA_Sys record for the 2 tables

    4) query the two tables with full join

    select t1.tbl,t1.server_db,t1.rows,t2.server_db,t2.rows ,t1.rows-t2.rows

     from pubs.dbo.tmp_wmsdb_ptr t1 full join pubs.dbo.tmp_wmsdb1_fsquprod t2 on t1.tbl=t2.tbl

    5) check sysindexes again, and this time I see the _WA_SYS like records in sysindexes for these two tables with indid=2.

    What are these _WA_Sys entry in sysindexes, Are they real indexes ? How can I stop them being created ?

     

    Thanks

  • August 16, 2005 at 7:44 pm

    #582680

    These are statistics that are generated/used by the query optimiser when it's generating an execution plan. For what should be apparent reasons now, it is not a good idea to stop them from being created

     

    --------------------
    Colt 45 - the original point and click interface

  • August 17, 2005 at 9:22 am

    #582780

    Thanks.

    This explains many things. Is it documented somewhere?

  • August 17, 2005 at 1:55 pm

    #582870

    Like Phill says, don't mess with the Stats.  What you should do on a regular basis is to update the stats so that the optimizier has the latest stats to go on.  In order for the SQL Server Query Optimizer to make good decisions, it must have up-to-date statistics on the tables and indexes in your database.

  • August 19, 2005 at 3:48 am

    #583455

    They're not statistics but indexes that SQL Server creates itself when it feels this index would be useful to perform queries.

    Stats are different from Indexes.

    Paul R Williams.

  • August 19, 2005 at 5:13 am

    #583466

    I think i'd stick with phill's explanation if I were you. 

    These are most definately statistics that are automatically generated by SQL (assuming you've got that setting turned on) when it decides that the information would be useful in a query and there's no index already on the column.

  • August 19, 2005 at 6:42 am

    #583485

    Yup they're automatically generated stats. Indexes don't delete/generate themselves whenever they want.

  • August 19, 2005 at 7:59 am

    #583524

    Yes, I apologise, you're right - I've just gone back to check my notes (Friday feeling I'm afraid !)

    Paul R Williams.

  • August 19, 2005 at 8:09 am

    #583536

    It's almost over... only 6 hours left .

  • August 19, 2005 at 8:18 am

    #583542

    Ha Ha! 14 mins! Central European Time!

  • August 19, 2005 at 8:18 am

    #583540

    Speak for yourself Remi, another 2 hours for me and i'm off on holiday for a week.

    Woo hoo

Viewing 11 posts - 1 through 10 (of 10 total)

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