PREEMPTIVE_OS_AUTHORIZATIONOPS waits in SQL Server

SQL Server threads which are controlled by SOS (SQL Server operating system) are Non preemptive but at times they switch preemptive when they can’t obey the rules of SOS. Some common places when SOS thread is switched preemptive are when we call extended proc’s, few Windows API etc.

 

Let us assume you use “execute as user x” in your job, SQL Server calls Windows functions like LookupAccountName  to get the credential of user. Windows functions interacts with AD services to get the credentials of account and return the info to the caller in SQL Server process and then SQL Server would build the logintoken. If there is a delay in AD and if it takes long time to respond to the windows function calls other threads in the same scheduler would get blocked so SQL Server thread would switch preemptive (Doesn’t follow SOS rules) before  making these  function  calls. PREEMPTIVE_OS_AUTHORIZATIONOPS wait type would occur when a thread is waiting on such windows functions (security) to return, So first thing which would have to do is to fix the performance of AD calls.

 

To narrow down and prove that this issue occurs because of Active directory performance. Login to SQL server using the startup account of SQL Server and execute below query when you notice PREEMPTIVE_OS_AUTHORIZATIONOPS wait type and compare the times printed. It will give you the time it takes for SQL Server to complete the AD calls.

 

 

create procedure PREEMPTIVEOSAUTHORIZATIONOPS  with execute as self

as

set nocount on

select CONVERT(varchar, getdate(), 126) PREEMPTIVEOSAUTHORIZATIONOPS

go

print convert(varchar, getdate(), 126)

exec dbo.PREEMPTIVEOSAUTHORIZATIONOPS;    

print convert(varchar, getdate(), 126)

go

SQL Server Backup compression

How to do Backup compression in SQL Server?

The backup compression  option determines whether SQL Server creates compressed or uncompressed backups .Backup compression option is off by default in SQL Server.The default behavior  can be modified by sp_configure option “backup compression default” .

Syntax:

USE master;
GO
EXEC sp_configure ‘backup compression default’, ‘1’;
RECONFIGURE WITH OVERRIDE;

To override the backup compression:

You can change the backup compression behavior for an individual backup by using WITH NO_COMPRESSION or WITH COMPRESSION in a BACKUP statement. We cannot take compressed and non-compressed backups on the same file. If we take COMPRESSION  backup on a file were  already non-compressed backup has taken,error will be shown.So we have to use different files for compressed and uncompressed backup.

Example to take backup for AdventureWorks with NO_COMPRESSION:

BACKUP DATABASE [AdventureWorks] TO  DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\uncompressed.bak’ WITH NO_COMPRESSION
GO

Example to take backup for AdventureWorks WITH COMPRESSION:

BACKUP DATABASE [AdventureWorks] TO  DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\compressed.bak’ WITH COMPRESSION
GO

To calculate the compression ratio of a backup:

After taking backup with compression and without compression the backup_size can be compared to see the difference.

Syntax:

select backup_size,compressed_backup_size,100- ((compressed_backup_size/backup_size)*100) as “compressed %”   from msdb..backupset

 image

Types of isolation levels in SQL Server

The ISO standard defines the following isolation levels in SQL Server Database Engine:  

Microsoft SQL Server supports these transaction isolation levels:

Read Committed

SQL Server acquires a share lock while reading a row into a cursor but frees the lock immediately after reading the row. Because shared lock requests are blocked by an exclusive lock, a cursor is prevented from reading a row that another task has updated but not yet committed. Read committed is the default isolation level setting for both SQL Server and ODBC.

Read Uncommitted

SQL Server requests no locks while reading a row into a cursor and honors no exclusive locks. Cursors can be populated with values that have already been updated but not yet committed. The user is bypassing all of the locking transaction control mechanisms in SQL Server.

Repeatable Read 

SQL Server requests a shared lock on each row as it is read into the cursor as in READ COMMITTED, but if the cursor is opened within a transaction, the shared locks are held until the end of the transaction instead of being freed after the row is read. So phantom rows are This has the same effect as specifying HOLDLOCK on a SELECT statement.

(Phantom read:Phantom reads occurs when an insert or delete action is performed against a row that is being read by a transaction.The second  transaction read shows a row that did not exist in the original read as the result of an insertion by a different transaction or due to deletion operation some rows  doesn’t appear)

Serializable

     In serializable read phantom reads are not allowed because while the first transaction is in progress other transaction wont execute.

Snapshot

SQL Server requests no locks while reading a row into a cursor and honors no exclusive locks. Cursor is populated with the values as of the time when the transaction first started. Scroll locks are still requested regardless of use of snapshot isolation.

 

Read uncommitted example: Uncommitted Read allows your transaction to read any data that is currently on a data page, whether that has been committed or not. For example,another user might have a transaction in progress that has updated data, and even though it’s holding exclusive locks on the data, your transaction can read it anyway.

image

If we execute the select query before the update transaction gets committed,it will not wait for the update transaction to commits.Query will be executed immediately without any time lapse.

image

Read Committed example

Read committed allows your transaction to read only if the data is committed.Read Committed operation never reads data that another application has changed but not yet committed.

image

If we execute the select query before the update transaction gets committed,it will wait till the update transaction gets committed.

image

Repeatable Read example :In Repeatable Read issuing the same query twice within a transaction will not make any changes to data values made by another user’s transaction.Repeatable Read allows phantom reads(Data getting changed in current transaction by other transactions is called Phantom Reads).So phantom rows will appear.

image

While the transaction(first query) is in progress,repeatable read allows another transaction(second query) to execute.It means it allow phantom reads.So second transaction(second query),need not wait till first transaction(first query) completes.Here values will be added before first query completes.

image

Serializable example : The Serializable isolation level adds to the properties of Repeatable Read by ensuring that if a query is reissued, rows will not have been added in the table. In other words, phantoms will not appear if the same query is issued twice within a transaction.

image

While the transaction(first query) is in progress,serializable read does not  allow another transaction(second query),It means it don’t allow phantom reads.So second transaction(second query), must wait till first transaction(first query) completes.Here values will be added only after first query completes.

image

snapshot example : To use the snapshot isolation level you need to enable it on the database by running the following command

ALTER DATABASE DBname
SET ALLOW_SNAPSHOT_ISOLATION ON

SQL Server database snapshot

What is the use of SQL Server database snapshot and how to create SQL Server database snapshot?

Database snapshots are available only in SQL Server 2005 Enterprise Edition and later versions. All recovery models support database snapshots.

A database snapshot is a read-only, static view of the source database.Multiple snapshots can exist on a source database and always reside on the same server instance as the database. Each database snapshot is transactionally consistent with the source database as of the moment of the snapshot’s creation.A snapshot persists until it is explicitly dropped by the database owner.

Snapshots can be used for reporting purposes.In the event of a user error on a source database, you can revert the source database to the state it was in when the snapshot was created. Data loss is confined to updates to the database since the snapshot’s creation.

Creating a series of snapshots over time captures sequential snapshots of the source database. Each snapshot exist until it is dropped.Each snapshot will continue to grow as  pages in original database are updated, you may want to conserve disk space by deleting an older snapshot after creating a new snapshot.

Steps to create database snapshot in SQL Server:

1.Create a new database or use existing database to create the database snapshots.

Syntax:

USE master
GO
— Create database
CREATE DATABASE test
GO
USE test
GO

2.Create table  and Insert values into table.

Syntax:

–create a Table
CREATE TABLE test1 (a INT, b INT,c INT)
—Insert values
declare @a nchar(10)
set @a=1
while (@a<5)
begin
insert into test1 values (@a,’1′,’1′)
set @a=@a+1
end

3.Create a snapshot  for a source database.

Syntax:

— Create Snapshot Database
CREATE DATABASE Snapshottest ON
(Name =’test’,
FileName=’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\testsnap.ss’)
AS SNAPSHOT OF test;
GO

image

Snapshottest database is created successfully.

4.Now let us view both the source database and snapshot database.

Syntax:

SELECT * FROM test.dbo.test1;
Go
SELECT * FROM Snapshottest.dbo.test1;
Go

Data’s in tables of source and snapshot database are same.

 image

Size on disk  of the testsnap .ss could be viewed by opening its properties window.Let us note the  size on disk  of the source database.

image

5.Let us update existing rows in the table.

Syntax:

update test1 set a=0,b=0,c=0

6.After updating rows ,let us view the source and snapshot database.The values are updated only in source database,not in snapshot database.Because snapshot database is consistent with the source database as of the moment of the snapshot’s creation.

Syntax:

— Select from test and Snapshottest Database
SELECT * FROM test.dbo.test1;
SELECT * FROM Snapshottest.dbo.test1;
GO

image 

But size on disk is increased,It clearly shows that when we update data in Source database, it copies the old/existing data pages to Snapshot database. This is the reason why the size of the snapshot database is increasing while updating the source database.

image

7.We could revert the source database.Reverting overwrites updates made to the source database since the snapshot was created by copying the pages from the sparse files back into the source database.

Syntax:

— Restore old data from Snapshot Database
USE master
GO
RESTORE DATABASE test
FROM DATABASE_SNAPSHOT = ‘Snapshottest’;

8.View the data in the tables from the source and the snapshot database after the restore from snapshot.

Syntax:

SELECT * FROM test.dbo.test1;
Go
SELECT * FROM Snapshottest.dbo.test1;
Go

image

9.We drop the snapshot database like any other database using drop database command.

Syntax:

— drop snapshottest
DROP DATABASE [snapshottest];

10. We can also create database snapshot on mirror database for load balancing.

How to create table with filestream column and Insert data

How to create table with filestream column and Insert data?

Filestream data type in SQL Server  can be used to store images,documents,etc., in database.

In this blog I will explain how to create table with filestream data type.

Before creating table with filestream  column,we have to enable filestream feature in SQL Server configuration manager.Follow “How to enable and configure Filestream in SQL SERVER 2008 / 2012

Create a table and adding filestream data:

1.Create Filestream enabled database.

Syntax:

CREATE DATABASE test
ON
PRIMARY ( NAME = test1,
    FILENAME = ‘c:\data\testdat1.mdf’),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = test3,
    FILENAME = ‘c:\data\test1’)
LOG ON  ( NAME = testlog1,
    FILENAME = ‘c:\data\test1.ldf’)
GO

2.After you have a filestream enabled database.we now need to create a table and add data to it.

Syntax:

CREATE TABLE [dbo].[test](
   [ID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
   [Number] VARCHAR(20),
   [Description] VARCHAR(50),
   [Image] VARBINARY(MAX) FILESTREAM NULL
)

3.Insert values into table. In below example I am inserting image file in to the table. 

Syntax:

DECLARE @img AS VARBINARY(MAX)
— Load the image data
SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX))
      FROM OPENROWSET(Bulk  ‘C:\image\jellyfish.jpg’, SINGLE_BLOB ) AS y
— Insert the data to the table         
INSERT INTO test (ID,Number,Description, Image)
SELECT NEWID(), ‘MS1001′,’jellyfish’, @img

clip_image001[8]

4.To view the table with filestream included column

image

NON CLUSTERED COLUMNSTORE INDEX

What is non clustered column store index in SQL Server?

Column store index is a new feature introduced in SQL Sever 2012.In Non clustered column store index data is stored as column.Column order is not important in column store index.In this non clustered column store index we don’t have traditional row execution mode instead of that,we have batch execution mode.This improves query execution.Batch execution mode  executes several rows at a time as a batch.This reduces CPU consumption.

In column store execution, only columns that query needs will be read.This reduces I/O and memory utilization.

We cant have Unique,Primary or Foreign key constraints in column store index and also it does not have feature like INCLUDE.Once we have created the Non clustered column store index for a table,we can’t update the table with new values because the table is READ-ONLY.We could have only one non clustered column store index.Column store index could have only 1024 columns.And we don’t have clustered column store index only Non clustered is available.

This column store index uses the benefits of segment elimination based on some conditions.It gives faster performance for common data warehousing queries.

Create a Column Store Index in SQL Server by using below steps.

Using GUI:

1.In Object Explorer, expand Tables, right click -> Indexes and click New Index. Select non clustered column store index .

 

image

2.Add the column store columns.

image

  •   select the table column to be added to the index.

 

image

  • Selected Column store column will be added.

 

image

3.Set the options by selecting options from select page.In that give the value for Max degree of parallelism as greater than or equal to (2) to have batch execution.

Set other extended properties you need by clicking extended properties node.

 

image

4.Non clustered column store index is successfully created.

image

USING T-SQL:

NONCLUSTERED COLUMNSTORE index without options:

Create database product;

Use product;

CREATE TABLE Test

(ID [int] NOT NULL,

RecDate [int] NOT NULL,

DelDate [int] NOT NULL,

ExtDate[int] NOT NULL);

GO

CREATE CLUSTERED INDEX cl_test ON Test (ID);

GO

CREATE NONCLUSTERED COLUMNSTORE INDEX csindex_test ON Test (RecDate, DelDate, ExtDate);

GO                                

                                                (or)

NONCLUSTERED COLUMNSTORE index with options:

CREATE NONCLUSTERED COLUMNSTORE INDEX csindex_test ON Test

(RecDate, DelDate, ExtDate)

WITH

(DROP_EXISTING = ON, MAXDOP = 2)

ON “default”

GO

image

How to create clustered and non-clustered index

Index is a database object, which can be created on one or more columns(max 16 columns).The index will improve the performance of data retrieval and adding.indexes are created in an existing table to locate rows quickly and efficiently.

What is clustered index?

Table can have only one clustered index.In clustered index data’s in table is sorted in particular order based on index keys(either AESC/DESC).In clustered index page chain that holds data pages is also sorted in same order as index keys.So,SQL server follows the page chain in order to retrieve the data rows.By this new rows could be added just by adjusting the links in the page chain without moving entire pages.The leaf nodes of a clustered index contains the data pages with index keys.
What is Non clustered index?
For a table we could have more than one non clustered index because it doesn’t affect data pages organization.The leaf node of a non clustered index does not consist of data pages. Instead, the leaf nodes contain index rows.If we have both the clustered index and non clustered index for a table then index row will have Clustered index key columns that point to the data row. If there is no clustered index then the index row contains Non-Clustered index key columns which is stored along with row locator (or)  row identifier.The pointer from an index row to a data row is called a row locator.
Both clustered and non clustered indexes can be unique. That is no two rows can have the same value for the index key. Also we have one special type of index called Non clustered column store index.
 
Creating a clustered index  using Object Explorer:
1.In Object Explorer, expand the table for which you want to create a clustered index.

image

2.Following window will allow as to select the key columns for index.

image

3.We could add other options by selecting options node from select page.

 

image

4.Click ok.Clustered index will be created.

Create Non-Clustered index using object explorer:

1.Similarly Select Non clustered index from New index.Select the Key columns for an index.

image

2.In non clustered index we could add included columns for your index.In order to overcome existing index limits.After including needed included columns ,Click ok.

image

3.Similarly other options could be added by selecting different nodes from select page.

4.Click ok .Index will be created.