SQL Server Performance

Table operations too slow.

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by GRKool, Jun 20, 2007.

  1. GRKool New Member

    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.
  2. Madhivanan Moderator

    Did you get any error?
    Show us how you are adding table to table

    Madhivanan

    Failing to plan is Planning to fail
  3. GRKool New Member

    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".

  4. Madhivanan Moderator

    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 @c=@@rowcount
    set rowcount 0
    End


    Madhivanan

    Failing to plan is Planning to fail
  5. Adriaan New Member

    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?
  6. GRKool New Member

    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 @c=@@rowcount
    set rowcount 0
    End


    Madhivanan

    Failing to plan is Planning to fail


    I am not authorized to use the profiler here.
    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.

  7. GRKool New Member

    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.
  8. Adriaan New Member

    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.
  9. GRKool New Member

    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.
  10. Adriaan New Member

    Do you have a maintenance job scheduled to optimize the indexes?
  11. GRKool New Member

    quote:Originally posted by Adriaan

    Do you have a maintenance job scheduled to optimize the indexes?

    Not very sure; but couldn't see any.
  12. GRKool New Member

    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.

Share This Page