Unexpected "Tuning indexed views requires SQL Server 2005 Enterprise Edition"?
One day this week, I received the following query from a colleague.
Run ScriptToPrepareRepro.sql in order to backup and drop a couple of indexes AdventureWorks.
Start the Database Tuning Advisor and create a new tuning session.
Select the Workload.sql script as workload file to run against AdventureWorks.
On the “Tuning Options” tab, make sure that “Indexes”, “No partitioning” and “Keep all existing PDS” are selected.
Start the analysis (click Actions/Start Analysis)
Evaluate these recommendations (click Actions/Evaluate Recommendations).
On the “Tuning Options” tab, note that it says that “Tuning options are not considered in evaluation mode”, and these are grayed out.
Start the evaluation of the recommended indexes (click Actions/Start Analysis).
If all of the above is running against SQL Server 2005 Standard Edition, the following error appears during action “Consuming Workload”:
TITLE: DTAEngine
------------------------------
Tuning indexed views requires SQL Server 2005 Enterprise Edition.
------------------------------
BUTTONS:
OK
------------------------------If steps 1 through 9 are ran against SQL Server 2005 Enterprise/Developer Edition, the error does not appear.
This demonstrates that it is not possible to evaluate recommendations from the Database Tuning Advisor, unless this is done using Enterprise/Developer Editions.
I have not been able to locate anything in Books Online or KB that documents that this feature of DTA is only available on Enterprise/Developer Editions. However, considering point 7 above, this may be an indication that this will not work on anything but Enterprise/Developer, since it is under TuningOptions that the FeatureSet element (IDX_IV, IDX, IV or NCL_IDX) is defined.
After having debugged it and analyzed the source code for DTA wizard and engine, I came to the conclusion that this is due to a bug, which I have filed so that it is considered in a future Service Pack.
I cannot publish source code of the product here, so I'll try to describe what's happening without showing one line of code.
The DTA wizard prepares an XML file with all the instructions and constraints specified by the user, and that file is passed to the DTAEngine which parses the input XML file (attached is the one used in my sample repro) and prepares itself to act according to the settings provided. It happens that if the DTAEngine XML parser encounters it’s working to ONLY evaluate recommendations (EvaluateConfiguration element exists in the XML config file), it ignores the FeatureSet element (provided it exists).
All the information retrieved from the XML file is parsed and internally stored in a class (let's call that class COpts). Since the FeatureSet element doesn’t exist in the input XML file, the member variable (COpts->m_FeatureSet) where the parser stores the list of features found in the input should be set to zero (m_FeatureSet is a bitmask where every valid FeatureSet is designated with a given bit). Having m_FeatureSet set to zero would indicate that none of the valid FeatureSets have been set.
| Note: In SQL Server 2005 SP2, those valid values are IV for indexed views, IDX for clustered and non-clustered indexes, IDX_IV for clustered and non-clustered indexes and indexed views, and NCL_IDX for non-clustered indexes. |
The problem is that but it happens that the constructor for COpts, even before the XML is parsed, initializes m_FeatureSet with a value that indicates that the clustered indexes, non-clustered indexes, and indexed views.
Later in the code path, we evaluate that if either IDX_IV or IV feature sets were enabled, and SQL Server is version 2005 and is neither Enterprise or Developer edition, we throw a specific exception that we catch and end up producing the message which gives the name to this post (Tuning indexed views requires SQL Server 2005 Enterprise Edition).
| Note: Attached to this post you can find the two files used for the repro and the XML which was produced during my test. |