Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chris_wood99
Yak Posting Veteran
70 Posts |
 Posted - 2005-07-11 : 05:02:14
|
| Recently started new position and the main user complaint is performance in the application. I have checked the server configuration for bottlenecks and configuration and turned off the autoshrink option for starters but i'm happy the server is not being overloaded.I suspect it is probably the actual code thats been written, but to prove this I am now looking to run a trace in profiler and run it through the index tuning wizard..I'm not entirely sure what i should be tracing, should i simply trace all database activity for say a half hour period? if i then run this through the index tuning wizard how reliable are the indexing suggestions? my obvious fear is implementing the changes suggested only to make performance worse..i am currently building a test environment to try ann bench mark the performance... what is the best method for testing for improvements of client applications? |
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-07-11 : 08:22:39
|
| The 2 event categories should be the T-SQL event category, and the Stored Procedure Event Category. The classes I use are SQL:StmtCompleted, RPC:Completed, and RPC:Completed. Useful columns include but are not limited to End Time, Duration, Object ID, Object Name, Text Data, CPU... If you have the space to spare I would leave it running for the work day. Feed it back into a test server with as similar confgiuration as possible, and I would impliment the changes in increments on your production box.Sean RoussyPlease backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.I am available for consulting work. Just email me through the forum. |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-07-11 : 10:37:10
|
| When I do this, I like to get at least a few hours of Profiler output on at least 3 different days. I then use the Index Tuning wizard for each trace, and I only implement the indexes that are suggested every time.I then repeat theis process after creating the new indexes. When I get to the point that it does not suggest additional indexes, I stop.CODO ERGO SUM |
|
|
|
|
|
|
|
|
Index tuning