Low paging, but high reads from Disk

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
LVL 6
anushahannaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dbidbaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anushahannaAuthor Commented:
could buffer reads (60k+) become too high, to be good?
0
dbidbaCommented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

anushahannaAuthor Commented:
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
dbidbaCommented:
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
dbidbaCommented:
Perfmon output attached.

PerfMon.bmp
0
anushahannaAuthor Commented:
a good point made.

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

thanks
0
dbidbaCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.