Database Design
1. Bad logical database design results in bad physical database design, and generally results in poor database performance. So, if it is your responsibility to design a database from scratch, be sure you take the necessary time and effort to get the logical database design right. Once the logical design is right, then you also need to take the time to get the physical design right.
2. Normalize your data to ensure best performance.
3. Take advantage of SQL Server’s built-in referential integrity. You don’t need to write your own.
4. Always specify the narrowest columns you can. In addition, always choose the smallest data type you need to hold the data you need to store in a column. The narrower the column, the less amount of data SQL Server has to store, and the faster SQL Server is able to read and write data.
5. Try to avoid performing both OLTP and OLAP transactions within the same database.
Queries and Stored Procedures
1. Maintain all code in a source control system.
2. Keep transactions as short as possible. This reduces locking and increases application concurrency, which helps to boost performance.
3. Avoid using query hints unless you know exactly what you are doing, and you have verified that the hint actually boosts performance.
4. Encapsulate all transactions within stored procedures, including both the BEGIN TRANSACTION and COMMIT TRANSACTION statements in the procedure.
5. Use the least restrictive transaction isolation level possible for your user connection, instead of always using the default READ COMMITTED.
6. Whenever a client application needs to send Transact-SQL to SQL Server, send it in the form of a stored procedure instead of a script or embedded Transact-SQL. Stored procedures offer many benefits, including:
a. Reduced network traffic and latency, boosting application performance.
b. Stored procedure execution plans can be reused, staying cached in SQL Server’s memory, reducing server overhead.
c. Client execution requests are more efficient. For example, if an application needs to INSERT a large binary value into an image data column not using a stored procedure, it must convert the binary value to a character string (which doubles its size), and send it to SQL Server. When SQL Server receives it, it then must convert the character value back to the binary format. This is a lot of wasted overhead. A stored procedure eliminates this issue as parameter values stay in the binary format all the way from the application to SQL Server, reducing overhead and boosting performance.
d. Stored procedures help promote code reuse. While this does not directly boost an application’s performance, it can boost the productivity of developers by reducing the amount of code required, along with reducing debugging time.
e. Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients (assuming you keep the parameters the same and don’t remove any result sets columns). This saves developer time.
f. Stored procedures provide better security to your data
7. SET NOCOUNT ON at the beginning of each stored procedure you write. This statement should be included in every stored procedure you write.
8. Before you are done with your stored procedure code, review it for any unused code, parameters, or variables that you may have forgotten to remove while you were making changes, and remove them.
9. For best performance, all objects that are called within the same stored procedure should all be owned by the same object owner or schema, preferably dbo, and should also be referred to in the format of object_owner. object_name or schema_owner.object_ name.
10. One way to help ensure that stored procedures query plans are reused from execution to execution of the same stored procedure is to ensure that any user connections information, SET options, database options, or SQL Server configuration options don’t change from execution to execution of the same stored procedure. If they do change, then SQL Server may consider these same stored procedures to be different, and not be able to reuse the current query plan stored in cache.
Transact-SQL
1. Don’t be afraid to make liberal use of in-line and block comments in your Transact-SQL code, they will not affect the performance of your application and they will enhance your productivity when you or others come back to the code and try to modify it.
2. If possible, avoid using SQL Server cursors. They generally use a lot of SQL Server resources and reduce the performance and scalability of your applications. If you need to perform rowby- row operations, try to find another method to perform the task.
3. When using the UNION statement, keep in mind that, by default, it performs the equivalent of a SELECT DISTINCT on the final result set. In other words, UNION takes the results of two like recordsets, combines them, and then performs a SELECT DISTINCT in order to eliminate any duplicate rows. This process occurs even if there are no duplicate records in the final recordset. If you know that there are duplicate records, and this presents a problem for your application, then by all means use the UNION statement to eliminate the duplicate rows. But if not, use UNION ALL, which is less resource intensive.
4. Carefully evaluate whether your SELECT query needs the DISTINCT clause or not. Some developers automatically add this clause to every one of their SELECT statements, even when it is not necessary.
5. In your queries, don’t return column data you don’t need. For example, you should not use SELECT * to return all the columns from a table if you don’t need all the data from each column. In addition, using SELECT * may prevent the use of covered indexes, further potentially hurting query performance.
6. Always include a WHERE clause in your SELECT statement to narrow the number of rows returned. Only return those rows you need.
7. When you have a choice of using the IN or the BETWEEN clauses in your Transact-SQL, you will generally want to use the BETWEEN clause, as it is more efficient.
8. If you need to write a SELECT statement to retrieve data from a single table, don’t SELECT the data from a view that points to multiple tables. Instead, SELECT the data from the table directly, or from a view that only contains the table you are interested in. If you SELECT the data from the multi-table view, the query will experience unnecessary overhead, and performance will be hindered.
9. If your application allows users to run queries, but you are unable in your application to easily prevent users from returning hundreds, even thousands of unnecessary rows of data, consider using the TOP operator within the SELECT statement. This way, you can limit how many rows are returned, even if the user doesn’t enter any criteria to help reduce the number or rows returned to the client.
10. Try to avoid WHERE clauses that are non-sargable. If a WHERE clause is sargable, this means that it can take advantage of an index (assuming one is available) to speed completion of the query. If a WHERE clause is nonsargable, this means that the WHERE clause (or at least part of it) cannot take advantage of an index, instead performing a table/index scan, which may cause the query’s performance to suffer. Non-sargable search arguments in the WHERE clause, such as “IS NULL”, “<>”, “!=”, “!>”, “!<”, “NOT”, “NOT EXISTS”, “NOT IN”, “NOT LIKE”, and “LIKE ‘%500’” generally prevent (but not always) the query optimizer from using an index to perform a search. In addition, expressions that include a function on a column, expressions that have the same column on both sides of the operator, or comparisons against a column (not a constant), are not sargable.
SQL Server 2005 CLR
1. Don’t turn on the CLR unless you will be using it.
2. Use the CLR to complement Transact- SQL code, not to replace it.
3. Standard data access, such as SELECT, INSERTs, UPDATEs, and DELETEs are best done via Transact-SQL code, not the CLR.
4. Computationally or procedurally intensive business logic can often be encapsulated as functions running in the CLR.
5. If a procedure includes both significant data access and computation and logic, consider separating the procedure code in the CLR that calls the Transact-SQL stored procedure that does most of the data access.
6. Use the CLR for error handling, as it is more robust than what Transact-SQL offers.
7. Use the CLR for string manipulation, as it is generally faster than using Transact- SQL.
8. Use the CLR when you need to take advantage of the large base class library.
9. Use the CLR when you want to access external resources, such as the file system, Event Log, a web service, or the registry.
10. Set CLR code access security to the most restrictive permissions as possible.
XML
1. XML is best used for modeling data with a flexible or rich structure.
2. Don’t use XML for conventional, structured data.
3. Store object properties as XML data in XML data types.
4. When possible, use typed XML data types over untyped XML data to boost performance.
5. Add primary and secondary indexes to XML columns to speed retrieval.