Index Tuning Wizard ???

[Connatic]

I have a (like)query that was taking 6 seconds to run, so after valuable assistance from members of this forum, i tried copying the whole database (tables & et all) onto another server.

Doing this, and then using the Index Tuning Wizard reduced the time of my query down to 1 second.

When i tried to run the Index Tuning Wizard on my original database it didn't come back with the same suggestions as for the new version of the database !!!

So, at this point i rebooted my server & deleted my database & then rebuilt it.

Everything in the two databases are identical, but what i can't figure out is why the Index Tuning Wizard has improved my query performance in my new database, but won't do it for my original database???

The Index Tuning Wizard Improved my query by creating a Clustered Indexed view !

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW [dbo].[_hypmv_0] WITH SCHEMABINDING AS SELECT  [dbo].[matters].[m_mat_desc] as _hypmv_0_col_1,  [dbo].[matters].[m_clt_name] as _hypmv_0_col_2,  [dbo].[matters].[m_mat_num] as _hypmv_0_col_3,  [dbo].[matters].[m_clt_code] as _hypmv_0_col_4,  [dbo].[matters].[m_mat_id] as _hypmv_0_col_5,  [dbo].[addresstypes].[at_desc] as _hypmv_0_col_6,  [dbo].[addresses].[add_id] as _hypmv_0_col_7,  [dbo].[addresses].[add_type_id] as _hypmv_0_col_8 FROM  [dbo].[addresstypes],  [dbo].[addresses],  [dbo].[matters],  [dbo].[mat_addr_lnk]   WHERE ( [dbo].[addresstypes].[at_type_id] = [dbo].[addresses].[add_type_id] ) AND ( [dbo].[addresses].[add_id] = [dbo].[mat_addr_lnk].[add_id] ) AND ( [dbo].[matters].[m_mat_id] = [dbo].[mat_addr_lnk].[mat_id] )
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

I do not know how to get my original database to use this indexed View Manually & do not know why The Index Tuning Wizard is not creating it Automattically, and this is the crux of my problem !!!

Any help or suggestions will be greatly appreciated !!

Here is my Query for those who did not see my earlier post

SELECT m.m_mat_id, m.m_clt_code, m.m_mat_num, m.m_clt_name, 
         m_mat_desc, t.at_desc, a.add_type_id, a.add_id 
  FROM matters m, addresstypes t, addresses a, mat_addr_lnk l
  WHERE (m.m_clt_name like '%' + 'brown' + '%')
  and m.m_mat_id = l.mat_id 
  and a.add_id = l.add_id 
  and a.add_type_id = t.at_type_id 
  ORDER BY m.m_clt_code, m.m_mat_num ASC 
[code]

i will post the before & after Query plans aswell if anybody wants to see them !!

-Richard

 

[Connatic]

What no-one know anything about the Index Tuning Wizard, or how to carry out it's function in a manual fashion

I don't need an exact answer, just a point in the right direction will help !!

Anyone ???? Please ????