Monitoring Physical Reads of a Query on sql 2005

I run a sql in the following form to see io performance

dbcc freeproccache
go
dbcc dropcleanbuffers
go

set statistics io on

select .....

set statistics io off

After the first attempt, I am showing on the main table (200,000 rows) 33 
physical reads.  A minute later I am running the same query, 51 physical 
reads.  As it is, this is an oltp system, and data on these tables doesn't 
get updated often.  The execution plan didn't change.

What am I missing here?

Thanks in advance
0
Utf
12/17/2009 4:01:02 PM
sqlserver.server 1327 articles. 0 followers. Follow

6 Replies
606 Views

Similar Articles

[PageSpeed] 44

Hard to say but you have easily had some page splits which would account for 
more reads. Are you sure these are not read ahead reads?

-- 

Andrew J. Kelly   SQL MVP
Solid Quality Mentors

"RG" <RG@discussions.microsoft.com> wrote in message 
news:4AA59545-B8E6-43AE-90C4-77605FECD3E2@microsoft.com...
> I run a sql in the following form to see io performance
>
> dbcc freeproccache
> go
> dbcc dropcleanbuffers
> go
>
> set statistics io on
>
> select .....
>
> set statistics io off
>
> After the first attempt, I am showing on the main table (200,000 rows) 33
> physical reads.  A minute later I am running the same query, 51 physical
> reads.  As it is, this is an oltp system, and data on these tables doesn't
> get updated often.  The execution plan didn't change.
>
> What am I missing here?
>
> Thanks in advance 

0
Andrew
12/17/2009 8:29:14 PM
It is physical reads.  Unfortunately,  I am not able to access the machine 
now.  I would have been able to post the response.

The machine running sql server is windows xp 64bit workstation.  I am the 
sole user on the machine.  In such case, could there still be page splits?

Thanks again   I
"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message 
news:%23uejSe1fKHA.5020@TK2MSFTNGP02.phx.gbl...
> Hard to say but you have easily had some page splits which would account 
> for more reads. Are you sure these are not read ahead reads?
>
> -- 
>
> Andrew J. Kelly   SQL MVP
> Solid Quality Mentors
>
> "RG" <RG@discussions.microsoft.com> wrote in message 
> news:4AA59545-B8E6-43AE-90C4-77605FECD3E2@microsoft.com...
>> I run a sql in the following form to see io performance
>>
>> dbcc freeproccache
>> go
>> dbcc dropcleanbuffers
>> go
>>
>> set statistics io on
>>
>> select .....
>>
>> set statistics io off
>>
>> After the first attempt, I am showing on the main table (200,000 rows) 33
>> physical reads.  A minute later I am running the same query, 51 physical
>> reads.  As it is, this is an oltp system, and data on these tables 
>> doesn't
>> get updated often.  The execution plan didn't change.
>>
>> What am I missing here?
>>
>> Thanks in advance
> 

0
RG
12/17/2009 9:10:36 PM
physical reads are those for which the data is pulled from disk.  pages in 
the buffer pool (from previous selects from the table) can be flushed from 
the buffer pool by sql server to make room for other pages that are read. 
This could easily lead to differing numbers of physical reads each time you 
run the query after a delay.

-- 
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"RG" <RG@discussions.microsoft.com> wrote in message 
news:4AA59545-B8E6-43AE-90C4-77605FECD3E2@microsoft.com...
>I run a sql in the following form to see io performance
>
> dbcc freeproccache
> go
> dbcc dropcleanbuffers
> go
>
> set statistics io on
>
> select .....
>
> set statistics io off
>
> After the first attempt, I am showing on the main table (200,000 rows) 33
> physical reads.  A minute later I am running the same query, 51 physical
> reads.  As it is, this is an oltp system, and data on these tables doesn't
> get updated often.  The execution plan didn't change.
>
> What am I missing here?
>
> Thanks in advance 


0
TheSQLGuru
12/17/2009 9:31:20 PM
Are you saying dbcc dropcleanbuffers doesn't flush buffer pool?

if it doesn't, is there a command that does?

Keep in mind,  1) I am sole user of the machine 2) The machine has 2gig of 
ram 3) The default memory allocation on sql server 2005 is dynamic from 0 to 
the limit or almost 2gig.

Thanks

"TheSQLGuru" <kgboles@earthlink.net> wrote in message 
news:spydnUmz2qDyP7fWnZ2dnUVZ_tydnZ2d@earthlink.com...
> physical reads are those for which the data is pulled from disk.  pages in 
> the buffer pool (from previous selects from the table) can be flushed from 
> the buffer pool by sql server to make room for other pages that are read. 
> This could easily lead to differing numbers of physical reads each time 
> you run the query after a delay.
>
> -- 
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
>
> "RG" <RG@discussions.microsoft.com> wrote in message 
> news:4AA59545-B8E6-43AE-90C4-77605FECD3E2@microsoft.com...
>>I run a sql in the following form to see io performance
>>
>> dbcc freeproccache
>> go
>> dbcc dropcleanbuffers
>> go
>>
>> set statistics io on
>>
>> select .....
>>
>> set statistics io off
>>
>> After the first attempt, I am showing on the main table (200,000 rows) 33
>> physical reads.  A minute later I am running the same query, 51 physical
>> reads.  As it is, this is an oltp system, and data on these tables 
>> doesn't
>> get updated often.  The execution plan didn't change.
>>
>> What am I missing here?
>>
>> Thanks in advance
>
> 

0
RG
12/17/2009 10:33:54 PM
You can try adding a CHECKPOINT before the DROPCLEANBUFFER command. The 
command ill not remove dirty pages and the CHECKPOINT would make sure all 
pages are clean. Still I found cases where STATISTICS IO reports fewer 
physical read than when I knew happened, and I've always concluded this ti 
be some weakness in the STATISTICS IO code. I tend to work with logical read 
and/or use Profiler to get I/O.

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"RG" <nobody@nowhere.com> wrote in message 
news:74CB61ED-55FA-4EA2-85E6-D6E227EE5D63@microsoft.com...
> Are you saying dbcc dropcleanbuffers doesn't flush buffer pool?
>
> if it doesn't, is there a command that does?
>
> Keep in mind,  1) I am sole user of the machine 2) The machine has 2gig of 
> ram 3) The default memory allocation on sql server 2005 is dynamic from 0 
> to the limit or almost 2gig.
>
> Thanks
>
> "TheSQLGuru" <kgboles@earthlink.net> wrote in message 
> news:spydnUmz2qDyP7fWnZ2dnUVZ_tydnZ2d@earthlink.com...
>> physical reads are those for which the data is pulled from disk.  pages 
>> in the buffer pool (from previous selects from the table) can be flushed 
>> from the buffer pool by sql server to make room for other pages that are 
>> read. This could easily lead to differing numbers of physical reads each 
>> time you run the query after a delay.
>>
>> -- 
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>>
>> "RG" <RG@discussions.microsoft.com> wrote in message 
>> news:4AA59545-B8E6-43AE-90C4-77605FECD3E2@microsoft.com...
>>>I run a sql in the following form to see io performance
>>>
>>> dbcc freeproccache
>>> go
>>> dbcc dropcleanbuffers
>>> go
>>>
>>> set statistics io on
>>>
>>> select .....
>>>
>>> set statistics io off
>>>
>>> After the first attempt, I am showing on the main table (200,000 rows) 
>>> 33
>>> physical reads.  A minute later I am running the same query, 51 physical
>>> reads.  As it is, this is an oltp system, and data on these tables 
>>> doesn't
>>> get updated often.  The execution plan didn't change.
>>>
>>> What am I missing here?
>>>
>>> Thanks in advance
>>
>>
> 
0
Tibor
12/18/2009 5:17:27 AM
Read ahead reads are physical reads.  If you made any updates or inserts 
into the table it can induce page splits but in any case I wouldn't waste 
too much time trying to figure it out. Without a repro its hard to say what 
was going on, the environment etc.

-- 

Andrew J. Kelly   SQL MVP
Solid Quality Mentors

"RG" <nobody@nowhere.com> wrote in message 
news:2F81EE5F-E307-4802-A361-27DC82770610@microsoft.com...
> It is physical reads.  Unfortunately,  I am not able to access the machine 
> now.  I would have been able to post the response.
>
> The machine running sql server is windows xp 64bit workstation.  I am the 
> sole user on the machine.  In such case, could there still be page splits?
>
> Thanks again   I
> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message 
> news:%23uejSe1fKHA.5020@TK2MSFTNGP02.phx.gbl...
>> Hard to say but you have easily had some page splits which would account 
>> for more reads. Are you sure these are not read ahead reads?
>>
>> -- 
>>
>> Andrew J. Kelly   SQL MVP
>> Solid Quality Mentors
>>
>> "RG" <RG@discussions.microsoft.com> wrote in message 
>> news:4AA59545-B8E6-43AE-90C4-77605FECD3E2@microsoft.com...
>>> I run a sql in the following form to see io performance
>>>
>>> dbcc freeproccache
>>> go
>>> dbcc dropcleanbuffers
>>> go
>>>
>>> set statistics io on
>>>
>>> select .....
>>>
>>> set statistics io off
>>>
>>> After the first attempt, I am showing on the main table (200,000 rows) 
>>> 33
>>> physical reads.  A minute later I am running the same query, 51 physical
>>> reads.  As it is, this is an oltp system, and data on these tables 
>>> doesn't
>>> get updated often.  The execution plan didn't change.
>>>
>>> What am I missing here?
>>>
>>> Thanks in advance
>>
> 
0
Andrew
12/18/2009 2:33:13 PM
Reply:

Similar Artilces:

SMTP Monitor
We're running SBS2003, and relaying email from several remote Java applications through our own Exchange server (SBS). I need to measure the resource strain of this, and assume I want to monitor SMTP. What's the best way to capture that info? Resource strain in terms of... ? - cpu - memory - drive i/o - volume of particular smtp traffic from/to server or recipient Perfmon and smtp logs will do it. Having a baseline will help. -- Bharat Suneja MCSE, MCT -------------------------------- "Runner" <pwright@searchsoft.net> wrote in message news:uIuvetuvFHA.2212@TK2MS...

Money 2005
Option to start statement download when the program starts does not work. I have to manually click the circle with the arrows button. What is wrong with the setup? I think this may be a WinXP issue... I was using Money 2005 with Win98SE and automatic updates worked (or at least happened) but recently upgraded to WinXP SP2 and now I too have to manually click the "Update Now" option... I have also noticed that some settings do not get saved between Money sessions... for example, the Update Status Dialog that has the "Hide this window during update" checkbox... well...

Synchronizing and Read Only
I am trying to set up a one note notebook on a desktop that I can access from a notebook computer as a synchronized one note notebook. I am using Windows 7 and have a Homegroup set up on the network. I have set up a Test notebook on the desktop and when I attempt to open it from the notebook computer through the Homegroup it always opens as read only. I have looked at the permissions of the folder on the desktop and ensured that the folder is not read only but everytime I attempt to open it it reverts back to read only (permissions show as a blue square not a check). I have also ...

Read Receipts being stored up and sent months later
We have a person outside the organization that sends email to a person in our organization and they request a read receipt. Today, the person outside the organization recived months of read receipts from the person in our organization. Outlook 2002, Exchange 2000. Any thoughts? Thank you did the person mark them all read today? Switch mailboxes? -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net ...

Read/Not read
Hello We have an exhange-server environment. The Boss' secretary need's to be able to read the Boss' mail, without the messages being marked as read, within outlook 2003. She can access the Boss mail, but all mail she reads is being marked as read, hence the boss can not figure out what he has seen/not seen. What is the solution? Thanx a lot /Jan Hi Towli. There is no way to marked as unread automatically, Just she should be tick the unread option on the pop up menu after she open the her boss e-mail. Once you right click button one of e-mail on the e-mail list, you w...

Calculate number of days query
Hi, In a table i have field "promise date" which tells my suppliers, when the orders are due to arrrive in the plant. i need to write a query which will calculate the number of days the supplier is late from the promise date. something like: current date - promise date below is the query in which i want to add the days late number: SELECT supplier, ordernumber, promiseddate, currentdate-promisedate as Ageing FROM tblPastDue; what i need from this query is to also calcuate the number of days which supplier is late on the order. please help and let me know, how can i calculate th...

read an ascii file with fopen
I try to open with fopen and read an ascii file, line by line, but get garbage - among the right data in the CString variable that is filled with this line data. Can someone copy&paste the right code how to so that? Thanks in advance. Mark "Mark" <mark@chasan.ar> wrote in message news:%23sPmEzsgGHA.2208@TK2MSFTNGP05.phx.gbl... > I try to open with fopen and read an ascii file, line by line, but get > garbage - among the right data in the CString variable that is filled with > this line data. > > Can someone copy&paste the right code how to so that?...

Importing 4.0 customizations sends SQL Server 2005 CPU to 100%, ha
Good morning all, I'm trying to import a customization xml file in a CRM 4 deployment and when I get 2/3 - 3/4 the way through the actual import, the SQL Server utilization spikes to 100% and the import never finishes. SQL doesn't come close to using all available memory, but the CPU hit keeps just about everything else from running. The CRM server running SBS03, so everything is on one box- the kicker seems to be that its a virtual machine. I can perform the import in another environment where the SQL is on a physical box. Any suggestions on settings or techniques to sweet tal...

Read Receipts #5
Does anyone know how Read Receipts get sent to email originators even though their read receipt service is NOT activated? Even more interesting is that the read receipts are ONLY generated when one particular individual reads their emails, even though the email is sent to a distribution list. For example, email is sent to a distribution list that contains over 12 email addresses. When the email is read by this particular person in the distribution list, the originator receives a receipt, but only for that particular person. No other read receipts is generated from the other mem...

Reading in a file
I'd like to slurp in a file by: 1. Obtain the filesize 2. Allocate a buffer via malloc (this is vanilla C) 3. Read the file into the newly allocated space. Here's what I did (error checking suppressed): size_t getFileSize( char *filename ) { int fd = open(filename, O_RDONLY)) == -1) struct stat fstat; stat(filename, &fstat); close(fd); return fstat.st_size; } // Get the file length xmlfile->length = getFileSize("test.xml"); // Allocate the space (+1 for a \0 at the end. see below.) xmlfile->data =...

Pasting an sql from notepad to a single cell in Excel
I need to copy and paste around 100 sql scripts from Notepad to Excel Each SQL statement should copy into a single cell. However, a simple "copy and paste" does not paste the SQL as a grouping in one excel cell but instead splits the sql into separate row eg ACTUAL RESULT ***************** EXCEL ROW 1: select c.legl_type_c, count(* EXCEL ROW 2:from ptemp.cii_cust EXCEL ROW 3:where cust_i < 4500000 EXCEL ROW 4:group by c.legl_type_c DESIRED RESULT ***************** EXCEL ROW 1: select c.legl_type_c, count(* from ptemp.cii_cust ...

Analyical services License for SQL 2000 Standard
We are on SQL Server 2000 Standard Edition. Do we need any additional license for installing SQL Analytical services for Analysis cubes or is it already included when SQL Server 2000 Standard is purchased. thank you. The SQL Server Analysis Services portion is "free" because it is part of the SQL Server 2000 product. The prebuilt GP cubes are not free and must be purchased from MBS. In GP 9, for the benefit of those who may not know, the Business Intelligence Foundation layer includes the cube for the financial modules. The distribution and multi-company cubes are extra c...

Orientation of monitor picture?
At home I'm running Windows XP Media Center with a 22" LCD from AOC and a Radeon HD 3850 or something. The cheapy HD card. Is there a way to change the orientation of things so things display vertical rather than horizontal or is that dependant on my video card or monitor to have that capability? I'm playing some pinball games that would look sweet if I could temporarily turn the monitor sideways. I looked and it should support vertical scaling, maybe I just don't know how to do it. Maybe there's some other software besides just the driver I need,...

Running Windows Media Player from MFC Application Query?
Hi all I am making an application in which i displaying in a list control all the mp3 files belonging to a folder selected by the user. I want to add the following functionality. As soon as the user double clicks on any of the displayed MP3 file, i want to invoke the windows Media Player and play that MP3 file. Now how i invoke the Windows Media Player for my MFC Application? Waiting for suggestions Regards ...

HarrisDirect and Money 2005
I imported my brokerage accounts info from HarrisDirect into Money 2005. The balances aren't even close to what they should be or what I find going directly to HarrisDirect MarketSpeed software. Anyone been successful or have similar problems? Thanks..... ...

test post do not read #2
test psst. I read it. Paul B wrote: > > test -- Dave Peterson ec35720@netscape.com I didn't -- Don Guillett SalesAid Software donaldb@281.com "Dave Peterson" <ec35720@netscape.com> wrote in message news:418979BC.2DF52A69@netscape.com... > psst. I read it. > > Paul B wrote: > > > > test > > -- > > Dave Peterson > ec35720@netscape.com ...

Scheduled Query Based Distribution Lists
I would like to make several mail distribution groups based on department, Company, and other attributes. A query based distribution list almost gets me there, but some of these lists may become very large and as the documentation tells me, it can affect server performance as query-based distribution lists are generated each time they're used. Coming from a notes environment, what we did was build all our groups nightly based on values from the address book. This was just a simple matter of making an agent that did this. Yes, the list might be a day old and not reflect people added...

why use Monitor.Enter...Monitor.Exit insted of lock(object9
Hi! If I want to lock a section in the code from concurrency item problems I can use the static Monitor class in this way Monitor:Enter .... Monitor.Exit I can also use the C# lock keyword to lock the same section. So I don't see any point at all to use the Monitor.Enter... Monitor.Exit instead of the lock keword just to lock a section in the code because of two reasons. 1. It much less to write lock then the other 2. The most importand it that you get the try..finally for free which mean that you will always free the lock in case of running into same kind of excepti...

queries written inside MS-Access are getting deleted
Some of the queries I have written inside MS-Access are getting deleted automatically. And while I run the queries through code, I get this error 'Query should have one destination field' Explanation: I created a query in MS-access. Ran it from the code. Closed the database. Started it again, and now for that particular query, it is showing 'SELECT ;' only. Strange. I am in panic mode now What can be the possible reason? My first thought is that the database file is corrupt. Unfortunately it's a type of corruption that Compact and Repair probably won...

monitor queue
Is there a way to have the exchange server send a message to the administrator when the outbound queue gets to a certain limit? Or is there a 3rd party utility that will do that? TIA ...

unable to read 2003 publication in 2002 Publisher
Since I don't see a save to 2002 I thought my 2003 publication could be read with my 2002 Publisher. Since it doesn't what next? ***************************************** Mark M Simonian MD FAAP 681 Medical Center Dr West #106 Clovis, CA 93611 (559) 325-6850 www.markmsimonian.medem.com Mark S <msimonian01@comcast.net> was very recently heard to utter: > Since I don't see a save to 2002 I thought my 2003 publication could > be read with my 2002 Publisher. Since it doesn't what next? It should be able to be read with Publisher 2002. How large is the file? -- ...

Query related to using .chm file in MFC Application?
Hi I have a demo SDI application, where i have added 2 sub menu items(Help Topic 1<ID_HELP_TOPIC1> & Help Topic 2<ID_HELP_TOPIC2>) in the Help drop down menu. Now when the user presses <Help1> button i want to open a different .htm page of my .chm help file. Similarly if the user presses <Help2> button i want to open different .htm page. To achieve this i have followed the following link: http://support.microsoft.com/kb/191118/EN-US Below is the code that is added by me in the file MainFrm.cpp static const DWORD aMenuHelpIDs[] = { ID_HELP_...

Changing font size in Reading Pane
I use the accessibility settings in IE6 on my XP SP1 box to ignore font sizes. Although that works for everything else, in Outlook 2003 the text in HTML mail is too large. Similarly the text in Help is also larger then it needs to be. Changing the text size in IE doesn't seem to do it. Turning off ignore fonts sizes does make the size of the text in the reading pane acceptable but messes me up elsewhere. I'm running 800X600. Is there a way to change the font size for the reading pane and still allow font sizes to be ignored in the accessibility settings? Thank you. Regards, Man...

Show Crosstab query on a continuous form
Hi, Is it possible to show a crosstab query on a single form (at the Detail section) and have variable (not fixed) number of Value columns to display? I know that I can insert a datasheet subform for the Crosstab query, but thought that it might load faster (and it looks better) if it were one continuous form. Thanks in advance. To do this, you would need to design a continuous form with the max number (255?) of unbound text boxes you could need. Then in the form's Open event, run a query to determine the actual fields that will be returned this time, and assign the field na...

And Or in a Parameter Query
How do you make use of AND or OR in a Parameter Query? Entering one value works fine, but using and/or retrieves an empty string. Any ideas? A parameter query uses and /or just like any other query as far as I know. You can't use and / or in the parameter section because you can't have more then one data type assigned to a field. That is the point behind a parameter query: Acccess is unable to determine what type of data is being stored/returned, Currency, Date, whatever, so you use the parameter to force it to work with that data one consistent way. However, just...