Improving Performance on SQL Server

When you will be trying to improve performance in queries remember this:

 

1. Rewrite your query... think about using functions on columns can eliminate the use of indexes... subqueries, correlated and nocorrelated can limit the choices for the query optimizer, instead of it you may choose to put the results of subqueries on independent queries and variables... and use these variables later.

Besides, avoid cursors... sql server is good on dealing with groups of records... cursor is a mechanism to use on row at the time. no good.

2.  schema improvements. think about normalize and denormalize accordingly with the use of your application (OLAP or OLTP). Indexes, statistics, composite indexes, covering indexes .. bla bla bla..

all of this you may know.. but you don't know how many time i saw these obvious mistakes happens, so it may be important to remember.

 

3. Statistics. The path to the optimizer to choose the right index is statistics... so choose the way you update if you do so automatically or manually, and the sample rate ... like full scan, is useful sometimes.. but keep in mind the overhead to do so.

 

4. Indexes. Create indexes in low density columns. Keep in mind that sql server will not use your index if no query uses a selective predicate and sometimes it depends on the density the column, please... i've seen indexes created on a column Gender... gender is Male or Female... sql server will not use this index (in a 50% / 50% aprox. relation)