Hi,

Im having a problem with large read queries initiated by end users causing 
problems with my write times.

i would like to understand how to ID the query while its running and kill 
it.

I use profiler and i see the query after its executed but how do i ID a 
process thats causing the problem while its running ?

sp_who

kill_sp

Thanks for any info
Scott

Scott (nospam123@yahoo.co.uk) writes:
> Im having a problem with large read queries initiated by end users causing 
> problems with my write times.
> 
> i would like to understand how to ID the query while its running and kill 
> it.
> 
> I use profiler and i see the query after its executed but how do i ID a 
> process thats causing the problem while its running ?
 
To start with, what problem are they causing? Blocking or just too much
load on the server? You say they they cause problem with your write times,
which makes me think that blocking is the issue.

In either case, my gut reaction is that killing other users, may not be
the best solution. After all, the users may have submitted these queries,
because they wanted the results from them. Of course, it is not 
inconceivable that the users write their queries incorrectly, but 
nevertheless.

If the issue is blocking, and you have SQL 2005 or later, you should 
consider enabling READ COMMITTED SNAPSHOT ISOLATION, to prevent readers
from blocking writers.

If the issue is just an overall excess in resource consumption, running
sp_who2 a couple of times, and paying attention to the delta in the CPU
and DiskIO columns should help.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

I run the following quick and dirty query to ID the top io consumers:

select * into #tmp
  from sysprocesses
go

-- wait for a few seconds and thene execute the following
select top 20 t2.physical_io - t1.physical_io as io_diff, t2.*
  from #tmp t1, sysprocesses t2
where t1.spid = t2.spid and t1.login_time =t2.login_time
order by io_diff desc

Since it's quick and dirty, you need to be careful with the result. But most 
time, it's quite effective.

Linchi

"Scott" wrote:

> Hi,
> 
> Im having a problem with large read queries initiated by end users causing 
> problems with my write times.
> 
> i would like to understand how to ID the query while its running and kill 
> it.
> 
> I use profiler and i see the query after its executed but how do i ID a 
> process thats causing the problem while its running ?
> 
> sp_who
> 
> kill_sp
> 
> Thanks for any info
> Scott
> 
> 
>

Thanks for the reply.

Erland:

Write times are very important , user queries (i.e web app reporting) are 
slowing them.

Is it a resource problem ?  i dont think so as no sql performance errors.

I guess its a blocking issue but how can i confirm.



Linchi:

Thanks for the scripts. It would only use used in an utter emergency.



All the best.

Not having sql performance errors does NOT mean you don't have a resource 
problem.

The following little script can show if you have blocking and whether your 
processes are involved:

select * from master..sysprocesses where spid in (select blocked from 
master..sysprocesses) or blocked != 0

Linchi

"Scott" wrote:

> Thanks for the reply.
> 
> Erland:
> 
> Write times are very important , user queries (i.e web app reporting) are 
> slowing them.
> 
> Is it a resource problem ?  i dont think so as no sql performance errors.
> 
> I guess its a blocking issue but how can i confirm.
> 
> 
> 
> Linchi:
> 
> Thanks for the scripts. It would only use used in an utter emergency.
> 
> 
> 
> All the best.
> 
> 
>

thanks for the reply. Script executed in test and live environment and no 
records returned at present. I will run it again next time we spot a 
problem. Marvolous help, much appricated.

heres an example:

select * from master..sysprocesses where spid in (select blocked from

master..sysprocesses) or blocked != 0

returns:

SPID: 91
KPID: 4520
BLOCKED: 194
WAIT TYPE: 140
LAST WAIT TYPE:    LOCK_M_S  (SHARED LOCK)
KEY: 5:72057594051493888 (14001c8a0200)        (WHAT DOES THIS REFERE TO ?)
physical_io:   13545
MEM: 4
status: SUSPENDED
cmd: CONDITIONAL


Taking this info i guess i need to ID the SPID 91 to find the statement,

So I run SP_WHO


SPID:  91
ECID: 0
STAUTS: sleeping
CMD:  AWAITING COMMAND
REQUEST_ID:  0


Can anyone offer some insight into the above ?

Thank you

think i see it. have profiler running all the time looking for large 
queries. Should be able to see it from teh SPID field.

Erland. Read Committed Snapshot Isolation

http://it.toolbox.com/blogs/coding-dotnet/snapshot-isolation-in-sqlserver-2005-part-i-8266

appears to be the default for SQL 2005,  in which case i will have it 
running already.

Scott (nospam123@yahoo.co.uk) writes:
> Erland. Read Committed Snapshot Isolation
> 
> http://it.toolbox.com/blogs/coding-dotnet/snapshot-isolation-in-sqlserver-
2005-part-i-8266
> 
> appears to be the default for SQL 2005,  in which case i will have it 
> running already. 
 
No, RCSI is not enabled by default. YOu can use this query to see if
you have any RCSI databases:

select * from sys.databases where is_read_committed_snapshot_on = 1


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx