SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get Index Scan Count


Get Index Scan Count

Author
Message
Arsh
Arsh
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5073 Visits: 998
Comments posted to this topic are about the item Get Index Scan Count
Sue_H
Sue_H
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72282 Visits: 14916


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
Arsh
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5073 Visits: 998
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.

GilaMonster
GilaMonster
SSC Guru
SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)

Group: General Forum Members
Points: 902862 Visits: 48740
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
Arsh
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5073 Visits: 998
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 .
GilaMonster
GilaMonster
SSC Guru
SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)

Group: General Forum Members
Points: 902862 Visits: 48740
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
Arsh
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5073 Visits: 998
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 ?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)

Group: General Forum Members
Points: 902862 Visits: 48740
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
Sue_H
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72282 Visits: 14916
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



GilaMonster
GilaMonster
SSC Guru
SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)

Group: General Forum Members
Points: 902862 Visits: 48740

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
Sue_H
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72282 Visits: 14916
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
Arsh
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5073 Visits: 998

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.

GilaMonster
GilaMonster
SSC Guru
SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)

Group: General Forum Members
Points: 902862 Visits: 48740
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
Sue_H
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72282 Visits: 14916
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
manik_anu
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2123 Visits: 361
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.
francesco.mantovani
francesco.mantovani
SSC-Addicted
SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)

Group: General Forum Members
Points: 419 Visits: 140
The query always returns me an error,

I try with
USE master
USE DBNAME
USE HelloWorld

(I have a database called HelloWorld) :

It always returns me
Msg 2521, Level 16, State 40, Line 3 Could not find database ID 70.

Like there was no database
Sue_H
Sue_H
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72282 Visits: 14916
francesco.mantovani - Thursday, January 11, 2018 1:02 PM
The query always returns me an error,

I try with
USE master
USE DBNAME
USE HelloWorld

(I have a database called HelloWorld) :

It always returns me
Msg 2521, Level 16, State 40, Line 3 Could not find database ID 70.

Like there was no database


That's the error I mentioned in an earlier post - if you look at the script, the database id is hard coded in dm_db_index_physical_stats. Replace 70 with DB_ID()

Sue



Arsh
Arsh
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5073 Visits: 998
My apologies ..I tried to edit it but not finding an option anywhere. please replace 70 with ur DB id or NULL or 0 to get the results for all the DB 's . Thank u
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search