What is the first word that comes to mind whenever one mentions the need for optimizing search or for increasing the performance of a database? Index.

Indexes play a vital role in SQL Server performance tuning, but they too need periodic maintenance. An index is logically implemented as a B-tree.

Over time as data is accumulated by the table, fragmentation may set in and the distribution of data within the index may become unbalanced, with some pages becoming full (as governed by the fill factor) leading to “page splits” whereas others might be sparsely filled. If data is deleted from the table, it leaves behind a “hole” in the index page – this free space is not reclaimed, which contributes to the bloating of the index. This reduces the efficiency of the indexes, while possibly consuming more than required disk space. At this stage, administrators need to perform maintenance operations on the index.

Index Maintenance operations can be classified into two:

1. Index Rebuild
   
   
   
   • During a rebuild, the entire index (from the root to the leaf nodes) is rebuilt using the same columns, index type, uniqueness attribute, and sort order
     
   • The rebuild of an index effectively recreates the entire B-tree structure
   
   
2. Index Reorganize
   
   
   
   • Index reorganization, on the other hand, only removes fragmentation at the leaf level
     
   • Intermediate-level pages and the root page are not defragmented

From an availability perspective, Reorganization is better than a rebuild, because reorganization is always an ONLINE process, i.e. it acquires only short-term locks whereas REBUILD prevents any changes to the underlying table until the operation completes, i.e. it acquires long-term locks. The question therefore is:

What types of locks do index REBUILD and index REORGANIZE operations occupy on the underlying table, by default (i.e. the “ONLINE” option is not specified)?

Bonus Exercise

As a bonus exercise, you may want to share any queries or tests that you may have conducted to arrive at the answer to the question above. Please note that the bonus exercise does not carry any rewards this time around – it is only as an exercise for the brain cells.

Rules

The rules are very simple:

1. You can answer these questions till Wednesday 22:00hrs (IST)/Wednesday 12:30hrs (US-EDT) – August 15, 2012
   
2. Answers will be shared and winners declared in my post on Thursday, August 16, 2012
   
3. You can answer multiple times – however, at the end of the day, only one entry would be considered
   
4. Follow the following on Twitter!
   
   
   
   • Pluralsight (Handle is: @pluralsight)
     
   • BeyondRelational.com (Handle: @beyondrel)
     
   • Me! (Handle is: @nakulv_sql )
   
   
5. Winners will be chosen randomly
   
6. Prizes will be given out through Twitter – hence, it is important that you follow me

Remember: Participation is always more important! Make sure you reply to the quiz even if it’s just a guess!

Until we meet next time,

Be courteous. Drive responsibly.

Earlier this week, we discussed the following ACID properties that are geared towards ensuring the integrity of the data residing within the database.

• Atomicity
  
• Consistency
  
• Isolation
  
• Durability

Every user database within Microsoft SQL Server follows these ACID properties. However, there is one special case – one database which does not support one of the ACID properties. Therefore, I had asked 3 questions – one main, and the rest were bonus questions as mentioned below:

1. Which SQL Server database does not follow one of the ACID properties?
   
2. Name the ACID property which is not followed in the answer to question #1
   
3. Explain, in your own words, the reason why you believe the database does not follow the said ACID property

Today, I will attempt to answer these questions myself.

Which SQL Server database does not follow one of the ACID properties? Why?

In my humble opinion, this has to be the tempdb database. The tempdb database does not follow the ACID property of Durability. Here’s why:

Whenever Microsoft SQL Server is restarted, the tempdb is recreated. Any data that was committed to the tempdb is lot and replaced by a fresh copy from the model database. To test this out on your own, or to read about my experiments with the re-creation of the tempdb, you can refer my previous posts referenced below:

1. TempDB – Is it a copy of the Model database?
   
2. TempDB v/s Model database – Minimum size considerations – CREATE DATABASE – Error Msg 1803

Who came up with the correct answers?

First of all, I would like to thank everyone who responded. But, this is a competition, and hence, below are the people whose responses were correct:

• Gaurang Patel
  
• Olga Medvedeva
  
• Hardik Doshi
  
• Erik Eckhardt
  
• Nirav Gandhi
  
• Joey Noll
  
• Ramkoti

Unfortunately, I will not be considering the answer of the user “sk2000” because the original answer was “NoSQL”, which generally do not have the concept of ACID properties.

And now, for the winners…

The following are the winners, randomly selected from those who participated in the quiz.

• Gaurang Patel
  
• Joey Noll

Expect my DM on your twitter accounts this week-end (or earlier) with more information on your prize – a Pluralsight training code!

Check out: http://www.pluralsight-training.net/microsoft for a detailed list of the courses available. Did you know that Pluralsight has courses spanning almost every Microsoft technology currently available in the Market? If you are a blogger, do write about how you used the Pluralsight trainings – which trainings you took, and what is your feedback.

For those who did not win…

Do not despair if your name did not appear in the list above. Another quiz is coming up on Monday, August 13, 2012. Be sure to participate in that one too!

Until we meet next time,

Be courteous. Drive responsibly.

Any RDBMS system follows a certain set of general principles that are geared towards ensuring the integrity of the data residing within the database. These fundamental principles are collectively known as ACID properties, which every database within an RDBMS system should confirm to.

• Atomicity:
  
  
  
  • At a transaction level, this is the all-or-nothing approach, i.e. either all changes are committed or none – one cannot have a partially committed transaction
    
  • Example: A parallel can be established with the banking industry – when transferring funds from one bank account to another, it is imperative that the amount of money deducted from the source account are credited to the destination account. It is not acceptable to have a deduction from one account, but no credit to another account (that would be a scam!). If something goes wrong during the transaction, the rollback needs to be triggered in both systems – source and destination
    
  • Similarly, all RDBMS systems need to ensure that if something goes wrong within any transaction, an “undo” or “rollback” happens for the non-committed transaction
  
  
• Consistency:
  
  
  
  • Whenever any transaction is “committed”, the effect of the transaction should leave the database in a logically consistent state
    
  • Example: When adding sales data into a database, it would be logically inconsistent to enter a sales order without having a corresponding customer record. Through the use of foreign keys, the RDBMS ensures that one cannot leave the database in a logically inconsistent state when committing a transaction
  
  
• Isolation
  
  
  
  • Each transaction should be isolated (or protected) from the effects of other concurrently running transactions
    
  • This means that although multiple transactions are running in parallel, the net effect of the execution of the execution should be identical to that of executing all transactions in series (i.e. one after another in some serial order)
    
  • Example: Taking our banking example further, let’s assume that John wants to transfer $100 to Jack’s account. Jack wants to transfer $75 to Steve’s account. Both are separate transactions, but the net effect of these transactions is that Jack will end up with $25 more than his base balance ($x). Here’s how: [($x + $100) – ($75)] = $x + $25. If this is not the end result, the system is no good
  
  
• Durability
  
  
  
  • Durability simply means that once a transaction has been completed, it’s effect must be persisted within the database irrespective of the state of the system (i.e. even in case of a system crash, the data must be permanently committed to disk upon recovery)
    
  • Example: Continuing our example of the banking system, if the system goes down in the middle of the banking transaction, Atomicity would ensure that uncommitted transactions are rolled back. However, if the system goes down after completion of the transaction, then changes made to all accounts involved must remain as-is when the system comes back online

The Question – Which SQL Server database does not follow one of the ACID properties?

Every user database within Microsoft SQL Server follows these ACID properties. However, there is one special case – one database which does not support one of the ACID properties. The question today is:

Which SQL Server database does not follow one of the ACID properties?

Bonus Questions!!!

Here are two (2) bonus questions which you can answer – the bonus questions also contribute to the prize-winning questions!

1. Based on the answer to the basic question, name the ACID property which the database does not follow
   
2. Explain, in your own words, the reason why you believe the database does not follow the said ACID property

Rules

The rules are very simple:

1. You can answer these questions till Wednesday 22:00hrs (IST)/Wednesday 12:30hrs (US-EDT) – August 08, 2012
   
2. Answers will be shared and winners declared in my post on Thursday, August 09, 2012
   
3. You can answer multiple times – however, at the end of the day, only one entry would be considered
   
4. Follow the following on Twitter!
   
   
   
   • Pluralsight (Handle is: @pluralsight)
     
   • BeyondRelational.com (Handle: @beyondrel)
     
   • Me! (Handle is: @nakulv_sql )
   
   
5. Winners will be chosen randomly

Remember: Participation is always more important! Make sure you reply to the quiz even if it’s just a guess! 

Until we meet next time,

Be courteous. Drive responsibly.

Helping a team with an implementation has it’s own unique challenges, and the biggest challenge is – they ask questions! I get a kick out of this challenge because it gives me and the teams around me a lot to learn. Currently while helping a team to implement optimistic concurrency, somebody asked me a question:

What is the next value of ROWVERSION at any given instant of time?

The question makes absolute sense. If we can determine the active value of an identity column and determine which value would be used next, there is no reason why we cannot determine the active value of a ROWVERSION column. After all, ROWVERSION is an auto-incrementing number.

The answer comes in the form of a non-deterministic function – MIN_ACTIVE_ROWVERSION. This function returns the lowest active ROWVERSION value in the current database. A ROWVERSION value is active if it is being used in a transaction that has not yet been committed.

Explanation

The definition from Books-On-Line is a little too heavy to digest in one go. So, here’s a break-down of the sentence.

A new ROWVERSION value is typically generated when an INSERT or UPDATE is performed on a table that contains a column using the ROWVERSION data-type. A transaction may be performing multiple INSERTs and UPDATEs – causing multiple changes to the ROWVERSION value. However, the lowest of these values (effectively, the value that was first used when the transaction was started) is what is returned by the function – MIN_ACTIVE_ROWVERSION().

For all practical purposes, this is same as the value of @@DBTS + 1.

Demonstration

Here’s a demonstration of the function – MIN_ACTIVE_ROWVERSION. This demo requires two active sessions to the SQL Server instance.

Let’s begin by creating a test table, and inserting some test data into it. We will use the MIN_ACTIVE_ROWVERSION() to determine the value of the ROWVERSION column that will be used by SQL Server.

USE tempdb
GO

--Safety Check
IF OBJECT_ID('SourceData') IS NOT NULL
BEGIN
    DROP TABLE SourceData
END
GO

--Create the demo (source) table
CREATE TABLE SourceData (RowId INT IDENTITY (1,1),
                         RowVal VARCHAR(20),
                         DataVersion ROWVERSION
                        )
GO

--Fetch the currently active value of ROWVERSION
SELECT MIN_ACTIVE_ROWVERSION() AS MinActiveRowVersion

--Now, insert some test data
INSERT INTO SourceData (RowVal) VALUES ('Windows'),
                                       ('SQL Server'),
                                       ('BizTalk Server'),
                                       ('Exchange Server')
GO

SELECT RowId, RowVal, DataVersion
FROM SourceData

--Fetch the currently active value of ROWVERSION
SELECT MIN_ACTIVE_ROWVERSION() AS MinActiveRowVersion

Now, switch to another session connected to the same SQL Server instance, and execute the following query:

-- In a separate window, begin a transaction, insert some test data, but do not commit
-- Session 02
USE tempdb
GO
BEGIN TRANSACTION MinRowVersion
    --Fetch the currently active value of ROWVERSION
    SELECT MIN_ACTIVE_ROWVERSION() AS MinActiveRowVersion
    
    INSERT INTO SourceData (RowVal) VALUES ('System Center'),
                                           ('Office')
    
    SELECT RowId, RowVal, DataVersion
    FROM SourceData
    
    --Fetch the currently active value of ROWVERSION
    SELECT MIN_ACTIVE_ROWVERSION() AS MinActiveRowVersion
    
--Session 02 - We are NOT performing a COMMIT on the transaction
--COMMIT TRANSACTION MinRowVersion

Although the transaction inserts two (2) records, the MIN_ACTIVE_ROWVERSION() returns the same value as seen at the end of the statements executed in session #01. This is because the transaction in session #02 is not yet committed.

Committing the transaction in session #02 should now reflect the updated value of ROWVERSION column value.

--Session 02
COMMIT TRANSACTION MinRowVersion

--Return back to Session 01
--Session 01
--Fetch the currently active value of ROWVERSION
SELECT MIN_ACTIVE_ROWVERSION() AS MinActiveRowVersion

--Cleanup
IF OBJECT_ID('SourceData') IS NOT NULL
BEGIN
    DROP TABLE SourceData
END
GO

References:

• MIN_ACTIVE_ROWVERSION(): http://technet.microsoft.com/en-us/library/bb839514(v=sql.110).aspx

Until we meet next time,
Be courteous. Drive responsibly.

Recently, I was helping a team implement optimistic concurrency into their application. In my previous post (SQL Server – DDL – ROWVERSION v/s TIMESTAMP – a key difference), we reviewed a critical DDL difference with respect to table definition between the ROWVERSION and TIMESTAMP columns.

Many DBAs and developers believe that ROWVERSION is a unique value. This statement causes an interpretation that a table with a ROWVERSION column will always have unique values across the database.

The above statement is not accurate – the ROWVERSION column does get a new value during an insert, however, there is a possibility that ROWVERSION columns can hold duplicate values. This can happen during a bulk insert (SELECT…INTO).

Here’s a simple demonstration:

USE tempdb
GO

--Safety Check
IF OBJECT_ID('RowVersionDemo') IS NOT NULL
BEGIN
    DROP TABLE RowVersionDemo
END
GO

IF OBJECT_ID('SourceData') IS NOT NULL
BEGIN
    DROP TABLE SourceData
END
GO

--Create the demo (source) table
CREATE TABLE SourceData (RowId INT IDENTITY (1,1),
                         RowVal VARCHAR(20),
                         DataVersion ROWVERSION
                        )
GO

--Insert some test data
INSERT INTO SourceData (RowVal) VALUES ('Windows'),
                                       ('SQL Server'),
                                       ('BizTalk Server'),
                                       ('Exchange Server')

--Bulk Insert the source data into destination table
SELECT SourceData.RowId, SourceData.RowVal, SourceData.DataVersion
INTO RowVersionDemo FROM SourceData

--Select the data inserted (for demo purposes)
SELECT RowId, RowVal, DataVersion
FROM SourceData

SELECT RowId, RowVal, DataVersion
FROM RowVersionDemo
GO

--Check the data-types of the columns created as part of the BULK-INSERT:
SELECT isc.TABLE_NAME,
       isc.COLUMN_NAME,
       isc.DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS AS isc
WHERE isc.TABLE_NAME = 'RowVersionDemo'
GO

--Cleanup
IF OBJECT_ID('RowVersionDemo') IS NOT NULL
BEGIN
    DROP TABLE RowVersionDemo
END
GO

IF OBJECT_ID('SourceData') IS NOT NULL
BEGIN
    DROP TABLE SourceData
END
GO

We can see here that SQL Server inserted the column data “as-is” into the destination tables. A look at the data-types of the destination table shows us that it has been created as a “timestamp” column, which is a documented bug within Microsoft SQL Server.

In short, using ROWVERSION does not guarantee uniqueness – one can have duplicate values amongst the ROWVERSION columns within the same database.

References:

• Connect Cases related to ROWVERSION being displayed as “TIMESTAMP”
  • https://connect.microsoft.com/SQLServer/feedback/details/274333/deprecate-timestamp-the-keyword-not-rowversion-itself
  • https://connect.microsoft.com/SQLServer/feedback/details/530944/sql-2008-still-shows-timestamp-not-rowversion
• Optimistic Concurrency using ROWVERSION: http://beyondrelational.com/modules/2/blogs/77/posts/11268/optimistic-concurrency-a-conceptual-introduction.aspx

Until we meet next time,
Be courteous. Drive responsibly.