locked
Slow running query on heavily updating table RRS feed

  • Question

  • Hi,

    I am working on a live gps tracking project. The project is using SQL Server 2008 web edition. There are thousands of devices which are sending data packets in every 10 seconds or less depending on various settings to the server. On server there is a tcp server listener program which reads and parse data and insert it into a "Table-A". Table-A has a trigger which updates Table-B on each insertion (Table-B is used to show live updated status on website). Now Table-A has millions of records per day and high insertion rate per second.

    We also need to show the history reports and other reports using "Table-A". The problem is query on "Table-A" is very slow and affacts the insertion on "Table-A". Please help to overcome this problem.

    Thursday, February 5, 2015 10:20 AM

Answers

  • Consider turning on the READ_COMMITTED_SNAPSHOT database option to use row versioning instead of locking to provide read consistency in the default READ_COMMITTED isolation level.  This will blocking between inserts/selects and updates/selects on both tables.  Be aware that this option will increase storage requirements by 14 bytes per row plus increased tempdb usage for the row version store.  However, even with this overhead, you may get improved performance by mitigating blocking.

    Also, make sure you have indexes in place to support Table-A queries efficiently.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, February 5, 2015 12:43 PM
    Answerer
  • Since there are frequent inserts ,I believe you are selecting records using NOLOCK hint to avoid shared locks.

    Niting,

    I am pretty much sure in OP scenario NOLOCK will cause blunder. Please don't advise NOLOCK on forum where we have no or minimal information about OP database and infrastructure. Its not a good coding practice.

    Your comment to perform regular stats update also does not makes sense, are you sure stats is the issue I can sense some app design problem.

    Ganesh,

    I guess trigger on Table A is making things slow as it has to complete and I guess this is what taking time , I am not sure.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Thursday, February 5, 2015 1:50 PM
    Answerer

All replies

  • Since there are frequent inserts ,I believe you are selecting records using NOLOCK hint to avoid shared locks.

    Also I would suggest to check your query whether it is being blocked when data is retieved.

    Kindly check the following on this table -'Table-A'.

    1. Fragmentation details on index

    2. Table statistics

    Action: - 1. If Fragmentation is high kindly perform regular index rebuild.

                 2. Perform regular statistics update may be at every 8 hours.

     Above step will improve the query performance.

    And compare the performance.


    • Edited by Niting123 Thursday, February 5, 2015 10:29 AM
    Thursday, February 5, 2015 10:25 AM
  • Do you observe blocking? Have you checked that inserts do not cause page splits? Can you show us an execution plan of the query SELECT on Table-A?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, February 5, 2015 12:24 PM
  • Consider turning on the READ_COMMITTED_SNAPSHOT database option to use row versioning instead of locking to provide read consistency in the default READ_COMMITTED isolation level.  This will blocking between inserts/selects and updates/selects on both tables.  Be aware that this option will increase storage requirements by 14 bytes per row plus increased tempdb usage for the row version store.  However, even with this overhead, you may get improved performance by mitigating blocking.

    Also, make sure you have indexes in place to support Table-A queries efficiently.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, February 5, 2015 12:43 PM
    Answerer
  • Since there are frequent inserts ,I believe you are selecting records using NOLOCK hint to avoid shared locks.

    Niting,

    I am pretty much sure in OP scenario NOLOCK will cause blunder. Please don't advise NOLOCK on forum where we have no or minimal information about OP database and infrastructure. Its not a good coding practice.

    Your comment to perform regular stats update also does not makes sense, are you sure stats is the issue I can sense some app design problem.

    Ganesh,

    I guess trigger on Table A is making things slow as it has to complete and I guess this is what taking time , I am not sure.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Thursday, February 5, 2015 1:50 PM
    Answerer