Indexes Question

[marchanje]

The Manage Indexes option in EM shows 2 indexes for tblPersonnelInfo:
PK_tblPersonnelInfo and SSN_tblPersonnelInfo

Where did items 4 - 68 come from and why do they not show up in EM?

Query, against sysobjects and sysindexes show lots more:

Index information for Table: tblPersonnelInfo:
1. Index # 0 is tblPersonnelInfo and FillFactor 0
2. Index # 2 is PK_tblPersonnelInfo and FillFactor 90
3. Index # 3 is SSN_tblPersonnelInfo and FillFactor 0
4. Index # 4 is _WA_Sys_LName_263B8EAF and FillFactor 0
5. Index # 5 is _WA_Sys_FName_263B8EAF and FillFactor 0
6. Index # 6 is _WA_Sys_MI_263B8EAF and FillFactor 0
68. Index # 68 is _WA_Sys_NonJTDPosition_263B8EAF and FillFactor 0

Thanks,
Marc

 

[JamesLean]

These are column statistics which SQL Server has automatically created where it thinks they are needed to help query performance.

If you don't want it to do this then you can disable "auto create statistics" in the database options. --James

 

[marchanje]

Thanks James!

Anyone know of any good articles dealing with the pros and cons of having "auto create statistics" on?

 

[JamesLean]

I would recommend you have it kept on, unless you find a specific reason for turning it off. Check out this article for more info on this and other performance-improving features:

http://www.sql-server-performance.com/sql_server_performance_audit6.asp

--James