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:
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. 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:
2005-part-i-8266
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

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

Show quoteHide quote

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

Show quoteHide quote

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.

Autostatistic ON/OFF on separate tables?
Install SQL Server 2008 Questions ....
problem with new login
Saving images
Column Limits
Problem with triggers an ntext
SQLhas encountered 2 occurrence(s) of IO requests taking longer th
sql replication
SQL Server Programming Books
database log file keeps growing