February 28, 2008 at 4:56 am
On a table I ran SP_HELPINDEX and got some indexes back but I found what is a duplicate but I am hugely confused by one of the index’s name which I was sure is an auto statistics object name have I gone mad?(The index_keys are the same)
The indexes are as follows
1)CPK_EXTRACTS nonclustered, unique, primary key located on PRIMARY
2)_WA_Sys_extrc_refno_59D10CA5 nonclustered located on PRIMARY
So two question
No 1. Are they same is there any problem with dropping the second one?
No 2. Every time I have seen an object starting with _WA_Sys it has been a stat everything I can see says this is an index how could I end up with an index named in this way?
Many thanks
February 28, 2008 at 5:20 pm
Anything that starts with _WA_Sys is system created index which s used ofr faster retrival by sql server. you need to see if they stil exists and delte them as they can degrade your performance.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 28, 2008 at 6:37 pm
Sugesh Kumar (2/28/2008)
Anything that starts with _WA_Sys is system created index which s used ofr faster retrival by sql server. you need to see if they stil exists and delte them as they can degrade your performance.
You think so?? because i am facing also the same problem seeing not 1 but if i remember it correctly three (3) _WA_Sys created index. Is there no impact in the production database if i delete this indexes? because as you are saying it can degrade our server's performance and really in some cases our server is hitting with low performance
"-=Still Learning=-"
Lester Policarpio
February 28, 2008 at 10:45 pm
The _WA_sys aren't indexes. They're automatically created column statistics. Nothing more.
They're created to help the optimiser determine the number of rows that queries should return. They're created on columns that don't appear as the leading column of any index on the table
You can drop them safely. If SQL needs then, it will recreated them (assuming aut create statistics is on)
You don't drop them using DROP INDEX, you drop them using DROP STATISTICS.
If you have a lot of these stats, it can indicate the the indexes on the table aren't appopriate to the workload.
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
February 29, 2008 at 5:02 am
Thats what i thought objects named like that are stats but one I run
SELECT *
FROM SYSINDEXES idx
INNER JOIN SYSOBJECTS tbl ON idx.[id] = tbl.[id]
WHERE indid > 0
and tbl.[name] = 'extracts'
and INDEXPROPERTY( tbl.[id], idx.[name], 'IsStatistics') = 0
that name is showen, so it is an index not a stat this is confusing me. Why is it an index?
February 29, 2008 at 5:10 am
Odd. SQL won't automatically create indexes, only statistics.
Is it possible that someone created that index and gave it the same name as the statistic it was supposed to replace?
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply