Hello,
in our project i have to drop an existing sql server database and
immediately create a new (and empty) one with exactly the same name. MSDN
says that in case of dropping a database also the physical files on the
harddrive are deleted but this is not the case, as sql server complains that
there's already a file with that name on disk.
Is there a way to delete also the disk files of a database using T-SQL or do
i have to choose a different approach?
Thanks in advance
ChristianIn 6.5 and older, the database files were not deleted when you execute DROP
DATABASE. As of 7.0,
they are. If you are seeing something different, you have either hit a bug i
n the product (unlikely)
or may have some problems in your scripts.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"chris" <chris@.discussions.microsoft.com> wrote in message
news:68AFCD1C-85B6-4661-9332-3F50830BF386@.microsoft.com...
> Hello,
> in our project i have to drop an existing sql server database and
> immediately create a new (and empty) one with exactly the same name. MSDN
> says that in case of dropping a database also the physical files on the
> harddrive are deleted but this is not the case, as sql server complains th
at
> there's already a file with that name on disk.
> Is there a way to delete also the disk files of a database using T-SQL or
do
> i have to choose a different approach?
> Thanks in advance
> Christian|||Check the default file location. Could it be that you previously created a
database with the same name in this location, but later abandoned it and
started creating databases with files in another location while the default
file location was left unchanged?
The easiest way would be to check your default location and check your file
system.
ML
http://milambda.blogspot.com/|||Thank you very much for your replies.
I found that before dropping the database it was set OFFLINE to disconnect
users and in this case the files remain on disk. I set it to SINGLE_USER
instead and then it worked.
Chris
"chris" wrote:
> Hello,
> in our project i have to drop an existing sql server database and
> immediately create a new (and empty) one with exactly the same name. MSDN
> says that in case of dropping a database also the physical files on the
> harddrive are deleted but this is not the case, as sql server complains th
at
> there's already a file with that name on disk.
> Is there a way to delete also the disk files of a database using T-SQL or
do
> i have to choose a different approach?
> Thanks in advance
> Christian|||> I found that before dropping the database it was set OFFLINE to disconnect
> users and in this case the files remain on disk.
That was news for me. Thanks for sharing the information, Chris.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"chris" <chris@.discussions.microsoft.com> wrote in message
news:B5951FCB-2434-4241-969C-02BF0E7816A3@.microsoft.com...
> Thank you very much for your replies.
> I found that before dropping the database it was set OFFLINE to disconnect
> users and in this case the files remain on disk. I set it to SINGLE_USER
> instead and then it worked.
> Chris
> "chris" wrote:
>
Showing posts with label existing. Show all posts
Showing posts with label existing. Show all posts
Sunday, March 11, 2012
Friday, March 9, 2012
Drop database on a different SQL Server.
Hi,
I am new to SQL Server 2005.Till now, I have been using a SP to execute DROP DATABASE command to drop databases on my existing database server.
but now i want to delete a database which is on a different SQL Server 2005 instance on a different machine. but i am not sure how to do this.
Can anyone please help me on this?
Any help would be appreciated.
Thanx in advance.
Kawal
If you have the remote server credential connect your SQL Mang. Studio with the target server & execute the same script.|||
Thanks for the reply!
i can do that but my problem is different.
I am deleting database from my application which calls a VB component to delete the database. SP is called from this VB component and this SP resides on Admin DB which is on , say server1. database that has to be deleted is on a different server running a different SQL Server instance.
Is this thing possible in any way?
Waiting for your reply.
|||As far as I know, in this case, you have to recreate the sproc on target server.|||and can you please tell how to do that? as i tols earlier, i am new to SQL Server 
Drop Database failed as already in use - how do I close existing connections as per Management S
I have some code to delete a database. This allows a user to enum the databases and select one to delete. It the determines the filename, so it can remove the physical files, drops the database and deletes the files. However, it frequently fails saying the database is currently in use....
I noticed the same bahaviour when deleting a database via Management Studio, however checking the box to close active connections does the trick and the db is successfully deleted.
Question: how do I close the active connection in smo ?
Thanks, Nick
Hi,
http://www.sqlserver2005.de/sqlserver2005/MyBlog/tabid/56/EntryID/9/Default.aspx
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Thanks Jens this is really helpfulDrop Database failed as already in use - how do I close existing connections as per Manageme
I have some code to delete a database. This allows a user to enum the databases and select one to delete. It the determines the filename, so it can remove the physical files, drops the database and deletes the files. However, it frequently fails saying the database is currently in use....
I noticed the same bahaviour when deleting a database via Management Studio, however checking the box to close active connections does the trick and the db is successfully deleted.
Question: how do I close the active connection in smo ?
Thanks, Nick
Hi,
http://www.sqlserver2005.de/sqlserver2005/MyBlog/tabid/56/EntryID/9/Default.aspx
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Thanks Jens this is really helpfulDrop Database failed as already in use - how do I close existing connections as per Manageme
I have some code to delete a database. This allows a user to enum the databases and select one to delete. It the determines the filename, so it can remove the physical files, drops the database and deletes the files. However, it frequently fails saying the database is currently in use....
I noticed the same bahaviour when deleting a database via Management Studio, however checking the box to close active connections does the trick and the db is successfully deleted.
Question: how do I close the active connection in smo ?
Thanks, Nick
Hi,
http://www.sqlserver2005.de/sqlserver2005/MyBlog/tabid/56/EntryID/9/Default.aspx
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Thanks Jens this is really helpfuldrop column fails
Hi all,
Want to drop a column of an existing table with
alter table TE20_AREA
drop column E20IN_NODE_NUMBER
But get:
The object 'DF__TE20_AREA__E20IN__26509D48' is dependent on column
'E20IN_NODE_NUMBER'.
What is the object DF... checked for indexes, constraints, relations
haven't found somthing with this name '
BTW, deleting the row in the gui works like a charme '? (Does not
help because db modification needs to be in a batch job :-( )
Any help is highly appreciated.
TIA
DanHi Dan,
An object name starting with DF_ and ending in a number is a automatically
generated name for a default constraint. You will see these names when you
create a column with a default in a table in Enterprise Manager, or with for
example CREATE TABLE some_table(some_column INT DEFAULT 0). You can
explicitly name default constraints with:
CREATE TABLE some_table(some_column INT CONSTRAINT
DF_some_table__some_column_is_zero DEFAULT 0).
You can get rid of Defaults with automatically named constraints in a script
with the following bit of code. Just replace the <table name> and <column
names> with your table and column(s):
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
--
Jacco Schalkwijk
SQL Server MVP
"Dan Ackermann" <dummy@.intos.ch> wrote in message
news:%2313rW$4$DHA.692@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Want to drop a column of an existing table with
> alter table TE20_AREA
> drop column E20IN_NODE_NUMBER
> But get:
> The object 'DF__TE20_AREA__E20IN__26509D48' is dependent on column
> 'E20IN_NODE_NUMBER'.
> What is the object DF... checked for indexes, constraints, relations
> haven't found somthing with this name '
> BTW, deleting the row in the gui works like a charme '? (Does not
> help because db modification needs to be in a batch job :-( )
> Any help is highly appreciated.
> TIA
> Dan
>
Want to drop a column of an existing table with
alter table TE20_AREA
drop column E20IN_NODE_NUMBER
But get:
The object 'DF__TE20_AREA__E20IN__26509D48' is dependent on column
'E20IN_NODE_NUMBER'.
What is the object DF... checked for indexes, constraints, relations
haven't found somthing with this name '
BTW, deleting the row in the gui works like a charme '? (Does not
help because db modification needs to be in a batch job :-( )
Any help is highly appreciated.
TIA
DanHi Dan,
An object name starting with DF_ and ending in a number is a automatically
generated name for a default constraint. You will see these names when you
create a column with a default in a table in Enterprise Manager, or with for
example CREATE TABLE some_table(some_column INT DEFAULT 0). You can
explicitly name default constraints with:
CREATE TABLE some_table(some_column INT CONSTRAINT
DF_some_table__some_column_is_zero DEFAULT 0).
You can get rid of Defaults with automatically named constraints in a script
with the following bit of code. Just replace the <table name> and <column
names> with your table and column(s):
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
--
Jacco Schalkwijk
SQL Server MVP
"Dan Ackermann" <dummy@.intos.ch> wrote in message
news:%2313rW$4$DHA.692@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Want to drop a column of an existing table with
> alter table TE20_AREA
> drop column E20IN_NODE_NUMBER
> But get:
> The object 'DF__TE20_AREA__E20IN__26509D48' is dependent on column
> 'E20IN_NODE_NUMBER'.
> What is the object DF... checked for indexes, constraints, relations
> haven't found somthing with this name '
> BTW, deleting the row in the gui works like a charme '? (Does not
> help because db modification needs to be in a batch job :-( )
> Any help is highly appreciated.
> TIA
> Dan
>
drop column fails
Hi all,
Want to drop a column of an existing table with
alter table TE20_AREA
drop column E20IN_NODE_NUMBER
But get:
The object 'DF__TE20_AREA__E20IN__26509D48' is dependent on column
'E20IN_NODE_NUMBER'.
What is the object DF... checked for indexes, constraints, relations
haven't found somthing with this name '
BTW, deleting the row in the gui works like a charme '? (Does not
help because db modification needs to be in a batch job :-( )
Any help is highly appreciated.
TIA
DanHi Dan,
An object name starting with DF_ and ending in a number is a automatically
generated name for a default constraint. You will see these names when you
create a column with a default in a table in Enterprise Manager, or with for
example CREATE TABLE some_table(some_column INT DEFAULT 0). You can
explicitly name default constraints with:
CREATE TABLE some_table(some_column INT CONSTRAINT
DF_some_table__some_column_is_zero DEFAULT 0).
You can get rid of Defaults with automatically named constraints in a script
with the following bit of code. Just replace the <table name> and <column
names> with your table and column(s):
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Dan Ackermann" <dummy@.intos.ch> wrote in message
news:%2313rW$4$DHA.692@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Want to drop a column of an existing table with
> alter table TE20_AREA
> drop column E20IN_NODE_NUMBER
> But get:
> The object 'DF__TE20_AREA__E20IN__26509D48' is dependent on column
> 'E20IN_NODE_NUMBER'.
> What is the object DF... checked for indexes, constraints, relations
> haven't found somthing with this name '
> BTW, deleting the row in the gui works like a charme '? (Does not
> help because db modification needs to be in a batch job :-( )
> Any help is highly appreciated.
> TIA
> Dan
>
Want to drop a column of an existing table with
alter table TE20_AREA
drop column E20IN_NODE_NUMBER
But get:
The object 'DF__TE20_AREA__E20IN__26509D48' is dependent on column
'E20IN_NODE_NUMBER'.
What is the object DF... checked for indexes, constraints, relations
haven't found somthing with this name '
BTW, deleting the row in the gui works like a charme '? (Does not
help because db modification needs to be in a batch job :-( )
Any help is highly appreciated.
TIA
DanHi Dan,
An object name starting with DF_ and ending in a number is a automatically
generated name for a default constraint. You will see these names when you
create a column with a default in a table in Enterprise Manager, or with for
example CREATE TABLE some_table(some_column INT DEFAULT 0). You can
explicitly name default constraints with:
CREATE TABLE some_table(some_column INT CONSTRAINT
DF_some_table__some_column_is_zero DEFAULT 0).
You can get rid of Defaults with automatically named constraints in a script
with the following bit of code. Just replace the <table name> and <column
names> with your table and column(s):
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Dan Ackermann" <dummy@.intos.ch> wrote in message
news:%2313rW$4$DHA.692@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Want to drop a column of an existing table with
> alter table TE20_AREA
> drop column E20IN_NODE_NUMBER
> But get:
> The object 'DF__TE20_AREA__E20IN__26509D48' is dependent on column
> 'E20IN_NODE_NUMBER'.
> What is the object DF... checked for indexes, constraints, relations
> haven't found somthing with this name '
> BTW, deleting the row in the gui works like a charme '? (Does not
> help because db modification needs to be in a batch job :-( )
> Any help is highly appreciated.
> TIA
> Dan
>
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!
Willie
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
>
|||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 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...
> 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_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]
> 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
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,
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 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...
> 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_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]
> 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
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
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
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?
>
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?
>
Subscribe to:
Posts (Atom)