Parameter Sniffing Problem and Possible Workarounds

SQL Server compiles the stored procedures using (sniffing) the parameters send the first time the procedure is compiled and put it in plan cache. After that every time the procedure executed again, SQL Server retrieves the execution plan from the cache and uses it (unless there is a reason for recompilation). The potential problem arises…

11

SQLSaturday #243, Cape Town : “Top 10 Performance Tips for SQL Server Developers”

I just presented my “Top 10 Performance Tips for SQL Server Developers” session at SQLSaturday #243, Cape Town. It was really awesome audience. You can download the deck and demo scripts below. Top 10 Performance Tips for SQL Server Developers Performance of a SQL Server depends on many factors and due to the nature of…

0

Step by Step : Backup and Restore for SQL Server in Windows Azure Blob Storages

As all of you already awared, backing up data is a vitally important part of the strategy in protecting data loss due to disasters such as loosing data center completely. This article is not a pure disaster recovery article and not talks about disaster recovery strategies. In this article we are going to talk about…

1

How to Find Unused Statistics

As you know, statistics are used for row estimation during query plan generation phase. So they are really important for query performance. If the “Auto Create Statistics” database option is “On”, for every column in the where clause or join, statistics are automatically created by the database engine. These auto created statistics might not be…

2

I will be on the stage!

A few week later I will be on the stage for 2 SQLSaturday events. First of them will be held on May 18 in Kosovo, Albania and this will be my first time in Kosovo, really excited about it. Thanks for your invitation Dugi 🙂 The second one will be held on May 25 in…

3

Indexes Overhead on DML (Insert-Update-Delete) Operations

Indices dramatically affect query performance. A report which can take several hours can be completed in just a few seconds with a proper index. On the other hand every index might affect DML (Insert-Update-Delete) operations, because every DML operation touches clustered index and every related nonclustered index. To be able to see this effect I…

4

Using Plan Guides and Plan Freezing

Today one of my customers came to me with a question. The question was “We have some 3rd party applications which heavily use ad-hoc queries with Index Force. Because of changing query optimizer behavior these queries do tons of logical reads. We would like to optimize these queries by removing index forces however we don’t…

2