{"id":521,"date":"2011-10-24T06:29:00","date_gmt":"2011-10-24T06:29:00","guid":{"rendered":"\/blogs\/joe\/post\/Hunting-down-the-origins-of-FETCH-API_CURSOR-and-sp_cursorfetch.aspx"},"modified":"2013-01-02T20:32:29","modified_gmt":"2013-01-03T04:32:29","slug":"hunting-down-the-origins-of-fetch-api_cursor-and-sp_cursorfetch","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/hunting-down-the-origins-of-fetch-api_cursor-and-sp_cursorfetch\/","title":{"rendered":"Hunting down the origins of FETCH API_CURSOR and sp_cursorfetch"},"content":{"rendered":"

\nSo picture the following scenario on a SQL Server 2008 R2 instance (an amalgam of various DBA situations you've no doubt seen before)…<\/font>  <\/font><\/span><\/font>\n<\/p>\n

\nYou get a call from the application team reporting slow performance on a specific service.  <\/span>They don’t know why it is slow but they do know the session that is running too slow based on the connection and session properties.  <\/span>They tell you that the issue is happening right now <\/em>and that they are seeing the offending session issue the following RPC:Completed events in SQL Profiler:<\/font><\/font>\n<\/p>\n

\n·<\/font><\/font>        <\/font><\/font><\/span><\/span><\/span>exec sp_cursorfetch 180150003,32,1,1<\/font><\/font>\n<\/p>\n

\n·<\/font><\/font>        <\/font><\/font><\/span><\/span><\/span>exec sp_cursorfetch 180150003,32,1,1<\/font><\/font>\n<\/p>\n

\n·<\/font><\/font>        <\/font><\/font><\/span><\/span><\/span>exec sp_cursorfetch 180150003,32,1,1<\/font><\/font>\n<\/p>\n

\n·<\/font><\/font>        <\/font><\/font><\/span><\/span><\/span>exec sp_cursorfetch 180150003,32,1,1<\/font><\/font>\n<\/p>\n

\nThey ask you to take it over and find out what is happening. You’re not sure what the original query is or why this is showing up, but you see the session id they are pointing to is “53” (and you don’t remember the syntax around pulling SQL text “the new way” – so you execute the following ):<\/font><\/font>\n<\/p>\n

\nDBCC INPUTBUFFER (53)<\/font><\/font>\n<\/p>\n

\nThis returns:<\/font><\/font>\n<\/p>\n

\n               <\/font><\/span>FETCH API_CURSOR0000000000000004<\/font><\/font>\n<\/p>\n

\nYou do a few quick searches and it seems to have a relationship with server-side cursors.  <\/span><\/font><\/font>\n<\/p>\n

\n<\/span>You try activity monitor – just in case – but again, no luck:<\/font><\/font>\n<\/p>\n

\n\"clip_image001\"<\/a><\/span>\n<\/p>\n

\nYou take out the new DMV queries – but your query against sys.dm_exec_requests isn’t turning up anything for session id 53 because the executions of this cursor are erratic and you're not timing it (but SQL Profiler does show it plodding along in fits and starts).<\/font><\/font>\n<\/p>\n

\nYou then run the following query against sys.dm_exec_connections and see if that turns up anything useful based on the most recent SQL handle:<\/font><\/font>\n<\/p>\n

\nSELECT t.text<\/font><\/font>\n<\/p>\n

\nFROM sys.dm_exec_connections c<\/font><\/font>\n<\/p>\n

\nCROSS APPLY sys.dm_exec_sql_text (c.most_recent_sql_handle) t<\/font><\/font>\n<\/p>\n

\nWHERE session_id = 53<\/font><\/font>\n<\/p>\n

\n \n<\/p>\n

\n<\/font><\/font>\n<\/p>\n

\nThis returns:<\/font><\/font>\n<\/p>\n

\nFETCH API_CURSOR0000000000000004<\/font><\/font>\n<\/p>\n

\nDidn’t help.<\/font>   <\/font><\/span><\/font>\n<\/p>\n

\nSo what about other DMVs?  <\/span>You eventually find a reference to the sys.dm_exec_cursors DMV and see it can tell you about open cursors, their properties and associated SQL handle.  But you're not sure the SQL Handle will be any help because it hasn't been helpful with the other DMVs:<\/font><\/font>\n<\/p>\n

\nSELECT c.session_id, c.properties, c.creation_time, c.is_open, t.text<\/font><\/font>\n<\/p>\n

\nFROM sys.dm_exec_cursors (53) c<\/font><\/font>\n<\/p>\n

\nCROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t<\/font><\/font>\n<\/p>\n

\n<\/font><\/font>\n<\/p>\n

\nWhat do we get this time? Something a bit more useful:<\/font><\/font>\n<\/p>\n

\n\"clip_image003\"<\/a><\/span>\n<\/p>\n

\nFrom the results we see the properties of the cursor (using scroll locks) and we also see when it was created – and we see the original query text (unlike the cryptic FETCH API_CURSOR business or the sp_cursorfetch).<\/font>  We see it was a SELECT * FROM dbo.FactResellerSales.<\/font><\/span><\/font>\n<\/p>\n

\nNow this isn’t to say that SQL Profiler wouldn’t have helped in this situation – but in this case the cursor was defined before the developers captured the downstream activity. <\/font>  <\/font><\/span><\/font>\n<\/p>\n

\nIf they had been tracing it sooner, you might have seen something like this (and then see it followed by sp_cursorfetch):<\/font><\/font>\n<\/p>\n

\ndeclare @p1 int<\/font><\/font>\n<\/p>\n

\nset @p1=180150003<\/font><\/font>\n<\/p>\n

\ndeclare @p3 int<\/font><\/font>\n<\/p>\n

\nset @p3=2<\/font><\/font>\n<\/p>\n

\ndeclare @p4 int<\/font><\/font>\n<\/p>\n

\nset @p4=2<\/font><\/font>\n<\/p>\n

\ndeclare @p5 int<\/font><\/font>\n<\/p>\n

\nset @p5=-1<\/font><\/font>\n<\/p>\n

\nexec sp_cursoropen @p1 output,N'SELECT * FROM dbo.FactResellerSales',@p3 output,@p4 output,@p5 output<\/font><\/font>\n<\/p>\n

\nselect @p1, @p3, @p4, @p5<\/font><\/font>\n<\/p>\n

\n \n<\/p>\n

\n<\/font><\/font>\n<\/p>\n

\nBut in a situation where you’re reacting <\/em>to an incident (fox has already left the henhouse, so to speak), chances are you weren’t tracing this activity.  And if that’s the case, you’ve now found one reason to use sys.dm_exec_cursors if you didn’t already have one.<\/font><\/font><\/p>\n","protected":false},"excerpt":{"rendered":"

So picture the following scenario on a SQL Server 2008 R2 instance (an amalgam of various DBA situations you've no doubt seen before)…  You get a call from the application team reporting slow performance on a specific service.  They don’t know why it is slow but they do know the session that is running too slow […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28],"tags":[],"class_list":["post-521","post","type-post","status-publish","format-standard","hentry","category-performance"],"yoast_head":"\nHunting down the origins of FETCH API_CURSOR and sp_cursorfetch - Joe Sack<\/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\/joe\/hunting-down-the-origins-of-fetch-api_cursor-and-sp_cursorfetch\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Hunting down the origins of FETCH API_CURSOR and sp_cursorfetch - Joe Sack\" \/>\n<meta property=\"og:description\" content=\"So picture the following scenario on a SQL Server 2008 R2 instance (an amalgam of various DBA situations you've no doubt seen before)…  You get a call from the application team reporting slow performance on a specific service.  They don’t know why it is slow but they do know the session that is running too slow […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/hunting-down-the-origins-of-fetch-api_cursor-and-sp_cursorfetch\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2011-10-24T06:29:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-03T04:32:29+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/hunting-down-the-origins-of-fetch-api_cu\/506addb8\/clip_image001_thumb.png\" \/>\n<meta name=\"author\" content=\"Joseph Sack\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Joseph Sack\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/hunting-down-the-origins-of-fetch-api_cursor-and-sp_cursorfetch\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/hunting-down-the-origins-of-fetch-api_cursor-and-sp_cursorfetch\/\",\"name\":\"Hunting down the origins of FETCH API_CURSOR and sp_cursorfetch - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2011-10-24T06:29:00+00:00\",\"dateModified\":\"2013-01-03T04:32:29+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/hunting-down-the-origins-of-fetch-api_cursor-and-sp_cursorfetch\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/hunting-down-the-origins-of-fetch-api_cursor-and-sp_cursorfetch\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/hunting-down-the-origins-of-fetch-api_cursor-and-sp_cursorfetch\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Performance\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/performance\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Hunting down the origins of FETCH API_CURSOR and sp_cursorfetch\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\",\"name\":\"Joe Sack\",\"description\":\"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\",\"name\":\"Joseph Sack\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"caption\":\"Joseph Sack\"},\"description\":\"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.\",\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/joe\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Hunting down the origins of FETCH API_CURSOR and sp_cursorfetch - Joe Sack","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\/joe\/hunting-down-the-origins-of-fetch-api_cursor-and-sp_cursorfetch\/","og_locale":"en_US","og_type":"article","og_title":"Hunting down the origins of FETCH API_CURSOR and sp_cursorfetch - Joe Sack","og_description":"So picture the following scenario on a SQL Server 2008 R2 instance (an amalgam of various DBA situations you've no doubt seen before)…  You get a call from the application team reporting slow performance on a specific service.  They don’t know why it is slow but they do know the session that is running too slow […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/hunting-down-the-origins-of-fetch-api_cursor-and-sp_cursorfetch\/","og_site_name":"Joe Sack","article_published_time":"2011-10-24T06:29:00+00:00","article_modified_time":"2013-01-03T04:32:29+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/hunting-down-the-origins-of-fetch-api_cu\/506addb8\/clip_image001_thumb.png"}],"author":"Joseph Sack","twitter_misc":{"Written by":"Joseph Sack","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/hunting-down-the-origins-of-fetch-api_cursor-and-sp_cursorfetch\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/hunting-down-the-origins-of-fetch-api_cursor-and-sp_cursorfetch\/","name":"Hunting down the origins of FETCH API_CURSOR and sp_cursorfetch - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2011-10-24T06:29:00+00:00","dateModified":"2013-01-03T04:32:29+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/hunting-down-the-origins-of-fetch-api_cursor-and-sp_cursorfetch\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/hunting-down-the-origins-of-fetch-api_cursor-and-sp_cursorfetch\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/hunting-down-the-origins-of-fetch-api_cursor-and-sp_cursorfetch\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/"},{"@type":"ListItem","position":2,"name":"Performance","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/performance\/"},{"@type":"ListItem","position":3,"name":"Hunting down the origins of FETCH API_CURSOR and sp_cursorfetch"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/","name":"Joe Sack","description":"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648","name":"Joseph Sack","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","caption":"Joseph Sack"},"description":"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.","sameAs":["http:\/\/3.209.169.194\/blogs\/joe","https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack"],"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/521","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/comments?post=521"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/521\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=521"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=521"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=521"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}