Wow, it was a crazy week at Sage Summit. One week ago today I was presenting at the session “Prepare your Sage ERP X3 Solution for Infinity and Beyond – System Performance Best Practices”. As it turned out, I caught the flu the night before so I was completely out of it for the presentation. I’ve heard the presentation still went over pretty good, so I’m glad for that. But, I’m pretty sure I didn’t talk about or demo something I had planned which was the concept of missing indexes inside SQL Server. So, to thank all of the people who attended my session, and make up for the missing topic, today’s blog is all about missing indexes and what to do about them inside Sage ERP X3. More importantly, what is a missing index and why should you care? Hey by the way, if you went to Sage Summit, did you check out SPROCKIT at the Smithsonian? That was one cool cat!

What is an index?

Brent Ozar does a great job on his blog titled SQL Server Index Terms showing the concept of an index and how it’s built. Microsoft also explains what Clustered Index Structures are, along with heap structures. Basically, indexes are a necessary component implemented by most all database engines today like Oracle, MongoDB and SQL Server and are designed to improve the speed of read operations for any given query. In SQL Server specifically, heap tables are tables without a clustered index. And a clustered index is a special type of index that stores the data in order at the time the data is written to the index.

You can figure out just how many indexes you have inside each folder in X3 by running the following query:

SELECT s.name [SchemaName], i.type_desc, i.is_unique, COUNT(*) CountOfIndexType
FROM sys.indexes i
    INNER JOIN sys.tables t
        on t.object_id = i.object_id
    INNER JOIN sys.schemas s
        ON s.schema_id = t.schema_id
GROUP BY s.name, i.type_desc, i.is_unique

Your results will vary from mine depending on a number of factors, such as which add-ons you have installed for Sage ERP X3, along with any custom tables you may have developed for your solution. But, in a fairly vanilla install you should have something like the following results:

The X3 folder will have somewhere around 2,445 nonclustered indexes designed with the unique attribute set to yes. And as you can see, the folder titled DEMO (folder is analogous to the term schema inside SQL Server, a topic for another time) has around 2203 unique and about 525 non-unique indexes. So, for any given folder, there are roughly 3,000 indexes.

Are these indexes all that I’ll ever need?

For goodness sake, NO. If Sage controlled all the code that was ever run on your system, and controlled all the data that was ever going to exist on your system as well, then Sage could design the perfect index strategy, given enough time. But, Sage doesn’t control all the code that gets run against your X3 database. And you are the owner of what data goes into your ERP system through the specific business relationships you have with your vendors and customers. Just off the top of my head, here are some simple examples of code run against the Sage ERP X3 database inside SQL Server that Sage doesn’t control:

• Stored procedures you write. Stored procedures are code that can be written directly inside SQL Server, contained within specific databases
• SQL Server Views you write. You can write these inside Sage ERP X3 or inside SQL Server
• Crystal Reports, or any Business Intelligence solution that you write that reports directly off the Sage ERP X3 database
• Any other application that you allow to connect to the Sage ERP X3 database. That would include providing Excel spreadsheets that house data connections to your SQL Server, along with Microsoft Access and any other type of home grown application you write in .NET or Java, etc…
• SQL Server Reporting Services
• SQL Server Analysis Services

As you can see, a LOT of code can be sent to the X3 database that Sage cannot account for in our design phase. So, if all of these different applications have the potential to send completely unique queries to the database, does that mean that everyone who writes those queries needs to be a query expert? To some degree, people who write queries against a common data store should have enough wisdom about that data store to retrieve data in a manner that doesn’t hog the available resources away from other users (data entry personnel or business intelligence gurus alike). Your server has a finite performance capacity. Meaning, only so much data can be retrieved within a specific time period based on how much RAM, CPU, network and disk resources you implemented on the server. That mans that when you run a query against the X3 database you should look for good indexing opportunities to help minimize the cost of searching data, and often, the indexes specific to your custom query may not already exist.

Hey Bob, how do I know if I'm missing indexes on my queries?

There are soooooooo many ways. I’ll write another blog some time later that shows you how to look for a missing index on a specific query. But, to keep this short, let’s list just one technique that looks at the server as a whole.

As it turns out, SQL Server, starting with SQL 2005, began cataloging interesting facts in diagnostic views. One interesting fact SQL implemented was the ability to learn what indexes could be of benefit on your specific SQL Server given the queries you’ve run against. So this query will provide you the a list of those missing index opportunities along with their estimated impact.

SELECT 
    dm_mid.database_id AS [Database ID],
    sch.name,
    dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) [Avg Estimated Impact],
    dm_migs.last_user_seek AS [Last User Seek],
    object_name(dm_mid.object_id,dm_mid.database_id) AS [Table Name],
    'CREATE INDEX [IX_' + object_name(dm_mid.object_id,dm_mid.database_id) + '_'
    + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
    CASE
    WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
    ELSE ''
    END
    + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
    + ']'
    + ' ON ' + dm_mid.statement
    + ' (' + ISNULL (dm_mid.equality_columns,'')
    + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
    '' END
    + ISNULL (dm_mid.inequality_columns, '')
    + ')'
    + ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
    INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
        ON dm_migs.group_handle = dm_mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details dm_mid
        ON dm_mig.index_handle = dm_mid.index_handle
    INNER JOIN sys.all_objects o
        ON o.object_id = dm_mid.object_id
    INNER JOIN sys.schemas sch
        ON o.schema_id = sch.schema_id    
WHERE dm_mid.database_ID = DB_ID()
ORDER BY [Avg Estimated Impact] DESC
        

Sample Missing Index results on my system

So why do I care about these missing indexes?

A simple look at the Average Estimated Impact column in terms of relative importance to one another should provide some insight as to indexes that could have benefited queries against those specific tables. These index suggestions come directly from SQL Server and I’ve ordered the result set by the average estimated impact column, so anything that is higher on the list is more important to you, and what is lower on the list is less important.

Word to the wise

Do NOT take all the indexes suggested here and implement them. There is a cost SQL Server has to pay for adding indexes to a system. Insert, update, and delete statements all cost slightly more when more indexes are added. Usually, it’s negligible, unless you are creating an enormous amount of indexes that aren’t otherwise benefiting you. The point is, you should reason out which indexes are beneficial to you and implement what makes sense. This is where a background in database administration can be of help.

How do I implement these inside the X3 dictionary so they persist?

If you know anything about Sage ERP X3, then you’ll know that creating these indexes inside SQL Server directly using the Create Statement above is the absolute wrong thing to do. You should be creating these indexes inside the table dictionary. But, if you’ve done that, you’ll quickly realize that in version 5 and 6 of Sage ERP X3 we don’t have a way to describe a clustered index. I’ve seen some early builds of version 7 that are including the ability to more easily create clustered indexes within the table dictionary. However, if you look closely at the results above you’ll notice that some of the create index statements leverage a hint called INCLUDE. This is a REALLY nice feature inside SQL that allows you to cover columns in a query with your index. You can learn more about it here.

Include Column Definition

Microsoft defines include columns as

“Non-key columns, called included columns, can be added to the leaf level of a nonclustered index to improve query performance by covering the query. That is, all columns referenced in the query are included in the index as either key or non-key columns. This allows the query optimizer to locate all the required information from an index scan; the table or clustered index data is not accessed. For more information, see Create Indexes with Included Columns.”

How to Create a New Index That Supports the Include Option on an X3 Table

Let’s take a common example of an index suggestion inside X3 where we have found a useful index on the ATEXTE table.Here’s a screenshot that shows the create index statement that comes back from the query I provided earlier, and where it is explained a bit.

As we can see from the screenshot above, the language field (LAN_0) is put on the main part of the index. This makes sense because the original query joins by the LAN_0 field causing a seek operation. Here is the original query for this particular index suggestion:

(@P1 nvarchar(256),@P2 nvarchar(256))
Select AMK_.ROWID, AMK_.MODULE_0, AMK_.ABRMSK_0, AMK_.CODMSK_0, ATX_.TEXTE_0
From SUPV6.AMSK AMK_
LEFT OUTER JOIN SUPV6.ATEXTE ATX_
ON ((ATX_.LAN_0 = @P1)
AND (ATX_.NUMERO_0 = AMK_.INTMSK_0))
Where
(UPPER( AMK_.ABRMSK_0 ) = @P2)
Order by AMK_.CODMSK_0 Option (FAST 100)

This query is trying to retrieve the string message or description in the ATEXTE table, the TEXTE_0 column, so for sure, this could be a good index to implement as Sage ERP X3 uses this all over the place.

The query plan for this common query results in a hash match however, largely because the NUMERO_0 and the TEXTE_0 were contained on two other indexes:. And as you can see in the screenshot below of the query plan, SQL Server is recommending to create a nonclustered index already (the part in red above the query plan is the missing index suggestion).

Sage ERP X3 can accommodate the specific way SQL Server would like this index built using the following approach:

To build this index inside X3 you would do the following:

1. Launch Development > Data and parameters > Tables > Tables
2. Bring up the table code ATEXTE
   
3. Add a new index called ZMISS1 and set it to allow duplicates

1. Setting a new index to be unique is a functional change to the application and shouldn’t
   be done without serious consideration

• In the Configuration file screen within function add the following syntax
  
• Save the table modifications
• Perform a table validation

1. Note: A forced validation is not required

How to create a new clustered index on an X3 table

Now, let’s say you’ve identified a great opportunity for a clustered index, that can also be accommodated with the configuration file text box inside the table dictionary. For example, let’s say you wanted to change the NUMERO index on the ATEXTE table to clustered. To do that you would use
the following syntax:

So, the steps to implement this would be as follows:

1. Launch Development > Data and parameters > Tables > Tables
2. Bring up the ATEXTE table
3. On the index tab add the syntax identified above into the configuration file section
   

1. Notice, you can put the INCLUDE directive as well as the Clustered index directive in the
   same configuration file

• Press the save button
• Now press the validate button and perform a forced validation

1. Note: Forced validations should not be performed during production times. Users should be off the system in this situation.

Defect Report – Quick Caveat and Full Disclosure

One thing to note, when I wrote these instructions up I found a defect with the Sage ERP X3 runtime. The syntax above will assign the include component to the wrong index on a table. As it turns out, the included columns portion will get assigned to the index just prior to the one you told it to within the configuration file. This will be fixed in the next runtime engine for Sage ERP X3! In this situation, if you truly want that included column set then you can implement it inside SQL, if you sufficient training within Sage ERP X3 to understand the trade offs. Or, and this is the preferred solution, wait until the next runtime comes out (that which is after runtime 223) as it should contain this fix. Disclaimer alert: The word should is not a promise to deliver by Sage or its employees.

I hope this helps you squeeze more performance and elongate the life of your hardware for your SQL Server implementation. Drop me a line to let me know if you liked this article!