Clustered Index affecting DELETE
-
Hi,
I have a table with 31 Million rows in, keyed off an INT IDENTITY column. This column is also used in the clustered index. I have removed all triggers from the table and also all of its other indexes.
My issue is that to delete one row, ie DELETE <tablename> WHERE ID = 123, it takes longer than 2 minutes.
Is this something to do with my clustered index? Is there anyway for me to improve the performance of this DELETE?
Thanks for your help.
Graham
-
Is the delete getting blocked?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Does another large table reference it with a foreign key?
-
You say this column is used in the clustered index. Is it the only column in the clustered index or are there others? If so is the identity column the first value in the clustered index?
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
-
Is the ID the ONLY column in the clustered index?
you said "used" so I felt I had to ask.
-
Take a look at the execution plan to understand what's happening with the query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Hi guys,
thank you so much for your answers.
The reason was because as HowardW said, there was another large table that referenced this table and there was no index on the FK in that table.
So, does that mean all my FK columns should really be indexed?
To answer a few other questions: Yes, the ID column is the only column in the clustered index and it is the first column.
Thanks again
Graham
-
graham.allwood (2/6/2013)
So, does that mean all my FK columns should really be indexed?As a rule of thumb, yes, for the sort of performance issues you've experienced. But as with everything in SQL Server, it depends on what your typical workload looks like. If the delete was a one-off, you rarely join and filter on the foreign key column and it's insert heavy, maybe not...
-
does that mean all my FK columns should really be indexed?
No, just those on (potentially) large tables whose parent table will ever execute delete command.
On delete, SQL internally SELECTs child tables to check will FK constraint be violated. If there is no index on that FK column, it will do a full table scan of the child table, even if you delete just one row from the parent.
It has nothing to do with clustered index on the parent, or is it heap. It doesn/t even matter what is the size of parent table. Only thing that matters is the size of the child table and does it have an index on it's FK column.
_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths -
It's a good practice to check all your large tables if their FK columns are indexed. Especially if you know that parent table (PK table) will regularly execute deletes.
_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths -
There are 3 possible reasons I can think of for indexing a foreign key:
1. As has been said, to improve performance of deletes from the parent table
2. If you ever filter on the key in a query
3. It may result in a more efficient execution plan for some queries
Of course we are not just talking about single column indexes here. More likely is that a multi column index will be more useful, as long as the foreign key column is the first column in the index.
Use this to find all foreign keys that are not indexed
select cu.TABLE_SCHEMA [schema_name], cu.TABLE_NAME table_name, cu.COLUMN_NAME column_name, cu.CONSTRAINT_NAME constraint_name
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu
join sys.foreign_keys f on cu.TABLE_NAME = object_name(f.parent_object_id) and CONSTRAINT_NAME = f.name
where f.type = 'F'
and not exists (
select 1
from sys.indexes i
join sys.index_columns ic on i.object_id = ic.object_id and i.index_id = ic.index_id and ic.key_ordinal = 1
join sys.all_columns ac on ac.object_id = ic.object_id and ac.column_id = ic.column_id
where cu.TABLE_NAME = object_name(i.object_id)
and cu.COLUMN_NAME = ac.name
)
order by 1, 2, 3;
And this query shows the clustered indexes with the most scans. This may or may not be due to insufficient indexing (could be badly written queries, poor selectivity, very small indexes where a scan is more efficient, etc) but useful to know nonetheless.
select s.name [schema_name], o.name table_name, i.name index_name, ios.range_scan_count
from sys.dm_db_index_operational_stats (db_id(), null, null, null) ios
join sys.indexes i on i.object_id = ios.object_id and i.index_id = ios.index_id
join sys.objects o on i.object_id = o.object_id
join sys.schemas s on o.schema_id = s.schema_id
where o.type = 'U'
and i.index_id = 1
order by 4 desc;
-
Thanks for the advise.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply