SQL Server DBA Best Practices(2)

Application Design and Coding Best Practices

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 non-sharable, 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.

SQL Server Component Best Practices

SSIS

1. Schedule large data imports, exports, or transformation on your production servers during less busy periods of the day to reduce the impact on your users.

2. Consider running large, resource intensive SSIS packages on a dedicated physical server.

3. Rename all default name and description properties using standard naming conventions. This will make it easier for you and others to debug or modify your packages.

4. Include annotations in your packages to make it easier for you, and others, to understand what is going on.

5. Use Sequence Containers to organize package structures into logical work units.

6. Use Namespaces for your packages.

7. Only scope variables for the containers for which they are needed.

8. If you know that data is already presorted, set IsSorted=TRUE. Doing so can help prevent unnecessary sorts, which use up resources unnecessarily.

9. When selecting columns from a table to return, return only what is needed. In addition use a Transact-SQL statement in an OLE DB Source component, or the Lookup Component, instead of selecting an entire table. This way, you prevent unnecessary data from being processed.

10. When INSERTing data, use the SQL Server Destination instead of the OLE DB Destination to boost performance.

Reporting Services

1. Ideally, dedicate one or more physical servers for Reporting Services.

2. The SQL Server databases used to produce the raw data for reports should be on their own dedicated physical servers.

3. Only return the actual data you need in the report, no more or no less.

4. Optimize the performance of the Transact-SQL code you are using to return data for your report before you actually design the physical appearance of the report.

5. Manage all report code and .rdl files using a Source Control system.

Notification Services

1. Implementing Notifications Services requires careful planning and design.

2. SQL Server 2000 and 2005 Notification Services can be a heavy burden on a SQL Server, depending on how it is used. For best overall performance, it is recommended to run Notification Services to a dedicated SQL Server.

3. Notification Services makes heavy use of the tempdb database. Because of this, you should consider two things: First, consider allocating a minimum size for the tempdb database, so that when SQL Server is restarted, it will create a new tempdb database of an appropriate size. This prevents SQL Server from having to expand the tempdb database size automatically, which can lead to short bursts of poor I/O performance as the database is expanded. Second, consider installing the tempdb database on its own dedicated physical disk drive or array in order to reduce I/O contention.

4. Notification Services also makes heavy use of the transaction log. To reduce I/O contention, consider locating the transaction log file on its own dedicated physical disk drive. In addition, the Full Backup Recovery model should be used to ensure a minimum of lost data, should there be any problem. Also, because the log is heavily used, be sure that it is backed up (log truncated) often so that it does not fill up your disk space and stop your server.

5. While many indexes are created automatically by Notification Services when the Notification Services database is created, not every potentially useful index is created. Once your system is in production, consider taking a Profiler Trace during a very busy time of the server and use the Index Wizard or the Database Engine Tuning Advisor to identify potential new indexes.

Analysis Services

1. Always run OLAP applications on their own dedicated servers, never sharing a server running OLTP applications. The two types of applications are mutually exclusive when it comes to performance tuning.

2. When designing OLAP cubes, don’t include measures or dimensions that your users won’t use. The way to prevent this is good systems analysis and design. Unused data will increase the size of your cubes and slow performance.

3. When using the star schema design, at a minimum, you will create a non-clustered index on the primary key of each dimension table and a non-clustered index on each of the related foreign keys. From there, you can create no clustered indexes on additional columns that will be queried on frequently. You don’t need to create composite indexes to create covering indexes because SQL Server will use index intersection to do this for you automatically.

4. When you create indexes on your data warehouses and data marts, use a FILLFACTOR of 100 to ensure that the index pages are as full as possible. This reduces unnecessary I/O, speeding up performance.

5. Schedule cube updates on your production servers during less busy periods of the day to reduce the impact on your users.

Service Broker

1. Planning for a Service Broker implementation requires that you answer these questions: a. Decide the role that Service Broker will play in the application. b. Identify the required information for each step of a conversation in order to complete each task. c. Select where the message processing logic will run. d. Decide on the technology to be used to implement your application e. Identify which server components will your application use the most.

2. Before rolling out a Service Broker application, test it thoroughly in a test environment that closely represents your actual production environment.

3. After a Service Broker application has been designed and written, it must be rolled out. This is generally done with a set of installation scripts that are run to create the necessary message types, contracts, queues, services, and stored procedures. Ideally, the DBA will review these scripts before running them.

4. Once the installation scripts are run, it is the job of the DBA to configure the necessary security principles, certificates, remote service bindings, and required routes.

5. Because a Service Broker installation can be complex, it must be fully documented so that it can be easily replicated and uninstalled if necessary.

Full-Text Search

1. If using SQL Server 2005, full-text catalogs can be attached and detached along with their database. This is the best way to move a database and catalog together.

2. If using SQL Server 2005, use BACKUP and RESTORE to backup and restore your full-text catalogs. Catalogs should be backed up when the database is backed up.

3. Generally speaking, limited hardware resources contribute most to poor Full- Text Search performance. Check for CPU, disk I/O, and memory bottlenecks, and if they are identified, they often must be corrected with the purchase of larger hardware.

4. Regularly defragment the index on the base tables that feed the Full-Text Search engine. In addition, regularly reorganize the full-text catalog.

5. Keep full-text key columns as narrow as possible to speed performance.

 

 

文章下载：

¡   http://www.sql-club.com/images/www_sql-club_com/andrewknight/747/o_DBA%20Best%20Practise.rar

 

 

资源：

¡   微软BI开拓者

¡ SQLServer2005怎样关联事件探查器和系统监视器的性能计数数据（之一）、之二

¡  充分利用SQLServer2005的性能工具Performance Dashboard（之一）、之二、之三

¡  SQLServer索引创建策略——引言（I）、引言（II）、第1部分：离线、连续、非分区、第2部分：离线、并行、非分区、第2部分：离线、并行、非分区（无统计计划（无柱状图））

¡   临时表vs.表变量以及它们对SQLServer性能的影响

¡   怎样做Windows Server 2003群集（之一） 、之二