The other day I had a client complaining about slowness, queries erroring out, blocking and processes stopping; you know the usual complaints. So, like any other DBA I pulled out my handy dandy toolbox of queries to determine exactly what was going on with the server.
Upon reviewing their current running sessions, I noticed that the Isolation Level for a simple UPDATE transaction was “Serializable” and all other transactions were “Read Committed”. And this Serializable transaction was blocking all other transactions.
Now, let me preference the rest of this blog series with: in all my SQL experiences, I have yet had to change the isolation levels on a transaction or database level. Which means, I have never studied this topic in depth. So, this got me thinking, what are the different isolation levels and why would anyone ever change from the default isolation, which is “Read Committed”?
Typically, when I attempt to learn something, I like to write it down. Because this is a lot of information, I am going to present my learning in 3 parts: Concurrency Issues and how SQL works, Isolation Levels and how they help resolve Concurrency issues, and some helpful scripts to test and figure all this out.
So, let’s start at the beginning. The purpose of an Isolation level is to provide SQL server with a mechanism to protect data against specific concurrency problems when multiple CRUD (create, read, update, delete) operations occur on the same data.
Data is intended to be read, modified, created or deleted over time. Most databases have multiple connections doing all these things at the same time. Managing all these concurrent connections SQL server must decide which statements gets precedence. SQL typically operates on a “first-in, first-out” method of handling queries. But what those queries do can determine the fate of queries in line to be executed. And these decisions can cause data concurrency issues. The following concurrency “phenomena” have been identified by ISO/ANSI standard for SQL-92 as possible results for concurrent transactions.
It is possible for two or more transactions to modify the same row. While the subsequent transactions are reading the data, the 1st transaction commits its changes. Then the subsequent transaction makes its changes and commits, thereby possibly losing the changes committed by the 1st transaction. In this scenario, the last transaction always wins.
A dirty read is when a SELECT statement will read against a table that is currently being modified and not yet committed to the database. By default, SQL Server will protect against this on all isolation levels, except for Read Uncommitted or by using NOLOCK in your queries.
This form of concurrency problem can happen when a specific dataset (using the same WHERE clause) is accessed multiple times within the same transaction and some of the rows in the dataset are updated between the 1st and 2nd access. This will product two different result sets.
Very similar to non-repeatable reads, a phantom read concurrency is when two queries in the same transaction, against the same table with the same WHERE clause produces two different result sets because a 3rd query has changed data between the first and second SELECT statements.
How to combat these Concurrency Problems?
For most DBAs, the integrity of their data is, or should be, the most important aspect of their job. It quite literally can be a “job killer”. Fortunately for us, Microsoft has built SQL Server with several levels to isolate the data during CRUD operations to ensure these concurrency problems are eliminated. These Isolation Levels are called Read Uncommitted, Read Committed, Repeatable, Serializable, Snapshot.
The following script when executed will display all current sessions and their Isolation Levels. It is always good to know what your connections are doing to SQL.
select s.session_id ,s.status ,db_name(s.database_id) [Database] ,case when s.transaction_isolation_level = 1 then 'Read Uncommitted' when s.transaction_isolation_level=2 then 'Read Committed' when s.transaction_isolation_level=3 then 'Repeatable' when s.transaction_isolation_level=4 then 'Serializable' when s.transaction_isolation_level=5 then 'Snapshot' else 'Unspecified' end [Transaction Isolation Level] ,s.host_name ,s.cpu_time ,s.deadlock_priority from sys.dm_exec_sessions s where s.session_id>50
In the case of my client, there was one UPDATE transaction that was using Serializable Isolation Level, it was this session that was BLOCKING all other transactions from completing. But why was the transaction Serializable and why was it blocking for so long of a period?
Not knowing the details behind Serializable Isolation Level, it was time for some more learning. I will discuss the different Isolation levels in my next post. Until then…
Yesterday, Idera Software (@Idera_Software) announced their slate of 2017 Idera ACEs (Advisors and Community Educators).
I am honored to be included as an Idera ACE for 2017. Part of my responsibilities as an ACE is to represent Idera at local SQL Saturdays which means I get to travel more, meet more people and present more!
One of the reasons I enjoy working as a consultant with Sparkhound is the concept of “knowledge transfer”. We believe in not only helping clients with their SQL Server problems; but also teaching them how to do it for themselves.
Being an Idera ACE seems to fall right in line with that philosophy! Joining me in this endeavor are the following members of the SQL Family! I hope to meet each and everyone of you over the course of the next year!
Mindy Curnutt – Dallas, TX
I will also do my best to dedicate more time to blogging, answering community forum questions and I will see you at SQL Saturdays!
This past week I have been working with a client to setup mirroring on a SQL 2014 instance. I realize mirroring is a deprecated feature; but many DBAs and companies still use it as a fail safe line of defense. And the fact that Mirroring has been a “deprecated feature” since SQL 2012 and is still available in SQL 2016; I think it might be around a little longer.
I am writing about this not to call out any client on the use of Mirroring or to promote or condone mirroring but to remind me of the things to look for when setting up any mirroring environment.
The Task at hand
My client was upgrading from SQL 2012 to 2014. The current SQL 2012 instance was mirrored to a second server. I was to reproduce the mirroring on the 2014 instance in preparation for the rollout to production.
This particular setup was problematic for me because of Windows Firewall rules and having multiple instance (heck multiple versions) of SQL installed on the same server.
The following version of SQL were installed on the same server:
- SQL 2008 R2 (default instance)
- SQL 2012 ( named instance) mirrored with a second server
- SQL 2014 (named instance) this was the server I needed to setup with mirroring to reproduce the SQL 2012 environment.
- Windows Firewall was basically configured to close everything unless a rule specified otherwise.
This list of things to check may not apply to all configurations and all mirroring setups. It is just some things that prevented this setup for succeeding.
- With multiple instances and connecting via Named Pipes, it is very important to have the “Named Pipes” protocol enabled in SQL Configuration Manager.
- With multiple instances SQL Browser becomes very important, especially if you do not want to use Port Numbers in your connection string. Here is the important part, check the Windows Firewall rules to ensure UDP Port #1434 has both an Incoming and Outgoing rule.
- Mirroring requires its own Port for communication as well. Incoming and Outgoing Windows Firewall rules will have to be created to ensure communication between the two servers. By default this is TCP Port #5022. However, because the SQL 2012 instance was already using this port for mirroring, I had to configure SQL 2014 with port #5023. Again, another firewall rule. Actually, I just added the port to the existing firewall rule.
- The last piece that was problematic was permissions. Because Principle and Target each had their own domain service account, each account needed to be added to the opposite server with the correct database permissions.
Once all of this was figured out and configured correctly, the mirroring session configured and started up with no problems!
Philosophy has always intrigued me and René Descartes is one of my personal favorite philosophers. Cogito ergo sum “I think, therefore I am”. The moment we stop thinking is the moment we stop existing. So, today I decided to learn something new. I wanted to setup a Stretch Database in SQL Server 2016.
Just about everything in SQL Server can be accomplished by either the SSMS GUI or Transact SQL commands. Anytime, I attempt something for the first time I always use the GUI, then learn the T-SQL commands.
This will launch the “Enable Stretch Wizard. First step in the wizard is the the Introduction. First thing I noticed was the the statement: “Once the data is migrated to Azure, unique and primary keys will not be enforced on Azure” Really? I wonder what else will be “not enforced” or not compatible with Azure. Time to look up my friendly neighborhood BOL, Limitations for Stretch Database. Wow, that’s a good number of limitations; however this is a simple test so I am not worried.
Next we have to connect to an Azure Subscription. The wizard step allows you to configure your SQL Azure database environment, select the Azure region (always pick one closest to you) and you get to choose between a preconfigured Azure server or allow the wizard to create one. I chose to allow SSMS to create a new Azure server. Next you have to create a database master key (DMK) to protect your Azure data. And finally you need to provide your IP Address (remember your external IP, not your machine IP) so SSMS can setup firewall rules for you.
Then you get the customary SQL Summary page before you execute the commands. Now that my database has been stretched, I added a table and data using this schema:
CREATE TABLE [dbo].[tblUsers]( [ID] [int] IDENTITY(1,1) NOT NULL, [FirstName] [varchar](50) NULL, [LastName] [varchar](50) NULL, [Email] [varchar](100) NULL, [DateEntered] smalldatetime null, CONSTRAINT [PK_tblUsers] PRIMARY KEY CLUSTERED ( [ID] ASC ) ON [PRIMARY] ) GO
I not only wanted to test the Primary Key limitation; but I also wanted to test the Filtered Stretch on the Date Entered Column. I populated the table with 11,000 records using the website http://mockaroo.com.
After the tblUsers table was populated, I enabled the Stretch Table wizard by right clicking on the table and selecting STRETCH >> ENABLE. After the Introduction screen you get to select which tables you would like to stretch to Azure. The GUI screen shows you all the tables and if there will be any “warnings” about stretching, the option to filter, number of rows in the table and the size of the table.
The first time I executed this I did not use the Stretch Filter and stretched the entire table. It worked perfectly. I could use SSMS to connect to my remote SQL Server and query the Azure table directly and could see there were 11,000 rows.
As you can see, we now have a new path in our query plan with an operator called “Remote Query”. Basically the local server queries the remote query then using the local Primary key Concatenates them back together to produce the desired result. So can we update the data?
Nope, sure can’t. Once the data lives in Azure, the data is READ ONLY.
Next I wanted to add the filtering on the DateEntered column so I can edit more recent data. I disabled Stretch and re-enabled it using the filters this time. I received error after error after error. According to BOL this query is not a valid filtering query. According to BOL, the date parameter needs to include the format value. At least that was the case with mine.
/* INCORRECT */ CREATE FUNCTION dbo.fn_example5(@column1 datetime) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS is_eligible WHERE @column1 < CONVERT(datetime, '1/1/2014') GO /* CORRECT */ CREATE FUNCTION dbo.fn_example5(@column1 datetime) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS is_eligible WHERE @column1 < CONVERT(datetime, '1/1/2014',101) GO
6713 rows found in Azure
and 4287 rows found in the local database. Exactly what I expected. Again, the query execution plan has to use “Remote Query” and Concatenate the results to produce the desired results.
Final Thoughts on Stretch Database
The SSMS has designed the Stretch Wizards to be very effective and very easy to use. However, when you “Disable” a stretch database it does not delete the table/database in Azure which means you will still incur charges for storage. And if you re-enable Stretch on a database it creates a 2nd database in Azure.
With the READ-ONLY limitation from the Azure side of Stretch database, you better make 100% sure you don’t need to edit anything in Azure. The only way to make any changes to the Azure side of the data is to Disable and “bring the data back” to the local machine and this incurs charges on the Azure side. Then you make your changes to the data, and re-enable it for Stretch. Which again, created another database in your Azure server.
The pricing for Azure SQL Stretch database seems to be a little steep. At the lowest performance level offered, it is ~$1860 per month! Microsoft calculates what they call DSU (Database Stretch Units) which “represents thee power of the query and is quantified by workload objectives: how fast rows are written, read and computed against.” https://azure.microsoft.com/en-us/pricing/details/sql-server-stretch-database/
All in all, Stretch Database is a very cool feature for archiving older data that will not change. But only if you can afford it. I would think using file groups with partitioning of data on separate drives could be a more cost effective solution for archiving than Azure stretch database. A very good learning experience.