472,373 Members | 1,951 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,373 software developers and data experts.

Removing Tuning Wizard Indexes

Just curious if anyone has a script to find and delete all indexes
created by index tuning wizard, leaving the original indexes untouched.
All of the original indexes in this particular database are preceded
with IX_, whereas those created by ITW are the table name followed by a
number.

I'm thinking of something along the lines of "sp_MSforeachtable
@command1="print '?'" + a DBCC which just targets the ITW indexes (if
such a thing exists). Any ideas how to go about this?
Jul 20 '05 #1
3 2494

"crystal1" <cr******@spamless.net> wrote in message
news:ci********@enews3.newsguy.com...
Just curious if anyone has a script to find and delete all indexes created
by index tuning wizard, leaving the original indexes untouched. All of the
original indexes in this particular database are preceded with IX_,
whereas those created by ITW are the table name followed by a number.

I'm thinking of something along the lines of "sp_MSforeachtable
@command1="print '?'" + a DBCC which just targets the ITW indexes (if such
a thing exists). Any ideas how to go about this?


You could execute the output of a query like this (see "sysindexes" in Books
Online):

select 'drop index ' + object_name(id) + '.' + name
from sysindexes
where indid not in (0,255)
and name like object_name(id) + '[0-9]%'

Simon
Jul 20 '05 #2
"Simon Hayes" <sq*@hayes.ch> wrote in message news:<41**********@news.bluewin.ch>...
"crystal1" <cr******@spamless.net> wrote in message
news:ci********@enews3.newsguy.com...
Just curious if anyone has a script to find and delete all indexes created
by index tuning wizard, leaving the original indexes untouched. All of the
original indexes in this particular database are preceded with IX_,
whereas those created by ITW are the table name followed by a number.

I'm thinking of something along the lines of "sp_MSforeachtable
@command1="print '?'" + a DBCC which just targets the ITW indexes (if such
a thing exists). Any ideas how to go about this?


You could execute the output of a query like this (see "sysindexes" in Books
Online):

select 'drop index ' + object_name(id) + '.' + name
from sysindexes
where indid not in (0,255)
and name like object_name(id) + '[0-9]%'

Simon

I guess you are talking about Hypothetical Indexes.
Here is the script to drop the hypothetical indexes.

DECLARE @strSQL nvarchar(1024)
DECLARE @objid int
DECLARE @indid tinyint
DECLARE ITW_Stats CURSOR FOR SELECT id, indid FROM sysindexes WHERE
name LIKE 'hind_%' ORDER BY name
OPEN ITW_Stats
FETCH NEXT FROM ITW_Stats INTO @objid, @indid
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SELECT @strSQL = (SELECT case when INDEXPROPERTY(i.id, i.name,
'IsStatistics') = 1 then 'drop statistics [' else 'drop index [' end +
OBJECT_NAME(i.id) + '].[' + i.name + ']'
FROM sysindexes i join sysobjects o on i.id = o.id
WHERE i.id = @objid and i.indid = @indid AND
(INDEXPROPERTY(i.id, i.name, 'IsHypothetical') = 1 OR
(INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1 AND
INDEXPROPERTY(i.id, i.name, 'IsAutoStatistics') = 0)))
EXEC(@strSQL)
FETCH NEXT FROM ITW_Stats INTO @objid, @indid
END
CLOSE ITW_Stats
DEALLOCATE ITW_Stats

Thank you
Raju
Jul 20 '05 #3
Simon Hayes wrote:
You could execute the output of a query like this (see "sysindexes" in Books
Online):

select 'drop index ' + object_name(id) + '.' + name
from sysindexes
where indid not in (0,255)
and name like object_name(id) + '[0-9]%'

Simon


Excellent. Thank you.
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Maryam | last post by:
Hi, I am having problems getting anything useful out of the index tuning wizard. I have created a table and inserted data into it. When i run the index tuning wizard i expect 2 indexes to be...
0
by: NathanG | last post by:
Hi, I've run the (SQL2K) Index Tuning Wizard against our 50GB database and it has recommended changes that will produce a "-3506%" improvement iin performance. The recommendations include a...
2
by: SAP BASIS Consultant | last post by:
Hello, I have worked with Oracle and SAP over a number of years. I worked a bit with DB2 and SAP, and I am expecting to do so in the future. I am looking for a good book on tuning (For both OLTP...
2
by: Ed L. | last post by:
The planner is choosing a sequential scan for my query. I am trying to understand why since it is clearly not the fastest choice, and what the proper tuning dial is to adjust here. Here's the...
2
by: rAinDeEr | last post by:
Hi, I have a table with the following definition. The primary key is west_nme and west_eff_tms and i have created unique index on them. The foreign keys which reference other parent tables are...
0
by: uzi | last post by:
Hi Does someone know how to embed the audio and video tuning wizard into an application? I have noticed that the same component is used by messenger and Microsoft one note.
0
by: Medhatithi | last post by:
Hi, I have been in several ways benefiited from this site. I would like to share some sql tuning techniques(simple, but effective) with you all. SQL Tuning Tips Oracle Tips Session #6 ...
4
by: Tommy Hayes | last post by:
Hello all, I want to use the SQL Server 2005 Tuning Advisor on our database, and I'm hoping someone here can just confirm the steps for me. We have a 10GB database that has a number of...
3
by: Sam Durai | last post by:
A simple update involving two small tables takes 45 minutes to complete. I would appreciate if you can kindly help me to understand the root cause of this slowness. Users would be happy if I can...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
1
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
0
Oralloy
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
1
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...
1
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...
0
by: jack2019x | last post by:
hello, Is there code or static lib for hook swapchain present? I wanna hook dxgi swapchain present for dx11 and dx9.
0
DizelArs
by: DizelArs | last post by:
Hi all) Faced with a problem, element.click() event doesn't work in Safari browser. Tried various tricks like emulating touch event through a function: let clickEvent = new Event('click', {...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.