How to Gather the Information

Gathering information to solve an optimizer problem is a multi-step procedure, involving distinct Transact-SQL sessions or commands. You will save each session’s output to a file. You can then examine the information in these files, or make the files available to your Sybase Technical Support representative.

Steps for Gathering the Information

Here are the steps for gathering the information:

Save the text of the Transact-SQL query that provoked the optimizer problem to a file called query_text.
Create an input file, input_file1, that contains the following Transact-SQL:
```
1> use database_name
2> go
1> sp_help table_name
2> go
```
database_name is the name of the database containing table_name, the relevant table. If there is more than one table involved in the problem query, run the input_file1 script once and name each file according to its table name.

If the query’s FROM clause involves a view, input_file1 should look like this:
```
1> use database_name
2> go
1> sp_helptext view_name
2> go
1> sp_help base_table_name
2> go
...repeat for other base tables in view
```
Run input_file1 through isql, saving the results to output_file1:
```
% isql -Usa -P < input_file1 > output_file1 -e
```
Save output_file1.
Create a second input file, input_file2, that contains the following Transact-SQL:
```
1> use database_name
2> go
1> select @@version
2> go
1> set showplan on
2> go
1> set statistics io on
2> go
1> set statistics time on
2> go
1> dbcc traceon(3604)
2> go
1> dbcc traceon(302)
2> go
1> dbcc traceon(310)
2> go
... contents of query_text
```
You must have “sa_role” to run dbcc traceon(302) and dbcc traceon(310).

At the end of input_file2, include the contents of query_text, the file you created in step 1, which includes the Transact-SQL code that provoked the optimizer problem.
Run input_file2 through isql, saving the results of the commands in input_file2 to output_file2:
```
% isql -Usa -P < input_file2 > output_file2 -e
```
Save output_file2 .
Run optdiag to capture table statistics, saving the results of the command to output_file3:
```
 % optdiag statistics database..table -o output_file3
```
If the query involves multiple tables, run optdiag for each table and save the output in separate files.

You should now have the following text files:

File name	Contains
query_text	The text of the Transact-SQL query, stored procedure, trigger, or view definition that provoked your optimizer problem.
output_file1	The results of running sp_help on the table(s) implicated in the optimizer problem.
output_file2	The results of running set showplan on, set statistics io on, set statistics time on, dbcc traceon (302), dbcc traceon (310), and the Transact-SQL query that provoked the optimizer problem.
output_file3	The output from the optdiag utility.