Slow query

Transact-SQL
1

I have a scheduled job that runs every 3 weeks that calls a complex query and it has failed today, first time ever.

I ran the query in the TEST environment and it's fast and succeeds; the number of rows in the tables are very similar to PROD.

However I moved the PROD database to TEST and the query is really slow to the point that I have to stop it. Would be great if anyone can guide me what else to look at.

Things I tried on this recent copy of PROD database, but did not work:

  • Create indexes but it's not doing anything
  • Create temp tables instead of using CTE
  • It's not parameter sniffing as my query has not parameters
  • No fragmentation, stats are updated.
2

I found the issue, I would need to fix it now. The minimal difference is that one table has different number of rows but the numbers are very minimal. How is it possible to cause so much slowness? I need some ideas about how to fix it, Thanks.

PROD
||53620|

TEST
||54193|

3

I think you should take a look at the execution plan of the query. I think you will find the answer there.

4

Agreed. Look at the Estimated Execution plan. That should give you a good idea of why it's performing slowly.

1 Like
5

hi

hope this helps

i faced the same issue in one of the companies i worked for

my issue was = statistics were not being updated/created
your issue could be the same or something different

-- Update all statistics with sp_updatestats
EXEC sp_updatestats;

-- Update all statistics on a table
USE AdventureWorks2022;  
GO  
UPDATE STATISTICS Sales.SalesOrderDetail;  
GO
1 Like