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?
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 ?
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.
Use this sample and apply your own code and cut and paste what it returns
SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY PRIMARY KEY, Col2 char(1))
INSERT INTO myTable99(Col2)
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SET SHOWPLAN_TEXT ON
DELETE FROM myTable99 WHERE Col1 = 2
SET SHOWPLAN_TEXT OFF
SET NOCOUNT OFF
DROP TABLE myTable99