SQL Server Performance
A problem with SQL Server performance is the most common reason my services are called upon.There are so many reasons for poor performance or scalability, ranging from application and schema design, through to indexing and server configuration. I have grouped these into a few categories and listed them here. This page will eventually form a hub from which articles specific to each factor can be found.
Application Design
Using server side cursorsToo many round trips to the server
Long running transactions
Deadlocking prevention
Insufficient archiving
Reporting on OLTP database
Inadequate error handling
Schema Design
Inefficient design for anticipated workloadChoice of clustered Indexes
Covering Indexes
Included Columns
Insufficient indexes
Too many indexes
Index selectivity
Column order in composite indexes
Indexed views
Table partitioning
Wrong data types
Primary and foreign keys
Constraints
TSQL
Avoid index hintsAvoid join hints
Avoid cursors
Avoid implicit type conversions
Avoid ad hoc SQL. Use stored procedures
set nocount on
Study execution plans
SP Recompilation
Index Scans
Bookmark lookups
Hash joins
Retrieving too many rows
Fully qualify database objects
Specify columns (only those required)
Implicit type conversions in where clause
Use minimum isolation level, or (nolock) hint
Excessive temporary tables (tempdb overhead and contention)
Ensure WHERE clause uses indexes
Ensure OR condition use indexes
SQL triggers
Avoid sp_ prefix in stored procedures
Hardware
Disk SubsystemMemory
CPUs
Network
Server Configuration
OSSQL Version
sp_configure
max degree of parallelism
Trace flags
tempdb configuration
/3GB
AWE
Database Configuration
AutoShrinkNumber of files/filegroups
Location of files
Split clustered and nonclustered index into separate filegroups
Capacity planning and pre-sizing
Maintenance
DefraggingDisk defragging
Statistics
Timing
Monitoring and Troubleshooting
Perfmon countersProfiler
DMVs
DBCC
SQL Server performance is something best considered right at the beginning of any development effort, but that is rarely the case.
If you are starting out on a new development, or have inherited a system with performance problems, I hope you find some of these articles helpful. If you would like more detail about a particular subject please contact me.
Not found what you're looking for?
Use this search box. It is tuned for SQL Server searches. Try it and see!Do you have a question about SQL Server? Would you like to answer a question?
Go to the SQL FAQ to get started.
