AX2009 Tag

30 november 2015by StefanAX2012AX2012 R2AX2012 R3Geen categorieSQL1

SQL Trace Flag for Dynamics Ax: Do we need it?

What is a SQL Trace Flag?

A SQL Trace Flag is an indicator that changes the way the SQL engine reacts. Why would we want to that? Well because the DBA always knows best, uh well actually because the standard behavior of the SQL engine will not always give the best possible performance for a Dynamics Ax ERP. Which SQL Trace Flag could be of interest for a Dynamics Ax Database? Well I found 5 that work great for some situations. (-T1117 -T1224 -T2371 -T4136 -T4199). Yes I said “some situations” because which SQL Trace Flag can be enabled depends on lots of things, and should be decided carefully. Let me inform you about these 5 SQL Trace Flags.

SQL Trace Flag T1117: TempDB in multiple files

When you divided the TempDB in multiple data files to match the number of CPU cores as recommended by Microsoft for Dynamics AX, SQL Trace Flag –T1117 comes in handy. It will change the standard behavior of auto growing database files in a file group. The standard behavior is to auto grow one of the files at a time. With this SQL Trace Flag enabled all files in a file group will grow simultaneously.

For example:

1 db divided in 4 data files with an initial size of 100MB and an auto grow of 20MB each. When your reaches the 400MB and all 4 files are full, one of them will auto grow to 120MB. But the next data will be written in the file with the freest space. Guess which one that will be…. The one that grew off course.   Now the same example with SQL Trace Flag –T1117 globally enabled: When your reaches the 400MB and all 4 files are full, all of them will auto grow to 120MB. The next data will be written in the file with the freest space. Guess which one that will be…. All of them.

Should we enable this SQL Trace Flag this minute?

No, there is a drawback. This SQL Trace Flag is great for the TempDB but it affects all data files of all databases in the instance. They will simultaneously grow if they’re in the same file group. So think before you act, if you don’t want the files to grow simultaneously put them in different file groups, or don’t enable this SQL Trace Flag.

More information on this SQL Trace Flag: Microsoft Dynamics Ax Performance team

SQL Trace Flag T1224: Running big operation during the day.

When running big operations (5000+ rows) during the day, like master planning, they could be blocking your users from doing their daily business. This is due to lock escalation. SQL locks a page to make sure only one process effects that page at a time. When a certain number of pages of a table are locked by the same process SQL can decide to lock the table if this is less costly on memory. This will make all requests for pages in that table wait till the first process is done. SQL Trace Flag -T1224 Disables lock escalation based on the number of locks. However, memory pressure can still activate lock escalation.

Should we enable this SQL Trace Flag this minute?

No, if you don’t need to run big operations simultaneously or with users in the system, you’re probably free of blocking by lock escalation. And lock escalation will have a positive effect on the performance of your SQL. So please plan large operations carefully outside office hours where possible.

SQL Trace Flag T2371: Keeping statistics up-to-date on large tables

The SQL query optimizer uses statistics to create query plans that improve query performance. Performance of Dynamics AX depends largely on up-to-date statistics. That’s why it is recommended to set “Auto update statistics” on. But when will the statistics be updated? Well when 20% of the rows are updated/inserted. This SQL Trace Flag is only available on SQL Server 2008 R2 SP1 and all later versions.  There was a small update to this SQL Trace Flag in SQL Server 2008 R2 SP2 and SQL Server 2012 SP1 that makes it more precise.

For example:

On a table containing 1,000 rows the statistics are updated when 200 rows are updated. And the same table after a while containing 1,000,000 rows the statistics are updated when 200,000 rows are updated. Did you read that right? Yes you need to update 200,000 rows till the statistics are updated. Imagine even larger tables. We all know that in an ERP like Dynamics AX these large tables are pretty common. Microsoft also recognized that and released SQL Trace Flag –T2371. This SQL Trace Flag will make the 20% rule a dynamic percentage rule that kicks in at 25,000 rows.

For example with SQL Trace Flag –T2317 globally enabled:

SQL Server will update statistics on a table containing 1,000 rows when 200 rows are updated. And on a table containing 1,000,000 rows the statistics are updated when 32,000 rows are updated. In a chart the dynamic percentage rule looks like this:

T2371

Should we enable this SQL Trace Flag this minute?

No, first examine if your database has a lot of large tables (above 100.000 rows is a good rule of thumb). Without large tables this SQL Trace Flag is useless.

SQL Trace Flag T4136: Parameter sniffing optimisation

Dynamics AX uses parameterized SQL statements if we don’t use the ‘Forceliterals’ parameter in AX. I personally have never seen a developer who fully understood the working of ‘Forceliterals’ let alone use it in code. It even is best practice not to use it.

Why? Well when using parameterized SQL statements the query plans are cached and reused. SQL Server will use one plan for all similar queries instead of compiling each query.  This can significantly reduce CPU overhead and improve throughput. That sounds like a good thing and it is.

But there is a drawback, if the queries are complex and/or the data distribution on the columns against which the parameter is compared vary, the cost of different plan choices can change.  A plan that is optimal for one parameter value may perform poorly for another value.  The query optimizer still needs to estimate the selectivity and cardinality of predicates using a parameter value. In Dynamics AX especially from version 2012 on the queries are almost always complex.

One way to avoid parameter sniffing is to put the OPTIMIZE FOR UNKNOWN option to all SQL statements leaving AX. Another way is to enable SQL Trace Flag -T4136. This SQL Trace Flag is only available on SQL Server 2008 R2 Cumulative Update 2, SQL Server 2008 SP1 Cumulative Update 7 and SQL Server 2005 SP3 Cumulative Update 9 and later.

For example:

If we have 2 companies in our Dynamics Ax Application and one only 2 customers and the other a couple thousand. The following could happen: if the first query we send to loop through customer is done in the company with a couple thousand customers , it could look like:

SELECT * FROM CUSTTABLE WHERE  DATAAREAID = ‘@P2’

And SQL Server Engine will solve it by doing a full table scan because almost all records are needed.

Next time we do the same loop in the other company due to the parameter on dataareaid (and partition in AX2012) the query is identical. That is why SQL Server will solve it in the same manner, with a table scan, where a page scan could be more appropriate. If only SQL could store a queryplan per dataareaid….

Should we enable this SQL Trace Flag this minute?

No, because Microsoft provided a way to use parameters but not for dataareaid (and partition in AX2012). This makes SQL server store a queryplan per dataareaid, making SQL Trace Flag T4136 completely unnecessary.

AX2009 SP1: KB 3000700

AX2012 RTM: KB 2920058

AX2012 R2: KB 2969229

You can find more information on the blog of Dynamics in the field.

More information on this SQL Trace Flag: Microsoft Support on SQL Trace Flag T4136

SQL Trace Flag T4199: Query execution plan updates

Microsoft turned off All query execution plan updates/hotfixes by default to make sure that an update will not affect the way your query executes. Several of these hotfixes will positively boost performance for Dynamics AX. Microsoft controls all future query processor fixes that are scheduled to be ‘On-By-Default’ in a later release with trace flag t4199. This SQL Trace Flag is only available on Cumulative update package 6 for SQL Server 2005 Service Pack 3, Cumulative update package 7 for SQL Server 2008, and Cumulative update package 7 for SQL Server 2008 Service Pack 1 and all later versions.

Should we enable this SQL Trace Flag this minute?

No, there is are 2 drawbacks. 1: Your DBA will send you off to an insane asylum because of the next drawback. 2: If you enable this SQL Trace Flag you enable all future hotfixes/updates to the query execution plans, because we all know you’re going to forget about enabling this SQL Trace Flag.

More information on this SQL Trace Flag: Microsoft Support on SQL Trace Flag T4199

Lees Verder
6 november 2015by StefanAX2012AX2012 R2AX2012 R3Geen categorieSQL1

Index fragmentation effects on performance

Before jumping into index fragmentation I’ll first explain what indexes are and how they are used by the SQL Server Engine. I tried to compare it to a real world way of searching data, to paint a clear picture in your head. I’m not going into the differences between clustered and non-clustered indexes as that would be a nice new blog.

How do indexes work?

Indexes are used by SQL Server to search for records in tables as we use indexes in books to get to certain chapters/words/authors etc. The way an index in SQL Server works is similar to the way we lookup a phone number in a (old fashioned and hard copy) phonebook.

  1. We decide we need a phonebook for this search not a cookbook.
  2. We pick up the phonebook of the state we need.
  3. We look at the index to find the pages that have the correct city.
  4. We go to the page that contains the first letter of the last name of whose phone number we need.
  5. We find the last name and see that there are in fact several.
  6. We look at the address and find the right number.
SQL Server will do the same thing, with the right index off course:
  1. It decides it needs a certain index.
  2. It will look at the 1st level of the index to find the right part (phonebook of state).
  3. It will look at the 2nd level of the index to find the right part again (correct city).
  4. It will look at the 3th level of the index to find the right part again (last name).
  5. It finds the page containing the correct last name.
  6. It traverses through the records for the one that contains the needed address.

What is index fragmentation?

Index fragmentation is what it says the fragmentation of an index.
So a page with empty spaces in between. Or pages that are out of order. Or even split up pages.

How does index fragmentation start?

Back to the phonebook:
I’m responsible for updating the phonebook manually. If someone would move to another city I would take my eraser and erase them from their old city. Leaving a blank record.
Now go to the new city and insert their phone number in an already full page. I don’t like to scribble another ‘Mr Smith’ in between the others.
So I erase all ‘Smith’ entries that go after my new entry and put those on a new page, leaving a partly filled page. The new page won’t fit in between so I stick it at the end of the phone book.
Finding ‘Mr Smith’ got a lot more difficult now.
  1. We decide we need a phonebook for this search not a cookbook.
  2. We pick up the phonebook of the state we need.
  3. We look at the index to find the pages that have the correct city.
  4. We go to the page that contains the first letter of the last name of whose phone number we need.
  5. We find the last name and see that there are in fact several.
  6. We get to the page our ‘Mr Smith’ should be on, only to find a reference half way the page telling us to look at a page in the end of the book.
  7. We go to the page at the end of the book
  8. We look at the address and find the right number.
And yes again SQL Server will do the same for us, and it’s called: page split. More detailed information on page splits can be found at Tony Rodgerson’s blog.
By doing so I and SQL Server added an entire page just for one entry and leaving a blank record on the old page. And there you have it: index fragmentation. Putting the new page in the back of the phone book also applies here, on the hard drive there is no room to put the page in between so it is stored where there is room. Making the search for the data even more difficult.
As you probably know a hard drive (HDD) has to rotate to get to the right spot, so data neatly ordered will be found much faster than randomly stored data. The rotation in HDD’s is what makes it a lot slower than SDD’s that don’t use moving parts.
This is only one person moving to another city. But think of the number of changes that have to be made to the phonebook of New York annually. People move, they change their name, they get married, they go live together, get their own place and phone number etc.
This index gets fragmented constantly, so it will always be slow… Not necessarily.

What happens with index fragmentation?

Well consider this:
If all the pages of your phonebook would only contain 1 phone number. How many pages should you read to get to the right ‘Mr. Smith in New York’? Exactly too much.
Again for SQL Server this would work in the same way. SQL Server indexes are divided in pages of 8Kbytes. If these pages only hold one phone number it needs to check a lot of pages in the end.
SQL Server also has another great feature called lock escalation; which makes it lock the pages that hold the records so other users cannot edit the records you’re currently working on. If the percentage to be locked pages get’s high enough SQL Server escalates to locking the entire index (table). This locking mechanism is your friend in most cases, it prevents users from updating the same record at the same time with all consequences of that. But with index fragmentation the lock escalation can kick in too fast making SQL Server lock a lot more then you want, and by doing so making other users wait for the records till they are no longer locked.

How to temporarily or partly prevent index fragmentation?

If we could leave some space open on the pages of a phonebook we could enter new entries there and only shuffle the rest. This would increase the number pages to begin with but prevents the introducing of a new page part.
In SQL Server you can set a property on an index called fill factor which indicates how full a page should be when the index is created.
That means that if you set this to 70 the pages will be filled for 70% leaving 30% for new or updated records. which leads to less index fragmentation, well till the 30% is also filled. (if the Pad Index setting wasn’t set to true SQL will only use fill factor for the leaf pages: the pages containing the actual data. If it was set the pages that are used by SQL to get to the leaf pages called intermediate pages are also filled according to fill factor).
I hear you think: oh then I’ll use fill factor and set it at 50% for all indexes in my Dynamics Ax environment. You could do that, but then you’ll make everything twice as slow as it is with a 100% fill factor. And page splits only occur to indexes that change a lot.
Example 1:
The SalesTable has an index on RecId. RecId’s are dispensed in a neatly order, and they cannot be updated. This index will only grow in the last page, creating new ones right at the end. As long as you prohibit deletion of SalesTable records, no gaps will be created in between. Why would you ever adjust the fill factor on this index? The answer is: You shouldn’t !
Example 2:
The AIFMessageLog table has an index on a GUID created in Ax and send to SQL. GUID’s created in Ax can be considered as random strings. They will trigger index fragmentation at every insert and/or update operation. So this index will get fragmented by every change you make. And the workflow items get created a lot, making this index fragmentation’s level up to 98% in no time at all. Setting this index’s fill factor to 70 and maintain it a lot will certainly help a lot. More information on GUID’s and index fragmentation can be found  as Kimberly L. Tripp’s blog.

TIP: Workflow and AIF are using GUID’s in indexes so set fill factor accordingly and maintain them.

Example 3:
The Parameter tables in Dynamics Ax mostly have one or a few records in them, with an index on RecId. They get no new records and are updated almost never. Why mess with the fill factor? Index fragmentation is no issue here.
What you also could do is once in a while rewrite the entire phonebook, making all pages full again and you end up with less pages.
In SQL Server we call this an index rebuild. When an index rebuild is done the old one is replaced by a new one that will use the fill factor setting discussed earlier. Index fragmentation will  be 0  after a rebuild.
Using standard maintenance jobs in SQL will rebuild all indexes on all tables if they are fragmented or not. Rebuilding an index can be done offline which means the index is removed and then rebuild, while rebuilding SQL Server cannot use the index and will use another one or a table lock. If you use Enterprise Edition of SQL you can rebuild indexes online, then SQL build a new index while the old one is still in play replacing it when done. I recommend to rebuild indexes in a maintenance window when there is minimum activity on your database.
There is a great alternative to using the maintenance jobs of SQL: scripts. No, I don’t want you to script this for yourself. Ola Hallengren provides scripts that calculate if rebuild is necessary for an index and it takes your version and edition of SQL Server in consideration. In my experience these scripts work very for Dynamics Ax databases they only need one adjustment. There is a parameter in them that only take indexes from a 1000 pages and up in consideration, set this to 100 for Dynamics Ax.

How to permanently prevent index fragmentation to be an issue?

There are several things you could do, and some are free of charge others are considered an investment. Do what you can and you’re performance will improve as index fragmentation goes down.
  1. Make sure the physical drives your data is on are not shared with other applications. Mainly for HDD storage as the other applications will use the HDD rotation as well.
  2. Use the scripts of Ola Hallengren to reduce index fragmentation without rebuilding all indexes. Set the parameter of minimum pages to 100 instead of 1000 for Dynamics Ax databases.
  3. Use SDD’s for storage of data instead of HDD’s. Because SDD’s don’t use moving parts.
  4. Cache your database by assigning enough memory to SQL Server to cache the entire database, or at least the records that are frequently used. Because cached data is way faster then any stored data on hard discs.

Is index fragmentation the root cause of performance issues?

 Simple answer: No, it isn’t but it is an easily treatable result of more complex underlying problems. Index fragmentation is caused by several things as:
  1. Poorly chosen indexes. (indexes on fields that are updated all the time)
  2. Changing of usage of the system. (Users are allowed to change id fields, which wasn’t in the original design)
  3. Poorly developed queries. (queries that cannot make full use of the indexes that are already there)
  4. and a thousand other reasons.
All code samples, walkthroughs and other instructions are provided as-is. Use any and all information provided in this blog at your own risk. It is never advised to import or write code in a production environment without rigorous testing in a test or development environment.
All opinions expressed in this blog are solely my own and do not necessarily reflect the opinions of my employer.
The names of actual companies and products mentioned in this blog may be the trademarks of their respective owners.
Microsoft and Dynamics AX are registered trademarks of Microsoft Corporation in the United States and other countries.
Lees Verder
25 oktober 2015by StefanAX2012AX2012 R2AX2012 R3Geen categorieSQL5

Missing index will cause slow performance: SQL can help you

Introduction

As a new member of the Kaya family I feel the urge to introduce myself. Hi I’m Boye Walters, a 34 year old developer with over 12 year experience in Microsoft Dynamics Ax and Microsoft SQL Server. Having worked with all versions of Ax ever released in the Netherlands I can truthfully say that I’ve seen a lot already in Ax. As for SQL Server I’ve actively worked with SQL2000 and above, using all features available including integration, analysis and reporting.
Now the introduction is done let’s start off with something I experienced last week at a customer.

A badly performing View

I was asked to build a view with a dozen or so datasources having inner, outer  and exist joins. To maximize performance I minimized the number of fields and put some relevant ranges in place minimizing the number of records. I also used the relations already on the tables. It should produce an ultrafast view you would think. Too bad, it took me 40 minutes to open the view in the Ax client. But after 40 minutes it showed a lot of records: 2. Because I didn’t use any business logic, user interface or what so ever, I figured it should be SQL causing the delay. As a ran the query in SQL I believed to be proven wrong again, because it ran in 4 seconds. But SQL did suggest an index that could improve performance 85%. After applying it running the query in SQL took under a second. And in Ax it also took under a second. Sounds strange? Yes. Explainable? Absolutely , because of the missing index the query requested a table lock on dbo.salesline which was heavily queried so it took a while to get that.

How did I identify the missing index?

Well after synchronizing the view in Ax I started SQL Server Management Studio (SSMS) and went to the right database and View. There I rightclicked on it and selected ‘Script view asSELECT TONew Query editor window’.

Script view asSELECT TONew Query editor window

‘Script view as – SELECT TO – New Query editor window’

This will generate a select script for the view.
Now instead of running it went for the execution plan. Using ‘Display Estimated Executing Plan (Ctrl+L)’ button on the top of my Management Studio. Look at the second query (first is just to select the right database), if SQL determines it is needs an index to give you the answer to your query it will say so. The way it does is by showing a create statement for the missing index in green. Like it did with me:

view02

Now I’m going to test it before I build it in Ax. First I rightclicked on the message and choose ‘Missing Index Details’. This opened up a new Query window with a create statement that is commented like this:

view02

I uncommented the query and gave the index a name as requested.
Now I ran this script, and the index was created in SQL. I went back to the previous script and used the ‘Display Estimated Executing Plan (Ctrl+L)’ button again, to make sure SQL didn’t suggest another missing index.
No more missing index, so I went to Ax and ran the view again. After recovering from the Wow factor (and going through several emotions: impressed, not believing, not understanding, believing, grasping the understanding and still wowed ) I created the missing index in Ax after removing it in SQL.

Why ask a program not a developer?

As many of you know, SQL Server works in a (for non SQL developers) odd way. You tell SQL Server what you want and how it should be formatted but SQL Server determines what would be the best way to deliver you what you want. That makes SQL Server the one that can tell you what it is missing to do it’s job properly. This also means you could use this method to determine what missing index would speed up a SQL query that you caught with the trace parcer. There are even queries possible in SQL Server that show you all missing indexes at ones. And that is exactly what we use in our Performance Dashboard.

Interesting links:

Technet on finding missing indexes

Lees Verder