SQL Server Performance Tuning Considerations
When using a SQL Server Platform, the responsibility for maintaining performance of the SQL Server database is a task of the SQL Administrator (SA). The following information can assist with this task and should be considered when performance tuning a SQL Database Instance and database for P6 EPPM:
Monitoring and Tuning for Performance
The goal of monitoring databases is to assess how a server is performing. Effective monitoring involves taking periodic snapshots of current performance to isolate processes that are causing problems, and gathering data continuously over time to track performance trends. Click here for a Microsoft TechNet site which will provide useful information in the guidance of how to perform these tasks effectively.
Highlighted information which should be considered from this section:
Optimizing SQL Server and Database Performance
Optimizing SQL Server Performance
Microsoft SQL Server automatically tunes many of the server configuration options, so system administrators are required to do little, if any, tuning. Configuration options can be modified by the system administrator however, but we generally recommend that administrators leave the server configuration options at their default values.
Highlighted information which may be considered from this section:
Optimizing Database Performance
Database optimization will ensure that you are receiving the best performance possible as usage and data increases over time and the following optimizations should be considered:
Snapshot Isolation:
Accessing data using snapshot isolation decreases the possibility of blocking transactions attempting to modify data. For instruction to ensure Snapshot isolation is being used, please see the following MSDN Article: Using Snapshot Isolation.
Index Tuning:
The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time however, these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly. You can remedy index fragmentation by either reorganizing an index or by rebuilding an index.
For recommendations to detect fragmentation, please see the following MSDN Article: Detect Fragmentation
- After detecting fragmentation, if percentage of logical fragmentation is less than 30%, index Reorganization can be used: Index Reorg
- After detecting fragmentation, if percentage of logical fragmentation is greater than 30%, index rebuild can be used: Index Rebuild
Statistics:
Statistics are used by the SQL Server query optimizer to choose the most efficient plan for retrieving or updating data. Improper statistics gathered against the database could cause the query optimzer to choose an unecessary, ineffiecient plan (For Example: A query performs a full table scan instead of using an index if applicable).
For instruction on how to gather statistics against a SQL Database, please refer to the following knowledge base article:
Doc ID 904287.1
Doc Title: See How to Gather Statistics on a SQL Database
For further information regarding SQL Statistics and the SQL Server Query Optimizer: Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
Database Shrinking
Each file within a database can be reduced to remove unused pages. Although the Database Engine will reuse space effectively, there are times when a file no longer needs to be as large as it once was; shrinking the file may then become necessary. Both data and transaction log files can be reduced, or shrunk. The database files can be shrunk manually, either as a group or individually, or the database can be set to shrink automatically at specified intervals.
For instruction, please refer to the following MSDN Article: Shrinking a Database
See Also:
