March 25, 2012 at 7:42 pm
Robert Davis (3/25/2012)
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.
Thank you Robert, for providing a clear concise answer to me.
I'm guilty of having a faulty understanding here (I've been spoilt by prior years of Oracle database fun!)
Cheers
Michael
March 25, 2012 at 7:45 pm
You're welcome, Michael. You are right that there are some very basic elements that are handled very differently between Oracle and SQL server.
March 25, 2012 at 8:02 pm
Robert,
You said in your explaination of dirty pages:
'Dirty page: A page that has changed (has transactions committed to it) and has not been written back to disk.'
Reference: http://support.microsoft.com/kb/78363
Quote: 'To improve performance, SQL Server maintains a cache of the previously read and/or modified ("dirty") pages. Periodically, these pages are physically written to the disk, ensuring that the changes to any rows are reflected in user databases.'
Point 4 states
'Log pages are flushed when a transaction ends.
When a transaction ends, either with a COMMIT or ABORT, the log pages are flushed to disk. '
Dirty pages can be as you've described and as MS describe (ie. Non-committed).
This is confirmed in another article (refer http://www.sqlservercentral.com/articles/Stairway+Series/73775/):
Quote:
'...Any page in the cache that has been modified since being read from disk so that the data value in cache is different from what's on disk is called a dirty page. These dirty pages may contain both:
* Data that has been committed and "hardened" to the transaction log file but not yet to the data file
* Data modified by open transactions i.e. those that have not yet been committed (or rolled back)...'
So my point is, I understood dirty pages to ONLY be bullet point two, you corrected me with your full run down of how the cache, dirty reads and dirty pages work - exception being that actually there are two possible ways a page is considered 'dirty'.
Just thought I'd post this for further clarity (or not potentially!).
Cheers
Michael
March 25, 2012 at 8:21 pm
I covered that. Uncommitted transactions will be locked and under READ COMMITTED isolation, your attempt to read that transaction will result in your transaction being blocked and waiting for the lock to be released. You will not be able to read the dirty transaction as you will wait until it is either committed or rolled back.
You cannot read dirty data (transaction in flight) in REAd COMMitted isolation.
March 26, 2012 at 4:09 am
eforta.it (3/25/2012)
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.
No I didn't. The second select in my example is done while the page is still dirty. Dirty = changed and not yet written back to disk, nothing more, that page is dirty until something comes along and writes it back to the data file. So in my example, an update occurs and a select occurs while the page is still dirty.
What would happen if that read came from disk where that committed update is not reflected. Simple, incorrect data.
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 26, 2012 at 7:18 am
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.
1) it is my understanding that if you alter the schema/indexes of a table the plan cache is flushed of plans that hit that object.
2) given that you still have index scans going on, either you are doing very large-rows-hitting queries or you are not optimally indexed yet. You could have non-leading-column queries that need to scan to get to the second+ columns in indexes. This could also be from not using the correct datatypes (nvarchar/varchar mismatching is very common developer mistake here).
3) It is actually quite likely that your memory needs to be increased (it is difficult to have too much).
4) How are read-aheads on the system. You could have a too-slow IO subsystem (almost every client I come across does) and it isn't getting data to RAM fast enough.
5) Consider getting a professional on board for a performance review. I suspect there will be some low-hanging fruit such a person can find to really open up your application throughput.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 26, 2012 at 9:35 am
eforta.it (3/25/2012)
Robert,You said in your explaination of dirty pages:
'Dirty page: A page that has changed (has transactions committed to it) and has not been written back to disk.'
Reference: http://support.microsoft.com/kb/78363
Quote: 'To improve performance, SQL Server maintains a cache of the previously read and/or modified ("dirty") pages. Periodically, these pages are physically written to the disk, ensuring that the changes to any rows are reflected in user databases.'
Point 4 states
'Log pages are flushed when a transaction ends.
When a transaction ends, either with a COMMIT or ABORT, the log pages are flushed to disk. '
Dirty pages can be as you've described and as MS describe (ie. Non-committed).
This is confirmed in another article (refer http://www.sqlservercentral.com/articles/Stairway+Series/73775/):
Quote:
'...Any page in the cache that has been modified since being read from disk so that the data value in cache is different from what's on disk is called a dirty page. These dirty pages may contain both:
* Data that has been committed and "hardened" to the transaction log file but not yet to the data file
* Data modified by open transactions i.e. those that have not yet been committed (or rolled back)...'
So my point is, I understood dirty pages to ONLY be bullet point two, you corrected me with your full run down of how the cache, dirty reads and dirty pages work - exception being that actually there are two possible ways a page is considered 'dirty'.
Just thought I'd post this for further clarity (or not potentially!).
Cheers
Michael
I think that you are thinking that reading from a dirty page is bad because it is called "dirty." Reading from a dirty page is still reading current data. It is not a bad thing. A "dirty Read" may be a bad thing (99 times out of 100 it is), but that is not the same as reading from a "dirty page." Reading from a "dirty page" is neither a good nor bad thing, it is the correct thing. It just means the data has not been written to disk, which you don't care about because you don't read THAT page from disk. You read it from cache. Don't confuse "dirty" with meaning "incorrect data."
Jared
CE - Microsoft
March 26, 2012 at 9:43 am
TheSQLGuru (3/26/2012)
1) it is my understanding that if you alter the schema/indexes of a table the plan cache is flushed of plans that hit that object.
If you modify the schema of a table, every plan is flushed that reference the table. However, if you add/delete/rebuild indexes, only plans that actually reference those indexes are flushed. For example, if you have an index that is not being used by an existing plan that could use it, rebuilding the index will not flush that plan. This is why we recommend calling sp_recompile on the table after making index changes. Watch any Kimbery Tripp demo on indexing and you will see her calling this every time she creates new indexes or rebuilds on a table. Same for creating user defined stats or rebuilding stats.
Just clarifying what SQL Guru said a little, not disagreeing.
March 26, 2012 at 10:02 am
Robert Davis (3/26/2012)
TheSQLGuru (3/26/2012)
1) it is my understanding that if you alter the schema/indexes of a table the plan cache is flushed of plans that hit that object.If you modify the schema of a table, every plan is flushed that reference the table. However, if you add/delete/rebuild indexes, only plans that actually reference those indexes are flushed. For example, if you have an index that is not being used by an existing plan that could use it, rebuilding the index will not flush that plan. This is why we recommend calling sp_recompile on the table after making index changes. Watch any Kimbery Tripp demo on indexing and you will see her calling this every time she creates new indexes or rebuilds on a table. Same for creating user defined stats or rebuilding stats.
Just clarifying what SQL Guru said a little, not disagreeing.
I thought it might be a bit more complex than I remembered. Thanks for the clarification Robert! Personally I always do the sp_recompile too, and now I am reminded of why! 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 26, 2012 at 10:37 am
Robert Davis (3/26/2012)
TheSQLGuru (3/26/2012)
1) it is my understanding that if you alter the schema/indexes of a table the plan cache is flushed of plans that hit that object.If you modify the schema of a table, every plan is flushed that reference the table. However, if you add/delete/rebuild indexes, only plans that actually reference those indexes are flushed.
<pedantic>
The plans aren't flushed at all (imagine a plan cache of a few GB and a single table gets altered, that would be a lot of work to find which plans are dependant on that object). What happens is that when the plan is executed it undergoes pre-execution validation and one of the things checked for is whether or not the schema of the referenced objects has changed since the plan was compiled. If any have, the plan for that statement (not the entire batch) is tossed back to the optimiser for a recompile.
http://sqlinthewild.co.za/index.php/2011/08/16/compiles-and-recompiles/
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 26, 2012 at 10:40 am
Thanks for the explanation, Gail! That makes a lot of sense.
March 26, 2012 at 10:51 am
GilaMonster (3/26/2012)
Robert Davis (3/26/2012)
TheSQLGuru (3/26/2012)
1) it is my understanding that if you alter the schema/indexes of a table the plan cache is flushed of plans that hit that object.If you modify the schema of a table, every plan is flushed that reference the table. However, if you add/delete/rebuild indexes, only plans that actually reference those indexes are flushed.
<pedantic>
The plans aren't flushed at all (imagine a plan cache of a few GB and a single table gets altered, that would be a lot of work to find which plans are dependant on that object). What happens is that when the plan is executed it undergoes pre-execution validation and one of the things checked for is whether or not the schema of the referenced objects has changed since the plan was compiled. If any have, the plan for that statement (not the entire batch) is tossed back to the optimiser for a recompile.
since plans are not flushed, we are allocating cache memory to the unused plans? does it affect the performance? does it affect number of physical reads? and is it the case we should consider flushing buffers?
March 26, 2012 at 10:55 am
Confusing Queries (3/26/2012)
since plans are not flushed, we are allocating cache memory to the unused plans?
Yes, but they'll be removed if they aren't used for a while. Also a recompile replaces the existing plan, you don't get two (or more) plans in cache for the same procedure as a result of a recompile)
does it affect the performance? does it affect number of physical reads? and is it the case we should consider flushing buffers?
Not unless taken to extremes (tens of GB of plans in cache)
No, plans in cache have nothing whatsoever to do with reads, which are fetches of data pages from either memory or disk
No, not unless you want SQL to suddenly have to work much harder than normal.
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 26, 2012 at 4:03 pm
Robert Davis (3/26/2012)
Thanks for the explanation, Gail! That makes a lot of sense.
+1. I like that much better than chewing through plans to evict!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 26, 2012 at 4:25 pm
GilaMonster (3/26/2012)
Confusing Queries (3/26/2012)
Is it the case we should consider flushing buffers?
No, not unless you want SQL to suddenly have to work much harder than normal.
so, under what circumstances people consider flushing buffers? or it is just mean to use for performance testing on development box.
Viewing 15 posts - 16 through 30 (of 45 total)
You must be logged in to reply to this topic. Login to reply