Performance degradation - Help troubleshooting

Question

Performance degradation - Help troubleshooting

Viewing 2 posts - 31 through 32 (of 32 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32470 More actions · Answer 1

I guess the table scan is seqid from db2. Strange that is isn't an index scan. Is that the table or is that the one of the top-clusterindex scan

The not-in isn't really sargable which explains the clustered index scan on db1 (as you need the other columns in case it matches)

ramana3327 SSCoach Points: 19722 More actions · Answer 2

Hi All,

Thanks for helping on this issue. I updated the statistics it initially showed cpu usage is down. I created 2 non cluster indexes for query waiting for cpu & showing self blocked. I didn’t see much difference. After that I use dbcc freeproccache. It went down the cpu usage. I didn’t change the maxdop or costthreshold parallelism but currently I am not seeing that query anymore waiting for cpu or blocking itself. Once in a while I am seeing cpu wait but I didn’t see that query anymore.