November 27, 2007 at 2:10 pm
I have a production server and test server that have the same database (the test server has a copy of the DB back when we went live in October). Both databases are running at about 10900 page lookups per second and the test server is 90% idle and not used - I've looked at several counters and they all seem to be normal with exception to this one and I've read that you should compare it with batch requests per second and that number is around 1. (.909)
Does this seem high, how do I begin to troubleshoot this?
thank you for any advice you might be able to share.
November 27, 2007 at 4:15 pm
There is nothing wrong with page lookups per second being high. All that counter really means is that SQL Server is accessing pages that are already in memory. It is not unusual to see 150,000 pages/sec or more when you are scanning a large table.
November 27, 2007 at 4:20 pm
Are you having performance problems on your production DB? What has prompted you to begin looking at performance metrics on these 2 servers?
November 27, 2007 at 4:21 pm
Thanks. How does the Batch requests correlate to Page lookups?
I'm trying to troubleshoot a complaint from the users that run an application that sometimes takes minutes to view results. The vendor who created the application and database has been no help so I've been trying to troubleshoot it myself. The workstations are double their requirements & are working fine, the network response is great, the server performance is terrific, I'm not sure where to go from here.
Any suggestions?
Thanks.
November 27, 2007 at 4:31 pm
I should add that the performance has always been terrible and I know other companies have had performance issues with this vendor's app/db as well. When this database was on SQL 2000 & W2K it also performed terrible.
It's about a 9 GB DB running on SQL 2005 sp2 and Windows 2003 sp1 on an HP DL380 Dual Processor 3.20 GHz with 4GB of RAM, it's SAN attached.
Thanks for any help.
November 28, 2007 at 4:33 am
Have you run Profiler to identify the poor performing processes? If not, I'd do that right away. You say it's a third party vendor. Once you've identified badly behaved procedures, missing indexes, whatever, can you make changes or are you just stuck?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 28, 2007 at 8:12 am
I second Grant here. You should run profiler to identify poorly performing processes. I would also recommend running Windows Performance Monitor along with Profiler. Take a close look at your disk configuration before you go pointing the finger at the vendor. SANs are notoriously poor performers for DB servers when improperly configured.
November 28, 2007 at 10:52 am
Thank you both. I need help with understanding the profiler, is there a good document out there on the profiler?
I have been using perfmon and here are some of the statistics I have collected so far if this will help you see what I'm seeing:
CPU
%Privileged Time = 20% (Below 80% is recommended)
%Processor Time = 30% (Below 80% is recommended)
Physical Disk
Avg Disk Sec /Read = 0 (Should be less than or equal to 8ms)
Avg Disk Sec/Write = .007 (Should be less than or equal to 8ms) ( I did see a Max of .649 once and this monitor is on the SAN drive)
% Idle time 73% (should be greater than 50%)
Memory
Avail Mbytes = 1014MB (should be greater than 100MB, 4MB is critical)
Free System Page Table Entries = 185798 (Critical when values fall below 5000)
Paging File Counters
% Usage = 3.95 (recommended below 70%)
%Usage Peak = 5.95 (recommended below 70%)
Process (sqlservr) counters
%Privileged Time .110 (less than 30% of Processor Time)
%Processor Time 8.46 (less than 80%, divide by number of processors)
SQL Access Methods Counters
Forwarded Records/sec = (less than 10%) 14 but I saw Max jump to 1445.77 once)
Full Scan /sec = 3.147 (lower the better, high numbers could mean missing indexes or large amount of rows being requested)
Index searches/sec Avg 10 Max 504 (eValuate the ratio of full scans/sec to index searches/sec 1000 indexes to 1 full scan common can be less)
Page Splits/sec .010 (high page splits can be the result of table design and fill factors compared with the number of batch requests/sec)
Batch requests/sec 4.657
Scanpoint revalidation/sec .018 (Can indicate Hot Spots in data)
Range scans 6.394 (Number of qualified range scans through indexes per second)
SQL Server Buffer Manager counters
Buffer Cache Hit Ratio 98% (The higher the better)
Page Life Expectancy 6323 (Should greater than 300)
Checkpoint Pages/sec 0 (less than 300)
Lazy Writes/sec 0 (As close to 0 as possible, anything above 20 will indicate memory pressure)
Page lookups = 11932 (Number of requests to find a page in the buffer pool, check for missing updates, can indicate inefficient query plans, compare to the number of batch requests per second. )
Free Pages 2543 but dropped to 111 while watching for 5 mins for avg and minimum was 23 (Investigate when less than 640, look for significant drops)
SQL Server Memory Manager
Total Server Memory KB 1620224
Target Server Memory KB 1620224
Memory Grants Pending 0
Memory Grants outstanding 0(should be 0)
SQL Server Latch counters
Latch Waits/sec 24.971
Total Latch Wait time .717
(Total latch wait time divided by latch waits/sec should less than 10)
SQL Server Locks Counters
Avg wait time (ms) 0
Lock wait time (ms) 0
(Look for Peaks greater than 60000 ms)
Lock Waits/sec 0
Lock Timout 0 (timeout greater than 0)
SQL Server Statistics
Batch requests/sec 9.384
Compilations/sec 4.010
Recompliations/sec 0
SQl Server General Statistics
Lock waits 0
Log write waits 0
Log buffer to be written 0
network IO waits 0
Wait for Worker 0
Non-page latch waits 0
Page latch waits 0
Page IO latch waits 0
November 28, 2007 at 12:30 pm
BOL should give you most of what you need.
I strongly recommend using the TSQL commands to run the profiler job, not the gui. I also recommend placing the output to a file, not a table. You can import it later.
I wrote an article on the basics for monitoring performance that includes a bunch of this stuff. I published that one over at Simple-Talk [/url](sorry Steve).
Do some searches in the articles here for more information.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 6, 2011 at 3:44 pm
Ratio of Batch requests to Page Lookups should not be much greater than 100. If it is, it indicates too much logical I/O because of bad query plans
January 7, 2011 at 7:35 am
mcroce (1/6/2011)
Ratio of Batch requests to Page Lookups should not be much greater than 100. If it is, it indicates too much logical I/O because of bad query plans
which are often caused by missing indexes...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply