March 27, 2011 at 10:10 am
Iam playing with SQL server 2008 (SP2) Developer edition now and I have following issue:
In test db I have test table with one record.
set statistics io on;
select * from test;
the result is:
test
-----------
1
(1 row(s) affected)
Table 'test'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Now let's clean buffer pool:
checkpoint;
dbcc dropcleanbuffers;
And do select againg:
select * from test;
Result:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
test
-----------
1
(1 row(s) affected)
Table 'test'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
You can see physical reads is 0.
My question is why? When the bufferpool is clean there must be physical reads!
Thank you for comments
Luigi
March 27, 2011 at 10:25 am
your whole object already resides in memory, so no physical reads needed.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 27, 2011 at 1:23 pm
Hi, thank you for comment,
but it is not clear for me.
I did dbcc dropcleanbuffers;
and after that the data won't be in the buffer pool?
March 27, 2011 at 2:08 pm
Edit: nevermind...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 27, 2011 at 2:40 pm
What's the size of your database ?
What's the data size in your database ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 27, 2011 at 3:07 pm
Test db only.
With one table where is just one record.
create table test (test int);
go
insert test select 1;
My question is:
How to completely clear buffer pool (same state as when sql server is restarted)? After that must be any select from db done by physical reads. Can I simulate that using DBCC utility? I thought that size of db is not important in this case.
March 27, 2011 at 11:40 pm
As its one Db with one table, remove Physical RAM from your machine and put new RAM. You will see physical read for which you are so curious to see 😀 😀 😀
----------
Ashish
March 28, 2011 at 12:34 am
Libor Polansky (3/27/2011)
Test db only.With one table where is just one record.
create table test (test int);
go
insert test select 1;
My question is:
How to completely clear buffer pool (same state as when sql server is restarted)? After that must be any select from db done by physical reads. Can I simulate that using DBCC utility? I thought that size of db is not important in this case.
It's just to small.
Even if you put that db offline and bring it back online, your query may still show up using no phisical reads, altough it had to open the db.
IMO it just read (the little volume) the data with the same io block to fetch the db system info.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply