Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Sql Server 2000 is maxed out, and I am not sure why, but I believe it may be due to the fact that a query or queries were run that are slowing it down. Is there a way to determine which query caused the slow down, and at what time?

share|improve this question

3 Answers 3

up vote 3 down vote accepted

Yes, if you have the Enterprise Manager tool, you can do a "Profile trace" with that. Then, after you have traced the queries, you can analyze the data manually (with your own eyes) or you can use the SQL Analyzer tool to actually analyze individual queries (using Show Execution Plan ??) and get detailed info on their performance. (this is not possible using SQL Express versions or MSDE )

share|improve this answer
    
I have sql 2000, but I'm also using Sql 2008 R2 management studio tool. Is it possible to get that to work with SQL Server 2000 with regard to the profile feature? –  Marc Noon Jul 22 '11 at 5:21

A quick check for blocking - not sure where I picked this up (might be K Delaney):

IF exists (select * from master.dbo.sysprocesses where spid IN(select blocked from master.dbo.sysprocesses)) select spid, status, loginame=substring(loginame,1,12), hostname=substring(hostname,1,12), blk=CONVERT(char(3), blocked), dbname=substring(DB_NAME(dbid),1,10), cmd, waittype from master.dbo.sysprocesses where spid in (select blocked from master.dbo.sysprocesses) and blocked = 0 else select 'No blocking process found'

share|improve this answer
    
ahhh, so if the process is blocking its still running on the server? I think sp_locks, and the sp_who2 thing is also something else I discovered on this, thanks. –  Marc Noon Jul 22 '11 at 5:26
1  
Yep, the BlkBy column in sp_who2 will get you thre too. –  jl. Jul 22 '11 at 13:34

SQL Server Profiler

share|improve this answer
    
This tool is only necessary if your running SQL Express versions of 2005 and 2008. If you have FULL versions of SQL Server or SQL2000, those versions have the Microsoft version of the Profiler tool already included. –  djangofan Jul 20 '11 at 20:51
1  
He said he has 2000. He wants to see performance ... Profiler was the best tool in 2000 to do that. Where did I mention Express, 2005 or 2008? –  Adrien Jul 20 '11 at 20:53
    
I do have all the above. Thanks for the info. –  Marc Noon Jul 22 '11 at 5:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.