In the following months, I hope to do some hands-on tuning of various ISV SQL Server databases in use at client sites. Some folks would assume that there is nothing to do – the ISV have already tuned it. I hate to disappoint you, but as an ISV Architect I have seen tuning not happening in shipping products for a wide variety of reasons:
- “We cannot afford to delay shipping to get tuning done”
- “Customers are not complaining about performance – why spend the time”
- “Tuning is not a customer feature request”
- “Test/Quality Assurance will alert us if there is a problem”
I have never been able to get a product fully tuned before sales and marketing forces it out of the door.
On the other side is a simple argument: a tuned SQL Server database means better performance, fewer support calls, and a significantly lower Total Cost of Ownership (TCO) to the client. How much cheaper? The best performance increase that I have done was 20 fold (2,000%), resulting in a single socket server not breaking sweat, compared to far more expensive hardware. If you have competition in your market, reducing the TCO makes you more competitive and increases sales. If you are a client, it means that you can avoid or delay hardware purchases by increasing throughput of your existing system when load increases.
Latest experience and bottom line financial impact
I started this series of posts after doing a preliminary rough tuning of an Enova SmarTeam SQL Server installation used by Langen Packaging Group. After tuning, the users reported that the queries are taking half of the time that they did prior. If you are running on Oracle, this is of special significance:
- Case studies have shown that SQL Server has better performance per dollar than Oracle when installed as is. See TCP-E Results or individual product case studies.
- SQL Server is bundled with a set of tools that allows ISV products to be easily tuned, yielding more than a doubling of work load on the same hardware for some ISV products.
If you are using Oracle and looking at upgrading your hardware to deal with increased load, the cheapest solution to address this load may be simply changing to SQL Server and then tuning the ISV application. Migrating your data from Oracle to SQL Server is not a complex process as you can read in the first of this series of posts on migration.
I will not bore you with yet another retelling on how to do a Tuning Trace and running the Database Engine Tuning Advisor (DTA). If you are not familiar with these, see my earlier post or:
- Creating a Workload by Using a Trace Log
- Running Database Engine Tuning Advisor and Selecting Indexes
These tools are located on your programs menu as shown below.
If they are missing, you may need to change your installation settings to include them.
The tuning flow is shown below and consists of:
- Capturing representative activity against the database into a trace log.
- Executing tuning against the trace log
- Select and implement the best candidate index or index pattern
- Repeat the tuning and selection until:
- Nothing more to add
- Expected gain falls below some threshold.
You may ask, “Why not just go for all the recommendations and save time iterating through tunings and recommendations?” The answer is simple: “Yes, you can but…”
The “but” stems from my passion, getting the maximum performance possible. Often correlations between the recommendations exist. What do I mean by correlation?
- Index A added alone may improve performance by 12%,
- Index B added alone may improve performance by 10%
- Adding both A and B may not result in 22% improvement, you may see just 14% improvement.
My experience has been that iterating through the recommendations results in fewer indexes added in total. Every index and statistic consumes resources; keeping the number of indexes and statistics low is a good thing.
Keeping It Simple
DTA provides many reports that can work as a good sleep aid (see below). We will skip these reports and keep to a simple approach.
There are three ways to view the recommendations for indexes and statistics produced by Database Engine Tuning Advisor:
- Index Recommendations Tab
- Save Recommendations …
- Export Session Results …
Each of these views provides the same information. The key difference is that the [Export Session Results …] saves the information as XML. With XML we can write code to extract information instead of having to read and manually compile information. Below, I will show how to get each of these different formats of the recommendations.
Index Recommendations Tab
This presents a list of all of the recommendations with a checkbox beside each. The list can contain hundreds of recommendations. Most people become totally saturated reading through this list and just click All, and the Action / Apply Recommendations.
Save recommendations (TSQL)
This writes the recommendations as TSQL to a file. This is easier to read than the Index Recommendation Tab above. You will find this option under Action on the DTA Menu
The saved file looks like:
CREATE NONCLUSTERED INDEX [_dta_index_TDM_CLASS_DESCRIPTION_13_1420584149__K1_K2_3_4_5_6_7_8_9_10_11_12_13_14] ON [SMARTEAM].[TDM_CLASS_DESCRIPTION] ( [CLASS_ID] ASC, [COL_ORDER] ASC ) INCLUDE ( [COL_NAME], [COL_TYPE], [COL_SIZE], [COL_LANG], [COL_MANDATORY], [INDEX_ORDER], [REF_CLASS_ID], [REF_TYPE], [COL_DISPLAY], [COL_DESCRIPTION], [COL_MASK], [DFLT_DISPLAY_ORDER]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] go CREATE STATISTICS [_dta_stat_1420584149_2] ON [SMARTEAM].[TDM_CLASS_DESCRIPTION]([COL_ORDER]) Go
If you have different file groups for various types of data, then you may wish to alter ON [PRIMARY] to the appropriate file group.
Export Session Results… (XML)
You will find this option under File on the DTA Menu.
Clicking the menu item opens up a dialog to save the information
The saved file looks like:
<Table> <Name>TDM_CLASS_DESCRIPTION</Name> <Recommendation> <Create> <Index Clustered="true" IndexSizeInMB="0.539063"> <Name>_dta_index_TDM_CLASS_DESCRIPTION_c_13_1420584149__K9</Name> <Column Type="KeyColumn" SortOrder="Ascending"> <Name>[REF_CLASS_ID]</Name> </Column> <FileGroup>[PRIMARY]</FileGroup> </Index> </Create> <Create> <Index IndexSizeInMB="0.539063"> <Name>_dta_index_TDM_CLASS_DESCRIPTION_13_1420584149__K1_K2_3_4_5_6_7_8_9_10_11_12_13_14</Name> <Column Type="KeyColumn" SortOrder="Ascending"> <Name>[CLASS_ID]</Name> </Column> <Column Type="KeyColumn" SortOrder="Ascending"> <Name>[COL_ORDER]</Name> </Column> <Column Type="IncludedColumn"> <Name>[COL_NAME]</Name> </Column> <Column Type="IncludedColumn"> <Name>[COL_TYPE]</Name> </Column>
The time to do a tuning can easily take several weeks for a deep analysis or less than two hours if you do no analysis and accept recommendations without any analysis. For each ISV product in this series, I will be doing pattern analysis because most of the ISV products are customized by the user. Most recommendations derived can be executed against your SQL Server database without a need to explicitly modify it for your customizations. The result should be a significant performance improvement.
These recommendations are the low hanging fruit off the tuning tree. There are higher fruits available if you have the time, or bring in an experienced consultant to retrieve them.