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.

X

SQL Server 2014 Managed Lock Priority for online index rebuild, PART 2

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.

“The replication agent has not logged a progress message in 10 minutes”

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 🙂 ).

SQL Server 2014 Managed Lock Priority for Online Index Rebuild, PART 1

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.

Are your clustering keys good enough

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

Update with WRITE on varcharmax column leads to time-outs

The following interesting case came out from our SQL User Group member Ilian Yovchev who works for David Holding company.

They have updated their text filed types to varcharmax data type according to the SQL Server roadmap and deprecation plans. They use update with WRITE to insert data in those new fields.

But they faced time outs during some updates and on query executions against the table with varcharmax field type. They have traced those time outs and found that the reason behind them is sync stats update performing on varcharmax col. Strange, isn’t it? Well, not exactly as synch stats update (Asych Stats Update=OFF in db level) can potentially lead to query time out.

Let’s see the specifics of this case.

Find and tune inefficient query executions

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.

Temp tables or table variables

Some Misconceptions and main differences

Every time I perform a code review or read some blog posts that use temp tables or temp variables I find that the difference between temp tables and temp variables is not well understood. The common misconception for example is that both are created in memory, the other is that it is not recommended to use temp tables rather than table variables (vise versa is also common), or people just prefer to use one or another temp object without knowing if it is appropriate for specific case scenario.

Here are some examples in order to clarify the differences.

Why not rebuild all indexes at once?

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.