Data Pages per user

Question

Data Pages per user

darth.pathos

Ten Centuries

Points: 1084
More actions
June 8, 2009 at 6:44 am

#131034

Hi all,
I'm using a utility called SQLSpy to monitor my SQL 2000 database activity; one of the things I've noticed is that the number of pages accessed per user seems to be ridiculously high (right now, 10% of my users that are online are accessing more than 300 pages...). I understand (I think) the basic concept of datapages (8kb per page, etc.) but how many pages would be deemed "acceptable"? Does more pages lead to performance problems as I'm assuming? Or is this in fact nothing to worry about?
TIA. I am going to get more coffee.
Chris

Viewing 9 posts - 1 through 8 (of 8 total)

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

joeroshan SSChampion Points: 10377 More actions · Answer 1

There is no acceptable number of pages as such. It depends on the environment you have.

SQL server loads a page into memory when its first accessed. Subsequent requests will be serviced from memory. It will write the pages back to disk when it needs memory or according to recovery interval.

Check for 'page life expectancy counter' for memory bottlenecks

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

darth.pathos Ten Centuries Points: 1084 More actions · Answer 2

thanks Joeroshan for the quick reply. I now at least have a direction to go in. Have a great day and happy SQLing!

Chris

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

What you can do is use profiler (or a server-side trace) to see what the IOs are like for frequently run queries. High IOs may indicate inefficient queries or a lack of good indexes.

This might help. http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

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

darth.pathos Ten Centuries Points: 1084 More actions · Answer 4

Hi Gail - Thanks for your reply - I actually have your article printed and in my Binder of SQL Stuff that sits on my desk; it's well-referenced, let me tell you! I will go back and focus my attention on that particular aspect of it, but was looking for more in-depth (ie. Geeky ;-)) information about them.

On that note, I'm currently watching my Page Life Expectancy on PerfMon - it just went from 1471 to 0 in less than 2 seconds (it's almost a perfectly vertical drop). Is this normal? I understand between 300 and 400 hundred seconds is what's expected - do I have a memory issue with my server or something else that's going on?

Thanks.....

Chris

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

darth.pathos (6/8/2009)
but was looking for more in-depth (ie. Geeky ;-)) information about them.

Not sure what you're looking for. What's normal for pages/sec depends greatly on the app. A datawarehouse where the users are doing massive queries will have a very different profile from an OLTP system. Hence there's no value that can be considered acceptable. It's mostly one of those values where you worry when it deviates from the baseline you have established.

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

joeroshan SSChampion Points: 10377 More actions · Answer 6

I think what Gail said is correct. You have to use Profiler to identify which all queries are I/O intensive.

Meanwhile if you have page life expectancy going down for too often, it means the pages are written back to disk very often. 300 denotes a page is expected to remain in memory for 5 minutes.

Which edition you are having on your box? Have you tried enabling AWE. I believe in 2000 you need enterprise edition for that

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 7

The number of pages being accessed are physical read and logical read combined .You should find the queries being fired (using profiler).

Once you have the queries run them individually and see how much pages does each query touches by using SET STATISTICS IO ON .

.

for any fresh demand OS generally gives the pages in 64 KB chunk .So , at one time SQL will be granted Eight 8kb pages in case new pages are requested .this can also be due to hard page fault or totally new pages .

HTH

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

darth.pathos Ten Centuries Points: 1084 More actions · Answer 8

well this certainly gives me a lot to play with. I'm going to hopefully have a chance this aft to go through and try out these ideas; I'm very curious to see what my database will turn up. thanks again guys, I really appreciate your time! 😀

Chris