What is this _WA_Sys_tbl_59063A47 index

Question

What is this _WA_Sys_tbl_59063A47 index

lugang

Right there with Babe

Points: 752
More actions
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

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

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

philcart SSC-Forever Points: 47795 More actions · Answer 1

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

lugang Right there with Babe Points: 752 More actions · Answer 2

Thanks.

This explains many things. Is it documented somewhere?

Vik Nat Hall of Fame Points: 3930 More actions · Answer 3

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.

Paul Williams SSCommitted Points: 1930 More actions · Answer 4

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.

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 5

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.