Locking in Database

[sendba]

Hai all

If i give Sp_who this is the result i am getting

SPID Status Login HostName BlkBy DBName Command CPUTime

21 BACKGROUND sa . . master TASK MANAGER 0
166 sleeping web2 as02.us-south.net . MerchantManager SELECT 12744009
166 sleeping web2 as02.us-south.net 166 MerchantManager SELECT 15
166 sleeping web2 as02.us-south.net 166 MerchantManager SELECT 31
166 sleeping web2 as02.us-south.net 166 MerchantManager SELECT 31
166 sleeping web2 as02.us-south.net 166 MerchantManager SELECT 47
166 RUNNABLE web2 as02.us-south.net . MerchantManager SELECT 16
166 RUNNABLE web2 as02.us-south.net . MerchantManager SELECT 15
166 sleeping web2 as02.us-south.net 166 MerchantManager SELECT 62
166 sleeping web2 as02.us-south.net 166 MerchantManager SELECT 16

I am seeing the blkby as the same process id .
Is there any issues and how to resolve this

Please help

Regards
senthil

 

[DrSql]

You able to Kill 166?

Dr.Sql
Good Luck.

 

[hmckillop]

Is parallel processing on?
If so turn it off via the sp_reconfigure advanced option

use master
go
sp_configure 'max degree of parallelism', '1'
go

and also right clicking in enterprise manager and ensure only one processor per query

"I'm living so far beyond my income that we may almost be said to be living apart

 

[sendba]

Its a Production Box I cant kill the process .
My doubt is whether there would be any issues when the process is blocked by the same process id .
But when i run sp_who again i dont see any locks

 

[hmckillop]

if you run it a few seconds later, there isnt an issue, its just sql managing blocking, but as i said MSSQL doesnt handle parallelism, and I have had 1st hand experience of this.
In your case, if your sql box is under load, dont worry about seeing blocking, that part is ok, if you see deadlocks thens the time to worry about it.
What you see in the above results is not out of the norm and shouldnt concern you unless performance is effected.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[DrSql]

If you kill a SPID it will kill only that process, all other SPID's stay alive. See what Activity is this spsids are doing, also look in to Loked objects.

Dr.Sql
Good Luck.

 

[mrdenny]

This is the basics of what is happening.

When SQL decides to use parallism it breaks up the query into more than one part and each CPU processes a part then it's all put together at the end.

The next time this happens pull the records from the sysprocesses database for the spid (in this case it was 166). You'll see the kpid column. Each record that was returned from sp_who will have a different kpid. Assuming that they are in order from 0-8 (0 is the master kpid, 1-8 are the child kpids that are actually doing the work one per CPU).

According to this kpids 1,2,3,4,7 and 8 were being blocked by either kpid 5 or 6. Depending on your isolation level and what else is going on this may be normal.

If you see this as a problem you can turn off the parallelism for this query only by putting "OPTION (MAXDOP 1)" at the end of the select statement like this.

select *
from table1
join table2 on table1.col2 = table2.col4
where table1.col1 = 'Soemthing'
option (maxdop 1)

This will force the query to use only a single CPU for the process and stop it from blocking it self.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief