constructs that normally disallow autoparameterization.

JOIN
BULK INSERT
IN lists
UNION
INTO
FOR BROWSE
OPTION
DISTINCT
TOP
WAITFOR statements
GROUP BY, HAVING, COMPUTE
Full-text predicates
Subqueries
FROM clause of a SELECT statement has table valued method or full-text table or OPENROWSET or OPENXML or OPENQUERY or OPENDATASOURCE

Comparison predicate of the form EXPR <> a non-null constant


Autoparameterization is also disallowed for data modification statements that use the following constructs:

DELETE/UPDATE with FROM CLAUSE
UPDATE with SET clause that has variables

Plan Cache

DBCC CACHESTATS

displays information about the objects currently in the buffer cache, such as hit rates, compiled objects and plans, etc.



DBCC FREEPROCCACHE

This command removes all cached plans from memory




DBCC FLUSHPROCINDB (dbid)

This command allows you to specify a particular database id, and then clears all plans from that particular database. Note that the usecount query that we'll use in this section does not return database id information, but the sys.dm_exec_sql_text TVF has that information available, so dbid could be added to the usecount query.

OLTP and OLAP in SQL Server 2005

Here are some tips for OLTP workloads with strict response time requirements, high concurrency requirements, and/or high overall server throughput requirements:

1. Watch out for blocking and/or memory-consuming operators such as sorts, hash joins, and hash aggregates. Memory-consuming operators degrade response times because of the verhead of acquiring a memory grant, and they reduce concurrency by creating contention for memory, which is a limited resource.

2. Avoid scans and be sure that queries make maximum use of indexes. Keep in mind that
the cost of a scan is proportional to the size of the table, whereas the cost of an index seek is proportional to the number of rows returned. Thus, seeks yield more consistent performance as data sizes grow.


Here are some tips for data warehousing workloads where the response time of a single query or report is most important:

1. Make sure that large queries that process large datasets are choosing parallel query
plans. While parallel plans do add extra query processing overhead, they also minimize
the response time of queries.

2. Watch out for skew in parallel plans. Parallel scans over small tables and nested loops
joins with very few rows on the outer side of the join can lead to skew problems in which
some threads process many more rows or perform much more work than other threads.

3. Avoid sort-based operators, such as stream aggregates and merge joins in highly parallel plans on large machines with more processors or more cores. Specifically look out for merging exchanges at high degrees of parallelism. Merging exchanges use more server resources and are more subject to skew and scalability issues.

SQL Query Operators

memory-consuming operators such as

a sort, hash join, or hash aggregate

SARG (search argument)

Sargable operators

include =, >, <, =>, <=, BETWEEN, and sometimes LIKE. Whether LIKE is sargable depends on the type and position of wildcards used.

Here are some SARG examples:
name = 'jones'
salary > 40000
60000 < salary
department = 'sales'
name = 'jones' AND salary > 100000
name LIKE 'dail%'


Here are some examples of nonsargable clauses:
ABS(price) < 4
name LIKE '%jon%'
name = 'jones' OR salary > 100000