Index usage overview

  • Wilfred van Dijk

    SSCrazy Eights

    Points: 8969

    Comments posted to this topic are about the item Index usage overview

    Wilfred
    The best things in life are the simple things

  • Madhu R

    SSC Veteran

    Points: 212

    Hi,

    I am trying to run your index preview. But its turns out as error for my DB.

    Here is the error

    Msg 102, Level 15, State 1, Procedure vw_index_usage, Line 3

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure vw_index_usage, Line 21

    Incorrect syntax near 'MB'.

    Msg 102, Level 15, State 1, Procedure vw_index_usage, Line 22

    Incorrect syntax near 'cols'.

    Msg 102, Level 15, State 1, Procedure vw_index_usage, Line 23

    Incorrect syntax near 'included'.

  • ian.scrivens

    Valued Member

    Points: 64

    This is because of the formatting when copying and pasting from the web.

    Select one of the 'space' characters, for example the first character on the second line the do a 'replace all' and replace them all with a single space. This should sort it out.

  • hfxDBA

    SSC Veteran

    Points: 265

    Not knocking the script... but for newbies would be nice to explain the column outputs in a brief description. They could probably find explanations in BOL (and common sense) but might be a nice to get the original thoughts form the OP

  • MOP

    Valued Member

    Points: 65

    That does not seem to fix things. Also, replacing the double quote (not space) does not fix things either...;-)

  • hfxDBA

    SSC Veteran

    Points: 265

    MOP (3/23/2010)


    That does not seem to fix things. Also, replacing the double quote (not space) does not fix things either...;-)

    I can confirm that the fix mentioned works (for me). if all else fails with the find and replace, retype the query and you'll be able to run it fine.

  • MOP

    Valued Member

    Points: 65

    Yes, my bad. I picked the first character in the third line, did a replace with space and the script ran just fine.

    Thanks!

  • ian.scrivens

    Valued Member

    Points: 64

    hfxDBA (3/23/2010)


    Not knocking the script... but for newbies would be nice to explain the column outputs in a brief description. They could probably find explanations in BOL (and common sense) but might be a nice to get the original thoughts form the OP

    I agree, and explaination would be nice.

  • Wilfred van Dijk

    SSCrazy Eights

    Points: 8969

    Got it, I'll post an explanation later this week.

    Wilfred
    The best things in life are the simple things

  • YvonneZ

    SSC Rookie

    Points: 46

    Hi there,

    I have encounted the following errors when trying the script:

    Server: Msg 208, Level 16, State 1, Procedure vw_index_usage, Line 3

    Invalid object name 'sys.index_columns'.

    Server: Msg 208, Level 16, State 1, Procedure vw_index_usage, Line 3

    Invalid object name 'sys.index_columns'.

    Server: Msg 208, Level 16, State 1, Procedure vw_index_usage, Line 3

    Invalid object name 'sys.dm_db_index_usage_stats'.

    Server: Msg 208, Level 16, State 1, Procedure vw_index_usage, Line 3

    Invalid object name 'sys.indexes'.

    Server: Msg 208, Level 16, State 1, Procedure vw_index_usage, Line 3

    Invalid object name 'sys.filegroups'.

    I guess it doesn't work on SQL 2000 (which is what I am using)?

    Thanks!

    Yvonne

  • 2 Tim 3:16

    SSCarpal Tunnel

    Points: 4431

    I get 0 rows returned. tried it on several DB's.

    Tim White

  • Wilfred van Dijk

    SSCrazy Eights

    Points: 8969

    I guess it doesn't work on SQL 2000 (which is what I am using)?

    Sorry, MSSQL 2005 and higher

    Wilfred
    The best things in life are the simple things

  • Madhu R

    SSC Veteran

    Points: 212

    Hi,

    Thanks. I removed those empty spaces.. !! Its "completed" successfully.

    However its turned out as 100% successfull, I can possibly run the view and its turns out as nothing. And I run across the same procedure into few DB's.

    L

  • Wilfred van Dijk

    SSCrazy Eights

    Points: 8969

    This view will only work in the database the view is created:

    and????????a.database_id = DB_ID()????????????-- for current database only

    (that's the only thing which comes into my mind, or there are no indexes in the database 😀 )

    Try cutting this view into smaller pieces, for example first query the dm_ view, add join on sysindexes etc.

    Wilfred
    The best things in life are the simple things

  • Gail Wanabee

    SSCrazy Eights

    Points: 8378

    Wilfred,

    What is the purpose of this elaborate query?

    (NOTE: I have commented out the ALTER INDEX (DISABLE) statement until I get an explanation of what the query is supposed to do.)

    I've run it against several test databases using SS2005 and SS2008. Without the ALTER INDEX (DISABLE) clause enabled, it appears to do nothing.

    LC

Viewing 15 posts - 1 through 15 (of 36 total)

You must be logged in to reply to this topic. Login to reply