{"id":27175,"date":"2024-03-16T08:01:18","date_gmt":"2024-03-16T08:01:18","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/how-can-i-query-the-historical-execution-records-in-sql-server\/"},"modified":"2024-03-22T10:23:17","modified_gmt":"2024-03-22T10:23:17","slug":"how-can-i-query-the-historical-execution-records-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/how-can-i-query-the-historical-execution-records-in-sql-server\/","title":{"rendered":"How can I query the historical execution records in SQL Server?"},"content":{"rendered":"

To check the historical execution records of SQL Server, you can use the following methods:<\/p>\n

    \n
  1. Utilize the dynamic management view sys.dm_exec_query_stats to gather information on past query executions, including query text, execution counts, average execution time, etc. This view allows sorting and filtering based on metrics such as execution time, CPU time, logical reads, etc.<\/li>\n<\/ol>\n
    SELECT<\/span> \r\n    creation_time,\r\n    last_execution_time,\r\n    total_logical_reads,\r\n    total_elapsed_time,\r\n    execution_count,\r\n    st.text\r\nFROM<\/span> \r\n    sys.dm_exec_query_stats AS<\/span> qs\r\nCROSS<\/span> APPLY \r\n    sys.dm_exec_sql_text(qs.sql_handle) AS<\/span> st\r\nORDER<\/span> BY<\/span> \r\n    last_execution_time DESC<\/span>\r\n<\/code><\/pre>\n
      \n
    1. Using SQL Server Profiler: Profiler is a powerful tool that can be used to monitor and analyze SQL Server activities. Tracing sessions can be set up to capture executed queries and view their history.<\/li>\n
    2. Using Extended Events: Extended Events is an event handler for SQL Server that can capture and analyze activities of the SQL Server engine. By using Extended Events, you can create event sessions to capture executed queries and view their history.<\/li>\n<\/ol>\n
      CREATE<\/span> EVENT SESSION QueryHistory\r\nON<\/span> SERVER\r\nADD<\/span> EVENT sqlserver.sql_statement_completed\r\n(\r\n    ACTION (sqlserver.sql_text)\r\n)\r\nADD<\/span> TARGET package0.event_file\r\n(\r\n    SET<\/span> filename =<\/span> 'C:\\Path\\To\\QueryHistory.xel'<\/span>\r\n)\r\nWITH<\/span>\r\n(\r\n    MAX_MEMORY =<\/span> 4096<\/span> KB,\r\n    EVENT_RETENTION_MODE =<\/span> ALLOW_SINGLE_EVENT_LOSS\r\n);\r\n<\/code><\/pre>\n
        \n
      1. Third-party tools can also be used to query the historical execution records of SQL Server, such as SQL Server Management Studio and Idera SQL Diagnostic Manager.<\/li>\n<\/ol>\n
        You can choose any method and select a suitable query method based on your needs, then further analyze the results.<\/p>\n","protected":false},"excerpt":{"rendered":"
        To check the historical execution records of SQL Server, you can use the following methods: Utilize the dynamic management view sys.dm_exec_query_stats to gather information on past query executions, including query text, execution counts, average execution time, etc. This view allows sorting and filtering based on metrics such as execution time, CPU time, logical reads, etc. […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","footnotes":""},"categories":[1],"tags":[],"class_list":["post-27175","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"\nHow can I query the historical execution records in SQL Server? - Blog - Silicon Cloud<\/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.silicloud.com\/blog\/how-can-i-query-the-historical-execution-records-in-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How can I query the historical execution records in SQL Server?\" \/>\n<meta property=\"og:description\" content=\"To check the historical execution records of SQL Server, you can use the following methods: Utilize the dynamic management view sys.dm_exec_query_stats to gather information on past query executions, including query text, execution counts, average execution time, etc. This view allows sorting and filtering based on metrics such as execution time, CPU time, logical reads, etc. […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/blog\/how-can-i-query-the-historical-execution-records-in-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog - Silicon Cloud\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\" \/>\n<meta property=\"article:published_time\" content=\"2024-03-16T08:01:18+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-22T10:23:17+00:00\" \/>\n<meta name=\"author\" content=\"Sophia Anderson\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:site\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sophia Anderson\" \/>\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\":\"Article\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-i-query-the-historical-execution-records-in-sql-server\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-i-query-the-historical-execution-records-in-sql-server\/\"},\"author\":{\"name\":\"Sophia Anderson\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/19a24313de9c988db3d69226b4a40a30\"},\"headline\":\"How can I query the historical execution records in SQL Server?\",\"datePublished\":\"2024-03-16T08:01:18+00:00\",\"dateModified\":\"2024-03-22T10:23:17+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-i-query-the-historical-execution-records-in-sql-server\/\"},\"wordCount\":190,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-i-query-the-historical-execution-records-in-sql-server\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/how-can-i-query-the-historical-execution-records-in-sql-server\/\",\"name\":\"How can I query the historical execution records in SQL Server? - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-16T08:01:18+00:00\",\"dateModified\":\"2024-03-22T10:23:17+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-i-query-the-historical-execution-records-in-sql-server\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/how-can-i-query-the-historical-execution-records-in-sql-server\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-i-query-the-historical-execution-records-in-sql-server\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.silicloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How can I query the historical execution records in SQL Server?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"name\":\"Silicon Cloud Blog\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\",\"name\":\"Silicon Cloud Blog\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"contentUrl\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"width\":1024,\"height\":1024,\"caption\":\"Silicon Cloud Blog\"},\"image\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\",\"https:\/\/twitter.com\/SiliCloudGlobal\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/19a24313de9c988db3d69226b4a40a30\",\"name\":\"Sophia Anderson\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c726c09aa40e37115fb5c62d0c3ed62c16ca255d3763e2e3ae83a70ddf8c2175?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c726c09aa40e37115fb5c62d0c3ed62c16ca255d3763e2e3ae83a70ddf8c2175?s=96&d=mm&r=g\",\"caption\":\"Sophia Anderson\"},\"url\":\"https:\/\/www.silicloud.com\/blog\/author\/sophiaanderson\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How can I query the historical execution records in SQL Server? - Blog - Silicon Cloud","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.silicloud.com\/blog\/how-can-i-query-the-historical-execution-records-in-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"How can I query the historical execution records in SQL Server?","og_description":"To check the historical execution records of SQL Server, you can use the following methods: Utilize the dynamic management view sys.dm_exec_query_stats to gather information on past query executions, including query text, execution counts, average execution time, etc. This view allows sorting and filtering based on metrics such as execution time, CPU time, logical reads, etc. […]","og_url":"https:\/\/www.silicloud.com\/blog\/how-can-i-query-the-historical-execution-records-in-sql-server\/","og_site_name":"Blog - Silicon Cloud","article_publisher":"https:\/\/www.facebook.com\/SiliCloudGlobal\/","article_published_time":"2024-03-16T08:01:18+00:00","article_modified_time":"2024-03-22T10:23:17+00:00","author":"Sophia Anderson","twitter_card":"summary_large_image","twitter_creator":"@SiliCloudGlobal","twitter_site":"@SiliCloudGlobal","twitter_misc":{"Written by":"Sophia Anderson","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.silicloud.com\/blog\/how-can-i-query-the-historical-execution-records-in-sql-server\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/how-can-i-query-the-historical-execution-records-in-sql-server\/"},"author":{"name":"Sophia Anderson","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/19a24313de9c988db3d69226b4a40a30"},"headline":"How can I query the historical execution records in SQL Server?","datePublished":"2024-03-16T08:01:18+00:00","dateModified":"2024-03-22T10:23:17+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/how-can-i-query-the-historical-execution-records-in-sql-server\/"},"wordCount":190,"commentCount":0,"publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/blog\/how-can-i-query-the-historical-execution-records-in-sql-server\/","url":"https:\/\/www.silicloud.com\/blog\/how-can-i-query-the-historical-execution-records-in-sql-server\/","name":"How can I query the historical execution records in SQL Server? - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-16T08:01:18+00:00","dateModified":"2024-03-22T10:23:17+00:00","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/how-can-i-query-the-historical-execution-records-in-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/how-can-i-query-the-historical-execution-records-in-sql-server\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/how-can-i-query-the-historical-execution-records-in-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.silicloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How can I query the historical execution records in SQL Server?"}]},{"@type":"WebSite","@id":"https:\/\/www.silicloud.com\/blog\/#website","url":"https:\/\/www.silicloud.com\/blog\/","name":"Silicon Cloud Blog","description":"","publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.silicloud.com\/blog\/#organization","name":"Silicon Cloud Blog","url":"https:\/\/www.silicloud.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","contentUrl":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","width":1024,"height":1024,"caption":"Silicon Cloud Blog"},"image":{"@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/SiliCloudGlobal\/","https:\/\/twitter.com\/SiliCloudGlobal"]},{"@type":"Person","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/19a24313de9c988db3d69226b4a40a30","name":"Sophia Anderson","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c726c09aa40e37115fb5c62d0c3ed62c16ca255d3763e2e3ae83a70ddf8c2175?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c726c09aa40e37115fb5c62d0c3ed62c16ca255d3763e2e3ae83a70ddf8c2175?s=96&d=mm&r=g","caption":"Sophia Anderson"},"url":"https:\/\/www.silicloud.com\/blog\/author\/sophiaanderson\/"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/27175","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/comments?post=27175"}],"version-history":[{"count":1,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/27175\/revisions"}],"predecessor-version":[{"id":61390,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/27175\/revisions\/61390"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=27175"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=27175"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=27175"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}