SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


blocking for same query on user DB


blocking for same query on user DB

Author
Message
sqlguy80
sqlguy80
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2117 Visits: 437
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

Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (362K reputation)SSC Guru (362K reputation)SSC Guru (362K reputation)SSC Guru (362K reputation)SSC Guru (362K reputation)SSC Guru (362K reputation)SSC Guru (362K reputation)SSC Guru (362K reputation)

Group: General Forum Members
Points: 362123 Visits: 34462
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

The Scary DBA
Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
sqlguy80
sqlguy80
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2117 Visits: 437
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
sqlguy80
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2117 Visits: 437
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
sqlguy80
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2117 Visits: 437
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
Grant Fritchey
SSC Guru
SSC Guru (362K reputation)SSC Guru (362K reputation)SSC Guru (362K reputation)SSC Guru (362K reputation)SSC Guru (362K reputation)SSC Guru (362K reputation)SSC Guru (362K reputation)SSC Guru (362K reputation)

Group: General Forum Members
Points: 362123 Visits: 34462
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

The Scary DBA
Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)

Group: General Forum Members
Points: 896588 Visits: 48274
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search