Database Performance

Performance

 * SPWho
 * Indexes
 * Clustered
 * Based on the table’s key values, actually physically orders the rows.
 * Only one per table, obviously, because the rows can only be physically ordered in one way.
 * By default, when a primary key is selected, a clustered index is created based on that key.
 * May be defined by several columns
 * Favor few columns and ones that are unique, sequential and that do not change much.
 * Non-clusered
 * Data from specified fields are stored separately with pointers to their rows. (unless clustered and then the pointer is to the clustered index)
 * Creating a Unique constraint results in a non-clustered index being created.
 * Can include the columns whose information you are looking for when creating indexes for specific queries.
 * S.P. caching / compiling
 * Good
 * Executing t-sql
 * SP calls are best (simple RPCs transmitted,cached,compiled)
 * Parameterized queries next (cached,compiled
 * Dynamic (embedded or ad-hoc) worst (not cached,compiled)
 * Short SPs
 * Compiled, Cached execution plans
 * 2-part names dbo.xxx
 * SARG (Search Arguments)
 * Clean Where-clause statements (no expressions or functions)
 * Note: this was the case in SQL Server 2000. Improvements in SQL Server 2005 make the use of functions in a WHERE clause all but unnoticeable performance-wise.
 * Calling SPs with the fully qualified name (database_name.dbo.myProcedure)
 * Bad
 * Long SPs
 * set nocount off (default)
 * 3-part names
 * nested transactions
 * Not SARG
 * Dirty Where-clause statements (NOT, !,<>,upper,datepart,like etc.)
 * Note: this was the case in SQL Server 2000. Improvements in SQL Server 2005 make the use of functions in a WHERE clause all but unnoticeable performance-wise.
 * use tempdb
 * use temp tables
 * cursors
 * Consider the use of Common Table Expressions (CTEs)
 * Compiled SP Execution plans
 * Plans are recompiled when:
 * Requested (create proc.. with recompile as…, Exec myproc … with recompile)
 * Use this when you know you want a new execution plan (maybe because your where clause has changed)
 * Called from connections containing different settings
 * Plan aged out of memory
 * Interleaved DDL and DML
 * Data Definition Language
 * Create, alter, drop
 * Keep these at the top of SPs as the recompile happens after one of these when it comes across a DML statement.
 * Data Manipulation Language
 * Insert, update,delete
 * Schema changes to objects in sp
 * New index statistics (see Auto Update Statistics)
 * Sp_configure
 * Set statements that vary from call to call
 * High use of temp tables (see Keep Plan query hint)
 * Plans are bad when
 * Paths vary (lots of IFs)
 * The path that the first execution used is the one cached.
 * Query performance
 * Checking for data in a table
 * Good:
 * Less good: