Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Sunday, December 4, 2011 2:59 AM
Hi,
what does this counter represents ? does this represent number of db pages it is trying to access from memory or hard disk ? i was trying to understand if this counter gives the information on hard page fault ?
Thanks,
Sunday, December 4, 2011 10:28 PM ✅Answered | 1 vote
sp_reset_connection is called everytime a connection is reused from a connection pool.
http://support.microsoft.com/kb/310617
Linchi Shea did a lot of testing of the impact of Logins/sec years ago and blogged his results here:
Why would you expect hard page faults in Windows when SQL Server is under memory pressure? The SQLOS is its own "psuedo" operating system that provides all the memory management for SQL Server, so internal pressure in SQLOS doesn't necessitate that you have page faults occurring in Windows.
Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!
Sunday, December 4, 2011 5:21 AM
Number of physical database page reads that are issued per second. This statistic displays the total number of physical page reads across all databases. Because physical I/O is expensive, you may be able to minimize the cost, either by using a larger data cache, intelligent indexes, and more efficient queries, or by changing the database design.
Related link is given below
http://technet.microsoft.com/en-us/library/ms189628.aspx
Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer
Sunday, December 4, 2011 6:17 AM | 1 vote
This counter represents actual physical page reads from disk, associated with the SQL Server workload, it doesn't represent hard page faults as the Memory\Pages/sec counter does. This counter will account for the additional read-ahead reads that cause physical I/O that is not accounted for in the SQLServer:Buffer Manager\Buffer Cache Hit Ratio counter which is useless on systems where the read-ahead mechanism can properly populate the buffer pool with database pages before they are actually required. The best description of this counter comes from the Performance Analysis of Logs Tool, rather than the previously referenced BOL Topic.
SQLServer:Buffer Manager Page reads/sec
*Description: SQLServer_Buffer Manager Page Reads_sec *
*Description: Number of physical database page reads issued per second. Number of physical database page reads issued. 80 – 90 per second is normal, anything that is above indicates indexing or memory constraint. *
*Threshold: *
*Informational: Page Reads/sec > 90 *
*Next Steps: Attempt to tune the application so that fewer I/O operations are required. For example, perhaps I/O would be reduced if there were appropriate indexes or if the database design were denormalized. If the applications cannot be tuned, you will need to acquire disk devices with more capacity. Compare to the Memory: Pages/sec counter to see if there is paging while the SQL Server:Buffer Manager\Page reads/sec is high. Note: Before adjusting the fill factor, at a database level compare the SQL Server:Buffer Manager\Page reads/sec counter to the SQL Server:Buffer Manager\Page writes/sec counter, and use the fill factor option only if writes are a substantial fraction of reads (greater than 30 percent). *
*Reference: *
*SQL Server, Buffer Manager Object *
Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!
Sunday, December 4, 2011 4:28 PM
Thanks Jonathan!! As always your answer is very explanatory.
I understood what SQL Server Buffer Manager Page Reads Per Sec and also the difference of SQL Server Buffer Manager Page Reads Per Sec and Memory Pages Per Sec from your answer.
This is what is confusing me now :
32 bit SQL Server with AWE not enabled. 4 GB of RAM.
Memory Pages/sec = 0, Pages Input,Output/sec = 0.
Checkpoint pages per second : 0
Lazy writes per second : 0
But Page Life Expectancy is just around 100 !!
Total Pages = Target Pages = 204800
**
Stolen Pages = 105000**
Database Pages: 107389 (around 850 mb)
SQL Server Buffer Manager Pages Reads per sec is around 1000 and writes per sec is around 150.
Avg Disk Queue Length and % Disk Time both is high.
Whole system has slowed down.............
%Processor time > 90%
General Stats :
**Active temp tables : 300 (stays there forever)
Logins/sec : goes up to 1500
Logouts/sec : stays mostly 0
**Userconnection : 80 (stays there)
When I look into the Server Side trace, it shows that I have exec sp_reset_connection before every stored proc execution.
Doesnt this mean the connection pooling is being done, which is a good thing ? Then why Logins/sec is high ? Is it causing the CPU to go high ? If this is a problem how can I solve this ?
Doesnt this show we are likely to have memory constraint and have hard page fault ? If yes, then why am I seeing pages/sec =0 ? If no, then why the page life expectancy is too less ?
As per SAN's Administrator there is no pressure in SAN's side. As per the page fault counter there is no memory pressure. But the CPU usage is very high. Alot of soft page fault. Can I say it is just CPU's bottleneck ? If I can then it is due to Logins/sec I am seeing. But while looking at the trace I can see alot of sp_reset_connection ............ just confused......
What do you think is the main problem here ? How can I find what is actually causing the whole issue ?
Thanks alot for taking time to read the whole content!!!
Sunday, December 4, 2011 5:24 PM | 1 vote
It sounds to me like you have missing indexes, that are resulting in table scans that are driving up CPU usage and I/O. The problem definitely isn't connection pooling, it looks to be entirely workload and memory pressure related from your counter information. Download a copy of my book, Troubleshooting SQL Server: A Guide for Accidental DBAs, and look over the CPU and Missing Index chapters. That is the best thing I could recommend at this point.
Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!
Sunday, December 4, 2011 9:38 PM
Thanks alot Jonathan for your opinion and the Book!!!!
It makes sense because when I looked into Full Scans/sec, it is always more than 1000. But since you said the system looks to have memory pressure I have a question.
If the counters are showing memory pressure, why the hard page fault is 0 ?
Also, I can see thounsands and thousands of exec sp_reset_connection, wont it degrade the CPU performance ?
Isnt high Logins/sec bad for sever performance ?
I really appreciate your answers!!!!
Thanks again!!