SSHC-Windows-SQLConfig-BasePerf.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": [
                "# Windows Server Information #\r\n",
                "\r\n",
                "## Gather Windows Log information ##\r\n",
                "\r\n",
                "**Running Gather Log Events.ps1** as an administrator in PowerShell ISE (if applicable; if not, run in PowerShell as administrator) will collect error messages from the last 30 days found in the Application, Security and/or System Event Logs. \r\n",
                "* The script will export the data to a CSV file (“export.csv”), which can be found in the C:\\temp folder on the server in which the script is run. \r\n",
                "\r\n",
                "**Power Options** – High Performance should always be selected.   \r\n",
                "\r\n",
                "* Check to make sure High Performance is the selected Power Option, and that GPO has not made changes. \r\n",
                "\r\n",
                "Check for possible new anti-virus software on the server. If found, recommend excluding SQL files from the anti-virus scan. \r\n",
                "\r\n",
                "http://support.microsoft.com/kb/309422 "
            ],
            "metadata": {
                "azdata_cell_guid": "af5fe560-e9b6-431a-9d0e-98d0f076696c"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "### SQL Server Information ###\r\n",
                "**Server Properties**\r\n",
                "1.\tEvaluate current SQL Server version. Report if there is a recent SP (<=2016) or CU (>=2017)\r\n",
                "  *  Version reference: https://sqlcollaborative.github.io/builds\r\n",
                "  *\tThere is a specific issue with TDE on early versions of 2016 that is critical. Need to make sure we are patched above CU7 2016 or CU4 2016 SP1 if using TDE.\r\n",
                "    ** https://support.microsoft.com/en-us/help/4019893/fix-restore-fails-when-you-do-backup-by-using-compression-and-checksum\r\n",
                "2.\tReview SQL Server Default locations in Server Properties. The default locations must exist and could cause a service pack or CU install to fail if they do not.\r\n",
                "3.\tSQL Server Log \r\n",
                " *\tReview the SQL Server Error Log using Error Log.sql. Document any new/suspicious activity.\r\n",
                "4.\tSystem Configuration Values \r\n",
                "  *\t**Configuration Changes History.sql** checks for system configuration changes."
            ],
            "metadata": {
                "azdata_cell_guid": "bd75df55-3d28-49bb-901c-b4779cc017ba"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "--error_log.sql\r\n",
                "--#TODO: review string filters at bottom.\r\n",
                "--Can execute in a multiserver query\r\n",
                "--Execute in Grid mode\r\n",
                "\r\n",
                "PRINT ('Error Log Output Window')\r\n",
                "use tempdb\r\n",
                "go\r\n",
                "select SYSDATETIMEOFFSET()\r\n",
                "declare @oldestdate as date, @now as datetime2(0)\r\n",
                "select @oldestdate = dateadd(week,-5, sysdatetime()), @now = sysdatetime() --Filter the time frame of the logs.\r\n",
                "\r\n",
                "select 'Getting errors since ' + cast(@oldestdate as varchar(30))\r\n",
                "\r\n",
                "--Get list of logs associated with the SQL Server (by default is 7, probably need more!) \r\n",
                "CREATE TABLE #SQLErrorLogList (\r\n",
                "    LogNumber INT NOT NULL,\r\n",
                "    LogEndDate datetime2(0) NOT NULL,\r\n",
                "    LogSize_b BIGINT NOT NULL);\r\n",
                "CREATE NONCLUSTERED INDEX IDX_CL_ell on #SQLErrorLogList (LogNumber) INCLUDE (LogEndDate);\r\n",
                "\r\n",
                "INSERT INTO #SQLErrorLogList\r\n",
                "EXEC sys.sp_enumerrorlogs;\r\n",
                "\r\n",
                "--error messages in current log\r\n",
                "create table #readerrorlog\r\n",
                "( LogDate datetime not null\r\n",
                ", LogProcessInfo varchar(255) not null \r\n",
                ", [LogMessageText] varchar(1500) not null \r\n",
                ")\r\n",
                "\r\n",
                "CREATE CLUSTERED INDEX IDX_CL_rel on #readerrorlog (LogDate);\r\n",
                "\r\n",
                "declare @lognumber int = 0, @endoflogfiles bit = 0, @maxlognumber int = 0;\r\n",
                "\r\n",
                "select @maxlognumber =   MAX(LogNumber) from #SQLErrorLogList\r\n",
                "WHILE (\r\n",
                "\t\t((Select LogEndDate from #SQLErrorLogList where @lognumber = LogNumber) > @oldestdate)\r\n",
                "\t\tand @lognumber <= @maxlognumber\r\n",
                "\t\t) \r\n",
                "BEGIN\r\n",
                "\r\n",
                "\tINSERT INTO #readerrorlog \r\n",
                "\tEXEC master.dbo.xp_readerrorlog  \r\n",
                "\t  @lognumber\t\t--current log file\r\n",
                "\t, 1\t\t\t\t\t--SQL Error Log\r\n",
                "\t, N''\t\t\t\t--search string 1, must be unicode. Leave empty on purpose, as we do filtering later on.\r\n",
                "\t, N''\t\t\t\t--search string 2, must be unicode. Leave empty on purpose, as we do filtering later on.\r\n",
                "\t, @oldestdate, @now --time filter. Should be @oldestdate < @now\r\n",
                "\t, N'desc'\t\t\t--sort\r\n",
                "\t\t\t\r\n",
                "\t--print 'including lognumber ' + str(@lognumber)\r\n",
                "\r\n",
                "\tset @lognumber = @lognumber + 1\t\r\n",
                "END\r\n",
                "GO\r\n",
                "\r\n",
                "CREATE NONCLUSTERED INDEX IDX_NC_rel on #readerrorlog (Logdate desc, [LogMessageText]) INCLUDE( LogProcessInfo)\r\n",
                "\r\n",
                "GO\r\n",
                "--order of servers in a multiserver query is not determinant\r\n",
                "\r\n",
                "--Raw error list\r\n",
                "select * from #readerrorlog \r\n",
                "where  1=1\r\n",
                "and (\t\r\n",
                "\tLogMessageText like '%error%'\r\n",
                "or\tLogMessageText like '%failure%'\r\n",
                "or\tLogMessageText like '%failed%'\r\n",
                "or\tLogMessageText like '%corrupt%'\r\n",
                ")\r\n",
                "and LogMessageText not like '%without errors%'\r\n",
                "and LogMessageText not like '%returned no errors%'\r\n",
                "and LogMessageText not like 'Registry startup parameters:%'\r\n",
                "and LogMessageText not like '%informational%'\r\n",
                "and LogMessageText not like '%found 0 errors%'\r\n",
                "order by LogDate desc;\r\n",
                "\r\n",
                "--Aggregate error counts\r\n",
                "select LogMessageText, LogProcessInfo, ErrorCount = count(LogDate), MostRecentOccurrence = max(LogDate) \r\n",
                "from #readerrorlog \r\n",
                "where  1=1\r\n",
                "and (\t\r\n",
                "\tLogMessageText like '%error%'\r\n",
                "or\tLogMessageText like '%failure%'\r\n",
                "or\tLogMessageText like '%failed%'\r\n",
                "or\tLogMessageText like '%corrupt%'\r\n",
                ")\r\n",
                "and LogMessageText not like '%without errors%'\r\n",
                "and LogMessageText not like '%returned no errors%'\r\n",
                "and LogMessageText not like 'Registry startup parameters:%'\r\n",
                "and LogMessageText not like '%informational%'\r\n",
                "and LogMessageText not like '%found 0 errors%'\r\n",
                "group by LogMessageText, LogProcessInfo\r\n",
                "order by count(LogDate) desc, max(LogDate) desc;\r\n",
                "\r\n",
                "SELECT Reboots = LogDate FROM #readerrorlog WHERE LogMessageText like 'Registry startup parameters:%'\r\n",
                "ORDER BY LogDate desc;\r\n",
                "GO\r\n",
                "\r\n",
                "drop table #readerrorlog\r\n",
                "drop table #SQLErrorLogList"
            ],
            "metadata": {
                "azdata_cell_guid": "d9fec979-ef8b-4393-9f99-674d26682f6f",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "--Configuration Changes History.sql\r\n",
                "--Based on the configuration changes history report in SSMS\r\n",
                "PRINT('Configuration Change History Output Window')\r\n",
                "exec sp_executesql @stmt=N'begin try\r\n",
                "declare @enable int;\r\n",
                "select @enable = convert(int,value_in_use) from sys.configurations where name = ''default trace enabled''\r\n",
                "if @enable = 1 --default trace is enabled\r\n",
                "begin\r\n",
                "        declare @d1 datetime;\r\n",
                "        declare @diff int;  \r\n",
                "        declare @curr_tracefilename varchar(500); \r\n",
                "        declare @base_tracefilename varchar(500); \r\n",
                "        declare @indx int ;\r\n",
                "        declare @temp_trace table (\r\n",
                "                textdata nvarchar(MAX) collate database_default \r\n",
                "        ,       login_name sysname collate database_default\r\n",
                "        ,       start_time datetime\r\n",
                "        ,       event_class int\r\n",
                "        );\r\n",
                "        \r\n",
                "        select @curr_tracefilename = path from sys.traces where is_default = 1 ; \r\n",
                "        \r\n",
                "        set @curr_tracefilename = reverse(@curr_tracefilename)\r\n",
                "        select @indx  = PATINDEX(''%\\%'', @curr_tracefilename) \r\n",
                "        set @curr_tracefilename = reverse(@curr_tracefilename)\r\n",
                "        set @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) - @indx) + ''\\log.trc'';\r\n",
                "        \r\n",
                "        insert into @temp_trace\r\n",
                "        select TextData\r\n",
                "        ,       LoginName\r\n",
                "        ,       StartTime\r\n",
                "        ,       EventClass \r\n",
                "        from ::fn_trace_gettable( @base_tracefilename, default ) \r\n",
                "        where ((EventClass = 22 and Error = 15457) or (EventClass = 116 and TextData like ''%TRACEO%(%''))\r\n",
                "        \r\n",
                "        select @d1 = min(start_time) from @temp_trace\r\n",
                "        \r\n",
                "        --set @diff= datediff(hh,@d1,getdate())\r\n",
                "        --set @diff=@diff/24; \r\n",
                "\r\n",
                "        select --(row_number() over (order by start_time desc))%2 as l1\r\n",
                "                @d1 as TraceStartDate\r\n",
                "        ,       start_time as EventDate\r\n",
                "\t\t,       case event_class \r\n",
                "                        when 116 then ''Trace Flag '' + substring(textdata,patindex(''%(%'',textdata),len(textdata) - patindex(''%(%'',textdata) + 1) \r\n",
                "                        when 22 then substring(textdata,58,patindex(''%changed from%'',textdata)-60) \r\n",
                "                end as config_option\r\n",
                "        ,       login_name\r\n",
                "        ,       case event_class \r\n",
                "                        when 116 then ''--''\r\n",
                "                        when 22 then substring(substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata))\r\n",
                "                                                                ,patindex(''%changed from%'',substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata)))+13\r\n",
                "                                                                ,patindex(''%to%'',substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata))) - patindex(''%from%'',substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata))) - 6) \r\n",
                "                end as old_value\r\n",
                "        ,       case event_class \r\n",
                "                        when 116 then substring(textdata,patindex(''%TRACE%'',textdata)+5,patindex(''%(%'',textdata) - patindex(''%TRACE%'',textdata)-5)\r\n",
                "                        when 22 then substring(substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata))\r\n",
                "                                                                ,patindex(''%to%'',substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata)))+3\r\n",
                "                                                                , patindex(''%. Run%'',substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata))) - patindex(''%to%'',substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata))) - 3) \r\n",
                "                end as new_value\r\n",
                "        from @temp_trace \r\n",
                "        order by start_time desc\r\n",
                "end else \r\n",
                "begin \r\n",
                "        select top 0  1  as l1, 1 as difference,1 as date , 1 as config_option,1 as start_time , 1 as login_name, 1 as old_value, 1 as new_value\r\n",
                "end\r\n",
                "end try \r\n",
                "begin catch\r\n",
                "select  ERROR_NUMBER() as Error_Number\r\n",
                ",       ERROR_SEVERITY() as date \r\n",
                ",       ERROR_STATE() as config_option\r\n",
                ",       1 as start_time \r\n",
                ",       ERROR_MESSAGE() as login_name\r\n",
                ",       1 as old_value, 1 as new_value\r\n",
                "end catch',@params=N''"
            ],
            "metadata": {
                "azdata_cell_guid": "247beb42-5f8e-475e-b384-d8ef38f5392c",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Performance ##\r\n",
                "1.\tRun **Page Life Expectancy.sql**\r\n",
                "  * Does SQL have enough memory, based on PLE, Churn, and the Target vs Total?\r\n",
                "2.\tReview CPU Utilization.Sql script and look for any recent +90% periods.\r\n",
                "3.\tAre there any recent Memory Dumps?\r\n",
                "  *\tRunning **Find Memory Mini Dumps.sql** will let you know if SQL is having issues or has had any  “mini dump crashes” in the recent past."
            ],
            "metadata": {
                "azdata_cell_guid": "c86fb8d5-551b-4746-a9e5-d658ebaac71a"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "--Page Life Expectancy.sql\r\n",
                "PRINT('Page Life Expectancy')\r\n",
                "select \r\n",
                "\tp.InstanceName\r\n",
                ",\tc.Version \r\n",
                ",\t'LogicalCPUCount'\t\t= os.cpu_count\r\n",
                ",\tOS_Physical_Mem_MB = os.[Server Physical Mem (MB)] -- SQL2012+ only\r\n",
                ",\tMin_Server_Mem_MB = c.[Min_Server_Mem_MB]\r\n",
                ",\tMax_Server_Mem_MB = c.[Max_Server_Mem_MB] --2147483647 means unlimited, just like it shows in SSMS\r\n",
                ",\tp.PLE_s --300s is only an arbitrary rule for smaller memory servers (<16gb), for larger, it should be baselined and measured.\r\n",
                ",\t'Churn (MB/s)'\t\t\t=\tcast((os.Total_Server_Memory_GB*1024.)/NULLIF(p.PLE_s,0) as decimal(19,2))\r\n",
                ",\tOS_Available_physical_mem_GB = (SELECT cast(available_physical_memory_kb / 1024. / 1024. as decimal(19,2)) from sys.dm_os_sys_memory) \r\n",
                ",\tSQL_Physical_memory_in_use_GB = (SELECT cast(physical_memory_in_use_kb / 1024. / 1024. as decimal(19,2)) from sys.dm_os_process_memory)\r\n",
                ",\tos.Total_Server_Memory_GB --May be more or less than memory_in_use \r\n",
                ",\tos.Target_Server_Memory_GB\t\r\n",
                ",\tTarget_vs_Total = CASE WHEN os.Total_Server_Memory_GB < os.Target_Server_Memory_GB\t \r\n",
                "\t\t\t\t\t\t\tTHEN 'Target >= Total. SQL wants more memory than it has, or is building up to that point.'\r\n",
                "\t\t\t\t\t\t\tELSE 'Total >= Target. SQL has enough memory to do what it wants.' END\r\n",
                ",\tsi.LPIM -- Works on SQL 2016 SP1, 2012 SP4+\r\n",
                "from(\r\n",
                "\tselect \r\n",
                "\t\tInstanceName = @@SERVERNAME  \r\n",
                "\t,\tPLE_s\t=\tcase when object_name like '%Buffer Manager%' and counter_name = 'Page life expectancy'  then cntr_value end  --This only looks at the overall buffer pool, not individual NUMA nodes. https://www.sqlskills.com/blogs/paul/page-life-expectancy-isnt-what-you-think/\r\n",
                "\tfrom sys.dm_os_performance_counters \r\n",
                "\twhere object_name like '%Buffer Manager%' and counter_name = 'Page life expectancy'\r\n",
                ")  as p\r\n",
                "inner join (select 'InstanceName' = @@SERVERNAME, Version = @@VERSION, \r\n",
                "\t\t\tMin_Server_Mem_MB  = max(case when name = 'min server memory (MB)' then convert(bigint, value_in_use) end) ,\r\n",
                "\t\t\tMax_Server_Mem_MB = max(case when name = 'max server memory (MB)' then convert(bigint, value_in_use) end) \r\n",
                "\t\t\tfrom sys.configurations) as c on p.InstanceName = c.InstanceName\r\n",
                "inner join (SELECT 'InstanceName' = @@SERVERNAME \r\n",
                "\t\t\t, cpu_count , hyperthread_ratio AS 'HyperthreadRatio',\r\n",
                "\t\t\tcpu_count/hyperthread_ratio AS 'PhysicalCPUCount'\r\n",
                "\t\t\t, 'Server Physical Mem (MB)' = cast(physical_memory_kb/1024. as decimal(19,2))   -- SQL2012+ only\r\n",
                "\t\t\t, \tTotal_Server_Memory_GB = convert(decimal(19,3), committed_kb / 1024. / 1024.)\r\n",
                "\t\t\t,\tTarget_Server_Memory_GB = convert(decimal(19,3), committed_target_kb / 1024. / 1024.)\r\n",
                "\t\t\tFROM sys.dm_os_sys_info ) as os\r\n",
                "on c.InstanceName=os.InstanceName\r\n",
                "\r\n",
                "-- Works on SQL 2016 SP1, 2012 SP4+\r\n",
                "cross apply (select LPIM = CASE sql_memory_model_Desc \r\n",
                "\t\t\t\t\tWHEN  'Conventional' THEN 'Lock Pages in Memory privilege is not granted'\r\n",
                "\t\t\t\t\tWHEN 'LOCK_PAGES' THEN 'Lock Pages in Memory privilege is granted'\r\n",
                "\t\t\t\t\tWHEN 'LARGE_PAGES' THEN 'Lock Pages in Memory privilege is granted in Enterprise mode with Trace Flag 834 ON'\r\n",
                "\t\t\t\t\tEND from sys.dm_os_sys_info \r\n",
                "\t\t\t\t) as si\r\n",
                " \r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "44573d60-f2fa-43d4-a02d-52f3ba5f24e4",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Page Life Expectancy"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Warning: Null value is eliminated by an aggregate or other SET operation."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.047"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 13,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "InstanceName"
                                    },
                                    {
                                        "name": "Version"
                                    },
                                    {
                                        "name": "LogicalCPUCount"
                                    },
                                    {
                                        "name": "OS_Physical_Mem_MB"
                                    },
                                    {
                                        "name": "Min_Server_Mem_MB"
                                    },
                                    {
                                        "name": "Max_Server_Mem_MB"
                                    },
                                    {
                                        "name": "PLE_s"
                                    },
                                    {
                                        "name": "Churn (MB/s)"
                                    },
                                    {
                                        "name": "OS_Available_physical_mem_GB"
                                    },
                                    {
                                        "name": "SQL_Physical_memory_in_use_GB"
                                    },
                                    {
                                        "name": "Total_Server_Memory_GB"
                                    },
                                    {
                                        "name": "Target_Server_Memory_GB"
                                    },
                                    {
                                        "name": "Target_vs_Total"
                                    },
                                    {
                                        "name": "LPIM"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "CORP-2TK94207B1\\SQL2K19",
                                    "1": "Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) \n\tSep 24 2019 13:48:23 \n\tCopyright (C) 2019 Microsoft Corporation\n\tDeveloper Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 19041: )\n",
                                    "2": "8",
                                    "3": "32582.85",
                                    "4": "16",
                                    "5": "8000",
                                    "6": "309",
                                    "7": "1.09",
                                    "8": "21.10",
                                    "9": "0.15",
                                    "10": "0.329",
                                    "11": "7.813",
                                    "12": "Target >= Total. SQL wants more memory than it has, or is building up to that point.",
                                    "13": "Lock Pages in Memory privilege is not granted"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>InstanceName</th><th>Version</th><th>LogicalCPUCount</th><th>OS_Physical_Mem_MB</th><th>Min_Server_Mem_MB</th><th>Max_Server_Mem_MB</th><th>PLE_s</th><th>Churn (MB/s)</th><th>OS_Available_physical_mem_GB</th><th>SQL_Physical_memory_in_use_GB</th><th>Total_Server_Memory_GB</th><th>Target_Server_Memory_GB</th><th>Target_vs_Total</th><th>LPIM</th></tr>",
                            "<tr><td>CORP-2TK94207B1\\SQL2K19</td><td>Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) \n\tSep 24 2019 13:48:23 \n\tCopyright (C) 2019 Microsoft Corporation\n\tDeveloper Edition (64-bit) on Windows 10 Enterprise 10.0 &lt;X64&gt; (Build 19041: )\n</td><td>8</td><td>32582.85</td><td>16</td><td>8000</td><td>309</td><td>1.09</td><td>21.10</td><td>0.15</td><td>0.329</td><td>7.813</td><td>Target &gt;= Total. SQL wants more memory than it has, or is building up to that point.</td><td>Lock Pages in Memory privilege is not granted</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 13
        },
        {
            "cell_type": "code",
            "source": [
                "--CPU Utilization.sql\r\n",
                "--This is simple use of the ring_buffer for historical CPU, goes back a little over 4 hours.\r\n",
                "-- for more CPU and Memory, look at toolbox/sys_dm_os_ring_buffers.sql\r\n",
                "PRINT('CPU Utilization Output Window')\r\n",
                "select\r\n",
                "\tAvg_SystemIdle_Pct\t\t\t\t=\tAVG( record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') )\r\n",
                ",\tAvg_SQLProcessUtilization_Pct\t=\tAVG( record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') )\r\n",
                "      from (\r\n",
                "            select timestamp, convert(xml, record) as record\r\n",
                "            from sys.dm_os_ring_buffers\r\n",
                "            where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'\r\n",
                "            and record like '%<SystemHealth>%') as x\r\n",
                "\r\n",
                "declare @ts_now bigint\r\n",
                "--select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info\r\n",
                "select @ts_now = cpu_ticks / (cpu_ticks/ms_ticks) from sys.dm_os_sys_info;\r\n",
                "select\trecord_id\r\n",
                "\t,\tEventTime\t\t\t\t=  dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) \r\n",
                "\t,\tSQLProcessUtilization\r\n",
                "\t,\tSystemIdle\r\n",
                "\t,\tOtherProcessUtilization\t= 100 - SystemIdle - SQLProcessUtilization \r\n",
                "from (\r\n",
                "      select\r\n",
                "            record_id\t\t\t\t=\trecord.value('(./Record/@id)[1]', 'int')\r\n",
                "        ,\tSystemIdle\t\t\t\t=\trecord.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') \r\n",
                "        ,\tSQLProcessUtilization\t=\trecord.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') \r\n",
                "        ,\ttimestamp\r\n",
                "      from (\r\n",
                "            select timestamp, convert(xml, record) as record\r\n",
                "            from sys.dm_os_ring_buffers\r\n",
                "            where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'\r\n",
                "            and record like '%<SystemHealth>%') as x\r\n",
                "      ) as y\r\n",
                "order by record_id desc\r\n",
                "\r\n",
                "\r\n",
                "--http://sqlblog.com/blogs/ben_nevarez/archive/2009/07/26/getting-cpu-utilization-data-from-sql-server.aspx"
            ],
            "metadata": {
                "azdata_cell_guid": "bec4e6cf-5bc0-4707-aa75-f265ae72f514",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "--Find Memory Mini Dumps.sql \r\n",
                "-- Get information on location, time and size of any memory dumps from SQL Server  \r\n",
                "-- Only SQL 2008R2+\r\n",
                "PRINT('Fine Memory Mini Dump Output Window')\r\n",
                "SELECT [filename], creation_time, size_in_bytes/1048576.0 AS [Size (MB)]\r\n",
                "FROM sys.dm_server_memory_dumps \r\n",
                "ORDER BY creation_time DESC OPTION (RECOMPILE);"
            ],
            "metadata": {
                "azdata_cell_guid": "35a70b92-4ae6-4be6-8130-4fe498b82db2"
            },
            "outputs": [],
            "execution_count": null
        }
    ]
}