Your browser (Internet Explorer 7 or lower) is out of date. It has known security flaws and may not display all features of this and other websites. Learn how to update your browser.
Finally I managed to download and install the new CTP2. You need to have at least Windows Server 2012 or Windows 8 OS in order for the installation process to complete. Those are minimal OS requirements. You can find more info at https://msdn.microsoft.com/en-us/library/ms143506(v=sql.130).aspx#top_principal
Sharing first thoughts
Today Microsoft has announced the CTP2 of SQL Server 2016 (http://sqlmag.com/sql-server-2016/ctp-2-sql-server-2016-now-available), the long waited public preview of the new SQL Server version. And as one of my friends said some time ago, “I was just about to retire when they release a new version and it became exited again”. Speaking of myself, I am not planning to retire soon, but a first CTP of a new SQL Server version is truly exciting event, like taking a new adventure, it is like getting a new motivation for the job.
So what motivates me in this new version and what I am planning to write and speak about in next few months (years) (and hopefully implement in practice and production environments).
I am rarely doing one of those posts that just targets clicks by fancy title and says nothing but a link that you could find by yourself. So beside giving you links and keywords of the product, let me share with you my favorite features that I am going to test and blog, and some info about how to start your own testing of this newly released CTP2.
Can I have predictable Online Index Rebuilds (cont.)
The Managed Lock Priority in SQL Server 2014 give us a choice for managing locks of the online index rebuilt operation.
We need to do that because the online index rebuild operation is not performed entirely online. There are Schema locks that the process need to obtain at the beginning and at the end of the index rebuild and those type of locks (especially Sch-M one) could have incompatibility with other locks currently obtained on the object. Because of that some parts of the process need to be done in “offline” mode, and as a result we experience blocking.
In this part II of my blog post I am continuing to describe the Managed Lock Priority OIR with the second new option we have. What happens when we give a priority to a user transaction instead of the online index rebuild process.
Or What is happening with my replication?
I had an interesting case couple of months ago, which started with receiving several questions from a customer about SQL Server replication. Some of them were “Should I use replication at all?”, or “Should I consider using replication on satellite network?!” This cough my attention and I dived into the case, I had to find out why the people where so upset about SQL Server replication (I had some ideas though 🙂 ).
As I already wrote in my post “Be careful with scheduling online index rebuilds” the problem with online index rebuild (except the need of huge amount of resources) is unpredictability and blocking. In my customer’s case the online index rebuild take between 2 and 12 hours. The new option in SQL Server 2014 called Managed Lock Priority seems to provide at least first steps in getting a solution to the problem (or creating additional problems?). The new feature allows a DBA to manage the S-lock/Sch-M lock for OIR.
As this new feature brings more questions than answers, it really grabbed my attention. So I decided to dive into it and to try to define when and how to use it.
The first CTP of SQL Server 2014 is available for almost two months. For those of you that are about to install it now on Windows Server 2012, here are some advices to make the process smooth.
Why some of your clustered indexes create performance problems and how to check that?
Many physical database implementation suffer performance because of wrong clustering key. If the clustering key is not appropriately chosen it can lead to
•high level of page split events
•high level of index fragmentation
•increasing the overall object size – the clustered and all nonclustered indexes of the table
The results are slow execution of insert statements, overall performance degradation when accessing specific tables, increasing the size of the table object and the database as a whole.
The goal of this post is to describe the possible damages of the wrong clustering keys, as well as to give the hints for finding problematic clustering keys in your current database. Last but not least I am giving you the main recommendations on choosing the clustering keys. The code and described principles in this post are valid for all latest SQL Server versions – 2008, 2008R2 and 2012
Or a way to find cardinality errors in your cached execution plans
If SQL Server can’t accurately predict estimated number of rows for a query plan operator, then a discrepancy appears in the estimated v/s actual number of rows in the query plan. QP creates a query plan and choses a specific operator because it expects that the query returns some number of rows, but actually when the plan executes, a lot more (or far less) number of rows go trough the operator. This is called cardinality error, because SQL Server makes an error on estimating cardinality of an operator. The result of cardinality error in almost every case is that the query executes with suboptimal execution plan. Of course it means that query has performance problems and delays in some executions.
On my SQL Master Academy trainings for novice and experienced DBA we discuss a lot why and how to perform index rebuilds. And there are constantly a couple of questions about index rebuilds:
•Do I need index rebuild at all?
•Why not rebuild all the indexes of the database as one task?
Let’s discuss those and give some advices and scripts to use.
Online index rebuild is a feature available from SQL Server 2005 Enterprise Edition. While it does sound useful and attractive to use you should be careful considering it as part of your database maintenance plan. If you use it as a regular index rebuild operation then you could end up with serious locking and blocking and you cannot be sure and define the approx time of index rebuilds, it could be different every time.
In one of my customers the online index rebuild of the database took between 2 and 12 hours! So we decide to refine the task and go for offline index rebuilds for most of the indexes.
Why is that happening and what is behind the online index rebuilds.