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