Summary
Database performance begins with good database design. Software Engineers who design databases for SQL Server should have a fundamental understanding of SQL Server's database engine, approach to Online Transaction Processing, and features for monitoring and optimizing databases. Below is a set of guidelines for database design as well as background information on database monitoring and SQL Statement Optimization.

1. Database Design Guidelines
1. Use a Normalized Database Model or a STAR Schema for Data Warehouses.
2. Use efficient Data Types.
3. In general, tables should have only one primary key. A table should not have more than 3 primary keys.
4. A table should have as few clustered indexes as possible. There should be no more than 2 or 3 clustered indexes on a table. As the number of clustered indexes on a table increases, the performance improvement of each index significantly lowers.
5. While a new index may significantly speed up a SELECT Statement, it may also cause INSERT, UPDATE and/or DELETE statement(s) to run much more slowly. There should be no more than 4-5 indexes on a table. A good new index can reduce response time from minutes to seconds while a poorly chosen index can result in queries taking minutes rather than seconds. When a database table has more than 4-5 indexes, it is necessary to more rigorously Performance Test and Load Test the database.


2. Database Monitoring
1. sp_Who Stored Procedure: Reports snapshot information about current SQL Server users and processes, including the currently executing statement and whether the statement is blocked.
2. sp_Lock Stored Procedure: Reports snapshot information about locks, including the object ID, index ID, type of lock, and type or resource to which the lock applies.
3. sp_SpaceUsed Stored Procedure: Displays an estimate of the current amount of disk space used by a table or database.
4. sp_Monitor Stored Procedure: Displays statistics, including CPU usage, I/O usage, and the amount of time idle since sp_monitor was last executed.


3. SQL Statement Optimization
1. Review the way in which the application is establishing a connection to the database. Is the connection type suitable to the SQL Statements that will be run over the database connection?
2. Check the average number of connections to the database and evaluate the number of connections based on the central processing unit, memory and tempdb space that is available to the database.
3. Review all code that keeps database connections open and cursors open.
4. Review the amount of data moving back and forth between the client and the server.
5. Write queries that specify field names in the SELECT Statement, used indexed fields in WHERE Clauses, Order By Clauses, and Group By Clauses.
6. Use Query Optimizer to review execution plans, reduce table scans, and identify performance bottlenecks. For more information on query optimization, see Microsoft's Query Processing Architecture Guide
7. Do not join more than 4 tables in a SQL Statement. When more than 4 tables are joined together in a SQL Statement, it is necessary to more rigorously Performance Test and Load Test the SQL Statement.
8. Use SQL Statements that have execution plans that can be more easily cached by SQL Server's Query Store. For more information on SQL Server's Query Store, see Microsoft's Monitoring Performance by Using The Query Store Documentation.