SQLblog.com - The SQL Server blog spot on the web

May 2013 Cumulative Update for SQL Server 2008 SP3

AaronBertrand — Tue, 21 May 2013 12:14:00 GMT

Microsoft has released SQL Server 2008 Service Pack 3 Cumulative Update #11 Build # 10.00.5840 KB Article: KB #2834048 6 fixes Relevant for builds 10.00.5500 -> 10.00.5839 NOT for SQL Server 2008 R2 (10.50.xxxx)...(read more)

Huge news: Azure expanding to Australia!!!

Greg Low — Mon, 20 May 2013 22:31:00 GMT

I was so glad to hear today that Azure is expanding to Australia. This helps with two remaining areas of concern that I've heard from a variety of customers:

Compliance and data sovereignty (not wanting to store data outside Australia)
Latency (previously high latency even to our nearest external data centres)

With both these concerns now disappearing, it's time for more Australian customers to get involved with Azure if they've been resisting so far.

Two Azure sub-regions are to be added. One for New South Wales and another for Victoria. In addition, data geo-replication between the sub-regions will also be available.

AdventureWorks on Azure now hosted by Red Gate software

jamiet — Mon, 20 May 2013 21:48:39 GMT

In March 2013 I launched an initiative called AdventureWorks on Azure in which I hosted the AdventureWorks2012 sample database on Azure so that the SQL Server community at large could use it to get familiar with what ~~SQL Azure~~ Windows Azure SQL Database had to offer. I asked the SQL community to support the initiative by donating money to keep it afloat and that community responded in spades to the point where last month I was able to make a donation to the War Child charity on behalf of that community. In the interim period I added more sample data to the pot, made Northwind available, and also data related to Olympics medal tables down the years.

Today AdventureWorks on Azure takes on a new guise, Steve Jones of SQL Server Central fame announced via a press release that hosting of the database is now to be taken on by his employer Red Gate Software. Here’s what Steve had to say in today’s announcement:

One of the goals of Red Gate Software is that we not only sell products to the SQL Server community, but that we also participate in the community. In line with our goals of participating in the community, we have agreed to host the AdventureWorks 2012 sample database on the Azure platform.
Red Gate has worked to transition this set of databases to our own subscription and will assume the financial responsibility for making this available for the community to use.
http://www.sqlservercentral.com/articles/Adventureworks/99290/

This is great news. I no longer have to worry about finding the funds to keep the initiative afloat and the community can feel comfortable that this will be around for the foreseeable future, so thank you to Steve and red gate for this undertaking. Special thanks must also go to David Atkinson at Red Gate who first suggested that they take this on.

I have high hopes that Steve & Red Gate will build upon this with more offerings for the community and equally I also hope I can stay involved somehow. Watch this space!

@Jamiet

How Far Can You Push Tabular? Birds of a Feather at TechEd 2013–North America #msteched #ssas #tabular

Marco Russo (SQLBI) — Mon, 20 May 2013 13:15:00 GMT

I and Alberto Ferrari will moderate the following Birds of a Feather session at TechEd North America 2013:

CODE: BOF-ITP21
TITLE: How Far Can You Push Tabular?
SPEAKER: A. Ferrari; M. Russo
TIMESLOT: June 5, 2013 at 1:30 pm
ROOM: 263

Abstract: Tabular is the new engine in SQL Server Analysis Services. It is an in-memory columnar database capable of unprecedented performance but… how far can we go with an in-memory database? Will you be able to load your data warehouse in-memory to obtain the best performance? What should you care about when building the DWH? What does “fast” mean? What about real-time data? Can you query many billions rows really fast? In this BOF we will show some theory and share some of our experience, but we will welcome your questions and experience, to build a round-table of experts in order to speak about this fascinating topic.

I really look forward to this event – I and Alberto have some experience using Tabular in many different scenarios, but every time we meet new people at conferences we discover that there are new challenges ahead. This session will be the place to discuss together where are the limits today. Much far than many people realize, in my opinion!

Why We Write #5–An Interview With Jason Strate

drsql — Mon, 20 May 2013 02:40:59 GMT

My next guest is a person I have known for years, and have worked with on several occasions, Jason Strate ( @stratesql ). Jason is a very active writer and speaker (at my first SQL Saturday event, he spoke four times!), and always seems very busy. His blog ( jasonstrate.com ) had 23 posts just last month, and his twitter account is always active with interesting SQL and non-SQL tweets. If the blogs and tweets weren't enough, just this past year, Jason was a coauthor on one book last year ( Expert...(read more)

SQL Saturday #220 (Atlanta): Demos

Adam Machanic — Sat, 18 May 2013 18:33:00 GMT

Today at SQL Saturday #220 in Atlanta I presented a new brand new session , "SQL Server Query Plan Analysis: The 5 Culprits That Cause 95% of Your Performance Headaches." This session is designed to help people quickly analyze query plans and find likely culprits without being query tuning experts; I find that in a huge number of cases the root cause of problems is one of just a few potential situations. Thanks to everyone who joined me today for the deliveries! In addition to it being a new session...(read more)

SQL in the City Event Dates Announced!

andyleonard — Sat, 18 May 2013 06:00:00 GMT

Red Gate rocks. If you didn’t know that already, you know it now. The latest evidence to support this claim is the publication of the schedule for US SQL in the City events. They are: 9 Oct – Pasadena 11 Oct – Atlanta 14 Oct – Charlotte Registration for these events opens 24 May. :{>...(read more)

Announcing the 2013 Biml Workshop 15 Oct 2013 in Charlotte NC!

andyleonard — Sat, 18 May 2013 04:00:00 GMT

When : October 15, 2013 8:45 am - 4:45 pm Charlotte, NC Where : Wake Forest University Charlotte Center 200 North College Street Charlotte, NC 28202 What : Business Intelligence Markup Language (Biml) automates your BI patterns and eliminates the manual repetition that consumes most of your time. Come see why BI professionals around the world think Biml is the future of data integration and BI. Registration is just $69. Register before July 15th and receive early bird discount of just $49. Breakfast,...(read more)

sp_spaceused Alternative

Andrew Kelly — Fri, 17 May 2013 20:35:00 GMT

I don’t know why but for some reason I have never liked using sp_spaceused. It probably started a long time ago when the results were often inaccurate due to the meta data being out of date. I am pretty sure that was fixed somewhere along the line but the system stored procedure had some other limitations that usually prevented me from seeing the data that I really wanted in most cases. So at some point I created my own stored procedure to view the data I was most interested in almost on a daily...(read more)

Join Matt Masson and I for Developing Extensions for SSIS 22 May at 11:00 AM EDT

andyleonard — Fri, 17 May 2013 12:44:10 GMT

Matt Masson and I are co-presenting Developing Extensions for SSIS 22 May 2013 at 11:00 AM EDT. If you’ve never heard Matt present, you are in for a treat. Matt is knowledgeable (he helped build Integration Services 2012!) and entertaining. This is going to be a good one, folks! Abstract Join Matt Masson and Andy Leonard for a discussion and demonstrations on extending SSIS with custom tasks and data flow components. This code heavy session walks you through the creation of a custom SQL Server Integration...(read more)

Private Cloud and Virtualisation Strategy Event - Brisbane, Sydney, Melbourne

Greg Low — Fri, 17 May 2013 03:50:00 GMT

Fellow MVP Alessandro Cardoso sent me information today about a private cloud and virtualisation event that's running later this month.

It's being offered in Brisbane, Sydney, and Melbourne.

If that's of interest, you'll find more details here: http://virtualisationandmanagement.wordpress.com/2013/05/08/private-cloud-virtualisation-strategy-events-in-brisbane-melbourne-sydney-may-and-june-2013/

Reflections on SQLBits XI

jamiet — Thu, 16 May 2013 22:03:50 GMT

Its been a couple of weeks since SQLBits XI happened in Nottingham and I thought I’d jot down a few thoughts for posterity.

First the venue. I think its fair to say that the overall consensus was that the East Midlands Conference Centre was the best SQLBits venue these has been so far – I’ve been to more than half of them and based on my experience I would agree with that sentiment. The hotel especially was top quality – I was pleased with my room and the breakfasts were way better than the Travelodge standards I’ve become accustomed to at such events. Perhaps the great weather over the weekend helped lift the spirits but I’d say the bar has been set high, I hope future SQLBits conferences are at similar standard venues.

You may have noticed that many of my blog posts over the past year have been related to SSDT and my SQLBits sessions this time around followed that trend. I delivered a session on the Friday, jointly with Craig Ottley-Thistlethwaite, entitled “Real World SDDT” and also delivered my first ever day-long pre-conference seminar on the Thursday entitled “SSDT Database Projects from the ground up”. Delivering a pre-con was slightly petrifying and I spent a large part of the three months previous preparing for it; I hope it was worth it. I had 30 attendees which I was delighted with (especially given this was my first pre-con) and we established that the furthest anyone travelled was from Romania, though I’ll assume it wasn’t just so he could come to my pre-con . I had some pleasant feedback via Twitter afterwards from some of the attendees and I’m crossing my fingers that the official feedback is in a similar vein.

The session I did with Craig went fairly well I thought. I did the first 30minutes where I covered the basics of SSDT deployment from a high level before Craig ratcheted the complexity up a few notches by demoing some interaction between SSDT, MSBuild & Git – really great stuff for those who like to get into the nuts and bolts of this stuff. I was delighted that Craig was willing to do the session with me (in fact it was his idea) as this was his debut public speaking gig and I’m hoping its given him the desire to do more in the future.

On the Saturday my wife’s uncle, John Milne, came to the conference. John has been working in customer service for years but of late has decided that he wants a change of career and to that end has been studying an Open University course in IT. He told me he had particularly enjoyed the database-focused modules of his course and hence I suggested he come to the free Saturday of SQLBits to try and get a flavour of what the industry is all about and perhaps learn about some real-world experiences to add to his academic travails. By the end of the day John told me he’s had a fantastic time, learned a lot, and was hooked. Mission accomplished I’d say. John lives in Leeds so I introduced him to Chris Testa-O’Neill who helps to run the Leeds user group and John should be going along to some user group events in the near future – if you happen to meet him there please welcome him into the fold!

All that remains for me to say is a massive thank you to the SQLBits committee who do such an amazing job, all voluntarily, in putting this all together. Thank you Simon Sabin, James Rowland-Jones, Chris Webb, Darren Green, Allan Mitchell, Tim Kent, Chris Testa-O’Neill & Martin Bell. I also want to thank all of the volunteer SQLBits helpers that worked tirelessly on the weekend to make sure the whole thing ran smoothly.

Bring on SQLBits XII!

@Jamiet

INSERT SELECT is Broken. Ask Microsoft to Fix It.

Adam Machanic — Wed, 15 May 2013 16:42:00 GMT

Imagine that you're moving thousands or millions of rows between two tables . Maybe it's between a staging table and a data warehouse in an ETL process. Maybe you're manipulating some data via a temp table as you're preparing lookup data for your OLTP system. Maybe you're preparing data for an end user. It doesn't really matter what your use case, because there are so many of them. We, as database developers, spend all day moving data back and forth . Unfortunately, sometimes our processes break...(read more)

Part of the journey: failure

Rob Farley — Tue, 14 May 2013 00:09:00 GMT

The topic for this month’s T-SQL Tuesday is about the journey. Wendy Pastrick’s choice (I’m hosting again next month!).

There are a lot of journeys. There are some that just keep going, and others that seem to finish (some in success; some in failure). Of course, many of the ones that finish end up being the start of new journeys, but sometimes they don’t need to continue – they just need closure. There are things that can be learned regardless of how things went, whether or not goals were reached, and whether or not there was failure.

There’s been a few things recently to remind me of this...

I visited a company recently who has put a video together promoting the idea of failure. It wasn’t asking that people fail, but said “Go ahead and fail,” because failure happens. They had been through a rough time, but were persisting and seeing things turn around.

Just the other night, we saw the musical Chitty Chitty Bang Bang (you probably know the movie – the Bond film where Dick van Dyke stars as the guy with the gadget-car, who takes on Goldfinger and falls for the girl with the inappropriate name). Anyway, there’s a brilliant song in that called “The Roses of Success” (YouTube link there). It has the same sentiment – “…from the ashes of disaster grow the roses of success!”

A few years ago, my kids started saying “FAIL!” when someone did something wrong. I can’t say I liked the insult. Far worse would’ve been “DIDN’T TRY!” It would be very easy to just stay in bed and ‘avoid failure’ that way, but anyone who fails has at least done something. To fail, you must at least be active.

I talk to a lot of people about Microsoft Certification, particularly people who have failed an exam. I tell everyone (not just those who have failed before) to try the exams before they feel they’re ready for them, . What’s the worst that can happen? Worst case, they don’t pass. But how is that a bad thing? It might feel less than brilliant (I know, it’s happened to me before), but it gives an opportunity to target the weak areas before having a subsequent attempt. It doesn’t matter how many attempts it takes to get a passing score – the wrong option would be to give up. Studying can be excellent, but not to the point of causing extra stress.

There are things in life we do easily, and there are things that we struggle with. I know there’s a bunch of stuff in my own life that falls into both categories. I don’t want this post to be a list of the things that I’m not doing well – I simply want to point out that I want to keep trying.

With God’s help, I can improve in the areas in which I’m not excelling, and start to smell the roses of success.

Israeli SQL Server Usergroup: Locking & Blocking in active environment

Michael Zilberstein — Mon, 13 May 2013 13:38:47 GMT

A week ago I gave my session on the subject. It is only first part of the two – second will be next time when there is an open slot. Demos from my session are available here ....(read more)

New Article on the SQL Server 2012 Backup and Restore PowerShell Cmdlets

AllenMWhite — Mon, 13 May 2013 13:08:00 GMT

While I was on vacation last week in Scotland Simple Talk published a new article I wrote called Backup and Restore SQL Server with the SQL Server 2012 PowerShell cmdlets. Hope you have as much fun with it as I did writing it. Allen...(read more)

SQL Down Under Show 59 - Guest Reza Rad now available for download

Greg Low — Mon, 13 May 2013 00:51:00 GMT

Hi Folks,

On Thursday night, I got to record a podcast with Reza Rad. I was interested to speak to Reza after reading his SQL Server 2012 Integration Services Cookbook.

In the show, we discuss SQL Server Integration Services (SSIS) extensibility and some aspects of performance tuning for SSIS.

You'll find the show here: http://www.sqldownunder.com/Podcasts

Enjoy!

Excited to be speaking at #SQLSATATL next weekend

dbaduck — Sun, 12 May 2013 05:06:00 GMT

I will be on for speaking next week at SQL Saturday #220 #SQLSATATL. My sessions will be on SQL Server TDE and SMO Internals for High Performance PowerShell. Both are great fun to present and good information to have. Hope to see you there in Alpharetta, GA....(read more)

Enterprise Storage Systems - EMC VMAX

jchang — Fri, 10 May 2013 15:52:00 GMT

I generally do not get involved in high-end SAN systems. It is almost impossible to find meaningful information on the hardware architecture from the vendor. And it is just as impossible to get configuration information from the SAN admin. The high-end SAN is usually a corporate resource managed in different department from the database. The SAN admin is generally hard set on implementing SAN vendor doctrine of "Storage as a Service" and does not care to hear input on special considerations from...(read more)

Performance-Based Management and Andy’s Law

andyleonard — Fri, 10 May 2013 13:29:00 GMT

Let’s begin with an assertion: “People are more important than process.” – Andy, circa 2008 Whenever an enterprise or institution adopts a new process or policy, that policy should serve people and not shackle them. The nicest thing that can be said about a policy that binds people is, “It wasn’t well thought out.” It’s a bad idea, in other words. What’s the logical thing to do when we encounter a bad idea? Reverse it, as quickly as possible. Is it a good idea to hang on to the bad idea because (hypothetically)...(read more)

SQLCLR Performance Session at TechEd US

Adam Machanic — Wed, 08 May 2013 16:14:00 GMT

I am super-excited to visit New Orleans next month for Microsoft TechEd; it will be my sixth time speaking at the show. My session takes an in-depth look at some of the techniques I've developed for using SQLCLR modules -- and some of the great performance gains I've been able to achieve. Hope to see you in NOLA! If you're not attending the show, the video will be available on demand a few days after I give the talk....(read more)

Where are the Windows Azure customer case studies – and why aren’t there more?

BuckWoody — Wed, 08 May 2013 14:31:28 GMT

“Case Studies” are a great tool when you’re evaluating a platform. Having evidence that other companies have deployed Windows Azure, in addition to how they did it, is a good way to plan your own deployments or even just evaluate whether Windows Azure would be a good fit. And we have several case studies you can examine here: https://www.windowsazure.com/en-us/home/case-studies/

But there aren’t a lot of them – and there isn’t much detail on some. Why not?

Well, as to the first question, we only keep a few of these on the web at any given time. They rotate based on date, industry, and other factors. If you want more, you can contact your local Microsoft team for something more specific to your situation or industry.

But even when you do, you may not get what you’re looking for – a full-scale architecture diagram with costs, names and dates, sizes and layouts and so on. That’s a tougher thing to put on the web, and here’s why: companies are reluctant (as they should be) to include that level of detail in a public place. There are legal and competitive reasons they just can’t do that. And of course at the very beginning of any project we have to get the company to agree to do a case study, and no, we don’t pay for that. The company is going to have to let us document things, work with them, and generally get involved in the project. Not a lot of companies are willing to do that. In the end, the case studies prove out that folks in your industry are using Windows Azure successfully, and that the detail is specific to your requirements and constraints. They are very useful to the business side of the company, but not as useful to the technical folks who want details.

So we’ve stepped into that gap with more of the “real details” on how to implement a Windows Azure solution. In most cases these are live, real apps – not just theoretical or best-practices kinds of documentation. We have a few places you can check for more detail, including the Windows Azure Training Kit, and much more.

Complete Applications

Contoso Cycles – a fully-functional, open sourced demo site on Azure: http://archive.msdn.microsoft.com/contosocycles

Fabrikam – a fully-functional, open sourced demo site on Azure: http://www.fabrikamshipping.com/

Complete Samples

Simple picture display app with source code: http://code.msdn.microsoft.com/windowsazure/MyPictures-on-Windows-91bb3057

Cloud Survey – walkthough of a complete survey site using multiple components: http://channel9.msdn.com/posts/Windows-Azure-Web-Sites-Modern-Application-Sample-Cloud-Survey

Bidnow - Auction site running on Azure source code: http://bidnow.codeplex.com/

Layered Architecture Example – Very in-depth pattern for working with hybrid and scale-out projects: http://cloudsample.codeplex.com/

Other Code Samples: https://www.windowsazure.com/en-us/develop/net/samples/

Guidance and Patterns

General Guidance: https://www.windowsazure.com/en-us/develop/net/guidance/

Architecture Patterns: https://www.windowsazure.com/en-us/develop/net/architecture/

Patterns and Practices for Windows Azure: http://msdn.microsoft.com/en-us/library/ff898430.aspx

SQL Dependency DMV’s

Tamarick Hill — Tue, 07 May 2013 20:57:00 GMT

SQL Server Dynamic Management Views (DMV's), or more appropriately termed Dynamic Management Objects (DMO’s), have been around for quite a while now. They made their first appearance in SQL Server 2005 and they unlocked a wealth of information that was either impossible to get, or would take extremely complicated queries to get the information that you were looking for. Since I began learning about DMV's I have been a big advocate for using them. After the discovery of two new DMV's that were added in SQL Server 2008, my love for DMV's has grown even stronger.

These two DMV's in question are used to track down objects that are referencing a specific SQL Server object(sys.dm_sql_referencing_entities), or objects that are referenced by a specific SQL Server object(sys.dm_sql_referenced_entities).

DBA's are often tasked to identify all objects that may be depending upon a specific object or to find all objects that a specific object depends on. For example, let's use the AdventureWorks2012 database and let's assume that after deploying a new Employee Management solution, it is determined that we no longer need the table 'HumanResources. Department' which was used by our old legacy application. As a result, we need to identify all objects that may be referencing this table before we decide to drop it. To identify this we can use one of the SQL Dependency DMV's to get the needed information.

SELECT referencing_schema_name, referencing_entity_name

FROM sys.dm_sql_referencing_entities ('HumanResources.Department', 'OBJECT')

This simple DMV was able to identify all objects, in this case only 2 views, which are currently referencing our 'HumanResources.Department' table. After gathering this output, we realize that we can probably decommission the 'vEmployeeDepartment' view as well. Before we do so, we need to analyze all objects that this view references in its definition. Again a simple DMV query will return the information needed.

SELECT referenced_server_name, referenced_database_name, referenced_schema_name,

referenced_entity_name, referenced_minor_name

FROM sys.dm_sql_referenced_entities ('HumanResources.vEmployeeDepartment', 'OBJECT')

This DMV was able to give us a vast amount of information about the view. It was able to tell us every object that is referenced in the view all the way down to the column level. If this view had any Cross Database or Cross Server references, that information would also be displayed.

For more information about these two DMV's, please visit the below links:

http://msdn.microsoft.com/en-us/library/bb630351(v=sql.110).aspx

http://msdn.microsoft.com/en-us/library/bb677185(v=sql.110).aspx

From 0 to DAX at TechEd Pre-Conference Seminar #dax #msteched #tee13

Marco Russo (SQLBI) — Tue, 07 May 2013 12:54:00 GMT

In June I and Alberto will deliver a pre-conference seminar at both TechEd North America (New Orleans, LA) and TechEd Europe (Madrid, Spain).

This day is a very good quickstart for those of you that still didn't complete one of our books, or those of you that missed one of our workshop about Tabular or PowerPivot. If you are planning to go to TechEd, you might also consider attending a full day about DAX, following the From 0 to DAX one-day seminar. Here are the links:

TechEd North America – From 0 to DAX Pre-Conference Seminar (New Orleans, LA - June 2, 2013)
TechEd Europe – From 0 to DAX Pre-Conference Seminar (Madrid, Spain - June 24, 2013)

And in case you are underestimating the importance of DAX in your future BI projects... read this blog post from Dandy Weyn - his privileged point of view inside Microsoft highlights how much DAX is important today and will be pervasive in the future!

Pros and Cons of Using Read-Uncommitted and NoLock

Tamarick Hill — Mon, 06 May 2013 16:43:00 GMT

Definitions

Read-Uncommitted Isolation Level

The read-uncommitted isolation level is the least restrictive isolation level within SQL Server, which is also what makes it popular for developers when looking to reduce blocking. Blocking is “typically” not an issue when using this isolation level because the query will not request shared (S) locks on the tables that it is reading. In addition to this, other processes are still allowed to read and modify data within any table that your query may be accessing under this isolation level. I say this is “typically” not an issue because there are some locks that are still generated and can cause certain types of blocking. A schema-stability (Sch-S) lock will be placed on the table(s) being accessed and a shared (S) lock will be placed on the database. The (Sch-S) lock on the table will only prevent DDL actions from occurring on the accessed tables such as dropping columns within the table or dropping the actual table itself. The (S) lock on the database is put in place to prevent the database from being dropped while it is being accessed. When an isolation level is used, the isolation level is in effect for everything running under that current connection.

The syntax for using this isolation level is below:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Nolock Table Hint

The nolock table hint behind the scenes performs the exact same action as running under the read-uncommitted isolation level. The only difference between the two is that the read-uncommitted isolation level determines the locking mechanism for the entire connection and the nolock table hint determines the locking mechanism for the table that you give the hint to. For example, if you have a stored procedure that will read data from 20 tables, the read-uncommitted isolation level would cause every table to be accessed without requesting shared (S) locks. If you only need a subset of those 20 tables to not acquire shared (S) locks, but have other tables that you need to only read committed data, then you would want to use the nolock hint instead.

The syntax for using a nolock hint is below:

SELECT * FROM DatabaseName.dbo.TableName WITH (NOLOCK)

Benefits

Reduced Blocking

As stated in the above sections, the primary benefit of using the read-uncommitted isolation level or the nolock table hint is that this reduces blocking. Users are able to use these two options and not run the risk of being blocked by readers and writers within the database. In addition to this, users do not have to worry about running the risk of blocking other readers and writers within the database tables.

Risks

Dirty Reads

There are many risks that you run when using the read-uncommitted isolation level or nolock table hints. The first risk that could occur is “Dirty Reads”. A dirty read occurs when your query reads a data page that is different in memory than the page on disk. Any data that has been changed, but not yet committed, is considered to be “dirty”. For some applications this may be ok, but for many applications, this can cause a major problem for the users that rely on the system.

A typical example that is used throughout the SQL Server community to illustrate this issue is: Suppose you need to get some repairs done on your wife’s car and you pull up to an ATM machine to withdraw the money needed. Your account currently has $300 in it. You put your debit card into the machine, enter your pin, and tell the machine you want to withdraw $200. Your wife then calls you and tells you that the car is working fine now and you don’t have to worry about getting repairs. You immediately cancel your transaction and drive off. Little did you know, the weekly balance report that you requested from your bank began processing after you entered your withdrawal amount but before you cancelled your transaction. The report is then emailed to you saying you have a balance of $100. Immediately you are now concerned and wondering what happened to your other $200. The issue is that the bank report read ‘dirty’ data that had not yet been committed and then passed along that ‘dirty’ information to you. This is a primary example of when reading ‘dirty’ data is not a good idea.

Non Repeatable Reads

Non repeatable reads becomes an issue when your application has code that executes which requires for data to be read multiple times and return the same consistent data upon each read. When using the read-uncommitted isolation level or nolock table hints shared (S) locks are not requested on the tables you are reading. As a result writers are able to access those tables and modify the data within them during the middle of your read operations. This can drastically skew your expected results.

To help illustrate this issue: Suppose I go to the bank to deposit some money into my account. I currently have a Checking account with a $100 balance and a Savings account with a $100 balance. I plan to deposit $300 into my checking account. Meanwhile, my wife is at home on her mobile application checking our account balance. The mobile application runs a stored procedure which reads the AccountBalance table one time for each account that you own, and then reads the table one final time to give you the sum of all your accounts. The application shows my wife we have $100 in the Checking account, $100 in the Savings account, and our two accounts have a total value of $500. Based on this information my wife received, she is completely confused by this inaccurate data.

What happened is the stored procedure read the table multiple times expecting the data would be the same in each read. However, an outside transaction (the deposit) was able to modify the data in between the multiple reads that the stored procedure issued. The developer of the stored procedure never anticipated this but he used a nolock hint on the table so that the application would not run into any blocking and results would return quicker. The developer did indeed prevent blocking, but at the expense of creating the risk of the application returning inaccurate data.

Same Data is Read Twice

There are rare occasions when the same data can be read twice when using the read-uncommitted isolation level or nolock hint. To illustrate this issue we have to give a little background first. Clustered Indexes are created on SQL Server tables to physically order the data within the table based on the Cluster Key. The leaf pages of the index contain the data pages which contains the actual data for the table. Data pages can hold 8K worth of data.

Scenario: You have an ETL process that will Extract all records from a table, perform some type of transformation, and then load that data into another table. There are two types of scans that occur in SQL Server to read data: allocation scans and range scans. Range scans occur when you have a specific filter (where clause) for the data you are reading, and an index can be used to help seek out those specific records. When you do not have a filter, an allocation scan is used to scan all of the data pages that have been allocated to that table. Pending you are not doing any type of sort operations, your data will read the data pages in the order as it finds them on the disk. For simplicity, let’s assume there is no fragmentation so your data pages are in order 1-10. So far your process has read pages 1-6. Remember your process is not requesting shared (S) locks so you are not blocking other users. Meanwhile, another process begins which inserts records into your table. This process attempts to insert records onto Page 3, but the page is full and the record will not fit. As a result the page has to be split and half of the records will remain on Page 3 and the other records will be moved to a new page which will be page 11. Your process has already read the data that was on Page 3, but now half of that data has been moved to page 11. As a result, as your process continues it will read Page 11 which contains data that has already been read. If there is no type of checks on the destination table, you will end up with bad duplicate data.

This is an example of how changing the locking mechanism can cause you to read duplicate data into your process and reduce the integrity of the data.

Phantom Reads

A phantom read occurs when you read data that is there one minute, and gone the next. A phantom read can become problematic when you have a process that performs some type of operation based on the data that it has read. To illustrate this issue, suppose you have stock in CompanyA and CompanyA decides to pay all of its stock holders a dividend. There is a stored procedure that first reads a table with all of the current stock holders and builds a temp table based on that list. Then the stored procedure initiates a dividend payout process for all of the accounts in the temp table. In the middle of this procedure running, a stock-holder sales all of his shares in the company. The sale of the stock did not complete until after the temp table was built, but before the dividend process completed. This will cause an issue with the dividend process because it will not be able to pay the dividend since the person no longer owns the stock.

Using the read uncommitted isolation level or no lock hint allowed the user to sale his stock at the same time the dividend process was running. If this isolation level or hint was not used, the user would have been blocked and unable to sale his stock until after the dividend payout completed.

Misconceptions

Using nolock table hints for update or delete statements

Sometimes I see developers create code which performs Update or Delete statements and use the nolock table hint. It is important to note that this hint only works for read-only operations. Anytime you are modifying data, such as in an Update or Delete statement, this hint is completely ignored by SQL Server. When performing these types of operations, locks have to be generated to ensure that transactions are atomic, consistent, isolated, and durable.

Conclusion

SQL Server is a very complex enterprise database solution with many options and flags that can be changed to alter the behavior of SQL Server. Although many of these options have a justified use, it is important to understand the risks that are associated with changing these options. The read-uncommitted isolation level and nolock table hint are no exception to this rule. Generally it is best practice to stick with the default isolation level and refrain from using table/query hints unless it is absolutely necessary and the solution has been thoroughly tested. Using read-uncommitted and nolock should be the EXCEPTION and not the RULE.