sys.dm_db_index_usage_stats

  • okbangas

    SSChampion

    Points: 11773

    According to books online, the user_seeks column contains

    Number of seeks by user queries

    Now, what is actually "number of seeks". I sincerely thought, that if a query used an index and used a seek operator to get values for 100 rows, then user_seeks would increase by 100. Apparently not. I was running a query seeking up approximately 32000 rows, still the user_seeks only increased by 1. So, I believe that user_seeks is the number of seek operators that has executed against that table.

    Am I right in my conclusion here, or have I overlooked something?



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • Gail Shaw

    SSC Guru

    Points: 1004484

    If a query seeks and returns 32000 rows, it's a single seek, so it should only increase the counter by 1. It's number of seeks, not number of rows returned by seeks.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Adi Cohn-120898

    SSC-Dedicated

    Points: 33944

    Just look at the query plan. The number of times that you see the seek operator in the query plan, is the number of times that the seek operation was used.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Gail Shaw

    SSC Guru

    Points: 1004484

    Adi Cohn-120898 (10/27/2011)


    Just look at the query plan. The number of times that you see the seek operator in the query plan, is the number of times that the seek operation was used.

    Not necessarily. What about operators that got executed multiple times?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • okbangas

    SSChampion

    Points: 11773

    My Seek operator executed approximately 32000 times, fetching one row each time. It still showed up as a single seek in sys.dm_db_index_usage_stats.



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

Viewing 5 posts - 1 through 5 (of 5 total)

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