November 30, 2018 at 8:36 am
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 |
November 30, 2018 at 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.
"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
November 30, 2018 at 11:29 am
Grant Fritchey - Friday, November 30, 2018 10:37 AMIf 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
December 5, 2018 at 1:22 pm
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?
December 5, 2018 at 7:42 pm
sqlguy80 - Friday, November 30, 2018 11:29 AMGrant Fritchey - Friday, November 30, 2018 10:37 AMIf 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!
December 6, 2018 at 4:41 am
sqlguy80 - Wednesday, December 5, 2018 7:42 PMHi 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
December 6, 2018 at 9:11 am
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
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply