Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,153,599 hits

  • Select GETDATE()

    December 2025
    M T W T F S S
    1234567
    891011121314
    15161718192021
    22232425262728
    293031  

Posts Tagged ‘what’s new’

Next Entries »

SQL 2014 Learning Series # 13 – New Feature – Managed Lock Priority (Part 1)

Posted by blakhani on June 17, 2014


Imagine a situation where DBA wants to perform maintenance operations like online index rebuild [using ALTER INDEX…REBUILD (ONLINE=ON)] or SWITCH partition [using ALTER TABLE… SWITCH PARTITION] on a highly OLTP system. Due to kind of locks taken by these activities, it’s highly possible that there would be blocking. If DDL is running first then it would block user workload. If user workload is already running and we need to perform DDL then our query/operation would be blocked. This might not be a problem for partition switch because it’s normally quick but can be a problem which you might have seen during online index rebuild.

Let’s understand these operation in little more detail to understand the reason of this feature.

Partition switch is a metadata operation which means it has to update some system tables and setup appropriate links in system tables so that SQL Server knows that you have bought a staging table into an existing table. To achieve this, SQL Server needed to take Schema Modification (SCH-M) lock on the source and the target table. Since SCH-M lock is incompatible with Shared Lock (S), this can block production users activity. The converse is also true. If we have a very heavy OLTP system and the table is very hot in terms on workload, it’s possible that we may not get a windows to acquire the lock and partition switch has to wait. 

Online Index Rebuild (a.k.a. OIR) process requires table short table S lock at the beginning, in the build phase, where it need to take consistent snapshot of the table and create a version of the table. The version store is used to manage and create new index. After the index is built, in the last phase, process has to do a metadata changes (just like partition switch) where it needs SCH-M lock. This can be blocked or can block concurrent user workload depending on who came first. In order to execute the DDL statement for OIR, all active blocking transactions running on a particular table must be completed. Here also, converse is true.

In earlier version of SQL Server, there was no option to provide priority of lock taken by certain operation (except deadlock_priority). Now, we have option to provide our choice during online index rebuild and partition switch.

Kill all blockers – When we execute the DDL (either ALTER TABLE .. SWITCH PARTITON or ALTER INDEX… REBUILD (ONLINE=ON)), we can specify to kill all user session which are blocking the activity and start the DDL. This is typically a business decision depending on priority of user workload and time of the day. If we don’t want to kill the blockers immediately then we have an option to ask the operation to wait for certain time which can be specified by “MAX_DURATION” switch. The number provided after MAX_DURATION is number of minutes the DDL should wait before killing the sessions. At the end of the duration, if DDL process still cannot get a lock, it can go ahead and kill all user transactions. This fact might be intuitive but I must point out here – If we specify certain value in MAX_DURATION (let’s say 5 minutes) but there are no concurrent workload on the system taking conflicting locks, the request would be processes immediately (and will not wait for 5 minutes). Max value possible is 71,582 minutes (=49 days)

Switch to normal queue – This is the default behavior in SQL Server 2012 where DDL operation waits in the same lock queue where other transactions are waiting for lock. SQL Server lock manager is First In First Out (FIFO) model which means if we submit a lock request, it won’t be granted until the earlier requesters have been granted their lock and have been release also. This option means that if our DDL didn’t acquire the lock even after MAX_DURATION time is elapsed, it would switch to normal queue and wait behind existing user requests. This is default behavior.

Exit DDL after wait – This option is opposite to first option (kill all blockers). If business says that user workload is more important than these maintenance operation then DBA would pick this choice. As the name implies, the DDL (SWITCH/OIR) would wait till the MAX_DURATION and if the locks are not acquired for maintenance operation, the DDL would abort itself and end-user would get “Timeout Expired” message.

Using above three options, a DBA should be able to manager partition switch and online rebuild operation. This all the possible due to a new lock queue introduced in SQL Server 2014 called as “Low Priority Lock Queue” and that’s why it doesn’t interfere with regular user workload.

Below image shows that all SPIDs are waiting in same queue. SPID 53 is a maintenance activity which is having conflicting lock and hence waiting.

image

Below would be the situation with the new lock queue. As we can see SPID 54 is NOT waiting for SPID 53 (as compared to regular queue in earlier image)

image

 

Here is the syntax as shown in books online.

(ALTER TABLE) http://msdn.microsoft.com/en-us/library/ms190273(v=sql.120).aspx 
(ALTER INDEX) http://msdn.microsoft.com/en-us/library/ms188388(v=sql.120).aspx

image

Few points I must highlight:

  • MAX_DURATION is not the duration of the operation itself. It’s the max wait duration.
  • Specifying very high value in MAX_DURATION would not be advisable because it prevents the transaction log from truncating from the point the DDL was submitted until it is executed . 71,582 minutes (1,193 hours or 49 days) unless you have the transaction log drive space to support 49 days’ worth of transactions, you might want to keep the MAX_DURATION a bit lower.

In next part of this series, we would look at example and demo of MLP feature.

To look at complete list of blog on SQL 2014 Learning Series, please visit here

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

    • Posted in SQL 2014 Learning Series, SQL Server 2014 | Tagged: , , , , , | 2 Comments »

    SQL 2014 Learning Series # 3 – New Feature – Backup Encryption

    Posted by blakhani on April 3, 2014


    Before we jump into the new feature, lets have a look at behavior in previous version. Same simulation can be done on SQL Server 2014 as well.

    1. Create Database
    2. Create table in the database and insert some rows.
    3. Take backup of the database and check if we are able to see those rows in the backup.

    Here are the commands which we can run to achieve this.

    Create database [SQLServerHelp]
go
use [SQLServerHelp]
go
Create Table MyData (i int, j char(100));
go
insert into MyData values (1, 'See this data')
go
backup database SQLServerHelp to Disk = 'E:\Tools\SQLServerHelp.bak'
go

    I have opened the backup file with one of the text editor and I can search for the row which I inserted. Note that opening big file in text editors may take a lot of time and file may become unusable for SQL Server. PLEASE DON’T TRY THIS ON PRODCUTION DATABASE BACKUP. I have highlighted the row which was inserted via above script. (1, ‘See this data’)

    image

    The way to avoid above situation is that encrypt the database use TDE or encrypt the column using column encryption. Please read below

    http://technet.microsoft.com/en-us/library/cc278098(v=SQL.100).aspx (Database Encryption in SQL Server 2008 Enterprise Edition)

    There are some overhead involved with above techniques and you may want to make sure that only backup is encrypted and you are OK if MDF file has data in readable format. To solve this, SQL Server 2014 has new feature called backup encryption. You can read more on books online

    Let’s look at steps involved.

    Create database master key for the master database.

    USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SQLServer-Help@123';
GO

    Create a Backup Certificate

    Use Master
GO
CREATE CERTIFICATE BackupEncryptCert
   WITH SUBJECT = 'SQL Backup Encryption Certificate';
GO

     

    Take a backup: This can be done by SSMS UI or T-SQL

    image

    T-SQL

    BACKUP DATABASE [SQLServerHelp] TO  DISK = N'E:\Tools\Encrypted_SQLServerHelp.bak' WITH NOFORMAT, NOINIT,  
NAME = N'SQLServerHelp-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10, ENCRYPTION(ALGORITHM = AES_128, SERVER CERTIFICATE = [BackupEncryptCert])
GO

    Here is the message which we can see as output in SSMS when we run T-SQL.

    image

    Here is the complete text of highlighted warning.

    “Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.”

    It’s clear that to restore this backup, we need to backup certificate. Going back to original problem, I tried searching for the text in new backup file but can’t find it. If you look at screenshot, it’s clear that it’s more unreadable.

    image

    As Vinod (b|t) mentioned in his blog, we can see complete message in ERRORLOG (this is also an enhancement in SQL Server 2014)

    2014-04-03 06:36:42.780 Backup       Database backed up. Database: SQLServerHelp, creation date(time): 2014/04/01(10:15:50), pages dumped: 307, first LSN: 34:24:37, last LSN: 34:56:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘E:\Tools\SQLServerHelp.bak’}). This is a

    2014-04-03 06:36:42.810 Backup       BACKUP DATABASE successfully processed 298 pages in 0.212 seconds (10.981 MB/sec).

    2014-04-03 07:25:49.220 Backup       Database backed up. Database: SQLServerHelp, creation date(time): 2014/04/01(10:15:50), pages dumped: 307, first LSN: 34:168:37, last LSN: 34:200:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘E:\Tools\Encrypted_SQLServerHelp.bak’}

    2014-04-03 07:25:49.240 Backup       BACKUP DATABASE successfully processed 298 pages in 0.228 seconds (10.211 MB/sec).

    It’s clear that second one took little more time. I am guessing this is because of encryption as all other factors are same.

    Hope you have learned something new today!

    Edit (9 April 2014) – Vinod (b|t) wrote a blog on restoring such backups. http://blogs.extremeexperts.com/2014/04/09/sql-server-2014-restoring-encrypted-backups/

     

     

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

    • Posted in SQL 2014 Learning Series, SQL Server 2014 | Tagged: , , , , , | 1 Comment »

    SQL 2014 Learning Series # 2 – New Permission – Select All User Securables

    Posted by blakhani on April 1, 2014


    In my last post of SQL 2014 Learning series I have explained “Connect any database” permission. As I mentioned, this permission does not grant any permission in any database beyond connect. Someone might think, what’s the use if they are just able to expand the database but not see any database object? Here is the screenshot (which I showed in earlier blog)

    As we can see above, database can be expanded but tables are not visible. The real usage of the “Connect any database” permission can be seen if it’s combined with “Select All User Securables”. You may ask, what are the situations when DBA need to give those two permissions? Imagine a situation where an auditor has been appointed to audit the databases. If you have to created auditor account (Balmukund in my example), a long way would be to create login, give permission to database and then permission to select individual tables. This permission “SELECT ALL USERS SECURABLES” is designed  to allow a user to view data in database where he can connect.

    Let me give this permission to Balmukund login. I can either use Management Studio Interface or T-SQL.

    image

    use [master]
GO
GRANT SELECT ALL USER SECURABLES TO [Balmukund]
GO

    As soon as permissions are given to Balmukund, he would be able to select data from all table in database.

    image

    If you notice closely, there is a lock icon on dbo.v1 under “Views”. This means that “Balmukund” would not be able to see the definition of the view. His permissions are limited to perform Select on tables and views.

    Good part about this permission is that even if we create new tables in the database or even a new database, the permissions are available on new objects as well. This means that DBA need not keep giving permission on newly created table and he can have sound sleep. After taking above screenshot, I created new database (yeah, that’s the name as well) and a new table. Select permissions are automatically available to Balmukund.

    image

    Hope this post was helpful.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

    • Posted in SQL 2014 Learning Series, SQL Server 2014 | Tagged: , , | 3 Comments »

    Next Entries »