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...
>> 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
>|||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,
>> 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
>>
>|||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 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 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 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]
>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...
>> 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?
>|||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.
>> 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
> 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 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
> 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]
>> 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.
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|||>>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
A straightforward update on a relatively small table. There must be
something more to this than is obvious. Your last thread included a
**bunch** of indexes. Was this accurate? The information posted wasn't
easily understandable since it did not clearly identify the columns used in
the various indexes (don't particularly care about names or filegroups). Is
there no primary key? If so, then this starts to look like an issue with a
poorly designed schema. A table with lots of NULLable columns can also be
an indication of schema design problems.
> There are no triggers on this table, so what other side-effects migth
> there be?
Are you absolutely certain? The reason I ask is because of the
datetime/user columns in the table. It would seem that the creation and
last modified columns are populated via a trigger (unless you are purposely
trying to avoid changing the last updated columns) - they are not nullable
and have no defaults and columns of this nature are usually populated via
some automated mechanism (if only to avoid problems with programmers or
administrators forgetting to do these sorts of things).
Are there foreign keys in other tables that point to chProductNumber? Are
there indexed views that reference the table? You might want to investigate
the relationships this table has to any thing else in the database.|||Very good point, no I am not sure about the triggers/outside effects. I did
check that it does not seem to fire any triggers thru the Manage Triggers
command in QA. Here is the showplan_all for the query if that is helpful.
UPDATE CustomerProduct SET chProductNumber='PAFGLPLK0B0BMS0EVDML' WHERE
chProductNumber='PAFGLPLK0B0BMS0RTDEN' 1 1 0 NULL NULL 1 NULL 13926.935 NULL
NULL NULL 2.7688262 NULL NULL UPDATE 0 NULL
|--Sequence 1 2 1 Sequence Sequence NULL NULL 13926.935 0.0 5.5707738E-2
27 2.7688262 NULL NULL PLAN_ROW 0 1.0
|--Index
Update(OBJECT:([onyx].[dbo].[CustomerProduct].[customerproduct_Site_ProductNumber]),
SET:([iProductId1010]=[CustomerProduct].[iProductId],
[chProductNumber1009]=RaiseIfNull([CustomerProduct].[chProductNumber]),
[iSiteId1008]=[CustomerProduc 1 3 2 Index Update Update
OBJECT:([onyx].[dbo].[CustomerProduct].[customerproduct_Site_ProductNumber]),
SET:([iProductId1010]=[CustomerProduct].[iProductId],
[chProductNumber1009]=RaiseIfNull([CustomerProduct].[chProductNumber]),
[iSiteId1008]=[CustomerProduct].[iSiteId], [IdxBmk10 NULL 27853.869
1.0026711E-2 2.7853869E-2 27 1.3565593 NULL NULL PLAN_ROW 0 1.0
| |--Table Spool 1 4 3 Table Spool Eager Spool NULL NULL 27853.869
0.79318851 5.145763E-3 59 1.3186787 [CustomerProduct].[chProductNumber],
[Bmk1003], [CustomerProduct].[iProductId], [CustomerProduct].[iSiteId],
[Act1006] NULL PLAN_ROW 0 1.0
| |--Split 1 5 4 Split Split NULL [Act1006] 27853.869 0.0
0.19428073 59 1.01403 [CustomerProduct].[chProductNumber], [Bmk1003],
[CustomerProduct].[iProductId], [CustomerProduct].[iSiteId], [Act1006] NULL
PLAN_ROW 0 1.0
| |--Clustered Index
Update(OBJECT:([onyx].[dbo].[CustomerProduct].[PKNUCCustomerProduct]),
SET:([CustomerProduct].[chProductNumber]=RaiseIfNull(Convert([@.1])))) 1 6 5
Clustered Index Update Update
OBJECT:([onyx].[dbo].[CustomerProduct].[PKNUCCustomerProduct]),
SET:([CustomerProduct].[chProductNumber]=RaiseIfNull(Convert([@.1]))) NULL
13926.935 1.0126539E-2 1.3926934E-2 93 0.8197493
[CustomerProduct].[chProductNumber], [Bmk1003],
[CustomerProduct].[iProductId], [CustomerProduct].[iSiteId], [ConstExpr1005]
NULL PLAN_ROW 0 1.0
| |--Compute
Scalar(DEFINE:([ConstExpr1005]=Convert([@.1]))) 1 7 6 Compute Scalar Compute
Scalar DEFINE:([ConstExpr1005]=Convert([@.1])) [ConstExpr1005]=Convert([@.1])
13926.935 0.0 1.3926935E-3 75 0.79569584 [Bmk1000],
[CustomerProduct].[chProductNumber], [CustomerProduct].[iProductId],
[CustomerProduct].[iSiteId], [ConstExpr1005] NULL PLAN_ROW 0 1.0
| |--Table Spool 1 8 7 Table Spool Eager Spool
NULL NULL 13926.935 0.72851229 5.0140964E-3 55 0.79430312 [Bmk1000],
[CustomerProduct].[chProductNumber], [CustomerProduct].[iProductId],
[CustomerProduct].[iSiteId] NULL PLAN_ROW 0 1.0
| |--Top(ROWCOUNT est 0) 1 9 8 Top Top
NULL NULL 13926.935 0.0 1.3926935E-3 55 6.0776766E-2 [Bmk1000],
[CustomerProduct].[chProductNumber], [CustomerProduct].[iProductId],
[CustomerProduct].[iSiteId] NULL PLAN_ROW 0 1.0
| |--Index
Seek(OBJECT:([onyx].[dbo].[CustomerProduct].[NNXcustomerproduct_prodnumber]),
SEEK:([CustomerProduct].[chProductNumber]='PAFGLPLK0B0BMS0RTDEN') ORDERED
FORWARD) 1 10 9 Index Seek Index Seek
OBJECT:([onyx].[dbo].[CustomerProduct].[NNXcustomerproduct_prodnumber]),
SEEK:([CustomerProduct].[chProductNumber]='PAFGLPLK0B0BMS0RTDEN') ORDERED
FORWARD [Bmk1000], [CustomerProduct].[chProductNumber],
[CustomerProduct].[iProductId], [CustomerProduct].[iSiteId] 13926.935
4.3944165E-2 1.5439909E-2 55 5.9384074E-2 [Bmk1000],
[CustomerProduct].[chProductNumber], [CustomerProduct].[iProductId],
[CustomerProduct].[iSiteId] NULL PLAN_ROW 0 1.0
|--Index
Update(OBJECT:([onyx].[dbo].[CustomerProduct].[NNXcustomerproduct_prodnumber]),
SET:([iSiteId1014]=[CustomerProduct].[iSiteId],
[iProductId1013]=[CustomerProduct].[iProductId],
[chProductNumber1012]=RaiseIfNull([CustomerProduct].[chProductN 1 14 2 Index
Update Update
OBJECT:([onyx].[dbo].[CustomerProduct].[NNXcustomerproduct_prodnumber]),
SET:([iSiteId1014]=[CustomerProduct].[iSiteId],
[iProductId1013]=[CustomerProduct].[iProductId],
[chProductNumber1012]=RaiseIfNull([CustomerProduct].[chProductNumber]),
[IdxBmk1011]=R NULL 27853.869 1.0026711E-2 2.7853869E-2 27 1.3565593 NULL
NULL PLAN_ROW 0 1.0
|--Table Spool 1 4 14 Table Spool Eager Spool NULL NULL
27853.869 0.79318851 5.145763E-3 59 1.3186787
[CustomerProduct].[chProductNumber], [Bmk1003],
[CustomerProduct].[iProductId], [CustomerProduct].[iSiteId], [Act1006] NULL
PLAN_ROW 0 1.0
"Scott Morris" <bogus@.bogus.com> wrote in message
news:esnXxXL1FHA.1264@.tk2msftngp13.phx.gbl...
>>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
> A straightforward update on a relatively small table. There must be
> something more to this than is obvious. Your last thread included a
> **bunch** of indexes. Was this accurate? The information posted wasn't
> easily understandable since it did not clearly identify the columns used
> in the various indexes (don't particularly care about names or
> filegroups). Is there no primary key? If so, then this starts to look
> like an issue with a poorly designed schema. A table with lots of
> NULLable columns can also be an indication of schema design problems.
>> There are no triggers on this table, so what other side-effects migth
>> there be?
> Are you absolutely certain? The reason I ask is because of the
> datetime/user columns in the table. It would seem that the creation and
> last modified columns are populated via a trigger (unless you are
> purposely trying to avoid changing the last updated columns) - they are
> not nullable and have no defaults and columns of this nature are usually
> populated via some automated mechanism (if only to avoid problems with
> programmers or administrators forgetting to do these sorts of things).
> Are there foreign keys in other tables that point to chProductNumber? Are
> there indexed views that reference the table? You might want to
> investigate the relationships this table has to any thing else in the
> database.
>
>|||Difficult to read with all the formatting / re-formatting done between your
posting and my reading. Plus, I am no expert in reading the text plan
output. Perhaps others can decipher it. If you use the graphical plan, you
should see any "extra" statements executed in triggers.
I'm not aware of any "manager triggers" command in QA. Are you using sql
server 2000 or another version?|||My bad, it's in the EM under the right click menu. Yeah, I looked at the
graphical version and it was pretty cut and dried, but there was one level
below the main that took like 48% of the time. I had though about posting an
attachment in NotePad. I'm thinking it is just a culmination of a few things
that is causing the slowness, so for now I am just going to run the query at
a very slow time and watch to like a hawk. Thanks for the help.
Willie
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23SQ6z0N1FHA.2616@.tk2msftngp13.phx.gbl...
> Difficult to read with all the formatting / re-formatting done between
> your posting and my reading. Plus, I am no expert in reading the text
> plan output. Perhaps others can decipher it. If you use the graphical
> plan, you should see any "extra" statements executed in triggers.
> I'm not aware of any "manager triggers" command in QA. Are you using sql
> server 2000 or another version?
>