Post reply

Index usage overview

  • March 12, 2010 at 2:25 pm

    #217818

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

    Wilfred
    The best things in life are the simple things

  • March 23, 2010 at 12:18 am

    #1137985

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

  • March 23, 2010 at 3:49 am

    #1138052

    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.

  • March 23, 2010 at 6:41 am

    #1138158

    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

  • March 23, 2010 at 6:47 am

    #1138162

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

  • March 23, 2010 at 6:50 am

    #1138166

    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.

  • March 23, 2010 at 6:56 am

    #1138173

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

    Thanks!

  • March 23, 2010 at 7:33 am

    #1138209

    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.

  • March 23, 2010 at 8:45 am

    #1138266

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

    Wilfred
    The best things in life are the simple things

  • March 23, 2010 at 9:30 am

    #1138317

    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

  • March 23, 2010 at 9:31 am

    #1138318

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

    Tim White

  • March 23, 2010 at 10:04 am

    #1138359

    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

  • March 23, 2010 at 7:02 pm

    #1138634

    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

  • March 24, 2010 at 1:36 am

    #1138722

    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

  • March 29, 2010 at 2:29 pm

    #1141718

    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