| Author |
Topic |
|
rtutus
Aged Yak Warrior
522 Posts |
 Posted - 2006-09-13 : 14:32:36
|
| There re certian times when I want to execute a sql request (select for example) then It gets too long before I get an answer. (that happens only some times exceptionnaly). What does that mean, is it that somebody is using heavily the DB or may be using Entreprise manager or what exactlyand how can I know who is responsible for taking all SQL server resources at that specefic time. What command or what tool can I use pls for this purpose.Thanks for your help. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-13 : 14:40:09
|
| You need to use SQL Profiler along with sp_who/sp_who2. The two who stored procedures will show you blocking issues. SQL Profiler will show you the rest. You might also want to run Performance Monitor to determine if there are any hardware bottlenecks.Tara Kizer |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-14 : 07:03:55
|
| If you are running some other software on the server that SQL is on that might be hogging the CPU.Kristen |
|
|
|
rtutus
Aged Yak Warrior
522 Posts |
Posted - 2006-09-14 : 09:41:54
|
| "use SQL Profiler along with sp_who/sp_who2". how do we do that what re the steps i never used profiler.Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-14 : 13:26:03
|
| You just run sp_who and sp_who2 in Query Analyzer. See if there are any blocked processes. SQL Profiler is too complex to get into detail here, so I'll refer you to SQL Server Books Online. What I typically do is use the default template, then add a couple of events to the trace. I save it to a table then run something like this to see the longest running queries:SELECT TOP 1000 Duration, TextDataFROM YourTraceTableWHERE TextData IS NOT NULLORDER BY Duration DESCTara Kizer |
|
|
|
rtutus
Aged Yak Warrior
522 Posts |
Posted - 2006-09-14 : 13:46:04
|
| I will read about profiler since it s a big thing.2 LITTLE QUESTIONS PLS:1-how can we tell that The processes re blocked, will theur status say: blocked or what.2-Will I have to guess what event might be causing SQL to slow down, so that I can add it to the trace to monitor or what.Because when SQL server is slow, I don t know what s slowing it down(May be I am just asking questions that don t make sense,sorry)Thank you |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-14 : 13:56:45
|
| 1. Run the stored procedures and you'll see the column that indicates this. 2. Figuring out what is causing the problem is going to take quite a bit of time. You can make guesses and attempt to fix those, but your guesses may be incorrect. We've been working on a production performance problem for the past few months and still don't have it resolved. We finally have enlisted the help of MS via a ticket with them. As soon as the slowness occurs again for us, we'll be sending the data that they requested to them for their analysis.Tara Kizer |
|
|
|
rtutus
Aged Yak Warrior
522 Posts |
Posted - 2006-09-14 : 14:01:37
|
| oops that seems too serious thanks guys. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-14 : 14:07:19
|
| A DBA's job is serious. This is the type of stuff we do all day.Tara Kizer |
|
|
|
Luis Martin
Yak Posting Veteran
54 Posts |
Posted - 2006-09-14 : 16:18:40
|
| As Tara Kizer said, you have to find out slow queries. Those queries can be the reason for slow performance. The way to find out is Profiler. Once you find those queries, you have to check execution plan. May be the queries were written in non efficient way or/and those queries need other indexes. |
|
|
|
|
our DB becomes slow sometimes