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

If you lost some intermediate backup files?

It’s sometimes really challengeable to restore a database if you lost some intermediate backup files such as Full or Differential backups. Let’s assume backups have been taken in below order. Timeline Backup Type Backup Name t1 Full Backup F1 t2 Diff Backup D1 t3 TLog Backup T1 t4 TLog Backup T2 t5 Full Backup F2…

0

Data Mismatch on WHERE Clause might Cause Serious Performance Problems

Like Applying Function on WHERE clause problem, any datatype mismatch on WHERE clause might cause serious performance problems. Predicates on both sides of comparisons (for example on WHERE clause) always must match datatypes. It means that if the left side of predicate is integer than the right side needs to be integer. If the datatypes…

1

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

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

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

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