none
Introduction of SQLDependencies has coincided in a complete block of a particular table on database RRS feed

  • Question

  • First off, I apologise in advance if this is in the wrong section.

     

    We have an ASP.Net Website (3.5 Framework) against a SQL Server 2008 R2 database.  We recently began working on the introduction of cache's into our persistance layer, and decided for now to go with the standard SQL Dependancy structure (I know this is sounding like an ASP.Net issue but bear with me).  We followed the MSDN articles for implementing the Systems, and as with all the best bugs, the new Cache's worked lovely throughout testing.  The correctly invalidate/drop from the ASP.Net Cache (System.Web.HttpRuntime.Cache) when their tables are updated, and much quicker than I had anticipated too.

    We were intent on now moving on to expand this to a more in-depth notification system when we started spotting problems on the Production Servers.  The website was going down, and we quickly found this to be because of blocking (note, not deadlocking).  I'm calling it blocking for now, but I'm not so sure...

    We eventually narrowed down the issue to being with one particular table.  This table is particularly involved in our dependencies/caching.  The table only contains 3000 records, and is only updated about once a minute.  For ease, and I don't know if this is in someway contributing, when I added dependencies for this table, I simply created them against the LinqToSQL command we use to populate the cache, which in some cases is this table joined to several others (INNER Joins reduce the resultset from 3000 records to about 2100).

    Ok, hopefully that's enough information on what we've changed recently.  Now the problem...

     

    If I do a very simple (in SSMS):

    UPDATE myTable

    SET myColumn1 = 'newValue'

    WHERE myID = 123

     

    My statement hangs... If I run this whilst there is no traffic from the web servers, Activity Monitor shows NO resource waits.  However, if I were to run this statement now whilst people are working, I'd obviously (and having tested this morning most definitely do) build up a very large queue of blocks, very quickly.

    Also, starting the simple statement above immediately jumps the SQL Server Processor Time from idle to ~45%, terminating the statement immediately drops the processor time.  I did this a couple times to make sure it wasn't a coincidence.

    The table in question has no complicated indices (simple primary key on the ID), has only 1 foreign key (simple ID to ID in a very small table), and no triggers apart from the auto-created trigger (unmodified) for the SQL Dependency.

    Could anyone tell me what's wrong with this table please?

     

    More info avail on request, and I appreciate your help in advance!

     

    • Moved by Pawel Marciniak Tuesday, December 7, 2010 7:38 PM (From:SQL Service Broker)
    Tuesday, December 7, 2010 10:18 AM

Answers

  • Issue Resolved:

     

    After countless amounts of digging, reverting code to previous deployments, disabling numerous services and servers, we stumbled across a fix...

     

    Detach Database

    Recycle SQL Server Engine

    Attach Database

     

    Don't ask me why, but that fixed it.  I imagine it was a bug, but now we've recycled everything, I doubt we have much to pass on to Microsoft.

     

    Hopefully if anyone else has the same issue they can get it reported before trying the above.

     

    Thanks
    • Marked as answer by Tommy Long Tuesday, December 7, 2010 10:21 PM
    Tuesday, December 7, 2010 10:20 PM