Hello SQL Experts,
I am wondering if someone could help me with this. I don't know if I can safely deactivate the Trace Flag 4136. The TraceFlag is not officaly documented but i could find the following:
We are using Dynamics AX. Our biggest performance problem are TempDB latencies. Latencies are never under 20ms.
My guesses for these latencies are
I have also attached the Crystal DiskMark stats for you.link text
The offical Microsoft documentation for Dynamics AX recommends the trace flag for overall better performance but also states that this should be tested before enabeling it in production. In our enviorment this Trace Flag is active since the launch of the application so no one ever tested it.
So I did the testing with the RML tools and an hour long trace capture from the production system. The test stated that some of the query saw an improvement but also other queries got a lot worse. I am not really sure how to interpret the results or if this is the right kind of test.
Dynamics AX uses a lot of cursors. If you open a form in AX it issues a SQL statement with an added query hint like
I Imagine the following if I disable the Trace Flag:
Are my assumptions correct? What is another good way to test if the Trace Flag adds value or makes things a lot worse.
Also our SQL Server setup 1. SQL Server 2012 SP2 CU8 (Please don't judge me) 2. Application: Microsoft Dynamcis AX 2012 R3 CU 8
I hope i could provide enough information.
By JonasWanninger 1 asked Dec 14, 2017 at 03:44 PM
Okay, let's go over a few of your points one by one.
By Hugo Kornelis 226 answered Dec 15, 2017 at 12:55 PM
Thanks Hugo for the detailed answer and sorry for not answering so long.
I played a little around and my conception always was that if I use an index the statistics of the index will be used for estimates. But this seems only partially true.
I also generated an index with the following order: - Partition - LastName - FirstName - MiddleName
Although the index I created has a bad histogram SQL Server guess the estimated rows 100% percent right. So am i right, that even if i use an index the estimates can come from the column stats? Do you know when SQL Server will use the index statistics rather than the column statistics?
By JonasWanninger 1 answered Dec 27, 2017 at 12:06 PM