deleting rows

I have a table with approx 5 million rows and 36 columns. It takes approx
4 minutes to delete 1 row. The table has 3 indexes in addition to it's primary key and has twelve foreign key constraints. We are still using sequel 7.
There is a backup run every night as part of the nightly maintenence that
reorg/reindexes and checks the database integrity. Any thoughts?

Thankswell I depends what your deleting on. Make sure that you're using an indexed column, and check the execution plan to make sure that it's doing a table seek and not a table scan.

What are you stats for this table set to ?|||I am using a simple delete such as delete from tablename where transsk = 1002
with transsk being the primary key. This has only become a problem once the table grew over a mil rows.|||what kind of index, clustered or non-clustered ?

and did you set a fill factor on the table ?

setting your index properly should bring down your delete to a few seconds.
I've got tables that are 6mil+ rows and a delete takes < 15 secs.|||The primary key is non-clustered with a fill factor of 90.
There are also three indexes. Two non-clustered and one clustered, all three with a fill factor of 90. It's also odd to me that inserting rows is not a problem.|||Inserting a row shouldn't be much of a problem as you don't have to seek to insert a row. If you've got a clustered index, there is a little bit of overhead as the data needs to be arranged logically. IE, it may have to shuffle other rows around to properly fit in the one you are inserting. With a non-clustered index, it can just append the row to the logical group and add an entry into the tree.

What you may want to try for benchmarking purposes is to remove the clustered index and see if you get a performance increase when inserting or deleting. I don't think you'll get much, but it's worth a shot...

have you taken a look at the execution plan for a simple delete like the one you posted ?|||Thanks, I will give that a try by removing the clustered index.
Do you have tables with as many foreign key constraints? I didn't know if 12 was a unusually large amount.

Also, I guess I'm an idiot, what do you mean by execution plan?|||If you open query analyzer, there is a button at the top that will show you the proposed execution plan that SQL server will use when you run that SQL. The execution plan is created based on statistics.

Also, I think 12 FK constraints on one table is *a lot*. You should really only have 1 to 3. That's likely the reason it's taking so long to delete anything, it's got many constraints to check before deleting a row.

Cheers,
-Kilka|||Use this sample and apply your own code and cut and paste what it returns

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY PRIMARY KEY, Col2 char(1))
GO

INSERT INTO myTable99(Col2)
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C'
GO

SET SHOWPLAN_TEXT ON
GO

DELETE FROM myTable99 WHERE Col1 = 2
GO

SET SHOWPLAN_TEXT OFF
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Deleting Indexes from a table

I am using SQL-DMO to loop through the Index collection and executing the
Remove method to delete indexses in a table. I am getting an error message
that says I can't delete an Index because it was created using 'PRIMARY KEY'
.
I need to be able to remove ALL Indexes from a table. Can I do this with
SQL-DMO?
Or how can I use SQL-DMO tor remove the PRIMARY KEY setting?
adv-thanks-anceJD,
You can't remove an index associated with a PRIMARY KEY constraint without
removing the PK constraint itself. So I think you'll either have to drop
the constraint and recreated it or if you just want to rebuild the index use
DBCC DBREINDEX instead.
HTH
Jerry
"JD" <JD@.discussions.microsoft.com> wrote in message
news:FDDB7221-2A65-4F8C-BB6A-1B9B931EC9B5@.microsoft.com...
>I am using SQL-DMO to loop through the Index collection and executing the
> Remove method to delete indexses in a table. I am getting an error
> message
> that says I can't delete an Index because it was created using 'PRIMARY
> KEY'.
> I need to be able to remove ALL Indexes from a table. Can I do this with
> SQL-DMO?
> Or how can I use SQL-DMO tor remove the PRIMARY KEY setting?
> adv-thanks-ance|||You will have to remove first all foreign key constraints, then all primary
key and unique constraints and then you will be able to drop the rest of the
indexes.
AMB
"JD" wrote:

> I am using SQL-DMO to loop through the Index collection and executing the
> Remove method to delete indexses in a table. I am getting an error messag
e
> that says I can't delete an Index because it was created using 'PRIMARY KE
Y'.
> I need to be able to remove ALL Indexes from a table. Can I do this with
> SQL-DMO?
> Or how can I use SQL-DMO tor remove the PRIMARY KEY setting?
> adv-thanks-ance|||Thanks,
Can you remove the PK constraint using SQL-DMO? Or will I have to write
a procedure that loops through all of the tables and remove the PK Constrain
t?
"Jerry Spivey" wrote:

> JD,
> You can't remove an index associated with a PRIMARY KEY constraint without
> removing the PK constraint itself. So I think you'll either have to drop
> the constraint and recreated it or if you just want to rebuild the index u
se
> DBCC DBREINDEX instead.
> HTH
> Jerry
> "JD" <JD@.discussions.microsoft.com> wrote in message
> news:FDDB7221-2A65-4F8C-BB6A-1B9B931EC9B5@.microsoft.com...
>
>|||JD,
I haven't actually used it before but there is a KEY object in SQL-DMO that
I believe will do what you're asking. See 'Key Object' in the SQL Server
Books Online. Also, be sure to read Alejandro's feedback as well if you
have RI established with FOREIGN KEYs to your PRIMARY KEYS.
HTH
Jerry
"JD" <JD@.discussions.microsoft.com> wrote in message
news:55256CBF-6AE1-437F-B3FB-087C67670AB7@.microsoft.com...
> Thanks,
> Can you remove the PK constraint using SQL-DMO? Or will I have to write
> a procedure that loops through all of the tables and remove the PK
> Constraint?
> "Jerry Spivey" wrote:
>|||Thanks for the help!
"Jerry Spivey" wrote:

> JD,
> I haven't actually used it before but there is a KEY object in SQL-DMO tha
t
> I believe will do what you're asking. See 'Key Object' in the SQL Server
> Books Online. Also, be sure to read Alejandro's feedback as well if you
> have RI established with FOREIGN KEYs to your PRIMARY KEYS.
> HTH
> Jerry
> "JD" <JD@.discussions.microsoft.com> wrote in message
> news:55256CBF-6AE1-437F-B3FB-087C67670AB7@.microsoft.com...
>
>|||Thanks for your help!
"Alejandro Mesa" wrote:
> You will have to remove first all foreign key constraints, then all primar
y
> key and unique constraints and then you will be able to drop the rest of t
he
> indexes.
>
> AMB
> "JD" wrote:
>sql

Deleting Indexes

Is there a way to delete the indexes on all the tables for a specific
database in SQL Server using Query Analyzer without knowing the exact name o
f
the Index? I am able to do this using SQLDMO, but I need to know if there is
a way to do this using Query Analyzer.
ThanksYou might want to look at the sysindexes table
Select * from sysindexes
I would create a cursor to loop through the sysindexes table and issue the
command
Drop Index TableName.IndexName
Both TableName and IndexName can be found in Sysindexes table
HTH
Ed
"JD" wrote:

> Is there a way to delete the indexes on all the tables for a specific
> database in SQL Server using Query Analyzer without knowing the exact name
of
> the Index? I am able to do this using SQLDMO, but I need to know if there
is
> a way to do this using Query Analyzer.
> Thanks|||This will not take care of indexes implicitly created by, say PK and Unique
constraints, but it may be a start:
select 'DROP INDEX '+object_name(id)+'.'+object_name(object_id(name))
from sysindexes
WHERE objectproperty(object_id(name), 'IsMsShipped') = 0
AND objectproperty(id, 'IsMsShipped') = 0
AND keycnt = 0
"JD" <JD@.discussions.microsoft.com> wrote in message
news:C554DC0B-22B6-4763-8A10-052843C5D47C@.microsoft.com...
> Is there a way to delete the indexes on all the tables for a specific
> database in SQL Server using Query Analyzer without knowing the exact name
> of
> the Index? I am able to do this using SQLDMO, but I need to know if there
> is
> a way to do this using Query Analyzer.
> Thanks|||hi,
if you know the name of the table using sp_help <table> you obtain the name
of its indexes.
regards,
"JD" wrote:

> Is there a way to delete the indexes on all the tables for a specific
> database in SQL Server using Query Analyzer without knowing the exact name
of
> the Index? I am able to do this using SQLDMO, but I need to know if there
is
> a way to do this using Query Analyzer.
> Thanks