While i3is a great tool for people
who deal with reactive tuning (i.e. consultants,
external and internal),
once the emergency is over it’s useful to be able to
head off subsequent emergencies before they occur
Status
Findings
•Identified
resource consumption activity for specified
timeframes and instances
•Detected
top database DBProdA3
•Detected
top statement
08494.51344.13547.51072
•The
database consumes 56.67% of all SQL Server resources
utilized
Next Steps
•Explore
and analyze underlying objects
Status
Findings – identify problem source
•Growth
in the Orders &Order Details
tables was the cause
Next Steps – improve the situation
•Identify
and evaluate solutions
Status
Findings – improve the situation
•Indepth
for SQL Server advised two new indexes
•Comparison
of old and new execution plans showed improvement
•
Next Steps – verify the solution
•Verify
statement improvement in live environment
Status
Findings
•Statement
performance has improved •
But, the improvement has created an
I/O problem •
The statement accesses two objects in
different files
•
Next Steps
•Examine
storage structure
Status
Conclusion
•It
appeared that the indexes and tables were located on
separate files
•But,
they are located on the same physical device
•
Next Steps
•Locate
the files on different physical devices
Scenario Summary
We have successfully completed the methodology cycle:
Detect
Users experienced long
response time
Find
Identified the database &
statement that caused the problem
Improve
Create new indexes and
locate them on separate physical devices
