blocking for same query on user DB

Question

blocking for same query on user DB

sqlguy80

SSCrazy

Points: 2762
More actions
November 30, 2018 at 8:36 am

#380954

Hello Gurus,
In Production I noticed one issue and need your help. On the web page when we click on a drop down button it takes about 1 min to load, should take only 3 seconds given the record count. when I ran sp_Who2 I noticed this, same query blocking itself. Any idea how to fix. Ixs all defragmented and stats up to date.

SPID Status Login HostName BlkBy DBName Command CPUTime
71 SUSPENDED servername 71 user_db SELECT 2147483647

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 1

If it's blocking itself is the query going to parallel execution? Check the execution plan to see what's going on with the query.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

sqlguy80 SSCrazy Points: 2762 More actions · Answer 2

Grant Fritchey - Friday, November 30, 2018 10:37 AM
If it's blocking itself is the query going to parallel execution? Check the execution plan to see what's going on with the query.

Hi Grant,
Strangely nothing out of ordinary in the exec plan , index seek , clustered index seek keylookup, small db, small row count on the table!
Thanks

sqlguy80 SSCrazy Points: 2762 More actions · Answer 3

I noticed the default settings maxdop=0 and cost threshold of parallelism=5 and the production instance is a VMWare virtual machine.

I'm thinking changing cost threshold of parallelism to 50 will fix this. Any inputs?

sqlguy80 SSCrazy Points: 2762 More actions · Answer 4

sqlguy80 - Friday, November 30, 2018 11:29 AM
Grant Fritchey - Friday, November 30, 2018 10:37 AM
If it's blocking itself is the query going to parallel execution? Check the execution plan to see what's going on with the query.
Hi Grant,
Strangely nothing out of ordinary in the exec plan , index seek , clustered index seek keylookup, small db, small row count on the table!
Thanks

Hi Grant ,
Changing Cost threshold of parallelism from default 5 to 50, fixed this blocking and slowness. I ran the clear cache and drop clean buffers , not sure if it was needed but did it and its def faster now and I dont see any blocking of the SPID blocking itself which is insane!

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 5

sqlguy80 - Wednesday, December 5, 2018 7:42 PM
Hi Grant ,
Changing Cost threshold of parallelism from default 5 to 50, fixed this blocking and slowness. I ran the clear cache and drop clean buffers , not sure if it was needed but did it and its def faster now and I dont see any blocking of the SPID blocking itself which is insane!

Clearing the cache & the buffers just lead to recompiles and rereading the data from disk into cache. It's unlikely that alone fixed any performance issues unless the recompile addressed an issue with bad parameter sniffing. If changing the cost threshold worked, then it was likely a parallelism issue.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

Let's stop guessing... what is the query? What is the actual execution plan. Please see the second link in my signature line below for what we need to be able to help on this type of performance issue.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SPID	Status	Login	HostName	BlkBy	DBName	Command	CPUTime
71	SUSPENDED		servername	71	user_db	SELECT	2147483647