·        Developer: Acquire a good understanding of the effect a query has on the underlying database.< ?xml:namespace prefix ="" o ns ="" "urn:schemas-microsoft-com:office:office" />

·        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.