BPCheck/BPCheck.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": "BP Check READ ME - http://aka.ms/BPCheck;\r\n\r\nChecks SQL Server in scope for Performance issues and some of most common skewed Best Practices. \r\n\r\n**SUPPORTABILITY:**    \r\nSupports SQL Server (starting with SQL Server 2008) and Azure SQL Database Managed Instance. \r\nNot all sections support Azure SQL Database single database or Elastic Pool.\r\n\r\n**DISCLAIMER:**   \r\nThis code is not supported under any Microsoft standard support program or service.\r\nThis code and information are provided \"AS IS\" without warranty of any kind, either expressed or implied.\r\nThe entire risk arising out of the use or performance of the script and documentation remains with you. \r\nFurthermore, Microsoft or the author shall not be liable for any damages you may sustain by using this information, whether direct, \r\nindirect, special, incidental or consequential, including, without limitation, damages for loss of business profits, business interruption, loss of business information \r\nor other pecuniary loss even if it has been advised of the possibility of such damages.\r\nRead all the implementation and usage notes thoroughly.\r\n\r\n**REQUIREMENTS:**\r\nOnly a sysadmin/local host admin will be able to perform all checks.     \r\n\r\nIf you want to perform all checks under non-sysadmin credentials, then that login must be:\r\n-\tMember of serveradmin server role or have the ALTER SETTINGS server permission; \r\n-\tMember of MSDB SQLAgentOperatorRole role, or have SELECT permission on the sysalerts table in MSDB;\r\n-\tGranted EXECUTE permissions on the following extended sprocs to run checks: sp_OACreate, sp_OADestroy, sp_OAGetErrorInfo, xp_enumerrorlogs, xp_fileexist and xp_regenumvalues;\r\n-\tGranted EXECUTE permissions on xp_msver;\r\n-\tGranted the VIEW SERVER STATE permission;\r\n-\tGranted the VIEW DATABASE STATE permission;\r\n-\tGranted EXECUTE permissions on xp_cmdshell or a xp_cmdshell proxy account should exist to run checks that access disk or OS security configurations.\r\n-\tMember of securityadmin role, or have EXECUTE permissions on sp_readerrorlog. \r\n\r\nPowershell must be installed to run checks that access disk configurations, as well as allow execution of remote signed or unsigned scripts.",
            "metadata": {}
        },
        {
            "cell_type": "markdown",
            "source": "Check pre-requisites for all checks",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\n\r\nDECLARE @sqlcmd NVARCHAR(max), @params NVARCHAR(600), @sqlmajorver int\r\n\r\nSELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);\r\n\r\nIF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 0)\r\nBEGIN\r\n\tPRINT 'WARNING: Only a sysadmin can run ALL the checks'\r\nEND\r\nELSE\r\nBEGIN\r\n    PRINT 'No issues found while checking pre-requisites to run checks: user is sysadmin'\r\nEND;\r\n\r\nIF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 0)\r\nBEGIN\r\n\tDECLARE @pid int, @pname sysname, @msdbpid int, @masterpid int\r\n\tDECLARE @permstbl TABLE ([name] sysname);\r\n\tDECLARE @permstbl_msdb TABLE ([id] tinyint IDENTITY(1,1), [perm] tinyint)\r\n\t\r\n\tSET @params = '@msdbpid_in int'\r\n\r\n\tSELECT @pid = principal_id, @pname=name FROM master.sys.server_principals (NOLOCK) WHERE sid = SUSER_SID();\r\n\r\n\tSELECT @masterpid = principal_id FROM master.sys.database_principals (NOLOCK) WHERE sid = SUSER_SID();\r\n\r\n\tSELECT @msdbpid = principal_id FROM msdb.sys.database_principals (NOLOCK) WHERE sid = SUSER_SID();\r\n\r\n\t-- Perms 1\r\n\tIF (ISNULL(IS_SRVROLEMEMBER(N'serveradmin'), 0) <> 1) AND ((SELECT COUNT(l.name)\r\n\t\tFROM master.sys.server_permissions p (NOLOCK) INNER JOIN master.sys.server_principals l (NOLOCK)\r\n\t\tON p.grantee_principal_id = l.principal_id\r\n\t\t\tAND p.class = 100 -- Server\r\n\t\t\tAND p.state IN ('G', 'W') -- Granted or Granted with Grant\r\n\t\t\tAND l.is_disabled = 0\r\n\t\t\tAND p.permission_name = 'ALTER SETTINGS'\r\n\t\t\tAND QUOTENAME(l.name) = QUOTENAME(@pname)) = 0)\r\n\tBEGIN\r\n\t\tRAISERROR('WARNING: If not sysadmin, then you must be a member of serveradmin server role or have the ALTER SETTINGS server permission. Exiting...', 16, 1, N'serveradmin')\r\n\t\tRETURN\r\n\tEND\r\n\tELSE IF (ISNULL(IS_SRVROLEMEMBER(N'serveradmin'), 0) <> 1) AND ((SELECT COUNT(l.name)\r\n\t\tFROM master.sys.server_permissions p (NOLOCK) INNER JOIN sys.server_principals l (NOLOCK)\r\n\t\tON p.grantee_principal_id = l.principal_id\r\n\t\t\tAND p.class = 100 -- Server\r\n\t\t\tAND p.state IN ('G', 'W') -- Granted or Granted with Grant\r\n\t\t\tAND l.is_disabled = 0\r\n\t\t\tAND p.permission_name = 'VIEW SERVER STATE'\r\n\t\t\tAND QUOTENAME(l.name) = QUOTENAME(@pname)) = 0)\r\n\tBEGIN\r\n\t\tRAISERROR('WARNING: If not sysadmin, then you must be a member of serveradmin server role or granted the VIEW SERVER STATE permission. Exiting...', 16, 1, N'serveradmin')\r\n\t\tRETURN\r\n\tEND\r\n    ELSE\r\n    BEGIN\r\n        RAISERROR('INFORMATION: No issues found while checking for sysadmin pre-requisites to run checks', 10, 1, N'serveradmin')\r\n    END;\r\n\r\n\t-- Perms 2\r\n\tINSERT INTO @permstbl\r\n\tSELECT a.name\r\n\tFROM master.sys.all_objects a (NOLOCK) INNER JOIN master.sys.database_permissions b (NOLOCK) ON a.[OBJECT_ID] = b.major_id\r\n\tWHERE a.type IN ('P', 'X') AND b.grantee_principal_id <>0 \r\n\tAND b.grantee_principal_id <> 2\r\n\tAND b.grantee_principal_id = @masterpid;\r\n\r\n\tINSERT INTO @permstbl_msdb ([perm])\r\n\tEXECUTE sp_executesql N'USE msdb; SELECT COUNT([name]) \r\nFROM msdb.sys.sysusers (NOLOCK) WHERE [uid] IN (SELECT [groupuid] \r\n\tFROM msdb.sys.sysmembers (NOLOCK) WHERE [memberuid] = @msdbpid_in) \r\nAND [name] = ''SQLAgentOperatorRole''', @params, @msdbpid_in = @msdbpid;\r\n\r\n\tINSERT INTO @permstbl_msdb ([perm])\r\n\tEXECUTE sp_executesql N'USE msdb; SELECT COUNT(dp.grantee_principal_id)\r\nFROM msdb.sys.tables AS tbl (NOLOCK)\r\nINNER JOIN msdb.sys.database_permissions AS dp (NOLOCK) ON dp.major_id=tbl.object_id AND dp.class=1\r\nINNER JOIN msdb.sys.database_principals AS grantor_principal (NOLOCK) ON grantor_principal.principal_id = dp.grantor_principal_id\r\nINNER JOIN msdb.sys.database_principals AS grantee_principal (NOLOCK) ON grantee_principal.principal_id = dp.grantee_principal_id\r\nWHERE dp.state = ''G''\r\n\tAND dp.grantee_principal_id = @msdbpid_in\r\n\tAND dp.type = ''SL''', @params, @msdbpid_in = @msdbpid;\r\n\r\n\tIF (SELECT [perm] FROM @permstbl_msdb WHERE [id] = 1) = 0 AND (SELECT [perm] FROM @permstbl_msdb WHERE [id] = 2) = 0\r\n\tBEGIN\r\n\t\tRAISERROR('WARNING: If not sysadmin, then you must be a member of MSDB SQLAgentOperatorRole role, or have SELECT permission on the sysalerts table in MSDB to run full scope of checks', 16, 1, N'msdbperms')\r\n\t\t--RETURN\r\n    END\r\n\tELSE IF (ISNULL(IS_SRVROLEMEMBER(N'securityadmin'), 0) <> 1) AND ((SELECT COUNT([name]) FROM @permstbl WHERE [name] = 'xp_enumerrorlogs') = 0 OR (SELECT COUNT([name]) FROM @permstbl WHERE [name] = 'sp_readerrorlog') = 0 OR (SELECT COUNT([name]) FROM @permstbl WHERE [name] = 'xp_readerrorlog') = 0)\r\n\tBEGIN\r\n\t\tRAISERROR('WARNING: If not sysadmin, then you must be a member of the securityadmin server role, or have EXECUTE permission on the following extended sprocs to run full scope of checks: xp_enumerrorlogs, xp_readerrorlog, sp_readerrorlog', 16, 1, N'secperms')\r\n\t\t--RETURN\r\n\tEND\r\n\tELSE IF (SELECT COUNT([name]) FROM @permstbl WHERE [name] = 'xp_cmdshell') = 0 OR (SELECT COUNT(credential_id) FROM master.sys.credentials WHERE name = '##xp_cmdshell_proxy_account##') = 0\r\n\tBEGIN\r\n\t\tRAISERROR('WARNING: If not sysadmin, then you must be granted EXECUTE permissions on xp_cmdshell and a xp_cmdshell proxy account should exist to run full scope of checks', 16, 1, N'xp_cmdshellproxy')\r\n\t\t--RETURN\r\n\tEND\r\n\tELSE IF (SELECT COUNT([name]) FROM @permstbl WHERE [name] = 'xp_fileexist') = 0 OR\r\n\t\t(SELECT COUNT([name]) FROM @permstbl WHERE [name] = 'sp_OAGetErrorInfo') = 0 OR\r\n\t\t(SELECT COUNT([name]) FROM @permstbl WHERE [name] = 'sp_OACreate') = 0 OR\r\n\t\t(SELECT COUNT([name]) FROM @permstbl WHERE [name] = 'sp_OADestroy') = 0 OR\r\n\t\t(SELECT COUNT([name]) FROM @permstbl WHERE [name] = 'xp_regenumvalues') = 0 OR\r\n\t\t(SELECT COUNT([name]) FROM @permstbl WHERE [name] = 'xp_regread') = 0 OR \r\n\t\t(SELECT COUNT([name]) FROM @permstbl WHERE [name] = 'xp_instance_regread') = 0 OR\r\n\t\t(SELECT COUNT([name]) FROM @permstbl WHERE [name] = 'xp_servicecontrol') = 0 \r\n\tBEGIN\r\n\t\tRAISERROR('WARNING: Must be a granted EXECUTE permissions on the following extended sprocs to run full scope of checks: sp_OACreate, sp_OADestroy, sp_OAGetErrorInfo, xp_fileexist, xp_regread, xp_instance_regread, xp_servicecontrol and xp_regenumvalues', 16, 1, N'extended_sprocs')\r\n\t\t--RETURN\r\n\tEND\r\n\tELSE IF (SELECT COUNT([name]) FROM @permstbl WHERE [name] = 'xp_msver') = 0 AND @sqlmajorver < 11\r\n\tBEGIN\r\n\t\tRAISERROR('WARNING: Must be granted EXECUTE permissions on xp_msver to run full scope of checks', 16, 1, N'extended_sprocs')\r\n\t\t--RETURN\r\n\tEND\r\n    ELSE\r\n    BEGIN\r\n        RAISERROR('INFORMATION: No issues found while checking for granular pre-requisites to run checks', 10, 1, N'extended_sprocs')\r\n\t\t--RETURN\r\n    END\r\nEND;",
            "metadata": {},
            "outputs": [],
            "execution_count": 22
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\n\r\nDECLARE @src VARCHAR(255), @desc VARCHAR(255), @psavail VARCHAR(20), @psver tinyint, @masterpid int\r\nDECLARE @agt smallint, @ole smallint, @sao smallint, @xcmd smallint\r\nDECLARE @ErrorMessage NVARCHAR(4000)\r\nDECLARE @permstbl TABLE ([name] sysname);\r\n\r\nSELECT @masterpid = principal_id FROM master.sys.database_principals (NOLOCK) WHERE sid = SUSER_SID()\r\n\r\nINSERT INTO @permstbl\r\nSELECT a.name\r\nFROM master.sys.all_objects a (NOLOCK) INNER JOIN master.sys.database_permissions b (NOLOCK) ON a.[OBJECT_ID] = b.major_id\r\nWHERE a.type IN ('P', 'X') AND b.grantee_principal_id <>0 \r\nAND b.grantee_principal_id <> 2\r\nAND b.grantee_principal_id = @masterpid;\r\n\r\nIF ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1 -- Is sysadmin\r\n    OR ((ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1 \r\n        AND (SELECT COUNT(credential_id) FROM sys.credentials WHERE name = '##xp_cmdshell_proxy_account##') > 0) -- Is not sysadmin but proxy account exists\r\n        AND (SELECT COUNT(l.name)\r\n        FROM sys.server_permissions p JOIN sys.server_principals l \r\n        ON p.grantee_principal_id = l.principal_id\r\n            AND p.class = 100 -- Server\r\n            AND p.state IN ('G', 'W') -- Granted or Granted with Grant\r\n            AND l.is_disabled = 0\r\n            AND p.permission_name = 'ALTER SETTINGS'\r\n            AND QUOTENAME(l.name) = QUOTENAME(USER_NAME())) = 0) -- Is not sysadmin but has alter settings permission\r\n    OR ((ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1 \r\n        AND ((SELECT COUNT([name]) FROM @permstbl WHERE [name] = 'xp_regread') > 0 AND\r\n        (SELECT COUNT([name]) FROM @permstbl WHERE [name] = 'xp_cmdshell') > 0)))\r\nBEGIN\r\n    DECLARE @pstbl_avail TABLE ([KeyExist] int)\r\n    BEGIN TRY\r\n        INSERT INTO @pstbl_avail\r\n        EXEC master.sys.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\\Microsoft\\PowerShell\\1' -- check if Powershell is installed\r\n    END TRY\r\n    BEGIN CATCH\r\n        SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;\r\n        SELECT @ErrorMessage = 'Could not determine if Powershell is installed - Error raised in TRY block. ' + ERROR_MESSAGE()\r\n        RAISERROR (@ErrorMessage, 16, 1);\r\n    END CATCH\r\n\r\n    SELECT @sao = CAST([value] AS smallint) FROM sys.configurations (NOLOCK) WHERE [name] = 'show advanced options'\r\n    SELECT @xcmd = CAST([value] AS smallint) FROM sys.configurations (NOLOCK) WHERE [name] = 'xp_cmdshell'\r\n    SELECT @ole = CAST([value] AS smallint) FROM sys.configurations (NOLOCK) WHERE [name] = 'Ole Automation Procedures'\r\n\r\n    RAISERROR ('Configuration options set for Powershell enablement verification', 10, 1) WITH NOWAIT\r\n    IF @sao = 0\r\n    BEGIN\r\n        EXEC sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE;\r\n    END\r\n    IF @xcmd = 0\r\n    BEGIN\r\n        EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE WITH OVERRIDE;\r\n    END\r\n    IF @ole = 0\r\n    BEGIN\r\n        EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE WITH OVERRIDE;\r\n    END\r\n    \r\n    IF (SELECT [KeyExist] FROM @pstbl_avail) = 1\r\n    BEGIN\r\n        DECLARE @psavail_output TABLE ([PS_OUTPUT] VARCHAR(2048));\r\n        INSERT INTO @psavail_output\r\n        EXEC master.dbo.xp_cmdshell N'%WINDIR%\\System32\\WindowsPowerShell\\v1.0\\powershell.exe -Command \"Get-ExecutionPolicy\"'\r\n    \r\n        SELECT @psavail = [PS_OUTPUT] FROM @psavail_output WHERE [PS_OUTPUT] IS NOT NULL;\r\n    END\r\n    ELSE\r\n    BEGIN\r\n        RAISERROR ('WARNING: Powershell is not installed. Install WinRM to proceed with PS based checks',16,1);\r\n    END\r\n            \r\n    IF (@psavail IS NOT NULL AND @psavail NOT IN ('RemoteSigned','Unrestricted'))\r\n    RAISERROR ('WARNING: Execution of Powershell scripts is disabled on this system.\r\nTo change the execution policy, type the following command in Powershell console: Set-ExecutionPolicy RemoteSigned\r\nThe Set-ExecutionPolicy cmdlet enables you to determine which Windows PowerShell scripts (if any) will be allowed to run on your computer. Windows PowerShell has four different execution policies:\r\nRestricted - No scripts can be run. Windows PowerShell can be used only in interactive mode.\r\nAllSigned - Only scripts signed by a trusted publisher can be run.\r\nRemoteSigned - Downloaded scripts must be signed by a trusted publisher before they can be run.\r\nUnrestricted - No restrictions; all Windows PowerShell scripts can be run; REQUIRED by BP Check.',16,1);\r\n\r\n    IF (@psavail IS NOT NULL AND @psavail IN ('RemoteSigned','Unrestricted'))\r\n    BEGIN\r\n        RAISERROR ('INFORMATION: Powershell is installed and enabled for script execution', 10, 1) WITH NOWAIT\r\n        \r\n        DECLARE @psver_output TABLE ([PS_OUTPUT] VARCHAR(1024));\r\n        INSERT INTO @psver_output\r\n        EXEC master.dbo.xp_cmdshell N'%WINDIR%\\System32\\WindowsPowerShell\\v1.0\\powershell.exe -Command \"Get-Host | Format-Table -Property Version\"'\r\n    \r\n        -- Gets PS version, as commands issued to PS v1 do not support -File\r\n        SELECT @psver = ISNULL(LEFT([PS_OUTPUT],1),2) FROM @psver_output WHERE [PS_OUTPUT] IS NOT NULL AND ISNUMERIC(LEFT([PS_OUTPUT],1)) = 1;\r\n        \r\n        SET @ErrorMessage = 'INFORMATION: Installed Powershell is version ' + CONVERT(CHAR(1), @psver) + ''\r\n        PRINT @ErrorMessage\r\n    END;\r\n    \r\n    IF @xcmd = 0\r\n    BEGIN\r\n        EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE WITH OVERRIDE;\r\n    END\r\n    IF @ole = 0\r\n    BEGIN\r\n        EXEC sp_configure 'Ole Automation Procedures', 0; RECONFIGURE WITH OVERRIDE;\r\n    END\r\n    IF @sao = 0\r\n    BEGIN\r\n        EXEC sp_configure 'show advanced options', 0; RECONFIGURE WITH OVERRIDE;\r\n    END;\r\nEND\r\nELSE\r\nBEGIN\r\n    RAISERROR ('WARNING: Missing permissions for Powershell enablement verification', 16, 1) WITH NOWAIT\r\nEND;",
            "metadata": {},
            "outputs": [],
            "execution_count": 24
        },
        {
            "cell_type": "markdown",
            "source": "Information section",
            "metadata": {}
        },
        {
            "cell_type": "markdown",
            "source": "Uptime subsection",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\n\r\nDECLARE @sqlcmd NVARCHAR(max), @params NVARCHAR(600), @sqlmajorver int\r\nDECLARE @UpTime VARCHAR(12),@StartDate DATETIME\r\n\r\nSELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);\r\n\r\nIF @sqlmajorver < 10\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT @UpTimeOUT = DATEDIFF(mi, login_time, GETDATE()), @StartDateOUT = login_time FROM master..sysprocesses (NOLOCK) WHERE spid = 1';\r\nEND\r\nELSE\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT @UpTimeOUT = DATEDIFF(mi,sqlserver_start_time,GETDATE()), @StartDateOUT = sqlserver_start_time FROM sys.dm_os_sys_info (NOLOCK)';\r\nEND\r\n\r\nSET @params = N'@UpTimeOUT VARCHAR(12) OUTPUT, @StartDateOUT DATETIME OUTPUT';\r\n\r\nEXECUTE sp_executesql @sqlcmd, @params, @UpTimeOUT=@UpTime OUTPUT, @StartDateOUT=@StartDate OUTPUT;\r\n\r\nSELECT 'Information' AS [Category], 'Uptime' AS [Information], GETDATE() AS [Current_Time], @StartDate AS Last_Startup, CONVERT(VARCHAR(4),@UpTime/60/24) + 'd ' + CONVERT(VARCHAR(4),@UpTime/60%24) + 'hr ' + CONVERT(VARCHAR(4),@UpTime%60) + 'min' AS Uptime",
            "metadata": {},
            "outputs": [],
            "execution_count": 2
        },
        {
            "cell_type": "markdown",
            "source": "OS Version and Architecture subsection",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\n\r\nDECLARE @sqlcmd NVARCHAR(max), @params NVARCHAR(600)\r\nDECLARE @sqlmajorver int, @sqlminorver int, @sqlbuild int\r\nDECLARE @ErrorMessage NVARCHAR(4000)\r\nDECLARE @clustered bit, @osver VARCHAR(5), @ostype VARCHAR(10), @osdistro VARCHAR(20), @server VARCHAR(128), @instancename NVARCHAR(128)\r\nDECLARE @arch smallint, @ossp VARCHAR(25), @SystemManufacturer VARCHAR(128)\r\n\r\nSELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);\r\nSELECT @sqlminorver = CONVERT(int, (@@microsoftversion / 0x10000) & 0xff);\r\nSELECT @sqlbuild = CONVERT(int, @@microsoftversion & 0xffff);\r\n\r\nIF (@sqlmajorver >= 11 AND @sqlmajorver < 14) OR (@sqlmajorver = 10 AND @sqlminorver = 50 AND @sqlbuild >= 2500)\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT @ostypeOUT = ''Windows'', @osdistroOUT = ''Windows'', @osverOUT = CASE WHEN windows_release IN (''6.3'',''10.0'') AND (@@VERSION LIKE ''%Build 10586%'' OR @@VERSION LIKE ''%Build 14393%'') THEN ''10.0'' ELSE windows_release END, @osspOUT = windows_service_pack_level, @archOUT = CASE WHEN @@VERSION LIKE ''%<X64>%'' THEN 64 WHEN @@VERSION LIKE ''%<IA64>%'' THEN 128 ELSE 32 END FROM sys.dm_os_windows_info (NOLOCK)';\r\n\tSET @params = N'@osverOUT VARCHAR(5) OUTPUT, @ostypeOUT VARCHAR(10) OUTPUT, @osdistroOUT VARCHAR(20) OUTPUT, @osspOUT VARCHAR(25) OUTPUT, @archOUT smallint OUTPUT';\r\n\tEXECUTE sp_executesql @sqlcmd, @params, @osverOUT=@osver OUTPUT, @ostypeOUT=@ostype OUTPUT, @osdistroOUT=@osdistro OUTPUT, @osspOUT=@ossp OUTPUT, @archOUT=@arch OUTPUT;\r\nEND\r\nELSE IF @sqlmajorver >= 14\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT @ostypeOUT = host_platform, @osdistroOUT = host_distribution, @osverOUT = CASE WHEN host_platform = ''Windows'' AND host_release IN (''6.3'',''10.0'') THEN ''10.0'' ELSE host_release END, @osspOUT = host_service_pack_level, @archOUT = CASE WHEN @@VERSION LIKE ''%<X64>%'' THEN 64 ELSE 32 END FROM sys.dm_os_host_info (NOLOCK)';\r\n\tSET @params = N'@osverOUT VARCHAR(5) OUTPUT, @ostypeOUT VARCHAR(10) OUTPUT, @osdistroOUT VARCHAR(20) OUTPUT, @osspOUT VARCHAR(25) OUTPUT, @archOUT smallint OUTPUT';\r\n\tEXECUTE sp_executesql @sqlcmd, @params, @osverOUT=@osver OUTPUT, @ostypeOUT=@ostype OUTPUT, @osdistroOUT=@osdistro OUTPUT, @osspOUT=@ossp OUTPUT, @archOUT=@arch OUTPUT;\r\nEND\r\nELSE\r\nBEGIN\r\n\tBEGIN TRY\r\n\t\tDECLARE @str VARCHAR(500), @str2 VARCHAR(500), @str3 VARCHAR(500)\r\n\t\tDECLARE @sysinfo TABLE (id int, \r\n\t\t\t[Name] NVARCHAR(256), \r\n\t\t\tInternal_Value bigint, \r\n\t\t\tCharacter_Value NVARCHAR(256));\r\n\t\t\t\r\n\t\tINSERT INTO @sysinfo\r\n\t\tEXEC xp_msver;\r\n\t\t\r\n\t\tSELECT @osver = LEFT(Character_Value, CHARINDEX(' ', Character_Value)-1) -- 5.2 is WS2003; 6.0 is WS2008; 6.1 is WS2008R2; 6.2 is WS2012, 6.3 is WS2012R2, 6.3 (14396) is WS2016\r\n\t\tFROM @sysinfo\r\n\t\tWHERE [Name] LIKE 'WindowsVersion%';\r\n\t\t\r\n\t\tSELECT @arch = CASE WHEN RTRIM(Character_Value) LIKE '%x64%' OR RTRIM(Character_Value) LIKE '%AMD64%' THEN 64\r\n\t\t\tWHEN RTRIM(Character_Value) LIKE '%x86%' OR RTRIM(Character_Value) LIKE '%32%' THEN 32\r\n\t\t\tWHEN RTRIM(Character_Value) LIKE '%IA64%' THEN 128 END\r\n\t\tFROM @sysinfo\r\n\t\tWHERE [Name] LIKE 'Platform%';\r\n\t\t\r\n\t\tSET @str = (SELECT @@VERSION)\r\n\t\tSELECT @str2 = RIGHT(@str, LEN(@str)-CHARINDEX('Windows',@str) + 1)\r\n\t\tSELECT @str3 = RIGHT(@str2, LEN(@str2)-CHARINDEX(': ',@str2))\r\n\t\tSELECT @ossp = LTRIM(LEFT(@str3, CHARINDEX(')',@str3) -1))\r\n\t\tSET @ostype = 'Windows'\r\n\tEND TRY\r\n\tBEGIN CATCH\r\n\t\tSELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;\r\n\t\tSELECT @ErrorMessage = 'Windows Version and Architecture subsection - Error raised in TRY block. ' + ERROR_MESSAGE()\r\n\t\tRAISERROR (@ErrorMessage, 16, 1);\r\n\tEND CATCH\r\nEND;\r\n\r\nDECLARE @machineinfo TABLE ([Value] NVARCHAR(256), [Data] NVARCHAR(256))\r\n\r\nIF @ostype = 'Windows'\r\nBEGIN\r\n\tINSERT INTO @machineinfo\r\n\tEXEC xp_instance_regread 'HKEY_LOCAL_MACHINE','HARDWARE\\DESCRIPTION\\System\\BIOS','SystemManufacturer';\r\n\tINSERT INTO @machineinfo\r\n\tEXEC xp_instance_regread 'HKEY_LOCAL_MACHINE','HARDWARE\\DESCRIPTION\\System\\BIOS','SystemProductName';\r\n\tINSERT INTO @machineinfo\r\n\tEXEC xp_instance_regread 'HKEY_LOCAL_MACHINE','HARDWARE\\DESCRIPTION\\System\\BIOS','SystemFamily';\r\n\tINSERT INTO @machineinfo\r\n\tEXEC xp_instance_regread 'HKEY_LOCAL_MACHINE','HARDWARE\\DESCRIPTION\\System\\BIOS','BIOSVendor';\r\n\tINSERT INTO @machineinfo\r\n\tEXEC xp_instance_regread 'HKEY_LOCAL_MACHINE','HARDWARE\\DESCRIPTION\\System\\BIOS','BIOSVersion';\r\n\tINSERT INTO @machineinfo\r\n\tEXEC xp_instance_regread 'HKEY_LOCAL_MACHINE','HARDWARE\\DESCRIPTION\\System\\BIOS','BIOSReleaseDate';\r\n\tINSERT INTO @machineinfo\r\n\tEXEC xp_instance_regread 'HKEY_LOCAL_MACHINE','HARDWARE\\DESCRIPTION\\System\\CentralProcessor\\0','ProcessorNameString';\r\nEND\r\n\r\nSELECT @SystemManufacturer = [Data] FROM @machineinfo WHERE [Value] = 'SystemManufacturer';\r\n\r\nSELECT 'Information' AS [Category], 'Machine' AS [Information], \r\n\tCASE @osver WHEN '5.2' THEN 'XP/WS2003'\r\n\t\tWHEN '6.0' THEN 'Vista/WS2008'\r\n\t\tWHEN '6.1' THEN 'W7/WS2008R2'\r\n\t\tWHEN '6.2' THEN 'W8/WS2012'\r\n\t\tWHEN '6.3' THEN 'W8.1/WS2012R2'\r\n\t\tWHEN '10.0' THEN 'W10/WS2016'\r\n\t\tELSE @ostype + ' ' + @osdistro\r\n\tEND AS [OS_Version],\r\n\tCASE WHEN @ostype = 'Windows' THEN @ossp ELSE @osver END AS [Service_Pack_Level],\r\n\t@arch AS [Architecture],\r\n\tSERVERPROPERTY('MachineName') AS [Machine_Name],\r\n\tSERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [NetBIOS_Name],\r\n\t@SystemManufacturer AS [System_Manufacturer],\r\n\t(SELECT [Data] FROM @machineinfo WHERE [Value] = 'SystemFamily') AS [System_Family],\r\n\t(SELECT [Data] FROM @machineinfo WHERE [Value] = 'SystemProductName') AS [System_ProductName],\r\n\t(SELECT [Data] FROM @machineinfo WHERE [Value] = 'BIOSVendor') AS [BIOS_Vendor],\r\n\t(SELECT [Data] FROM @machineinfo WHERE [Value] = 'BIOSVersion') AS [BIOS_Version],\r\n\t(SELECT [Data] FROM @machineinfo WHERE [Value] = 'BIOSReleaseDate') AS [BIOS_Release_Date],\r\n\t(SELECT [Data] FROM @machineinfo WHERE [Value] = 'ProcessorNameString') AS [Processor_Name];",
            "metadata": {},
            "outputs": [],
            "execution_count": 25
        },
        {
            "cell_type": "markdown",
            "source": "Disk space subsection",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\n\r\nDECLARE @sqlmajorver int\r\nDECLARE @ErrorMessage NVARCHAR(4000)\r\n\r\nSELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);\r\n\r\nIF @sqlmajorver > 9\r\nBEGIN\r\n\tSELECT DISTINCT 'Information' AS [Category], 'Disk_Space' AS [Information], vs.logical_volume_name,\r\n\t\tvs.volume_mount_point, vs.file_system_type, CONVERT(int,vs.total_bytes/1048576.0) AS TotalSpace_MB,\r\n\t\tCONVERT(int,vs.available_bytes/1048576.0) AS FreeSpace_MB, vs.is_compressed\r\n\tFROM sys.master_files mf\r\n\tCROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.[file_id]) vs\r\n\tORDER BY FreeSpace_MB ASC\r\nEND;",
            "metadata": {},
            "outputs": [],
            "execution_count": 26
        },
        {
            "cell_type": "markdown",
            "source": "HA Information subsection",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\n\r\nDECLARE @sqlcmd NVARCHAR(max), @params NVARCHAR(600)\r\nDECLARE @sqlmajorver int, @sqlminorver int, @sqlbuild int, @clustered bit\r\nDECLARE @ptochecks bit\r\n\r\nSET @ptochecks = 1 --(1 = ON; 0 = OFF)\r\n\r\nSELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);\r\nSELECT @sqlminorver = CONVERT(int, (@@microsoftversion / 0x10000) & 0xff);\r\nSELECT @sqlbuild = CONVERT(int, @@microsoftversion & 0xffff);\r\nSELECT @clustered = CONVERT(bit,ISNULL(SERVERPROPERTY('IsClustered'),0))\r\n\r\nIF @clustered = 1\r\nBEGIN\r\n\tIF @sqlmajorver < 11\r\n\t\tBEGIN\r\n\t\t\tEXEC ('SELECT ''Information'' AS [Category], ''Cluster'' AS [Information], NodeName AS node_name FROM sys.dm_os_cluster_nodes (NOLOCK)')\r\n\t\tEND\r\n\tELSE\r\n\t\tBEGIN\r\n\t\t\tEXEC ('SELECT ''Information'' AS [Category], ''Cluster'' AS [Information], NodeName AS node_name, status_description, is_current_owner FROM sys.dm_os_cluster_nodes (NOLOCK)')\r\n\t\tEND\r\n\tSELECT 'Information' AS [Category], 'Cluster' AS [Information], DriveName AS cluster_shared_drives FROM sys.dm_io_cluster_shared_drives (NOLOCK)\r\nEND\r\nELSE\r\nBEGIN\r\n\tSELECT 'Information' AS [Category], 'Cluster' AS [Information], 'NOT_CLUSTERED' AS [Status]\r\nEND;\r\n\r\nIF @sqlmajorver > 10\r\nBEGIN\r\n\tDECLARE @IsHadrEnabled tinyint, @HadrManagerStatus tinyint\r\n\tSELECT @IsHadrEnabled = CASE WHEN SERVERPROPERTY('EngineEdition') = 8 THEN 1 ELSE CONVERT(tinyint, SERVERPROPERTY('IsHadrEnabled')) END;\r\n\tSELECT @HadrManagerStatus = CASE WHEN SERVERPROPERTY('EngineEdition') = 8 THEN 1 ELSE CONVERT(tinyint, SERVERPROPERTY('HadrManagerStatus')) END;\r\n\t\r\n\tSELECT 'Information' AS [Category], 'AlwaysOn_AG' AS [Information], \r\n\t\tCASE @IsHadrEnabled WHEN 0 THEN 'Disabled'\r\n\t\t\tWHEN 1 THEN 'Enabled' END AS [AlwaysOn_Availability_Groups],\r\n\t\tCASE WHEN @IsHadrEnabled = 1 THEN\r\n\t\t\tCASE @HadrManagerStatus WHEN 0 THEN '[Not started, pending communication]'\r\n\t\t\t\tWHEN 1 THEN '[Started and running]'\r\n\t\t\t\tWHEN 2 THEN '[Not started and failed]'\r\n\t\t\tEND\r\n\t\tEND AS [Status];\r\n\t\r\n\tIF @IsHadrEnabled = 1\r\n\tBEGIN\t\r\n\t\tIF EXISTS (SELECT 1 FROM sys.dm_hadr_cluster) \r\n\t\tSELECT 'Information' AS [Category], 'AlwaysOn_Cluster' AS [Information], cluster_name, quorum_type_desc, quorum_state_desc \r\n\t\tFROM sys.dm_hadr_cluster;\r\n\r\n\t\tIF EXISTS (SELECT 1 FROM sys.dm_hadr_cluster_members) \r\n\t\tSELECT 'Information' AS [Category], 'AlwaysOn_Cluster_Members' AS [Information], member_name, member_type_desc, member_state_desc, number_of_quorum_votes \r\n\t\tFROM sys.dm_hadr_cluster_members;\r\n\t\t\r\n\t\tIF EXISTS (SELECT 1 FROM sys.dm_hadr_cluster_networks) \r\n\t\tSELECT 'Information' AS [Category], 'AlwaysOn_Cluster_Networks' AS [Information], member_name, network_subnet_ip, network_subnet_ipv4_mask, is_public, is_ipv4 \r\n\t\tFROM sys.dm_hadr_cluster_networks;\r\n\tEND;\r\n\t\r\n\tIF @ptochecks = 1 AND @IsHadrEnabled = 1\r\n\tBEGIN\r\n\t\t-- Note: If low_water_mark_for_ghosts number is not increasing over time, it implies that ghost cleanup might not happen.\r\n\t\tSET @sqlcmd = 'SELECT ''Information'' AS [Category], ''AlwaysOn_Replicas'' AS [Information], database_id, group_id, replica_id, group_database_id, is_local, synchronization_state_desc, \r\n\tis_commit_participant, synchronization_health_desc, database_state_desc, is_suspended, suspend_reason_desc, last_sent_time, last_received_time, last_hardened_time, \r\n\tlast_redone_time, log_send_queue_size, log_send_rate, redo_queue_size, redo_rate, filestream_send_rate, last_commit_time, \r\n\tlow_water_mark_for_ghosts' + CASE WHEN @sqlmajorver > 12 THEN ', secondary_lag_seconds' ELSE '' END + ' \r\nFROM sys.dm_hadr_database_replica_states'\r\n\t\tEXECUTE sp_executesql @sqlcmd\r\n\r\n\t\tSELECT 'Information' AS [Category], 'AlwaysOn_Replica_Cluster' AS [Information], replica_id, group_database_id, database_name, is_failover_ready, is_pending_secondary_suspend, \r\n\t\t\tis_database_joined, recovery_lsn, truncation_lsn \r\n\t\tFROM sys.dm_hadr_database_replica_cluster_states;\r\n\tEND\r\nEND;",
            "metadata": {},
            "outputs": [],
            "execution_count": 28
        },
        {
            "cell_type": "markdown",
            "source": "Linked servers info subsection",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\n\r\nDECLARE @sqlcmd NVARCHAR(max), @params NVARCHAR(600)\r\nDECLARE @sqlmajorver int\r\n\r\nSELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);\r\n\r\nIF (SELECT COUNT(*) FROM sys.servers AS s INNER JOIN sys.linked_logins AS l (NOLOCK) ON s.server_id = l.server_id INNER JOIN sys.server_principals AS p (NOLOCK) ON p.principal_id = l.local_principal_id WHERE s.is_linked = 1) > 0\r\nBEGIN\r\n\tSET @sqlcmd = 'SELECT ''Information'' AS [Category], ''Linked_servers'' AS [Information], s.name, s.product, \r\n\ts.provider, s.data_source, s.location, s.provider_string, s.catalog, s.connect_timeout, \r\n\ts.query_timeout, s.is_linked, s.is_remote_login_enabled, s.is_rpc_out_enabled, \r\n\ts.is_data_access_enabled, s.is_collation_compatible, s.uses_remote_collation, s.collation_name, \r\n\ts.lazy_schema_validation, s.is_system, s.is_publisher, s.is_subscriber, s.is_distributor, \r\n\ts.is_nonsql_subscriber' + CASE WHEN @sqlmajorver > 9 THEN ', s.is_remote_proc_transaction_promotion_enabled' ELSE '' END + ',\r\n\ts.modify_date, CASE WHEN l.local_principal_id = 0 THEN ''local or wildcard'' ELSE p.name END AS [local_principal], \r\n\tCASE WHEN l.uses_self_credential = 0 THEN ''use own credentials'' ELSE ''use supplied username and pwd'' END AS uses_self_credential, \r\n\tl.remote_name, l.modify_date AS [linked_login_modify_date]\r\nFROM sys.servers AS s (NOLOCK)\r\nINNER JOIN sys.linked_logins AS l (NOLOCK) ON s.server_id = l.server_id\r\nINNER JOIN sys.server_principals AS p (NOLOCK) ON p.principal_id = l.local_principal_id\r\nWHERE s.is_linked = 1'\r\n\tEXECUTE sp_executesql @sqlcmd\r\nEND\r\nELSE\r\nBEGIN\r\n\tSELECT 'Information' AS [Category], 'Linked_servers' AS [Information], 'None' AS [Status]\r\nEND;\r\n",
            "metadata": {},
            "outputs": [],
            "execution_count": 31
        },
        {
            "cell_type": "markdown",
            "source": "Instance info subsection",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\n\r\nDECLARE @sqlcmd NVARCHAR(max), @params NVARCHAR(600)\r\nDECLARE @ErrorMessage NVARCHAR(4000)\r\nDECLARE @sqlmajorver int, @sqlminorver int, @sqlbuild int, @masterpid int\r\nDECLARE @port VARCHAR(15), @replication int, @RegKey NVARCHAR(255), @cpuaffin VARCHAR(255), @cpucount int, @numa int\r\nDECLARE @i int, @cpuaffin_fixed VARCHAR(300), @affinitymask NVARCHAR(64), @affinity64mask NVARCHAR(1024)--, @cpuover32 int\r\nDECLARE @permstbl TABLE ([name] sysname);\r\n\r\nSELECT @masterpid = principal_id FROM master.sys.database_principals (NOLOCK) WHERE sid = SUSER_SID()\r\n\r\nINSERT INTO @permstbl\r\nSELECT a.name\r\nFROM master.sys.all_objects a (NOLOCK) INNER JOIN master.sys.database_permissions b (NOLOCK) ON a.[OBJECT_ID] = b.major_id\r\nWHERE a.type IN ('P', 'X') AND b.grantee_principal_id <>0 \r\nAND b.grantee_principal_id <> 2\r\nAND b.grantee_principal_id = @masterpid;\r\n\r\nSELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);\r\nSELECT @sqlminorver = CONVERT(int, (@@microsoftversion / 0x10000) & 0xff);\r\nSELECT @sqlbuild = CONVERT(int, @@microsoftversion & 0xffff);\r\n\r\nIF @sqlmajorver < 11 OR (@sqlmajorver = 10 AND @sqlminorver = 50 AND @sqlbuild < 2500)\r\nBEGIN\r\n\tIF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1) OR ((SELECT COUNT([name]) FROM @permstbl WHERE [name] = 'xp_regread') = 1)\r\n\tBEGIN\r\n\t\tBEGIN TRY\r\n\t\t\tSELECT @RegKey = CASE WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('InstanceName')) IS NULL THEN N'Software\\Microsoft\\MSSQLServer\\MSSQLServer\\SuperSocketNetLib\\Tcp'\r\n\t\t\t\tELSE N'Software\\Microsoft\\Microsoft SQL Server\\' + CAST(SERVERPROPERTY('InstanceName') AS NVARCHAR(128)) + N'\\MSSQLServer\\SuperSocketNetLib\\Tcp' END\r\n\t\t\tEXEC master.sys.xp_regread N'HKEY_LOCAL_MACHINE', @RegKey, N'TcpPort', @port OUTPUT, NO_OUTPUT\r\n\t\tEND TRY\r\n\t\tBEGIN CATCH\r\n\t\t\tSELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;\r\n\t\t\tSELECT @ErrorMessage = 'Instance info subsection - Error raised in TRY block 1. ' + ERROR_MESSAGE()\r\n\t\t\tRAISERROR (@ErrorMessage, 16, 1);\r\n\t\tEND CATCH\r\n\tEND\r\n\tELSE\r\n\tBEGIN\r\n\t\tRAISERROR('WARNING: Missing permissions for full \"Instance info\" checks. Bypassing TCP port check', 16, 1, N'sysadmin')\r\n\t\t--RETURN\r\n\tEND\r\nEND\r\nELSE\r\nBEGIN\r\n\tBEGIN TRY\r\n\t\t/*\r\n\t\tSET @sqlcmd = N'SELECT @portOUT = MAX(CONVERT(VARCHAR(15),value_data)) FROM sys.dm_server_registry WHERE registry_key LIKE ''%MSSQLServer\\SuperSocketNetLib\\Tcp\\%'' AND value_name LIKE N''%TcpPort%'' AND CONVERT(float,value_data) > 0;';\r\n\t\tSET @params = N'@portOUT VARCHAR(15) OUTPUT';\r\n\t\tEXECUTE sp_executesql @sqlcmd, @params, @portOUT = @port OUTPUT;\r\n\t\tIF @port IS NULL\r\n\t\tBEGIN\r\n\t\t\tSET @sqlcmd = N'SELECT @portOUT = CONVERT(VARCHAR(15),value_data) FROM sys.dm_server_registry WHERE registry_key LIKE ''%MSSQLServer\\SuperSocketNetLib\\Tcp\\%'' AND value_name LIKE N''%TcpDynamicPort%'' AND CONVERT(float,value_data) > 0;';\r\n\t\t\tSET @params = N'@portOUT VARCHAR(15) OUTPUT';\r\n\t\t\tEXECUTE sp_executesql @sqlcmd, @params, @portOUT = @port OUTPUT;\r\n\t\tEND\r\n\t\t*/\r\n\t\tSET @sqlcmd = N'SELECT @portOUT = MAX(CONVERT(VARCHAR(15),port)) FROM sys.dm_tcp_listener_states WHERE is_ipv4 = 1 AND [type] = 0 AND ip_address <> ''127.0.0.1'';';\r\n\t\tSET @params = N'@portOUT VARCHAR(15) OUTPUT';\r\n\t\tEXECUTE sp_executesql @sqlcmd, @params, @portOUT = @port OUTPUT;\r\n\t\tIF @port IS NULL\r\n\t\tBEGIN\r\n\t\t\tSET @sqlcmd = N'SELECT @portOUT = MAX(CONVERT(VARCHAR(15),port)) FROM sys.dm_tcp_listener_states WHERE is_ipv4 = 0 AND [type] = 0 AND ip_address <> ''127.0.0.1'';';\r\n\t\t\tSET @params = N'@portOUT VARCHAR(15) OUTPUT';\r\n\t\t\tEXECUTE sp_executesql @sqlcmd, @params, @portOUT = @port OUTPUT;\r\n\t\tEND\r\n\tEND TRY\r\n\tBEGIN CATCH\r\n\t\tSELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;\r\n\t\tSELECT @ErrorMessage = 'Instance info subsection - Error raised in TRY block 2. ' + ERROR_MESSAGE()\r\n\t\tRAISERROR (@ErrorMessage, 16, 1);\r\n\tEND CATCH\r\nEND\r\n\r\nIF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1) OR ((SELECT COUNT([name]) FROM @permstbl WHERE [name] = 'xp_instance_regread') = 1)\r\nBEGIN\r\n\tBEGIN TRY\r\n\t\tEXEC master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\\Microsoft\\MSSQLServer\\Replication', N'IsInstalled', @replication OUTPUT, NO_OUTPUT\r\n\tEND TRY\r\n\tBEGIN CATCH\r\n\t\tSELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;\r\n\t\tSELECT @ErrorMessage = 'Instance info subsection - Error raised in TRY block 3. ' + ERROR_MESSAGE()\r\n\t\tRAISERROR (@ErrorMessage, 16, 1);\r\n\tEND CATCH\r\nEND\r\nELSE\r\nBEGIN\r\n\tRAISERROR('WARNING: Missing permissions for full \"Instance info\" checks. Bypassing replication check', 16, 1, N'sysadmin')\r\n\t--RETURN\r\nEND\r\n\r\nSELECT @cpucount = COUNT(cpu_id) FROM sys.dm_os_schedulers WHERE scheduler_id < 255 AND parent_node_id < 64\r\nSELECT @numa = COUNT(DISTINCT parent_node_id) FROM sys.dm_os_schedulers WHERE scheduler_id < 255 AND parent_node_id < 64;\r\n\r\n;WITH bits AS \r\n(SELECT 7 AS N, 128 AS E UNION ALL SELECT 6, 64 UNION ALL \r\nSELECT 5, 32 UNION ALL SELECT 4, 16 UNION ALL SELECT 3, 8 UNION ALL \r\nSELECT 2, 4 UNION ALL SELECT 1, 2 UNION ALL SELECT 0, 1), \r\nbytes AS \r\n(SELECT 1 M UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL \r\nSELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL \r\nSELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9)\r\n-- CPU Affinity is shown highest to lowest CPU ID\r\nSELECT @affinitymask = CASE WHEN [value] = 0 THEN REPLICATE('1', @cpucount)\r\n\tELSE RIGHT((SELECT ((CONVERT(tinyint, SUBSTRING(CONVERT(binary(9), [value]), M, 1)) & E) / E) AS [text()] \r\n\t\tFROM bits CROSS JOIN bytes\r\n\t\tORDER BY M, N DESC\r\n\t\tFOR XML PATH('')), @cpucount) END\r\nFROM sys.configurations (NOLOCK)\r\nWHERE name = 'affinity mask';\r\n\r\nIF @cpucount > 32\r\nBEGIN\r\n\t;WITH bits AS \r\n\t(SELECT 7 AS N, 128 AS E UNION ALL SELECT 6, 64 UNION ALL \r\n\tSELECT 5, 32 UNION ALL SELECT 4, 16 UNION ALL SELECT 3, 8 UNION ALL \r\n\tSELECT 2, 4 UNION ALL SELECT 1, 2 UNION ALL SELECT 0, 1), \r\n\tbytes AS \r\n\t(SELECT 1 M UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL \r\n\tSELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL \r\n\tSELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9)\r\n\t-- CPU Affinity is shown highest to lowest CPU ID\r\n\tSELECT @affinity64mask = CASE WHEN [value] = 0 THEN REPLICATE('1', @cpucount)\r\n\t\tELSE RIGHT((SELECT ((CONVERT(tinyint, SUBSTRING(CONVERT(binary(9), [value]), M, 1)) & E) / E) AS [text()] \r\n\t\t\tFROM bits CROSS JOIN bytes\r\n\t\t\tORDER BY M, N DESC\r\n\t\t\tFOR XML PATH('')), @cpucount) END\r\n\tFROM sys.configurations (NOLOCK)\r\n\tWHERE name = 'affinity64 mask';\r\nEND;\r\n\r\n/*\r\nIF @cpucount > 32\r\nSELECT @cpuover32 = ABS(LEN(@affinity64mask) - (@cpucount-32))\r\n\r\nSELECT @cpuaffin = CASE WHEN @cpucount > 32 THEN REVERSE(LEFT(REVERSE(@affinity64mask),@cpuover32)) + RIGHT(@affinitymask,32) ELSE RIGHT(@affinitymask,@cpucount) END\r\n*/\r\n\r\nSELECT @cpuaffin = CASE WHEN @cpucount > 32 THEN @affinity64mask ELSE @affinitymask END\r\n\r\nSET @cpuaffin_fixed = @cpuaffin\r\n\r\nIF @numa > 1\r\nBEGIN\r\n\t-- format binary mask by node for better reading\r\n\tSET @i = CEILING(@cpucount*1.00/@numa) + 1\r\n\tWHILE @i < @cpucount + @numa\r\n\tBEGIN\r\n\t\tIF (@cpucount + @numa) - @i >= SQRT(CEILING(@cpucount*1.00/@numa))\r\n\t\tBEGIN\r\n\t\t\tSELECT @cpuaffin_fixed = STUFF(@cpuaffin_fixed, @i, 1, '_' + SUBSTRING(@cpuaffin_fixed, @i, 1))\r\n\t\tEND\r\n\t\tELSE\r\n\t\tBEGIN\r\n\t\t\tSELECT @cpuaffin_fixed = STUFF(@cpuaffin_fixed, @i, CEILING(@cpucount*1.00/@numa), SUBSTRING(@cpuaffin_fixed, @i, CEILING(@cpucount*1.00/@numa)))\r\n\t\tEND\r\n\r\n\t\tSET @i = @i + CEILING(@cpucount*1.00/@numa) + 1\r\n\tEND\r\nEND\r\n\r\nSELECT 'Information' AS [Category], 'Instance' AS [Information],\r\n\t(CASE WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('InstanceName')) IS NULL THEN 'DEFAULT_INSTANCE'\r\n\t\tELSE CONVERT(VARCHAR(128), SERVERPROPERTY('InstanceName')) END) AS Instance_Name,\r\n\t(CASE WHEN SERVERPROPERTY('IsClustered') = 1 THEN 'CLUSTERED' \r\n\t\tWHEN SERVERPROPERTY('IsClustered') = 0 THEN 'NOT_CLUSTERED'\r\n\t\tELSE 'INVALID INPUT/ERROR' END) AS Failover_Clustered,\r\n\t/*The version of SQL Server instance in the form: major.minor.build*/\t\r\n\tCONVERT(VARCHAR(128), SERVERPROPERTY('ProductVersion')) AS Product_Version,\r\n\t/*Level of the version of SQL Server Instance*/\r\n\tCASE WHEN (@sqlmajorver = 11 AND @sqlminorver >= 6020) OR (@sqlmajorver = 12 AND @sqlminorver BETWEEN 2556 AND 2569) OR (@sqlmajorver = 12 AND @sqlminorver >= 4427) OR @sqlmajorver >= 13 THEN \r\n\t\tCONVERT(VARCHAR(128), SERVERPROPERTY('ProductBuildType'))\r\n\tELSE 'NA' END AS Product_Build_Type,\r\n\tCONVERT(VARCHAR(128), SERVERPROPERTY('ProductLevel')) AS Product_Level,\r\n\tCASE WHEN (@sqlmajorver = 11 AND @sqlminorver >= 6020) OR (@sqlmajorver = 12 AND @sqlminorver BETWEEN 2556 AND 2569) OR (@sqlmajorver = 12 AND @sqlminorver >= 4427) OR @sqlmajorver >= 13 THEN \r\n\t\tCONVERT(VARCHAR(128), SERVERPROPERTY('ProductUpdateLevel'))\r\n\tELSE 'NA' END AS Product_Update_Level,\r\n\tCASE WHEN (@sqlmajorver = 11 AND @sqlminorver >= 6020) OR (@sqlmajorver = 12 AND @sqlminorver BETWEEN 2556 AND 2569) OR (@sqlmajorver = 12 AND @sqlminorver >= 4427) OR @sqlmajorver >= 13 THEN \r\n\t\tCONVERT(VARCHAR(128), SERVERPROPERTY('ProductUpdateReference'))\r\n\tELSE 'NA' END AS Product_Update_Ref_KB,\r\n\tCONVERT(VARCHAR(128), SERVERPROPERTY('Edition')) AS Edition,\r\n\tCONVERT(VARCHAR(128), SERVERPROPERTY('MachineName')) AS Machine_Name,\r\n\tRTRIM(@port) AS TCP_Port,\r\n\t@@SERVICENAME AS Service_Name,\r\n\t/*To identify which sqlservr.exe belongs to this instance*/\r\n\tSERVERPROPERTY('ProcessID') AS Process_ID, \r\n\tCONVERT(VARCHAR(128), SERVERPROPERTY('ServerName')) AS Server_Name,\r\n\t@cpuaffin_fixed AS Affinity_Mask_Bitmask,\r\n\tCONVERT(VARCHAR(128), SERVERPROPERTY('Collation')) AS [Server_Collation],\r\n\t(CASE WHEN @replication = 1 THEN 'Installed' \r\n\t\tWHEN @replication = 0 THEN 'Not_Installed' \r\n\t\tELSE 'INVALID INPUT/ERROR' END) AS Replication_Components_Installation,\r\n\t(CASE WHEN SERVERPROPERTY('IsFullTextInstalled') = 1 THEN 'Installed' \r\n\t\tWHEN SERVERPROPERTY('IsFulltextInstalled') = 0 THEN 'Not_Installed' \r\n\t\tELSE 'INVALID INPUT/ERROR' END) AS Full_Text_Installation,\r\n\t(CASE WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 1 THEN 'Integrated_Security' \r\n\t\tWHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 0 THEN 'SQL_Server_Security' \r\n\t\tELSE 'INVALID INPUT/ERROR' END) AS [Security],\r\n\t(CASE WHEN SERVERPROPERTY('IsSingleUser') = 1 THEN 'Single_User' \r\n\t\tWHEN SERVERPROPERTY('IsSingleUser') = 0\tTHEN 'Multi_User' \r\n\t\tELSE 'INVALID INPUT/ERROR' END) AS [Single_User],\r\n\t(CASE WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('LicenseType')) = 'PER_SEAT' THEN 'Per_Seat_Mode' \r\n\t\tWHEN CONVERT(VARCHAR(128), SERVERPROPERTY('LicenseType')) = 'PER_PROCESSOR' THEN 'Per_Processor_Mode' \r\n\t\tELSE 'Disabled' END) AS License_Type, -- From SQL Server 2008R2 always returns DISABLED.\r\n\tCONVERT(NVARCHAR(128), SERVERPROPERTY('BuildClrVersion')) AS CLR_Version,\r\n\tCASE WHEN @sqlmajorver >= 10 THEN \r\n\t\tCASE WHEN SERVERPROPERTY('FilestreamConfiguredLevel') = 0 THEN 'Disabled'\r\n\t\t\tWHEN SERVERPROPERTY('FilestreamConfiguredLevel') = 1 THEN 'Enabled_for_TSQL'\r\n\t\t\tELSE 'Enabled for TSQL and Win32' END\r\n\tELSE 'Not compatible' END AS Filestream_Configured_Level,\r\n\tCASE WHEN @sqlmajorver >= 10 THEN \r\n\t\tCASE WHEN SERVERPROPERTY('FilestreamEffectiveLevel') = 0 THEN 'Disabled'\r\n\t\t\tWHEN SERVERPROPERTY('FilestreamEffectiveLevel') = 1 THEN 'Enabled_for_TSQL'\r\n\t\t\tELSE 'Enabled for TSQL and Win32' END\r\n\tELSE 'Not compatible' END AS Filestream_Effective_Level,\r\n\tCASE WHEN @sqlmajorver >= 10 THEN \r\n\t\tSERVERPROPERTY('FilestreamShareName')\r\n\tELSE 'Not compatible' END AS Filestream_Share_Name,\r\n\tCASE WHEN @sqlmajorver >= 12 THEN \r\n\t\tSERVERPROPERTY('IsXTPSupported')\r\n\tELSE 'Not compatible' END AS XTP_Compatible,\r\n\tCASE WHEN @sqlmajorver >= 13 THEN \r\n\t\tSERVERPROPERTY('IsPolybaseInstalled')\r\n\tELSE 'Not compatible' END AS Polybase_Installed,\r\n\tCASE WHEN @sqlmajorver >= 13 THEN \r\n\t\tSERVERPROPERTY('IsAdvancedAnalyticsInstalled')\r\n\tELSE 'Not compatible' END AS R_Services_Installed;",
            "metadata": {},
            "outputs": [],
            "execution_count": 33
        },
        {
            "cell_type": "markdown",
            "source": "Buffer Pool Extension info subsection",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\n\r\nDECLARE @sqlmajorver int\r\n\r\nSELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);\r\n\r\nIF @sqlmajorver > 11\r\nBEGIN\r\n\tSELECT 'Information' AS [Category], 'BP_Extension' AS [Information], \r\n\t\tCASE WHEN state = 0 THEN 'BP_Extension_Disabled' \r\n\t\t\tWHEN state = 1 THEN 'BP_Extension_is_Disabling'\r\n\t\t\tWHEN state = 3 THEN 'BP_Extension_is_Enabling'\r\n\t\t\tWHEN state = 5 THEN 'BP_Extension_Enabled'\r\n\t\tEND AS state, \r\n\t\t[path], current_size_in_kb\r\n\tFROM sys.dm_os_buffer_pool_extension_configuration\r\nEND\r\nELSE\r\nBEGIN\r\n\tSELECT 'Information' AS [Category], 'BP_Extension' AS [Information], 'NA' AS state\r\nEND;",
            "metadata": {},
            "outputs": [],
            "execution_count": 35
        },
        {
            "cell_type": "markdown",
            "source": "Resource Governor info subsection",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\n\r\nDECLARE @sqlmajorver int\r\nDECLARE @sqlcmd NVARCHAR(max), @params NVARCHAR(600)\r\n\r\nSELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);\r\n\r\nIF @sqlmajorver > 9\r\nBEGIN\r\n\tSELECT 'Information' AS [Category], 'RG_Classifier_Function' AS [Information], CASE WHEN classifier_function_id = 0 THEN 'Default_Configuration' ELSE OBJECT_SCHEMA_NAME(classifier_function_id) + '.' + OBJECT_NAME(classifier_function_id) END AS classifier_function, is_reconfiguration_pending\r\n\tFROM sys.dm_resource_governor_configuration\r\n\r\n\tSET @sqlcmd = 'SELECT ''Information'' AS [Category], ''RG_Resource_Pool'' AS [Information], rp.pool_id, name, statistics_start_time, total_cpu_usage_ms, cache_memory_kb, compile_memory_kb, \r\n\tused_memgrant_kb, total_memgrant_count, total_memgrant_timeout_count, active_memgrant_count, active_memgrant_kb, memgrant_waiter_count, max_memory_kb, used_memory_kb, target_memory_kb, \r\n\tout_of_memory_count, min_cpu_percent, max_cpu_percent, min_memory_percent, max_memory_percent' + CASE WHEN @sqlmajorver > 10 THEN ', cap_cpu_percent, rpa.processor_group, rpa.scheduler_mask' ELSE '' END + '\r\nFROM sys.dm_resource_governor_resource_pools rp' + CASE WHEN @sqlmajorver > 10 THEN ' LEFT JOIN sys.dm_resource_governor_resource_pool_affinity rpa ON rp.pool_id = rpa.pool_id' ELSE '' END\r\n\tEXECUTE sp_executesql @sqlcmd\r\n\r\n\tSET @sqlcmd = 'SELECT ''Information'' AS [Category], ''RG_Workload_Groups'' AS [Information], group_id, name, pool_id, statistics_start_time, total_request_count, total_queued_request_count, \r\n\tactive_request_count, queued_request_count, total_cpu_limit_violation_count, total_cpu_usage_ms, max_request_cpu_time_ms, blocked_task_count, total_lock_wait_count, \r\n\ttotal_lock_wait_time_ms, total_query_optimization_count, total_suboptimal_plan_generation_count, total_reduced_memgrant_count, max_request_grant_memory_kb, \r\n\tactive_parallel_thread_count, importance, request_max_memory_grant_percent, request_max_cpu_time_sec, request_memory_grant_timeout_sec, \r\n\tgroup_max_requests, max_dop' + CASE WHEN @sqlmajorver > 10 THEN ', effective_max_dop' ELSE '' END + ' \r\nFROM sys.dm_resource_governor_workload_groups'\r\n\tEXECUTE sp_executesql @sqlcmd\r\nEND;",
            "metadata": {},
            "outputs": [],
            "execution_count": 36
        },
        {
            "cell_type": "markdown",
            "source": "Logon triggers subsection",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\n\r\nIF (SELECT COUNT([name]) FROM sys.server_triggers WHERE is_disabled = 0 AND is_ms_shipped = 0) > 0\r\nBEGIN\r\n\tSELECT 'Information' AS [Category], 'Logon_Triggers' AS [Information], name AS [Trigger_Name], type_desc AS [Trigger_Type],create_date, modify_date\r\n\tFROM sys.server_triggers WHERE is_disabled = 0 AND is_ms_shipped = 0\r\n\tORDER BY name;\r\nEND\r\nELSE\r\nBEGIN\r\n\tSELECT 'Information' AS [Category], 'Logon_Triggers' AS [Information], 'NA' AS [Comment]\r\nEND;",
            "metadata": {},
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": "Database Information subsection",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\n\r\nDECLARE @dbScope VARCHAR(256)\r\nSET @dbScope = NULL --(NULL = All DBs)\r\n\r\n-- Building DB list\r\nDECLARE @curdbname NVARCHAR(1000), @curdbid int, @currole tinyint, @cursecondary_role_allow_connections tinyint, @state tinyint\r\n\r\nIF EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tmpdbs0'))\r\nDROP TABLE #tmpdbs0;\r\nIF NOT EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tmpdbs0'))\r\nCREATE TABLE #tmpdbs0 (id int IDENTITY(1,1), [dbid] int, [dbname] NVARCHAR(1000), [compatibility_level] tinyint, is_read_only bit, [state] tinyint, is_distributor bit, [role] tinyint, [secondary_role_allow_connections] tinyint, is_database_joined bit, is_failover_ready bit, isdone bit);\r\n\r\nIF EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tmpdbfiledetail'))\r\nDROP TABLE #tmpdbfiledetail;\r\nIF NOT EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tmpdbfiledetail'))\r\nCREATE TABLE #tmpdbfiledetail([database_id] [int] NOT NULL, [file_id] int, [type_desc] NVARCHAR(60), [data_space_id] int, [name] sysname, [physical_name] NVARCHAR(260), [state_desc] NVARCHAR(60), [size] bigint, [max_size] bigint, [is_percent_growth] bit, [growth] int, [is_media_read_only] bit, [is_read_only] bit, [is_sparse] bit, [is_name_reserved] bit)\r\n\r\nIF EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.##tmpdbsizes'))\r\nDROP TABLE ##tmpdbsizes;\r\nIF NOT EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.##tmpdbsizes'))\r\nCREATE TABLE ##tmpdbsizes([database_id] [int] NOT NULL, [size] bigint, [type_desc] NVARCHAR(60))\r\n\r\n-- Get DB info\r\nDECLARE @sqlmajorver int\r\nDECLARE @sqlcmd NVARCHAR(max), @params NVARCHAR(600)\r\nDECLARE @dbid int, @dbname NVARCHAR(1000)\r\nDECLARE @ErrorMessage NVARCHAR(4000)\r\n\r\nSELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);\r\n\r\nIF @sqlmajorver < 11\r\nBEGIN\r\n\tSET @sqlcmd = 'SELECT database_id, name, [compatibility_level], is_read_only, [state], is_distributor, 1, 1, 0 FROM master.sys.databases (NOLOCK)'\r\n\tINSERT INTO #tmpdbs0 ([dbid], [dbname], [compatibility_level], is_read_only, [state], is_distributor, [role], [secondary_role_allow_connections], [isdone])\r\n\tEXEC sp_executesql @sqlcmd;\r\nEND;\r\n\r\nIF @sqlmajorver > 10\r\nBEGIN\r\n\tSET @sqlcmd = 'SELECT sd.database_id, sd.name, sd.[compatibility_level], sd.is_read_only, sd.[state], sd.is_distributor, MIN(COALESCE(ars.[role],1)) AS [role], ar.secondary_role_allow_connections, rcs.is_database_joined, rcs.is_failover_ready, 0 \r\n\tFROM master.sys.databases (NOLOCK) sd\r\n\t\tLEFT JOIN sys.dm_hadr_database_replica_states (NOLOCK) d ON sd.database_id = d.database_id\r\n\t\tLEFT JOIN sys.availability_replicas ar (NOLOCK) ON d.group_id = ar.group_id AND d.replica_id = ar.replica_id\r\n\t\tLEFT JOIN sys.dm_hadr_availability_replica_states (NOLOCK) ars ON d.group_id = ars.group_id AND d.replica_id = ars.replica_id\r\n\t\tLEFT JOIN sys.dm_hadr_database_replica_cluster_states (NOLOCK) rcs ON rcs.database_name = sd.name AND rcs.replica_id = ar.replica_id\r\n\tGROUP BY sd.database_id, sd.name, sd.is_read_only, sd.[state], sd.is_distributor, ar.secondary_role_allow_connections, sd.[compatibility_level], rcs.is_database_joined, rcs.is_failover_ready;'\r\n\tINSERT INTO #tmpdbs0 ([dbid], [dbname], [compatibility_level], is_read_only, [state], is_distributor, [role], [secondary_role_allow_connections], is_database_joined, is_failover_ready, [isdone])\r\n\tEXEC sp_executesql @sqlcmd;\r\nEND;\r\n\r\n/* Validate if database scope is set */\r\nIF @dbScope IS NOT NULL AND ISNUMERIC(@dbScope) <> 1 AND @dbScope NOT LIKE '%,%'\r\nBEGIN\r\n\tRAISERROR('ERROR: Invalid parameter. Valid input consists of database IDs. If more than one ID is specified, the values must be comma separated.', 16, 42) WITH NOWAIT;\r\n\tRETURN\r\nEND;\r\n\t\r\nIF @dbScope IS NOT NULL\r\nBEGIN\r\n    RAISERROR (N'Applying specific database scope list, if any', 10, 1) WITH NOWAIT\r\n\tSELECT @sqlcmd = 'DELETE FROM #tmpdbs0 WHERE [dbid] > 4 AND [dbid] NOT IN (' + REPLACE(@dbScope,' ','') + ')'\r\n\tEXEC sp_executesql @sqlcmd;\r\nEND;\r\n\r\n/* Populate data file info*/\r\nWHILE (SELECT COUNT(id) FROM #tmpdbs0 WHERE isdone = 0) > 0\r\nBEGIN\r\n\tSELECT TOP 1 @curdbname = [dbname], @curdbid = [dbid], @currole = [role], @state = [state], @cursecondary_role_allow_connections = secondary_role_allow_connections FROM #tmpdbs0 WHERE isdone = 0\r\n\tIF (@currole = 2 AND @cursecondary_role_allow_connections = 0) OR @state <> 0\r\n\tBEGIN\r\n\t\tSET @sqlcmd = 'SELECT [database_id], [file_id], type_desc, data_space_id, name, physical_name, state_desc, size, max_size, is_percent_growth,growth, is_media_read_only, is_read_only, is_sparse, is_name_reserved\r\nFROM sys.master_files (NOLOCK) WHERE [database_id] = ' + CONVERT(VARCHAR(10), @curdbid)\r\n\tEND\r\n\tELSE\r\n\tBEGIN\r\n\t\tSET @sqlcmd = 'USE ' + QUOTENAME(@curdbname) + ';\r\nSELECT ' + CONVERT(VARCHAR(10), @curdbid) + ' AS [database_id], [file_id], type_desc, data_space_id, name, physical_name, state_desc, size, max_size, is_percent_growth,growth, is_media_read_only, is_read_only, is_sparse, is_name_reserved\r\nFROM sys.database_files (NOLOCK)'\r\n\tEND\r\n\r\n\tBEGIN TRY\r\n\t\tINSERT INTO #tmpdbfiledetail\r\n\t\tEXECUTE sp_executesql @sqlcmd\r\n\tEND TRY\r\n\tBEGIN CATCH\r\n\t\tSELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;\r\n\t\tSELECT @ErrorMessage = 'Database Information subsection - Error raised in TRY block. ' + ERROR_MESSAGE()\r\n\t\tRAISERROR (@ErrorMessage, 16, 1);\r\n\tEND CATCH\r\n\t\r\n\tUPDATE #tmpdbs0\r\n\tSET isdone = 1\r\n\tWHERE [dbid] = @curdbid\r\nEND;\r\n\r\nBEGIN TRY\r\n\tINSERT INTO ##tmpdbsizes([database_id], [size], [type_desc])\r\n\tSELECT [database_id], SUM([size]) AS [size], [type_desc]\r\n\tFROM #tmpdbfiledetail\r\n\tWHERE [type_desc] <> 'LOG'\r\n\tGROUP BY [database_id], [type_desc]\r\nEND TRY\r\nBEGIN CATCH\r\n\tSELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;\r\n\tSELECT @ErrorMessage = 'Database Information subsection - Error raised in TRY block. ' + ERROR_MESSAGE()\r\n\tRAISERROR (@ErrorMessage, 16, 1);\r\nEND CATCH\r\n\r\nIF @sqlmajorver < 11\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT ''Information'' AS [Category], ''Databases'' AS [Information],\r\n\tdb.[name] AS [Database_Name], SUSER_SNAME(db.owner_sid) AS [Owner_Name], db.[database_id], \r\n\tdb.recovery_model_desc AS [Recovery_Model], db.create_date, db.log_reuse_wait_desc AS [Log_Reuse_Wait_Description], \r\n\t(dbsize.[size]*8)/1024 AS [Data_Size_MB], ISNULL((dbfssize.[size]*8)/1024,0) AS [Filestream_Size_MB], \r\n\tls.cntr_value/1024 AS [Log_Size_MB], lu.cntr_value/1024 AS [Log_Used_MB],\r\n\tCAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log_Used_pct], \r\n\tdb.[compatibility_level] AS [Compatibility_Level], db.collation_name AS [DB_Collation], \r\n\tdb.page_verify_option_desc AS [Page_Verify_Option], db.is_auto_create_stats_on, db.is_auto_update_stats_on,\r\n\tdb.is_auto_update_stats_async_on, db.is_parameterization_forced, \r\n\tdb.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,\r\n\tdb.is_read_only, db.is_auto_close_on, db.is_auto_shrink_on, ''NA'' AS [is_indirect_checkpoint_on], \r\n\tdb.is_trustworthy_on, db.is_db_chaining_on, db.is_parameterization_forced\r\nFROM master.sys.databases AS db (NOLOCK)\r\nINNER JOIN ##tmpdbsizes AS dbsize (NOLOCK) ON db.database_id = dbsize.database_id\r\nINNER JOIN sys.dm_os_performance_counters AS lu (NOLOCK) ON db.name = lu.instance_name\r\nINNER JOIN sys.dm_os_performance_counters AS ls (NOLOCK) ON db.name = ls.instance_name\r\nLEFT JOIN ##tmpdbsizes AS dbfssize (NOLOCK) ON db.database_id = dbfssize.database_id AND dbfssize.[type_desc] = ''FILESTREAM''\r\nWHERE dbsize.[type_desc] = ''ROWS''\r\n\tAND dbfssize.[type_desc] = ''FILESTREAM''\r\n\tAND lu.counter_name LIKE N''Log File(s) Used Size (KB)%'' \r\n\tAND ls.counter_name LIKE N''Log File(s) Size (KB)%''\r\n\tAND ls.cntr_value > 0 AND ls.cntr_value > 0' + CASE WHEN @dbScope IS NOT NULL THEN CHAR(10) + ' AND db.[database_id] IN (' + REPLACE(@dbScope,' ','') + ')' ELSE '' END + '\r\nORDER BY [Database_Name]\t\r\nOPTION (RECOMPILE)'\r\nEND\r\nELSE IF @sqlmajorver = 11\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT ''Information'' AS [Category], ''Databases'' AS [Information],\r\n\tdb.[name] AS [Database_Name], SUSER_SNAME(db.owner_sid) AS [Owner_Name], db.[database_id], \r\n\tdb.recovery_model_desc AS [Recovery_Model], db.create_date, db.log_reuse_wait_desc AS [Log_Reuse_Wait_Description], \r\n\t(dbsize.[size]*8)/1024 AS [Data_Size_MB], ISNULL((dbfssize.[size]*8)/1024,0) AS [Filestream_Size_MB], \r\n\tls.cntr_value/1024 AS [Log_Size_MB], lu.cntr_value/1024 AS [Log_Used_MB],\r\n\tCAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log_Used_pct], \r\n\tdb.[compatibility_level] AS [Compatibility_Level], db.collation_name AS [DB_Collation], \r\n\tdb.page_verify_option_desc AS [Page_Verify_Option], db.is_auto_create_stats_on, db.is_auto_update_stats_on,\r\n\tdb.is_auto_update_stats_async_on, db.is_parameterization_forced, \r\n\tdb.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,\r\n\tdb.is_read_only, db.is_auto_close_on, db.is_auto_shrink_on, \r\n\tCASE WHEN db.target_recovery_time_in_seconds > 0 THEN 1 ELSE 0 END AS is_indirect_checkpoint_on,\r\n\tdb.target_recovery_time_in_seconds, db.is_encrypted, db.is_trustworthy_on, db.is_db_chaining_on, db.is_parameterization_forced\r\nFROM master.sys.databases AS db (NOLOCK)\r\nINNER JOIN ##tmpdbsizes AS dbsize (NOLOCK) ON db.database_id = dbsize.database_id\r\nINNER JOIN sys.dm_os_performance_counters AS lu (NOLOCK) ON db.name = lu.instance_name\r\nINNER JOIN sys.dm_os_performance_counters AS ls (NOLOCK) ON db.name = ls.instance_name\r\nLEFT JOIN ##tmpdbsizes AS dbfssize (NOLOCK) ON db.database_id = dbfssize.database_id AND dbfssize.[type_desc] = ''FILESTREAM''\r\nWHERE dbsize.[type_desc] = ''ROWS''\r\n\tAND lu.counter_name LIKE N''Log File(s) Used Size (KB)%'' \r\n\tAND ls.counter_name LIKE N''Log File(s) Size (KB)%''\r\n\tAND ls.cntr_value > 0 AND ls.cntr_value > 0' + CASE WHEN @dbScope IS NOT NULL THEN CHAR(10) + ' AND db.[database_id] IN (' + REPLACE(@dbScope,' ','') + ')' ELSE '' END + '\r\nORDER BY [Database_Name]\t\r\nOPTION (RECOMPILE)'\r\nEND\r\nELSE IF @sqlmajorver = 12\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT ''Information'' AS [Category], ''Databases'' AS [Information],\r\n\tdb.[name] AS [Database_Name], SUSER_SNAME(db.owner_sid) AS [Owner_Name], db.[database_id], \r\n\tdb.recovery_model_desc AS [Recovery_Model], db.create_date, db.log_reuse_wait_desc AS [Log_Reuse_Wait_Description], \r\n\t(dbsize.[size]*8)/1024 AS [Data_Size_MB], ISNULL((dbfssize.[size]*8)/1024,0) AS [Filestream_Size_MB], \r\n\tls.cntr_value/1024 AS [Log_Size_MB], lu.cntr_value/1024 AS [Log_Used_MB],\r\n\tCAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log_Used_pct], \r\n\tdb.[compatibility_level] AS [Compatibility_Level], db.collation_name AS [DB_Collation], \r\n\tdb.page_verify_option_desc AS [Page_Verify_Option], db.is_auto_create_stats_on, db.is_auto_create_stats_incremental_on,\r\n\tdb.is_auto_update_stats_on, db.is_auto_update_stats_async_on, db.delayed_durability_desc AS [delayed_durability_status], \r\n\tdb.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,\r\n\tdb.is_read_only, db.is_auto_close_on, db.is_auto_shrink_on,\r\n\tCASE WHEN db.target_recovery_time_in_seconds > 0 THEN 1 ELSE 0 END AS is_indirect_checkpoint_on,\r\n\tdb.target_recovery_time_in_seconds, db.is_encrypted, db.is_trustworthy_on, db.is_db_chaining_on, db.is_parameterization_forced\r\nFROM master.sys.databases AS db (NOLOCK)\r\nINNER JOIN ##tmpdbsizes AS dbsize (NOLOCK) ON db.database_id = dbsize.database_id\r\nINNER JOIN sys.dm_os_performance_counters AS lu (NOLOCK) ON db.name = lu.instance_name\r\nINNER JOIN sys.dm_os_performance_counters AS ls (NOLOCK) ON db.name = ls.instance_name\r\nLEFT JOIN ##tmpdbsizes AS dbfssize (NOLOCK) ON db.database_id = dbfssize.database_id AND dbfssize.[type_desc] = ''FILESTREAM''\r\nWHERE dbsize.[type_desc] = ''ROWS''\r\n\tAND lu.counter_name LIKE N''Log File(s) Used Size (KB)%'' \r\n\tAND ls.counter_name LIKE N''Log File(s) Size (KB)%''\r\n\tAND ls.cntr_value > 0 AND ls.cntr_value > 0' + CASE WHEN @dbScope IS NOT NULL THEN CHAR(10) + ' AND db.[database_id] IN (' + REPLACE(@dbScope,' ','') + ')' ELSE '' END + '\r\nORDER BY [Database_Name]\t\r\nOPTION (RECOMPILE)'\r\nEND\r\nELSE IF @sqlmajorver >= 13\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT ''Information'' AS [Category], ''Databases'' AS [Information],\r\n\tdb.[name] AS [Database_Name], SUSER_SNAME(db.owner_sid) AS [Owner_Name], db.[database_id], \r\n\tdb.recovery_model_desc AS [Recovery_Model], db.create_date, db.log_reuse_wait_desc AS [Log_Reuse_Wait_Description], \r\n\t(dbsize.[size]*8)/1024 AS [Data_Size_MB], ISNULL((dbfssize.[size]*8)/1024,0) AS [Filestream_Size_MB], \r\n\tls.cntr_value/1024 AS [Log_Size_MB], lu.cntr_value/1024 AS [Log_Used_MB],\r\n\tCAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log_Used_pct], \r\n\tdb.[compatibility_level] AS [Compatibility_Level], db.collation_name AS [DB_Collation], \r\n\tdb.page_verify_option_desc AS [Page_Verify_Option], db.is_auto_create_stats_on, db.is_auto_create_stats_incremental_on,\r\n\tdb.is_auto_update_stats_on, db.is_auto_update_stats_async_on, db.delayed_durability_desc AS [delayed_durability_status], \r\n\tdb.is_query_store_on, db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,\r\n\tdb.is_read_only, db.is_auto_close_on, db.is_auto_shrink_on, \r\n\tCASE WHEN db.target_recovery_time_in_seconds > 0 THEN 1 ELSE 0 END AS is_indirect_checkpoint_on,\r\n\tdb.target_recovery_time_in_seconds, db.is_encrypted, db.is_trustworthy_on, db.is_db_chaining_on, db.is_parameterization_forced, \r\n\tdb.is_memory_optimized_elevate_to_snapshot_on, db.is_remote_data_archive_enabled, db.is_mixed_page_allocation_on\r\nFROM master.sys.databases AS db (NOLOCK)\r\nINNER JOIN sys.dm_os_performance_counters AS lu (NOLOCK) ON db.name = lu.instance_name\r\nINNER JOIN sys.dm_os_performance_counters AS ls (NOLOCK) ON db.name = ls.instance_name\r\nINNER JOIN ##tmpdbsizes AS dbsize (NOLOCK) ON db.database_id = dbsize.database_id\r\nLEFT JOIN ##tmpdbsizes AS dbfssize (NOLOCK) ON db.database_id = dbfssize.database_id AND dbfssize.[type_desc] = ''FILESTREAM''\r\nWHERE dbsize.[type_desc] = ''ROWS''\r\n\tAND lu.counter_name LIKE N''Log File(s) Used Size (KB)%'' \r\n\tAND ls.counter_name LIKE N''Log File(s) Size (KB)%''\r\n\tAND ls.cntr_value > 0 AND ls.cntr_value > 0' + CASE WHEN @dbScope IS NOT NULL THEN CHAR(10) + ' AND db.[database_id] IN (' + REPLACE(@dbScope,' ','') + ')' ELSE '' END + '\r\nORDER BY [Database_Name]\t\r\nOPTION (RECOMPILE)'\r\nEND\r\nELSE IF @sqlmajorver >= 14\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT ''Information'' AS [Category], ''Databases'' AS [Information],\r\n\tdb.[name] AS [Database_Name], SUSER_SNAME(db.owner_sid) AS [Owner_Name], db.[database_id], \r\n\tdb.recovery_model_desc AS [Recovery_Model], db.create_date, db.log_reuse_wait_desc AS [Log_Reuse_Wait_Description], \r\n\t(dbsize.[size]*8)/1024 AS [Data_Size_MB], ISNULL((dbfssize.[size]*8)/1024,0) AS [Filestream_Size_MB], \r\n\tls.cntr_value/1024 AS [Log_Size_MB], lu.cntr_value/1024 AS [Log_Used_MB],\r\n\tCAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log_Used_pct],\r\n\tCASE WHEN ssu.reserved_space_kb>0 THEN ssu.reserved_space_kb/1024 ELSE 0 END AS [Version_Store_Size_MB],\r\n\tdb.[compatibility_level] AS [Compatibility_Level], db.collation_name AS [DB_Collation], \r\n\tdb.page_verify_option_desc AS [Page_Verify_Option], db.is_auto_create_stats_on, db.is_auto_create_stats_incremental_on,\r\n\tdb.is_auto_update_stats_on, db.is_auto_update_stats_async_on, db.delayed_durability_desc AS [delayed_durability_status], \r\n\tdb.is_query_store_on, db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,\r\n\tdb.is_read_only, db.is_auto_close_on, db.is_auto_shrink_on, \r\n\tCASE WHEN db.target_recovery_time_in_seconds > 0 THEN 1 ELSE 0 END AS is_indirect_checkpoint_on,\r\n\tdb.target_recovery_time_in_seconds, db.is_encrypted, db.is_trustworthy_on, db.is_db_chaining_on, db.is_parameterization_forced, \r\n\tdb.is_memory_optimized_elevate_to_snapshot_on, db.is_remote_data_archive_enabled, db.is_mixed_page_allocation_on\r\nFROM master.sys.databases AS db (NOLOCK)\r\nINNER JOIN ##tmpdbsizes AS dbsize (NOLOCK) ON db.database_id = dbsize.database_id\r\nINNER JOIN sys.dm_os_performance_counters AS lu (NOLOCK) ON db.name = lu.instance_name\r\nINNER JOIN sys.dm_os_performance_counters AS ls (NOLOCK) ON db.name = ls.instance_name\r\nLEFT JOIN ##tmpdbsizes AS dbfssize (NOLOCK) ON db.database_id = dbfssize.database_id AND dbfssize.[type_desc] = ''FILESTREAM''\r\nLEFT JOIN sys.dm_tran_version_store_space_usage AS ssu (NOLOCK) ON db.database_id = ssu.database_id\r\nWHERE dbsize.[type_desc] = ''ROWS''\r\n\tAND lu.counter_name LIKE N''Log File(s) Used Size (KB)%'' \r\n\tAND ls.counter_name LIKE N''Log File(s) Size (KB)%''\r\n\tAND ls.cntr_value > 0 AND ls.cntr_value > 0' + CASE WHEN @dbScope IS NOT NULL THEN CHAR(10) + ' AND db.[database_id] IN (' + REPLACE(@dbScope,' ','') + ')' ELSE '' END + '\r\nORDER BY [Database_Name]\t\r\nOPTION (RECOMPILE)'\r\nEND\r\n\r\nEXECUTE sp_executesql @sqlcmd;\r\n\t\r\nSELECT 'Information' AS [Category], 'Database_Files' AS [Information], DB_NAME(database_id) AS [Database_Name], [file_id], type_desc, data_space_id AS [Filegroup], name, physical_name,\r\n\tstate_desc, (size * 8) / 1024 AS size_MB, CASE max_size WHEN -1 THEN 'Unlimited' ELSE CONVERT(VARCHAR(10), max_size) END AS max_size,\r\n\tCASE WHEN is_percent_growth = 0 THEN CONVERT(VARCHAR(10),((growth * 8) / 1024)) ELSE growth END AS [growth], CASE WHEN is_percent_growth = 1 THEN 'Pct' ELSE 'MB' END AS growth_type,\r\n\tis_media_read_only, is_read_only, is_sparse, is_name_reserved\r\nFROM #tmpdbfiledetail\r\nORDER BY database_id, [file_id];\r\n\r\nIF @sqlmajorver >= 12\r\nBEGIN\r\n\tIF EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tblInMemDBs'))\r\n\tDROP TABLE #tblInMemDBs;\r\n\tIF NOT EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tblInMemDBs'))\r\n\tCREATE TABLE #tblInMemDBs ([DBName] sysname, [Has_MemoryOptimizedObjects] bit, [MemoryAllocated_MemoryOptimizedObjects_KB] DECIMAL(18,2), [MemoryUsed_MemoryOptimizedObjects_KB] DECIMAL(18,2));\r\n\t\r\n\tUPDATE #tmpdbs0\r\n\tSET isdone = 0;\r\n\r\n\tUPDATE #tmpdbs0\r\n\tSET isdone = 1\r\n\tWHERE [state] <> 0 OR [dbid] < 5;\r\n\r\n\tUPDATE #tmpdbs0\r\n\tSET isdone = 1\r\n\tWHERE [role] = 2 AND secondary_role_allow_connections = 0;\r\n\t\r\n\tIF (SELECT COUNT(id) FROM #tmpdbs0 WHERE isdone = 0) > 0\r\n\tBEGIN\r\n\t\tRAISERROR (N'Starting Storage analysis for In-Memory OLTP Engine', 10, 1) WITH NOWAIT\r\n\t\r\n\t\tWHILE (SELECT COUNT(id) FROM #tmpdbs0 WHERE isdone = 0) > 0\r\n\t\tBEGIN\r\n\t\t\tSELECT TOP 1 @dbname = [dbname], @dbid = [dbid] FROM #tmpdbs0 WHERE isdone = 0\r\n\t\t\t\r\n\t\t\tSET @sqlcmd = 'USE ' + QUOTENAME(@dbname) + ';\r\nSELECT ''' + REPLACE(@dbname, CHAR(39), CHAR(95)) + ''' AS [DBName], ISNULL((SELECT 1 FROM sys.filegroups FG WHERE FG.[type] = ''FX''), 0) AS [Has_MemoryOptimizedObjects],\r\nISNULL((SELECT CONVERT(DECIMAL(18,2), (SUM(tms.memory_allocated_for_table_kb) + SUM(tms.memory_allocated_for_indexes_kb))) FROM sys.dm_db_xtp_table_memory_stats tms), 0.00) AS [MemoryAllocated_MemoryOptimizedObjects_KB],\r\nISNULL((SELECT CONVERT(DECIMAL(18,2),(SUM(tms.memory_used_by_table_kb) + SUM(tms.memory_used_by_indexes_kb))) FROM sys.dm_db_xtp_table_memory_stats tms), 0.00) AS [MemoryUsed_MemoryOptimizedObjects_KB];'\r\n\r\n\t\t\tBEGIN TRY\r\n\t\t\t\tINSERT INTO #tblInMemDBs\r\n\t\t\t\tEXECUTE sp_executesql @sqlcmd\r\n\t\t\tEND TRY\r\n\t\t\tBEGIN CATCH\r\n\t\t\t\tSELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;\r\n\t\t\t\tSELECT @ErrorMessage = 'Storage analysis for In-Memory OLTP Engine subsection - Error raised in TRY block. ' + ERROR_MESSAGE()\r\n\t\t\t\tRAISERROR (@ErrorMessage, 16, 1);\r\n\t\t\tEND CATCH\r\n\t\t\t\r\n\t\t\tUPDATE #tmpdbs0\r\n\t\t\tSET isdone = 1\r\n\t\t\tWHERE [dbid] = @dbid\r\n\t\tEND\r\n\tEND;\r\n\r\n\tIF (SELECT COUNT([DBName]) FROM #tblInMemDBs WHERE [Has_MemoryOptimizedObjects] = 1) > 0\r\n\tBEGIN\r\n\t\tSELECT 'Information' AS [Category], 'InMem_Database_Storage' AS [Information], DBName AS [Database_Name],\r\n\t\t\t[MemoryAllocated_MemoryOptimizedObjects_KB], [MemoryUsed_MemoryOptimizedObjects_KB]\r\n\t\tFROM #tblInMemDBs WHERE Has_MemoryOptimizedObjects = 1\r\n\t\tORDER BY DBName;\r\n\tEND\r\n\tELSE\r\n\tBEGIN\r\n\t\tSELECT 'Information' AS [Category], 'InMem_Database_Storage' AS [Information], 'NA' AS [Comment]\r\n\tEND\r\nEND;\r\n\r\n-- http://support.microsoft.com/kb/2857849\r\nDECLARE @IsHadrEnabled tinyint\r\n\r\nSELECT @IsHadrEnabled = CONVERT(tinyint, SERVERPROPERTY('IsHadrEnabled'))\r\n\r\nIF @sqlmajorver > 10 AND @IsHadrEnabled = 1\r\nBEGIN\r\n\tSELECT 'Information' AS [Category], 'AlwaysOn_AG_Databases' AS [Information], dc.database_name AS [Database_Name],\r\n\t\td.synchronization_health_desc, d.synchronization_state_desc, d.database_state_desc\r\n\tFROM sys.dm_hadr_database_replica_states d\r\n\tINNER JOIN sys.availability_databases_cluster dc ON d.group_database_id=dc.group_database_id\r\n\tWHERE d.is_local=1\r\nEND;",
            "metadata": {},
            "outputs": [],
            "execution_count": 39
        },
        {
            "cell_type": "markdown",
            "source": "Database file autogrows last 72h subsection",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\n\r\nDECLARE @sqlcmd NVARCHAR(max), @params NVARCHAR(600)\r\nDECLARE @sqlmajorver int, @sqlminorver int, @sqlbuild int\r\nDECLARE @ErrorMessage NVARCHAR(4000)\r\nDECLARE @ostype VARCHAR(10)\r\n\r\nSELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);\r\nSELECT @sqlminorver = CONVERT(int, (@@microsoftversion / 0x10000) & 0xff);\r\nSELECT @sqlbuild = CONVERT(int, @@microsoftversion & 0xffff);\r\n\r\nIF (@sqlmajorver >= 11) OR (@sqlmajorver = 10 AND @sqlminorver = 50 AND @sqlbuild >= 2500)\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT @ostypeOUT = ''Windows'' FROM sys.dm_os_windows_info (NOLOCK)';\r\n\tSET @params = N'@ostypeOUT VARCHAR(10) OUTPUT';\r\n\tEXECUTE sp_executesql @sqlcmd, @params, @ostypeOUT=@ostype OUTPUT;\r\nEND\r\nELSE\r\nBEGIN\r\n\tSET @ostype = 'Windows'\r\nEND;\r\n\r\nIF EXISTS (SELECT TOP 1 id FROM sys.traces WHERE is_default = 1)\r\nBEGIN\r\n\tDECLARE @tracefilename VARCHAR(500)\r\n\tIF @ostype = 'Windows'\r\n\tSELECT @tracefilename = LEFT([path],LEN([path]) - PATINDEX('%\\%', REVERSE([path]))) + '\\log.trc' FROM sys.traces WHERE is_default = 1;\r\n\t\r\n\tIF @ostype <> 'Windows'\r\n\tSELECT @tracefilename = LEFT([path],LEN([path]) - PATINDEX('%/%', REVERSE([path]))) + '/log.trc' FROM sys.traces WHERE is_default = 1;\r\n\r\n\tWITH AutoGrow_CTE (databaseid, [filename], Growth, Duration, StartTime, EndTime)\r\n\tAS\r\n\t(\r\n\tSELECT databaseid, [filename], SUM(IntegerData*8) AS Growth, Duration, StartTime, EndTime--, CASE WHEN EventClass =\r\n\tFROM sys.fn_trace_gettable(@tracefilename, default)\r\n\tWHERE EventClass >= 92 AND EventClass <= 95 AND DATEDIFF(hh,StartTime,GETDATE()) < 72 -- Last 24h\r\n\tGROUP BY databaseid, [filename], IntegerData, Duration, StartTime, EndTime\r\n\t)\r\n\tSELECT 'Information' AS [Category], 'Recorded_Autogrows_Lst72H' AS [Information], DB_NAME(database_id) AS Database_Name, \r\n\t\tmf.name AS logical_file_name, mf.size*8 / 1024 AS size_MB, mf.type_desc,\r\n\t\tag.Growth AS [growth_KB], CASE WHEN is_percent_growth = 1 THEN 'Pct' ELSE 'MB' END AS growth_type,\r\n\t\tDuration/1000 AS Growth_Duration_ms, ag.StartTime, ag.EndTime\r\n\tFROM sys.master_files mf\r\n\tLEFT OUTER JOIN AutoGrow_CTE ag ON mf.database_id=ag.databaseid AND mf.name=ag.[filename]\r\n\tWHERE ag.Growth > 0 --Only where growth occurred\r\n\tGROUP BY database_id, mf.name, mf.size, ag.Growth, ag.Duration, ag.StartTime, ag.EndTime, is_percent_growth, mf.growth, mf.type_desc\r\n\tORDER BY Database_Name, logical_file_name, ag.StartTime;\r\nEND\r\nELSE\r\nBEGIN\r\n\tSELECT 'Information' AS [Category], 'Recorded_Autogrows_Lst72H' AS [Information], 'WARNING: Could not gather information on autogrow times' AS [Comment]\r\nEND;",
            "metadata": {},
            "outputs": [],
            "execution_count": 44
        },
        {
            "cell_type": "markdown",
            "source": "Database triggers subsection",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\n\r\nDECLARE @dbScope VARCHAR(256)\r\nSET @dbScope = NULL --(NULL = All DBs)\r\n\r\nDECLARE @sqlcmd NVARCHAR(max), @params NVARCHAR(600)\r\nDECLARE @sqlmajorver int\r\nDECLARE @ErrorMessage NVARCHAR(4000)\r\nDECLARE @dbid int, @dbname NVARCHAR(1000)\r\n\r\n/* Validate if database scope is set */\r\nIF @dbScope IS NOT NULL AND ISNUMERIC(@dbScope) <> 1 AND @dbScope NOT LIKE '%,%'\r\nBEGIN\r\n\tRAISERROR('ERROR: Invalid parameter. Valid input consists of database IDs. If more than one ID is specified, the values must be comma separated.', 16, 42) WITH NOWAIT;\r\n\tRETURN\r\nEND;\r\n\t\r\nIF @dbScope IS NOT NULL\r\nBEGIN\r\n    RAISERROR (N'Applying specific database scope list', 10, 1) WITH NOWAIT\r\n\tSELECT @sqlcmd = 'DELETE FROM #tmpdbs0 WHERE [dbid] > 4 AND [dbid] NOT IN (' + REPLACE(@dbScope,' ','') + ')'\r\n\tEXEC sp_executesql @sqlcmd;\r\nEND;\r\n\r\nIF EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tblTriggers'))\r\nDROP TABLE #tblTriggers;\r\nIF NOT EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tblTriggers'))\r\nCREATE TABLE #tblTriggers ([DBName] sysname, [triggerName] sysname, [schemaName] sysname, [tableName] sysname, [type_desc] NVARCHAR(60), [parent_class_desc] NVARCHAR(60), [create_date] DATETIME, [modify_date] DATETIME, [is_disabled] bit, [is_instead_of_trigger] bit, [is_not_for_replication] bit);\r\n\r\nIF EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tmpdbs0'))\r\nDROP TABLE #tmpdbs0;\r\nIF NOT EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tmpdbs0'))\r\nCREATE TABLE #tmpdbs0 (id int IDENTITY(1,1), [dbid] int, [dbname] NVARCHAR(1000), [compatibility_level] tinyint, is_read_only bit, [state] tinyint, is_distributor bit, [role] tinyint, [secondary_role_allow_connections] tinyint, is_database_joined bit, is_failover_ready bit, isdone bit);\r\n\r\nSELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);\r\n\r\nIF @sqlmajorver < 11\r\nBEGIN\r\n\tSET @sqlcmd = 'SELECT database_id, name, [compatibility_level], is_read_only, [state], is_distributor, 1, 1, 0 FROM master.sys.databases (NOLOCK)'\r\n\tINSERT INTO #tmpdbs0 ([dbid], [dbname], [compatibility_level], is_read_only, [state], is_distributor, [role], [secondary_role_allow_connections], [isdone])\r\n\tEXEC sp_executesql @sqlcmd;\r\nEND;\r\n\r\nIF @sqlmajorver > 10\r\nBEGIN\r\n\tSET @sqlcmd = 'SELECT sd.database_id, sd.name, sd.[compatibility_level], sd.is_read_only, sd.[state], sd.is_distributor, MIN(COALESCE(ars.[role],1)) AS [role], ar.secondary_role_allow_connections, rcs.is_database_joined, rcs.is_failover_ready, 0 \r\n\tFROM master.sys.databases (NOLOCK) sd\r\n\t\tLEFT JOIN sys.dm_hadr_database_replica_states (NOLOCK) d ON sd.database_id = d.database_id\r\n\t\tLEFT JOIN sys.availability_replicas ar (NOLOCK) ON d.group_id = ar.group_id AND d.replica_id = ar.replica_id\r\n\t\tLEFT JOIN sys.dm_hadr_availability_replica_states (NOLOCK) ars ON d.group_id = ars.group_id AND d.replica_id = ars.replica_id\r\n\t\tLEFT JOIN sys.dm_hadr_database_replica_cluster_states (NOLOCK) rcs ON rcs.database_name = sd.name AND rcs.replica_id = ar.replica_id\r\n\tGROUP BY sd.database_id, sd.name, sd.is_read_only, sd.[state], sd.is_distributor, ar.secondary_role_allow_connections, sd.[compatibility_level], rcs.is_database_joined, rcs.is_failover_ready;'\r\n\tINSERT INTO #tmpdbs0 ([dbid], [dbname], [compatibility_level], is_read_only, [state], is_distributor, [role], [secondary_role_allow_connections], is_database_joined, is_failover_ready, [isdone])\r\n\tEXEC sp_executesql @sqlcmd;\r\nEND;\r\n\r\nUPDATE #tmpdbs0\r\nSET isdone = 1\r\nWHERE [state] <> 0 OR [dbid] < 5;\r\n\r\nUPDATE #tmpdbs0\r\nSET isdone = 1\r\nWHERE [role] = 2 AND secondary_role_allow_connections = 0;\r\n\r\nIF (SELECT COUNT(id) FROM #tmpdbs0 WHERE isdone = 0) > 0\r\nBEGIN\r\n    WHILE (SELECT COUNT(id) FROM #tmpdbs0 WHERE isdone = 0) > 0\r\n    BEGIN\r\n        SELECT TOP 1 @dbname = [dbname], @dbid = [dbid] FROM #tmpdbs0 WHERE isdone = 0\r\n        \r\n        SET @sqlcmd = 'USE ' + QUOTENAME(@dbname) + ';\r\nSELECT N''' + REPLACE(@dbname, CHAR(39), CHAR(95)) + ''' AS [DBName], st.name, ss.name, stb.name, st.type_desc, st.parent_class_desc, st.create_date, st.modify_date, st.is_disabled, st.is_instead_of_trigger, st.is_not_for_replication\r\nFROM sys.triggers AS st\r\nINNER JOIN sys.tables stb ON st.parent_id = stb.[object_id]\r\nINNER JOIN sys.schemas ss ON stb.[schema_id] = ss.[schema_id]\r\nWHERE st.is_ms_shipped = 0\r\nORDER BY stb.name, st.name;'\r\n\r\n        BEGIN TRY\r\n            INSERT INTO #tblTriggers\r\n            EXECUTE sp_executesql @sqlcmd\r\n        END TRY\r\n        BEGIN CATCH\r\n            SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;\r\n            SELECT @ErrorMessage = 'Database triggers subsection - Error raised in TRY block. ' + ERROR_MESSAGE()\r\n            RAISERROR (@ErrorMessage, 16, 1);\r\n        END CATCH\r\n        \r\n        UPDATE #tmpdbs0\r\n        SET isdone = 1\r\n        WHERE [dbid] = @dbid\r\n    END\r\nEND;\r\n\r\nIF (SELECT COUNT([triggerName]) FROM #tblTriggers) > 0\r\nBEGIN\r\n    SELECT 'Information' AS [Category], 'Database_Triggers' AS [Information], DBName AS [Database_Name],\r\n        triggerName AS [Trigger_Name], schemaName AS [Schema_Name], tableName AS [Table_Name], \r\n        type_desc AS [Trigger_Type], parent_class_desc AS [Trigger_Parent], \r\n        CASE is_instead_of_trigger WHEN 1 THEN 'INSTEAD_OF' ELSE 'AFTER' END AS [Trigger_Behavior],\r\n        create_date, modify_date, \r\n        CASE WHEN is_disabled = 1 THEN 'YES' ELSE 'NO' END AS [is_disabled], \r\n        CASE WHEN is_not_for_replication = 1 THEN 'YES' ELSE 'NO' END AS [is_not_for_replication]\r\n    FROM #tblTriggers\r\n    ORDER BY DBName, tableName, triggerName;\r\nEND\r\nELSE\r\nBEGIN\r\n    SELECT 'Information' AS [Category], 'Database_Triggers' AS [Information], 'NA' AS [Comment]\r\nEND;",
            "metadata": {},
            "outputs": [],
            "execution_count": 45
        },
        {
            "cell_type": "markdown",
            "source": "Feature usage subsection",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\n\r\nDECLARE @dbScope VARCHAR(256)\r\nSET @dbScope = NULL --(NULL = All DBs)\r\n\r\nDECLARE @dbid int, @dbname VARCHAR(1000)\r\nDECLARE @sqlcmd NVARCHAR(max), @params NVARCHAR(600)\r\nDECLARE @sqlmajorver int, @sqlbuild int\r\nDECLARE @ErrorMessage NVARCHAR(4000)\r\nDECLARE @IsHadrEnabled tinyint\r\n\r\n/* Validate if database scope is set */\r\nIF @dbScope IS NOT NULL AND ISNUMERIC(@dbScope) <> 1 AND @dbScope NOT LIKE '%,%'\r\nBEGIN\r\n\tRAISERROR('ERROR: Invalid parameter. Valid input consists of database IDs. If more than one ID is specified, the values must be comma separated.', 16, 42) WITH NOWAIT;\r\n\tRETURN\r\nEND;\r\n\t\r\nIF @dbScope IS NOT NULL\r\nBEGIN\r\n    RAISERROR (N'Applying specific database scope list', 10, 1) WITH NOWAIT\r\n\tSELECT @sqlcmd = 'DELETE FROM #tmpdbs0 WHERE [dbid] > 4 AND [dbid] NOT IN (' + REPLACE(@dbScope,' ','') + ')'\r\n\tEXEC sp_executesql @sqlcmd;\r\nEND;\r\n\r\nIF EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tmpdbs0'))\r\nDROP TABLE #tmpdbs0;\r\nIF NOT EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tmpdbs0'))\r\nCREATE TABLE #tmpdbs0 (id int IDENTITY(1,1), [dbid] int, [dbname] NVARCHAR(1000), [compatibility_level] tinyint, is_read_only bit, [state] tinyint, is_distributor bit, [role] tinyint, [secondary_role_allow_connections] tinyint, is_database_joined bit, is_failover_ready bit, isdone bit);\r\n\r\nSELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);\r\nSELECT @sqlbuild = CONVERT(int, @@microsoftversion & 0xffff);\r\nSELECT @IsHadrEnabled = CONVERT(tinyint, SERVERPROPERTY('IsHadrEnabled'))\r\n\r\nIF @sqlmajorver < 11\r\nBEGIN\r\n\tSET @sqlcmd = 'SELECT database_id, name, [compatibility_level], is_read_only, [state], is_distributor, 1, 1, 0 FROM master.sys.databases (NOLOCK)'\r\n\tINSERT INTO #tmpdbs0 ([dbid], [dbname], [compatibility_level], is_read_only, [state], is_distributor, [role], [secondary_role_allow_connections], [isdone])\r\n\tEXEC sp_executesql @sqlcmd;\r\nEND;\r\n\r\nIF @sqlmajorver > 10\r\nBEGIN\r\n\tSET @sqlcmd = 'SELECT sd.database_id, sd.name, sd.[compatibility_level], sd.is_read_only, sd.[state], sd.is_distributor, MIN(COALESCE(ars.[role],1)) AS [role], ar.secondary_role_allow_connections, rcs.is_database_joined, rcs.is_failover_ready, 0 \r\n\tFROM master.sys.databases (NOLOCK) sd\r\n\t\tLEFT JOIN sys.dm_hadr_database_replica_states (NOLOCK) d ON sd.database_id = d.database_id\r\n\t\tLEFT JOIN sys.availability_replicas ar (NOLOCK) ON d.group_id = ar.group_id AND d.replica_id = ar.replica_id\r\n\t\tLEFT JOIN sys.dm_hadr_availability_replica_states (NOLOCK) ars ON d.group_id = ars.group_id AND d.replica_id = ars.replica_id\r\n\t\tLEFT JOIN sys.dm_hadr_database_replica_cluster_states (NOLOCK) rcs ON rcs.database_name = sd.name AND rcs.replica_id = ar.replica_id\r\n\tGROUP BY sd.database_id, sd.name, sd.is_read_only, sd.[state], sd.is_distributor, ar.secondary_role_allow_connections, sd.[compatibility_level], rcs.is_database_joined, rcs.is_failover_ready;'\r\n\tINSERT INTO #tmpdbs0 ([dbid], [dbname], [compatibility_level], is_read_only, [state], is_distributor, [role], [secondary_role_allow_connections], is_database_joined, is_failover_ready, [isdone])\r\n\tEXEC sp_executesql @sqlcmd;\r\nEND;\r\n\r\nUPDATE #tmpdbs0\r\nSET isdone = 1\r\nWHERE [state] <> 0 OR [dbid] < 5;\r\n\r\nIF @sqlmajorver > 9\r\nBEGIN\r\n\tIF EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tblPerSku'))\r\n\tDROP TABLE #tblPerSku;\r\n\tIF NOT EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tblPerSku'))\r\n\tCREATE TABLE #tblPerSku ([DBName] sysname NULL, [Feature_Name] VARCHAR(100));\r\n\t\r\n\tUPDATE #tmpdbs0\r\n\tSET isdone = 0;\r\n\r\n\tUPDATE #tmpdbs0\r\n\tSET isdone = 1\r\n\tWHERE [state] <> 0 OR [dbid] < 5;\r\n\r\n\tUPDATE #tmpdbs0\r\n\tSET isdone = 1\r\n\tWHERE [role] = 2 AND secondary_role_allow_connections = 0;\r\n\t\r\n\tIF (SELECT COUNT(id) FROM #tmpdbs0 WHERE isdone = 0) > 0\r\n\tBEGIN\r\n\t\tWHILE (SELECT COUNT(id) FROM #tmpdbs0 WHERE isdone = 0) > 0\r\n\t\tBEGIN\r\n\t\t\tSELECT TOP 1 @dbname = [dbname], @dbid = [dbid] FROM #tmpdbs0 WHERE isdone = 0\r\n\t\t\t\r\n\t\t\tSET @sqlcmd = 'USE ' + QUOTENAME(@dbname) + ';\r\nSELECT ''' + REPLACE(@dbname, CHAR(39), CHAR(95)) + ''' AS [dbname], feature_name FROM sys.dm_db_persisted_sku_features (NOLOCK)\r\nUNION ALL\r\nSELECT ''' + REPLACE(@dbname, CHAR(39), CHAR(95)) + ''' AS [dbname], ''Change_Tracking'' AS feature_name FROM sys.change_tracking_databases (NOLOCK) WHERE database_id = DB_ID()\r\nUNION ALL\r\nSELECT TOP 1 ''' + REPLACE(@dbname, CHAR(39), CHAR(95)) + ''' AS [dbname], ''Fine_grained_auditing'' AS feature_name FROM sys.database_audit_specifications (NOLOCK)'\r\n\r\n\t\t\tIF @sqlmajorver >= 13\r\n\t\t\tSET @sqlcmd = @sqlcmd + CHAR(10) + 'UNION ALL\r\nSELECT TOP 1 ''' + REPLACE(@dbname, CHAR(39), CHAR(95)) + ''' AS [dbname], ''Polybase'' AS feature_name FROM sys.external_data_sources (NOLOCK)\r\nUNION ALL\r\nSELECT TOP 1 ''' + REPLACE(@dbname, CHAR(39), CHAR(95)) + ''' AS [dbname], ''Row_Level_Security'' AS feature_name FROM sys.security_policies (NOLOCK)\r\nUNION ALL\r\nSELECT TOP 1 ''' + REPLACE(@dbname, CHAR(39), CHAR(95)) + ''' AS [dbname], ''Always_Encrypted'' AS feature_name FROM sys.column_master_keys (NOLOCK)\r\nUNION ALL\r\nSELECT TOP 1 ''' + REPLACE(@dbname, CHAR(39), CHAR(95)) + ''' AS [dbname], ''Dynamic_Data_Masking'' AS feature_name FROM sys.masked_columns (NOLOCK) WHERE is_masked = 1'\r\n\r\n\t\t\tBEGIN TRY\r\n\t\t\t\tINSERT INTO #tblPerSku\r\n\t\t\t\tEXECUTE sp_executesql @sqlcmd\r\n\t\t\tEND TRY\r\n\t\t\tBEGIN CATCH\r\n\t\t\t\tSELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;\r\n\t\t\t\tSELECT @ErrorMessage = 'Feature usage subsection - Error raised in TRY block. ' + ERROR_MESSAGE()\r\n\t\t\t\tRAISERROR (@ErrorMessage, 16, 1);\r\n\t\t\tEND CATCH\r\n\t\t\t\r\n\t\t\tUPDATE #tmpdbs0\r\n\t\t\tSET isdone = 1\r\n\t\t\tWHERE [dbid] = @dbid\r\n\t\tEND\r\n\tEND;\r\n\t\r\n\tIF @sqlmajorver > 10 AND ((@sqlmajorver = 13 AND @sqlbuild < 4000) OR @sqlmajorver < 13) AND @IsHadrEnabled = 1\r\n\tBEGIN\r\n\t\tINSERT INTO #tblPerSku\r\n\t\tSELECT [dbname], 'Always_On' AS feature_name FROM #tmpdbs0 WHERE is_database_joined = 1;\r\n\tEND;\r\n\t\r\n\tIF (SELECT COUNT(DISTINCT [name]) FROM master.sys.databases (NOLOCK) WHERE database_id NOT IN (2,3) AND source_database_id IS NOT NULL) > 0 -- Snapshot\r\n\tBEGIN\r\n\t\tINSERT INTO #tblPerSku\r\n\t\tSELECT DISTINCT [name], 'DB_Snapshot' AS feature_name FROM master.sys.databases (NOLOCK) WHERE database_id NOT IN (2,3) AND source_database_id IS NOT NULL;\r\n\tEND;\r\n\r\n\tIF (SELECT COUNT(DISTINCT [name]) FROM master.sys.master_files (NOLOCK) WHERE database_id NOT IN (2,3) AND [type] = 2 and file_guid IS NOT NULL) > 0 -- Filestream\r\n\tBEGIN\r\n\t\tINSERT INTO #tblPerSku\r\n\t\tSELECT DISTINCT DB_NAME(database_id), 'Filestream' AS feature_name FROM sys.master_files (NOLOCK) WHERE database_id NOT IN (2,3) AND [type] = 2 and file_guid IS NOT NULL;\t\r\n\tEND;\r\n\t\r\n\tIF (SELECT COUNT([Feature_Name]) FROM #tblPerSku) > 0\r\n\tBEGIN\r\n\t\tSELECT 'Information' AS [Category], 'Feature_usage' AS [Check], 'INFORMATION: Some databases are using features that are not common to all editions' AS [Comment]\r\n\t\tSELECT 'Information' AS [Category], 'Feature_usage' AS [Information], DBName AS [Database_Name], [Feature_Name]\r\n\t\tFROM #tblPerSku\r\n\t\tORDER BY 2, 3\r\n\tEND\r\n\tELSE\r\n\tBEGIN\r\n\t\tSELECT 'Information' AS [Category], 'Feature_usage' AS [Check], 'NA' AS [Comment]\r\n\tEND\r\nEND;",
            "metadata": {},
            "outputs": [],
            "execution_count": 47
        },
        {
            "cell_type": "markdown",
            "source": "Backups since last Full Information subsection",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\nSET DATEFORMAT mdy;\r\n\r\nDECLARE @sqlcmd NVARCHAR(max), @params NVARCHAR(600)\r\nDECLARE @sqlmajorver int\r\n\r\nSELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);\r\n\r\nIF @sqlmajorver > 10\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT ''Information'' AS [Category], ''Backups_since_last_Full'' AS [Information], \r\n[database_name] AS [Database_Name], CASE WHEN type = ''D'' THEN ''Database''\r\n\tWHEN type = ''I'' THEN ''Diff_Database''\r\n\tWHEN type = ''L'' THEN ''Log''\r\n\tWHEN type = ''F'' THEN ''File''\r\n\tWHEN type = ''G'' THEN ''Diff_file''\r\n\tWHEN type = ''P'' THEN ''Partial''\r\n\tWHEN type = ''Q'' THEN ''Diff_partial''\r\n\tELSE NULL END AS [bck_type],\r\n[backup_start_date], [backup_finish_date],\r\nCONVERT(decimal(20,2),backup_size/1024.00/1024.00) AS [backup_size_MB],\r\nCONVERT(decimal(20,2),compressed_backup_size/1024.00/1024.00) AS [compressed_backup_size_MB],\r\n[recovery_model], [user_name],\r\ndatabase_backup_lsn AS [full_base_lsn], [differential_base_lsn], [expiration_date], \r\n[is_password_protected], [has_backup_checksums], [is_readonly], is_copy_only, [has_incomplete_metadata] AS [Tail_log]\r\nFROM msdb.dbo.backupset bck1 (NOLOCK)\r\nWHERE is_copy_only = 0 -- No COPY_ONLY backups\r\nAND backup_start_date >= (SELECT MAX(backup_start_date) FROM msdb.dbo.backupset bck2 (NOLOCK) WHERE bck2.type IN (''D'',''F'',''P'') AND is_copy_only = 0 AND bck1.database_name = bck2.database_name)\r\nORDER BY database_name, backup_start_date DESC'\r\nEND\r\nELSE \r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT ''Information'' AS [Category], ''Backups_since_last_Full'' AS [Information], \r\n[database_name] AS [Database_Name], CASE WHEN type = ''D'' THEN ''Database''\r\n\tWHEN type = ''I'' THEN ''Diff_Database''\r\n\tWHEN type = ''L'' THEN ''Log''\r\n\tWHEN type = ''F'' THEN ''File''\r\n\tWHEN type = ''G'' THEN ''Diff_file''\r\n\tWHEN type = ''P'' THEN ''Partial''\r\n\tWHEN type = ''Q'' THEN ''Diff_partial''\r\n\tELSE NULL END AS [bck_type],\r\n[backup_start_date], [backup_finish_date], \r\nCONVERT(decimal(20,2),backup_size/1024.00/1024.00) AS [backup_size_MB],\r\n''NA'' AS [compressed_backup_size_MB], \r\n[recovery_model], [user_name],\r\ndatabase_backup_lsn AS [full_base_lsn], [differential_base_lsn], [expiration_date], \r\n[is_password_protected], [has_backup_checksums], [is_readonly], is_copy_only, [has_incomplete_metadata] AS [Tail_log]\r\nFROM msdb.dbo.backupset bck1 (NOLOCK)\r\nWHERE is_copy_only = 0 -- No COPY_ONLY backups\r\nAND backup_start_date >= (SELECT MAX(backup_start_date) FROM msdb.dbo.backupset bck2 (NOLOCK) WHERE bck2.type IN (''D'',''F'',''P'') AND is_copy_only = 0 AND bck1.database_name = bck2.database_name)\r\nORDER BY database_name, backup_start_date DESC'\r\nEND;\r\n\r\nEXECUTE sp_executesql @sqlcmd;",
            "metadata": {},
            "outputs": [],
            "execution_count": 48
        },
        {
            "cell_type": "markdown",
            "source": "System Configuration subsection",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\n\r\nSELECT 'Information' AS [Category], 'All_System_Configurations' AS [Information],\r\n\tname AS [Name],\r\n\tconfiguration_id AS [Number],\r\n\tminimum AS [Minimum],\r\n\tmaximum AS [Maximum],\r\n\tis_dynamic AS [Dynamic],\r\n\tis_advanced AS [Advanced],\r\n\tvalue AS [ConfigValue],\r\n\tvalue_in_use AS [RunValue],\r\n\tdescription AS [Description]\r\nFROM sys.configurations (NOLOCK)\r\nORDER BY name OPTION (RECOMPILE);",
            "metadata": {},
            "outputs": [],
            "execution_count": 49
        },
        {
            "cell_type": "markdown",
            "source": "Checks section",
            "metadata": {}
        },
        {
            "cell_type": "markdown",
            "source": "Number of available Processors for this instance vs. MaxDOP setting subsection",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\n\r\nDECLARE @cpucount int, @numa int, @affined_cpus int, @affinitymask NVARCHAR(64), @affinity64mask NVARCHAR(1024)--, @cpuover32 int\r\nDECLARE @i int, @cpuaffin VARCHAR(300), @cpuaffin_fixed VARCHAR(1024), @sqlmajorver int\r\n\r\nSELECT @numa = COUNT(DISTINCT parent_node_id) FROM sys.dm_os_schedulers WHERE scheduler_id < 255 AND parent_node_id < 64;\r\n-- SELECT @numa = numa_node_count FROM sys.dm_os_sys_info;\r\nSELECT @cpucount = COUNT(cpu_id) FROM sys.dm_os_schedulers WHERE scheduler_id < 255 AND parent_node_id < 64\r\n-- SELECT @cpucount = cpu_count FROM sys.dm_os_sys_info;\r\nSELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);\r\n\r\n;WITH bits AS \r\n(SELECT 7 AS N, 128 AS E UNION ALL SELECT 6, 64 UNION ALL \r\nSELECT 5, 32 UNION ALL SELECT 4, 16 UNION ALL SELECT 3, 8 UNION ALL \r\nSELECT 2, 4 UNION ALL SELECT 1, 2 UNION ALL SELECT 0, 1), \r\nbytes AS \r\n(SELECT 1 M UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL \r\nSELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL \r\nSELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9)\r\n-- CPU Affinity is shown highest to lowest CPU ID\r\nSELECT @affinitymask = CASE WHEN [value] = 0 THEN REPLICATE('1', @cpucount)\r\n\tELSE RIGHT((SELECT ((CONVERT(tinyint, SUBSTRING(CONVERT(binary(9), [value]), M, 1)) & E) / E) AS [text()] \r\n\t\tFROM bits CROSS JOIN bytes\r\n\t\tORDER BY M, N DESC\r\n\t\tFOR XML PATH('')), @cpucount) END\r\nFROM sys.configurations (NOLOCK)\r\nWHERE name = 'affinity mask';\r\n\r\nIF @cpucount > 32\r\nBEGIN\r\n\t;WITH bits AS \r\n\t(SELECT 7 AS N, 128 AS E UNION ALL SELECT 6, 64 UNION ALL \r\n\tSELECT 5, 32 UNION ALL SELECT 4, 16 UNION ALL SELECT 3, 8 UNION ALL \r\n\tSELECT 2, 4 UNION ALL SELECT 1, 2 UNION ALL SELECT 0, 1), \r\n\tbytes AS \r\n\t(SELECT 1 M UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL \r\n\tSELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL \r\n\tSELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9)\r\n\t-- CPU Affinity is shown highest to lowest CPU ID\r\n\tSELECT @affinity64mask = CASE WHEN [value] = 0 THEN REPLICATE('1', @cpucount)\r\n\t\tELSE RIGHT((SELECT ((CONVERT(tinyint, SUBSTRING(CONVERT(binary(9), [value]), M, 1)) & E) / E) AS [text()] \r\n\t\t\tFROM bits CROSS JOIN bytes\r\n\t\t\tORDER BY M, N DESC\r\n\t\t\tFOR XML PATH('')), @cpucount) END\r\n\tFROM sys.configurations (NOLOCK)\r\n\tWHERE name = 'affinity64 mask';\r\nEND;\r\n\r\n/*\r\nIF @cpucount > 32\r\nSELECT @cpuover32 = ABS(LEN(@affinity64mask) - (@cpucount-32))\r\n\r\nSELECT @cpuaffin = CASE WHEN @cpucount > 32 THEN REVERSE(LEFT(REVERSE(@affinity64mask),@cpuover32)) + RIGHT(@affinitymask,32) ELSE RIGHT(@affinitymask,@cpucount) END\r\n*/\r\n\r\nSELECT @cpuaffin = CASE WHEN @cpucount > 32 THEN @affinity64mask ELSE @affinitymask END\r\n\r\nSET @cpuaffin_fixed = @cpuaffin\r\nSET @i = CEILING(@cpucount*1.00/@numa) + 1\r\nWHILE @i < @cpucount + @numa\r\nBEGIN\r\n\tIF (@cpucount + @numa) - @i >= CEILING(@cpucount*1.00/@numa)\r\n\tBEGIN\r\n\t\tSELECT @cpuaffin_fixed = STUFF(@cpuaffin_fixed, @i, 1, '_' + SUBSTRING(@cpuaffin_fixed, @i, 1))\r\n\tEND\r\n\tELSE\r\n\tBEGIN\r\n\t\tSELECT @cpuaffin_fixed = STUFF(@cpuaffin_fixed, @i, CEILING(@cpucount*1.00/@numa), SUBSTRING(@cpuaffin_fixed, @i, CEILING(@cpucount*1.00/@numa)))\r\n\tEND\r\n\r\n\tSET @i = @i + CEILING(@cpucount*1.00/@numa) + 1\r\nEND;\r\n\r\n-- MaxDOP should be between 8 and 15. This is handled specifically on NUMA scenarios below.\r\nSELECT @affined_cpus = COUNT(cpu_id) FROM sys.dm_os_schedulers WHERE is_online = 1 AND scheduler_id < 255 AND parent_node_id < 64;\r\n\r\nSELECT 'Processor_checks' AS [Category], 'Parallelism_MaxDOP' AS [Check],\r\n\tCASE WHEN [value] > @affined_cpus THEN 'WARNING: MaxDOP setting exceeds available processor count (affinity)'\r\n\t\tWHEN @numa = 1 AND @affined_cpus <= 8 AND [value] > 0 AND [value] <> @affined_cpus THEN 'WARNING: MaxDOP setting is not recommended for current processor count (affinity)'\r\n\t\tWHEN @numa = 1 AND @affined_cpus > 8 AND ([value] = 0 OR [value] > 8) THEN 'WARNING: MaxDOP setting is not recommended for current processor count (affinity)'\r\n\t\tWHEN @sqlmajorver >= 13 AND @numa > 1 AND CEILING(@cpucount*1.00/@numa) <= 15 AND ([value] = 0 OR [value] > CEILING(@cpucount*1.00/@numa)) THEN 'WARNING: MaxDOP setting is not recommended for current NUMA node to processor count (affinity) ratio'\r\n\t\tWHEN @sqlmajorver >= 13 AND @numa > 1 AND CEILING(@cpucount*1.00/@numa) > 15 AND ([value] = 0 OR [value] > CEILING(@cpucount*1.00/@numa/2)) THEN 'WARNING: MaxDOP setting is not recommended for current NUMA node to processor count (affinity) ratio'\r\n\t\tWHEN @sqlmajorver < 13 AND @numa > 1 AND CEILING(@cpucount*1.00/@numa) < 8 AND ([value] = 0 OR [value] > CEILING(@cpucount*1.00/@numa)) THEN 'WARNING: MaxDOP setting is not recommended for current NUMA node to processor count (affinity) ratio'\r\n\t\tWHEN @sqlmajorver < 13 AND @numa > 1 AND CEILING(@cpucount*1.00/@numa) >= 8 AND ([value] = 0 OR [value] > 8 OR [value] > CEILING(@cpucount*1.00/@numa)) THEN 'WARNING: MaxDOP setting is not recommended for current NUMA node to processor count (affinity) ratio'\r\n\t\tELSE 'OK'\r\n\tEND AS [Deviation]\r\nFROM sys.configurations (NOLOCK) WHERE name = 'max degree of parallelism';\t\r\n\r\nSELECT 'Processor_checks' AS [Category], 'Parallelism_MaxDOP' AS [Information], \r\n\tCASE \r\n\t\t-- If not NUMA, and up to 8 @affined_cpus then MaxDOP up to 8\r\n\t\tWHEN @numa = 1 AND @affined_cpus <= 8 THEN @affined_cpus\r\n\t\t-- If not NUMA, and more than 8 @affined_cpus then MaxDOP 8 \r\n\t\tWHEN @numa = 1 AND @affined_cpus > 8 THEN 8\r\n\t\t-- If SQL 2016 or higher and has NUMA and # logical CPUs per NUMA up to 15, then MaxDOP is set as # logical CPUs per NUMA, up to 15 \r\n\t\tWHEN @sqlmajorver >= 13 AND @numa > 1 AND CEILING(@cpucount*1.00/@numa) <= 15 THEN CEILING((@cpucount*1.00)/@numa)\r\n\t\t-- If SQL 2016 or higher and has NUMA and # logical CPUs per NUMA > 15, then MaxDOP is set as 1/2 of # logical CPUs per NUMA\r\n\t\tWHEN @sqlmajorver >= 13 AND @numa > 1 AND CEILING(@cpucount*1.00/@numa) > 15 THEN \r\n\t\t\tCASE WHEN CEILING(@cpucount*1.00/@numa/2) > 16 THEN 16 ELSE CEILING(@cpucount*1.00/@numa/2) END\r\n\t\t-- If up to SQL 2016 and has NUMA and # logical CPUs per NUMA up to 8, then MaxDOP is set as # logical CPUs per NUMA \r\n\t\tWHEN @sqlmajorver < 13 AND @numa > 1 AND CEILING(@cpucount*1.00/@numa) < 8 THEN CEILING(@cpucount*1.00/@numa)\r\n\t\t-- If up to SQL 2016 and has NUMA and # logical CPUs per NUMA > 8, then MaxDOP 8\r\n\t\tWHEN @sqlmajorver < 13 AND @numa > 1 AND CEILING(@cpucount*1.00/@numa) >= 8 THEN 8\r\n\t\tELSE 0\r\n\tEND AS [Recommended_MaxDOP],\r\n\t[value] AS [Current_MaxDOP], @cpucount AS [Available_Processors], @affined_cpus AS [Affined_Processors], \r\n\t-- Processor Affinity is shown highest to lowest CPU ID\r\n\t@cpuaffin_fixed AS Affinity_Mask_Bitmask\r\nFROM sys.configurations (NOLOCK) WHERE name = 'max degree of parallelism';",
            "metadata": {},
            "outputs": [],
            "execution_count": 2
        },
        {
            "cell_type": "markdown",
            "source": "Processor Affinity in NUMA architecture subsection",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\n\r\nDECLARE @cpucount int, @numa int, @affined_cpus int, @affinitymask NVARCHAR(64), @affinity64mask NVARCHAR(1024)--, @cpuover32 int\r\nDECLARE @i int, @cpuaffin VARCHAR(300), @cpuaffin_fixed VARCHAR(300)\r\n\r\nSELECT @numa = COUNT(DISTINCT parent_node_id) FROM sys.dm_os_schedulers WHERE scheduler_id < 255 AND parent_node_id < 64;\r\nSELECT @cpucount = COUNT(cpu_id) FROM sys.dm_os_schedulers WHERE scheduler_id < 255 AND parent_node_id < 64\r\n\r\n;WITH bits AS \r\n(SELECT 7 AS N, 128 AS E UNION ALL SELECT 6, 64 UNION ALL \r\nSELECT 5, 32 UNION ALL SELECT 4, 16 UNION ALL SELECT 3, 8 UNION ALL \r\nSELECT 2, 4 UNION ALL SELECT 1, 2 UNION ALL SELECT 0, 1), \r\nbytes AS \r\n(SELECT 1 M UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL \r\nSELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL \r\nSELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9)\r\n-- CPU Affinity is shown highest to lowest CPU ID\r\nSELECT @affinitymask = CASE WHEN [value] = 0 THEN REPLICATE('1', @cpucount)\r\n\tELSE RIGHT((SELECT ((CONVERT(tinyint, SUBSTRING(CONVERT(binary(9), [value]), M, 1)) & E) / E) AS [text()] \r\n\t\tFROM bits CROSS JOIN bytes\r\n\t\tORDER BY M, N DESC\r\n\t\tFOR XML PATH('')), @cpucount) END\r\nFROM sys.configurations (NOLOCK)\r\nWHERE name = 'affinity mask';\r\n\r\nIF @cpucount > 32\r\nBEGIN\r\n\t;WITH bits AS \r\n\t(SELECT 7 AS N, 128 AS E UNION ALL SELECT 6, 64 UNION ALL \r\n\tSELECT 5, 32 UNION ALL SELECT 4, 16 UNION ALL SELECT 3, 8 UNION ALL \r\n\tSELECT 2, 4 UNION ALL SELECT 1, 2 UNION ALL SELECT 0, 1), \r\n\tbytes AS \r\n\t(SELECT 1 M UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL \r\n\tSELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL \r\n\tSELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9)\r\n\t-- CPU Affinity is shown highest to lowest CPU ID\r\n\tSELECT @affinity64mask = CASE WHEN [value] = 0 THEN REPLICATE('1', @cpucount)\r\n\t\tELSE RIGHT((SELECT ((CONVERT(tinyint, SUBSTRING(CONVERT(binary(9), [value]), M, 1)) & E) / E) AS [text()] \r\n\t\t\tFROM bits CROSS JOIN bytes\r\n\t\t\tORDER BY M, N DESC\r\n\t\t\tFOR XML PATH('')), @cpucount) END\r\n\tFROM sys.configurations (NOLOCK)\r\n\tWHERE name = 'affinity64 mask';\r\nEND;\r\n\r\n/*\r\nIF @cpucount > 32\r\nSELECT @cpuover32 = ABS(LEN(@affinity64mask) - (@cpucount-32))\r\n\r\nSELECT @cpuaffin = CASE WHEN @cpucount > 32 THEN REVERSE(LEFT(REVERSE(@affinity64mask),@cpuover32)) + RIGHT(@affinitymask,32) ELSE RIGHT(@affinitymask,@cpucount) END\r\n*/\r\n\r\nSELECT @cpuaffin = CASE WHEN @cpucount > 32 THEN @affinity64mask ELSE @affinitymask END\r\n\r\nSET @cpuaffin_fixed = @cpuaffin\r\nSET @i = CEILING(@cpucount*1.00/@numa) + 1\r\nWHILE @i < @cpucount + @numa\r\nBEGIN\r\n\tIF (@cpucount + @numa) - @i >= CEILING(@cpucount*1.00/@numa)\r\n\tBEGIN\r\n\t\tSELECT @cpuaffin_fixed = STUFF(@cpuaffin_fixed, @i, 1, '_' + SUBSTRING(@cpuaffin_fixed, @i, 1))\r\n\tEND\r\n\tELSE\r\n\tBEGIN\r\n\t\tSELECT @cpuaffin_fixed = STUFF(@cpuaffin_fixed, @i, CEILING(@cpucount*1.00/@numa), SUBSTRING(@cpuaffin_fixed, @i, CEILING(@cpucount*1.00/@numa)))\r\n\tEND\r\n\r\n\tSET @i = @i + CEILING(@cpucount*1.00/@numa) + 1\r\nEND;\r\n\r\nIF @numa > 1\r\nBEGIN\r\n\tWITH ncpuCTE (ncpus) AS (SELECT COUNT(cpu_id) AS ncpus from sys.dm_os_schedulers WHERE is_online = 1 AND scheduler_id < 255 AND parent_node_id < 64 GROUP BY parent_node_id, is_online HAVING COUNT(cpu_id) = 1),\r\n\tcpuCTE (node, afin) AS (SELECT DISTINCT(parent_node_id), is_online FROM sys.dm_os_schedulers WHERE scheduler_id < 255 AND parent_node_id < 64 GROUP BY parent_node_id, is_online)\r\n\tSELECT 'Processor_checks' AS [Category], 'Affinity_NUMA' AS [Check],\r\n\t\tCASE WHEN (SELECT COUNT(*) FROM ncpuCTE) > 0 THEN 'WARNING: Current NUMA configuration is not recommended. At least one node has a single assigned CPU' \r\n\t\t\tWHEN (SELECT COUNT(DISTINCT(node)) FROM cpuCTE WHERE afin = 0 AND node NOT IN (SELECT DISTINCT(node) FROM cpuCTE WHERE afin = 1)) > 0 THEN 'WARNING: Current NUMA configuration is not recommended. At least one node does not have assigned CPUs' \r\n\t\t\tELSE 'OK' END AS [Deviation]\r\n\tFROM sys.dm_os_sys_info (NOLOCK) \r\n\tOPTION (RECOMPILE);\r\n\t\r\n\tSELECT 'Processor_checks' AS [Category], 'Affinity_NUMA' AS [Information], cpu_count AS [Logical_CPU_Count], \r\n\t\t(SELECT COUNT(DISTINCT parent_node_id) FROM sys.dm_os_schedulers WHERE scheduler_id < 255 AND parent_node_id < 64) AS [NUMA_Nodes],\r\n\t\t-- Processor Affinity is shown highest to lowest CPU ID\r\n\t\t@cpuaffin_fixed AS Affinity_Mask_Bitmask\r\n\tFROM sys.dm_os_sys_info (NOLOCK) \r\n\tOPTION (RECOMPILE);\r\nEND\r\nELSE\r\nBEGIN\r\n\tSELECT 'Processor_checks' AS [Category], 'Affinity_NUMA' AS [Check], 'Not NUMA' AS [Deviation]\r\n\tFROM sys.dm_os_sys_info (NOLOCK)\r\n\tOPTION (RECOMPILE);\r\nEND;",
            "metadata": {},
            "outputs": [],
            "execution_count": 1
        },
        {
            "cell_type": "markdown",
            "source": "Additional Processor information subsection",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\n\r\nDECLARE @cpucount int, @numa int, @affined_cpus int, @affinitymask NVARCHAR(64), @affinity64mask NVARCHAR(1024)--, @cpuover32 int\r\nDECLARE @i int, @cpuaffin VARCHAR(300), @cpuaffin_fixed VARCHAR(300), @ostype VARCHAR(10), @SystemManufacturer VARCHAR(128)\r\nDECLARE @sqlcmd NVARCHAR(max), @params NVARCHAR(600)\r\nDECLARE @sqlmajorver int, @sqlminorver int, @sqlbuild int\r\nDECLARE @ErrorMessage NVARCHAR(4000)\r\nDECLARE @machineinfo TABLE ([Value] NVARCHAR(256), [Data] NVARCHAR(256))\r\n\r\nSELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);\r\nSELECT @sqlminorver = CONVERT(int, (@@microsoftversion / 0x10000) & 0xff);\r\nSELECT @sqlbuild = CONVERT(int, @@microsoftversion & 0xffff);\r\n\r\nSELECT @numa = COUNT(DISTINCT parent_node_id) FROM sys.dm_os_schedulers WHERE scheduler_id < 255 AND parent_node_id < 64;\r\nSELECT @cpucount = COUNT(cpu_id) FROM sys.dm_os_schedulers WHERE scheduler_id < 255 AND parent_node_id < 64;\r\n\r\nIF (@sqlmajorver >= 11 AND @sqlmajorver < 14) OR (@sqlmajorver = 10 AND @sqlminorver = 50 AND @sqlbuild >= 2500)\r\nBEGIN\r\n\tSET @ostype = 'Windows'\r\nEND\r\nELSE IF @sqlmajorver >= 14\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT @ostypeOUT = host_platform FROM sys.dm_os_host_info (NOLOCK)';\r\n\tSET @params = N'@ostypeOUT VARCHAR(10) OUTPUT';\r\n\tEXECUTE sp_executesql @sqlcmd, @params, @ostypeOUT=@ostype OUTPUT;\r\nEND\r\n\r\nIF @ostype = 'Windows'\r\nBEGIN\r\n\tINSERT INTO @machineinfo\r\n\tEXEC xp_instance_regread 'HKEY_LOCAL_MACHINE','HARDWARE\\DESCRIPTION\\System\\BIOS','SystemManufacturer';\r\n\tINSERT INTO @machineinfo\r\n\tEXEC xp_instance_regread 'HKEY_LOCAL_MACHINE','HARDWARE\\DESCRIPTION\\System\\BIOS','BIOSVendor';\r\n\tINSERT INTO @machineinfo\r\n\tEXEC xp_instance_regread 'HKEY_LOCAL_MACHINE','HARDWARE\\DESCRIPTION\\System\\CentralProcessor\\0','ProcessorNameString';\r\nEND;\r\n\r\nSELECT @SystemManufacturer = [Data] FROM @machineinfo WHERE [Value] = 'SystemManufacturer';\r\n\r\n;WITH bits AS \r\n(SELECT 7 AS N, 128 AS E UNION ALL SELECT 6, 64 UNION ALL \r\nSELECT 5, 32 UNION ALL SELECT 4, 16 UNION ALL SELECT 3, 8 UNION ALL \r\nSELECT 2, 4 UNION ALL SELECT 1, 2 UNION ALL SELECT 0, 1), \r\nbytes AS \r\n(SELECT 1 M UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL \r\nSELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL \r\nSELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9)\r\n-- CPU Affinity is shown highest to lowest CPU ID\r\nSELECT @affinitymask = CASE WHEN [value] = 0 THEN REPLICATE('1', @cpucount)\r\n\tELSE RIGHT((SELECT ((CONVERT(tinyint, SUBSTRING(CONVERT(binary(9), [value]), M, 1)) & E) / E) AS [text()] \r\n\t\tFROM bits CROSS JOIN bytes\r\n\t\tORDER BY M, N DESC\r\n\t\tFOR XML PATH('')), @cpucount) END\r\nFROM sys.configurations (NOLOCK)\r\nWHERE name = 'affinity mask';\r\n\r\nIF @cpucount > 32\r\nBEGIN\r\n\t;WITH bits AS \r\n\t(SELECT 7 AS N, 128 AS E UNION ALL SELECT 6, 64 UNION ALL \r\n\tSELECT 5, 32 UNION ALL SELECT 4, 16 UNION ALL SELECT 3, 8 UNION ALL \r\n\tSELECT 2, 4 UNION ALL SELECT 1, 2 UNION ALL SELECT 0, 1), \r\n\tbytes AS \r\n\t(SELECT 1 M UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL \r\n\tSELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL \r\n\tSELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9)\r\n\t-- CPU Affinity is shown highest to lowest CPU ID\r\n\tSELECT @affinity64mask = CASE WHEN [value] = 0 THEN REPLICATE('1', @cpucount)\r\n\t\tELSE RIGHT((SELECT ((CONVERT(tinyint, SUBSTRING(CONVERT(binary(9), [value]), M, 1)) & E) / E) AS [text()] \r\n\t\t\tFROM bits CROSS JOIN bytes\r\n\t\t\tORDER BY M, N DESC\r\n\t\t\tFOR XML PATH('')), @cpucount) END\r\n\tFROM sys.configurations (NOLOCK)\r\n\tWHERE name = 'affinity64 mask';\r\nEND;\r\n\r\n/*\r\nIF @cpucount > 32\r\nSELECT @cpuover32 = ABS(LEN(@affinity64mask) - (@cpucount-32))\r\n\r\nSELECT @cpuaffin = CASE WHEN @cpucount > 32 THEN REVERSE(LEFT(REVERSE(@affinity64mask),@cpuover32)) + RIGHT(@affinitymask,32) ELSE RIGHT(@affinitymask,@cpucount) END\r\n*/\r\n\r\nSELECT @cpuaffin = CASE WHEN @cpucount > 32 THEN @affinity64mask ELSE @affinitymask END\r\nSELECT @affined_cpus = COUNT(cpu_id) FROM sys.dm_os_schedulers WHERE is_online = 1 AND scheduler_id < 255 AND parent_node_id < 64;\r\n\r\nSET @cpuaffin_fixed = @cpuaffin\r\nSET @i = CEILING(@cpucount*1.00/@numa) + 1\r\nWHILE @i < @cpucount + @numa\r\nBEGIN\r\n\tIF (@cpucount + @numa) - @i >= CEILING(@cpucount*1.00/@numa)\r\n\tBEGIN\r\n\t\tSELECT @cpuaffin_fixed = STUFF(@cpuaffin_fixed, @i, 1, '_' + SUBSTRING(@cpuaffin_fixed, @i, 1))\r\n\tEND\r\n\tELSE\r\n\tBEGIN\r\n\t\tSELECT @cpuaffin_fixed = STUFF(@cpuaffin_fixed, @i, CEILING(@cpucount*1.00/@numa), SUBSTRING(@cpuaffin_fixed, @i, CEILING(@cpucount*1.00/@numa)))\r\n\tEND\r\n\r\n\tSET @i = @i + CEILING(@cpucount*1.00/@numa) + 1\r\nEND;\r\n\r\nSELECT 'Processor_checks' AS [Category], 'Processor_Summary' AS [Information], cpu_count AS [Logical_CPU_Count], hyperthread_ratio AS [Cores2Socket_Ratio],\r\n\tcpu_count/hyperthread_ratio AS [CPU_Sockets], \r\n\tCASE WHEN @numa > 1 THEN (SELECT COUNT(DISTINCT parent_node_id) FROM sys.dm_os_schedulers WHERE scheduler_id < 255 AND parent_node_id < 64) ELSE 0 END AS [NUMA_Nodes],\r\n\t@affined_cpus AS [Affined_Processors], \r\n\t-- Processor Affinity is shown highest to lowest Processor ID\r\n\t@cpuaffin_fixed AS Affinity_Mask_Bitmask\r\nFROM sys.dm_os_sys_info (NOLOCK)\r\nOPTION (RECOMPILE);",
            "metadata": {},
            "outputs": [],
            "execution_count": 3
        },
        {
            "cell_type": "markdown",
            "source": "HP logical processor issue check subsection\r\n\r\n**Note:** See more information in https://support.hpe.com/hpsc/doc/public/display?docId=emr_na-c04650594",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\n\r\nDECLARE @ostype VARCHAR(10), @SystemManufacturer VARCHAR(128), @BIOSVendor AS VARCHAR(128), @Processor_Name AS VARCHAR(128)\r\nDECLARE @ErrorMessage NVARCHAR(4000)\r\nDECLARE @sqlcmd NVARCHAR(max), @params NVARCHAR(600)\r\nDECLARE @sqlmajorver int\r\nDECLARE @machineinfo TABLE ([Value] NVARCHAR(256), [Data] NVARCHAR(256))\r\n\r\nSELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);\r\n\r\nIF @sqlmajorver < 14\r\nBEGIN\r\n\tSET @ostype = 'Windows';\r\nEND\r\nELSE\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT @ostypeOUT = host_platform FROM sys.dm_os_host_info (NOLOCK)';\r\n\tSET @params = N'@ostypeOUT VARCHAR(10) OUTPUT';\r\n\tEXECUTE sp_executesql @sqlcmd, @params, @ostypeOUT=@ostype OUTPUT;\r\nEND\r\n\r\nIF @ostype = 'Windows'\r\nBEGIN\r\n\tINSERT INTO @machineinfo\r\n\tEXEC xp_instance_regread 'HKEY_LOCAL_MACHINE','HARDWARE\\DESCRIPTION\\System\\BIOS','SystemManufacturer';\r\n\tINSERT INTO @machineinfo\r\n\tEXEC xp_instance_regread 'HKEY_LOCAL_MACHINE','HARDWARE\\DESCRIPTION\\System\\BIOS','BIOSVendor';\r\n\tINSERT INTO @machineinfo\r\n\tEXEC xp_instance_regread 'HKEY_LOCAL_MACHINE','HARDWARE\\DESCRIPTION\\System\\CentralProcessor\\0','ProcessorNameString';\r\nEND\r\n\r\nSELECT @SystemManufacturer = [Data] FROM @machineinfo WHERE [Value] = 'SystemManufacturer';\r\n\r\n-- Check for HP Logical Processor issue (https://support.hpe.com/hpsc/doc/public/display?docId=emr_na-c04650594)\r\nIF LOWER(@SystemManufacturer) <> 'microsoft' AND LOWER(@SystemManufacturer) <> 'vmware' AND LOWER(@ostype) = 'windows'\r\nBEGIN\r\n\tSELECT @BIOSVendor = [Data] FROM @machineinfo WHERE [Value] = 'BIOSVendor'\r\n\tSELECT @Processor_Name = [Data] FROM @machineinfo WHERE [Value] = 'ProcessorNameString'\r\n\tIF LOWER(@BIOSVendor) = 'hp' AND LOWER(@Processor_Name) LIKE '%xeon%e5%' --and\r\n\tBEGIN\r\n\t\tSELECT 'Processor_checks' AS [Category], 'HP_Logical_Processor_Issue' AS [Information], 'WARNING: You may be affected by HP Logical Processor issue outlined in https://support.hpe.com/hpsc/doc/public/display?docId=emr_na-c04650594' AS [Deviation]\r\n\tEND\r\n    ELSE\r\n    BEGIN\r\n        SELECT 'Processor_checks' AS [Category], 'HP_Logical_Processor_Issue' AS [Check], 'INFORMATION: Not an affected HP Machine' AS [Deviation];\r\n    END;\r\nEND\r\nELSE\r\nBEGIN\r\n\tSELECT 'Processor_checks' AS [Category], 'HP_Logical_Processor_Issue' AS [Check], 'Not a Physical Machine' AS [Deviation];\r\nEND;",
            "metadata": {},
            "outputs": [],
            "execution_count": 11
        },
        {
            "cell_type": "markdown",
            "source": "Processor utilization rate in the last 2 hours subsection",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\nSET DATEFORMAT mdy;\r\n\r\nDECLARE @ts_now bigint\r\nDECLARE @tblAggCPU TABLE (SQLProc tinyint, SysIdle tinyint, OtherProc tinyint, Minutes tinyint)\r\nSELECT @ts_now = ms_ticks FROM sys.dm_os_sys_info (NOLOCK);\r\n\r\nWITH cteCPU (record_id, SystemIdle, SQLProcessUtilization, [timestamp]) AS (SELECT \r\n        record.value('(./Record/@id)[1]', 'int') AS record_id,\r\n        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle,\r\n        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization,\r\n        [TIMESTAMP] FROM (SELECT [TIMESTAMP], CONVERT(xml, record) AS record \r\n            FROM sys.dm_os_ring_buffers (NOLOCK)\r\n            WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'\r\n            AND record LIKE '%<SystemHealth>%') AS x\r\n    )\r\nINSERT INTO @tblAggCPU\r\n    SELECT AVG(SQLProcessUtilization), AVG(SystemIdle), CASE WHEN AVG(SystemIdle) + AVG(SQLProcessUtilization) < 100 THEN 100 - AVG(SystemIdle) - AVG(SQLProcessUtilization) ELSE 0 END, 10 \r\n    FROM cteCPU \r\n    WHERE DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) > DATEADD(mi, -10, GETDATE())\r\nUNION ALL \r\n    SELECT AVG(SQLProcessUtilization), AVG(SystemIdle), CASE WHEN AVG(SystemIdle) + AVG(SQLProcessUtilization) < 100 THEN 100 - AVG(SystemIdle) - AVG(SQLProcessUtilization) ELSE 0 END, 20\r\n    FROM cteCPU \r\n    WHERE DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) <= DATEADD(mi, -10, GETDATE()) AND \r\n        DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) > DATEADD(mi, -20, GETDATE())\r\nUNION ALL \r\n    SELECT AVG(SQLProcessUtilization), AVG(SystemIdle), CASE WHEN AVG(SystemIdle) + AVG(SQLProcessUtilization) < 100 THEN 100 - AVG(SystemIdle) - AVG(SQLProcessUtilization) ELSE 0 END, 30\r\n    FROM cteCPU \r\n    WHERE DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) <= DATEADD(mi, -20, GETDATE()) AND \r\n        DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) > DATEADD(mi, -30, GETDATE())\r\nUNION ALL \r\n    SELECT AVG(SQLProcessUtilization), AVG(SystemIdle), CASE WHEN AVG(SystemIdle) + AVG(SQLProcessUtilization) < 100 THEN 100 - AVG(SystemIdle) - AVG(SQLProcessUtilization) ELSE 0 END, 40\r\n    FROM cteCPU \r\n    WHERE DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) <= DATEADD(mi, -30, GETDATE()) AND \r\n        DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) > DATEADD(mi, -40, GETDATE())\r\nUNION ALL \r\n    SELECT AVG(SQLProcessUtilization), AVG(SystemIdle), CASE WHEN AVG(SystemIdle) + AVG(SQLProcessUtilization) < 100 THEN 100 - AVG(SystemIdle) - AVG(SQLProcessUtilization) ELSE 0 END, 50\r\n    FROM cteCPU \r\n    WHERE DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) <= DATEADD(mi, -40, GETDATE()) AND \r\n        DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) > DATEADD(mi, -50, GETDATE())\r\nUNION ALL \r\n    SELECT AVG(SQLProcessUtilization), AVG(SystemIdle), CASE WHEN AVG(SystemIdle) + AVG(SQLProcessUtilization) < 100 THEN 100 - AVG(SystemIdle) - AVG(SQLProcessUtilization) ELSE 0 END, 60\r\n    FROM cteCPU \r\n    WHERE DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) <= DATEADD(mi, -50, GETDATE()) AND \r\n        DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) > DATEADD(mi, -60, GETDATE())\r\nUNION ALL \r\n    SELECT AVG(SQLProcessUtilization), AVG(SystemIdle), CASE WHEN AVG(SystemIdle) + AVG(SQLProcessUtilization) < 100 THEN 100 - AVG(SystemIdle) - AVG(SQLProcessUtilization) ELSE 0 END, 70\r\n    FROM cteCPU \r\n    WHERE DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) <= DATEADD(mi, -60, GETDATE()) AND \r\n        DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) > DATEADD(mi, -70, GETDATE())\r\nUNION ALL \r\n    SELECT AVG(SQLProcessUtilization), AVG(SystemIdle), CASE WHEN AVG(SystemIdle) + AVG(SQLProcessUtilization) < 100 THEN 100 - AVG(SystemIdle) - AVG(SQLProcessUtilization) ELSE 0 END, 80\r\n    FROM cteCPU \r\n    WHERE DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) <= DATEADD(mi, -70, GETDATE()) AND \r\n        DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) > DATEADD(mi, -80, GETDATE())\r\nUNION ALL \r\n    SELECT AVG(SQLProcessUtilization), AVG(SystemIdle), CASE WHEN AVG(SystemIdle) + AVG(SQLProcessUtilization) < 100 THEN 100 - AVG(SystemIdle) - AVG(SQLProcessUtilization) ELSE 0 END, 90\r\n    FROM cteCPU \r\n    WHERE DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) <= DATEADD(mi, -80, GETDATE()) AND \r\n        DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) > DATEADD(mi, -90, GETDATE())\r\nUNION ALL \r\n    SELECT AVG(SQLProcessUtilization), AVG(SystemIdle), CASE WHEN AVG(SystemIdle) + AVG(SQLProcessUtilization) < 100 THEN 100 - AVG(SystemIdle) - AVG(SQLProcessUtilization) ELSE 0 END, 100\r\n    FROM cteCPU \r\n    WHERE DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) <= DATEADD(mi, -90, GETDATE()) AND \r\n        DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) > DATEADD(mi, -100, GETDATE())\r\nUNION ALL \r\n    SELECT AVG(SQLProcessUtilization), AVG(SystemIdle), CASE WHEN AVG(SystemIdle) + AVG(SQLProcessUtilization) < 100 THEN 100 - AVG(SystemIdle) - AVG(SQLProcessUtilization) ELSE 0 END, 110\r\n    FROM cteCPU \r\n    WHERE DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) <= DATEADD(mi, -100, GETDATE()) AND \r\n        DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) > DATEADD(mi, -110, GETDATE())\r\nUNION ALL \r\n    SELECT AVG(SQLProcessUtilization), AVG(SystemIdle), CASE WHEN AVG(SystemIdle) + AVG(SQLProcessUtilization) < 100 THEN 100 - AVG(SystemIdle) - AVG(SQLProcessUtilization) ELSE 0 END, 120\r\n    FROM cteCPU \r\n    WHERE DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) <= DATEADD(mi, -110, GETDATE()) AND \r\n        DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) > DATEADD(mi, -120, GETDATE())\r\n\r\nIF (SELECT COUNT(SysIdle) FROM @tblAggCPU WHERE SysIdle < 30) > 0\r\nBEGIN\r\n    SELECT 'Processor_checks' AS [Category], 'Processor_Usage_last_2h' AS [Check], 'WARNING: Detected CPU usage over 70 pct' AS [Deviation];\r\nEND\r\nELSE IF (SELECT COUNT(SysIdle) FROM @tblAggCPU WHERE SysIdle < 10) > 0\r\nBEGIN\r\n    SELECT 'Processor_checks' AS [Category], 'Processor_Usage_last_2h' AS [Check], 'WARNING: Detected CPU usage over 90 pct' AS [Deviation];\r\nEND\r\nELSE\r\nBEGIN\r\n    SELECT 'Processor_checks' AS [Category], 'Processor_Usage_last_2h' AS [Check], 'OK' AS [Deviation];\r\nEND;\r\n\r\nSELECT 'Processor_checks' AS [Category], 'Agg_Processor_Usage_last_2h' AS [Information], SQLProc AS [SQL_Process_Utilization], SysIdle AS [System_Idle], OtherProc AS [Other_Process_Utilization], Minutes AS [Time_Slice_Last_x_min]\r\nFROM @tblAggCPU;",
            "metadata": {},
            "outputs": [],
            "execution_count": 1
        },
        {
            "cell_type": "markdown",
            "source": "Server Memory subsection",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\nSET DATEFORMAT mdy;\r\n\r\nDECLARE @sqlcmd NVARCHAR(max), @params NVARCHAR(600)\r\nDECLARE @sqlmajorver int, @sqlminorver int, @sqlbuild int\r\nDECLARE @ErrorMessage NVARCHAR(4000)\r\nDECLARE @masterpid int\r\nDECLARE @permstbl TABLE ([name] sysname);\r\nDECLARE @maxservermem bigint, @minservermem bigint, @systemmem bigint, @systemfreemem bigint, @numa int, @numa_nodes_afinned tinyint\r\nDECLARE @commit_target bigint -- Includes stolen and reserved memory in the memory manager\r\nDECLARE @committed bigint -- Does not include reserved memory in the memory manager\r\nDECLARE @mwthreads_count int, @xtp int, @clustered bit, @arch smallint, @osver VARCHAR(5), @ostype VARCHAR(10), @SystemManufacturer VARCHAR(128)\r\n\r\nSELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);\r\nSELECT @masterpid = principal_id FROM master.sys.database_principals (NOLOCK) WHERE sid = SUSER_SID()\r\n\r\nINSERT INTO @permstbl\r\nSELECT a.name\r\nFROM master.sys.all_objects a (NOLOCK) INNER JOIN master.sys.database_permissions b (NOLOCK) ON a.[OBJECT_ID] = b.major_id\r\nWHERE a.type IN ('P', 'X') AND b.grantee_principal_id <>0\r\nAND b.grantee_principal_id <>2\r\nAND b.grantee_principal_id = @masterpid;\r\n\r\nIF @sqlmajorver >= 14\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT @ostypeOUT = host_platform, @archOUT = CASE WHEN @@VERSION LIKE ''%<X64>%'' THEN 64 WHEN @@VERSION LIKE ''%<IA64>%'' THEN 128 ELSE 32 END FROM sys.dm_os_host_info (NOLOCK)';\r\n\tSET @params = N'@ostypeOUT VARCHAR(10) OUTPUT, @archOUT smallint OUTPUT';\r\n\tEXECUTE sp_executesql @sqlcmd, @params, @ostypeOUT=@ostype OUTPUT, @archOUT=@arch OUTPUT;\r\nEND\r\nELSE\r\nBEGIN\r\n    DECLARE @sysinfo TABLE (id int, \r\n        [Name] NVARCHAR(256), \r\n        Internal_Value bigint, \r\n        Character_Value NVARCHAR(256));\r\n        \r\n    INSERT INTO @sysinfo\r\n    EXEC xp_msver;\r\n\r\n    SELECT @arch = CASE WHEN RTRIM(Character_Value) LIKE '%x64%' OR RTRIM(Character_Value) LIKE '%AMD64%' THEN 64\r\n        WHEN RTRIM(Character_Value) LIKE '%x86%' OR RTRIM(Character_Value) LIKE '%32%' THEN 32\r\n        WHEN RTRIM(Character_Value) LIKE '%IA64%' THEN 128 END\r\n    FROM @sysinfo\r\n    WHERE [Name] LIKE 'Platform%';\r\n\r\n\tSET @ostype = 'Windows'\r\nEND;\r\n\r\nIF @sqlmajorver = 9\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT @systemmemOUT = t1.record.value(''(./Record/MemoryRecord/TotalPhysicalMemory)[1]'', ''bigint'')/1024, \r\n\t@systemfreememOUT = t1.record.value(''(./Record/MemoryRecord/AvailablePhysicalMemory)[1]'', ''bigint'')/1024\r\nFROM (SELECT MAX([TIMESTAMP]) AS [TIMESTAMP], CONVERT(xml, record) AS record \r\n\tFROM sys.dm_os_ring_buffers (NOLOCK)\r\n\tWHERE ring_buffer_type = N''RING_BUFFER_RESOURCE_MONITOR''\r\n\t\tAND record LIKE ''%RESOURCE_MEMPHYSICAL%''\r\n\tGROUP BY record) AS t1';\r\nEND\r\nELSE\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT @systemmemOUT = total_physical_memory_kb/1024, @systemfreememOUT = available_physical_memory_kb/1024 FROM sys.dm_os_sys_memory';\r\nEND\r\n\r\nSET @params = N'@systemmemOUT bigint OUTPUT, @systemfreememOUT bigint OUTPUT';\r\n\r\nEXECUTE sp_executesql @sqlcmd, @params, @systemmemOUT=@systemmem OUTPUT, @systemfreememOUT=@systemfreemem OUTPUT;\r\n\r\nIF @sqlmajorver >= 9 AND @sqlmajorver < 11\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT @commit_targetOUT=bpool_commit_target*8, @committedOUT=bpool_committed*8 FROM sys.dm_os_sys_info (NOLOCK)'\r\nEND\r\nELSE IF @sqlmajorver >= 11\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT @commit_targetOUT=committed_target_kb, @committedOUT=committed_kb FROM sys.dm_os_sys_info (NOLOCK)'\r\nEND\r\n\r\nSET @params = N'@commit_targetOUT bigint OUTPUT, @committedOUT bigint OUTPUT';\r\n\r\nEXECUTE sp_executesql @sqlcmd, @params, @commit_targetOUT=@commit_target OUTPUT, @committedOUT=@committed OUTPUT;\r\n\r\nSELECT @minservermem = CONVERT(int, [value]) FROM sys.configurations (NOLOCK) WHERE [Name] = 'min server memory (MB)';\r\nSELECT @maxservermem = CONVERT(int, [value]) FROM sys.configurations (NOLOCK) WHERE [Name] = 'max server memory (MB)';\r\nSELECT @mwthreads_count = max_workers_count FROM sys.dm_os_sys_info;\r\nSELECT @numa_nodes_afinned = COUNT (DISTINCT parent_node_id) FROM sys.dm_os_schedulers WHERE scheduler_id < 255 AND parent_node_id < 64 AND is_online = 1;\r\nSELECT @numa = COUNT(DISTINCT parent_node_id) FROM sys.dm_os_schedulers WHERE scheduler_id < 255 AND parent_node_id < 64;\r\nSELECT @clustered = CONVERT(bit,ISNULL(SERVERPROPERTY('IsClustered'),0));\r\n\r\nDECLARE @machineinfo TABLE ([Value] NVARCHAR(256), [Data] NVARCHAR(256))\r\n\r\nIF @ostype = 'Windows'\r\nBEGIN\r\n\tINSERT INTO @machineinfo\r\n\tEXEC xp_instance_regread 'HKEY_LOCAL_MACHINE','HARDWARE\\DESCRIPTION\\System\\BIOS','SystemManufacturer';\r\nEND;\r\n\r\nSELECT @SystemManufacturer = [Data] FROM @machineinfo WHERE [Value] = 'SystemManufacturer';\r\n\r\nSELECT 'Memory_checks' AS [Category], 'Memory_issues_MaxServerMem' AS [Check],\r\n\tCASE WHEN @maxservermem = 2147483647 THEN 'WARNING: MaxMem setting is default. Please revise memory settings'\r\n\t\tWHEN @maxservermem > @systemmem THEN 'WARNING: MaxMem setting exceeds available system memory'\r\n\t\tWHEN SERVERPROPERTY('EditionID') IN (284895786, 1293598313) AND @maxservermem > 67108864 THEN 'WARNING: MaxMem setting exceeds Web and Business Intelligence Edition limits'\r\n\t\tWHEN SERVERPROPERTY('EditionID') = -1534726760 AND @maxservermem > 134217728 THEN 'WARNING: MaxMem setting exceeds Standard Edition limits'\r\n\t\tWHEN SERVERPROPERTY('EngineEdition') = 4 AND @maxservermem > 1443840 THEN 'WARNING: MaxMem setting exceeds Express Edition limits'\r\n\t\tWHEN @numa > 1 AND (@maxservermem/@numa) * @numa_nodes_afinned > (@systemmem/@numa) * @numa_nodes_afinned THEN 'WARNING: Current MaxMem setting will leverage node foreign memory. Maximum value for MaxMem setting on this configuration is ' + CONVERT(NVARCHAR,(@systemmem/@numa) * @numa_nodes_afinned) + ' for a single instance'\r\n\t\tELSE 'OK'\r\n\tEND AS [Deviation], @maxservermem AS [sql_max_mem_MB];\r\n\r\nSELECT 'Memory_checks' AS [Category], 'Memory_issues_MinServerMem' AS [Check],\r\n\tCASE WHEN @minservermem = 0 AND (LOWER(@SystemManufacturer) = 'microsoft' OR LOWER(@SystemManufacturer) = 'vmware') THEN 'WARNING: Min Server Mem setting is not set in a VM, allowing memory pressure on the Host to attempt to deallocate memory on a guest SQL Server'\r\n\t\tWHEN @minservermem = 0 AND @clustered = 1 THEN 'INFORMATION: Min Server Mem setting is default in a clustered instance. Leverage Min Server Mem for the purpose of limiting memory concurrency between instances'\r\n\t\tWHEN @minservermem = @maxservermem THEN 'WARNING: Min Server Mem setting is equal to Max Server Mem. This will not allow dynamic memory. Please revise memory settings'\r\n\t\tWHEN @numa > 1 AND (@minservermem/@numa) * @numa_nodes_afinned > (@systemmem/@numa) * @numa_nodes_afinned THEN 'WARNING: Current MinMem setting will leverage node foreign memory'\r\n\t\tELSE 'OK'\r\n\tEND AS [Deviation], @minservermem AS [sql_min_mem_MB];\r\n\r\nSELECT 'Memory_checks' AS [Category], 'Memory_issues_FreeMem' AS [Check],\r\n\tCASE WHEN (@systemfreemem*100)/@systemmem <= 5 THEN 'WARNING: Less than 5 percent of Free Memory available. Please revise memory settings'\r\n\t\t/* 64 is the default LowMemThreshold for windows on a system with 8GB of mem or more*/\r\n\t\tWHEN @systemfreemem <= 64*3 THEN 'WARNING: System Free Memory is dangerously low. Please revise memory settings'\r\n\t\tELSE '[OK]'\r\n\tEND AS [Deviation], @systemmem AS system_total_physical_memory_MB, @systemfreemem AS system_available_physical_memory_MB;\r\n\r\nSELECT 'Memory_checks' AS [Category], 'Memory_issues_CommitedMem' AS [Check],\r\n\tCASE WHEN @commit_target > @committed AND @sqlmajorver >= 11 THEN 'INFORMATION: Memory manager will try to obtain additional memory'\r\n\t\tWHEN @commit_target < @committed AND @sqlmajorver >= 11  THEN 'INFORMATION: Memory manager will try to shrink the amount of memory committed'\r\n\t\tWHEN @commit_target > @committed AND @sqlmajorver < 11 THEN 'INFORMATION: Buffer Pool will try to obtain additional memory'\r\n\t\tWHEN @commit_target < @committed AND @sqlmajorver < 11  THEN 'INFORMATION: Buffer Pool will try to shrink'\r\n\t\tELSE 'OK'\r\n\tEND AS [Deviation], @commit_target/1024 AS sql_commit_target_MB, @committed/1024 AS sql_commited_MB;\r\n\r\nSELECT 'Memory_checks' AS [Category], 'Memory_reference' AS [Check],\r\n\tCASE WHEN @arch IS NULL THEN '[WARNING: Could not determine architecture needed for check]'\r\n\t\tWHEN (@systemmem <= 2048 AND @maxservermem > @systemmem-512-(@mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END))- CASE WHEN @arch = 32 THEN 256 ELSE 0 END) OR\r\n\t\t(@systemmem BETWEEN 2049 AND 4096 AND @maxservermem > @systemmem-819-(@mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END))- CASE WHEN @arch = 32 THEN 256 ELSE 0 END) OR\r\n\t\t(@systemmem BETWEEN 4097 AND 8192 AND @maxservermem > @systemmem-1228-(@mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END))- CASE WHEN @arch = 32 THEN 256 ELSE 0 END) OR\r\n\t\t(@systemmem BETWEEN 8193 AND 12288 AND @maxservermem > @systemmem-2048-(@mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END))- CASE WHEN @arch = 32 THEN 256 ELSE 0 END) OR\r\n\t\t(@systemmem BETWEEN 12289 AND 24576 AND @maxservermem > @systemmem-2560-(@mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END))- CASE WHEN @arch = 32 THEN 256 ELSE 0 END) OR\r\n\t\t(@systemmem BETWEEN 24577 AND 32768 AND @maxservermem > @systemmem-3072-(@mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END))- CASE WHEN @arch = 32 THEN 256 ELSE 0 END) OR\r\n\t\t(@systemmem > 32768 AND SERVERPROPERTY('EditionID') IN (284895786, 1293598313) AND @maxservermem > CAST(0.5 * (((@systemmem-4096-(@mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END)- CASE WHEN @arch = 32 THEN 256 ELSE 0 END)) + 65536) - ABS((@systemmem-4096-(@mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END)- CASE WHEN @arch = 32 THEN 256 ELSE 0 END)) - 65536)) AS int)) OR -- Find min of max mem for machine or max mem for Web and Business Intelligence SKU\r\n\t\t(@systemmem > 32768 AND SERVERPROPERTY('EditionID') = -1534726760 AND @maxservermem > CAST(0.5 * (((@systemmem-4096-(@mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END)- CASE WHEN @arch = 32 THEN 256 ELSE 0 END)) + 131072) - ABS((@systemmem-4096-(@mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END)- CASE WHEN @arch = 32 THEN 256 ELSE 0 END)) - 131072)) AS int)) OR -- Find min of max mem for machine or max mem for Standard SKU\r\n\t\t(@systemmem > 32768 AND SERVERPROPERTY('EngineEdition') IN (3,8) AND @maxservermem > @systemmem-4096-(@mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END)- CASE WHEN @arch = 32 THEN 256 ELSE 0 END)) THEN 'WARNING: Not at the recommended MaxMem setting for this server memory configuration, with a single instance' -- Enterprise Edition or Managed Instance\r\n\t\tELSE 'OK'\r\n\tEND AS [Deviation],\t\t\r\n\tCASE WHEN @systemmem <= 2048 THEN @systemmem-512-(@mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END)- CASE WHEN @arch = 32 THEN 256 ELSE 0 END)\r\n\t\tWHEN @systemmem BETWEEN 2049 AND 4096 THEN @systemmem-819-(@mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END)- CASE WHEN @arch = 32 THEN 256 ELSE 0 END)\r\n\t\tWHEN @systemmem BETWEEN 4097 AND 8192 THEN @systemmem-1228-(@mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END)- CASE WHEN @arch = 32 THEN 256 ELSE 0 END)\r\n\t\tWHEN @systemmem BETWEEN 8193 AND 12288 THEN @systemmem-2048-(@mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END)- CASE WHEN @arch = 32 THEN 256 ELSE 0 END)\r\n\t\tWHEN @systemmem BETWEEN 12289 AND 24576 THEN @systemmem-2560-(@mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END)- CASE WHEN @arch = 32 THEN 256 ELSE 0 END)\r\n\t\tWHEN @systemmem BETWEEN 24577 AND 32768 THEN @systemmem-3072-(@mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END)- CASE WHEN @arch = 32 THEN 256 ELSE 0 END)\r\n\t\tWHEN @systemmem > 32768 AND SERVERPROPERTY('EditionID') IN (284895786, 1293598313) THEN CAST(0.5 * (((@systemmem-4096-(@mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END)- CASE WHEN @arch = 32 THEN 256 ELSE 0 END)) + 65536) - ABS((@systemmem-4096-(@mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END)- CASE WHEN @arch = 32 THEN 256 ELSE 0 END)) - 65536)) AS int) -- Find min of max mem for machine or max mem for Web and Business Intelligence SKU\r\n\t\tWHEN @systemmem > 32768 AND SERVERPROPERTY('EditionID') = -1534726760 THEN CAST(0.5 * (((@systemmem-4096-(@mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END)- CASE WHEN @arch = 32 THEN 256 ELSE 0 END)) + 131072) - ABS((@systemmem-4096-(@mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END)- CASE WHEN @arch = 32 THEN 256 ELSE 0 END)) - 131072)) AS int) -- Find min of max mem for machine or max mem for Standard SKU\r\n\t\tWHEN @systemmem > 32768 AND SERVERPROPERTY('EngineEdition') IN (3,8) THEN @systemmem-4096-(@mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END)- CASE WHEN @arch = 32 THEN 256 ELSE 0 END) -- Enterprise Edition or Managed Instance\r\n\tEND AS [Recommended_MaxMem_MB_SingleInstance],\r\n\tCASE WHEN @systemmem <= 2048 THEN 512\r\n\t\tWHEN @systemmem BETWEEN 2049 AND 4096 THEN 819\r\n\t\tWHEN @systemmem BETWEEN 4097 AND 8192 THEN 1228\r\n\t\tWHEN @systemmem BETWEEN 8193 AND 12288 THEN 2048\r\n\t\tWHEN @systemmem BETWEEN 12289 AND 24576 THEN 2560\r\n\t\tWHEN @systemmem BETWEEN 24577 AND 32768 THEN 3072\r\n\t\tWHEN @systemmem > 32768 THEN 4096\r\n\tEND AS [Mem_MB_for_OS],\r\n\tCASE WHEN @systemmem <= 2048 THEN @mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END)\r\n\t\tWHEN @systemmem BETWEEN 2049 AND 4096 THEN @mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END)\r\n\t\tWHEN @systemmem BETWEEN 4097 AND 8192 THEN @mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END)\r\n\t\tWHEN @systemmem BETWEEN 8193 AND 12288 THEN @mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END)\r\n\t\tWHEN @systemmem BETWEEN 12289 AND 24576 THEN @mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END)\r\n\t\tWHEN @systemmem BETWEEN 24577 AND 32768 THEN @mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END)\r\n\t\tWHEN @systemmem > 32768 THEN @mwthreads_count*(CASE WHEN @arch = 64 THEN 2 WHEN @arch = 128 THEN 4 WHEN @arch = 32 THEN 0.5 END)\r\n\tEND AS [Potential_threads_mem_MB],\r\n\t@mwthreads_count AS [Configured_workers];\r\n\r\nIF @sqlmajorver = 9\r\nBEGIN\r\n\tSELECT 'Memory_checks' AS [Category], 'Memory_Summary' AS [Information], \r\n\t\t@maxservermem AS sql_max_mem_MB, @minservermem AS sql_min_mem_MB,\r\n\t\t@commit_target/1024 AS sql_commit_target_MB, --BPool in SQL 2005 to 2008R2\r\n\t\t@committed/1024 AS sql_commited_MB, --BPool in SQL 2005 to 2008R2\r\n\t\t@systemmem AS system_total_physical_memory_MB, \r\n\t\t@systemfreemem AS system_available_physical_memory_MB\r\nEND\r\nELSE\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT ''Memory_checks'' AS [Category], ''Memory_Summary'' AS [Information], \r\n\t@maxservermemIN AS sql_max_mem_MB, @minservermemIN AS sql_min_mem_MB, \r\n\t@commit_targetIN/1024 AS sql_commit_target_MB, --BPool in SQL 2005 to 2008R2\r\n\t@committedIN/1024 AS sql_commited_MB, --BPool in SQL 2005 to 2008R2\r\n\tphysical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB, \r\n\tlarge_page_allocations_kb/1024 AS sql_large_page_allocations_MB, \r\n\tlocked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,\t\r\n\t@systemmemIN AS system_total_physical_memory_MB, \r\n\t@systemfreememIN AS system_available_physical_memory_MB, \r\n\ttotal_virtual_address_space_kb/1024 AS sql_total_VAS_MB, \r\n\tvirtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB, \r\n\tvirtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB, \r\n\tvirtual_address_space_available_kb/1024 AS sql_VAS_available_MB,\r\n\tpage_fault_count AS sql_page_fault_count,\r\n\tmemory_utilization_percentage AS sql_memory_utilization_percentage, \r\n\tprocess_physical_memory_low AS sql_process_physical_memory_low, \r\n\tprocess_virtual_memory_low AS sql_process_virtual_memory_low\t\r\nFROM sys.dm_os_process_memory (NOLOCK)'\r\n\tSET @params = N'@maxservermemIN bigint, @minservermemIN bigint, @systemmemIN bigint, @systemfreememIN bigint, @commit_targetIN bigint, @committedIN bigint';\r\n\tEXECUTE sp_executesql @sqlcmd, @params, @maxservermemIN=@maxservermem, @minservermemIN=@minservermem,@systemmemIN=@systemmem, @systemfreememIN=@systemfreemem, @commit_targetIN=@commit_target, @committedIN=@committed\r\nEND;\r\n\r\nIF @numa > 1 AND @sqlmajorver > 10\r\nBEGIN\r\n\tEXEC ('SELECT ''Memory_checks'' AS [Category], ''NUMA_Memory_Distribution'' AS [Information], memory_node_id, virtual_address_space_reserved_kb, virtual_address_space_committed_kb, locked_page_allocations_kb, pages_kb, foreign_committed_kb, shared_memory_reserved_kb, shared_memory_committed_kb, processor_group FROM sys.dm_os_memory_nodes;')\r\nEND\r\nELSE IF @numa > 1 AND @sqlmajorver = 10\r\nBEGIN\r\n\tEXEC ('SELECT ''Memory_checks'' AS [Category], ''NUMA_Memory_Distribution'' AS [Information], memory_node_id, virtual_address_space_reserved_kb, virtual_address_space_committed_kb, locked_page_allocations_kb, single_pages_kb, multi_pages_kb, shared_memory_reserved_kb, shared_memory_committed_kb, processor_group FROM sys.dm_os_memory_nodes;')\r\nEND;\r\n",
            "metadata": {},
            "outputs": [],
            "execution_count": 4
        },
        {
            "cell_type": "markdown",
            "source": "Memory Resource Monitor Tresholds subsection",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "/* \r\nFrom Windows Internals book by David Solomon and Mark Russinovich:\r\n\"The default level of available memory that signals a low-memory-resource notification event is approximately 32 MB per 4 GB, \r\nto a maximum of 64 MB. The default level that signals a high-memory-resource notification event is three times the default low-memory value.\"\r\n*/ \r\n\r\nSET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\nSET DATEFORMAT mdy;\r\n\r\nDECLARE @masterpid int, @RegKey NVARCHAR(255), @LowMemoryThreshold int, @systemmem bigint\r\nDECLARE @permstbl TABLE ([name] sysname);\r\n\r\nDECLARE @sqlcmd NVARCHAR(max), @params NVARCHAR(600)\r\nDECLARE @sqlmajorver int\r\nDECLARE @ErrorMessage NVARCHAR(4000)\r\n\r\nSELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);\r\n\r\nSELECT @masterpid = principal_id FROM master.sys.database_principals (NOLOCK) WHERE sid = SUSER_SID()\r\n\r\nINSERT INTO @permstbl\r\nSELECT a.name\r\nFROM master.sys.all_objects a (NOLOCK) INNER JOIN master.sys.database_permissions b (NOLOCK) ON a.[OBJECT_ID] = b.major_id\r\nWHERE a.type IN ('P', 'X') AND b.grantee_principal_id <>0\r\nAND b.grantee_principal_id <>2\r\nAND b.grantee_principal_id = @masterpid;\r\n\r\nIF @sqlmajorver = 9\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT @systemmemOUT = t1.record.value(''(./Record/MemoryRecord/TotalPhysicalMemory)[1]'', ''bigint'')/1024\r\nFROM (SELECT MAX([TIMESTAMP]) AS [TIMESTAMP], CONVERT(xml, record) AS record \r\n\tFROM sys.dm_os_ring_buffers (NOLOCK)\r\n\tWHERE ring_buffer_type = N''RING_BUFFER_RESOURCE_MONITOR''\r\n\t\tAND record LIKE ''%RESOURCE_MEMPHYSICAL%''\r\n\tGROUP BY record) AS t1';\r\nEND\r\nELSE\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT @systemmemOUT = total_physical_memory_kb/1024 FROM sys.dm_os_sys_memory';\r\nEND\r\n\r\nSET @params = N'@systemmemOUT bigint OUTPUT';\r\n\r\nEXECUTE sp_executesql @sqlcmd, @params, @systemmemOUT=@systemmem OUTPUT;\r\n\r\nIF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1) OR ((SELECT COUNT([name]) FROM @permstbl WHERE [name] = 'xp_regread') = 1)\r\nBEGIN\r\n\tBEGIN TRY\r\n\t\tSELECT @RegKey = N'System\\CurrentControlSet\\Control\\SessionManager\\MemoryManagement'\r\n\t\tEXEC master.sys.xp_regread N'HKEY_LOCAL_MACHINE', @RegKey, N'LowMemoryThreshold', @LowMemoryThreshold OUTPUT, NO_OUTPUT\r\n\t\t\r\n\t\tIF @LowMemoryThreshold IS NULL\r\n\t\tSELECT @LowMemoryThreshold = CASE WHEN @systemmem <= 4096 THEN 32 ELSE 64 END\r\n\tEND TRY\r\n\tBEGIN CATCH\r\n\t\tSELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;\r\n\t\tSELECT @ErrorMessage = 'Server Memory subsection - Error raised in TRY block. ' + ERROR_MESSAGE()\r\n\t\tRAISERROR (@ErrorMessage, 16, 1);\r\n\tEND CATCH\r\nEND\r\nELSE\r\nBEGIN\r\n\tRAISERROR('WARNING: Missing permissions for full \"Instance info\" checks. Bypassing LowMemoryThreshold check', 16, 1, N'sysadmin')\r\n\t--RETURN\r\nEND;\r\n\r\nIF @LowMemoryThreshold IS NOT NULL\r\nSELECT 'Memory_checks' AS [Category], 'Memory_RM_Tresholds' AS [Information], @LowMemoryThreshold AS [MEMPHYSICAL_LOW_Threshold], @LowMemoryThreshold * 3 AS [MEMPHYSICAL_HIGH_Threshold]\r\n\r\nSELECT 'Memory_checks' AS [Category], 'Memory_RM_Notifications' AS [Information], \r\nCASE WHEN x.[TIMESTAMP] BETWEEN -2147483648 AND 2147483647 AND si.ms_ticks BETWEEN -2147483648 AND 2147483647 THEN DATEADD(ms, x.[TIMESTAMP] - si.ms_ticks, GETDATE()) \r\n    ELSE DATEADD(s, ([TIMESTAMP]/1000) - (si.ms_ticks/1000), GETDATE()) END AS Event_Time,\r\n    record.value('(./Record/ResourceMonitor/Notification)[1]', 'VARCHAR(max)') AS [Notification],\r\n    record.value('(./Record/MemoryRecord/TotalPhysicalMemory)[1]', 'bigint')/1024 AS [Total_Physical_Mem_MB],\r\n    record.value('(./Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint')/1024 AS [Avail_Physical_Mem_MB],\r\n    record.value('(./Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint')/1024 AS [Avail_VAS_MB],\r\n    record.value('(./Record/MemoryRecord/TotalPageFile)[1]', 'bigint')/1024 AS [Total_Pagefile_MB],\r\n    record.value('(./Record/MemoryRecord/AvailablePageFile)[1]', 'bigint')/1024 AS [Avail_Pagefile_MB]\r\nFROM (SELECT [TIMESTAMP], CONVERT(xml, record) AS record \r\n            FROM sys.dm_os_ring_buffers (NOLOCK)\r\n            WHERE ring_buffer_type = N'RING_BUFFER_RESOURCE_MONITOR') AS x\r\nCROSS JOIN sys.dm_os_sys_info si (NOLOCK)\r\n--WHERE CASE WHEN x.[timestamp] BETWEEN -2147483648 AND 2147483648 THEN DATEADD(ms, x.[timestamp] - si.ms_ticks, GETDATE()) \r\n--\tELSE DATEADD(s, (x.[timestamp]/1000) - (si.ms_ticks/1000), GETDATE()) END >= DATEADD(hh, -12, GETDATE())\r\nORDER BY 2 DESC;",
            "metadata": {},
            "outputs": [],
            "execution_count": 8
        },
        {
            "cell_type": "markdown",
            "source": "Hand Movements from Cache Clock Hands subsection",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\nSET DATEFORMAT mdy;\r\n\r\nDECLARE @sqlcmd NVARCHAR(max), @params NVARCHAR(600)\r\nDECLARE @sqlmajorver int\r\nDECLARE @ErrorMessage NVARCHAR(4000)\r\n\r\nSELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);\r\n\r\nIF (SELECT COUNT(rounds_count) FROM sys.dm_os_memory_cache_clock_hands (NOLOCK) WHERE rounds_count > 0) > 0\r\nBEGIN\r\n\tIF @sqlmajorver >= 11\r\n\tBEGIN\r\n\t\tSET @sqlcmd = N'SELECT ''Memory_checks'' AS [Category], ''Clock_Hand_Notifications'' AS [Information], mcch.name, mcch.[type], \r\nmcch.clock_hand, mcch.clock_status, SUM(mcch.rounds_count) AS rounds_count,\r\nSUM(mcch.removed_all_rounds_count) AS cache_entries_removed_all_rounds, \r\nSUM(mcch.removed_last_round_count) AS cache_entries_removed_last_round,\r\nSUM(mcch.updated_last_round_count) AS cache_entries_updated_last_round,\r\nSUM(mcc.pages_kb) AS cache_pages_kb,\r\nSUM(mcc.pages_in_use_kb) AS cache_pages_in_use_kb,\r\nSUM(mcc.entries_count) AS cache_entries_count, \r\nSUM(mcc.entries_in_use_count) AS cache_entries_in_use_count, \r\nCASE WHEN mcch.last_tick_time BETWEEN -2147483648 AND 2147483647 AND si.ms_ticks BETWEEN -2147483648 AND 2147483647 THEN DATEADD(ms, mcch.last_tick_time - si.ms_ticks, GETDATE()) \r\n\tWHEN mcch.last_tick_time/1000 BETWEEN -2147483648 AND 2147483647 AND si.ms_ticks/1000 BETWEEN -2147483648 AND 2147483647 THEN DATEADD(s, (mcch.last_tick_time/1000) - (si.ms_ticks/1000), GETDATE()) \r\n\tELSE NULL END AS last_clock_hand_move\r\nFROM sys.dm_os_memory_cache_counters mcc (NOLOCK)\r\nINNER JOIN sys.dm_os_memory_cache_clock_hands mcch (NOLOCK) ON mcc.cache_address = mcch.cache_address\r\nCROSS JOIN sys.dm_os_sys_info si (NOLOCK)\r\nWHERE mcch.rounds_count > 0\r\nGROUP BY mcch.name, mcch.[type], mcch.clock_hand, mcch.clock_status, mcc.pages_kb, mcc.pages_in_use_kb, mcch.last_tick_time, si.ms_ticks, mcc.entries_count, mcc.entries_in_use_count\r\nORDER BY SUM(mcch.removed_all_rounds_count) DESC, mcch.[type];'\r\n\tEND\r\n\tELSE\r\n\tBEGIN\r\n\t\tSET @sqlcmd = N'SELECT ''Memory_checks'' AS [Category], ''Clock_Hand_Notifications'' AS [Information], mcch.name, mcch.[type], \r\nmcch.clock_hand, mcch.clock_status, SUM(mcch.rounds_count) AS rounds_count,\r\nSUM(mcch.removed_all_rounds_count) AS cache_entries_removed_all_rounds, \r\nSUM(mcch.removed_last_round_count) AS cache_entries_removed_last_round,\r\nSUM(mcch.updated_last_round_count) AS cache_entries_updated_last_round,\r\nSUM(mcc.single_pages_kb) AS cache_single_pages_kb,\r\nSUM(mcc.multi_pages_kb) AS cache_multi_pages_kb,\r\nSUM(mcc.single_pages_in_use_kb) AS cache_single_pages_in_use_kb,\r\nSUM(mcc.multi_pages_in_use_kb) AS cache_multi_pages_in_use_kb,\r\nSUM(mcc.entries_count) AS cache_entries_count, \r\nSUM(mcc.entries_in_use_count) AS cache_entries_in_use_count, \r\nCASE WHEN mcch.last_tick_time BETWEEN -2147483648 AND 2147483647 AND si.ms_ticks BETWEEN -2147483648 AND 2147483647 THEN DATEADD(ms, mcch.last_tick_time - si.ms_ticks, GETDATE()) \r\n\tWHEN mcch.last_tick_time/1000 BETWEEN -2147483648 AND 2147483647 AND si.ms_ticks/1000 BETWEEN -2147483648 AND 2147483647 THEN DATEADD(s, (mcch.last_tick_time/1000) - (si.ms_ticks/1000), GETDATE()) \r\n\tELSE NULL END AS last_clock_hand_move\r\nFROM sys.dm_os_memory_cache_counters mcc (NOLOCK)\r\nINNER JOIN sys.dm_os_memory_cache_clock_hands mcch (NOLOCK) ON mcc.cache_address = mcch.cache_address\r\nCROSS JOIN sys.dm_os_sys_info si (NOLOCK)\r\nWHERE mcch.rounds_count > 0\r\nGROUP BY mcch.name, mcch.[type], mcch.clock_hand, mcch.clock_status, mcc.single_pages_kb, mcc.multi_pages_kb, mcc.single_pages_in_use_kb, mcc.multi_pages_in_use_kb, mcch.last_tick_time, si.ms_ticks, mcc.entries_count, mcc.entries_in_use_count\r\nORDER BY SUM(mcch.removed_all_rounds_count) DESC, mcch.[type];'\r\n\tEND\r\n\tEXECUTE sp_executesql @sqlcmd;\r\nEND\r\nELSE\r\nBEGIN\r\n\tSELECT 'Memory_checks' AS [Category], 'Clock_Hand_Notifications' AS [Information], 'OK' AS Comment\r\nEND;",
            "metadata": {},
            "outputs": [],
            "execution_count": 9
        },
        {
            "cell_type": "markdown",
            "source": "Buffer Pool Consumers from Buffer Descriptors subsection",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\n\r\nDECLARE @sqlcmd NVARCHAR(max), @params NVARCHAR(600)\r\nDECLARE @sqlmajorver int\r\n\r\nSELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);\r\n\r\n-- Note: in case of NUMA architecture, more than one entry per database is expected\r\n\r\nSET @sqlcmd = 'SELECT ''Memory_checks'' AS [Category], ''Buffer_Pool_Consumers'' AS [Information], \r\nnuma_node, COUNT_BIG(DISTINCT page_id)*8/1024 AS total_pages_MB, \r\nCASE database_id WHEN 32767 THEN ''ResourceDB'' ELSE DB_NAME(database_id) END AS database_name,\r\nSUM(CONVERT(BIGINT,row_count))/COUNT_BIG(DISTINCT page_id) AS avg_row_count_per_page, \r\nSUM(CONVERT(BIGINT, free_space_in_bytes))/COUNT_BIG(DISTINCT page_id) AS avg_free_space_bytes_per_page\r\n' + CASE WHEN @sqlmajorver >= 12 THEN ',is_in_bpool_extension' ELSE '' END + '\r\n' + CASE WHEN @sqlmajorver = 10 THEN ',numa_node' ELSE '' END + '\r\n' + CASE WHEN @sqlmajorver >= 11 THEN ',AVG(read_microsec) AS avg_read_microsec' ELSE '' END + '\r\nFROM sys.dm_os_buffer_descriptors\r\n--WHERE bd.page_type IN (''DATA_PAGE'', ''INDEX_PAGE'')\r\nGROUP BY database_id' + CASE WHEN @sqlmajorver >= 10 THEN ', numa_node' ELSE '' END + CASE WHEN @sqlmajorver >= 12 THEN ', is_in_bpool_extension' ELSE '' END + '\r\nORDER BY total_pages_MB DESC;'\r\nEXECUTE sp_executesql @sqlcmd;",
            "metadata": {},
            "outputs": [],
            "execution_count": 10
        },
        {
            "cell_type": "markdown",
            "source": "Memory Allocations from Memory Clerks and In-Memory OLTP Engine subsection",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\n\r\nDECLARE @sqlcmd NVARCHAR(max), @params NVARCHAR(600)\r\nDECLARE @sqlmajorver int, @xtp bit\r\n\r\nSELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);\r\n\r\nSET @sqlcmd = N'SELECT ''Memory_checks'' AS [Category], [type] AS Alloc_Type, \r\n' + CASE WHEN @sqlmajorver < 11 THEN 'SUM(single_pages_kb + multi_pages_kb + virtual_memory_committed_kb + shared_memory_committed_kb + awe_allocated_kb) AS Alloc_Mem_KB'\r\n\tELSE 'SUM(pages_kb + virtual_memory_committed_kb + shared_memory_committed_kb + awe_allocated_kb) AS Alloc_Mem_KB' END + '\r\nFROM sys.dm_os_memory_clerks \r\nWHERE type IN (''CACHESTORE_COLUMNSTOREOBJECTPOOL'',''CACHESTORE_CLRPROC'',''CACHESTORE_OBJCP'',''CACHESTORE_PHDR'',''CACHESTORE_SQLCP'',''CACHESTORE_TEMPTABLES'',\r\n''MEMORYCLERK_SQLBUFFERPOOL'',''MEMORYCLERK_SQLCLR'',''MEMORYCLERK_SQLGENERAL'',''MEMORYCLERK_SQLLOGPOOL'',''MEMORYCLERK_SQLOPTIMIZER'',\r\n''MEMORYCLERK_SQLQUERYCOMPILE'',''MEMORYCLERK_SQLQUERYEXEC'',''MEMORYCLERK_SQLQUERYPLAN'',''MEMORYCLERK_SQLSTORENG'',''MEMORYCLERK_XTP'',\r\n''OBJECTSTORE_LOCK_MANAGER'',''OBJECTSTORE_SNI_PACKET'',''USERSTORE_DBMETADATA'',''USERSTORE_OBJPERM'')\r\nGROUP BY [type]\r\nUNION ALL\r\nSELECT ''Memory_checks'' AS [Category], ''Others'' AS Alloc_Type, \r\n' + CASE WHEN @sqlmajorver < 11 THEN 'SUM(single_pages_kb + multi_pages_kb + virtual_memory_committed_kb + shared_memory_committed_kb) AS Alloc_Mem_KB'\r\n\tELSE 'SUM(pages_kb + virtual_memory_committed_kb + shared_memory_committed_kb) AS Alloc_Mem_KB' END + '\r\nFROM sys.dm_os_memory_clerks \r\nWHERE type NOT IN (''CACHESTORE_COLUMNSTOREOBJECTPOOL'',''CACHESTORE_CLRPROC'',''CACHESTORE_OBJCP'',''CACHESTORE_PHDR'',''CACHESTORE_SQLCP'',''CACHESTORE_TEMPTABLES'',\r\n''MEMORYCLERK_SQLBUFFERPOOL'',''MEMORYCLERK_SQLCLR'',''MEMORYCLERK_SQLGENERAL'',''MEMORYCLERK_SQLLOGPOOL'',''MEMORYCLERK_SQLOPTIMIZER'',\r\n''MEMORYCLERK_SQLQUERYCOMPILE'',''MEMORYCLERK_SQLQUERYEXEC'',''MEMORYCLERK_SQLQUERYPLAN'',''MEMORYCLERK_SQLSTORENG'',''MEMORYCLERK_XTP'',\r\n''OBJECTSTORE_LOCK_MANAGER'',''OBJECTSTORE_SNI_PACKET'',''USERSTORE_DBMETADATA'',''USERSTORE_OBJPERM'')\r\nORDER BY Alloc_Mem_KB DESC'\r\nEXECUTE sp_executesql @sqlcmd;\r\n\r\nIF @sqlmajorver >= 12\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT @xtpOUT = COUNT(*) FROM sys.dm_db_xtp_memory_consumers';\r\n\tSET @params = N'@xtpOUT int OUTPUT';\r\n\tEXECUTE sp_executesql @sqlcmd, @params, @xtpOUT = @xtp OUTPUT;\r\n\t\r\n\tIF @xtp > 0\r\n\tBEGIN\r\n\t\tSET @sqlcmd = N'SELECT ''Memory_checks'' AS [Category], ''InMemory_Consumers'' AS Alloc_Type, \r\nOBJECT_NAME([object_id]) AS [Object_Name], memory_consumer_type_desc, [object_id], index_id, \r\nallocated_bytes/(1024*1024) AS Allocated_MB, used_bytes/(1024*1024) AS Used_MB, \r\nCASE WHEN used_bytes IS NULL THEN ''used_bytes_is_varheap_only'' ELSE '''' END AS [Comment]\r\nFROM sys.dm_db_xtp_memory_consumers\r\nWHERE [object_id] > 0\r\nORDER BY Allocated_MB DESC' -- Only user objects; system objects are negative numbers\r\n\t\tEXECUTE sp_executesql @sqlcmd;\r\n\r\n\t\tSET @sqlcmd = N'SELECT ''Memory_checks'' AS [Category], ''InMemory_Alloc'' AS Alloc_Type, \r\nSUM(allocated_bytes)/(1024*1024) AS total_allocated_MB, SUM(used_bytes)/(1024*1024) AS total_used_MB\r\nFROM sys.dm_db_xtp_memory_consumers\r\nORDER BY total_allocated_MB DESC'\r\n\t\tEXECUTE sp_executesql @sqlcmd;\r\n\tEND;\r\nEND;\r\n",
            "metadata": {},
            "outputs": [],
            "execution_count": 11
        },
        {
            "cell_type": "markdown",
            "source": "OOM Notifications subsection",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\nSET DATEFORMAT mdy;\r\n\r\nDECLARE @sqlcmd NVARCHAR(max), @params NVARCHAR(600)\r\n\r\nIF (SELECT COUNT([TIMESTAMP]) FROM sys.dm_os_ring_buffers (NOLOCK) WHERE ring_buffer_type = N'RING_BUFFER_OOM') > 0\r\nBEGIN\t\t\r\n\tSELECT 'Memory_checks' AS [Category], 'OOM_Notifications' AS [Information], \r\n\tCASE WHEN x.[TIMESTAMP] BETWEEN -2147483648 AND 2147483647 AND si.ms_ticks BETWEEN -2147483648 AND 2147483647 THEN DATEADD(ms, x.[TIMESTAMP] - si.ms_ticks, GETDATE()) \r\n\t\tELSE DATEADD(s, ([TIMESTAMP]/1000) - (si.ms_ticks/1000), GETDATE()) END AS Event_Time,\r\n\t\trecord.value('(./Record/OOM/Action)[1]', 'varchar(50)') AS [Action],\r\n\t\trecord.value('(./Record/OOM/Resources)[1]', 'int') AS [Resources],\r\n\t\trecord.value('(./Record/OOM/Task)[1]', 'varchar(20)') AS [Task],\r\n\t\trecord.value('(./Record/OOM/Pool)[1]', 'int') AS [PoolID],\r\n\t\trgrp.name AS [PoolName],\r\n\t\trecord.value('(./Record/MemoryRecord/MemoryUtilization)[1]', 'bigint') AS [MemoryUtilPct],\r\n\t\trecord.value('(./Record/MemoryRecord/TotalPhysicalMemory)[1]', 'bigint')/1024 AS [Total_Physical_Mem_MB],\r\n\t\trecord.value('(./Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint')/1024 AS [Avail_Physical_Mem_MB],\r\n\t\trecord.value('(./Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint')/1024 AS [Avail_VAS_MB],\r\n\t\trecord.value('(./Record/MemoryRecord/TotalPageFile)[1]', 'bigint')/1024 AS [Total_Pagefile_MB],\r\n\t\trecord.value('(./Record/MemoryRecord/AvailablePageFile)[1]', 'bigint')/1024 AS [Avail_Pagefile_MB]\r\n\tFROM (SELECT [TIMESTAMP], CONVERT(xml, record) AS record \r\n\t\t\t\tFROM sys.dm_os_ring_buffers (NOLOCK)\r\n\t\t\t\tWHERE ring_buffer_type = N'RING_BUFFER_OOM') AS x\r\n\tCROSS JOIN sys.dm_os_sys_info si (NOLOCK)\r\n\tLEFT JOIN sys.resource_governor_resource_pools rgrp (NOLOCK) ON rgrp.pool_id = record.value('(./Record/OOM/Pool)[1]', 'int')\r\n\t--WHERE CASE WHEN x.[timestamp] BETWEEN -2147483648 AND 2147483648 THEN DATEADD(ms, x.[timestamp] - si.ms_ticks, GETDATE()) \r\n\t--\tELSE DATEADD(s, (x.[timestamp]/1000) - (si.ms_ticks/1000), GETDATE()) END >= DATEADD(hh, -12, GETDATE())\r\n\tORDER BY 2 DESC;\r\nEND\r\nELSE\r\nBEGIN\r\n\tSELECT 'Memory_checks' AS [Category], 'OOM_Notifications' AS [Information], 'OK' AS Comment\r\nEND;",
            "metadata": {},
            "outputs": [],
            "execution_count": 12
        },
        {
            "cell_type": "markdown",
            "source": "Lock Pages in Memory subsection",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nSET ANSI_WARNINGS ON;\r\nSET QUOTED_IDENTIFIER ON;\r\nSET DATEFORMAT mdy;\r\n\r\nDECLARE @sqlcmd NVARCHAR(max), @params NVARCHAR(600);\r\nDECLARE @sqlmajorver int, @sqlminorver int, @sqlbuild int;\r\nDECLARE @ErrorMessage NVARCHAR(4000);\r\nDECLARE @masterpid int;\r\nDECLARE @permstbl TABLE ([name] sysname);\r\nDECLARE @lpim bit, @lognumber int, @logcount int;\r\nDECLARE @arch smallint, @osver VARCHAR(5), @ostype VARCHAR(10), @StartDate DATETIME;\r\n\r\nSELECT @masterpid = principal_id FROM master.sys.database_principals (NOLOCK) WHERE sid = SUSER_SID()\r\n\r\nINSERT INTO @permstbl\r\nSELECT a.name\r\nFROM master.sys.all_objects a (NOLOCK) INNER JOIN master.sys.database_permissions b (NOLOCK) ON a.[OBJECT_ID] = b.major_id\r\nWHERE a.type IN ('P', 'X') AND b.grantee_principal_id <>0\r\nAND b.grantee_principal_id <>2\r\nAND b.grantee_principal_id = @masterpid;\r\n\r\nSELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);\r\nSELECT @sqlminorver = CONVERT(int, (@@microsoftversion / 0x10000) & 0xff);\r\nSELECT @sqlbuild = CONVERT(int, @@microsoftversion & 0xffff);\r\n\r\nIF (@sqlmajorver >= 11 AND @sqlmajorver < 14) OR (@sqlmajorver = 10 AND @sqlminorver = 50 AND @sqlbuild >= 2500)\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT @ostypeOUT = ''Windows'', @osverOUT = CASE WHEN windows_release IN (''6.3'',''10.0'') AND (@@VERSION LIKE ''%Build 10586%'' OR @@VERSION LIKE ''%Build 14393%'') THEN ''10.0'' ELSE windows_release END, @archOUT = CASE WHEN @@VERSION LIKE ''%<X64>%'' THEN 64 WHEN @@VERSION LIKE ''%<IA64>%'' THEN 128 ELSE 32 END FROM sys.dm_os_windows_info (NOLOCK)';\r\n\tSET @params = N'@osverOUT VARCHAR(5) OUTPUT, @ostypeOUT VARCHAR(10) OUTPUT, @archOUT smallint OUTPUT';\r\n\tEXECUTE sp_executesql @sqlcmd, @params, @osverOUT=@osver OUTPUT, @ostypeOUT=@ostype OUTPUT, @archOUT=@arch OUTPUT;\r\nEND\r\nELSE IF @sqlmajorver >= 14\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT @ostypeOUT = host_platform, @osverOUT = CASE WHEN host_platform = ''Windows'' AND host_release IN (''6.3'',''10.0'') THEN ''10.0'' ELSE host_release END, @archOUT = CASE WHEN @@VERSION LIKE ''%<X64>%'' THEN 64 ELSE 32 END FROM sys.dm_os_host_info (NOLOCK)';\r\n\tSET @params = N'@osverOUT VARCHAR(5) OUTPUT, @ostypeOUT VARCHAR(10) OUTPUT, @archOUT smallint OUTPUT';\r\n\tEXECUTE sp_executesql @sqlcmd, @params, @osverOUT=@osver OUTPUT, @ostypeOUT=@ostype OUTPUT, @archOUT=@arch OUTPUT;\r\nEND;\r\nELSE\r\nBEGIN\r\n\tBEGIN TRY\r\n\t\tDECLARE @str VARCHAR(500), @str2 VARCHAR(500), @str3 VARCHAR(500)\r\n\t\tDECLARE @sysinfo TABLE (id int, \r\n\t\t\t[Name] NVARCHAR(256), \r\n\t\t\tInternal_Value bigint, \r\n\t\t\tCharacter_Value NVARCHAR(256));\r\n\t\t\t\r\n\t\tINSERT INTO @sysinfo\r\n\t\tEXEC xp_msver;\r\n\t\t\r\n\t\tSELECT @osver = LEFT(Character_Value, CHARINDEX(' ', Character_Value)-1) -- 5.2 is WS2003; 6.0 is WS2008; 6.1 is WS2008R2; 6.2 is WS2012, 6.3 is WS2012R2, 6.3 (14396) is WS2016\r\n\t\tFROM @sysinfo\r\n\t\tWHERE [Name] LIKE 'WindowsVersion%';\r\n\t\t\r\n\t\tSELECT @arch = CASE WHEN RTRIM(Character_Value) LIKE '%x64%' OR RTRIM(Character_Value) LIKE '%AMD64%' THEN 64\r\n\t\t\tWHEN RTRIM(Character_Value) LIKE '%x86%' OR RTRIM(Character_Value) LIKE '%32%' THEN 32\r\n\t\t\tWHEN RTRIM(Character_Value) LIKE '%IA64%' THEN 128 END\r\n\t\tFROM @sysinfo\r\n\t\tWHERE [Name] LIKE 'Platform%';\r\n\r\n\t\tSET @ostype = 'Windows'\r\n\tEND TRY\r\n\tBEGIN CATCH\r\n\t\tSELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;\r\n\t\tSELECT @ErrorMessage = 'Windows Version and Architecture subsection - Error raised in TRY block. ' + ERROR_MESSAGE()\r\n\t\tRAISERROR (@ErrorMessage, 16, 1);\r\n\tEND CATCH\r\nEND;\r\n\r\nIF ((@sqlmajorver = 13 AND @sqlbuild >= 4000) OR @sqlmajorver > 13)\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT @lpimOUT = CASE WHEN sql_memory_model = 2 THEN 1 ELSE 0 END FROM sys.dm_os_sys_info (NOLOCK)';\r\n\tSET @params = N'@lpimOUT bit OUTPUT';\r\n\tEXECUTE sp_executesql @sqlcmd, @params, @lpimOUT=@lpim OUTPUT;\r\nEND\r\n\r\nIF ((@sqlmajorver = 13 AND @sqlbuild < 4000) OR (@sqlmajorver >= 10 AND @sqlmajorver < 13))\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT @lpimOUT = CASE WHEN locked_page_allocations_kb > 0 THEN 1 ELSE 0 END FROM sys.dm_os_process_memory (NOLOCK)'\r\n\tSET @params = N'@lpimOUT bit OUTPUT';\r\n\tEXECUTE sp_executesql @sqlcmd, @params, @lpimOUT=@lpim OUTPUT\r\nEND\r\n\r\nIF @sqlmajorver = 9\r\nBEGIN\r\n\tIF ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1 -- Is sysadmin\r\n\t\tOR ISNULL(IS_SRVROLEMEMBER(N'securityadmin'), 0) = 1 -- Is securityadmin\r\n\t\tOR ((SELECT COUNT([name]) FROM @permstbl WHERE [name] = 'sp_readerrorlog') > 0\r\n\t\t\tAND (SELECT COUNT([name]) FROM @permstbl WHERE [name] = 'xp_readerrorlog') > 0\r\n\t\t\tAND (SELECT COUNT([name]) FROM @permstbl WHERE [name] = 'xp_enumerrorlogs') > 0)\r\n\tBEGIN\r\n\t\tIF EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#lpimdbcc'))\r\n\t\tDROP TABLE #lpimdbcc;\r\n\t\tIF NOT EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#lpimdbcc'))\r\n\t\tCREATE TABLE #lpimdbcc (logdate DATETIME, spid VARCHAR(50), logmsg VARCHAR(4000))\r\n\r\n\t\tIF EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#lpimavail_logs'))\r\n\t\tDROP TABLE #lpimavail_logs;\r\n\t\tIF NOT EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#lpimavail_logs'))\r\n\t\tCREATE TABLE #lpimavail_logs (lognum int, logdate DATETIME, logsize int) \r\n\r\n\t\t-- Get the number of available logs \r\n\t\tINSERT INTO #lpimavail_logs \r\n\t\tEXEC xp_enumerrorlogs \r\n\r\n\t\t-- Get Start date\r\n\t\tSET @sqlcmd = N'SELECT @StartDateOUT = login_time FROM master..sysprocesses (NOLOCK) WHERE spid = 1';\r\n\t\t\r\n        SET @params = N'@StartDateOUT DATETIME OUTPUT';\r\n\r\n\t\tEXECUTE sp_executesql @sqlcmd, @params, @StartDateOUT=@StartDate OUTPUT;\r\n\t\t\r\n\t\tSELECT @lognumber = MIN(lognum) FROM #lpimavail_logs WHERE DATEADD(dd, DATEDIFF(dd, 0, logdate), 0) >= DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0)\r\n\r\n\t\tSELECT @logcount = ISNULL(MAX(lognum),@lognumber) FROM #lpimavail_logs WHERE DATEADD(dd, DATEDIFF(dd, 0, logdate), 0) >= DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0)\r\n\r\n\t\tIF @lognumber IS NULL\r\n\t\tBEGIN\r\n\t\t\tSELECT @ErrorMessage = 'WARNING: Could not retrieve information about Locked pages usage in SQL Server 2005'\r\n\t\t\tRAISERROR (@ErrorMessage, 16, 1);\r\n\t\tEND\r\n\t\tELSE\r\n\t\tWHILE @lognumber < @logcount \r\n\t\tBEGIN\r\n\t\t\t-- Cycle through sql error logs (Cannot use Large Page Extensions:  lock memory privilege was not granted)\r\n\t\t\tSELECT @sqlcmd = 'EXEC master..sp_readerrorlog ' + CONVERT(VARCHAR(3),@lognumber) + ', 1, ''Using locked pages for buffer pool'''\r\n\t\t\tBEGIN TRY\r\n\t\t\t\tINSERT INTO #lpimdbcc (logdate, spid, logmsg) \r\n\t\t\t\tEXECUTE (@sqlcmd);\r\n\t\t\tEND TRY\r\n\t\t\tBEGIN CATCH\r\n\t\t\t\tSELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;\r\n\t\t\t\tSELECT @ErrorMessage = 'Errorlog based subsection - Error raised in TRY block 1. ' + ERROR_MESSAGE()\r\n\t\t\t\tRAISERROR (@ErrorMessage, 16, 1);\r\n\t\t\tEND CATCH\r\n\t\t\t-- Next log \r\n\t\t\t--SET @lognumber = @lognumber + 1 \r\n\t\t\tSELECT @lognumber = MIN(lognum) FROM #lpimavail_logs WHERE lognum > @lognumber\r\n\t\tEND \r\n\r\n\t\tIF (SELECT COUNT(*) FROM #lpimdbcc) > 0\r\n\t\tBEGIN\r\n\t\t\tSET @lpim = 1\r\n\t\tEND\r\n\t\tELSE IF (SELECT COUNT(*) FROM #lpimdbcc) = 0 AND @lognumber IS NOT NULL\r\n\t\tBEGIN\r\n\t\t\tSET @lpim = 0\r\n\t\tEND;\r\n\t\t\r\n\t\tDROP TABLE #lpimavail_logs;\r\n\t\tDROP TABLE #lpimdbcc;\r\n\tEND\r\n\tELSE\r\n\tBEGIN\r\n\t\tRAISERROR('WARNING: Only a sysadmin or securityadmin can run the \"Locked_pages\" check. Bypassing check', 16, 1, N'permissions')\r\n\t\tRAISERROR('WARNING: If not sysadmin or securityadmin, then user must be a granted EXECUTE permissions on the following sprocs to run checks: xp_enumerrorlogs and sp_readerrorlog. Bypassing check', 16, 1, N'extended_sprocs')\r\n\t\t--RETURN\r\n\tEND;\r\nEND\r\n\r\nIF @lpim = 0 AND CONVERT(DECIMAL(3,1), @osver) < 6.0 AND @arch = 64\r\nBEGIN\r\n\tSELECT 'Memory_checks' AS [Category], 'Locked_pages' AS [Check], 'WARNING: Locked pages are not in use by SQL Server. In a WS2003 x64 architecture it is recommended to enable LPIM' AS [Deviation]\r\nEND\r\nELSE IF @lpim = 1 AND CONVERT(DECIMAL(3,1), @osver) < 6.0 AND @arch = 64\r\nBEGIN\r\n\tSELECT 'Memory_checks' AS [Category], 'Locked_pages' AS [Check], 'INFORMATION: Locked pages are being used by SQL Server. This is recommended in a WS2003 x64 architecture' AS [Deviation]\r\nEND\r\nELSE IF @lpim = 1 AND CONVERT(DECIMAL(3,1), @osver) >= 6.0 AND @arch = 64\r\nBEGIN\r\n\tSELECT 'Memory_checks' AS [Category], 'Locked_pages' AS [Check], 'INFORMATION: Locked pages are being used by SQL Server. This is recommended in WS2008 or above only when there are signs of paging' AS [Deviation]\r\nEND\r\nELSE IF @lpim IS NULL\r\nBEGIN\r\n\tSELECT 'Memory_checks' AS [Category], 'Locked_pages' AS [Check], 'Could not retrieve information' AS [Deviation]\r\nEND\r\nELSE\r\nBEGIN\r\n\tSELECT 'Memory_checks' AS [Category], 'Locked_pages' AS [Check], 'Not in use' AS [Deviation]\r\nEND;",
            "metadata": {},
            "outputs": [],
            "execution_count": 13
        }
    ]
}