Notes from Orlando SQLConnections Conference – Tips and Tricks to Tuning for High Performance
This entry was posted on 4/6/2007 3:41 PM and is filed under SQL Server.
Last week I attended the SQLConnections Conference in Orlando. The conference ran concurrently with ASP .Net Connections, Visual Studio Connections, and SharePoint Connections. On Sunday I had the opportunity to participate in Kimberly Tripp’s workshop on “SQL Server 2005: Tips and Tricks to Tuning for High Performance”. The workshop began with a discussion of areas that affect performance:
The “Process”
The “Application”
The “Database”
The “Software”
The “Hardware”
Each one of these areas contains items we can look at to see if we can optimize performance. For example, in the process area, can steps be eliminated or can pieces of a transaction be compartmentalized? For the application area, can we limit the number of columns and/or rows returned? Can we reduce the number of server round trips? In terms of the database design, how can we identify queries that are the sources of excessive resource utilization?
To address the sources of excessive resource utilization we should consider both expensive queries and frequently executed queries. We can begin to perform analysis using Profiler and the Tuning template. Specific information was provided on how to find the most frequently executed queries. We can save the results of a trace into a table and aggregate the results. Profiler sometimes only allows us to begin to identify the problem – often the underlying root cause is a larger problem!
The overall approach to tuning is to start with a handful of queries that are either frequently used and/or very resource intensive and tune these first. These may yield the best “bang for the buck” depending on what the requirements of the engagement are.
A very in depth discuss of clustered and non-clustered indexes was presented. Code demos of the execution plans displayed how many logical reads and writes were necessary for each operation and helped explain why some indexes or table scans are more efficient than others. A lot of query performance is dependent on how selective an index is.
In addition to a great discussion of indexing, there also was a list of other things to try prior to adding more indexes. For example, we need to consider if database statistics are up to date. One trouble shooting approach is to try running UPDATE STATISTICS and seeing if that improves performance. Another approach is to see if executing the stored procedure with RECOMPILE option yields better performance. We should also try rewriting the affected code/query. For example, if we are writing the query as a join, try rewriting it as a subquery and vice versa.
Overall the session was extremely informative and contained useful tips and tricks that apply to both SQL Server 2000 and SQL Server 2005. If you have a chance to see Ms. Tripp, please take advantage of the opportunity! She has been working with SQL Server since 1990 and knows the product inside and out.
