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

“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.

Be careful when scheduling an online index rebuild

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.

Master database error after applying SQL Server 2008 R2 SP2

When applying SP to SQL Server you need to be careful, I mean really, with planning and attention to every step and especially to errors if any appear.

While I know perfectly the meaning of this sentence I was not as careful as needed applying the SQL Server 2008 R2 SP2, because it was on my laptop, and I did couple of other things meanwhile including listening to my baby girl first songs

So I run the SP2 installation, went trough the Wizard and started the process. I even forgot to close the SSMS and the installation process kindly reminded me that it didn’t have control over SSMS. OK, so I closed it and started the patch again. After several minutes I saw the lovely green icon and the message of successful installation. Great! One more task from the checklist is done, while of course thinking about completely different thinks and watching my baby’s first steps! BUT when I started the SSMS and tried to login the “nice” messages came in.