HI Experts- 

Need help with heavy blocking on SQL Server Database.

Any Select or update on table A is causing blocking ..then users get timeout in application

UPDATE on Table A ran from spid 51 run for 30seconds-1 min and thenSELECT on Table B ran by spid 52this causes Blocking and run for 1-2 minutes then users get timeout in applicationSELECT is coming from a View—its not a fast view but getting 3 million rows in <90secondsand then whoever else uses the table gets blocked and soon ….sometimes no blocking and then all of a sudden timeout due to blocking

FYI:

Db size 43GBTable A is heavily used …1Million rowsno missing indexes, no un used indexes, no duplicate indexesIndex and update stats jobs run every night 2amMax dop, Cost of threshold parallelism  are set 8 and 50database update stats =on and asyn trueREAD COMMITTED is the default isolation levelTempdb files are configured according to number of cpuother non-heavily used tables run finefull and t logs are running fineCPU is 16 core which is huge for this sql servermemory is 64GBSSRS is running in same machine (assigned sql server 80% of memory)

Anyone had similar issue? please advise

Things I tried is to make 1st select query faster but that is the max It could run and I ran out of options.

​ HI Experts- Need help with heavy blocking on SQL Server Database.Any Select or update on table A is causing blocking ..then users get timeout in applicationUPDATE on Table A ran from spid 51 run for 30seconds-1 min and thenSELECT on Table B ran by spid 52this causes Blocking and run for 1-2 minutes then users get timeout in applicationSELECT is coming from a View—its not a fast view but getting 3 million rows in <90secondsand then whoever else uses the table gets blocked and soon ….sometimes no blocking and then all of a sudden timeout due to blocking FYI:Db size 43GBTable A is heavily used …1Million rowsno missing indexes, no un used indexes, no duplicate indexesIndex and update stats jobs run every night 2amMax dop, Cost of threshold parallelism  are set 8 and 50database update stats =on and asyn trueREAD COMMITTED is the default isolation levelTempdb files are configured according to number of cpuother non-heavily used tables run finefull and t logs are running fineCPU is 16 core which is huge for this sql servermemory is 64GBSSRS is running in same machine (assigned sql server 80% of memory)Anyone had similar issue? please advise Things I tried is to make 1st select query faster but that is the max It could run and I ran out of options.   Read More

​