Post reply

Performance degradation - Help troubleshooting

  • September 7, 2022 at 9:15 pm

    #4085125

    Thank you.

    The query that is executing has several small queries. One part of the query is selecting data from one table1 in database1 where Id not in(select id from table1 in database 2) and inserting into temp table

    select id,col2, col3, case when etc, col4 into #temp1 from db1..tbl1 where id not in (select seqid from db2..tbl1).

    I created index on id on db1.tb1 & on seqid on db2.tbl1.

    Query plan is below

    image

    • This reply was modified 2 years, 3 months ago by  ramana3327.

  • September 12, 2022 at 1:52 pm

    #4086630

    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)

  • September 14, 2022 at 11:57 am

    #4087537

    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.

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

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