| Author |
Topic |
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
 Posted - 2007-11-15 : 12:55:33
|
| Hi everybody,We are having a application performance issue, where the user's are complaining that the application is really slow. They access the application thru citrix. I ran sp_who on the database side to see if there are any blocks, but the block column does'nt have anything greater than o. So trying to see how I can track where the problem is. Please help. Thanks. |
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-11-15 : 12:57:08
|
| Begin from mking a trace using SQL profiler |
 |
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2007-11-15 : 13:15:25
|
| What event classes and data columns should I trace in the profiler? |
|
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-11-15 : 13:17:36
|
| Begin from a standard pattern, which is by defaultisolate queries with high Reads, Writes, CPU and Duration |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2007-11-15 : 13:22:54
|
| Thanks, I am also copying the results into a sql table so that I can later query the data. I started with CPU >= 10,000, please let me know what values I can filter for reads and writes. |
|
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-11-15 : 13:36:15
|
| Better records everything and filter latera trace of 200-500K rows is enoughWhen you filter CPU>=10000 you exclude records, for example,CPU=100,Reads=10000,Duration=300000 (slow IO)or CPU=10,Reads=10,Writes=0,Duration=500000 (lock)So using that filter you can lose important clue. |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2007-11-15 : 13:42:07
|
| I got the trace results in a sql table, please let me know what I should check from the output. Thanks. |
|
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-11-15 : 15:40:15
|
| LOL. this is a creative part.Your users complained they everything was too slow? If you saved your trace into a table, select * from Trace where TextData is not null order by Duration descFind the longest queries. Find out, WHY do they take so long. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-15 : 23:28:17
|
| Or run index turning wizard with trace results. |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2007-11-16 : 16:40:33
|
quote:
Originally posted by rmiao
Or run index turning wizard with trace results.
Thanks for the response.I am using sql server 2000, now I have the results of the sql profiler trace in a table. Based on the exploration of index tuning wizard:Is this the way to run index tuning wizard on the trace results:From sql server enterprise manager, select tools ->Wizards, then Database->Create index wizard,select the tablename which has trace results and then go thru the steps,then select all the columns, then in index options: going with the default(make this a unique index is unchecked, fill factor:optimal).Can I run this during the production usage time, I have about 600k trace results rows in the table. Please let me know, thanks everybody for the help. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-17 : 01:40:38
|
| You are better to run it on testing server. |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2007-11-18 : 09:43:15
|
| Thanks, I shall try in the testing server, the steps which I described above are they correct to follow? |
|
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-11-18 : 15:14:06
|
| Index tuning wizard... Well, may be you can get something usefukl from it. I hope.I learnt the database optimisation for 15 years.Question "I have perf issues with my database. What do you recommend?" is like asking "I am not feeling well. What do you recommend?" |
|
|
|
|
Database performance issue