Get Index Scan Count

  • Comments posted to this topic are about the item Get Index Scan Count

  • One problem with the script is that it is hard coded to use db_id 70 when using this:
    sys.dm_db_index_physical_stats (70, NULL, NULL, NULL, NULL) AS p

    Maybe change that to Default, null or 0 instead of 70

    Sue

  • Sue_H - Monday, January 8, 2018 3:02 PM

    One problem with the script is that it is hard coded to use db_id 70 when using this:
    sys.dm_db_index_physical_stats (70, NULL, NULL, NULL, NULL) AS p

    Maybe change that to Default, null or 0 instead of 70

    Sue

    Oh thanks for pointing that out Sue. Not sure if I can change it now.

  • You should mention in that script that it's index scans since the last time the database or SQL Server were restarted. Not over all time.

    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
  • Thanks Gail. Actually I had put that in brackets. I think I lost it when re-editing for some other words, and then didn't proof-read; Did this in wee hours of the morning before dawn.bad habit .

  • Sue_H - Monday, January 8, 2018 3:02 PM

    One problem with the script is that it is hard coded to use db_id 70 when using this:
    sys.dm_db_index_physical_stats (70, NULL, NULL, NULL, NULL) AS p

    Maybe change that to Default, null or 0 instead of 70

    Sue

    DB_ID() would probably be more efficient, since dm_db_index_usage_stats is scoped to the current DB.

    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
  • Yes Gail. Sue had pointed this too. This is the first script I posted and also no idea how can edit it now. Can u help ?

  • I'm not a site moderator.
    And I know Sue pointed it out, that's why my post was a reply to Sue

    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
  • GilaMonster - Tuesday, January 9, 2018 1:30 AM

    Sue_H - Monday, January 8, 2018 3:02 PM

    One problem with the script is that it is hard coded to use db_id 70 when using this:
    sys.dm_db_index_physical_stats (70, NULL, NULL, NULL, NULL) AS p

    Maybe change that to Default, null or 0 instead of 70

    Sue

    DB_ID() would probably be more efficient, since dm_db_index_usage_stats is scoped to the current DB.

    I referenced this: sys.dm_db_index_physical_stats (70, NULL, NULL, NULL, NULL)
    With dm_db_index_physical_stats, specifying default, null or 0 uses the current database.

    Sue

  • Sue_H - Tuesday, January 9, 2018 6:44 AM

    With dm_db_index_physical_stats, specifying default, null or 0 uses the current database.

    NULL, 0 or DEFAULT returns data for all databases on the instance.
    https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql

    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
  • GilaMonster - Tuesday, January 9, 2018 7:05 AM

    Sue_H - Tuesday, January 9, 2018 6:44 AM

    With dm_db_index_physical_stats, specifying default, null or 0 uses the current database.

    NULL, 0 or DEFAULT returns data for all databases on the instance.
    https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql

    I should have qualified - current database in that script. But it's just the join later that will limit that - as you previously mentioned.
    Sorry didn't know why you referenced that at first or how its more efficient but now I get what you were saying - thanks.

    Sue


  • Just checked it. Have mentioned on the headline 'Script to get the index scan count and Average Fragmentation(since last restart)' . But yes it should've been in other place as well. Thanks.
  • Sue_H - Tuesday, January 9, 2018 7:20 AM

    GilaMonster - Tuesday, January 9, 2018 7:05 AM

    Sue_H - Tuesday, January 9, 2018 6:44 AM

    With dm_db_index_physical_stats, specifying default, null or 0 uses the current database.

    NULL, 0 or DEFAULT returns data for all databases on the instance.
    https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql

    I should have qualified - current database in that script. But it's just the join later that will limit that - as you previously mentioned.
    Sorry didn't know why you referenced that at first or how its more efficient but now I get what you were saying - thanks.

    Sue

    Sorry, should have been clearer in the initial comment.

    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
  • GilaMonster - Tuesday, January 9, 2018 7:34 AM

    Sue_H - Tuesday, January 9, 2018 7:20 AM

    GilaMonster - Tuesday, January 9, 2018 7:05 AM

    Sue_H - Tuesday, January 9, 2018 6:44 AM

    With dm_db_index_physical_stats, specifying default, null or 0 uses the current database.

    NULL, 0 or DEFAULT returns data for all databases on the instance.
    https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql

    I should have qualified - current database in that script. But it's just the join later that will limit that - as you previously mentioned.
    Sorry didn't know why you referenced that at first or how its more efficient but now I get what you were saying - thanks.

    Sue

    Sorry, should have been clearer in the initial comment.

    Not really....duh on me. But I'm in the states, it's early and the coffee is too weak. Brain just needed to be nudged.

  • Thanks for your query. May i know margin of each column? e.g: What is the margin of Fragmentation of each index? if exists what will happen? How to resolve this?

    Manik
    You cannot get to the top by sitting on your bottom.

Viewing 15 posts - 1 through 15 (of 17 total)

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