18
Email alerts or historical information available somewhere which provides details (SPIDs, SQL statements and so on) of blocking chains in the Production environment (and ideally sandboxes too).
With previous versions (e.g. AX 2009/2012), this information could be easily captured using SQL Server profiler / extended events tracing and DynamicsPerf included an analysis feature to go with it (and/or there are various other analysis tools available).
In my opinion this is essential, as blocking analysis is an integral part of mitigating and preventing performance issues in any ERP system. Catching the issue at the time is in theory possible in LCS, however is not practical as it relies heavily on human intervention, within seconds more often than not.
In D365FFO Production/Sandbox (with SQL Azure), 2 possible options are:
1) Use extended events.
I appreciate this would require some Azure storage to be used. e.g. a combination of these 2:
Set up the extended events for blocking-
https://blogs.msdn.microsoft.com/nav/2015/01/16/using-sql-server-extended-events-to-produce-a-blocked-process-report/
Configure Azure storage -
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-xevent-code-event-file
2) Use the Watchdog service to monitor sys.sysprocesses for blocking and if blocking exists, output the blocking chain information into a text or xml file (which gets attached to an email and/or at least an internal ticket), using a relatively straightforward DMV query script like one of the following.
https://blog.sqlauthority.com/2015/07/07/sql-server-identifying-blocking-chain-using-sql-scripts/
https://azure.microsoft.com/en-gb/blog/finding-blocking-queries-in-sql-azure/
https://blogs.msdn.microsoft.com/blogdoezequiel/2012/09/18/sql-swiss-army-knife-11-1-locking-blocking-and-active-transactions/
With previous versions (e.g. AX 2009/2012), this information could be easily captured using SQL Server profiler / extended events tracing and DynamicsPerf included an analysis feature to go with it (and/or there are various other analysis tools available).
In my opinion this is essential, as blocking analysis is an integral part of mitigating and preventing performance issues in any ERP system. Catching the issue at the time is in theory possible in LCS, however is not practical as it relies heavily on human intervention, within seconds more often than not.
In D365FFO Production/Sandbox (with SQL Azure), 2 possible options are:
1) Use extended events.
I appreciate this would require some Azure storage to be used. e.g. a combination of these 2:
Set up the extended events for blocking-
https://blogs.msdn.microsoft.com/nav/2015/01/16/using-sql-server-extended-events-to-produce-a-blocked-process-report/
Configure Azure storage -
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-xevent-code-event-file
2) Use the Watchdog service to monitor sys.sysprocesses for blocking and if blocking exists, output the blocking chain information into a text or xml file (which gets attached to an email and/or at least an internal ticket), using a relatively straightforward DMV query script like one of the following.
https://blog.sqlauthority.com/2015/07/07/sql-server-identifying-blocking-chain-using-sql-scripts/
https://azure.microsoft.com/en-gb/blog/finding-blocking-queries-in-sql-azure/
https://blogs.msdn.microsoft.com/blogdoezequiel/2012/09/18/sql-swiss-army-knife-11-1-locking-blocking-and-active-transactions/
STATUS DETAILS
Needs Votes