Entries in Sysindexes

Recently a copy of a production database was made in order to try to improve
for an application that was experiencing slowness. Performance had
apparently improved until today. The developers are claiming that the
degredation in performance is due to extra enties in sysindexes with names
starting with '_WA_SYS_'. A more complete example is
'_WA_Sys_TS_ESTATE_ANALYSIS_29221CFB'
When I execute sp_helpindex on a table with these kind of entires, I do not
see anything that corresponds to this name. I did notice that for a given
table there could be many entries like this. I do see entires in sysindexes
corresponding to actual indexes. I've also noticed that the entries starting
with '_WA_SYS_' have the first & root fields equal to '0x00000000'.
Can someone explain what these '_WA_SYS_' entries are and can they be
deleted safely?
There is a mainteneace plan set up to due data & index page reorganization
everyday except Sunday. Could this be what is creating this entires?
Thanks
--
MGI believe those are index column statistics and I am sure they are not the
cause of any degraded performance. IMHO you should not delete them.
Nathan H. Omukwenyi.
"MGeles" <michael.geles@.thomson.com> wrote in message
news:E3DF30D2-787E-4051-B58A-1C49EE8BCE2C@.microsoft.com...
> Recently a copy of a production database was made in order to try to
> improve
> for an application that was experiencing slowness. Performance had
> apparently improved until today. The developers are claiming that the
> degredation in performance is due to extra enties in sysindexes with names
> starting with '_WA_SYS_'. A more complete example is
> '_WA_Sys_TS_ESTATE_ANALYSIS_29221CFB'
> When I execute sp_helpindex on a table with these kind of entires, I do
> not
> see anything that corresponds to this name. I did notice that for a given
> table there could be many entries like this. I do see entires in
> sysindexes
> corresponding to actual indexes. I've also noticed that the entries
> starting
> with '_WA_SYS_' have the first & root fields equal to '0x00000000'.
> Can someone explain what these '_WA_SYS_' entries are and can they be
> deleted safely?
> There is a mainteneace plan set up to due data & index page reorganization
> everyday except Sunday. Could this be what is creating this entires?
> Thanks
> --
> MG|||When the Auto Create Statistics option is enabled, _WA_ indexes are
created for columns that do not have an index.
Somewhere I read, can't find the article right now, that if you are
seeing indexes named that way, it is advisable to add indexes to replace
them, as the Auto created ones are not as efficient as a regular index.
The article also stated that the Auto Create Statistics should not be
disable as any index is better then none.
HTH
Michael
nathan wrote:
> I believe those are index column statistics and I am sure they are not the
> cause of any degraded performance. IMHO you should not delete them.
> Nathan H. Omukwenyi.
>
>
> "MGeles" <michael.geles@.thomson.com> wrote in message
> news:E3DF30D2-787E-4051-B58A-1C49EE8BCE2C@.microsoft.com...
>> Recently a copy of a production database was made in order to try to
>> improve
>> for an application that was experiencing slowness. Performance had
>> apparently improved until today. The developers are claiming that the
>> degredation in performance is due to extra enties in sysindexes with names
>> starting with '_WA_SYS_'. A more complete example is
>> '_WA_Sys_TS_ESTATE_ANALYSIS_29221CFB'
>> When I execute sp_helpindex on a table with these kind of entires, I do
>> not
>> see anything that corresponds to this name. I did notice that for a given
>> table there could be many entries like this. I do see entires in
>> sysindexes
>> corresponding to actual indexes. I've also noticed that the entries
>> starting
>> with '_WA_SYS_' have the first & root fields equal to '0x00000000'.
>> Can someone explain what these '_WA_SYS_' entries are and can they be
>> deleted safely?
>> There is a mainteneace plan set up to due data & index page reorganization
>> everyday except Sunday. Could this be what is creating this entires?
>> Thanks
>> --
>> MG
>|||"Michael T" <michaelteff@.skyline.com> wrote in message
news:uD1bdQlYGHA.4424@.TK2MSFTNGP05.phx.gbl...
> When the Auto Create Statistics option is enabled, _WA_ indexes are
> created for columns that do not have an index.
> Somewhere I read, can't find the article right now, that if you are seeing
> indexes named that way, it is advisable to add indexes to replace them, as
> the Auto created ones are not as efficient as a regular index. The article
> also stated that the Auto Create Statistics should not be disable as any
> index is better then none.
>
Ok. Indexes and Statistics are distinct, but related objects. When you
create an index, statistics are created automatically, but you might want to
create additional statistics on unindexed columns or sets of columns.
See:
Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
Much of the information is valid for SQL 2000 too.
David|||MGeles,
Those entries are create automatically by sql server when the database
option "auto create statistics" is on. SQL Server create those statistics on
columns used in the "join" clause, or in the "where" clause, and there is not
an index associated to them (from where sql server can access distribution
statistics about those columns). These statistics are used by the query
optimizer when creating the execution plan. If you delete those entries, SQL
Server will create them again as soon as it needs them.
Statistical maintenance functionality (autostats) in SQL Server
http://support.microsoft.com/kb/q195565/
AMB
"MGeles" wrote:
> Recently a copy of a production database was made in order to try to improve
> for an application that was experiencing slowness. Performance had
> apparently improved until today. The developers are claiming that the
> degredation in performance is due to extra enties in sysindexes with names
> starting with '_WA_SYS_'. A more complete example is
> '_WA_Sys_TS_ESTATE_ANALYSIS_29221CFB'
> When I execute sp_helpindex on a table with these kind of entires, I do not
> see anything that corresponds to this name. I did notice that for a given
> table there could be many entries like this. I do see entires in sysindexes
> corresponding to actual indexes. I've also noticed that the entries starting
> with '_WA_SYS_' have the first & root fields equal to '0x00000000'.
> Can someone explain what these '_WA_SYS_' entries are and can they be
> deleted safely?
> There is a mainteneace plan set up to due data & index page reorganization
> everyday except Sunday. Could this be what is creating this entires?
> Thanks
> --
> MG|||MG-
Check out the link below.
http://www.extremeexperts.com/SQL/FAQ/SysStats.aspx
--
Thomas
"MGeles" wrote:
> Recently a copy of a production database was made in order to try to improve
> for an application that was experiencing slowness. Performance had
> apparently improved until today. The developers are claiming that the
> degredation in performance is due to extra enties in sysindexes with names
> starting with '_WA_SYS_'. A more complete example is
> '_WA_Sys_TS_ESTATE_ANALYSIS_29221CFB'
> When I execute sp_helpindex on a table with these kind of entires, I do not
> see anything that corresponds to this name. I did notice that for a given
> table there could be many entries like this. I do see entires in sysindexes
> corresponding to actual indexes. I've also noticed that the entries starting
> with '_WA_SYS_' have the first & root fields equal to '0x00000000'.
> Can someone explain what these '_WA_SYS_' entries are and can they be
> deleted safely?
> There is a mainteneace plan set up to due data & index page reorganization
> everyday except Sunday. Could this be what is creating this entires?
> Thanks
> --
> MG