Index Update Reports - Scans/Seeks

Question

Index Update Reports - Scans/Seeks

Viewing 3 posts - 1 through 4 (of 4 total)

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

ScottPletcher SSC Guru Points: 101207 More actions · Answer 1

If you issued an UPDATE against an empty table, the UPDATE counter would still increment.

Maybe that's what happened?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 2

That is possible. Our stored procedures are not called systematically and the parameters are probably not checked for no data. I guess just ignore those empty tables.

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 3

This won't tell you if a table is "empty" or not but it will tell you if it has been used by users since the last restart or not. I wouldn't use this to drop a table, though... you could have quarterly or annual tables or reference tables that only the system uses. Also, empty tables are sometimes the target of INSTEAD OF triggers.

As a bit of a side bar, you might want to bone up on some of the "OBJECT" related functions, start using aliases for improved readability and simplicity, and spend a bit more time aligning your code so that when you come back to it a year later, you don't have to spend so much time trying to read what it does.

   WITH cteGetUsage AS
(
 SELECT  CurrentDatabase    = DB_NAME()
        ,SchemaName         = OBJECT_SCHEMA_NAME(idx.object_id)
        ,TableName          = OBJECT_NAME(idx.object_id)
        ,IndexName          = idx.name
        ,IndexType          = idx.type_desc
        ,UserSeeks          = ISNULL(sta.user_seeks  ,-1) --The "-1" means not used since last restart
        ,UserScans          = ISNULL(sta.user_scans  ,-1) --The "-1" means not used since last restart
        ,UserUpdates        = ISNULL(sta.user_updates,-1) --The "-1" means not used since last restart
        ,Createdttm         = GETDATE()
   FROM      sys.indexes                 idx
   LEFT JOIN sys.dm_db_index_usage_stats sta  ON idx.object_id = sta.object_id 
                                             AND idx.index_id  = sta.index_id
)
 SELECT usg.*
   FROM cteGetUsage usg
  --WHERE usg.UserSeeks   < 1 --Uncomment the WHERE/ANDs to see only user unused Indexes/Tables/Heaps.
  --  AND usg.UserScans   < 1
  --  AND usg.UserUpdates < 1
  ORDER BY SchemaName, TableName, IndexName
;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)