Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345

Indexes, Indexes, Indexes Expand / Collapse
Author
Message
Posted Friday, July 8, 2011 5:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 26, 2012 7:57 AM
Points: 12, Visits: 523
@ Brian O'Leary

Please go back and read what I posted earlier in this thread and run the query I provided. I think I already showed where you may be mistaken.

The data collection query in the article always returns table names from the current database due to how the OBJECT_NAME function is used, and it always returns index names from the current database, but it can associate them with index usage stats for different indexes on different tables in different databases. The association (JOIN) is on object_id and index_id. If object_id and index_id match across databases (I have several cases of this occurring on a lightly loaded SQL Server instance) then the query returns very misleading rows. The names of the tables and indexes in the other databases are irrelevant because the query does not see them.

You have minimized (again) the inaccuracy of the data collection query in the article. Your "observations" are stated authoritatively, but I believe they are wrong.

Post #1139291
Posted Thursday, July 5, 2012 6:16 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:37 PM
Points: 35,353, Visits: 31,895
I know this is an old article but since it was recently republished I have to say that I hope and pray that folks read the discussion before they even think about using the code as is especially when it comes to the code that supposedly returns the correct name of the table and index. To be blunt, the code is incorrect for what it's advertised to do and you could end up deleting incorrect indexes if you use the code as is.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1325807
Posted Friday, July 6, 2012 12:08 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 5:41 PM
Points: 356, Visits: 918
Naked Ape (7/5/2011)
Interesting reading. Was that a deliberate reference to Alices Restaurant, Massacre (twenty-eight color graphs with circles and arrows and a paragraph on the bottom of each one)?


Thank you Naked Ape. It took until the 2nd page of posts but I'm glad I wasn't the only one who picked up on the Arlo Guthrie reference.
Post #1326262
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse