Forum Discussion

theknk1223's avatar
theknk1223
Copper Contributor
Jul 12, 2024

HELP with BLOCKING in sql server

 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 then
  • SELECT on Table B ran by spid 52
  • this causes Blocking and run for 1-2 minutes then users get timeout in application
  • SELECT is coming from a View—its not a fast view but getting 3 million rows in <90seconds
  • and 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 43GB
  • Table A is heavily used ...1Million rows
  • no missing indexes, no un used indexes, no duplicate indexes
  • Index and update stats jobs run every night 2am
  • Max dop, Cost of threshold parallelism  are set 8 and 50
  • database update stats =on and asyn true
  • READ COMMITTED is the default isolation level
  • Tempdb files are configured according to number of cpu
  • other non-heavily used tables run fine
  • full and t logs are running fine
  • CPU is 16 core which is huge for this sql server
  • memory is 64GB
  • SSRS 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.

 

  • Arshad440's avatar
    Arshad440
    Brass Contributor
    hi
    please try to add waiting time between these two queries if it's applicable. You can use 'WAITFOR DELAY'.

    Regards
    Arshad

Resources