Check list for SQL optimization
SQL: Scale Up vs. Scale Out
Check
|
Description
|
Optimize the application before
scaling up or scaling out.
|
|
Address historical and reporting
data.
|
|
Scale up for most applications.
|
|
Scale out when scaling up does not
suffice or is cost-prohibitive.
|
Schema
Check
|
Description
|
Devote the appropriate resources
to schema design.
|
|
Separate online analytical
processing (OLAP) and online transaction processing (OLTP) workloads.
|
|
Normalize first, denormalize later
for performance.
|
|
Define all primary keys and
foreign key relationships.
|
|
Define all unique constraints and
check constraints.
|
|
Choose the most appropriate data
type.
|
|
Use indexed views for
denormalization.
|
|
Partition tables vertically and
horizontally.
|
Queries
Check
|
Description
|
Know the performance and
scalability characteristics of queries.
|
|
Write correctly formed queries.
|
|
Return only the rows and columns
needed.
|
|
Avoid expensive operators such as
NOT LIKE.
|
|
Avoid explicit or implicit
functions in WHERE clauses.
|
|
Use locking and isolation level
hints to minimize locking.
|
|
Use stored procedures or
parameterized queries.
|
|
Minimize cursor use.
|
|
Avoid long actions in triggers.
|
|
Use temporary tables and table
variables appropriately.
|
|
Limit query and index hint use.
|
|
Fully qualify database objects.
|
Indexes
Check
|
Description
|
Create indexes based on use.
|
|
Keep clustered index keys as small
as possible.
|
|
Consider range data for clustered
indexes.
|
|
Create an index on all foreign
keys.
|
|
Create highly selective indexes.
|
|
Create a covering index for
often-used, high-impact queries.
|
|
Use multiple narrow indexes rather
than a few wide indexes.
|
|
Create composite indexes with the
most restrictive column first.
|
|
Consider indexes on columns used
in WHERE, ORDER BY, GROUP BY, and DISTINCT clauses.
|
|
Remove unused indexes.
|
|
Use the Index Tuning Wizard.
|
Transactions
Check
|
Description
|
Avoid long-running transactions.
|
|
Avoid transactions that require
user input to commit.
|
|
Access heavily used data at the
end of the transaction.
|
|
Try to access resources in the
same order.
|
|
Use isolation level hints to
minimize locking.
|
|
Ensure that explicit transactions
commit or roll back.
|
Stored Procedures
Check
|
Description
|
Use Set NOCOUNT ON in stored
procedures.
|
|
Do not use the sp_prefix
for custom stored procedures.
|
Execution Plans
Check
|
Description
|
Evaluate the query execution plan.
|
|
Avoid table and index scans.
|
|
Evaluate hash joins.
|
|
Evaluate bookmarks.
|
|
Evaluate sorts and filters.
|
|
Compare actual versus estimated
rows and executions.
|
Execution Plan Recompiles
Check
|
Description
|
Use stored procedures or
parameterized queries.
|
|
Use sp_executesql for
dynamic code.
|
|
Avoid interleaving data definition
language (DDL) and data manipulation language (DML) in stored procedures,
including the tempdb database DDL.
|
|
Avoid cursors over temporary
tables.
|
SQL XML
Check
|
Description
|
Avoid OPENXML over large XML
documents.
|
|
Avoid large numbers of concurrent
OPENXML statements over XML documents.
|
Tuning
Check
|
Description
|
Use SQL Profiler to identify
long-running queries.
|
|
Take note of small queries called
often.
|
|
Use sp_lock and sp_who2
to evaluate locking and blocking.
|
|
Evaluate waittype and waittime
in master..sysprocesses.
|
|
Use DBCC OPENTRAN to locate
long-running transactions.
|
Testing
Check
|
Description
|
Ensure that your transactions logs
do not fill up.
|
|
Budget your database growth.
|
|
Use tools to populate data.
|
|
Use existing production data.
|
|
Use common user scenarios, with
appropriate balances between reads and writes.
|
|
Use testing tools to perform
stress and load tests on the system.
|
Monitoring
Check
|
Description
|
Keep statistics up to date.
|
|
Use SQL Profiler to tune
long-running queries.
|
|
Use SQL Profiler to monitor table
and index scans.
|
|
Use Performance Monitor to monitor
high resource usage.
|
|
Set up an operations and
development feedback loop.
|
Deployment Considerations
Check
|
Description
|
Use default server configuration
settings for most applications.
|
|
Locate logs and the tempdb
database on separate devices from the data.
|
|
Provide separate devices for
heavily accessed tables and indexes.
|
|
Use the correct RAID
configuration.
|
|
Use multiple disk controllers.
|
|
Pre-grow databases and logs to
avoid automatic growth and fragmentation performance impact.
|
|
Maximize available memory.
|
|
Manage index fragmentation.
|
|
Keep database administrator tasks
in mind.
|
Comments