SQL Server Query Optimization recommendations

server| optimization

Some queries are inherently resource intensive. This is related to basic database and indexing issues. These queries are not inefficient because the query optimizer implements these queries in the most efficient way possible. However, they do consume a lot of resources, and Transact-SQL-oriented collections make these queries look inefficient. The intelligent level of the query optimizer does not eliminate the inherent resource costs of these constructs. These queries are inherently expensive compared to uncomplicated queries. Although Microsoft (r) SQL Server? 2000 Use the best access plan, but are limited by the likelihood of the underlying construction.

For example, the following types of queries can be resource intensive:

Query that returns a large result set

A WHERE clause with a height that is not unique
However, there are some suggestions for optimizing queries and improving query performance, including:

Add more memory (especially if the server is running many complex queries and several of these queries are slow to execute).

Run SQL Server on a computer that has more than one processor. Multiple processors enable SQL Server to leverage parallel queries. For more information, see Parallel query processing.

Consider rewriting the query.
If your query uses cursors, determine if you can write cursor queries more efficiently using more efficient cursor types, such as fast forward-only cursors, or simple queries. Simple query performance is generally better than cursor operations. A set of cursor sentences is usually an outer loop operation in which each row within the outer loop is processed once the internal statement is used, so consider using a group by or case statement or instead using a subquery.

If your application uses loops, consider putting loops inside the query. Applications often contain loops with parameterized queries that execute many times and require a network round-trip between the computer running the application and SQL Server. Instead, use temporary tables to create a more complex query. The query optimizer optimizes this query by simply providing a roundtrip network.

Do not use multiple aliases for a single table within the same query to simulate index crossings. Impersonation of an index intersection is not necessary because SQL Server automatically considers the intersection of indexes and can use multiple indexes on the same table within the same query. For example, the following sample query is given:
SELECT * from LineItem
WHERE Partkey BETWEEN 17000 and 17100 and
ShipDate BETWEEN ' 1/1/1994 ' and ' 1/31/1994 '

SQL Server can use indexes on both the Partkey and ShipDate columns, and then perform a hash match between the two subsets to get an index crossover.

Use query hints only if necessary. If a query uses a prompt that was executed on a previous version of SQL Server, you should test the query without specifying a hint. A hint prevents the query optimizer from choosing a better execution plan. For more information, see Select.
Use query governor to configure options and settings. You can use the query governor configuration option to prevent long-running queries from consuming system resources. By default, the query governor configuration option allows all queries to be executed without regard to the time required for the query. However, you can set the query governor to a maximum number of seconds to allow all queries for all connections to be executed, or queries that only allow specific connections to be performed. The query governor is based on the estimated query cost rather than the actual elapsed time, so there is no run-time overhead. It also stops long-running queries before they start, rather than running them until certain predefined limits are reached. For more information, see query governor cost limit options and SET query_governor_cost_limit.