Oracle vs SQLserver: Consistent Reads

I am in a good position given I work extensively with both Oracle and SQLserver databases to see the subtle but major differences in how certain parts of the database are implemented.

What is a consistent read?
It is the C in the ACID (Atomic,Consistent,Isolated and Durable) transaction model. It means when you read the data, your query will see a version of the data which has not changed from when you started running your query.

To provide consistent reads (or READ_COMMITTED transaction level) SQLserver uses shared locks on the data to stop any changes occurring to the data whilst a query is run. This effectively blocks any writes to the data.

***IMPORTANT*** In SQLserver 2000 and earlier, this is the main reason for explosive lock contention and table lock escalations, especially on tables having many inserts, deletes or updates happening concurrently. This type of lock and table escalation will blow away your CPU and any scalability you were hoping for.

Note: In SQLserver 2005, Microsoft have introduced READ_COMMITTED_SNAPSHOT as a database option, which will allow the database to show a version of the data (at the time of execution) without requiring shared locks, similar to the read consistent view Oracle provides with rollback/undo segments. See this part of the MSDN documentation for more information.

Oracle provide consistent reads (or read consistent view) of the data via the use of rollback or undo segments, these segments store the prior versions of the blocks (similar to pages in SQLserver) which contain the rows which are being written to. When you query the data and hit a block which has changed since you query started, Oracle will rebuild the data from the rollback or undo segment to keep the data consistent. See the Oracle Concepts manual for a detailed description of this method.

The dirty little secret (of SQLserver) is that you can do dirty or read uncommitted reads which will return data, even if it has changed since the query started. In some cases this is the only way to enable SQLserver to scale without falling over with lock contention. You use the TABLE hint (NOLOCK) or SET TRANSACTION LEVEL READ UNCOMMITTED at the beginning of your SQL code. This should be implemented only after understanding the nature of the work your application is performing against the database.

Finally SQLserver 2005 is getting closer in functionality to Oracle. It will be interesting to see the snapshots that SQLserver use to provide the versions of data are prone to problems which Oracle has had in the past i.e. the infamous "snapshot too old" error.

Have Fun

Paul

SQL Server: Remote queries on views

I spent most of Friday testing out different methods for retrieving data using remote queries with SQLserver.

Remote queries are of the form

select ... from databaseServer2.databaseName.TableOwner.TableName JOIN ...

One of the limitations of remote queries is you can't use TABLE HINTS like (NOLOCK) on remote queries. Given that this query call was for a report which doesn't require uncommitted data that is a bit of a bummer. The report aggregates some log data on a table which is getting constant inserts, so the inability to read the data whilst not effecting the constant writes was causing LOCK contention.

The first thought was the wrap the table in a view on the remote server something like this

create view uv_logtable as select from logtable (NOLOCK)

Unfortunately it would seem that remote queries and views are not a good option if you want the SQLserver execution plan to be any good. The execution plan when there is a view involved was very ordinary. Queries which had a 4 sec response time blew out to more than 4 mins, in some causes much longer. I am going to test some more and determine what the rules are.

The final thought was to take the stored procedure which runs the report and port to the remote server i.e. adding remote queries for the other data. This worked well, especially considering that most of the work was being down on the remote server in that report.
The added benefit was that I could add NOLOCK hints to the now local tables and amount of lock requests (tracked from perfmon) was stable compared to the current setup.

I will update after we go through our Monday peak period as to whether this was a success.

Have Fun

Paul

Update: Monday was fine, Tuesday was back to 100% CPU!!! Unfortunately I had missed adding a NOHINT to the local table for the report stored procedure. At one point we had 500,000+ lock requests per sec. Once I fixed the stored procedure, it took 2-3 minutes for the CPU to go back to 4-5%!
That massive CPU load caused by lock contention is a demostration of the problem SQLserver has with providing the ACID transactional model, by using shared locks to enable consistent reads. More in this post.

Personal DJ

Here is an interesting website Pandora, (ht: Steve Pavlina) which allows you to search for an artist or song title and builds a music feed based on that search. You can also guide your computer DJ by giving it feedback on whether you like to song or not. Very cool.

This service is a similar progression towards more consumer (individual) driven pulling of media content like time shifting TV, radio, movies and podcasts, with RSS and Atom providing an easy tool to see new or updated content.

Have Fun

Paul

Demographics explaining housing boom

I was looking at this article on Stephen Kirchner's economics blog, it is an interesting article from the Federal Reserve in the US about a model which uses demographics to predict housing demand and hence property booms.

Here are link to the PDF article from the Federal Reserve.

I noticed that the author of the article didn't run the model over Australia or New Zealand. It would be interesting to see what the model predicts for Australia. The other interesting country to model would be China, given the one child policy will affect China's demographics in the future.

I recommend you go read some of the Julian L. Simon's articles, this article is a good example of the idea that people are largely an untapped resource of potential, and there is also the online book "Ultimate Resource"

Have Fun

trackbacks: Fundamentals of House Price Inflation