Drop and recreate indexes

So, the ongoing saga continues. To drop and recreate my indexes on a
specific table, do I need to copy the SQL for the existing indexes (I need
to keep them the same) then delete them thru Manage Indexes and run the SQL
create index scripts? Or is there a better way? Thanks for the help!
WillieWillie,
Drop/re-create or rebuild? i.e, DBCC DBREINDEX / DBCC INDEXDEFRAG?
HTH
Jerry
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:ONgI0I20FHA.3780@.TK2MSFTNGP12.phx.gbl...
> So, the ongoing saga continues. To drop and recreate my indexes on a
> specific table, do I need to copy the SQL for the existing indexes (I need
> to keep them the same) then delete them thru Manage Indexes and run the
> SQL create index scripts? Or is there a better way? Thanks for the help!
> Willie
>|||Well, I've already tried dbcc indexdefrag and dbcc dbreindex and neither of
them seem to have helped my situation, so I was thinking I would go to the
next level and start over again with the indexes by creating new indexes
(with the same definitions). Is that a reasonable thing to do?
Willie
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23z%23dLL20FHA.664@.tk2msftngp13.phx.gbl...
> Willie,
> Drop/re-create or rebuild? i.e, DBCC DBREINDEX / DBCC INDEXDEFRAG?
> HTH
> Jerry
> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
> news:ONgI0I20FHA.3780@.TK2MSFTNGP12.phx.gbl...
>|||Willie,
I don't remember what your original issue was. However, you could try
recreating a single index prior to hitting them all to see if it fixes your
issue. (i.e, start small). Check out CREATE INDEX ...WITH DROP_EXISTING and
DBCC DBREINDEX.
HTH
Jerry
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:eKFSRU20FHA.404@.TK2MSFTNGP09.phx.gbl...
> Well, I've already tried dbcc indexdefrag and dbcc dbreindex and neither
> of them seem to have helped my situation, so I was thinking I would go to
> the next level and start over again with the indexes by creating new
> indexes (with the same definitions). Is that a reasonable thing to do?
> Willie
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23z%23dLL20FHA.664@.tk2msftngp13.phx.gbl...
>|||Willie Bodger wrote:
> Well, I've already tried dbcc indexdefrag and dbcc dbreindex and
> neither of them seem to have helped my situation, so I was thinking I
> would go to the next level and start over again with the indexes by
> creating new indexes (with the same definitions). Is that a
> reasonable thing to do?
I don't think so. DBCC DBREINDEX does pretty much the same as dropping
and recreating so I would not expect your situation to better. Maybe you
need a *different* index or must take other measures (e.g. work on the IO
performance side). Did you actually identify the cause of your problem?
Kind regards
robert|||Verify that the table does not have any poorly written / performing
triggers.|||I have not been able to really identify the issue, but the table doesn't
have any triggers (I thought it did, but upon actually going to Manage
Triggers it shows none). I ran thru the index tuning wizard and it had no
suggestions to make, the Update Statement is super simple (set field=xx
where field=yy). Is an Update statement just that slow?
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23LUT0%2390FHA.2924@.TK2MSFTNGP15.phx.gbl...
> Verify that the table does not have any poorly written / performing
> triggers.
>|||> suggestions to make, the Update Statement is super simple (set field=xx
> where field=yy). Is an Update statement just that slow?
Impossible to tell without sufficient and accurate information about the
schema, the actual statement, an understanding of the distribution of data
in the affected table(s), and knowledge of any code that might be executed
as a side-effect of the statement. Perhaps the easiest way to figure out
what is happening is via the Profiler. As Tibor indicated in your last
thread - check the query plan - this should at least point to the reason.
In fact, there were quite a few suggestions - perhaps you should re-review
them?|||Hmm... Guess I'm just missing something. I went thru the Estimated Execution
Plan, but it didn't seem to tell me a whole lot (granted, I'm still trying
to figure out what it all means). I thought that I had posted everything
relevant to trying to figure out what is going on, but I guess I missed a
few things, so let me try a little further.
>accurate information about the schema (is this the table create script? Do
>you need the Index info?):
CREATE TABLE [dbo].[CustomerProduct] (
[iProductId] [int] NOT NULL ,
[iSiteId] [int] NOT NULL ,
[iOwnerId] [int] NOT NULL ,
[chLanguageCode] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[iContactId] [int] NULL ,
[chProductNumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL
,
[vchSerialNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL
,
[flQuantity] [OnyxFloat] NULL ,
[dtPurchaseDate] [datetime] NULL ,
[iTrackingId] [int] NULL ,
[iSourceId] [int] NULL ,
[iStatusId] [int] NULL ,
[iAccessCode] [int] NOT NULL ,
[vchUser1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser5] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser6] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser7] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser8] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser9] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser10] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[chInsertBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
[dtInsertDate] [datetime] NOT NULL ,
[chUpdateBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
[dtUpdateDate] [datetime] NOT NULL ,
[tiRecordStatus] [tinyint] NOT NULL ,
[dtModifiedDate] [smalldatetime] NULL
) ON [PRIMARY]

>the actual statement:
UPDATE CustomerProduct
SET chProductNumber='PAFGLPLK0B0BMS0EVDML'
WHERE chProductNumber='PAFGLPLK0B0BMS0RTDEN'

>an understanding of the distribution of data in the affected table(s):
13910 of 1283741 records need to be updated

>knowledge of any code that might be executed as a side-effect of the
>statement:
There are no triggers on this table, so what other side-effects migth there
be?
Again, I really do appreciate teh help of those with more expertise than I
in this area.
Willie
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%233vJrFB1FHA.560@.TK2MSFTNGP12.phx.gbl...
> Impossible to tell without sufficient and accurate information about the
> schema, the actual statement, an understanding of the distribution of data
> in the affected table(s), and knowledge of any code that might be executed
> as a side-effect of the statement. Perhaps the easiest way to figure out
> what is happening is via the Profiler. As Tibor indicated in your last
> thread - check the query plan - this should at least point to the reason.
> In fact, there were quite a few suggestions - perhaps you should re-review
> them?
>|||Willie Bodger wrote:
> Hmm... Guess I'm just missing something. I went thru the Estimated
> Execution Plan, but it didn't seem to tell me a whole lot (granted,
> I'm still trying to figure out what it all means). I thought that I
> had posted everything relevant to trying to figure out what is going
> on, but I guess I missed a few things, so let me try a little further.
> CREATE TABLE [dbo].[CustomerProduct] (
> [iProductId] [int] NOT NULL ,
> [iSiteId] [int] NOT NULL ,
> [iOwnerId] [int] NOT NULL ,
> [chLanguageCode] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
> NULL , [iContactId] [int] NULL ,
> [chProductNumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_A
S
> NOT NULL ,
> [vchSerialNumber] [varchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [flQuantity] [OnyxFloat] NULL ,
> [dtPurchaseDate] [datetime] NULL ,
> [iTrackingId] [int] NULL ,
> [iSourceId] [int] NULL ,
> [iStatusId] [int] NULL ,
> [iAccessCode] [int] NOT NULL ,
> [vchUser1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [vchUser2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [vchUser3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [vchUser4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [vchUser5] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [vchUser6] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [vchUser7] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [vchUser8] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [vchUser9] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [vchUser10] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL
> , [chInsertBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT
> NULL , [dtInsertDate] [datetime] NOT NULL ,
> [chUpdateBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL , [dtUpdateDate] [datetime] NOT NULL ,
> [tiRecordStatus] [tinyint] NOT NULL ,
> [dtModifiedDate] [smalldatetime] NULL
> ) ON [PRIMARY]
>
> UPDATE CustomerProduct
> SET chProductNumber='PAFGLPLK0B0BMS0EVDML'
> WHERE chProductNumber='PAFGLPLK0B0BMS0RTDEN'
>
> 13910 of 1283741 records need to be updated
>
> There are no triggers on this table, so what other side-effects migth
> there be?
> Again, I really do appreciate teh help of those with more expertise
> than I in this area.
I didn't see any execution plan. Did you mean to include it? You can
generate a text version with QA.
Also, what might make things slow here is TX logging. If your TX log
resides on a slow disk (or the same disk as the data) you'll see
performance degradation for all updating SQL.
Kind regards
robert