[Prior Post in Series] [Next Post in Series]
Recently I had an opportunity to do an example tuning on an Enovia SmarTeam installation. Marc Young at xML Solutions arranged for me to work with the folks at Langen Packaging Group in Mississauga, Ontario. Mirek Tokarz facilitated the access: dealing with NDAs, IT Security Policy and the many other issues that needed to be well addressed when an external consultant accesses a system containing intellectual property and proprietary information.
I will not subject you to 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:
I was connected to an old developer test system populated with a 6 month old image of the production system. For purposes of tuning, this is more than adequate. For the purpose of intellectual property and protection from competition, it is an excellent choice. Regardless of NDA, you do not want to needlessly expose current inquiries and order to any 3rd party person, such as a consultant.
Mirek had taken me through a quick walk thru two weeks earlier, so I fired up Smarteam and proceeded to go into a quasi-black box testing. I got a few error messages that were handled well and I stopped when I had accumulated a small (1 MB) trace log consisting of 960 statements for tuning. My preference is to have a 200MB to 1 TB trace log.
The results of various runs using DTA are shown below. As cited in my introductory post, I tend to add indexes in steps because there is often a strong correlation between indexes. I will look at each run in detail in subsequent posts and provide a ready-to-try script as the last post of this series.
Time taken for tuning
Expected percentage improvement
Number of indexes recommended to be created
Number of indexes on views recommended to be created
Number of statistics recommended to be created
8 Hours 37 Minutes
1 Hour 40 Minutes
Key on Object_ID,Class_ID
1 Hour 45 Minutes
Key on Creation_Date,User_Object_ID, User_ID_Mod
1 Hour 3 Minutes
Key on Object_ID (No Class_ID)
Applying the DTA 7 Indices ONLY
The purpose of the tuning was not to do a comprehensive tuning – rather an illustrative tuning in detail to show the logic that I am using. The purpose of the tuning is also to illustrate some of the side-effects that you may see and explain them. For example, adding one set of indices increased the number of recommended indexes from 7 to 32, which seems counter to expectations of seeing decreasing recommended indexes.
You can see the details in the following posts (coming soon):
Mirek reported to me that queries were taking literally half of the time that they were prior to my tuning. His staff will be more productive and less frustrated as a result. He is convinced of the benefit of tuning indexes on an ISV system.
On my first run above, I received two warning messages on the [Progress] tab shown below. I should explain these a bit.