Page History

...

You can identify a performance problem by doing one or more of the following:

• Examining resource consumption of an entire instance and database
• Identifying heavy resource consumers
• Examining resource consumption over time
• Examining I/O waits of different storage devices connected to an a SQL Server instance
• Correlating SQL Server data with ERP/COM+ data
• Examining locking over time of a database entity
• Identifying offending SQL statements
• Identifying resource-consuming collapse statements or batches
• Examining the availability of an instance and database
• Examining the scalability of your application or SQL servers
• Identifying tempdb contentions

Anchor
Examiningresourceconsumptionofanentireinstanceanddatabase Examiningresourceconsumptionofanentireinstanceanddatabase

Examining resource consumption of an entire instance and database

When performing a tuning audit it is very important to analyze and understand the performance behavior of your instances and databases. You may have already drilled down to analyze the performance of a single query or program. However, examining the entire instance's behavior can alert you to the health of your system. The Instance (or Database) represents the performance of the average application. This helps you answer questions such as: "This query suffers from Log wait, but does the entire system suffer from lock wait?" If, for example, you discover that your system suffers from I/O wait and you tune a query that performs memory operations, you most likely will not improve the entire system's I/O wait problem. So examining the entire instance can provide a quick overview of the dominant resources consumed.

...

1. In the Instance list, choose the item you want to analyze.
2. In the Time Frame list, choose the period of time you want to analyze.
3. On the View controls in the Main area, click Overview. Examine the entire instance and determine which are the dominant resources that are consumed by your system.
4. On the Association controls in the Association area, click Databases.
5. On the Performance tab, place the cursor in the In MS-SQL column of the top database and view the information displayed on its resource consumption.

Note: As a rule of thumb, a healthy system should have a high Using CPU value, a 10–15% I/O Wait, a Log Wait based on the nature of the application, and the remaining states should show values that are as minimum as possible.

Anchor
Identifyingheavyresourceconsumers Identifyingheavyresourceconsumers

Identifying heavy resource consumers

Precise for SQL Server enables you to drill down to application components (such as Logins, Databases, Programs, Machines, and Statements) to determine which application component consumes the most resources. This is accomplished by clicking on an application component in the Association area. This process is iterative and you may continue to drill down until you discover the application component that you want to tune.

...

1. In the Instance list, choose the item you want to analyze.
2. In the Time Frame list, choose the period of time you want to analyze.
3. On the View controls in the Main area, click In MS-SQL to view a breakdown of the In MS-SQL time.
   Note: One of the components of In MS-SQL time that you can observe in the In MS-SQL graph is Internal Wait. You can view additional information on that particular component, over the selected time frame, by clicking Internal Waits on the View controls in the Main area. This view lets you analyze a breakdown of the Internal Wait parameter and determine which internal wait parameter is consuming the most time and resources.
4. On the Association controls in the Association area, click Programs.
5. On the Performance tab, place the cursor in the In MS-SQL column of the top program entity and view the information displayed regarding its resource consumption.
6. Drill down on the entity to focus on additional components.
7. Go back to the overview of the instance you originally selected.
8. In the Association area, use the Association controls to select different entities associated with the instance, and analyze the average duration of each entity.

Anchor
Examiningresourceconsumptionovertime Examiningresourceconsumptionovertime

Examining resource consumption over time

When you analyze and tune the performance of an application component (such as, database, program, or query) it is important to take into consideration its performance over time. Precise for SQL Server allows you to easily view the component's performance over a selected time period.

...

1. In the Instance list, choose the item you want to analyze.
2. In the Time Frame list, choose the period of time you want to analyze.
3. On the View controls in the Main area, click In MS-SQL.
4. Move the cursor over the bars in the graph to view the instance's resource consumption over time.

Anchor
ExaminingIOwaitsofdifferentstoragedevicesconnectedtoaSQLServerinstance ExaminingIOwaitsofdifferentstoragedevicesconnectedtoaSQLServerinstance

Examining I/O waits of different storage devices connected to an SQL Server instance

If you have determined that the application is suffering from I/O waits, you may want to examine how the I/O waits are divided amongst the various storage devices. You will want to balance the I/O waits among all the storage devices.

...

1. In the Instance list, choose the item you want to analyze.
2. In the Time Frame list, choose the period of time you want to analyze.
3. On the View controls in the Main area, click In MS-SQL.
4. On the Association controls in the Association area, click Storage Devices.
5. Observe the I/O waits for each storage device unit.
6. If you locate storage devices suffering from I/O waits, you can check which logical files reside on the storage device and move a few to a different storage device. Drill down from the storage device and analyze the Logical Files entities. Once you have identified which logical file(s) should be moved to a different storage device, check the I/O pattern on that storage device, to ensure that the move will not cause the I/O pattern on the new storage device to deteriorate.

Anchor
CorrelatingSQLServerdatawithERPCOMdata CorrelatingSQLServerdatawithERPCOMdata

Correlating SQL Server data with ERP/COM+ data

Certain obstacles make it difficult to investigate ERP data in the SQL Server Tier. Most activity is registered in SQL Server under generic names. For example, in SAP, all activities are listed under a single login (SAP\SAPServiceSQ1) and some generic program names (R3D01(1)sp crea OLEDB).

...

...

...

...

...

To correlate SQL Server data with ERP/COM+ data

1. In the Instance list, choose the item you want to analyze.
2. In the Time Frame list, choose the period of time you want to analyze.
3. On the Association controls in the Association area, click Siebel View/Programs.
4. Drill down to the entity whose data you want to correlate.
5. On the View controls in the Main area, click In MS-SQL.
6. Move the cursor over the instance heading to correlate SQL Server information with ERP information

Anchor
Examininglockingovertimeofadatabaseentity Examininglockingovertimeofadatabaseentity

Examining locking over time of a database entity

While many think that locking is bad, the truth is that locking is good—it maintains data integrity. The situations to avoid are contentions. Contentions are situations in which one application waits for a resource that is being held by another application.

...

• Look at the Overview view of your instance (database, program) to understand the percentage of Lock Wait your application suffers.
• Examine the Lock Wait over a selected time period. By examining resource consumption over time you can identify if Lock Wait occurred all the time, or only on specific dates.
• Try to gain an understanding of the Lock types your applications were blocked on. An overtime graph of the different Lock types can be displayed in the Locking view.
• Examine the locked objects. You can display a list of all locked objects and compare them according to lock time.
• Once you have identified all of the above, you also need to assess who is blocking who. For example, you will need to determine which session is blocking which session? . The locks tree displays all the contentions that occurred and shows which sessions were involved in contentions.

...

1. In the Instance list, choose the item you want to analyze.
2. In the Time Frame list, choose the period of time you want to analyze.
3. In the Association area drill down to the database entity you want to analyze.
4. On the View controls in the Main area, click Locking.
5. Examine the locking of the selected entity in the graph, for the period of time you selected. Move the cursor over the bars in the graph to view the Lock Wait breakdown (such as, table locks, row locks, page locks, key locks and other, in addition to total Lock Wait time for the selected time slice). Examine the locks tree to observe all the contentions that occurred and determine sessions were involved in contentions.
6. On the Association controls in the Association area, click Locked Objects.

Anchor
IdentifyingoffendingSQLstatements IdentifyingoffendingSQLstatements

Identifying offending SQL statements

Having identified a major resource-consuming entity, you can further drill down to the statements and batches executed by that entity. These may include long running SQL statements and SQL statements that use few resources but were executed frequently.

...

• View which entities executed the statement.
• View resources consumed by the statement, which enables you to determine if the statement is CPU or I/O bound, waiting for a lock, etc.
• View resource consumption patterns, which allow you to observe if the statement execution is affected by excessive use of the same resource by another statement.
• Tune the statement in the SQL tab.

To identify offending SQL Statements

1. In the Instance list, choose the item you want to analyze.
2. In the Time Frame list, choose the period of time you want to analyze.
3. In the Association area, drill down to the top resource-consuming database.
4. On the Association controls in the Association area, click Statements. On the Performance tab view which entities executed the statement and view the resources consumed by the statement.
5. On the Plan tab, in the Association area, view a summary of the statement's access plan.

Anchor
Identifyingresourceconsumingcollapsestatementsorbatches Identifyingresourceconsumingcollapsestatementsorbatches

Identifying resource-consuming collapse statements or batches

Many statements may look the same but differ in their literals. When you want to identify the most resource-consuming statements you would want to treat them as the same statement. Precise for SQL Server lets you identify the most resource-consuming collapsed statements or batches.

...

If you observe different performance patterns for the same statements you can also examine the distribution of their execution plans. If they have different execution plans this may explain the different performance patterns.

...

To identify resource-consuming collapse statements or batches

1. In the Instance list, choose the item you want to analyze.
2. In the Time Frame list, choose the period of time you want to analyze.
3. On the Association controls in the Association area, click Statements (Collapsed).
4. Drill down to the top resource-consuming collapsed statement.
5. On the Performance tab in the Association area, examine the different statements having the selected collapsed form to examine the performance of each individual instance.
6. On the Association controls in the Association area, click Plans to analyze the various access plans of the collapsed statement you selected.

Anchor
Examiningtheavailabilityofaninstanceanddatabase Examiningtheavailabilityofaninstanceanddatabase

Examining the availability of an instance and database

Since Because Precise for SQL Server maintains a constant connection with your SQL Server instance, it can easily report on the availability of the instance and databases.

The availability report displays the percentage of time the instance was up and the amount of time the instance was unavailable, over the selected time period.Note:

To examine the availability of an instance and database

1. In the Instance list, choose the instance you want to analyze.
2. In the Time Frame list, choose the period of time you want to analyze.
3. On the View controls in the Main area, click Availability.
4. Move the cursor over the Availability graphs and Unavailability Times graphs to observe how often the instance was available during the selected time period.

Anchor
ExaminingthescalabilityofyourapplicationorSQLservers ExaminingthescalabilityofyourapplicationorSQLservers

Examining the scalability of your application or SQL servers

Applications are called upon to support the addition of more and more users, and data, over the years. One of today's tasks is ensure that our application and servers are scalable for tomorrow's tasks. Precise for SQL Server enables you to examine if the application is scalable, or if the SQL servers are scalable.

...

1. In the Instance list, choose the item you want to analyze.
2. In the Time Frame list, choose the period of time you want to analyze.
3. On the View controls in the Main area, click Scalability.
4. In the Session Duration (Avg) vs. Sessions graph, observe the number of sessions and the impact of the average session duration, over the selected time period. For example, if you observe that average session duration increases when the number of sessions decrease, this may indicate that the application has a scalability problem.
5. On the View controls in the Main area, click System  Scalability.
6. Analyze the In MS-SQL vs. SQL Server CPU usage graph to determine if SQL Server CPU consumption growth affects the server CPU consumption and I/O wait times. Check the Physical I/O Operations vs. Queue Length graph to determine if a growth in I/O requests increases the time I/Os have to wait in the queue to be served.

Anchor
Identifyingtempdbcontentions Identifyingtempdbcontentions

Identifying tempdb contentions

Tempdb is the most heavily shared resource across the entire instance and can affect the performance of all applications. It is therefore important to monitor its usage. A high tempdb wait value may indicate a bottleneck in tempdb.

...

1. In the Instance list, choose the item you want to analyze.
2. In the Time Frame list, choose the period of time you want to analyze.
3. On the View controls in the Main area, click In MS-SQL to view a breakdown of the In MS-SQL time. Check the Tempdb Wait Time component of the In MS-SQL counter.
4. On the Association controls in the Association area, click Programs.
5. On the Performance tab, place the cursor in the In MS-SQL column of the top program entity and check which portion of resource consumption can be attributed to tempdb wait.
6. Drill down on the entity to focus on additional components.

...