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

EXEC vs. sp_executeSQL

When we want to execute a TSQL string we can use both EXEC and sp_executesql statements. But there are some very important differences between them sp_executesql allows for statements to be parameterized Therefore It’s more secure than EXEC in terms of SQL injection sp_executesql can leverage cached query plans. The TSQL string is built only…

8

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

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

Applying Functions on WHERE Clause Columns might Cause Serious Performance Problems

Any function such as collate, convert, substring etc. applied on a column might make impossible for SQL Server to use any index on this column. This because SQL Server query optimizer considers the column after the function as a new column, because the column values are not stored in the index pages as returned by…

2

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

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

Announcement : MEA Services Webcast Series

I’m really happy to announce that as MEA services team we have started an initiative to share our experience more with the public community. As a starting point we have organized a list of upcoming webcasts that you can attend to gain more in-depth information from the field about Microsoft products and technologies. Learn how…

1

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 Which SPs Use Forced Index

One of the periodical DBA task is to examine index usage statistics with sys.dm_db_index_usage_stats DMV and to find unused indexes. After that these unused or rarely used indexes can be dropped if they are really not used. But before that you should examine if these indexes forced in any of SQL Server objects such as…

1