WA_sys indexes (Who, where, why???)

Question

WA_sys indexes (Who, where, why???)

kkoshinski

SSC Veteran

Points: 238
More actions
August 10, 2010 at 4:29 am

#236470

Guys, I am struggling with some WA_sys indexes on one of my databases. I generally keep auto statistics enabled, as is best practice according to Microsoft. But I have never seen WA_sys indexes created until I ran "sp_updatestats" one day. The database that I ran it on got tons of new indexes. Since there were a few performance complaints, the development team is blaming the problems on these new indexes.
I know the topic has been brought up before, but I am still struggling to understand exactly why these are created and what they do. I have read in some places that they aren't real indexes at all - they're just somehow related to statistics. But based on what I see in my database, I'm not entirely convinced. In fact after doing some testing, I have someone saying that a query ran 20 seconds faster after all these new indexes were dropped from a particular table.
Could anyone give me a little more insight on why they were generated, and what I should do with them (e.g. keep, drop)? The only performance issue I can forsee is that if 15 or 20 new indexes have been added to a table (as in some cases), inserts and updates could suffer.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Those aren't indexes. They're automatically created statistics.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kkoshinski SSC Veteran Points: 238 More actions · Answer 2

Thanks for your reply. But this doesn't add up for the following reasons:

A. They are displayed as indexes in SSMS

B. If I script one out to a query editor window, they look like indexes

C. They have identically named cousins in the statistics folder already, so why the duplication?

D. If I write an appropriate SELECT query, the Actual Execution plan returned by SSMS tells me it did an index scan on it

So from what I can see, these actually are indexes, otherwise queries wouldn't use them to retieve data.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Then someone has manually gone and added indexes matching to auto created stats. SQL will not create indexes automatically. The only thing it creates automatically are statistics and the WA_Sys_* naming is what it uses for automatically created statistics.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

refer this

http://technet.microsoft.com/en-us/library/cc966419.aspx

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)