Notebooks/sp_BlitzCache.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "# sp_BlitzCache from Brent Ozar\r\n",
                "\r\n",
                "You can download the First Responder Kit from https://www.brentozar.com/first-aid/ It will provide you with an excellent set of resources for free including sp_Blitz.\r\n",
                "\r\n",
                "You can either run the Install-All-Scripts.sql script to install all of the stored procedures or the individual stored procedures using the T-SQL files. It can go i nthe master database or in a DBA database, it is up to you. \r\n",
                "\r\n",
                "With the stored procedures installed you can then investigate the instance that you are interested in. Brent has written about how to start here https://www.brentozar.com/blitzcache/ with a video as well\r\n",
                "\r\n",
                "## Run sp_Blitz_Cache with basic parameters\r\n",
                "\r\n",
                "You can sort the results from the cache by reads, CPU, executions, xpm, recent compilations, memory grant, writes, all and limit the number of queries to work with \r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "aa2ce5c3-1606-4035-8d7e-c9e1d95369ad"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "EXEC [dbo].[sp_BlitzCache]\r\n",
                "@SortOrder = 'all',\r\n",
                "    -- reads, CPU, executions, xpm, recent compilations, memory grant, writes, all\r\n",
                "@Top = 10\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "7928007e-afca-4250-8e0d-61111edb3cb5",
                "tags": []
            },
            "outputs": [],
            "execution_count": 2
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Help \r\n",
                "\r\n",
                "You can get more information about the stored procedure, parameters and results using the @Help = 1"
            ],
            "metadata": {
                "azdata_cell_guid": "e0ba0fb4-f538-4b4c-986b-a7375ce94404"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "EXEC [dbo].[sp_BlitzCache]\r\n",
                "@Help = 1"
            ],
            "metadata": {
                "azdata_cell_guid": "d9c135c7-56b5-43e2-a7fc-c3a972c0dd18"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "\r\nsp_BlitzCache from http://FirstResponderKit.org\r\n\t\r\nThis script displays your most resource-intensive queries from the plan cache,\r\nand points to ways you can tune these queries to make them faster.\r\n\r\n\r\nTo learn more, visit http://FirstResponderKit.org where you can download new\r\nversions for free, watch training videos on how it works, get more info on\r\nthe findings, contribute your own code, and more.\r\n\r\nKnown limitations of this version:\r\n - This query will not run on SQL Server 2005.\r\n - SQL Server 2008 and 2008R2 have a bug in trigger stats, so that output is\r\n   excluded by default.\r\n - @IgnoreQueryHashes and @OnlyQueryHashes require a CSV list of hashes\r\n   with no spaces between the hash values.\r\n - @OutputServerName is not functional yet.\r\n\r\nUnknown limitations of this version:\r\n - May or may not be vulnerable to the wick effect.\r\n\r\nChanges - for the full list of improvements and fixes in this version, see:\r\nhttps://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/\r\n\r\n\r\n\r\nMIT License\r\n\r\nCopyright (c) 2020 Brent Ozar Unlimited\r\n\r\nPermission is hereby granted, free of charge, to any person obtaining a copy\r\nof this software and associated documentation files (the \"Software\"), to deal\r\nin the Software without restriction, including without limitation the rights\r\nto use, copy, modify, merge, publish, distribute, sublicense, and/or sell\r\ncopies of the Software, and to permit persons to whom the Software is\r\nfurnished to do so, subject to the following conditions:\r\n\r\nThe above copyright notice and this permission notice shall be included in all\r\ncopies or substantial portions of the Software.\r\n\r\nTHE SOFTWARE IS PROVIDED \"AS IS\", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR\r\nIMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,\r\nFITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE\r\nAUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER\r\nLIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,\r\nOUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE\r\nSOFTWARE.\r\n"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.402"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 3,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "Parameter Name"
                                    },
                                    {
                                        "name": "Data Type"
                                    },
                                    {
                                        "name": "Parameter Description"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "@Help",
                                    "1": "BIT",
                                    "2": "Displays this help message."
                                },
                                {
                                    "0": "@Top",
                                    "1": "INT",
                                    "2": "The number of records to retrieve and analyze from the plan cache. The following DMVs are used as the plan cache: dm_exec_query_stats, dm_exec_procedure_stats, dm_exec_trigger_stats."
                                },
                                {
                                    "0": "@SortOrder",
                                    "1": "VARCHAR(10)",
                                    "2": "Data processing and display order. @SortOrder will still be used, even when preparing output for a table or for excel. Possible values are: \"CPU\", \"Reads\", \"Writes\", \"Duration\", \"Executions\", \"Recent Compilations\", \"Memory Grant\", \"Spills\", \"Query Hash\". Additionally, the word \"Average\" or \"Avg\" can be used to sort on averages rather than total. \"Executions per minute\" and \"Executions / minute\" can be used to sort by execution per minute. For the truly lazy, \"xpm\" can also be used. Note that when you use all or all avg, the only parameters you can use are @Top and @DatabaseName. All others will be ignored."
                                },
                                {
                                    "0": "@UseTriggersAnyway",
                                    "1": "BIT",
                                    "2": "On SQL Server 2008R2 and earlier, trigger execution count is incorrect - trigger execution count is incremented once per execution of a SQL agent job. If you still want to see relative execution count of triggers, then you can force sp_BlitzCache to include this information."
                                },
                                {
                                    "0": "@ExportToExcel",
                                    "1": "BIT",
                                    "2": "Prepare output for exporting to Excel. Newlines and additional whitespace are removed from query text and the execution plan is not displayed."
                                },
                                {
                                    "0": "@ExpertMode",
                                    "1": "TINYINT",
                                    "2": "Default 0. When set to 1, results include more columns. When 2, mode is optimized for Opserver, the open source dashboard."
                                },
                                {
                                    "0": "@OutputDatabaseName",
                                    "1": "NVARCHAR(128)",
                                    "2": "The output database. If this does not exist SQL Server will divide by zero and everything will fall apart."
                                },
                                {
                                    "0": "@OutputSchemaName",
                                    "1": "NVARCHAR(258)",
                                    "2": "The output schema. If this does not exist SQL Server will divide by zero and everything will fall apart."
                                },
                                {
                                    "0": "@OutputTableName",
                                    "1": "NVARCHAR(258)",
                                    "2": "The output table. If this does not exist, it will be created for you."
                                },
                                {
                                    "0": "@DurationFilter",
                                    "1": "DECIMAL(38,4)",
                                    "2": "Excludes queries with an average duration (in seconds) less than @DurationFilter."
                                },
                                {
                                    "0": "@HideSummary",
                                    "1": "BIT",
                                    "2": "Hides the findings summary result set."
                                },
                                {
                                    "0": "@IgnoreSystemDBs",
                                    "1": "BIT",
                                    "2": "Ignores plans found in the system databases (master, model, msdb, tempdb, and resourcedb)"
                                },
                                {
                                    "0": "@OnlyQueryHashes",
                                    "1": "VARCHAR(MAX)",
                                    "2": "A list of query hashes to query. All other query hashes will be ignored. Stored procedures and triggers will be ignored."
                                },
                                {
                                    "0": "@IgnoreQueryHashes",
                                    "1": "VARCHAR(MAX)",
                                    "2": "A list of query hashes to ignore."
                                },
                                {
                                    "0": "@OnlySqlHandles",
                                    "1": "VARCHAR(MAX)",
                                    "2": "One or more sql_handles to use for filtering results."
                                },
                                {
                                    "0": "@IgnoreSqlHandles",
                                    "1": "VARCHAR(MAX)",
                                    "2": "One or more sql_handles to ignore."
                                },
                                {
                                    "0": "@DatabaseName",
                                    "1": "NVARCHAR(128)",
                                    "2": "A database name which is used for filtering results."
                                },
                                {
                                    "0": "@StoredProcName",
                                    "1": "NVARCHAR(128)",
                                    "2": "Name of stored procedure you want to find plans for."
                                },
                                {
                                    "0": "@SlowlySearchPlansFor",
                                    "1": "NVARCHAR(4000)",
                                    "2": "String to search for in plan text. % wildcards allowed."
                                },
                                {
                                    "0": "@BringThePain",
                                    "1": "BIT",
                                    "2": "When using @SortOrder = 'all' and @Top > 10, we require you to set @BringThePain = 1 so you understand that sp_BlitzCache will take a while to run."
                                },
                                {
                                    "0": "@QueryFilter",
                                    "1": "VARCHAR(10)",
                                    "2": "Filter out stored procedures or statements. The default value is 'ALL'. Allowed values are 'procedures', 'statements', 'functions', or 'all' (any variation in capitalization is acceptable)."
                                },
                                {
                                    "0": "@Reanalyze",
                                    "1": "BIT",
                                    "2": "The default is 0. When set to 0, sp_BlitzCache will re-evalute the plan cache. Set this to 1 to reanalyze existing results"
                                },
                                {
                                    "0": "@MinimumExecutionCount",
                                    "1": "INT",
                                    "2": "Queries with fewer than this number of executions will be omitted from results."
                                },
                                {
                                    "0": "@Debug",
                                    "1": "BIT",
                                    "2": "Setting this to 1 will print dynamic SQL and select data from all tables used."
                                },
                                {
                                    "0": "@MinutesBack",
                                    "1": "INT",
                                    "2": "How many minutes back to begin plan cache analysis. If you put in a positive number, we'll flip it to negtive."
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>Parameter Name</th><th>Data Type</th><th>Parameter Description</th></tr><tr><td>@Help</td><td>BIT</td><td>Displays this help message.</td></tr><tr><td>@Top</td><td>INT</td><td>The number of records to retrieve and analyze from the plan cache. The following DMVs are used as the plan cache: dm_exec_query_stats, dm_exec_procedure_stats, dm_exec_trigger_stats.</td></tr><tr><td>@SortOrder</td><td>VARCHAR(10)</td><td>Data processing and display order. @SortOrder will still be used, even when preparing output for a table or for excel. Possible values are: &quot;CPU&quot;, &quot;Reads&quot;, &quot;Writes&quot;, &quot;Duration&quot;, &quot;Executions&quot;, &quot;Recent Compilations&quot;, &quot;Memory Grant&quot;, &quot;Spills&quot;, &quot;Query Hash&quot;. Additionally, the word &quot;Average&quot; or &quot;Avg&quot; can be used to sort on averages rather than total. &quot;Executions per minute&quot; and &quot;Executions / minute&quot; can be used to sort by execution per minute. For the truly lazy, &quot;xpm&quot; can also be used. Note that when you use all or all avg, the only parameters you can use are @Top and @DatabaseName. All others will be ignored.</td></tr><tr><td>@UseTriggersAnyway</td><td>BIT</td><td>On SQL Server 2008R2 and earlier, trigger execution count is incorrect - trigger execution count is incremented once per execution of a SQL agent job. If you still want to see relative execution count of triggers, then you can force sp_BlitzCache to include this information.</td></tr><tr><td>@ExportToExcel</td><td>BIT</td><td>Prepare output for exporting to Excel. Newlines and additional whitespace are removed from query text and the execution plan is not displayed.</td></tr><tr><td>@ExpertMode</td><td>TINYINT</td><td>Default 0. When set to 1, results include more columns. When 2, mode is optimized for Opserver, the open source dashboard.</td></tr><tr><td>@OutputDatabaseName</td><td>NVARCHAR(128)</td><td>The output database. If this does not exist SQL Server will divide by zero and everything will fall apart.</td></tr><tr><td>@OutputSchemaName</td><td>NVARCHAR(258)</td><td>The output schema. If this does not exist SQL Server will divide by zero and everything will fall apart.</td></tr><tr><td>@OutputTableName</td><td>NVARCHAR(258)</td><td>The output table. If this does not exist, it will be created for you.</td></tr><tr><td>@DurationFilter</td><td>DECIMAL(38,4)</td><td>Excludes queries with an average duration (in seconds) less than @DurationFilter.</td></tr><tr><td>@HideSummary</td><td>BIT</td><td>Hides the findings summary result set.</td></tr><tr><td>@IgnoreSystemDBs</td><td>BIT</td><td>Ignores plans found in the system databases (master, model, msdb, tempdb, and resourcedb)</td></tr><tr><td>@OnlyQueryHashes</td><td>VARCHAR(MAX)</td><td>A list of query hashes to query. All other query hashes will be ignored. Stored procedures and triggers will be ignored.</td></tr><tr><td>@IgnoreQueryHashes</td><td>VARCHAR(MAX)</td><td>A list of query hashes to ignore.</td></tr><tr><td>@OnlySqlHandles</td><td>VARCHAR(MAX)</td><td>One or more sql_handles to use for filtering results.</td></tr><tr><td>@IgnoreSqlHandles</td><td>VARCHAR(MAX)</td><td>One or more sql_handles to ignore.</td></tr><tr><td>@DatabaseName</td><td>NVARCHAR(128)</td><td>A database name which is used for filtering results.</td></tr><tr><td>@StoredProcName</td><td>NVARCHAR(128)</td><td>Name of stored procedure you want to find plans for.</td></tr><tr><td>@SlowlySearchPlansFor</td><td>NVARCHAR(4000)</td><td>String to search for in plan text. % wildcards allowed.</td></tr><tr><td>@BringThePain</td><td>BIT</td><td>When using @SortOrder = 'all' and @Top &gt; 10, we require you to set @BringThePain = 1 so you understand that sp_BlitzCache will take a while to run.</td></tr><tr><td>@QueryFilter</td><td>VARCHAR(10)</td><td>Filter out stored procedures or statements. The default value is 'ALL'. Allowed values are 'procedures', 'statements', 'functions', or 'all' (any variation in capitalization is acceptable).</td></tr><tr><td>@Reanalyze</td><td>BIT</td><td>The default is 0. When set to 0, sp_BlitzCache will re-evalute the plan cache. Set this to 1 to reanalyze existing results</td></tr><tr><td>@MinimumExecutionCount</td><td>INT</td><td>Queries with fewer than this number of executions will be omitted from results.</td></tr><tr><td>@Debug</td><td>BIT</td><td>Setting this to 1 will print dynamic SQL and select data from all tables used.</td></tr><tr><td>@MinutesBack</td><td>INT</td><td>How many minutes back to begin plan cache analysis. If you put in a positive number, we'll flip it to negtive.</td></tr></table>"
                    }
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 3,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "Column Name"
                                    },
                                    {
                                        "name": "Data Type"
                                    },
                                    {
                                        "name": "Column Description"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "# Executions",
                                    "1": "BIGINT",
                                    "2": "The number of executions of this particular query. This is computed across statements, procedures, and triggers and aggregated by the SQL handle."
                                },
                                {
                                    "0": "Executions / Minute",
                                    "1": "MONEY",
                                    "2": "Number of executions per minute - calculated for the life of the current plan. Plan life is the last execution time minus the plan creation time."
                                },
                                {
                                    "0": "Execution Weight",
                                    "1": "MONEY",
                                    "2": "An arbitrary metric of total \"execution-ness\". A weight of 2 is \"one more\" than a weight of 1."
                                },
                                {
                                    "0": "Database",
                                    "1": "sysname",
                                    "2": "The name of the database where the plan was encountered. If the database name cannot be determined for some reason, a value of NA will be substituted. A value of 32767 indicates the plan comes from ResourceDB."
                                },
                                {
                                    "0": "Total CPU",
                                    "1": "BIGINT",
                                    "2": "Total CPU time, reported in milliseconds, that was consumed by all executions of this query since the last compilation."
                                },
                                {
                                    "0": "Avg CPU",
                                    "1": "BIGINT",
                                    "2": "Average CPU time, reported in milliseconds, consumed by each execution of this query since the last compilation."
                                },
                                {
                                    "0": "CPU Weight",
                                    "1": "MONEY",
                                    "2": "An arbitrary metric of total \"CPU-ness\". A weight of 2 is \"one more\" than a weight of 1."
                                },
                                {
                                    "0": "Total Duration",
                                    "1": "BIGINT",
                                    "2": "Total elapsed time, reported in milliseconds, consumed by all executions of this query since last compilation."
                                },
                                {
                                    "0": "Avg Duration",
                                    "1": "BIGINT",
                                    "2": "Average elapsed time, reported in milliseconds, consumed by each execution of this query since the last compilation."
                                },
                                {
                                    "0": "Duration Weight",
                                    "1": "MONEY",
                                    "2": "An arbitrary metric of total \"Duration-ness\". A weight of 2 is \"one more\" than a weight of 1."
                                },
                                {
                                    "0": "Total Reads",
                                    "1": "BIGINT",
                                    "2": "Total logical reads performed by this query since last compilation."
                                },
                                {
                                    "0": "Average Reads",
                                    "1": "BIGINT",
                                    "2": "Average logical reads performed by each execution of this query since the last compilation."
                                },
                                {
                                    "0": "Read Weight",
                                    "1": "MONEY",
                                    "2": "An arbitrary metric of \"Read-ness\". A weight of 2 is \"one more\" than a weight of 1."
                                },
                                {
                                    "0": "Total Writes",
                                    "1": "BIGINT",
                                    "2": "Total logical writes performed by this query since last compilation."
                                },
                                {
                                    "0": "Average Writes",
                                    "1": "BIGINT",
                                    "2": "Average logical writes performed by each execution this query since last compilation."
                                },
                                {
                                    "0": "Write Weight",
                                    "1": "MONEY",
                                    "2": "An arbitrary metric of \"Write-ness\". A weight of 2 is \"one more\" than a weight of 1."
                                },
                                {
                                    "0": "Query Type",
                                    "1": "NVARCHAR(258)",
                                    "2": "The type of query being examined. This can be \"Procedure\", \"Statement\", or \"Trigger\"."
                                },
                                {
                                    "0": "Query Text",
                                    "1": "NVARCHAR(4000)",
                                    "2": "The text of the query. This may be truncated by either SQL Server or by sp_BlitzCache(tm) for display purposes."
                                },
                                {
                                    "0": "% Executions (Type)",
                                    "1": "MONEY",
                                    "2": "Percent of executions relative to the type of query - e.g. 17.2% of all stored procedure executions."
                                },
                                {
                                    "0": "% CPU (Type)",
                                    "1": "MONEY",
                                    "2": "Percent of CPU time consumed by this query for a given type of query - e.g. 22% of CPU of all stored procedures executed."
                                },
                                {
                                    "0": "% Duration (Type)",
                                    "1": "MONEY",
                                    "2": "Percent of elapsed time consumed by this query for a given type of query - e.g. 12% of all statements executed."
                                },
                                {
                                    "0": "% Reads (Type)",
                                    "1": "MONEY",
                                    "2": "Percent of reads consumed by this query for a given type of query - e.g. 34.2% of all stored procedures executed."
                                },
                                {
                                    "0": "% Writes (Type)",
                                    "1": "MONEY",
                                    "2": "Percent of writes performed by this query for a given type of query - e.g. 43.2% of all statements executed."
                                },
                                {
                                    "0": "Total Rows",
                                    "1": "BIGINT",
                                    "2": "Total number of rows returned for all executions of this query. This only applies to query level stats, not stored procedures or triggers."
                                },
                                {
                                    "0": "Average Rows",
                                    "1": "MONEY",
                                    "2": "Average number of rows returned by each execution of the query."
                                },
                                {
                                    "0": "Min Rows",
                                    "1": "BIGINT",
                                    "2": "The minimum number of rows returned by any execution of this query."
                                },
                                {
                                    "0": "Max Rows",
                                    "1": "BIGINT",
                                    "2": "The maximum number of rows returned by any execution of this query."
                                },
                                {
                                    "0": "MinGrantKB",
                                    "1": "BIGINT",
                                    "2": "The minimum memory grant the query received in kb."
                                },
                                {
                                    "0": "MaxGrantKB",
                                    "1": "BIGINT",
                                    "2": "The maximum memory grant the query received in kb."
                                },
                                {
                                    "0": "MinUsedGrantKB",
                                    "1": "BIGINT",
                                    "2": "The minimum used memory grant the query received in kb."
                                },
                                {
                                    "0": "MaxUsedGrantKB",
                                    "1": "BIGINT",
                                    "2": "The maximum used memory grant the query received in kb."
                                },
                                {
                                    "0": "MinSpills",
                                    "1": "BIGINT",
                                    "2": "The minimum amount this query has spilled to tempdb in 8k pages."
                                },
                                {
                                    "0": "MaxSpills",
                                    "1": "BIGINT",
                                    "2": "The maximum amount this query has spilled to tempdb in 8k pages."
                                },
                                {
                                    "0": "TotalSpills",
                                    "1": "BIGINT",
                                    "2": "The total amount this query has spilled to tempdb in 8k pages."
                                },
                                {
                                    "0": "AvgSpills",
                                    "1": "BIGINT",
                                    "2": "The average amount this query has spilled to tempdb in 8k pages."
                                },
                                {
                                    "0": "PercentMemoryGrantUsed",
                                    "1": "MONEY",
                                    "2": "Result of dividing the maximum grant used by the minimum granted."
                                },
                                {
                                    "0": "AvgMaxMemoryGrant",
                                    "1": "MONEY",
                                    "2": "The average maximum memory grant for a query."
                                },
                                {
                                    "0": "# Plans",
                                    "1": "INT",
                                    "2": "The total number of execution plans found that match a given query."
                                },
                                {
                                    "0": "# Distinct Plans",
                                    "1": "INT",
                                    "2": "The number of distinct execution plans that match a given query. \r\nThis may be caused by running the same query across multiple databases or because of a lack of proper parameterization in the database."
                                },
                                {
                                    "0": "Created At",
                                    "1": "DATETIME",
                                    "2": "Time that the execution plan was last compiled."
                                },
                                {
                                    "0": "Last Execution",
                                    "1": "DATETIME",
                                    "2": "The last time that this query was executed."
                                },
                                {
                                    "0": "Query Plan",
                                    "1": "XML",
                                    "2": "The query plan. Click to display a graphical plan or, if you need to patch SSMS, a pile of XML."
                                },
                                {
                                    "0": "Plan Handle",
                                    "1": "VARBINARY(64)",
                                    "2": "An arbitrary identifier referring to the compiled plan this query is a part of."
                                },
                                {
                                    "0": "SQL Handle",
                                    "1": "VARBINARY(64)",
                                    "2": "An arbitrary identifier referring to a batch or stored procedure that this query is a part of."
                                },
                                {
                                    "0": "Query Hash",
                                    "1": "BINARY(8)",
                                    "2": "A hash of the query. Queries with the same query hash have similar logic but only differ by literal values or database."
                                },
                                {
                                    "0": "Warnings",
                                    "1": "VARCHAR(MAX)",
                                    "2": "A list of individual warnings generated by this query."
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>Column Name</th><th>Data Type</th><th>Column Description</th></tr><tr><td># Executions</td><td>BIGINT</td><td>The number of executions of this particular query. This is computed across statements, procedures, and triggers and aggregated by the SQL handle.</td></tr><tr><td>Executions / Minute</td><td>MONEY</td><td>Number of executions per minute - calculated for the life of the current plan. Plan life is the last execution time minus the plan creation time.</td></tr><tr><td>Execution Weight</td><td>MONEY</td><td>An arbitrary metric of total &quot;execution-ness&quot;. A weight of 2 is &quot;one more&quot; than a weight of 1.</td></tr><tr><td>Database</td><td>sysname</td><td>The name of the database where the plan was encountered. If the database name cannot be determined for some reason, a value of NA will be substituted. A value of 32767 indicates the plan comes from ResourceDB.</td></tr><tr><td>Total CPU</td><td>BIGINT</td><td>Total CPU time, reported in milliseconds, that was consumed by all executions of this query since the last compilation.</td></tr><tr><td>Avg CPU</td><td>BIGINT</td><td>Average CPU time, reported in milliseconds, consumed by each execution of this query since the last compilation.</td></tr><tr><td>CPU Weight</td><td>MONEY</td><td>An arbitrary metric of total &quot;CPU-ness&quot;. A weight of 2 is &quot;one more&quot; than a weight of 1.</td></tr><tr><td>Total Duration</td><td>BIGINT</td><td>Total elapsed time, reported in milliseconds, consumed by all executions of this query since last compilation.</td></tr><tr><td>Avg Duration</td><td>BIGINT</td><td>Average elapsed time, reported in milliseconds, consumed by each execution of this query since the last compilation.</td></tr><tr><td>Duration Weight</td><td>MONEY</td><td>An arbitrary metric of total &quot;Duration-ness&quot;. A weight of 2 is &quot;one more&quot; than a weight of 1.</td></tr><tr><td>Total Reads</td><td>BIGINT</td><td>Total logical reads performed by this query since last compilation.</td></tr><tr><td>Average Reads</td><td>BIGINT</td><td>Average logical reads performed by each execution of this query since the last compilation.</td></tr><tr><td>Read Weight</td><td>MONEY</td><td>An arbitrary metric of &quot;Read-ness&quot;. A weight of 2 is &quot;one more&quot; than a weight of 1.</td></tr><tr><td>Total Writes</td><td>BIGINT</td><td>Total logical writes performed by this query since last compilation.</td></tr><tr><td>Average Writes</td><td>BIGINT</td><td>Average logical writes performed by each execution this query since last compilation.</td></tr><tr><td>Write Weight</td><td>MONEY</td><td>An arbitrary metric of &quot;Write-ness&quot;. A weight of 2 is &quot;one more&quot; than a weight of 1.</td></tr><tr><td>Query Type</td><td>NVARCHAR(258)</td><td>The type of query being examined. This can be &quot;Procedure&quot;, &quot;Statement&quot;, or &quot;Trigger&quot;.</td></tr><tr><td>Query Text</td><td>NVARCHAR(4000)</td><td>The text of the query. This may be truncated by either SQL Server or by sp_BlitzCache(tm) for display purposes.</td></tr><tr><td>% Executions (Type)</td><td>MONEY</td><td>Percent of executions relative to the type of query - e.g. 17.2% of all stored procedure executions.</td></tr><tr><td>% CPU (Type)</td><td>MONEY</td><td>Percent of CPU time consumed by this query for a given type of query - e.g. 22% of CPU of all stored procedures executed.</td></tr><tr><td>% Duration (Type)</td><td>MONEY</td><td>Percent of elapsed time consumed by this query for a given type of query - e.g. 12% of all statements executed.</td></tr><tr><td>% Reads (Type)</td><td>MONEY</td><td>Percent of reads consumed by this query for a given type of query - e.g. 34.2% of all stored procedures executed.</td></tr><tr><td>% Writes (Type)</td><td>MONEY</td><td>Percent of writes performed by this query for a given type of query - e.g. 43.2% of all statements executed.</td></tr><tr><td>Total Rows</td><td>BIGINT</td><td>Total number of rows returned for all executions of this query. This only applies to query level stats, not stored procedures or triggers.</td></tr><tr><td>Average Rows</td><td>MONEY</td><td>Average number of rows returned by each execution of the query.</td></tr><tr><td>Min Rows</td><td>BIGINT</td><td>The minimum number of rows returned by any execution of this query.</td></tr><tr><td>Max Rows</td><td>BIGINT</td><td>The maximum number of rows returned by any execution of this query.</td></tr><tr><td>MinGrantKB</td><td>BIGINT</td><td>The minimum memory grant the query received in kb.</td></tr><tr><td>MaxGrantKB</td><td>BIGINT</td><td>The maximum memory grant the query received in kb.</td></tr><tr><td>MinUsedGrantKB</td><td>BIGINT</td><td>The minimum used memory grant the query received in kb.</td></tr><tr><td>MaxUsedGrantKB</td><td>BIGINT</td><td>The maximum used memory grant the query received in kb.</td></tr><tr><td>MinSpills</td><td>BIGINT</td><td>The minimum amount this query has spilled to tempdb in 8k pages.</td></tr><tr><td>MaxSpills</td><td>BIGINT</td><td>The maximum amount this query has spilled to tempdb in 8k pages.</td></tr><tr><td>TotalSpills</td><td>BIGINT</td><td>The total amount this query has spilled to tempdb in 8k pages.</td></tr><tr><td>AvgSpills</td><td>BIGINT</td><td>The average amount this query has spilled to tempdb in 8k pages.</td></tr><tr><td>PercentMemoryGrantUsed</td><td>MONEY</td><td>Result of dividing the maximum grant used by the minimum granted.</td></tr><tr><td>AvgMaxMemoryGrant</td><td>MONEY</td><td>The average maximum memory grant for a query.</td></tr><tr><td># Plans</td><td>INT</td><td>The total number of execution plans found that match a given query.</td></tr><tr><td># Distinct Plans</td><td>INT</td><td>The number of distinct execution plans that match a given query. \r\nThis may be caused by running the same query across multiple databases or because of a lack of proper parameterization in the database.</td></tr><tr><td>Created At</td><td>DATETIME</td><td>Time that the execution plan was last compiled.</td></tr><tr><td>Last Execution</td><td>DATETIME</td><td>The last time that this query was executed.</td></tr><tr><td>Query Plan</td><td>XML</td><td>The query plan. Click to display a graphical plan or, if you need to patch SSMS, a pile of XML.</td></tr><tr><td>Plan Handle</td><td>VARBINARY(64)</td><td>An arbitrary identifier referring to the compiled plan this query is a part of.</td></tr><tr><td>SQL Handle</td><td>VARBINARY(64)</td><td>An arbitrary identifier referring to a batch or stored procedure that this query is a part of.</td></tr><tr><td>Query Hash</td><td>BINARY(8)</td><td>A hash of the query. Queries with the same query hash have similar logic but only differ by literal values or database.</td></tr><tr><td>Warnings</td><td>VARCHAR(MAX)</td><td>A list of individual warnings generated by this query.</td></tr></table>"
                    }
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 3,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "Configuration Parameter"
                                    },
                                    {
                                        "name": "Default Value"
                                    },
                                    {
                                        "name": "Unit of Measure"
                                    },
                                    {
                                        "name": "Description"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "Frequent Execution Threshold",
                                    "1": "100",
                                    "2": "Executions / Minute",
                                    "3": "Executions / Minute before a \"Frequent Execution Threshold\" warning is triggered."
                                },
                                {
                                    "0": "Parameter Sniffing Variance Percent",
                                    "1": "30",
                                    "2": "Percent",
                                    "3": "Variance required between min/max values and average values before a \"Parameter Sniffing\" warning is triggered. Applies to worker time and returned rows."
                                },
                                {
                                    "0": "Parameter Sniffing IO Threshold",
                                    "1": "100,000",
                                    "2": "Logical reads",
                                    "3": "Minimum number of average logical reads before parameter sniffing checks are evaluated."
                                },
                                {
                                    "0": "Cost Threshold for Parallelism Warning",
                                    "1": "10",
                                    "2": "Percent",
                                    "3": "Trigger a \"Nearly Parallel\" warning when a query's cost is within X percent of the cost threshold for parallelism."
                                },
                                {
                                    "0": "Long Running Query Warning",
                                    "1": "300",
                                    "2": "Seconds",
                                    "3": "Triggers a \"Long Running Query Warning\" when average duration, max CPU time, or max clock time is higher than this number."
                                },
                                {
                                    "0": "Unused Memory Grant Warning",
                                    "1": "10",
                                    "2": "Percent",
                                    "3": "Triggers an \"Unused Memory Grant Warning\" when a query uses >= X percent of its memory grant."
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>Configuration Parameter</th><th>Default Value</th><th>Unit of Measure</th><th>Description</th></tr><tr><td>Frequent Execution Threshold</td><td>100</td><td>Executions / Minute</td><td>Executions / Minute before a &quot;Frequent Execution Threshold&quot; warning is triggered.</td></tr><tr><td>Parameter Sniffing Variance Percent</td><td>30</td><td>Percent</td><td>Variance required between min/max values and average values before a &quot;Parameter Sniffing&quot; warning is triggered. Applies to worker time and returned rows.</td></tr><tr><td>Parameter Sniffing IO Threshold</td><td>100,000</td><td>Logical reads</td><td>Minimum number of average logical reads before parameter sniffing checks are evaluated.</td></tr><tr><td>Cost Threshold for Parallelism Warning</td><td>10</td><td>Percent</td><td>Trigger a &quot;Nearly Parallel&quot; warning when a query's cost is within X percent of the cost threshold for parallelism.</td></tr><tr><td>Long Running Query Warning</td><td>300</td><td>Seconds</td><td>Triggers a &quot;Long Running Query Warning&quot; when average duration, max CPU time, or max clock time is higher than this number.</td></tr><tr><td>Unused Memory Grant Warning</td><td>10</td><td>Percent</td><td>Triggers an &quot;Unused Memory Grant Warning&quot; when a query uses &gt;= X percent of its memory grant.</td></tr></table>"
                    }
                }
            ],
            "execution_count": 3
        },
        {
            "cell_type": "code",
            "source": [
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "62881441-6c65-4ddc-b7f9-d685c6d4c020"
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "You can run it with ExpertMode = 1 to on the more-detailed analysis of things like memory grants.  @ExportToExcel = 1 – excludes result set columns that would make Excel blow chunks when you copy/paste the results into Excel, like the execution plans. Good for sharing the plan cache metrics with other folks on your team."
            ],
            "metadata": {
                "azdata_cell_guid": "91331d3c-9388-4357-8f18-7c60e7d90d47"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "EXEC [dbo].[sp_BlitzCache]\n",
                "@SortOrder = 'all',\n",
                "@Top = 10,\n",
                "@ExportToExcel = 1"
            ],
            "metadata": {
                "azdata_cell_guid": "7514d2f1-1bd2-4a9f-9f0f-e76a70df5e8a"
            },
            "outputs": [],
            "execution_count": 2
        },
        {
            "cell_type": "markdown",
            "source": [
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "fc7c2643-d5f5-46c1-be97-96d328ee6cb6"
            }
        }
    ]
}