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 |
|
igorblackbelt
Constraint Violating Yak Guru
407 Posts |
 Posted - 2006-08-18 : 17:46:42
|
| Hello All -i tried BOL for this, but it wasn't really helpful, hopefully you guys will be able to help me out on this.A group that uses one of our applications (asp) has been complaining about a slow down on the app, my co-worker kept saying this could be becuase of our latest TLog backup implementation (every 30 minutes). I don't think that's the issue, so I went to check the "Current Activity" section and bam! There was the database listed over 20 times on the list. Now, to me looks like an issue on that database specifically, but I don't know how to read that, most of the processes for that db are in sleeping status, only 3 in runnable status.I don't think this is the best thing to do, should I kill these processes ? What should I do ?Thanks for the help, I'll post any progress that I make.---"There's no sexy way to carry a lunch bag." My boss |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-18 : 18:05:24
|
| We've got hundreds of accounts connected to our database servers, so I don't think that 20 is going to be an issue for you. Sleeping status just means that the user isn't doing anything right now. I certainly wouldn't kill them as it doesn't sound like that's your problem.I would run SQL Profiler to determine what queries are running slowly. Trace the data to a table then run this:SELECT TOP 100 Duration, TextDataFROM YourTraceTableNameWHERE TextData IS NOT NULLORDER BY Duration DESCThe above query will show you the 100 worst performing queries. Check to see if they need to be optimized or perhaps indexes added. Check the execution plan of these queries.I would also checking for blocking by using sp_who/sp_who2 in Query Analyzer. Lastly, I would check Performance Monitor for hardware bottlenecks.Tara Kizer |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-08-18 : 18:52:43
|
| In addition, you should investigate to see if the performance of the database server is really the cause of the problem. Web servers, network activity, poor application code, bad network cards or switches, and many other things could be causing the problem.I really doubt that transaction log backups are the cause of the problem.CODO ERGO SUM |
|
|
|
igorblackbelt
Constraint Violating Yak Guru
407 Posts |
Posted - 2006-08-21 : 13:49:54
|
| Thanks for the replies to both of you guys, I ran the profiler from 7 AM until 1 PM today and got some interesting results, about 80% of the queries that has the highest duration are queries from the DB mentioned on my first post, that DB is the one used on the app developed by my co-worker. Sounds like we need to work on indexes and query optimiztion.Question: Is it possible to run an execution plan for a query without running the query ?---"There's no sexy way to carry a lunch bag." My boss |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-21 : 13:51:55
|
quote:
Originally posted by igorblackbelt
Question: Is it possible to run an execution plan for a query without running the query ?
Yes. You just need to select the option to display the estimated execution plan.Tara Kizer |
|
|
|
rav3n0u5
Yak Posting Veteran
88 Posts |
Posted - 2006-08-21 : 21:48:27
|
| But if your stats are out of date, the estimated execution plan will not accurately reflect what actually runs.sp_updatestats should help with that. |
|
|
|
|
|
|
|
|
Help reading "Current Activity" and "Process Info"