Get Index Scan Count

Question

Get Index Scan Count

Arsh

SSCertifiable

Points: 6354
More actions
January 8, 2018 at 1:06 pm

#341634

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

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

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

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

Arsh SSCertifiable Points: 6354 More actions · Answer 2

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

Arsh SSCertifiable Points: 6354 More actions · Answer 4

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 .

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

Arsh SSCertifiable Points: 6354 More actions · Answer 6

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 ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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

Sue_H SSC Guru Points: 90860 More actions · Answer 8

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

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

Sue_H SSC Guru Points: 90860 More actions · Answer 10

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

Arsh SSCertifiable Points: 6354 More actions · Answer 11

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

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

Sue_H SSC Guru Points: 90860 More actions · Answer 13

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.

manik_anu SSCrazy Points: 2377 More actions · Answer 14

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.