Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Low paging, but high reads from Disk

Posted on 2010-01-12
8
Medium Priority
?
350 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
  • 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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Loops Section Overview
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

577 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