?
Solved

Low paging, but high reads from Disk

Posted on 2010-01-12
8
Medium Priority
?
348 Views
Last Modified: 2012-05-08
in a performance reading, the paging is low

Memory\Pages/sec            2.6       Low paging

but disk access is high.

Buffer Manager\Page lookups/sec      78000       Number of requests to find a page in the buffer pool.
Buffer Manager\Page reads/sec      62000       Reading pages from disk
Buffer Manager\Readahead pages/sec 61000 Number of pages read in anticipation of use.

Is this inconsistent?

Thanks
0
Comment
Question by:anushahanna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 5

Accepted Solution

by:
dbidba earned 2000 total points
ID: 26299845
They are two very different things.

Paging indicates that the operating system is moving (swapping) physical memory pages out to the page(swap) file on disk. On a database server this is a very bad thing. If Windows pages parts of the SQL Server software out to the page file on disk, performance will degrade very quickly.

The other counters refer to database pages being managed by SQL Server itself. It is designed to manage data in physical memory (buffers)(fast access) and disk pages (blocks)(slow access).  It should balance the two in the most effective manner possible. When a query you run needs a data page, it first looks for the page in the buffer cache in physical memory, and upon not finding it there will load the page from disk into the buffer cache where it may be accessed. The Least Recently Used (LRU) pages are usually the pages allowed to be flushed from cache and must be again read from disk when needed.

So low paging and high buffer reads indicates that your instance is humming along doing what it does best.  


0
 
LVL 6

Author Comment

by:anushahanna
ID: 26303757
could buffer reads (60k+) become too high, to be good?
0
 
LVL 5

Expert Comment

by:dbidba
ID: 26304265
Sure, but I don't know the bad number for your instance.

What happens once in a while is that someone launches a query which does excessive reads. Say you have a couple of tables each of which contain several million rows. Someone launches a query containing a cartesian product and SQL scans the multi-million row table multi-millions of times. You will see the CPUs peg at 100%, you will see logical reads very high, but physical reads very low. SQL is setting there repeatedly reading buffer pages, over and over and over again. Physical reads are very low because all the data needed by the query is in physical memory. The instance looks CPU bound, but it is actually IO bound. The number of buffer reads you get in that situation is near the max for your system.

This is why 90+% of database performance tuning is SQL statement tuning. The number of reads must be minimized in each query. You must identify the statements consuming the most resources and tune them. One area which can be hard to identify is update statements. I ran into a slow running update statement one time which was producing the correct results, but was updating the rows 40,000 times because of missing joins in the predicate. Once corrected, it ran in 1/40,000 of the time.

..dbi
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 6

Author Comment

by:anushahanna
ID: 26304562
so, in practice, we want only logical reads (all in physical mem), paging file reads very low, regardless if the output is 2 rows or 20k rows, right?

the only exception is when logical reads are out of control, due to a bad query (lack of enough joins)?

when you say "buffer reads you get in that situation is near the max for your system". In your experience, how high has it hit? did you literally see the cartesian product bring down the CPU and report enormous buffer reads?

thanks
0
 
LVL 5

Expert Comment

by:dbidba
ID: 26310421
anushahanna,

Please consider the script below. On a dedicated test server it produced about 320,000 buffer reads/sec at 100% CPU. It took 6 processes to max the 4 CPUs on the SQL 2008 64-bit instance.

..dbi

Memory\Pages/sec                               0
Buffer Manager\Page lookups/sec   320,000+
Buffer Manager\Page reads/sec          0
Buffer Manager\Readahead pages/sec     0
Processor\% Processor Time           100



-- ============================================================================
-- Script to stress buffer reads for an instance.
-- ============================================================================
-- ----------------------------------------------------------------------------
-- Create a large table sized to fit into memory.
-- ----------------------------------------------------------------------------
create table Tbl1 (Col1 int identity, Col2 char(8000))
go
insert into Tbl1 (Col2) values ('a')
go 50000
select count(*) from Tbl1
go

-- ----------------------------------------------------------------------------
-- Create another large table sized to fit into memory.
-- ----------------------------------------------------------------------------
create table Tbl2 (Col1 int identity, Col2 char(8000))
go
insert into Tbl2 (Col2) values ('a')
go 50000
select count(*) from Tbl2

-- ----------------------------------------------------------------------------
-- Create a small table for each CPU.
-- ----------------------------------------------------------------------------
create table Tbl3a (Col1 int identity, Col2 char(8000))
insert into Tbl3a (Col2) values ('a')
select count(*) from Tbl3a

create table Tbl3b (Col1 int identity, Col2 char(8000))
insert into Tbl3b (Col2) values ('a')
select count(*) from Tbl3b

create table Tbl3c (Col1 int identity, Col2 char(8000))
insert into Tbl3c (Col2) values ('a')
select count(*) from Tbl3c

create table Tbl3d (Col1 int identity, Col2 char(8000))
insert into Tbl3d (Col2) values ('a')
select count(*) from Tbl3d

create table Tbl3e (Col1 int identity, Col2 char(8000))
insert into Tbl3e (Col2) values ('a')
select count(*) from Tbl3e

create table Tbl3f (Col1 int identity, Col2 char(8000))
insert into Tbl3f (Col2) values ('a')
select count(*) from Tbl3f

create table Tbl3g (Col1 int identity, Col2 char(8000))
insert into Tbl3g (Col2) values ('a')
select count(*) from Tbl3g

create table Tbl3h (Col1 int identity, Col2 char(8000))
insert into Tbl3h (Col2) values ('a')
select count(*) from Tbl3h

create table Tbl3i (Col1 int identity, Col2 char(8000))
insert into Tbl3i (Col2) values ('a')
select count(*) from Tbl3i

create table Tbl3j (Col1 int identity, Col2 char(8000))
insert into Tbl3j (Col2) values ('a')
select count(*) from Tbl3j

-- ----------------------------------------------------------------------------
-- Run in session 1.
-- ----------------------------------------------------------------------------
update Tbl3a
set Col2 = T1.Col2
from
   Tbl1 T1
  ,Tbl2 T2
  ,Tbl3a T3
go 1000

-- ----------------------------------------------------------------------------
-- Run in session 2.
-- ----------------------------------------------------------------------------
update Tbl3b
set Col2 = T1.Col2
from
   Tbl1 T1
  ,Tbl2 T2
  ,Tbl3b T3
go 1000

-- ----------------------------------------------------------------------------
-- Run in session 3.
-- ----------------------------------------------------------------------------
update Tbl3c
set Col2 = T1.Col2
from
   Tbl1 T1
  ,Tbl2 T2
  ,Tbl3c T3
go 1000

-- ----------------------------------------------------------------------------
-- Run in session 4.
-- ----------------------------------------------------------------------------
update Tbl3d
set Col2 = T1.Col2
from
   Tbl1 T1
  ,Tbl2 T2
  ,Tbl3d T3
go 1000

-- ----------------------------------------------------------------------------
-- Run in session 5.
-- ----------------------------------------------------------------------------
update Tbl3e
set Col2 = T1.Col2
from
   Tbl1 T1
  ,Tbl2 T2
  ,Tbl3e T3
go 1000

-- ----------------------------------------------------------------------------
-- Run in session 6.
-- ----------------------------------------------------------------------------
update Tbl3f
set Col2 = T1.Col2
from
   Tbl1 T1
  ,Tbl2 T2
  ,Tbl3f T3
go 1000

-- ----------------------------------------------------------------------------
-- Run in session 7.
-- ----------------------------------------------------------------------------
update Tbl3g
set Col2 = T1.Col2
from
   Tbl1 T1
  ,Tbl2 T2
  ,Tbl3g T3
go 1000

-- ----------------------------------------------------------------------------
-- Run in session 8.
-- ----------------------------------------------------------------------------
update Tbl3h
set Col2 = T1.Col2
from
   Tbl1 T1
  ,Tbl2 T2
  ,Tbl3h T3
go 1000

-- ----------------------------------------------------------------------------
-- Run in session 9.
-- ----------------------------------------------------------------------------
update Tbl3i
set Col2 = T1.Col2
from
   Tbl1 T1
  ,Tbl2 T2
  ,Tbl3i T3
go 1000

-- ----------------------------------------------------------------------------
-- Run in session 10.
-- ----------------------------------------------------------------------------
update Tbl3j
set Col2 = T1.Col2
from
   Tbl1 T1
  ,Tbl2 T2
  ,Tbl3j T3
go 1000

Open in new window

0
 
LVL 5

Expert Comment

by:dbidba
ID: 26310446
Perfmon output attached.

PerfMon.bmp
0
 
LVL 6

Author Comment

by:anushahanna
ID: 26317303
a good point made.

Page reads/sec & Readahead pages/sec are 0 because it is all updates, right?

thanks
0
 
LVL 5

Expert Comment

by:dbidba
ID: 26317541
The Page reads/sec & Readahead pages/sec counters are zero because the instance is not doing any reads from disk. All the reads are within the buffer cache in physical memory. Although updates are being done, they had not committed at the time I took that screen shot, so no writes to disk were occurring.

..dbi
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question