High volumn query notifications

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Does anyone have any experience using query notifications with a high volume of data changes and a large number of subscribers? Anyone know if it scales well?

    What's happened is that a outsource company's developer decided (after consultation with his architect) to use query notifications for a project involving a data feed. Unfortunately this is the first I've heard of any of this, so I have very little details.

    The developer has 'stress' tested at the volume that's expected for the pilot project, that is 5 users and 5 inserts/sec with the notification on (I assume a subset of) the inserts. He says the desktop he was testing on can handle that load.

    If the pilot is a success, we're expecting the usage to increase to around 200 users and 400-500 inserts/sec. The developer hasn't tested at those levels. He said he would, but I doubt he's going to get to it before the pilot goes live (monday)

    Should I be panicking at this point?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Matt Miller (4)

    SSC Guru

    Points: 124150

    Is the point of the notification to force refreshes of the data? That's usually what this is for (meaning - notify that the contents of your cache is no good, so go get ther data again).

    I don't have any direct experience, but one of my colleagues played with it for a while. Setting the threshold to low means that your cache is almost always invalid, so you'd be better off assuming you don't have anything cached (meaning - just invalidate the cache without checking it and requery). Setting it too high, and well - you're looking at old data....

    If that stress test DOES in fact happen, make sure it's not all done on a single box, meaning - make sure that the notifications are done over the wire, and not between a local IIS box (assuming Web application of course) and a local SQL Server. The latency there is substantially different....

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Gail Shaw

    SSC Guru

    Points: 1004424

    I don't know exactly what the notifications are for. It's for a trading system, so I will guess that its to tell the front end that a price/rate has changed.

    Not IIS. It's a client-server app (as far as I know. I'm so far out of the loop, it's not even funny)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Orlando Colamatteo

    SSC Guru

    Points: 182207

    I know this is a very old thread but I am being confronted with a similar proposal, albeit nowhere near the volumes initially, where QNs and Service Broker would be used to notify n-web servers that data has changed meaning a client screen should be pushed new data. Do you remember the outcome of the project or have you worked with QNs and Service Broker since?

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply