SQL: Scale Up vs. Scale Out
| |
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
| |
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
| |
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
| |
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
| |
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
| |
Use Set NOCOUNT ON in stored procedures.
| |
Do not use the sp_prefix for custom stored procedures.
| |
Execution Plans
| |
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
| |
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
| |
Avoid OPENXML over large XML documents.
| |
Avoid large numbers of concurrent OPENXML statements over XML documents
| |
Tuning
| |
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
| |
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
| |
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
| |
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.
|
Wednesday 20 September 2017
SQL Server Performance Checklist
Subscribe to:
Post Comments (Atom)
How to find table row count?
--Use below query to find table row count select so.name,sp.rows from sys.objects so inner join sys.partitions sp on so.object_id = sp.obj...
-
Warning: the following job steps cannot be reached with the current job step flow logic If you get this error, I would guess that you hav...
-
Data Type SQL Server SSIS SSIS OLE DB ADO.NET Variables Pipeline Buffer bigint Int64 DT_I8 LARGE_INTEGER I...
-
SELECT SERVERPROPERTY('MachineName') AS [MachineName], SERVERPROPERTY('ServerName') AS [ServerName], SERVERPROPERTY('I...
No comments:
Post a Comment