Restoring MSDB Database

One of the primary reasons to take backups is to protect from accidentally data deletion and data corruption.

But, what happens when a SQL Server job is accidentally deleted? MSDB database contains all the information about backups,SQL Server Agent Jobs,Alerts,Operators..etc. So, the deleted job can be recovered by restoring MSDB database.

To restore the MSDB database, the server does not need to be in Single User Mode.To restore Master database, the server needs to be in single user mode and I explained it in this blog.

So, to restore MSDB, make sure you can get exclusive access on the database.Remember, MSDB is a system DB and is inherently used for many purposes such as maintaining job history,job step logs,notifications,database mail, alerts..etc. So, it may be better to stop SQL Server Agent while restoring MSDB database.To restore MSDB database:

Use Master
GO
Restore Database MSDB from disk='E:\SQL_Backup\Msdb.bak' with replace

One other alternative is to set the MSDB database in read only mode(so as, no new data is written but applications can continue to read data) and restore MSDB database as user database and then stop the SQL Server Service account and copy data and log files to where MSDB files are located and rename them as MSDB files and restart the SQL Service. This might not be possible in all cases as it involves restarting the SQL service.

Snapshot Isolation

To run transactions in snapshot isolation mode, first, enable snapshot isolation level on the database and then set the isolation level on the transaction. To enable option snapshot isolation on the database:
Alter Database <<DatabaseName>>
Set allow_snapshot_isolation on

IF Exists(Select 1 from sys.objects where name='foobar' and type='U')
Begin
Drop Table foobar
End
Create Table foobar (sno int,sname varchar(20))
Insert into foobar Values (1,'Spurs'),(2,'Rockets'),(3,'Pelicans')

--Session 1
Begin Tran
Update foobar set sname='Lakers' where sno=2
waitfor delay '00:00:05'
commit

/*Retrieves only committed data from the table.Writers do not block readers.So, as long as the txn in session 1 is committed before the select statement in session 2 runs,the updated value is returned. If the session 1 txn is not committed by the time select in session 2 runs,it will return the initial values. But data read will always be consistent inside the transaction,even if it is changed another transaction.*/
--Session 2
Set Transaction Isolation Level Snapshot
Begin Tran
Select * from foobar
waitfor delay '00:00:05'
Select * from foobar
Commit

/*CON:Update conflicts can occur when the data that the current transaction is trying to update was modified by another transaction*/
--Session 1
Set Transaction Isolation Level Snapshot
Begin Tran
Select * from foobar
waitfor delay '00:00:05'

--Data read in the same transaction will always be consistent.
select * from foobar
--update conflict occurs,since the data was changed by another transaction.
Update foobar set sname='Heat' where sno=2
select * from foobar
Commit

--Session 2
Begin Tran
Update foobar set sname='Hawks' where sno=2
commit

Read Committed Snapshot Isolation

If the transaction isolation level is ‘Read Committed‘ and the database has Read Committed Snapshot enabled, then the transactions automatically run under “Read Committed Snapshot Isolation“. To Enable the option on database:

Alter Database <<DatabaseName>>
Set read_committed_snapshot on
with RollBack Immediate

If Exists(Select 1 from sys.objects where name='foobar' and type='U')
Begin
Drop Table foobar
End
Create Table foobar (sno int,sname varchar(20))
Insert into foobar Values (1,'Spurs'),(2,'Rockets'),(3,'Pelicans')

--Session 1
Set Transaction Isolation Level Read Committed
Begin Tran
Select * from foobar
waitfor delay '00:00:05'

--This will read the data committed in session 2
select * from foobar
--updates the data
Update foobar set sname='Lakers' where sno=2
select * from foobar
Commit

--Session 2
Set Transaction Isolation Level Read Committed
Begin Tran
Update foobar set sname='Clippers' where sno=2
commit

Licensing SQL Server

Understanding sql server licensing is very important for planning upgrades or building new servers. This blog does not cover every licensing scenario but should help understanding the general basics of SQL 2012/14 license options.

There are two types of license models available 1. Core based license 2. Server + cal license.

Microsoft implemented core based license from sql 2012 and is the only option to buy enterprise edition. While other editions such as standard is available in both the above options. In sql 2008, it used to be socket based license.

Another key point is the core form factor chart. Microsoft has assigned form factor value based on the number of cores in the physical processor and this should be multiplied with total number of cores while determining the licenses you need.It should be rounded to the nearest higher integer,if the value is in decimal.The core form factor chart can be downloaded here.

Another important requirement is minimum of 4 core licenses per processor. That means, even if the physical processor has less than 4 cores, you still need to buy 4 licenses. This is something that’s important to remember while doing in place upgrades as most new processors have 4 cores at minimum.

The formula to calculate licenses:
No of physical processors * No of cores per processor * Core form factor

Scenario: I have a new server that has 4 quad core processors. If I want to install sql 2012 enterprise edition as a stand alone instance on the physical box, i need to buy 16(4*4*1)licenses.

Consider the same scenario but with dual cores, we would still need 16 licenses because the minimum per core license is 4. This means we are spending more money for less benefit. That’s why planning is important.

Also, all the available physical processors have to be licensed. In the above example,you cannot license just two of the processors(8 cores),all 4(16 cores) have to be licensed.

However, there are couple of workarounds for this
1. Disable the two processors in bios settings
2. Install VM and assign just two processors for it.

I will cover other types of licensing models in another blog post.

Hardware Basics

Understanding hardware basics is equally important for DBA as the performance and licensing costs of SQL Server depend on it and in this blog, we will see the basics on processors.

Sockets = A socket is a connector that connects the physical CPU to the PCU board. The PCU Board can contain multiple sockets. The maximum no of physical CPU’s a computer can have is the total no of sockets you have on the PCU board.

Processor Cores = Processor Core is integral part of the physical processor and it does the actual work. Most modern physical processors have multiple cores.Dual core means it has two cores inside the physical processor while quad core means it has 4.

Hyper-thread = When enabled, each core processors splits into 2 logical processors. Per my knowledge, the additional processor can help by reducing the processor wait times by pre-fetching the data for the next execution.

So,to calculate the total no of processors:
No.of.Sockets*No.of Cores* Hyper-threading enabled
If you have Hyper-threading, it will be called “logical” processors.

My workstation is Intel Xenon W3530 @2.80 GHZ. It has one socket with 4 cores and hyper threading is enabled.So,using the formula above,there are total of 8(1*4*2) logical processors.

This can be verified using a simple method.Go to RUN(Windows key+R) and type ‘msinfo32’. In the system summary, you can see the processors,cores and logical processors on the server.

Alter Database Set Options Using NO_WAIT,ROLLBACK IMMEDIATE,ROLLBACK AFTER

When altering database SET options such as snapshot isolation,read committed snapshot, we can specify options like No_wait;Rollback Immediate;Rollback after n

Example:
ALTER DATABASE AdventureWorks2012
SET SNAPSHOT ISOLATION ON WITH ROLLBACK IMMEDIATE

Rollback Immediate – Rollbacks all the uncommitted transactions immediately and then alters the database setting.

Example:
ALTER DATABASE AdventureWorks2012
SET SNAPSHOT ISOLATION ON WITH ROLLBACK AFTER 10

ROLLBACK AFTER N – Waits for N seconds and then rollbacks all the uncommitted transactions after that,in the example,it will wait for 10 seconds and rollbacks all the uncommitted transactions and then alters the database setting.

Example:
ALTER DATABASE AdventureWorks2012 SET SNAPSHOT ISOLATION ON WITH NO_WAIT

NO_WAIT – Will try to alter the database immediately and will fail, if it could not apply them.

STANDARD ALTER COMMAND – We can also write the Alter Database syntax without mentioning the above options but behaves similar to NO_WAIT option.

You can read more in this MSDN article.

Index Selectivity – Left Based

There are several types of indexes available but I will be referring to only clustered and non clustered indexes.

Clustered Index is a type of index where the logical and physical order of the table records are in the same order.

Non Clustered Index is a type of index where the logical and physical order of the table records are NOT in the same order. This index typically consists of specific columns defined in the index creation unlike clustered index which applies to the entire base table.

You can refer to BOL link here

There can only be one clustered index and up to 249 non clustered indexes on the table. All non clustered indexes will also include either cluster index key or ROWID depending on whether the table is clustered or heap that act as pointer back to base table. This pointer helps in creating a “look up” if additional columns are need to be fetched from the base table by the query.

The basic purpose of Indexes is to improve the performance of SQL Query and hence the design of indexes is very important as selectivity of index depends on how well the index and query logic is defined.

Query optimizer uses indexes and performs either scan or seek operation on them to fetch the data.Generally, seek is better for fetching single rows and small subset of rows,scan is better for pulling large set of rows.

While it is possible to tell the query optimizer to either use seek or scan using query hints, it is generally best practice to leave it up to the query optimizer and better investigate further to see why the optimizer is using not-so optimal plan(parameter sniffing,statistics update etc). It is also important to look the index definitions and the way queries are begin written.

I am using Adventure Works 2012 for this example,and you can download it from here.In AdventureWorks2012 database,there is a table called Person.Person and it has non clustered non unique index on LastName,FirstName,MiddleName.

Consider the below example:

Select * from Person.Person where FirstName='Gail' and MiddleName='A'
This performs a non clustered index scan but since the query returns only very small subset of data, an index seek would be an ideal operation here. The reason to do index scan is because the index is defined over LastName,FirstName and MiddleName but the query predicate is filtering only on FirstName and MiddleName. The Execution plan looks like this:

NC_IndexScan

Select * from Person.Person where LastName='Erickson' and MiddleName='A' This leads to a non clustered index seek which is an optimal plan.

NC_IndexSeek

So,the take away from this blog is if the left most index columns are used in the query predicate, the query optimizer comes up with an optimal plan. If the left the most column is not used in the query predicate, it will always lead to index scan whether it is optimal or not. That’s why it is important to have right indexes defined so the query optimizer can make best use of the index.

Optimize for Ad Hoc WorkLoads

“Optimize for ad hoc workloads” is a server level setting introduced in SQL Server 2008. The main reason to have this option is to prevent “plan cache bloating” problem due to one time used ad hoc queries.

For any query that runs on SQL Server, query optimizer creates a plan. It first checks if there is an existing plan in the cache,if so, it will use that plan else will create a new plan. The generation of plan consumes CPU as it has to compile the code and come up with best cost based query plan.The generated query plan will then be stored in the plan cache which takes some memory. Now, when the server gets lot of these ad hoc queries,it will take significant part of the server memory. This particularly becomes disadvantage when these queries are not necessary used more than once (referred as ‘plan cache bloating’).

So, when ‘optimize for ad hoc workloads’ is set to 1,it will store plan stub instead of query plan. This plan stub is significantly smaller in size compared to query plan. This is referred as  ‘Complied plan Stub’ cacheobjtype in sys.dm_exec_cached plan. But when the same query is run again(second run), it will store the actual query plan.  This means that the code has to be compiled again on the second run – which could be a disadvantage but generally is acceptable in many situations.

Ad hoc queries are the queries that are run without using parameters. Depending on the nature of the ad hoc query,even in simple parameterization,SQL Server can either parameterise the query that is deemed as “Safe” or will pass the literal values to the query optimizer,if the query is not deemed as safe by the optimizer.

Configuring “Optimize for ad hoc workloads” option

sp_configure 'show advanced options',1
GO
Reconfigure with Override

sp_configure 'optimize for ad hoc workloads',1
GO
Reconfigure With Override

If exists (Select 1 from sys.tables where name='student')
Begin
Drop Table Student
End
Create Table Student(sno int identity(1,1) Primary Key,Slname varchar(20))
Go
Insert into Student(slname)
Values('Herendaz'),('Schulz'),('McKnight'),('Lavenger'),('Smith')

–even though this is ad hoc query,query optimizer considers this as “safe” and parameterize the query
Select * from [dbo].[Student] where sno=5
If you check the execution plan, you would see something like this :
SimpleQuery_Parameterized

For simple queries like this, ‘optimize for ad hoc work loads’ setting does not have any impact and it will store the actual query plan as the query optimizer considers them as parameterized query. By default,SQL Server will try to parameterize the queries that it considers to be “safe”, even in simple parameterization.

The below query gives you an idea on how the query plan is stored in the plan cache. In this example, it is considered as ‘Prepared’ and not ‘ad hoc’.

Select usecounts,cacheobjtype,objtype,size_in_bytes,text
from sys.dm_exec_cached_plans a Cross Apply sys.dm_exec_sql_text(a.plan_handle)
Left Outer Join sys.dm_exec_query_stats b on a.plan_handle=b.plan_handle

SimpleQuery_Parameterized#2

For queries that SQL Server implicitly will not parameterize,it will store ‘complied plan stub’ for the first run and for the second run, it will store the actual query plan.
–query optimizer does not consider this as “safe” and hence does not parameterize this query
Select * from [dbo].[Student] where sno=4 and isnull(slname,'Unknown')='Lavenger'

The execution plan looks like this:
SimpleQuery_NotParameterized#2
Also, using the below query, you can find how the query plan is saved as.
Select usecounts,cacheobjtype,objtype,size_in_bytes,text
from sys.dm_exec_cached_plans a Cross Apply sys.dm_exec_sql_text(a.plan_handle)
Left Outer Join sys.dm_exec_query_stats b on a.plan_handle=b.plan_handle

For First Run:
SimpleQuery_Parameterized#2
For Second Run:
ComplexQuery_NotParameterized#3
You can see the difference in cacheobjtype and size for both first and second runs. The use count for second run shows as 1,because the query plan is recreated.

The database level setting on “parameterization” will impact the decision on whether it would parameterise only the ad hoc queries that it considers are “safe”(simple parameterization) or will it parameterize all the ad hoc queries (Forced Parameterization).

If forced parameterization is set for the database, then the main ad hoc query is parameterised but all the shell executions will have ‘complied plan stub’ for first execution.

I do not think there is an option in SQL server that tells on what run to store the query plan. When ‘optimize for ad hoc workload’ is enabled, SQL server, by default, stores the compiled plan for the second run. I wonder what would be the reason in not making this as user specified option.

Rollback does not reset/reuse the identity property / sequence value.

/*USING Identity property*/
If Exists(Select 1 from sys.tables where name='SIdent')
Begin
Drop Table SIdent
End
Create Table SIdent(sno int identity(1,10) Primary Key,Sname varchar(20))
GO

Begin Transaction
Insert into SIdent(sname) Values ('Wizards')
Rollback
GO
Begin Transaction
Insert into SIdent(sname) Values ('Wizards')
Commit
Go
-- first value is 11 and not 1 because rollback does not reset the Identity value.
Select min(sno) as [IdentityValue] from SIdent
Go

IdentityValue
/*USING SEQUENCE – SEQUENCE is a new TSQL functionality available from SQL Server 2012*/

If Exists(Select 1 from sys.tables where name='STest')
Begin
Drop Table STest
End
If Exists(Select 1 from sys.sequences where name='SeqVal')
Begin
Drop Sequence SeqVal
End
Create table STest(sno int primary key,sname varchar(20))
Create Sequence SeqVal as int
Start with 1
Increment by 10
Minvalue 1
Maxvalue 100
No Cycle

Begin Transaction
Insert into STest(sno,sname)
Select next value for SeqVal,'Cowboys'
RollBack
Begin Transaction
Insert into STest(sno,sname)
Select next value for SeqVal,'Steelers'
Commit
-- first value is 11 and not 1 because rollback does not reset the Sequence value.
Select min(sno) as [SequenceValue] from Stest

Sequence

Repeatable Read Isolation

IF Exists(Select 1 from sys.objects where name='foobar' and type='U')
Begin
Drop Table foobar
End
Create Table foobar (sno int,sname varchar(20))
Insert into foobar
Values(1,'Spurs'),(2,'Rockets'),(3,'Pelicans')


/*PRO:Repeatable Read does not allow Phantom Reads.*/
--Session 1

Set Transaction Isolation Level Repeatable Read
Begin Tran
Select * from Foobar
Waitfor Delay '00:00:05'
Select * from Foobar
--No Phantom Reads
Commit
--Session 2

Set Transaction Isolation Level Repeatable Read
Begin Tran
Update foobar set sname='Bobcats' where sno=3
Commit

/*CON:Repeatable Read allows Phantom Inserts.*/
--Session 1

Set Transaction Isolation Level Repeatable Read
Begin Tran
Select * from Foobar
Waitfor Delay '00:00:05'
Select * from Foobar
--Phantom Inserts
commit
--Session 2

Set Transaction Isolation Level Repeatable Read
Begin Tran
Insert into foobar Values(4,'Suns')
Commit