Pages Read & Written by a single query

Question

Pages Read & Written by a single query

r5d4

SSCrazy

Points: 2519
More actions
December 6, 2016 at 6:06 am

#332603

I'm working in an environment where some very wide tables are present (400+ columns).
Whilst long term I will be restructuring these and applying some normalization, I need to do some education of the user base first.
What I am after is how (preferably with TSQL) I can show how many pages are accessed by individual queries.
At the end of the day I want to say query X read 20 pages, query Y wrote to 200 pages, query Z read 40 and wrote to 10.
I'm open to any suggestions how to achieve this.
Thank you.

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

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

You can use the profiler to show that. Another option is to use the extended events. You can see the amount of pages that the query read with with the SSMS (but how many many were written) if you use set statistics io on in your session.

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/

sgmunson SSC Guru Points: 110639 More actions · Answer 2

r5d4 (12/6/2016)
I'm working in an environment where some very wide tables are present (400+ columns).
Whilst long term I will be restructuring these and applying some normalization, I need to do some education of the user base first.
What I am after is how (preferably with TSQL) I can show how many pages are accessed by individual queries.
At the end of the day I want to say query X read 20 pages, query Y wrote to 200 pages, query Z read 40 and wrote to 10.
I'm open to any suggestions how to achieve this.
Thank you.

I'm not sure that educating users about what they may perceive as a rather arcane technical detail associated with databases is necessarily the right approach. Just using SET STATISTICS IO ON; is sufficient to see how many logical and physical reads and scans take place, which might be more representative of resource consumption than the number of pages accessed.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

drew.allen SSC Guru Points: 76944 More actions · Answer 3

Adi Cohn-120898 (12/6/2016)
You can use the profiler to show that. Another option is to use the extended events. You can see the amount of pages that the query read with with the SSMS (but how many many were written) if you use set statistics io on in your session.
Adi

Using Profiler for traces has been deprecated. You should be using extended events instead.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

sgmunson (12/6/2016)
r5d4 (12/6/2016)
I'm working in an environment where some very wide tables are present (400+ columns).
Whilst long term I will be restructuring these and applying some normalization, I need to do some education of the user base first.
What I am after is how (preferably with TSQL) I can show how many pages are accessed by individual queries.
At the end of the day I want to say query X read 20 pages, query Y wrote to 200 pages, query Z read 40 and wrote to 10.
I'm open to any suggestions how to achieve this.
Thank you.
I'm not sure that educating users about what they may perceive as a rather arcane technical detail associated with databases is necessarily the right approach. Just using SET STATISTICS IO ON; is sufficient to see how many logical and physical reads and scans take place, which might be more representative of resource consumption than the number of pages accessed.

Be VERY careful about using SET STATISTICS if Scalar or mTVFs are being used. See the following article for why.

http://www.sqlservercentral.com/articles/T-SQL/91724/

--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)

sgmunson SSC Guru Points: 110639 More actions · Answer 5

Thanks Jeff for that rather good article. I learn something new every day, and love the learning!

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)