SQL Server Performance Forum – Threads Archive
Table operations too slow.
My table already had few millions of records.Few thousand records have got inserted into this table during performance testing.
Now, the table is not able to do any of the operations like normal SELECT / DELETE.
Any idea what can be the reason and how to get rid of this?
Thanks.
Did you get any error?
Show us how you are adding table to table Madhivanan Failing to plan is Planning to fail
I am not getting any error for this.
The records are successfully getting inserted into the table through simple INSERT statement. But further operations like delete, select are running in an infinite loop. I can give you an example as – I started the DELETE operation (to delete these test records) yesterday in the evening. The query was still running when I checked in the morning .. It was around 13+ hours. Query was as simple as – DELETE from tableA where InsertBy = "myName".
Run the profiler and see if there are any blocks How many rows do you expect to be deleted? If there are too many rows to be deleted, you can use batch-wise-deletion
select @c=10000
While @c>0
Begin
set rowcount @c
DELETE from tableA where InsertBy = ‘myName’
select @[email protected]@rowcount
set rowcount 0
End
Madhivanan Failing to plan is Planning to fail
Is this a parent table with cascade delete to other tables? Does the table have triggers that insert/update/delete on other tables? Does the table have a primary key? Does the table have a clustered index on multiple columns? Does the table have non-clustered indexes? Is there an indexed view covering this table?
quote:Originally posted by Madhivanan
Run the profiler and see if there are any blocks
How many rows do you expect to be deleted?
If there are too many rows to be deleted, you can use batch-wise-deletion
select @c=10000
While @c>0
Begin
set rowcount @c
DELETE from tableA where InsertBy = ‘myName’
select @[email protected]@rowcount
set rowcount 0
End
Madhivanan Failing to plan is Planning to fail
I am not authorized to use the profiler here.select @c=10000
While @c>0
Begin
set rowcount @c
DELETE from tableA where InsertBy = ‘myName’
select @[email protected]@rowcount
set rowcount 0
End
Madhivanan Failing to plan is Planning to fail
I also tried the deletion in blocks but didn’t work out. I am expecting around 30000 records to be deleted from this table.
Another observation is – Normally when we write " select * from tableA " and cancel the query in between; we atleast get few records from the table which have got selected in that particular amount of time.
This table returns no results except the statement – "Query cancelled by User
[Microsoft][ODBC SQL Server Driver]Operation canceled" … even if canceled after 10-15 minutes.
There are some other tables in the system which have around same no. of records as that of tableA; but those are working fine.
Thanks.
Is this a parent table with cascade delete to other tables?
– No. But similar kind of thing is handled through triggers. Does the table have triggers that insert/update/delete on other tables?
– Yes. It has got an extended table which gets affected on insert, update and delete operation. Does the table have a primary key?
– Yes. Does the table have a clustered index on multiple columns?
– only one – on the primary key column. Does the table have non-clustered indexes?
– Yes, 12. Is there an indexed view covering this table?
– There exists a view but it is not indexed. ****
I tried to see the execution plan for the query; but no results found.
The execution plan does not show you any actions on other tables, be it from casacdes or from triggers. That’s a clear limitation of execution plans, but hey – they have to draw the line somewhere. What type of data is in your primary key? If it is a steadily increasing value, like an identity column, then that is fine. If it is a more ‘natural’ primary key, then this is not the ideal candidate for a clustered index – primarily because the index is more likely to get fragmented after insertions, updates and deletions. In any case, all your non-clustered indexes are based on the clustered index, and so they will also get fragmented.
Ohh .. okay.
Its just few months I have started with SQL server; so trying each and every that comes in mind; so thought of trying execution plan as well. Yes, the primary key is the ID which sequentially gets incremented when we add a new record in table.
Do you have a maintenance job scheduled to optimize the indexes?
quote:Originally posted by Adriaan
Do you have a maintenance job scheduled to optimize the indexes?
Not very sure; but couldn’t see any.
Hi All, The problem got resolved. The DBA could figure it out.
It occured because of the locks on tableA. Some jobs were running in the background and due to large table size it took too long to execute which caused the overlap between my queries on the table. Thanks for your inputs. Regards.
]]>