_WA_Sys Indexes - Do I need them & How for Drop?

[smedvid]

I have a large database 100GB (+) with some tables (6 fields) containing over 100 Million rows. This database was recently transferred from an environment managed by developers (i.e. me) to the data center where corporate DBAs manage all databases. I have noticed that several _WA_Sys (System Indexes?) have been created on many tables. In most cases, it appears to be using the same exact field in the _WA_Sys index that already exists in the predefined indexes.

Are these excessive system indicies going to slow down my INSERTs? Do I really need these system indexes if I already have all the tables/fields properly indexes. How can I DROP these system indexes, and instruct the DBAs not to create them again? Is there a setting for Auto-Create System indexes somewhere?

tia,



Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[JayKusch]

Actually ... they are going to speed up your queries!!! This are SQL Server Statistics. You can identify them by the prefix of WA_

Thanks

J. Kusch

 

[smedvid]

But, I don't understand since I already have indexes created on all the same fields that appear to have the _WA indexes. Should I delete all my created indexes in favor of using the _WA indexes? Plus, there is a slight concern about diskspace. These _WA indexes are consuming disk space, and if they are not needed, they could free up much needed space.

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[SQLBill]

Like Jay said, they aren't real indexes. They maintain statistics that help SQL Server speed up your query.

Let's say you have a list of names. Your index will keep track of them alphabetically. So you need to find Medvid. The index will keep branching down until it finds the pointer to Medvid and it will do this every time.

Now, let's say you frequently search for Medvid. SQL Server will create a statistic or update an existing one that will enable a search to skip most of the index. The query will be able to directly to the pointer instead of first finding the M-Z block, then the M to P block, etc....

(At least that's my understanding of those 'indexes')

-SQLBill

 

[smedvid]

I see... thanks! Steve

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...