What do you consider are the biggest mistakes DBAs or developers make when they write Transact-SQL code that negatively affect performance?
Here are some of the items I’ve found important:
- Developer: Acquire a good understanding of the effect a query has on the underlying database.
- DBA: Perform regression testing and trend-analysis when moving any code (in application or database) from development servers to QA/test servers and onto production.
- DBA: Make sure proper indexes are in place.
- DBA: Make sure each table has a clustered index.
- Developer: Making sure to review complex queries with the DBA.
- DBA: Use read-only copies of databases to accommodate heavy reporting requirements during business hours or limit certain resource intensive queries to off-hours.
- Developer: Make sure to use read-only, forward-only, result sets whenever possible, and fetch all result set data in the application immediately.
- DBA: Make sure you are aware of developer requirements, the developer tools being used, and provide feedback to catch potential problems early.
- DBA: Make sure to select the RAID solution that best supports your needs.
- DBA: Make sure there is sufficient memory on the server.
- DBA: Proactively monitor the server hardware to make sure it can handle any increasing loads.
- DBA and Developer: Speak with the end-users to make sure they are happy with performance.
- DBA and Developer: Don’t assume pre-packaged applications (like customized CRM applications, SQL access or reporting tools) generate efficient SQL.
- DBA and Developer: Proactively monitor SQL activity on SQL Server.
- DBA and Developer: Avoid SQL cursors whenever possible.
- DBA and Developer: Keep SQL transactions as short as possible.
- DBA: Avoid running extraneous services on the server running SQL Server.
- DBA: Make sure that Microsoft’s Internet Information Server (IIS) or other web servers are on a different “box” than SQL Server.
- DBA and Developer: Use stored procedures for data access wherever possible.
- DBA: Backup to disk first (staging), and then copy the disk backup to tape.
- DBA: Keep index and column statistics up-to-date.
- DBA and Developer: Avoid table and index scans wherever possible.
- DBA and Developer: Avoid non-optimizable selection criteria in SQL statements, such as “not equal” comparisons in WHERE clauses.
- DBA and Developer: Only request the rows and columns needed for processing.
What is the best way to help identify poorly performing Transact-SQL code?
First, and foremost, it is imperative that all who develop Transact-SQL code for a database have a very good understanding of the database’s design. It is also a good idea to understand how SQL Server processes queries, and the simple do’s and don’ts of query design. There are a number of books, available web sites, and magazines that deal with these issues. Some of my favorite books include the Inside Microsoft SQL Server series and the SQL Server Performance Tuning series from Microsoft Press. A good understanding of these items will help eliminate problems up front and give you better ideas about how to tackle performance problems as you identify them.
Microsoft offers some basic tools which can be quite helpful when query tuning. You can use Query Analyzer (QA) to examine more closely individual queries and their corresponding execution plans both through the built-in graphical execution plan facility and the textual SET SHOWPLAN_ALL options. You can also monitor query statistics with the SET STATISTICS IO command and query time with SET STATISTICS TIME.
To monitor multiple SQL statements, users have available the SQL Server Profiler which can watch a database and collect information with varying levels of detail. I find that using QA is easier when tuning individual queries. When examining multiple queries, especially during testing and production, Profiler can help you collect the body of activity on the server. You can save Profiler information to a table for later reporting and interrogation. The data the Profiler collects is extremely helpful.
There are some very useful statistics to look for when examining a query for a potential performance problem. These include the number of reads, writes, CPU used on the server, and the duration of the query. Most queries have a “read” component. In other words, the query must access data in one or more tables in a database to generate the requested results. The read component is one of the most useful statistics since it has a direct affect on the CPU and duration of a query. Lower the reads on a query, and you increase a server’s health.
Obviously, the goal of any developer or DBA is to make sure SQL Server processes queries as quickly as possible. The more information the developer or DBA has, the easier it is to address performance issues. This is where I believe Coefficient has a clear advantage. Coefficient’s primary interest is the SQL that accesses your database. It presents this information in a clear and concise way. Users can spend a lot of time examining the output of SQL Server Profiler.
This is strength of Coefficient. Performance statistics for a database are generated automatically for the developer and DBA. An overall picture is created in fully hyperlinked HTML with graphs. Not only does this save a lot of time trying to analyze thousand or millions of rows of Profiler output manually, but the information can be shared throughout an entire organization using a company’s web server or network. Coefficient provides individual query analysis, overall query statistics, and database documentation all in one. Imagine the help of being able to see a comparison of one iteration of a database release to the next. To see what’s operating more efficiently and what’s not. The documentation aspect of Coefficient is extremely helpful in following trends in database performance.