Forum Discussion
theknk1223
Jul 12, 2024Copper Contributor
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.
- Arshad440Brass Contributorhi
please try to add waiting time between these two queries if it's applicable. You can use 'WAITFOR DELAY'.
Regards
Arshad