where they added SqlDependency.Start and Stop<\/a>). Stop and Start control the (new) internal listener. Start also sets up a stored procedure and a Service Broker Dialog Timer. Every 120 seconds, the timer fires, which activates the procedure, which sets the timer again. The first time I saw this, I thought the procedure was polling. Actually its more like its trauling. But its not polling (whew).\n<\/p>\n\nThe way SqlDependency works now is that requests for query notifications set up their own broker services to catch the notification message. The activation proc (also set up for the notifications) relays the notification message back to the client. Which calls your event handler, or in the case of ASP.NET's SqlNotificationRequest, invalidates the cache.\n<\/p>\n
\nSo what's the timer for? The timer is trauling to find services and procedures (they have GUIDs in their names) set up by SqlDependency instances and will clean them up if it finds any. This is NOT on an individual SqlDependency instance basis, but on a *per-listner* basis. That is, once for every client that calls Start.\n<\/p>\n
\nTake home message is, now more than ever, you need to limit the number of clients that call Start. Maybe only ASP.NET (or other) caches???\n<\/p>\n
\nI'm glad that's settled. See you on the road I'm teaching\/talking about this more in person…c'mon over and say hi.<\/p>\n","protected":false},"excerpt":{"rendered":"
I'm headed out on the road. For quite a while. Wanted to settle this before I went. Looks like the implementation details of SqlDependency have changed a bit. Even since the September CTP (where they added SqlDependency.Start and Stop). Stop and Start control the (new) internal listener. Start also sets up a stored procedure and […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22],"tags":[],"class_list":["post-892","post","type-post","status-publish","format-standard","hentry","category-query-notifications"],"yoast_head":"\n
What's the SqlDependency doing now? - Bob Beauchemin<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/whats-the-sqldependency-doing-now\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"What's the SqlDependency doing now? - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"I'm headed out on the road. For quite a while. Wanted to settle this before I went. Looks like the implementation details of SqlDependency have changed a bit. Even since the September CTP (where they added SqlDependency.Start and Stop). Stop and Start control the (new) internal listener. Start also sets up a stored procedure and […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/whats-the-sqldependency-doing-now\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2005-11-10T20:44:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-02-04T16:17:01+00:00\" \/>\n<meta name=\"author\" content=\"Bob Beauchemin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bob Beauchemin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/whats-the-sqldependency-doing-now\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/whats-the-sqldependency-doing-now\/\",\"name\":\"What's the SqlDependency doing now? - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2005-11-10T20:44:00+00:00\",\"dateModified\":\"2013-02-04T16:17:01+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/whats-the-sqldependency-doing-now\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/whats-the-sqldependency-doing-now\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/whats-the-sqldependency-doing-now\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Query Notifications\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/query-notifications\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"What’s the SqlDependency doing now?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\",\"name\":\"Bob Beauchemin\",\"description\":\"SQL Server Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\",\"name\":\"Bob Beauchemin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"caption\":\"Bob Beauchemin\"},\"sameAs\":[\"http:\/www.sqlskills.com\/blogs\/bobb\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"What's the SqlDependency doing now? - Bob Beauchemin","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/bobb\/whats-the-sqldependency-doing-now\/","og_locale":"en_US","og_type":"article","og_title":"What's the SqlDependency doing now? - Bob Beauchemin","og_description":"I'm headed out on the road. For quite a while. Wanted to settle this before I went. Looks like the implementation details of SqlDependency have changed a bit. Even since the September CTP (where they added SqlDependency.Start and Stop). Stop and Start control the (new) internal listener. Start also sets up a stored procedure and […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/whats-the-sqldependency-doing-now\/","og_site_name":"Bob Beauchemin","article_published_time":"2005-11-10T20:44:00+00:00","article_modified_time":"2013-02-04T16:17:01+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/whats-the-sqldependency-doing-now\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/whats-the-sqldependency-doing-now\/","name":"What's the SqlDependency doing now? - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2005-11-10T20:44:00+00:00","dateModified":"2013-02-04T16:17:01+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/whats-the-sqldependency-doing-now\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/whats-the-sqldependency-doing-now\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/whats-the-sqldependency-doing-now\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"Query Notifications","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/query-notifications\/"},{"@type":"ListItem","position":3,"name":"What’s the SqlDependency doing now?"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/","name":"Bob Beauchemin","description":"SQL Server Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e","name":"Bob Beauchemin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","caption":"Bob Beauchemin"},"sameAs":["http:\/www.sqlskills.com\/blogs\/bobb\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/892","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/comments?post=892"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/892\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=892"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=892"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=892"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}