not getting rid of physical reads
-
We did not have proper indexes on production and almost all the queries were using clustered index scan having high physical reads.
Now we have created new indexes specially dedicated to the queries we run against the production and deleted the old ones, we have index seek and some nonclustered index scan but, still we have the physical reads. Is it because buffer is full of the old plans with clustered index ones. Should I consider using DBCC DROPCLEANBUFFERS? Or the server is memory bound.
-
Confusing Queries (3/23/2012)
We did not have proper indexes on production and almost all the queries were using clustered index scan having high physical reads.Now we have created new indexes specially dedicated to the queries we run against the production and deleted the old ones, we have index seek and some nonclustered index scan but, still we have the physical reads. Is it because buffer is full of the old plans with clustered index ones. Should I consider using DBCC DROPCLEANBUFFERS? Or the server is memory bound.
If the indexes have already cluster indexes, you should realize that whenever you use the non-cluster indexes the as well use the cluster indexes. check this link.
-
Unless the server has memory space > database size, there will always be physical reads. Physical read just means that the required page was not in the data cache.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Confusing Queries (3/23/2012)
We did not have proper indexes on production and almost all the queries were using clustered index scan having high physical reads.Now we have created new indexes specially dedicated to the queries we run against the production and deleted the old ones, we have index seek and some nonclustered index scan but, still we have the physical reads. Is it because buffer is full of the old plans with clustered index ones. Should I consider using DBCC DROPCLEANBUFFERS? Or the server is memory bound.
Well... if you dropped the old indexes I'm pretty sure SQL Server is not using them - have you traced the query that hurts you the more so to check what is actually doing?
Chances are the new indexes are not working as well as expected.
_____________________________________
Pablo (Paul) BerzukovAuthor of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice. -
Hi there
Can you give a bit more information about the table and query...
a) How many rows in your data sets?
b) What is the query?
c) How many rows do you expect?
d) Parse stats for the queries please.
e) Define 'high physical reads' in context of what's happening.
You'll always have physical reads due to rows in cache changing from buffer writes and changes. It's important to realise that if you have a high changing OLTP database, it's likely you'll have high physical reads as the cache will contain old physical block info. Be careful about adding too many indexes, particular if they rarely get used.... you'll end up writing a lot to your database for no benefit... but I'm going off topic with this.
More info please 🙂
Cheers
Michael
-
eforta.it (3/25/2012)
It's important to realise that if you have a high changing OLTP database, it's likely you'll have high physical reads as the cache will contain old physical block info.Huh, what?
The cache can never contain 'old' information, writes are first to cache and later to disk.
Or did you mean something else?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (3/25/2012)
eforta.it (3/25/2012)
It's important to realise that if you have a high changing OLTP database, it's likely you'll have high physical reads as the cache will contain old physical block info.Huh, what?
The cache can never contain 'old' information, writes are first to cache and later to disk.
Or did you mean something else?
Sorry, Gail - I am talking about where in a high select environment, your buffer cache (the database blocks which are from other SELECTs) will contain aged cache blocks (due to the original SELECT blocks in the cache having been changed by other UPDATEs and INSERTs)....
Am I coming through OK? Even though I'm English - my written English can be as confusing as a weasel's dinner 🙂
-
The proper term (I've been googling :)) is 'Dirty Page Latency'...!
Thanks
-
eforta.it (3/25/2012)
Sorry, Gail - I am talking about where in a high select environment, your buffer cache (the database blocks which are from other SELECTs) will contain aged cache blocks (due to the original SELECT blocks in the cache having been changed by other UPDATEs and INSERTs)....The buffer cache only contains a single copy of any particular database page. It's used for selects and for data modifications (insert, update, delete). There won't be a 'new' cache page read from disk and 'aged' pages that have been modified (that could cause nasty lost update scenarios)
The way SQL's data cache works is that when a page is needed (for any operation) and it's not in the data cache, it will be fetched in. That mage will be read from and modified as various operations affect it, and if modified the changes will be later written back into the data file, but the changed page remains in cache until no longer needed and all operations are done against it.
That's why SQL can write database changes at later points after a modification has completed, because as long as the page in cache has been modified and the log records written to disk, the change is considered persistent and complete.
The term "Dirty Page Latency" is the interval between when the page is modified and when it's written back to disk. Note that writing to disk does not remove the page from the data cache.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
If you have a dirty page in memory and another user does a SELECT that would pull the same page - does it take from the disk or the buffer cache?
From a read consistency point of view, it would take it from disk - would it not?
That was my understanding.....I'm happy to be re-educated in this space if I'm wrong as it's important to me that I fully understand these indepth mechanics 🙂
Cheers
Michael
-
eforta.it (3/25/2012)
If you have a dirty page in memory and another user does a SELECT that would pull the same page - does it take from the disk or the buffer cache?The buffer cache. Has to be, the page on disk is potentially got an old copy of the data on it, if the read was from disk then you could be reading a page that does not reflect a committed transaction. That would be a very bad thing
From a read consistency point of view, it would take it from disk - would it not?
No, exactly the opposite. The data on disk is not considered to be consistent unless the database is cleanly shut down or completely quiesced.
Consider this timeline of a particular page (let's call it page 137)
t1 - page read from disk (query requested it and it wasn't in cache) and select reads page
t2 - transaction begins (Tran1)
t3 - transaction Tran1 adds a record on that page
t4 - transaction committed (Tran1)
t5 - select reads page
t6 - changes written back to disk by the checkpoint process.
What would happen if the read at t5 was from disk where the page does not reflect the changes done by the committed transaction Tran1?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
The default isolation level in SQL Server is Read Committed.
Please refer to here: http://msdn.microsoft.com/en-us/library/ms189122.aspx
Mid way down - there is a table which shows whether or not dirty reads, nonrepeatable reads and phantom transactions occur.
Read Committed mode doesn't allow dirty reads. So my understanding that when a dirty page is in memory (cache) and another user performs a select, it takes it from disk - as the page is dirty in memory and is only marked clean once checkpoint'd or committed.
I'll take a look at your example later and answer - but unless I'm missing something here, your posts are referring to a different mode of isolation?
Cheers
Michael
-
I think we're crossing wires here...
My example was Update has occurred and then select whilst update page still dirty.
You're taking my example and reversing it round to: Select and then update, then select.
Cheers
Michael
-
I'll make this simple. ALL READS COME FROM CACHE.
SQL operates with pages in cache only. It requests pages from cache. If the page doesn't exist in cache, a separate mechanism behind the scenes fetches the page from disk and loads it into cache. When SQL writes data to a page, it writes it to the SQL log and to the cache. At some point, the page will get written back to disk, but we don't care because we only use it from cache.
I think you are confused by the use of "dirty" to mean different things. Dirty read <> dirty page.
Dirty read: A dirty read is reading an uncommitted transaction.
Dirty page: A page that has changed (has transactions committed to it) and has not been written back to disk.
A dirty page is not a page with uncommitted transactions on it. A dirty page has committed transactions on it and the page on disk is older than the page in cache.
If you try to read a record that has an uncommitted transaction in process, you get a block because the transaction updating the row locks the row before it updates it. In READ COMMITTED isolation, your read operation will wait for the update to be committed (or rolled back) and the lock released.
It WILL NOT read a different version of the page in READ COMMITTED isolation. It will wait until it can get the locks required to perform the read from cache.
While I'm defining things, let me define this as well:
Committed transaction: The transaction has been written to the log file and to the page in cache.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Getting back to the original question. Having physical reads is normal in most situations, like Gail said. Take a look at your page life expectancy. This tells you on average how long a page is staying in memory. Theere is no single value for an ideal PLE but in general, the higher it is, the more you are reading data directly from cache. for normal use, you need to baseline the value so you can see how it trends over time. But it is useful even now for troubleshooting an issue,
So what is your PLE?
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]
Viewing 15 posts - 1 through 15 (of 45 total)
You must be logged in to reply to this topic. Login to reply