Indexusage stats

The following script is written by Greg_Linwood, it very useful when you want to check your index usage based on the table… 

create procedure [dbo].[usp_helpindexusagestats]

@tablename

varchar(1000)

as

set

nocount on

 

–collect output from system supplied [sp_helpindex]

if

object_id(‘tempdb..#helpindex’) > 0 drop table #helpindex

create

table #helpindex (

index_name

varchar (1000) not null primary key

, index_description varchar (1000) null

, index_keys varchar (1000) null)

insert #helpindex exec sp_helpindex @tablename

alter

table #helpindex add inccols varchar(1000) null

 

–cursor over output, adding included columns

declare

cr cursor for

select

si.name, sc.name

from sysobjects so

join

sysindexes si on so.id = si.id

join

sys.index_columns ic on si.id = ic.object_id and si.indid = ic.index_id

join

sys.columns sc on ic.object_id = sc.object_id and ic.column_id = sc.column_id

where

so.xtype = ‘U’

and so.name = @tablename

and ic.is_included_column = 1

order by si.name, ic.index_column_id

declare

@siname varchar(1000), @scname varchar(1000)

open

cr

fetch

next from cr into @siname, @scname

while

@@fetch_status = 0

begin

update #helpindex set inccols = isnull(inccols , ) + @scname + ‘, ‘ where index_name = @siname

fetch next from cr into @siname, @scname

end

update

#helpindex set inccols = left(inccols, datalength(inccols) 2)

where

right(inccols, 2) = ‘, ‘

close

cr

deallocate

cr

 

–return output, joining index usage stats DMV output

select

hi.index_name, hi.index_description, hi.index_keys

,

hi.inccols as included_columns, ius.index_id

,

user_seeks, user_scans, user_lookups, user_updates

,

last_user_seek, last_user_scan, last_user_lookup

from

#helpindex hi

join

sysindexes si on si.name = hi.index_name collate database_default

left

join sysobjects so on si.id = so.id

left

join sys.dm_db_index_usage_stats ius on ius.object_id = si.id

and

ius.index_id = si.indid and ius.database_id = db_id()

 

drop table #helpindex

Published Friday, 18 April 2008 1:04 AM by Greg_Linwood
Filed Under: ,
Advertisements
This entry was posted in SQL Server 2005 Scripts. Bookmark the permalink.

56 Responses to Indexusage stats

  1. Unknown says:

    liteon 19V 6.2A 120w 4 pin adapter laptop

    liteon 19V 6.32A 120w 4 pin adapter laptop
    liteon 19V 9.5A 180w 4 pin adapter laptop
    PA-1121-02 liteon 20V 6A 120w 5.5mm*2.5mm adapter laptop
    liteon 20V 6A 120w 4 pin adapter laptop
    liteon 20v 7.9A 180W 5.5mm*2.5mm adapter laptop
    liteon 20V 4.5A 90w 7.9mm*5.5mm pin inside adapter laptop

    92p1158 PA-1650-161 lenovo/Ibm 20V 3.25A 65w 7.9mm*5.5mm pin inside adapter laptop
    PC-VP-WP04 OP-520-69001 nec 15V 4A 60w 6.3mm*3.0mm adapter laptop
    PC-VP-BP21 PA-1480-19Q nec 19V 2.64A 50w 4.8mm*1.7mm adapter laptop
    ADP-60NH nec 19V 3.16A 60w 5.5mm*2.5mm adapter laptop
    OP-520-76401 nec 19V 6.32A 120w 5.5mm*2.5mm adapter laptop
    nec 19V 2.64A 50w 5.5mm*2.5mm adapter laptop

    PA-1121-03 PC-VP-WP55 nec 19V 6.3A 120w 5.5mm*2.5mm adapter laptop
    AD-4214N PSCV420102A samsung 14V 3A 42w 6.5mm*4.4mm adapter laptop
    SPA-P30 AD6019 samsung 19V 3.16A 60w 5.5mm*3.4mm pin inside

  2. hao says:

    添加评论

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s