Database deployments are critical tasks that can have a significant impact on performance in production environments. In this article, we will discuss some performance-related best practices for SQL Server database deployments.

Database Deployment Prerequisites

Before starting the deployment process, it is important to gather some fundamental information about the new database. This includes understanding the business logic, usage, capacity plan, and any special configurations that need to be considered. Some examples of information to collect include:

• Identification of fields containing XML or Binary information and their usage type
• Maximum expected number of records for the next year
• Special configurations such as DTC, Service Broker, CLR and assembly, RCSI, encryption, key provisioning, etc.
• Elevated privileges for application service account
• Data engineering solutions like data archiving, migration, and cleansing jobs
• Scheduled jobs for reports or DML operations
• Existing OLAP cubes or BI solutions
• Linked servers or distributed queries
• Direct access from outside by other users or systems
• Application server details and SQL port access

Database Schema Design Assessment

Before deployment, it is important to review the database schema design to identify any potential performance risks. Some aspects to consider during the design review include:

• Appropriate data types for columns to ensure efficient storage and indexing
• Avoiding columns with data types like Text, Image, Ntext, FILESTREAM, XML, and varbinary unless absolutely necessary
• Avoiding the use of Uniqueidentifier columns as primary keys, as they can have performance implications
• Ensuring columns have suitable sizes to be covered by the index model
• Avoiding heap tables (tables without primary keys or clustered indexes) as they can generate poor execution plans

Stored Procedure and Application Stress Tests

Performing stress tests on stored procedures and the application itself is crucial to identify potential performance issues. It is recommended to execute the most frequently used stored procedures with a minimum of 200 concurrent users and a maximum of 1000 concurrent users. This stress testing should be done with a sufficient amount of data, such as at least 1 million rows. The goal is to capture poorly written or worst-performing T-SQL queries and optimize them by converting them into SQL Server custom written stored procedures.

Database Index Analysis

After stress testing, it is important to analyze the missing indexes from the system stored procedures. This analysis will help identify the appropriate missing indexes that can enhance the performance of poorly performing queries and stored procedures. It is crucial to collaborate with experienced database administrators and the development team during this process.

Conclusion

Deploying a database in a production environment requires careful consideration and adherence to best practices. By following the guidelines mentioned in this article, you can ensure the best performance of your SQL Server database. Remember to always assess and document your deployment process to minimize any potential risks.