http://msdn.microsoft.com/en-us/library/ff647681.aspx[@more@]
SQL: Scale Up vs. Scale Out
Check | Description |
---|---|
![]() | Optimize the application before scaling up or scaling out. |
![]() | Address historical and reporting data. |
![]() | Scale up for most applications. |
![]() | Scale out when scaling up does not suffice or is cost-prohibitive. |
Schema
Check | Description |
---|---|
![]() | Devote the appropriate resources to schema design. |
![]() | Separate online analytical processing (OLAP) and online transaction processing (OLTP) workloads. |
![]() | Normalize first, denormalize later for performance. |
![]() | Define all primary keys and foreign key relationships. |
![]() | Define all unique constraints and check constraints. |
![]() | Choose the most appropriate data type. |
![]() | Use indexed views for denormalization. |
![]() | Partition tables vertically and horizontally. |
Queries
Check | Description |
---|---|
![]() | Know the performance and scalability characteristics of queries. |
![]() | Write correctly formed queries. |
![]() | Return only the rows and columns needed. |
![]() | Avoid expensive operators such as NOT LIKE. |
![]() | Avoid explicit or implicit functions in WHERE clauses. |
![]() | Use locking and isolation level hints to minimize locking. |
![]() | Use stored procedures or parameterized queries. |
![]() | Minimize cursor use. |
![]() | Avoid long actions in triggers. |
![]() | Use temporary tables and table variables appropriately. |
![]() | Limit query and index hint use. |
![]() | Fully qualify database objects. |
Indexes
Check | Description |
---|---|
![]() | Create indexes based on use. |
![]() | Keep clustered index keys as small as possible. |
![]() | Consider range data for clustered indexes. |
![]() | Create an index on all foreign keys. |
![]() | Create highly selective indexes. |
![]() | Create a covering index for often-used, high-impact queries. |
![]() | Use multiple narrow indexes rather than a few wide indexes. |
![]() | Create composite indexes with the most restrictive column first. |
![]() | Consider indexes on columns used in WHERE, ORDER BY, GROUP BY, and DISTINCT clauses. |
![]() | Remove unused indexes. |
![]() | Use the Index Tuning Wizard. |
Transactions
Check | Description |
---|---|
![]() | Avoid long-running transactions. |
![]() | Avoid transactions that require user input to commit. |
![]() | Access heavily used data at the end of the transaction. |
![]() | Try to access resources in the same order. |
![]() | Use isolation level hints to minimize locking. |
![]() | Ensure that explicit transactions commit or roll back. |
Stored Procedures
Check | Description |
---|---|
![]() | Use Set NOCOUNT ON in stored procedures. |
![]() | Do not use the sp_prefix for custom stored procedures. |
Execution Plans
Check | Description |
---|---|
![]() | Evaluate the query execution plan. |
![]() | Avoid table and index scans. |
![]() | Evaluate hash joins. |
![]() | Evaluate bookmarks. |
![]() | Evaluate sorts and filters. |
![]() | Compare actual versus estimated rows and executions. |
Execution Plan Recompiles
Check | Description |
---|---|
![]() | Use stored procedures or parameterized queries. |
![]() | Use sp_executesql for dynamic code. |
![]() | Avoid interleaving data definition language (DDL) and data manipulation language (DML) in stored procedures, including the tempdb database DDL. |
![]() | Avoid cursors over temporary tables. |
SQL XML
Check | Description |
---|---|
![]() | Avoid OPENXML over large XML documents. |
![]() | Avoid large numbers of concurrent OPENXML statements over XML documents. |
Tuning
Check | Description |
---|---|
![]() | Use SQL Profiler to identify long-running queries. |
![]() | Take note of small queries called often. |
![]() | Use sp_lock and sp_who2 to evaluate locking and blocking. |
![]() | Evaluate waittype and waittime in master..sysprocesses. |
![]() | Use DBCC OPENTRAN to locate long-running transactions. |
Testing
Check | Description |
---|---|
![]() | Ensure that your transactions logs do not fill up. |
![]() | Budget your database growth. |
![]() | Use tools to populate data. |
![]() | Use existing production data. |
![]() | Use common user scenarios, with appropriate balances between reads and writes. |
![]() | Use testing tools to perform stress and load tests on the system. |
Monitoring
Check | Description |
---|---|
![]() | Keep statistics up to date. |
![]() | Use SQL Profiler to tune long-running queries. |
![]() | Use SQL Profiler to monitor table and index scans. |
![]() | Use Performance Monitor to monitor high resource usage. |
![]() | Set up an operations and development feedback loop. |
Deployment Considerations
Check | Description |
---|---|
![]() | Use default server configuration settings for most applications. |
![]() | Locate logs and the tempdb database on separate devices from the data. |
![]() | Provide separate devices for heavily accessed tables and indexes. |
![]() | Use the correct RAID configuration. |
![]() | Use multiple disk controllers. |
![]() | Pre-grow databases and logs to avoid automatic growth and fragmentation performance impact. |
![]() | Maximize available memory. |
![]() | Manage index fragmentation. |
![]() | Keep database administrator tasks in mind. |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/66009/viewspace-1057163/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/66009/viewspace-1057163/