Idxtest
The Idxtest application illustrates using SQL-DMO to test optimization strategies for stored procedures and views. The sample uses dependency enumeration to determine objects dependent upon a Microsoft® SQL Server™ table. The user can then create test indexes and execute selected stored procedures or views and view execution time with or without the test index.
Default Location
C:\Program Files\Microsoft SQL Server\80\Tools\Devtools\Samples\sqldmo\vb\idxtest
Running the Sample
- Open the Idxtest.vbp project.
- Run the application.
Remarks
The Idxtest sample contains two forms. The main form, shown in the illustration, solicits login information from the user and connects to the indicated server.
Upon successful connection, the user can browse databases and tables to generate a list of dependent stored procedures and views.
With one or more views or stored procedures selected in the list, the test command and results grid is enabled. Click Test stored proc(s) to execute a selected stored procedure, or a SELECT * FROM query on the view, capturing execution time in the results grid.
The columns of the selected table are displayed in the index creation lists. To create an index for testing, use Add>> to move columns to the Columns in index list, then click Create index for test to create the index and populate it.
Objects, Methods, and Properties Illustrated
| Column.Name | QueryResults.GetColumnBool |
| Columns.Item | QueryResults.GetColumnLong |
| Database.ExecuteWithResults | QueryResults.GetColumnString |
| Database.ExecuteWithResultsAndMessages | QueryResults.Rows |
| Database.Name | StoredProcedure.EnumParamters |
| Databases.Item | SQLServer.ApplicationName |
| Index.IndexedColumns | SQLServer.Connect |
| Index.Name | SQLServer.DisConnect |
| Index.Remove | SQLServer.LoginSecure |
| Index.Type | Table.EnumDependencies |
| Indexes.Add | Table.Name |
| New SQLDMO.Index | Tables.Item |