SQL Server Performance

Table Locked while Bulk Insert in sql server 2005

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by bab_ganesh, Jul 31, 2008.

  1. bab_ganesh New Member

    I have a table with 2.5 million records. I will insert bulk of records in this table using BULK INSERT every day(more than 4 times). While Bulk insert I could not select the records from this table. Its taking too long time to execute the query in SQL Query Analyzer. In my web application I have a report showing from this table, it thrown an error "Timeout Expired". After insertion finished, I can select the records from the table and can see the report in my application without any problem.
    Please tell me why its happening like this. Is the table locked? (I think so.) If yes, how to know whether this table is locked or not?. Because I will show the friendly message to the user who are seeing that important report in my web application. How to avoid this locking?
    Additional Information
    • Table has 2.5 million rows with 30 columns and has a Primary Key field.
    • Table has a Indexed View with 25 columns
    • Indexed View has a Unique Clustered Index and has non-clustered index of remaining 24 column (to speed up my reports.[querying all the fields thats why])
    Please give your valuable suggestions.
    Thanks in advance.
  2. satya Moderator

  3. bab_ganesh New Member

    Hi Satya,
    Thanks for your reply. This article is saying about the performance of the BULK Insert.
    But the scenario is that my database gets hanged while bulk insert. I could not access any tables in this database. I dont know why its hanging. or any other issues for hanging the database.?
    Please suggest on this.
  4. satya Moderator

    What is the level of service pack on the SQL Server?
  5. bab_ganesh New Member

    SQL Server 2005 Enterprise Edition SP2
  6. satya Moderator

    THe performance is affected means the timeout is also one of the factor, so you need to dig out further by collecting system resource usage during thsi time.
  7. alent1234 New Member

    check your data and which column the clustered index is on. could be it's causing every single page to be written to instead of only a few pages
    we have a table where the CI used to be on the PK column which was just a generic identity column. used to cause a lot of problems because data that should have been physically together was scattered all over a 300 million row table. changed it like 6 months ago and it's a lot better now
  8. SQL2000DBA New Member

    Your table may be getting locked due to lock escalation. Actually SQL Server itself manages lock escalation from row level to table level. To resolve your issue look for any of the following option
    1). NOLOCK: Use NOLOCKS table hints wherever your are selecting data but using NOLOCKS hints means you are reading dirty data(uncommitted) which may or may not commit
    2). LOCKTIMEOUT:Ensure that your @@LOCK_TIMEOUT is set to -1 and also do the similar changes at application configuration level
    3). SNAPSHOT ISOLATION: Try using SNAPSHOT isolation level. SNAPSHOT provide you transaction-level read consistency
    4). If possible separate out BULK INSERT and SELECT Query load based on time which means perform BULK INSERT during nonpeak hours or maintenance window rather than during peak time.This approach will help you to offload the server from BULK INSERT operation during peak time and will enhance the performance of your reports queries.
  9. Did you specify any "Table Lock" hint. If you want to Row-level lock Set "Table Lock" to off. http://msdn.microsoft.com/en-us/library/ms180876(SQL.90).aspx
    Did you specify the "BATCHSIZE", if not by default, all the rows in a data file are imported as a single batch of unknown size in a single transaction, so there is a high probablity that you log file size is growing and you might have an I/O contention. Try setting the BATCHSIZE to a low number and try again.

Share This Page