Query Notification in MS SQL 2012

  • As I just found out, there is a difference in Query Notification between MS SQL 2008 R2 and MS SQL 2012. In 2008 R2, QN fired when anything on the selected row was changed. Enample:

    SELECT PrimaryKey FROM dbo.Table WHERE PrimaryKey = @Value

    QN for this command was fired when anything, not just primary key was changed.

    It's different in MS SQL 2012, now the QN fires just when the selected columns change. Now, is there a way to make it work as it did in 2008 R2? Our caching engine depends on it. Just a few guesses what could help:

    1) Select a column that always changes when anything on the row changes

    SELECT <RowVersion> FROM dbo.Table WHERE PrimaryKey = @Value

    2) Some hint perhaps?

    SELECT PrimaryKey FROM dbo.Table WHERE PrimaryKey = @Value WITH <hint that makes QN behave as it did in 2008 R2>

    3) or is there something that can be SET?

    SET <QUERY_NOTIFICATION_BEHAVIOR> = 2008

    I'd hate to set the database compatibility level back to 2008 - and even that is not guaranteed to help.

  • I believe that the recommendation on this is to change your query to watch the column that is actually changing. See here...

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • I am aware of that article and even acknowledge that the optimalization in QN is good - it's just that the old way was more useful for a caching engine.

    Ours is watching tens of tables with tens of columns - and since you cannot user SELECT * in QN, it would have to be modified every time a column is added/removed. Also the selected data would be many many more times bigger. Often, we are watching the entire table by SELECT PrimaryKey FROM dbo.Table - and if anything changes, the table on client is removed from cache.

  • Jakub.Janda (5/28/2012)


    I am aware of that article and even acknowledge that the optimalization in QN is good - it's just that the old way was more useful for a caching engine.

    Ours is watching tens of tables with tens of columns - and since you cannot user SELECT * in QN, it would have to be modified every time a column is added/removed. Also the selected data would be many many more times bigger. Often, we are watching the entire table by SELECT PrimaryKey FROM dbo.Table - and if anything changes, the table on client is removed from cache.

    I'm not sure which of your alternatives would be best then. My instinct would be to start with the RowVersion one, but that's just a SWAG.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • RBarryYoung (5/28/2012)


    I'm not sure which of your alternatives would be best then. My instinct would be to start with the RowVersion one, but that's just a SWAG.

    Your instinct was right on track. When you create a column of timestamp/rowversion datatype and bind the QN on it, it works as intended. Thanks for your time.

  • Glad it worked out for you then.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

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