Errors and Messages
Events in the workload that cannot be tuned produce errors and messages. These errors and messages are written to the tuning log, but do not stop a tuning session. Errors and messages report conditions that can provide useful information to users about tuning sessions, which helps them understand the tuning process better. Users can use this information to adjust their input parameters based on the feedback of a previous tuning session. These adjustments result in more effective use of Database Engine Tuning Advisor.
Such feedback can be particularly useful when large workloads are used to tune databases. For example, an error or message is written to the tuning log if one of the following conditions occurs:
A temporary table created by the workload no longer exists in the database.
A table referenced in the query was not selected for tuning.
The event could not be parsed.
Tuning Log Entries
You can cause Database Engine Tuning Advisor to write errors and messages to the tuning log by specifying a value for the –e argument when you use the dta command prompt utility. Or, you can select Save tuning log on the General tab of the Database Engine Tuning Advisor graphical user interface (GUI). For more information about using these two user interfaces of Database Engine Tuning Advisor. see How to: Tune a Database and dta Utility.
If you do not specify a file or table name for the -e argument of the dta utility, you can use the GUI to view tuning log information. To view the tuning log with the GUI, click the Progress tab during the tuning session or after it finishes. For more information, see How to: View Tuning Output.
The tuning log in the GUI contains the columns listed in the following table.
Tuning Log Columns
Column name |
Description |
|---|---|
CategoryID |
Maps the record to a defined identifier (ID). Each instance of the error is assigned an ID. The ID is a three-digit number that is prefixed by a single character. The prefix allows you to search the tuning log table for entries that pertain to statement, constraint, or server-level errors. The prefix character categorizes the records as follows:
|
Event |
The event string if applicable. Some messages are not related to any event, therefore this column can be empty. An event string corresponds to a stored procedure name, batch, and so on. |
Statement |
Database Engine Tuning Advisor parses the event and tunes the statements that are associated with the event. This column captures the statement (if applicable) that is related to the message that is returned to the user. This column can also be empty if it is not relevant to the message. Also, if the server cannot parse an event, this column is empty. |
Frequency |
Number of occurrences of this record. The default is 1. |
Reason |
The reason why the statement or event could not be tuned. This message can be generated by the server, ODBC, or by Database Engine Tuning Advisor. |
The following tables list messages that can be returned for the categories listed in the description of the CategoryID column in the preceding table.
Statement or Workload Related Errors
Category ID |
Tuning log message |
|---|---|
S001 |
Statement does not reference any tables. |
S002 |
Statement references table not selected to tune. |
S003 |
Statement references only small tables. |
S004 |
Statement references index hints or statement contains a NOEXPAND query hint. |
S005 |
Hardware characteristics of local computer is assumed. Returned when using the test server tuning mode. The extended stored procedure xp_msver returned an error, so Database Engine Tuning Advisor is unable to determine the hardware characteristics of the remote computer. For more information, see Reducing the Production Server Tuning Load. |
S006 |
Storage available on attached disks is ignored. An error occurred which prevents Database Engine Tuning Advisor from gathering information about storage space available on attached disks. In this case, Database Engine Tuning Advisor assumes an available storage size of three times the current raw data size, which includes the total size of heaps and clustered indexes on tables in the database. |
S0071 |
Replace event <string> with <string> for tuning purposes. |
S0082 |
Event does not reference any tables. |
S0092 |
Event references objects that are not selected to tune. |
1 Database Engine Tuning Advisor replaces sp_cursoropen, sp_executesql, sp_prepare, sp_cursorprepare, sp_prepexec, and sp_cursorprepexec events with the Transact-SQL string that is embedded in such events for purposes of tuning. Error message S007 provides the original event and the replaced event in the Reason column of the tuning log.
2 These are event-level messages, so the Statement column of the tuning log will be empty.
Constraint Related Errors
Category ID |
Tuning log message |
|---|---|
C002 |
The indexed view specified in the input configuration does not have any keys. However, this will be ignored while processing the configuration. |
C003 or C004 |
Database Engine Tuning Advisor detects whether the default tuning time, or a user-specified tuning time was set to tune the workload, and returns one of the following messages in the tuning log. (dta.exe assumes 8 hours and the GUI assumes one hour) If the default time was used, the message is: All events in the workload were not analyzed. dta.exe assumes a default time of 8 hours. Consider providing a time bound using one of the following methods. (1) If you are using the graphical user interface, limit the tuning time in the Tuning Options tabbed page. (2) If you are using dta.exe, specify the maximum amount of time for the -A argument. (3) If you are using an XML input file, specify the maximum amount of time for the TuningTimeInMin element under TuningOptions. If a user-specified time was used, the message is: All events in the workload were not analyzed. Use one of the following methods to increase time bound. (1) If you are using the graphical user interface, limit the tuning time in the Tuning Options tabbed page. (2) If you are using dta.exe, specify the maximum amount of time for the -A argument. (3) If you are using an XML input file, specify the maximum amount of time for the TuningTimeInMin element under TuningOptions. |
Server Related Errors
Category ID |
Tuning log message |
|---|---|
E000 |
Specific message from the server. This message varies. |
E001 |
Specific message from the test server. This message varies. |
E002 |
Database could not be cloned successfully in test server. |