Index Tuning Wizard Full Text Search

1
Index Tuning WizardFull Text Search

• Shaun Cassells
• November 3rd 2003

2
Index Tuning Wizard

• This wizard reviews a saved trace file then
  recommends changes to your indexes on the server
  to enhance the performance of the quires captured
  during the trace.

3
Step 1

• Select Index Tuning Wizard from the Tools menu in
  the SQL Server Profiler.
• Open Enterprise Manager
• Go to the tools Menu
• Go to Wizards
• Select Management tab

4
Select your server and DB
5
Step 2

• The ITW recommends indexes for only one DB at a
  time
• The default is not to remove any indexes you
  already have and to perform a less thorough
  analysis
• Selecting the option to perform a thorough
  analysis is a good idea because you would hate to
  have the wizard recommend indexes that arent as
  good as they could have been

6
Specify Workload dialog
7
Step 3

• Youre asked to identify the workload to be
  analyzed
• If you already have a trace captured, you can
  either select the trace file or point the ITW at
  a table containing your trace data

8
Advanced configuration options
9
Step 4

• By default, the wizard doesnt attempt to
  optimize any tables, so select all tables in your
  database. Then click next.
• The ITW then analyzes the workload youve given
  to it.
• This process might take awhile, depending on the
  size of your trace file.
• When its completed, the recommended indexes are
  show in the Index Recommendations dialog

10
Step 5

• You can get an analysis results, by clicking the
  analysis button and viewing the Original
  Configuration report
• Notice that its identical to the Recommended
  Configuration report
• Next again. If any changes were recommended, you
  can select to run the wizards recommended
  changes on a given SQL Server Agent Job Schedule,
  or save the changes as a T-SQL script and review
  or run them at your leisure

11
Step 6

• You now will have the option of when to apply the
  changes (if ANY).
• I would recommend immediately
• Thats it!
• Youve probably guessed that getting to really
  use the ITW is books and classes all in itself

12
Full-Text Indexing

• Full-Text indexes enable you to create an index
  on a text column, as well as any other character
  column.
• This feature is installed by default starting
  with SQL Server 2000 (except on 9x)
• Requires a unique index on each table you want to
  use for full-text indexing.
• Only the owner of a table can create a full-text
  index

13
One per table

• You can have only one full-text index per table,
  and the index is physically stored outside SQL
  Server (in your \mssql\FTData directory)
• Unlike normal SQL Server indexes, full-text
  indexes arent self-maintaining.
• You should schedule a job to update it
• There is a wizard to do this ?

14
FT Wizard

• In Enterprise Manager
• Select the table you want to do the FT query on
• Go to the tools drop down menu ? full-text wizard
• You will then need to select the unique index you
  want to use
• Later you can select the text you want to use as
  your query, even a word document

15
FT wizard

• After youve gone through it all go to the
  Enterprise Manager and expand the database
• Go to Full-Text Catalog
• Right click in the new window and select Start
  Full Population
• Thats it! Youve populated the index and can now
  query against it.
• Now be careful, you need to review the query it
  returns. Not always the most efficient query,
  but a good start Usually.