SQL Server 2012 slowness

Question

Hi ,

We have an issue at our SQL Server 2012 version server , Microsoft Dynamics AX - application team raising slowness concerns while validating from database perspective. we couldn't find any table locks in activity monitor or blocking sessions and no other jobs are running to produce conflicts with other queries . Even we have configured daily Reorg & Update stats job and weekly Rebuild index job . But still the application team facing slowness issue daily , also checked with other sides network and server end no issues found .

Particularly for there are two tables (Sales order and sales line ) they are trying to use from application end and during that time if execute update stats for those table , the issue resolves temporarily.

Could any one advise what would be the solution to fix this permanently .

Thanks

Answer 1

From the information you have shared, there is nothing we can do to assist, but give some advice for troubleshooting.

You will need to get hold of which queries that are also. You say that updating statistics helps. It would then be a good idea to get the query plan before and after the statistics update. It may not be the case that the statistics update really matters. Next time just do "sp_recompile tbl" and see if this has any effect. That would suggest that it is a parameter-sniffing issue.

What the actual solution would be is to early to say. You need to find information first.

Answer 2

Hi @Seelan, Sathiya

Here is a document you may want to refer to for troubleshooting issues where your entire SQL Server or database application appears to be slow: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-entire-sqlserver-slow?tabs=highcpu.

If the answer is helpful, please click Accept Answer and Up-Vote for the same. If you have any questions, please feel free to let me know.

Best regards,

Aniya