Misleading FAQ Article??
-
There's a FAQ article at:
http://qa.sqlservercentral.com/faq/viewfaqanswer.asp?categoryid=2&faqid=257
with a title of:
"SQL Server FAQ: How can find what indexes are used on a table or database?"
The FAQ covers the basic usage of "sp_indexes_rowset" (which I can find to be no different than "sp_indexes" when running against one table), but my issue with this is....how exactly is it telling me "...what indexes are used on a table or database"? It IS telling me what indexes exist for a table, but NOT (that I can tell) what ones are being utilized (used).
I was pretty excited to see something like this, but pretty let down when I actually got to the content.
How did this get a 5-Star rating?
-
This was removed by the editor as SPAM
-
LMAO......you are correct. In reading the article, I see your point. Just goes to show you that the readers didn't actually understand the difference, or perhaps rated it on different criteria than what others might have.
If you really want to know what indexes are used on a table, you must profile the database, including indexid, and objectid in the profile, and then examine the results. I generally profile it for a full week in my situation, in order to guarantee a full scope of actions in the database. (yours may be more or less)
Looking through the results, you can determine all accessed indexes (clustered =1, others = 2+ ) in the database by object, and identify objects where no index is used (index id 0) as well.
-
I'd agree on all counts. Sorry about that. Scorpion, you don't have all that wrapped up into a proc(s)?
Andy
-
I could throw it together easy enough if yall are interested.....always glad to help....but up to this point I have just done it manually.
-
Hey some replies.
How much of a performance hit do you usually see with the profile running?
I'm trying to clean up some indexes on a warehouse database. the database is about 130G, and mostly hit by users in the day, and adding/removing data at night. I've chopped the 12-14hr updates down to around 4-5hrs, but if I could eliminate some indexes, that would help even more.
-
The performance hit is really determined by how large the profile is. I generally limit this particular one to object scans, procs completing, and statements completing, and delimit the database id to the one I'm checking, eliminating system objects. Under these conditions, and storing the profile data to a local table, I get a minimal impact. Of course, under heavy loads, this can still be noticable, so common sense would have to be applied. Also, you need to run the profile long enough to be guaranteed of a full range of usage, so if you have a report that only runs once a month, you would want to be sure to include that time frame, etc....
I looked into wrapping this all up in a nice package that I could post, but since tracing to tables is not supported by scripting, the trace template would have to be used, and when it comes right down to it, it would be easier for you to simply create a trace, gather the data, and then query your table. I may end up writing the procedure and considerations up into a document, if I get time, and I took some screen shots the last time I did this for some of the junior dba's on one of the teams I work with, to that end. Maybe I can do it this weekend.
-
Scorpion - if you're doing to do all that, how about wrapping it up into a 'teach us how to fish' article as well? We can always use a little more content!
Andy
-
That's what I was talking about. And yes, this weekend I will write it up as a document and publish it here for everyone. I verified yesterday that nothing goes to production this weekend, so I should have the time.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply