Index usage overview

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

    Wilfred
    The best things in life are the simple things

  • 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'.

  • 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.

  • 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

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

  • 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.

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

    Thanks!

  • 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.

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

    Wilfred
    The best things in life are the simple things

  • 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

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

    Tim White

  • 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

  • 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

  • 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

  • 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 35 total)

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