June 3, 2002 at 11:43 am
I ran this query:
SELECT 'Index Name' = i.name,
'Statistics Date' = STATS_DATE(i.id, i.indid)
FROM sysobjects o, sysindexes i
WHERE o.name = '<table_name>'
AND o.id = i.id
and found some indexes that do not show up in Enterprise Manager. For example, _WA_Sys_LibraryID_7085C28B. What are these indexes and where do they come from? Should the even be there?
Thanks,
Jeff
"Keep Your Stick On the Ice" ..Red Green
June 3, 2002 at 12:04 pm
These are likely created because you have autostats on for the given database. They are created by SQL Server when that is the case. Here's some more information on statistics:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_72r9.asp
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
June 3, 2002 at 12:19 pm
Thanks Brian,
Can I delete these indexes, and do they consume space?
"Keep Your Stick On the Ice" ..Red Green
June 3, 2002 at 6:02 pm
Do not delete directly, try to determine which stats they are from and delete that stat set. They do use space but it should be very minor.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 3, 2002 at 6:33 pm
Unless you have a really good reason, I'd leave them. They are used to help the query optimizer work better.
Andy
June 3, 2002 at 10:54 pm
Brian is correct, they are statistics collections resulting from the auto create stats DB option. If you query any column SQL Server will collect stats for it, try it out yourself with a query over a column rarely (or never) used in a where predicate and you will be supprised what SQL Server does.
As part of best practice, I dont touch them.
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
June 4, 2002 at 4:54 am
But that is only if you have auto create statistics turned on in DB options. But I do agree you really shoul leave as is. They bennifit you in query plan generation.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 4, 2002 at 9:45 am
Thanks for the info everyone. I noticed the query plans have changed over the last couple of weeks and some apps are having performance issues. I don't understand in it's entirety why this has happened, but I am learning.
"Keep Your Stick On the Ice" ..Red Green
June 4, 2002 at 9:55 am
You might try sp_updatestats and look at DBCC DBREINDEX (SQL 7) or DBCC INDEXDEFRAG (SQL2K) and DBCC UPDATEUSAGE. May have some maintainence issues.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply