Whats New 2014

Hey all,

Continue to my whats New series,  I will try to write blog a day for whats new is 2014 as well. I am going through its new features and the more I am reading it it impresses me a lot. and I am feeling that this is it. I really like it.

Following are some of the features which I may go much dipper on but I think I need some time to do so.

Initially When I heart about 2014 I though it has on in-Memory as a new feature later realize that it has much more(will try to add more during my learning):

sql server 2014:”
1 >> Memory-optimized tables (formerly known as Hekaton) :are available in Enterprise editions, as well as evlauation and developer editions. In-Memory OLTP is a high performance, memory-optimized engine
2 >>Memory-Optimized Table checkpoint:The automatic checkpoint for memory-optimized tables runs after the log increases 512MB after the previous checkpoint.

3 >>Buffer Pool Extension :Enterprise/ Business Intelligence/ Standard(64 bit)
Buffer Pool Extension Edition:The Buffer Pool Extension feature is supported in Enterprise, standard, Evaluation , and Developer editions only.
The Buffer Pool Extension (BPE) feature of SQL Server 2014 allows SSDs to be used as additional buffer pool memory.
4>> Database Backup Encryption :
Starting in SQL Server 2014, SQL Server has the ability to encrypt the data while creating a backup.


5 >> Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator  (trace flag 9481 )setting trace flag 9481
The first major redesign of the SQL Server Query Optimizer cardinality estimation process since version 7.0.  The goal for the redesign was to improve accuracy, consistency and supportability of key areas within the cardinality estimation process,

XML showplan text

Select TOP (tie/percent) gives worse performance :resolves using new Cardinality Estimator

6 >>SQL Server Backup to URL

SQL Server Backup to URL was introduced in SQL Server 2012 SP1 CU2 supported only by Transact-SQL, PowerShell and SMO. In SQL Server 2014 you can use SQL Server Management Studio to backup to or restore from Windows Azure Blob storage service

7>> The 90 compatibility level is not valid in SQL Server 2014(120)

8>>We can define an Availability Group replica that resides in Azure.

9 >> Updateable columnstore indexes: This means you no longer have to drop and re-create columnstore indexes every time you need to load your warehouse tables

10>> Resource Governor for I/O : set the minimum and maximum reads or writes per second allowed by a process in a disk volume.

11 >>Delayed durability : Delayed durability allows you to return control back to the application before the log is hardened
Under delayed durability, log blocks are only flushed to disk when they reach their maximum size of 60KB.
the facility exists to make the delayed durability choice per transaction too

12 >>Incremental statistics: Incremental statistics in SQL Server 2014 allow you to update just those rows that have changed and merge them with what’s already there.

13 >>  Low Priority Wait /Low priority of online operations :you can specify how your re-index operation will handle being blocked. You specify how long it will wait and what to do when the wait is over. Will you have it follow traditional behavior and wait indefinitely? Will you have it terminate and move to the next table? Or will you kill the blocking query, so your re-indexing can complete? It’s your choice.

14>>parallel insert functionality of SELECT INTO :


Posted in Sql server 2014, Whats New | Tagged , | Leave a comment

re-initialize replication not working

for someone re-initialization is very simple, just go to publisher and right click and select re-initiallize and it will ask for do you what to generate snapshot now, if you say “yes” it will create a metadata of the articles accotiated with that publisher at snapshot specified location(if its at distributor -would be good). and snapshot agent runs and done. starts appling that snapshot at subsriber which inturns delete the tables and re-create.

but what if you run it several time but nothing happens and you dont know whats going on, as it dont provide you the error any where and also it will not show where is it going and what it is doing. for you its just like nothing happening.

so what will you do, as we are re-initializing the subsriber so we are safe to delete the subsriber and add the new one which inturns does the same of initialization. so I did so, guess what…. it started working and surprisingly I get error at the replication stating

cannot drop the table ‘xxx’ as its it is being referenced by object ‘yyy’

so now what to do, if you see the object ‘yyy’ its a view with schema binding option enable, due to which table cannot be drop.

this could be also because of function is using that articles(objects). so if  you see re-initialize is not working first check this dependent /reference objects with respect to replicated articles.

This may save some time for some one…



Posted in Add/remove, High Avaliability, Replication, Troubleshooting | Leave a comment

Sql Cluster Resource not comming online

when you check on the sql server services and sql agent services its getting started on windows cluster but on cluser its going down again. on error log you may get the cluster service

Login failed for user ‘cluster service account’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.

first thing you might think would be check the account password and see if its working– yeah its working now what next.

read the error log carefully and try to understand what is happening. here you could see sql server is started but when it tryed to attach – make available to cluster with cluster service acocunt its getting failed. means the given service account might not have previlage to sql server.

so what we did, we made cluster/sql fool and made a work aournd as failed over the cluster and as soon as sql server started, we open the sql server and checked is the given service account has access to it, and we were right, that account were not existed on to sql server, we have added that accound and granted sysadmin role and you know what…. it WORKS !!!

the possiblility of this issue could be:

“The cluster service account has been changed and that has not been updated at sql level”

Thanks to my frined Nitin here who helped me to resolve this issue.

Posted in Add/remove, Bug, Disaster Recovery, Error Log, High Avaliability | Tagged , , , , | Leave a comment

TempDB Contention 2.1.%


When you are performing many DML operations and makes tempdb contesnion on it, You may get wait_resource of  2.1.103

check perfomance counter:

  • Temp Tables Creation Rate
  • Temp Tables For Destruction

When you are performing many DML operations and makes tempdb contesnion on it, You may get wait_resource of  2.1.1 and 2.1.3

Sys,Dm_os_waiting_task – resource like (2:1:%) where % 1:PFS and 3 GAMs contention issue and 103 is DDL contension on Create/Drop temp table on sysmultiobjrefs objects.


for 2.1.1 and 2.1.3:

>> check tempdb is on seperate LUN (drives)

>>Enable the trace flag -T1118

>>check the Sort and DML queries on temp objects and try to tune it.

for 2.1.103:

>> enable trace flag -T1118

>> try not us create temp objects.

>> try to include indexes on the temp table.

>> apply hotfix

Must read :







Posted in DB Design, DMV, Internal, Lock/Blocking, Performance Tuning, TempDB, Troubleshooting | Leave a comment

Cluster failover fails

you may get an error while trying to failover to another node cluster.


to resolve this issue create an alias on the node with the cluster name and it should work.

will blog more on this later.


Posted in High Avaliability, Troubleshooting | Leave a comment

LogReader agent failed

You may get an error on log reader agent failed with following error:


Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission.


reason could be due to some activity the owner of the publisher database is gone (UNKNOWN) can cause this issue.


change the owner of the job usinga

EXEC sp_changedbowner ‘sa’

and it should work.




Posted in Replication, Transaction Log, Troubleshooting, What I learned today | Leave a comment

Sql Server 2014…….What!!!

Yes, its true.

Microsoft vice president has announce  that new version of sql server 2014, will release on 2014…

do you think it is possible, as  most of us yet familiar with 2012 ….

any way…as MS already clear  that they will release new version after every 2 years… this announcement is part of it.

you must see this diagram shows features and sql version implement.

[Must Read ]Whitepaper by expert – Kalen Delney on SQL Server 2014 In-Memory OLTP (“Hekaton”) : http://sqlblog.com/blogs/kalen_delaney/archive/2013/06/05/hekaton-whitepaper.aspx 

below are some good  links related to SQL server 2014.




Posted in Sql server 2014, Whats New | Tagged , , | Leave a comment

Sql server 2012 links

Feature of sqlserver 2012


good alternative to dbcc ind in sqlserver 2012



Posted in Denali, DMV, What I learned today, Whats New, Whitepapers | Tagged , , , | Leave a comment

Good Bye 2012 and welcome 2013

Hey Everyone….. from quite some time I have not written Blogs due to some personal and transiting from my old company … yeah If you read my last year good bye Blog I have indicated about my interest for changing the company took a year to do so…:)… yeah Now from this year I have joined a new company….  now having more than 12 Years working… and more than a decade with Sql Server… still Learning…. learning never ends:) loving it.

okay remembering what I have achieved year 2012 and how much achieved.

>> As committed that I will learn Denali and continue writing a Blog a day – have done a Denali Blog a day series on May 2012 , if you want  consolidated pdf  for that please comment or send an email to me.

continue learning Denali more stuff its getting interesting.

>> learning other stuff as well.

Hope I will continue to learn more about Sql server.

Thanks again all expert who share their valuable information on internet.

Thanks for Reading.

Posted in Computers and Internet, General, Personal | Tagged | Leave a comment

Denali –Memory : Memory Error Recovery in SQL Server 2012


Continue on What’s new in Denali and Memory enhancement, come across another great article by SQLCAT and Glenn Berry a new feature of Sql server 2012 in association with windows server 2012 called Memory Error Recovery in SQL Server 2012, Wanted to blog about it earlier but forget due to some other person stuff.


If you install sql server 2012 with windows server 2012 Enterprise edition will be a great package and have all great features for Denali includes Memory Error Recovery. Generally memory pages wont corrupt that easy but if you have very critical enterprise system and you don’t want to take chance. Could be due to some hardware failure or any other reason data on the memory could be corrupt. At this time for such cases you do not have a solution.

How it works:

If your data on memory BPool is corrupt or lost but that has been committed into disk with this option you could able to fetch the data from disk into memory and correct the memory data cache.


  • Windows server 2012 -Enterprise
  • Sql Server 2012 -Enterprise
  • Data should be committed to disk.
  • Hardware/Software should be compatible.


Reference :











Posted in Denali, Memory, Whats New | Tagged , , | Leave a comment