Saturday, 19 July 2008

Index maintenance using DMV’s in SQL Server 2005

SQL Server 2005 provides more flexible ways handling index maintenance activities using DMV (Dynamic Management View’s). The DMV’s can be effectively used to identify the index status such as

  • Indexes that requires maintenance activities such as reindexing / reorganizing
  • List of indexes that are going for index scan
  • List of not used indexes

Tables with indexes that require maintenance

SELECT OBJECT_NAME(OBJECT_ID) TableName,
( SELECT NAME FROM SYS.INDEXES
WHERE OBJECT_ID = A.OBJECT_ID
AND INDEX_ID = A.INDEX_ID) IndexName
FROM SYS.DM_DB_INDEX_USAGE_STATS A
WHERE USER_SEEKS >0 AND USER_SCANS >0
AND OBJECT_ID > 97
AND DATABASE_ID = 5
ORDER BY USER_UPDATES, USER_SEEKS DESC


Tables with Indexes going for Scan

SELECT OBJECT_NAME(OBJECT_ID) TableName ,
( SELECT NAME FROM SYS.INDEXES
WHERE OBJECT_ID = A.OBJECT_ID
AND INDEX_ID = A.INDEX_ID) IndexName
FROM SYS.DM_DB_INDEX_USAGE_STATS A
WHERE INDEX_ID <> 0
AND OBJECT_ID > 97
AND DATABASE_ID = 5
AND USER_SCANS > 0 ORDER BY USER_SCANS DESC

Tables with not used indexes

SELECT OBJECT_NAME(OBJECT_ID) TableName,
(SELECT NAME FROM SYS.INDEXES
WHERE OBJECT_ID = A.OBJECT_ID
AND INDEX_ID = A.INDEX_ID) IndexName
FROM SYS.DM_DB_INDEX_USAGE_STATS A
WHERE USER_SEEKS = 0
AND USER_SCANS =0
AND USER_LOOKUPS =0
AND USER_UPDATES = 0
AND OBJECT_ID > 97
AND INDEX_ID <> 0
AND DATABASE_ID = 5

6 comments:

Anonymous said...

works great as long as your database has ID 5 :-)

Maybe a little explanation around how to determine the Database ID??
But still really hnady - Thanks.

Samsudeen B said...

Hi Thanks for your comments

You can use the any of the following methods to get the Database ID

1.Replace "5" with the function DB_ID('Databasename')

2.replace "5" with db_id(),but make sure you are running the query on the same database on which you wanted to capture the statistics

ninest123 said...

ninest123 16.03
ray ban sunglasses, cheap oakley sunglasses, replica watches, michael kors outlet, oakley sunglasses, jordan shoes, prada handbags, nike outlet, nike air max, nike free, louis vuitton, tiffany and co, ugg boots, louboutin outlet, ray ban sunglasses, louis vuitton outlet, burberry outlet online, michael kors outlet, chanel handbags, uggs on sale, tiffany jewelry, louis vuitton outlet, michael kors outlet, oakley sunglasses, oakley sunglasses, louboutin shoes, prada outlet, ugg boots, ray ban sunglasses, ugg boots, burberry, replica watches, michael kors outlet, tory burch outlet, longchamp outlet, michael kors outlet, louis vuitton, michael kors, polo ralph lauren outlet, longchamp outlet, louis vuitton, polo ralph lauren outlet, louboutin, oakley sunglasses, gucci outlet, ugg boots, christian louboutin outlet, nike air max, longchamp

ninest123 said...

north face, hogan, coach outlet, air force, hollister pas cher, michael kors, coach outlet, lululemon, burberry, sac guess, nike roshe, nike free, true religion jeans, air jordan pas cher, lacoste pas cher, ralph lauren pas cher, michael kors, kate spade outlet, ralph lauren uk, michael kors, new balance pas cher, true religion jeans, ray ban uk, nike roshe run, converse pas cher, michael kors, hermes, mulberry, vans pas cher, louboutin pas cher, true religion outlet, ray ban pas cher, nike air max, oakley pas cher, air max, timberland, nike free run uk, kate spade handbags, nike air max, hollister, tn pas cher, replica handbags, nike air max, vanessa bruno, abercrombie and fitch, sac longchamp, true religion jeans, nike blazer, coach purses, longchamp pas cher, north face

ninest123 said...

ralph lauren, babyliss, bottega veneta, ipad cases, louboutin, baseball bats, ferragamo shoes, iphone 6 plus cases, mac cosmetics, iphone 6s plus cases, longchamp, lululemon, herve leger, chi flat iron, nfl jerseys, iphone cases, valentino shoes, hollister, new balance, nike trainers, hollister, beats by dre, iphone 5s cases, iphone 6 cases, celine handbags, north face outlet, oakley, mcm handbags, abercrombie and fitch, wedding dresses, soccer jerseys, insanity workout, soccer shoes, nike air max, vans shoes, instyler, nike huarache, nike roshe, north face outlet, p90x workout, ghd, hollister, iphone 6s cases, nike air max, birkin bag, timberland boots, jimmy choo shoes, s5 cases, reebok shoes, giuseppe zanotti, mont blanc, asics running shoes

ninest123 said...

ugg,uggs,uggs canada, canada goose, ugg,ugg australia,ugg italia, canada goose, hollister, wedding dresses, moncler, louis vuitton, canada goose outlet, swarovski, pandora charms, pandora jewelry, moncler outlet, moncler, sac louis vuitton pas cher, replica watches, louis vuitton, marc jacobs, louis vuitton, vans, moncler, swarovski crystal, pandora jewelry, toms shoes, moncler, juicy couture outlet, doudoune canada goose, karen millen, converse, lancel, canada goose outlet, coach outlet, gucci, ray ban, thomas sabo, moncler, supra shoes, canada goose uk, juicy couture outlet, links of london, barbour jackets, louis vuitton, canada goose, barbour, ugg boots uk, pandora charms, bottes ugg, converse outlet, moncler, montre pas cher, canada goose, ugg pas cher, moncler
ninest123 16.03