FixOrphanUsers_without Readonly_DB

  Declare @cnt1 int,@cnt2 int
  Declare @i int,@j int
  Declare @user varchar(50)
  Declare @sSQL varchar(200)
  declare @DBName varchar(500)
  /* Temporary Table to Hold all the Databses in the server*/
 
  Set nocount ON
 
  Select @DBName=NULL

  CREATE TABLE #tmplg1
  (
    Rown tinyint identity(1,1),
    DB varchar(500)
   )

  /* Temporary Table to Hold all the Orphaned users*/
  CREATE TABLE #tmplg2
  (
    Rown int identity(1,1),
    DBUser varchar(50)
   )
  /*Insert all the Databases into the Temp table If its not provided in the Parameter Else insert just the Specified database*/
  IF (Isnull(@DBName,'A')='A')
  Insert into #tmplg1(DB)
  select name from master.sys.databases where state_desc='ONLINE'and is_read_only != 1
    
  Else
  Insert into #tmplg1(DB) Values(@DBName)
 
  --Select * from #tmplg1

  Select @cnt1=count(1) from #tmplg1
  Select @i=1

  While (@i<=@cnt1)
  Begin
    Select @DBName=DB from #tmplg1 where Rown=@i
    Print char(13)+ 'Processing ' + @DBName + '...' + Char (13)
    SELECT @sSQL='Insert into #tmplg2(DBUser) '
    SELECT @sSQL= @sSQL + 'Select UserName = name  from [' + @DBName + ']..sysusers where issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null'
    Exec (@sSQL)

    Select @cnt2=count(1) from #tmplg2
    Select @j=1
    While(@j<=@cnt2)
    Begin
      Select @user=DBUser from #tmplg2 where Rown=@j
      IF EXISTS(select * from master..syslogins where loginname = @user) 
      Begin
        SELECT @sSQL='Use ['+@DBName+'] EXEC sp_change_users_login "Update_One","'+ @user+ '","'+ @user + '"'
        Exec (@sSQL)
--       Print @sSQL
      End
      Else
      Begin
        Print 'User '+ @user + ' is not present in SYSLOGIN'
      End
    --Select @j
    Select @j=@j+1
    End
  Select @i=@i+1
  End

/*Drops the Temporary tables*/
  Drop table #tmplg1
  Drop table #tmplg2

To script out entire databases schema and roles permissions with table and column level permissions.

/*
Security Audit Report
1) List all access provisioned to a SQL user or Windows user/group directly
2) List all access provisioned to a SQL user or Windows user/group through a database or application role
3) List all access provisioned to the public role

Columns Returned:
UserType        : Value will be either 'SQL User', 'Windows User', or 'Windows Group'.
                  This reflects the type of user/group defined for the SQL Server account.
DatabaseUserName: Name of the associated user as defined in the database user account.  The database user may not be the
                  same as the server user.
LoginName       : SQL or Windows/Active Directory user account.  This could also be an Active Directory group.
Role            : The role name.  This will be null if the associated permissions to the object are defined at directly
                  on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType  : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
                  DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ObjectType      : Type of object the user/role is assigned permissions on.  Examples could include USER_TABLE,
                  SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
Schema          : Name of the schema the object is in.
ObjectName      : Name of the object that the user/role is assigned permissions on.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ColumnName      : Name of the column of the object that the user/role is assigned permissions on. This value
                  is only populated if the object is a table, view or a table value function.
*/

    --1) List all access provisioned to a SQL user or Windows user/group directly
    SELECT
        [UserType] = CASE princ.[type]
                         WHEN 'S' THEN 'SQL User'
                         WHEN 'U' THEN 'Windows User'
                         WHEN 'G' THEN 'Windows Group'
                     END,
        [DatabaseUserName] = princ.[name],
        [LoginName]        = ulogin.[name],
        [Role]             = NULL,
        [PermissionType]   = perm.[permission_name],
        [PermissionState]  = perm.[state_desc],
        [ObjectType] = CASE perm.[class]
                           WHEN 1 THEN obj.[type_desc]        -- Schema-contained objects
                           ELSE perm.[class_desc]             -- Higher-level objects
                       END,
        [Schema] = objschem.[name],
        [ObjectName] = CASE perm.[class]
                           WHEN 3 THEN permschem.[name]       -- Schemas
                           WHEN 4 THEN imp.[name]             -- Impersonations
                           ELSE OBJECT_NAME(perm.[major_id])  -- General objects
                       END,
        [ColumnName] = col.[name]
    FROM
        --Database user
        sys.database_principals            AS princ
        --Login accounts
        LEFT JOIN sys.server_principals    AS ulogin    ON ulogin.[sid] = princ.[sid]
        --Permissions
        LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = princ.[principal_id]
        LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
        LEFT JOIN sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        --Table columns
        LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                           AND col.[column_id] = perm.[minor_id]
        --Impersonations
        LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        princ.[type] IN ('S','U','G')
        -- No need for these system accounts
        AND princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')

UNION

    --2) List all access provisioned to a SQL user or Windows user/group through a database or application role
    SELECT
        [UserType] = CASE membprinc.[type]
                         WHEN 'S' THEN 'SQL User'
                         WHEN 'U' THEN 'Windows User'
                         WHEN 'G' THEN 'Windows Group'
                     END,
        [DatabaseUserName] = membprinc.[name],
        [LoginName]        = ulogin.[name],
        [Role]             = roleprinc.[name],
        [PermissionType]   = perm.[permission_name],
        [PermissionState]  = perm.[state_desc],
        [ObjectType] = CASE perm.[class]
                           WHEN 1 THEN obj.[type_desc]        -- Schema-contained objects
                           ELSE perm.[class_desc]             -- Higher-level objects
                       END,
        [Schema] = objschem.[name],
        [ObjectName] = CASE perm.[class]
                           WHEN 3 THEN permschem.[name]       -- Schemas
                           WHEN 4 THEN imp.[name]             -- Impersonations
                           ELSE OBJECT_NAME(perm.[major_id])  -- General objects
                       END,
        [ColumnName] = col.[name]
    FROM
        --Role/member associations
        sys.database_role_members          AS members
        --Roles
        JOIN      sys.database_principals  AS roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
        --Role members (database users)
        JOIN      sys.database_principals  AS membprinc ON membprinc.[principal_id] = members.[member_principal_id]
        --Login accounts
        LEFT JOIN sys.server_principals    AS ulogin    ON ulogin.[sid] = membprinc.[sid]
        --Permissions
        LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = roleprinc.[principal_id]
        LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
        LEFT JOIN sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        --Table columns
        LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                           AND col.[column_id] = perm.[minor_id]
        --Impersonations
        LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        membprinc.[type] IN ('S','U','G')
        -- No need for these system accounts
        AND membprinc.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')

UNION

    --3) List all access provisioned to the public role, which everyone gets by default
    SELECT
        [UserType]         = '{All Users}',
        [DatabaseUserName] = '{All Users}',
        [LoginName]        = '{All Users}',
        [Role]             = roleprinc.[name],
        [PermissionType]   = perm.[permission_name],
        [PermissionState]  = perm.[state_desc],
        [ObjectType] = CASE perm.[class]
                           WHEN 1 THEN obj.[type_desc]        -- Schema-contained objects
                           ELSE perm.[class_desc]             -- Higher-level objects
                       END,
        [Schema] = objschem.[name],
        [ObjectName] = CASE perm.[class]
                           WHEN 3 THEN permschem.[name]       -- Schemas
                           WHEN 4 THEN imp.[name]             -- Impersonations
                           ELSE OBJECT_NAME(perm.[major_id])  -- General objects
                       END,
        [ColumnName] = col.[name]
    FROM
        --Roles
        sys.database_principals            AS roleprinc
        --Role permissions
        LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = roleprinc.[principal_id]
        LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
        --All objects
        JOIN      sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        --Table columns
        LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                           AND col.[column_id] = perm.[minor_id]
        --Impersonations
        LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        roleprinc.[type] = 'R'
        AND roleprinc.[name] = 'public'
        AND obj.[is_ms_shipped] = 0

ORDER BY
    [UserType],
    [DatabaseUserName],
    [LoginName],
    [Role],
    [Schema],
    [ObjectName],
    [ColumnName],
    [PermissionType],
    [PermissionState],
    [ObjectType]

SQL Server 2016 Diagnostic Information Queries

-- Check the major product version to see if it is SQL Server 2016 CTP 2 or greater
IF NOT EXISTS (SELECT * WHERE CONVERT(varchar(128), SERVERPROPERTY('ProductVersion')) LIKE '13%')
 BEGIN
  DECLARE @ProductVersion varchar(128) = CONVERT(varchar(128), SERVERPROPERTY('ProductVersion'));
  RAISERROR ('Script does not match the ProductVersion [%s] of this instance. Many of these queries may not work on this version.' , 18 , 16 , @ProductVersion);
 END
 ELSE
  PRINT N'You have the correct major version of SQL Server for this diagnostic information script';
 

-- Instance level queries *******************************

-- SQL and OS Version information for current instance  (Query 1) (Version Info)
SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version Info];
------

-- SQL Server 2016 RTM Branch Builds           
-- Build   Description   Release Date    
-- 13.0.200.172  CTP 2.0    5/26/2015
-- 13.0.300.44  CTP 2.1    6/14/2015
-- 13.0.407.1  CTP 2.2    7/28/2015
-- 13.0.500.53  CTP 2.3    9/4/2015
-- 13.0.600.65  CTP 2.4    9/30/2015
-- 13.0.700.242  CTP 3.0    10/29/2015
-- 13.0.900.73  CTP 3.2    12/12/2015
-- 13.0.1000.276 CTP 3.3    1/27/2016
-- 13.0.1100.288 RC0     3/2/2016
-- 13.0.1200.242 RC1     3/18/2016
-- 13.0.1300.275 RC2     3/28/2016
-- 13.0.1400.361 RC3     4/11/2016
-- 13.0.1601.5  RTM     6/1/2016
-- 13.0.2149.0  RTM CU1    7/25/2016 

-- Microsoft SQL Server 2016 RTM Latest Cumulative Update
-- https://www.microsoft.com/en-us/download/details.aspx?id=53338

-- Download SQL Server Management Studio (SSMS)
-- https://msdn.microsoft.com/en-us/library/mt238290.aspx    

-- Announcing updates to the SQL Server Incremental Servicing Model (ISM)
-- https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-updates-to-the-sql-server-incremental-servicing-model-ism/

-- How to determine the version, edition and update level of SQL Server and its components
-- https://support.microsoft.com/en-us/kb/321185

-- Get socket, physical core and logical core count from the SQL Server Error log. (Query 2) (Core Counts)
-- This query might take a few seconds if you have not recycled your error log recently
EXEC sys.xp_readerrorlog 0, 1, N'detected', N'socket';
------

-- This can help you determine the exact core counts used by SQL Server and whether HT is enabled or not
-- It can also help you confirm your SQL Server licensing model
-- Be on the lookout for this message "using 20 logical processors based on SQL Server licensing"
-- (when you have more than 20 logical cores) which means grandfathered Server/CAL licensing
-- This query will return no results if your error log has been recycled since the instance was last started

-- Get selected server properties (Query 3) (Server Properties)
SELECT SERVERPROPERTY('MachineName') AS [MachineName],
SERVERPROPERTY('ServerName') AS [ServerName], 
SERVERPROPERTY('InstanceName') AS [Instance],
SERVERPROPERTY('IsClustered') AS [IsClustered],
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS],
SERVERPROPERTY('Edition') AS [Edition],
SERVERPROPERTY('ProductLevel') AS [ProductLevel],    -- What servicing branch (RTM/SP/CU)
SERVERPROPERTY('ProductUpdateLevel') AS [ProductUpdateLevel], -- Within a servicing branch, what CU# is applied
SERVERPROPERTY('ProductVersion') AS [ProductVersion],
SERVERPROPERTY('ProductMajorVersion') AS [ProductMajorVersion],
SERVERPROPERTY('ProductMinorVersion') AS [ProductMinorVersion],
SERVERPROPERTY('ProductBuild') AS [ProductBuild],
SERVERPROPERTY('ProductBuildType') AS [ProductBuildType],     -- Is this a GDR or OD hotfix (NULL if on a CU build)
SERVERPROPERTY('ProductUpdateReference') AS [ProductUpdateReference], -- KB article number that is applicable for this build
SERVERPROPERTY('ProcessID') AS [ProcessID],
SERVERPROPERTY('Collation') AS [Collation],
SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled],
SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly],
SERVERPROPERTY('FilestreamConfiguredLevel') AS [FilestreamConfiguredLevel],
SERVERPROPERTY('IsHadrEnabled') AS [IsHadrEnabled],
SERVERPROPERTY('HadrManagerStatus') AS [HadrManagerStatus],
SERVERPROPERTY('IsXTPSupported') AS [IsXTPSupported],
SERVERPROPERTY('InstanceDefaultDataPath') AS [InstanceDefaultDataPath],
SERVERPROPERTY('InstanceDefaultLogPath') AS [InstanceDefaultLogPath],
SERVERPROPERTY('IsPolybaseInstalled') AS [IsPolybaseInstalled],    -- New for SQL Server 2016
SERVERPROPERTY('IsAdvancedAnalyticsInstalled') AS [IsRServicesInstalled], -- New for SQL Server 2016
SERVERPROPERTY('BuildClrVersion') AS [Build CLR Version];
------

-- This gives you a lot of useful information about your instance of SQL Server,
-- such as the ProcessID for SQL Server and your collation
-- Note: Some columns will be NULL on older SQL Server builds

-- Get instance-level configuration values for instance  (Query 4) (Configuration Values)
SELECT name, value, value_in_use, minimum, maximum, [description], is_dynamic, is_advanced
FROM sys.configurations WITH (NOLOCK)
ORDER BY name OPTION (RECOMPILE);
------

-- Focus on these settings:
-- automatic soft-NUMA disabled (should be 0 in most cases)
-- backup checksum default (should be 1)
-- backup compression default (should be 1 in most cases)
-- clr enabled (only enable if it is needed)
-- cost threshold for parallelism (depends on your workload)
-- lightweight pooling (should be zero)
-- max degree of parallelism (depends on your workload and hardware)
-- max server memory (MB) (set to an appropriate value, not the default)
-- optimize for ad hoc workloads (should be 1)
-- priority boost (should be zero)
-- remote admin connections (should be 1)

-- New configuration options for SQL Server 2016
-- allow polybase export (Allow INSERT into a Hadoop external table)
-- automatic soft-NUMA disabled (Automatic soft-NUMA is enabled by default)
-- external scripts enabled (Allows execution of external scripts, for R Services)
-- hadoop connectivity (Configure SQL Server to connect to external Hadoop or Microsoft Azure storage blob data sources through PolyBase)
-- polybase network encryption (Configure SQL Server to encrypt control and data channels when using PolyBase)
-- remote data archive (Allow the use of the REMOTE_DATA_ARCHIVE data access for Stretch databases)

-- Returns a list of all global trace flags that are enabled (Query 5) (Global Trace Flags)
DBCC TRACESTATUS (-1);
------

-- If no global trace flags are enabled, no results will be returned.
-- It is very useful to know what global trace flags are currently enabled as part of the diagnostic process.

-- Common trace flags that should be enabled in most cases
-- TF 3226 - Supresses logging of successful database backup messages to the SQL Server Error Log

-- TF 6534 - Enables use of native code to improve performance with spatial data
-- https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-native-spatial-implementations/

-- The behavior of TF 1117, 1118 are enabled for tempdb in SQL Server 2016 by default
-- The behavior of TF 2371 is enabled by default in SQL Server 2016

-- SQL Server query optimizer hotfix trace flag 4199 servicing model
-- https://support.microsoft.com/en-us/kb/974006

-- Returns status of instant file initialization (Query 6) (IFI Status)
EXEC sys.xp_readerrorlog 0, 1, N'Database Instant File Initialization';
------

-- Lets you determine whether Instant File Initialization (IFI) is enabled for the instance

-- SQL Server Process Address space info  (Query 7) (Process Memory)
-- (shows whether locked pages is enabled, among other things)
SELECT physical_memory_in_use_kb/1024 AS [SQL Server Memory Usage (MB)],
       large_page_allocations_kb, locked_page_allocations_kb, page_fault_count,
    memory_utilization_percentage, available_commit_limit_kb,
    process_physical_memory_low, process_virtual_memory_low
FROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE);
------

-- You want to see 0 for process_physical_memory_low
-- You want to see 0 for process_virtual_memory_low
-- This indicates that you are not under internal memory pressure
-- If locked_page_allocations_kb > 0, then LPIM is enabled

-- SQL Server Services information (Query 8) (SQL Server Services Info)
SELECT servicename, process_id, startup_type_desc, status_desc,
last_startup_time, service_account, is_clustered, cluster_nodename, [filename]
FROM sys.dm_server_services WITH (NOLOCK) OPTION (RECOMPILE);
------

-- Tells you the account being used for the SQL Server Service and the SQL Agent Service
-- Shows the process_id, when they were last started, and their current status
-- Shows whether you are running on a failover cluster instance

-- Get SQL Server Agent jobs and Category information (Query 9) (SQL Server Agent Jobs)
SELECT sj.name AS [JobName], sj.[description] AS [JobDescription], SUSER_SNAME(sj.owner_sid) AS [JobOwner],
sj.date_created, sj.[enabled], sj.notify_email_operator_id, sj.notify_level_email, sc.name AS [CategoryName],
js.next_run_date, js.next_run_time
FROM msdb.dbo.sysjobs AS sj WITH (NOLOCK)
INNER JOIN msdb.dbo.syscategories AS sc WITH (NOLOCK)
ON sj.category_id = sc.category_id
LEFT OUTER JOIN msdb.dbo.sysjobschedules AS js WITH (NOLOCK)
ON sj.job_id = js.job_id
ORDER BY sj.name OPTION (RECOMPILE);
------

-- Gives you some basic information about your SQL Server Agent jobs, who owns them and how they are configured
-- Look for Agent jobs that are not owned by sa
-- Look for jobs that have a notify_email_operator_id set to 0 (meaning no operator)
-- Look for jobs that have a notify_level_email set to 0 (meaning no e-mail is ever sent)
--
-- MSDN sysjobs documentation
-- http://msdn.microsoft.com/en-us/library/ms189817.aspx

-- Get SQL Server Agent Alert Information (Query 10) (SQL Server Agent Alerts)
SELECT name, event_source, message_id, severity, [enabled], has_notification,
       delay_between_responses, occurrence_count, last_occurrence_date, last_occurrence_time
FROM msdb.dbo.sysalerts WITH (NOLOCK)
ORDER BY name OPTION (RECOMPILE);
------

-- Gives you some basic information about your SQL Server Agent Alerts (which are different from SQL Server Agent jobs)
-- Read more about Agent Alerts here: http://www.sqlskills.com/blogs/glenn/creating-sql-server-agent-alerts-for-critical-errors/

-- Windows information (Query 11) (Windows Info)
SELECT windows_release, windows_service_pack_level,
       windows_sku, os_language_version
FROM sys.dm_os_windows_info WITH (NOLOCK) OPTION (RECOMPILE);
------

-- Gives you major OS version, Service Pack, Edition, and language info for the operating system
-- 6.3 is either Windows 8.1, Windows 10 or Windows Server 2012 R2, Windows Server 2016
-- 6.2 is either Windows 8 or Windows Server 2012
-- 6.1 is either Windows 7 or Windows Server 2008 R2
-- 6.0 is either Windows Vista or Windows Server 2008

-- Windows SKU codes
-- 4 is Enterprise Edition
-- 7 is Standard Server Edition
-- 8 is Datacenter Server Edition
-- 10 is Enterprise Server Edition
-- 48 is Professional Edition

-- 1033 for os_language_version is US-English

-- SQL Server 2014 requires Windows Server 2012 or newer

-- Quick-Start Installation of SQL Server 2016
-- https://msdn.microsoft.com/en-us/library/bb500433(v=sql.130).aspx

-- Hardware and Software Requirements for Installing SQL Server 2016
-- https://msdn.microsoft.com/en-us/library/ms143506(v=sql.130).aspx

-- Using SQL Server in Windows 8, Windows 8.1, Windows Server 2012 and Windows Server 2012 R2 environments
-- http://support.microsoft.com/kb/2681562

-- SQL Server NUMA Node information  (Query 12) (SQL Server NUMA Info)
SELECT node_id, node_state_desc, memory_node_id, processor_group, online_scheduler_count,
       active_worker_count, avg_load_balance, resource_monitor_state
FROM sys.dm_os_nodes WITH (NOLOCK)
WHERE node_state_desc <> N'ONLINE DAC' OPTION (RECOMPILE);
------

-- Gives you some useful information about the composition and relative load on your NUMA nodes
-- You want to see an equal number of schedulers on each NUMA node
-- Watch out if SQL Server 2016 Standard Edition has been installed on a machine with more than 24 physical cores

-- Balancing Your Available SQL Server Core Licenses Evenly Across NUMA Nodes
-- http://www.sqlskills.com/blogs/glenn/balancing-your-available-sql-server-core-licenses-evenly-across-numa-nodes/

-- Good basic information about OS memory amounts and state  (Query 13) (System Memory)
SELECT total_physical_memory_kb/1024 AS [Physical Memory (MB)],
       available_physical_memory_kb/1024 AS [Available Memory (MB)],
       total_page_file_kb/1024 AS [Total Page File (MB)],
    available_page_file_kb/1024 AS [Available Page File (MB)],
    system_cache_kb/1024 AS [System Cache (MB)],
       system_memory_state_desc AS [System Memory State]
FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);
------

-- You want to see "Available physical memory is high" for System Memory State
-- This indicates that you are not under external memory pressure

-- You can skip the next three queries if you know you don't
-- have a clustered instance

-- Shows you where the SQL Server failover cluster diagnostic log is located and how it is configured  (Query 14) (SQL Server Error Log)
SELECT is_enabled, [path], max_size, max_files
FROM sys.dm_os_server_diagnostics_log_configurations WITH (NOLOCK) OPTION (RECOMPILE);
------

-- Knowing this information is important for troubleshooting purposes
-- Also shows you the location of other error and diagnostic log files

-- Get information about your cluster nodes and their status  (Query 15) (Cluster Node Properties)
-- (if your database server is in a failover cluster)
SELECT NodeName, status_description, is_current_owner
FROM sys.dm_os_cluster_nodes WITH (NOLOCK) OPTION (RECOMPILE);
------

-- Knowing which node owns the cluster resources is critical
-- Especially when you are installing Windows or SQL Server updates
-- You will see no results if your instance is not clustered

-- Get information about any AlwaysOn AG cluster this instance is a part of (Query 16) (AlwaysOn AG Cluster)
SELECT cluster_name, quorum_type_desc, quorum_state_desc
FROM sys.dm_hadr_cluster WITH (NOLOCK) OPTION (RECOMPILE);
------

-- You will see no results if your instance is not using AlwaysOn AGs

-- Recommended hotfixes and updates for Windows Server 2012 R2-based failover clusters
-- http://support.microsoft.com/kb/2920151

-- Good overview of AG health and status (Query 17) (AlwaysOn AG Status)
SELECT ag.name AS [AG Name], ar.replica_server_name, ar.availability_mode_desc, adc.[database_name],
       drs.is_local, drs.is_primary_replica, drs.synchronization_state_desc, drs.is_commit_participant,
    drs.synchronization_health_desc, drs.recovery_lsn, drs.truncation_lsn, drs.last_sent_lsn,
    drs.last_sent_time, drs.last_received_lsn, drs.last_received_time, drs.last_hardened_lsn,
    drs.last_hardened_time, drs.last_redone_lsn, drs.last_redone_time, drs.log_send_queue_size,
    drs.log_send_rate, drs.redo_queue_size, drs.redo_rate, drs.filestream_send_rate,
    drs.end_of_log_lsn, drs.last_commit_lsn, drs.last_commit_time, drs.database_state_desc
FROM sys.dm_hadr_database_replica_states AS drs WITH (NOLOCK)
INNER JOIN sys.availability_databases_cluster AS adc WITH (NOLOCK)
ON drs.group_id = adc.group_id
AND drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag WITH (NOLOCK)
ON ag.group_id = drs.group_id
INNER JOIN sys.availability_replicas AS ar WITH (NOLOCK)
ON drs.group_id = ar.group_id
AND drs.replica_id = ar.replica_id
ORDER BY ag.name, ar.replica_server_name, adc.[database_name] OPTION (RECOMPILE);

-- Hardware information from SQL Server 2016  (Query 18) (Hardware Info)
SELECT cpu_count AS [Logical CPU Count], scheduler_count, hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count],
physical_memory_kb/1024 AS [Physical Memory (MB)], committed_kb/1024 AS [Committed Memory (MB)],
committed_target_kb/1024 AS [Committed Target Memory (MB)],
max_workers_count AS [Max Workers Count], affinity_type_desc AS [Affinity Type],
sqlserver_start_time AS [SQL Server Start Time], virtual_machine_type_desc AS [Virtual Machine Type],
softnuma_configuration_desc AS [Soft NUMA Configuration]
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);
------

-- Gives you some good basic hardware information about your database server
-- Cannot distinguish between HT and multi-core
-- Note: virtual_machine_type_desc of HYPERVISOR does not automatically mean you are running SQL Server inside of a VM
-- It merely indicates that you have a hypervisor running on your host
-- Soft NUMA configuration is a new column for SQL Server 2016

-- Configure SQL Server to Use Soft-NUMA (SQL Server)
-- https://msdn.microsoft.com/en-us/library/ms345357(v=sql.130).aspx

-- Get System Manufacturer and model number from SQL Server Error log (Query 19) (System Manufacturer)
EXEC sys.xp_readerrorlog 0, 1, N'Manufacturer';
------

-- This can help you determine the capabilities and capacities of your database server
-- Can also be used to confirm if you are running in a VM
-- This query might take a few seconds if you have not recycled your error log recently
-- This query will return no results if your error log has been recycled since the instance was started

-- Get processor description from Windows Registry  (Query 20) (Processor Description)
EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\CentralProcessor\0', N'ProcessorNameString';
------

-- Gives you the model number and rated clock speed of your processor(s)
-- Your processors may be running at less than the rated clock speed due
-- to the Windows Power Plan or hardware power management

-- You can use CPU-Z to get your actual CPU core speed and a lot of other useful information
-- http://www.cpuid.com/softwares/cpu-z.html

-- You can learn more about processor selection for SQL Server by following this link
-- http://www.sqlskills.com/blogs/glenn/processor-selection-for-sql-server/

-- See if buffer pool extensions (BPE) is enabled (Query 21) (BPE Configuration)
SELECT [path], state_description, current_size_in_kb,
CAST(current_size_in_kb/1048576.0 AS DECIMAL(10,2)) AS [Size (GB)]
FROM sys.dm_os_buffer_pool_extension_configuration WITH (NOLOCK) OPTION (RECOMPILE);
------

-- BPE is available in both Standard Edition and Enterprise Edition
-- It is a more interesting feature for Standard Edition

-- Buffer Pool Extension to SSDs in SQL Server 2014
-- http://blogs.technet.com/b/dataplatforminsider/archive/2013/07/25/buffer-pool-extension-to-ssds-in-sql-server-2014.aspx

-- Look at buffer descriptors to see BPE usage by database (Query 22) (BPE Usage)
SELECT DB_NAME(database_id) AS [Database Name], COUNT(page_id) AS [Page Count],
CAST(COUNT(*)/128.0 AS DECIMAL(10, 2)) AS [Buffer size(MB)],
AVG(read_microsec) AS [Avg Read Time (microseconds)]
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id <> 32767
AND is_in_bpool_extension = 1
GROUP BY DB_NAME(database_id)
ORDER BY [Buffer size(MB)] DESC OPTION (RECOMPILE);
------

-- You will see no results if BPE is not enabled or if there is no BPE usage

-- Get information on location, time and size of any memory dumps from SQL Server  (Query 23) (Memory Dump Info)
SELECT [filename], creation_time, size_in_bytes/1048576.0 AS [Size (MB)]
FROM sys.dm_server_memory_dumps WITH (NOLOCK)
ORDER BY creation_time DESC OPTION (RECOMPILE);
------

-- This will not return any rows if you have
-- not had any memory dumps (which is a good thing)

-- Get number of data files in tempdb database (Query 24) (tempdb data files)
EXEC sys.xp_readerrorlog 0, 1, N'The tempdb database has';
------

-- Get the number of data files in the tempdb database
-- 4-8 data files that are all the same size is a good starting point

-- File names and paths for all user and system databases on instance  (Query 25) (Database Filenames and Paths)
SELECT DB_NAME([database_id]) AS [Database Name],
       [file_id], name, physical_name, [type_desc], state_desc,
    is_percent_growth, growth,
    CONVERT(bigint, growth/128.0) AS [Growth in MB],
       CONVERT(bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)
ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);
------

-- Things to look at:
-- Are data files and log files on different drives?
-- Is everything on the C: drive?
-- Is TempDB on dedicated drives?
-- Is there only one TempDB data file?
-- Are all of the TempDB data files the same size?
-- Are there multiple data files for user databases?
-- Is percent growth enabled for any files (which is bad)?

-- Volume info for all LUNS that have database files on the current instance (Query 26) (Volume Info)
SELECT DISTINCT vs.volume_mount_point, vs.file_system_type,
vs.logical_volume_name, CONVERT(DECIMAL(18,2),vs.total_bytes/1073741824.0) AS [Total Size (GB)],
CONVERT(DECIMAL(18,2), vs.available_bytes/1073741824.0) AS [Available Size (GB)], 
CONVERT(DECIMAL(18,2), vs.available_bytes * 1. / vs.total_bytes * 100.) AS [Space Free %]
FROM sys.master_files AS f WITH (NOLOCK)
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs
ORDER BY vs.volume_mount_point OPTION (RECOMPILE);
------

-- Shows you the total and free space on the LUNs where you have database files
-- Being low on free space can negatively affect performance

-- Drive level latency information (Query 27) (Drive Level Latency)
-- Based on code from Jimmy May
SELECT tab.[Drive], tab.volume_mount_point AS [Volume Mount Point],
 CASE
  WHEN num_of_reads = 0 THEN 0
  ELSE (io_stall_read_ms/num_of_reads)
 END AS [Read Latency],
 CASE
  WHEN num_of_writes = 0 THEN 0
  ELSE (io_stall_write_ms/num_of_writes)
 END AS [Write Latency],
 CASE
  WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0
  ELSE (io_stall/(num_of_reads + num_of_writes))
 END AS [Overall Latency],
 CASE
  WHEN num_of_reads = 0 THEN 0
  ELSE (num_of_bytes_read/num_of_reads)
 END AS [Avg Bytes/Read],
 CASE
  WHEN num_of_writes = 0 THEN 0
  ELSE (num_of_bytes_written/num_of_writes)
 END AS [Avg Bytes/Write],
 CASE
  WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0
  ELSE ((num_of_bytes_read + num_of_bytes_written)/(num_of_reads + num_of_writes))
 END AS [Avg Bytes/Transfer]
FROM (SELECT LEFT(UPPER(mf.physical_name), 2) AS Drive, SUM(num_of_reads) AS num_of_reads,
          SUM(io_stall_read_ms) AS io_stall_read_ms, SUM(num_of_writes) AS num_of_writes,
          SUM(io_stall_write_ms) AS io_stall_write_ms, SUM(num_of_bytes_read) AS num_of_bytes_read,
          SUM(num_of_bytes_written) AS num_of_bytes_written, SUM(io_stall) AS io_stall, vs.volume_mount_point
      FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
      INNER JOIN sys.master_files AS mf WITH (NOLOCK)
      ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
   CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.[file_id]) AS vs
      GROUP BY LEFT(UPPER(mf.physical_name), 2), vs.volume_mount_point) AS tab
ORDER BY [Overall Latency] OPTION (RECOMPILE);
------

-- Shows you the drive-level latency for reads and writes, in milliseconds
-- Latency above 30-40ms is usually a problem
-- These latency numbers include all file activity against all SQL Server
-- database file on each drive since SQL Server was last started

-- Calculates average stalls per read, per write, and per total input/output for each database file  (Query 28) (IO Stalls by File)
SELECT DB_NAME(fs.database_id) AS [Database Name], CAST(fs.io_stall_read_ms/(1.0 + fs.num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],
CAST(fs.io_stall_write_ms/(1.0 + fs.num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms],
CAST((fs.io_stall_read_ms + fs.io_stall_write_ms)/(1.0 + fs.num_of_reads + fs.num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms],
CONVERT(DECIMAL(18,2), mf.size/128.0) AS [File Size (MB)], mf.physical_name, mf.type_desc, fs.io_stall_read_ms, fs.num_of_reads,
fs.io_stall_write_ms, fs.num_of_writes, fs.io_stall_read_ms + fs.io_stall_write_ms AS [io_stalls], fs.num_of_reads + fs.num_of_writes AS [total_io],
io_stall_queued_read_ms AS [Resource Governor Total Read IO Latency (ms)], io_stall_queued_write_ms AS [Resource Governor Total Write IO Latency (ms)]
FROM sys.dm_io_virtual_file_stats(null,null) AS fs
INNER JOIN sys.master_files AS mf WITH (NOLOCK)
ON fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]
ORDER BY avg_io_stall_ms DESC OPTION (RECOMPILE);
------

-- Helps determine which database files on the entire instance have the most I/O bottlenecks
-- This can help you decide whether certain LUNs are overloaded and whether you might
-- want to move some files to a different location or perhaps improve your I/O performance
-- These latency numbers include all file activity against each SQL Server
-- database file since SQL Server was last started

-- Look for I/O requests taking longer than 15 seconds in the five most recent SQL Server Error Logs (Query 29) (IO Warnings)
CREATE TABLE #IOWarningResults(LogDate datetime, ProcessInfo sysname, LogText nvarchar(1000));

 INSERT INTO #IOWarningResults
 EXEC xp_readerrorlog 0, 1, N'taking longer than 15 seconds';

 INSERT INTO #IOWarningResults
 EXEC xp_readerrorlog 1, 1, N'taking longer than 15 seconds';

 INSERT INTO #IOWarningResults
 EXEC xp_readerrorlog 2, 1, N'taking longer than 15 seconds';

 INSERT INTO #IOWarningResults
 EXEC xp_readerrorlog 3, 1, N'taking longer than 15 seconds';

 INSERT INTO #IOWarningResults
 EXEC xp_readerrorlog 4, 1, N'taking longer than 15 seconds';

SELECT LogDate, ProcessInfo, LogText
FROM #IOWarningResults
ORDER BY LogDate DESC;

DROP TABLE #IOWarningResults;
------ 

-- Finding 15 second I/O warnings in the SQL Server Error Log is useful evidence of
-- poor I/O performance (which might have many different causes)
-- Look to see if you see any patterns in the results (same files, same drives, same time of day, etc.)

-- Diagnostics in SQL Server help detect stalled and stuck I/O operations
-- https://support.microsoft.com/en-us/kb/897284

-- Recovery model, log reuse wait description, log file size, log usage size  (Query 30) (Database Properties)
-- and compatibility level for all databases on instance
SELECT db.[name] AS [Database Name], db.recovery_model_desc AS [Recovery Model], db.state_desc, db.containment_desc,
db.log_reuse_wait_desc AS [Log Reuse Wait Description],
CONVERT(DECIMAL(18,2), ls.cntr_value/1024.0) AS [Log Size (MB)], CONVERT(DECIMAL(18,2), lu.cntr_value/1024.0) AS [Log Used (MB)],
CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log Used %],
db.[compatibility_level] AS [DB Compatibility Level],
db.is_mixed_page_allocation_on, db.page_verify_option_desc AS [Page Verify Option],
db.is_auto_create_stats_on, db.is_auto_update_stats_on, db.is_auto_update_stats_async_on, db.is_parameterization_forced,
db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on, db.is_auto_close_on, db.is_auto_shrink_on,
db.target_recovery_time_in_seconds, db.is_cdc_enabled, db.is_published, db.is_distributor, db.is_encrypted,
db.group_database_id, db.replica_id,db.is_memory_optimized_elevate_to_snapshot_on,
db.delayed_durability_desc, db.is_auto_create_stats_incremental_on,
db.is_query_store_on, db.is_sync_with_backup,
db.is_supplemental_logging_enabled, db.is_remote_data_archive_enabled,
db.is_encrypted, de.encryption_state, de.percent_complete, de.key_algorithm, de.key_length     
FROM sys.databases AS db WITH (NOLOCK)
INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK)
ON db.name = lu.instance_name
INNER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK)
ON db.name = ls.instance_name
LEFT OUTER JOIN sys.dm_database_encryption_keys AS de WITH (NOLOCK)
ON db.database_id = de.database_id
WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%'
AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
AND ls.cntr_value > 0
ORDER BY db.[name] OPTION (RECOMPILE);
------

-- Things to look at:
-- How many databases are on the instance?
-- What recovery models are they using?
-- What is the log reuse wait description?
-- How full are the transaction logs?
-- What compatibility level are the databases on?
-- What is the Page Verify Option? (should be CHECKSUM)
-- Is Auto Update Statistics Asynchronously enabled?
-- Make sure auto_shrink and auto_close are not enabled!

-- is_mixed_page_allocation_on is a new property for SQL Server 2016. Equivalent to TF 1118 for a user database

-- SQL Server 2016: Changes in default behavior for autogrow and allocations for tempdb and user databases
-- https://blogs.msdn.microsoft.com/sql_server_team/sql-server-2016-changes-in-default-behavior-for-autogrow-and-allocations-for-tempdb-and-user-databases/

-- Missing Indexes for all databases by Index Advantage  (Query 31) (Missing Indexes All Databases)
SELECT CONVERT(decimal(18,2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage],
migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
ORDER BY index_advantage DESC OPTION (RECOMPILE);
------

-- Getting missing index information for all of the databases on the instance is very useful
-- Look at last user seek time, number of user seeks to help determine source and importance
-- Also look at avg_user_impact and avg_total_user_cost to help determine importance
-- SQL Server is overly eager to add included columns, so beware
-- Do not just blindly add indexes that show up from this query!!!

-- Get VLF Counts for all databases on the instance (Query 32) (VLF Counts)
-- (adapted from Michelle Ufford)
CREATE TABLE #VLFInfo (RecoveryUnitID int, FileID  int,
        FileSize bigint, StartOffset bigint,
        FSeqNo      bigint, [Status]    bigint,
        Parity      bigint, CreateLSN   numeric(38));
 
CREATE TABLE #VLFCountResults(DatabaseName sysname, VLFCount int);
 
EXEC sp_MSforeachdb N'Use [?];

    INSERT INTO #VLFInfo
    EXEC sp_executesql N''DBCC LOGINFO([?])'';
 
    INSERT INTO #VLFCountResults
    SELECT DB_NAME(), COUNT(*)
    FROM #VLFInfo;

    TRUNCATE TABLE #VLFInfo;'
 
SELECT DatabaseName, VLFCount 
FROM #VLFCountResults
ORDER BY VLFCount DESC;
 
DROP TABLE #VLFInfo;
DROP TABLE #VLFCountResults;
------

-- High VLF counts can affect write performance
-- and they can make full database restores and crash recovery take much longer
-- Try to keep your VLF counts under 200 in most cases (depending on log file size)

-- Important change to VLF creation algorithm in SQL Server 2014
-- http://www.sqlskills.com/blogs/paul/important-change-vlf-creation-algorithm-sql-server-2014/

-- Get CPU utilization by database (Query 33) (CPU Usage by Database)
WITH DB_CPU_Stats
AS
(SELECT pa.DatabaseID, DB_Name(pa.DatabaseID) AS [Database Name], SUM(qs.total_worker_time/1000) AS [CPU_Time_Ms]
 FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
 CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
              FROM sys.dm_exec_plan_attributes(qs.plan_handle)
              WHERE attribute = N'dbid') AS pa
 GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [CPU Rank],
       [Database Name], [CPU_Time_Ms] AS [CPU Time (ms)],
       CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]
FROM DB_CPU_Stats
WHERE DatabaseID <> 32767 -- ResourceDB
ORDER BY [CPU Rank] OPTION (RECOMPILE);
------

-- Helps determine which database is using the most CPU resources on the instance

-- Get I/O utilization by database (Query 34) (IO Usage By Database)
WITH Aggregate_IO_Statistics
AS
(SELECT DB_NAME(database_id) AS [Database Name],
CAST(SUM(num_of_bytes_read + num_of_bytes_written)/1048576 AS DECIMAL(12, 2)) AS io_in_mb
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]
GROUP BY database_id)
SELECT ROW_NUMBER() OVER(ORDER BY io_in_mb DESC) AS [I/O Rank], [Database Name], io_in_mb AS [Total I/O (MB)],
       CAST(io_in_mb/ SUM(io_in_mb) OVER() * 100.0 AS DECIMAL(5,2)) AS [I/O Percent]
FROM Aggregate_IO_Statistics
ORDER BY [I/O Rank] OPTION (RECOMPILE);
------

-- Helps determine which database is using the most I/O resources on the instance

-- Get total buffer usage by database for current instance  (Query 35) (Total Buffer Usage by Database)
-- This make take some time to run on a busy instance
WITH AggregateBufferPoolUsage
AS
(SELECT DB_NAME(database_id) AS [Database Name],
CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2))  AS [CachedSize]
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id))
SELECT ROW_NUMBER() OVER(ORDER BY CachedSize DESC) AS [Buffer Pool Rank], [Database Name], CachedSize AS [Cached Size (MB)],
       CAST(CachedSize / SUM(CachedSize) OVER() * 100.0 AS DECIMAL(5,2)) AS [Buffer Pool Percent]
FROM AggregateBufferPoolUsage
ORDER BY [Buffer Pool Rank] OPTION (RECOMPILE);
------

-- Tells you how much memory (in the buffer pool)
-- is being used by each database on the instance

-- Clear Wait Stats with this command
-- DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

-- Isolate top waits for server instance since last restart or wait statistics clear  (Query 36) (Top Waits)
WITH [Waits]
AS (SELECT wait_type, wait_time_ms/ 1000.0 AS [WaitS],
          (wait_time_ms - signal_wait_time_ms) / 1000.0 AS [ResourceS],
           signal_wait_time_ms / 1000.0 AS [SignalS],
           waiting_tasks_count AS [WaitCount],
           100.0 *  wait_time_ms / SUM (wait_time_ms) OVER() AS [Percentage],
           ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats WITH (NOLOCK)
    WHERE [wait_type] NOT IN (
        N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP',
  N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
        N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
        N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE',
  N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
        N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT',
  N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
        N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE',
  N'MEMORY_ALLOCATION_EXT', N'ONDEMAND_TASK_QUEUE',
  N'PREEMPTIVE_OS_LIBRARYOPS', N'PREEMPTIVE_OS_COMOPS', N'PREEMPTIVE_OS_CRYPTOPS',
  N'PREEMPTIVE_OS_PIPEOPS', N'PREEMPTIVE_OS_AUTHENTICATIONOPS',
  N'PREEMPTIVE_OS_GENERICOPS', N'PREEMPTIVE_OS_VERIFYTRUST',
  N'PREEMPTIVE_OS_FILEOPS', N'PREEMPTIVE_OS_DEVICEOPS',
  N'PREEMPTIVE_XE_GETTARGETSTATE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
  N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
  N'QDS_ASYNC_QUEUE',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH',
  N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP',
  N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP',
  N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES',
  N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT',
  N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT', N'XE_LIVE_TARGET_TVF', N'XE_TIMER_EVENT')
    AND waiting_tasks_count > 0)
SELECT
    MAX (W1.wait_type) AS [WaitType],
    CAST (MAX (W1.WaitS) AS DECIMAL (16,2)) AS [Wait_Sec],
    CAST (MAX (W1.ResourceS) AS DECIMAL (16,2)) AS [Resource_Sec],
    CAST (MAX (W1.SignalS) AS DECIMAL (16,2)) AS [Signal_Sec],
    MAX (W1.WaitCount) AS [Wait Count],
    CAST (MAX (W1.Percentage) AS DECIMAL (5,2)) AS [Wait Percentage],
    CAST ((MAX (W1.WaitS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgWait_Sec],
    CAST ((MAX (W1.ResourceS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgRes_Sec],
    CAST ((MAX (W1.SignalS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgSig_Sec]
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum
HAVING SUM (W2.Percentage) - MAX (W1.Percentage) < 99 -- percentage threshold
OPTION (RECOMPILE);
------

-- Cumulative wait stats are not as useful on an idle instance that is not under load or performance pressure

-- SQL Server Wait Types Library (Paul Randal)
-- https://www.sqlskills.com/help/waits/

-- The SQL Server Wait Type Repository
-- http://blogs.msdn.com/b/psssql/archive/2009/11/03/the-sql-server-wait-type-repository.aspx

-- Wait statistics, or please tell me where it hurts
-- http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

-- SQL Server 2005 Performance Tuning using the Waits and Queues
-- http://technet.microsoft.com/en-us/library/cc966413.aspx

-- sys.dm_os_wait_stats (Transact-SQL)
-- http://msdn.microsoft.com/en-us/library/ms179984(v=sql.120).aspx

-- Get a count of SQL connections by IP address (Query 37) (Connection Counts by IP Address)
SELECT ec.client_net_address, es.[program_name], es.[host_name], es.login_name,
COUNT(ec.session_id) AS [connection count]
FROM sys.dm_exec_sessions AS es WITH (NOLOCK)
INNER JOIN sys.dm_exec_connections AS ec WITH (NOLOCK)
ON es.session_id = ec.session_id
GROUP BY ec.client_net_address, es.[program_name], es.[host_name], es.login_name 
ORDER BY ec.client_net_address, es.[program_name] OPTION (RECOMPILE);
------

-- This helps you figure where your database load is coming from
-- and verifies connectivity from other machines

-- Get Average Task Counts (run multiple times)  (Query 38) (Avg Task Counts)
SELECT AVG(current_tasks_count) AS [Avg Task Count],
AVG(work_queue_count) AS [Avg Work Queue Count],
AVG(runnable_tasks_count) AS [Avg Runnable Task Count],
AVG(pending_disk_io_count) AS [Avg Pending DiskIO Count]
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE scheduler_id < 255 OPTION (RECOMPILE);
------

-- Sustained values above 10 suggest further investigation in that area
-- High Avg Task Counts are often caused by blocking/deadlocking or other resource contention

-- Sustained values above 1 suggest further investigation in that area
-- High Avg Runnable Task Counts are a good sign of CPU pressure
-- High Avg Pending DiskIO Counts are a sign of disk pressure

-- Detect blocking (run multiple times)  (Query 39) (Detect Blocking)
SELECT t1.resource_type AS [lock type], DB_NAME(resource_database_id) AS [database],
t1.resource_associated_entity_id AS [blk object],t1.request_mode AS [lock req],  --- lock requested
t1.request_session_id AS [waiter sid], t2.wait_duration_ms AS [wait time],       -- spid of waiter 
(SELECT [text] FROM sys.dm_exec_requests AS r WITH (NOLOCK)                      -- get sql for waiter
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle])
WHERE r.session_id = t1.request_session_id) AS [waiter_batch],
(SELECT SUBSTRING(qt.[text],r.statement_start_offset/2,
    (CASE WHEN r.statement_end_offset = -1
    THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2
    ELSE r.statement_end_offset END - r.statement_start_offset)/2)
FROM sys.dm_exec_requests AS r WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS qt
WHERE r.session_id = t1.request_session_id) AS [waiter_stmt],     -- statement blocked
t2.blocking_session_id AS [blocker sid],          -- spid of blocker
(SELECT [text] FROM sys.sysprocesses AS p          -- get sql for blocker
CROSS APPLY sys.dm_exec_sql_text(p.[sql_handle])
WHERE p.spid = t2.blocking_session_id) AS [blocker_batch]
FROM sys.dm_tran_locks AS t1 WITH (NOLOCK)
INNER JOIN sys.dm_os_waiting_tasks AS t2 WITH (NOLOCK)
ON t1.lock_owner_address = t2.resource_address OPTION (RECOMPILE);
------

-- Helps troubleshoot blocking and deadlocking issues
-- The results will change from second to second on a busy system
-- You should run this query multiple times when you see signs of blocking

-- Get CPU Utilization History for last 256 minutes (in one minute intervals)  (Query 40) (CPU Utilization History)
-- This version works with SQL Server 2016
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info WITH (NOLOCK));

SELECT TOP(256) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
               SystemIdle AS [System Idle Process],
               100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
               DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
   record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
   AS [SystemIdle],
   record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int')
   AS [SQLProcessUtilization], [timestamp]
   FROM (SELECT [timestamp], CONVERT(xml, record) AS [record]
   FROM sys.dm_os_ring_buffers WITH (NOLOCK)
   WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
   AND record LIKE N'%<SystemHealth>%') AS x) AS y
ORDER BY record_id DESC OPTION (RECOMPILE);
------

-- Look at the trend over the entire period
-- Also look at high sustained Other Process CPU Utilization values

-- Get top total worker time queries for entire instance (Query 41) (Top Worker Time Queries)
SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name], LEFT(t.[text], 50) AS [Short Query Text], 
qs.total_worker_time AS [Total Worker Time], qs.min_worker_time AS [Min Worker Time],
qs.total_worker_time/qs.execution_count AS [Avg Worker Time],
qs.max_worker_time AS [Max Worker Time],
qs.min_elapsed_time AS [Min Elapsed Time],
qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],
qs.max_elapsed_time AS [Max Elapsed Time],
qs.min_logical_reads AS [Min Logical Reads],
qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
qs.max_logical_reads AS [Max Logical Reads],
qs.execution_count AS [Execution Count], qs.creation_time AS [Creation Time]
-- ,t.[text] AS [Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);
------

-- Helps you find the most expensive queries from a CPU perspective across the entire instance
-- Can also help track down parameter sniffing issues

-- Page Life Expectancy (PLE) value for each NUMA node in current instance  (Query 42) (PLE by NUMA Node)
SELECT @@SERVERNAME AS [Server Name], [object_name], instance_name, cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Buffer Node%' -- Handles named instances
AND counter_name = N'Page life expectancy' OPTION (RECOMPILE);
------

-- PLE is a good measurement of memory pressure
-- Higher PLE is better. Watch the trend over time, not the absolute value
-- This will only return one row for non-NUMA systems

-- Page Life Expectancy isn't what you think…
-- http://www.sqlskills.com/blogs/paul/page-life-expectancy-isnt-what-you-think/

-- Memory Grants Pending value for current instance  (Query 43) (Memory Grants Pending)
SELECT @@SERVERNAME AS [Server Name], [object_name], cntr_value AS [Memory Grants Pending]                                                                                                      
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Memory Manager%' -- Handles named instances
AND counter_name = N'Memory Grants Pending' OPTION (RECOMPILE);
------

-- Run multiple times, and run periodically is you suspect you are under memory pressure
-- Memory Grants Pending above zero for a sustained period is a very strong indicator of internal memory pressure

-- Memory Clerk Usage for instance  (Query 44) (Memory Clerk Usage)
-- Look for high value for CACHESTORE_SQLCP (Ad-hoc query plans)
SELECT TOP(10) mc.[type] AS [Memory Clerk Type],
       CAST((SUM(mc.pages_kb)/1024.0) AS DECIMAL (15,2)) AS [Memory Usage (MB)]
FROM sys.dm_os_memory_clerks AS mc WITH (NOLOCK)
GROUP BY mc.[type] 
ORDER BY SUM(mc.pages_kb) DESC OPTION (RECOMPILE);
------

-- MEMORYCLERK_SQLBUFFERPOOL was new for SQL Server 2012. It should be your highest consumer of memory

-- CACHESTORE_SQLCP  SQL Plans        
-- These are cached SQL statements or batches that aren't in stored procedures, functions and triggers
-- Watch out for high values for CACHESTORE_SQLCP

-- CACHESTORE_OBJCP  Object Plans     
-- These are compiled plans for stored procedures, functions and triggers

-- Find single-use, ad-hoc and prepared queries that are bloating the plan cache  (Query 45) (Ad hoc Queries)
SELECT TOP(50) [text] AS [QueryText], cp.cacheobjtype, cp.objtype, cp.size_in_bytes/1024 AS [Plan Size in KB]
FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype IN (N'Adhoc', N'Prepared')
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC OPTION (RECOMPILE);
------

-- Gives you the text, type and size of single-use ad-hoc and prepared queries that waste space in the plan cache
-- Enabling 'optimize for ad hoc workloads' for the instance can help (SQL Server 2008 and above only)
-- Running DBCC FREESYSTEMCACHE ('SQL Plans') periodically may be required to better control this
-- Enabling forced parameterization for the database can help, but test first!

-- Plan cache, adhoc workloads and clearing the single-use plan cache bloat
-- http://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/

-- Get top total logical reads queries for entire instance (Query 46) (Top Logical Reads Queries)
SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name], LEFT(t.[text], 50) AS [Short Query Text],
qs.total_logical_reads AS [Total Logical Reads],
qs.min_logical_reads AS [Min Logical Reads],
qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
qs.max_logical_reads AS [Max Logical Reads],  
qs.min_worker_time AS [Min Worker Time],
qs.total_worker_time/qs.execution_count AS [Avg Worker Time],
qs.max_worker_time AS [Max Worker Time],
qs.min_elapsed_time AS [Min Elapsed Time],
qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],
qs.max_elapsed_time AS [Max Elapsed Time],
qs.execution_count AS [Execution Count], qs.creation_time AS [Creation Time]
-- ,t.[text] AS [Complete Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE);
------

To find unused indexes in DB

-- Indexes read and write operation specific table to find unused indexes based on read column

SELECT OBJECT_NAME(s.[object_id]) AS [ObjectName] ,
i.name AS [IndexName] ,
i.is_Disabled ,
i.index_id ,
user_seeks + user_scans + user_lookups AS [Reads] ,
user_updates AS [Writes] ,
i.type_desc AS [IndexType] ,
i.fill_factor AS [FillFactor] ,
i.is_primary_key ,
i.is_unique ,
is_unique_constraint
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
AND i.index_id = s.index_id
AND s.database_id = DB_ID()
and i.is_primary_key = 0
and user_seeks + user_scans + user_lookups = 0
--and OBJECT_NAME(s.[object_id]) in ('dbo.emp_table')
ORDER BY OBJECT_NAME(s.[object_id]) ,
writes DESC ,
reads DESC ;

To find Index fragmentation

-- To check index fragmentation in SQL database

use[DBname]

go

SELECT a.object_id, object_name(a.object_id) AS TableName,
    a.index_id, name AS IndedxName, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
    (DB_ID (N'AdventureWorks2016_EXT')
        , OBJECT_ID(N'HumanResources.Employee')
        , NULL
        , NULL
        , NULL) AS a
INNER JOIN sys.indexes AS b
    ON a.object_id = b.object_id
    AND a.index_id = b.index_id;
GO

To Find Index sizes in Database

-- To find Indexes sizes in a SQL Database

Use[DBName]

go

SELECT
OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS SchemaName,
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages) AS 'Indexsize(KB)'
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY i.OBJECT_ID,i.index_id,i.name
ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id

Error message: The database owner SID recorded in the master Database differs from the database owner SID recorded in database 'SSISDB'

Executed as user: uk\Login. Microsoft (R) SQL Server Execute Package Utility  Version 13.0.5820.21 for 64-bit  Copyright (C) 2016 Microsoft. All rights reserved.    Started:  15:20:42  Failed to execute IS server package because of error 0x80131904. Server: aznu-p-ltnr-001,1433, Package path: Environment reference Id: NULL.  Description: The database owner SID recorded in the master database differs from the database owner SID recorded in database 'SSISDB'. You should correct this situation by resetting the owner of database 'SSISDB' using the ALTER AUTHORIZATION statement.  Source: .Net SqlClient Data Provider  Started:  15:20:42  Finished: 15:20:42  Elapsed:  0.625 seconds.  The package execution failed.  The step failed.

 

Then you need to check SSISDB Owner and if the Owner of the DB is different than SA then you need to change it to SA, if still the issue is not resolved then you need to enable the trustworthy property of SSISDB. please find the below.

 

to resolve it try the following :-

 

Alter AUTHORIZATION ON DATABASE ::[SSISDB] TO SA;

 

Alter DATABASE [SSISDB] Set TRUSTWORTHY ON;

SQL Server Notes in brief

SQL Server DBA Notes:

SSL Certificate: Secure Sockets Layer (SSL) encryption:

The Secure Sockets Layer (SSL) can be used to encrypt data transferred on network between our SQL Server instance and a client application.

Coming to installation:

- Need to go to MM console (Microsoft management: run --> MMC )

- On the File menu, click Add/Remove Snap-in...

- Select Certificates, click Add.

- computer account --> local computer --> finish.

--Configuring SQL Server to accept encrypted connections:

- Expand SQL Server Network Configuration and right-click on Protocols for <YourMSSQLServer>, then click Properties.

- On the Certificate tab, select the certificate you would like to use.

- On the Flags tab, select Yes in the ForceEncryption box, then click OK.

- Restart the SQL Server service.

SQL Server 2012:

Editions:

-------------

Enterprise edition

Satndard edition

Devoloper edition

Web edition

Express editon

-- CAPACITY PLANNING:

Whenever we need to do capacity planing, we need to consider few things like

- Backup space : how much backup space abilbale in the disks

- Snapshot space: If the DB in TBs at that time it will be useful to mitigate the time

- How much Archive space avilable

- ReIndex (Including multiple db's reindexing at the same time)

- Checkdb

- How much TEMPDB action: how much it will grow need to consider

- Enough spindles to perform well

----------------------------------

INSTALLATION ON SQL SERVER 2012 WITH POWER SHELL - GO TO ADMIN TOOLS - POWERSHELL

-- GET ALL THE COMPONETS AVILABLE IN THE SERVER:

GET-WINDOWSFETURE

----------------

-- STATISTICS:

-----------------

sTATISTICS are used by SQL Server to decide which index to use when you perform a query.

- Statistics are needs to maintain to be effective.

Statistics update Modes:

- Synchronous forces queries to wait when stats are out of date

- Asynchronous allows queries to continue with old stats and builds new ones in the background.

Query to check the options are enable in the db:

--SELECT NAME, IS_AUTO-UPDATE-STATS_ON, IS_AUTO_UPDATE_STATS_ASYNC_ON

FROM SYS.DATABASES

===============

Resticted Access:

- Single User: Allows only one User to access the DB

- Resticted User: Allows only db_owner role, sysadmin, or dbcreator

- Multi User: Allows all users

DMV's:

------

sys.dm_exec_sessions - Sessions in SQL Server

sys.dm_exec_connections - Connections to SQL Server

sys.dm_db_index_usage_stats - Seeks, scans, lookups per index

sys.dm_io_virtual_file_stats - IO statistics for databases and log files

sys.dm_tran_active_transactions - Transaction state for an instance of SQL Server

sys.dm_exec_query_plan - Returns query plan

sys.dm_os_wait_stats - Returns information what resources SQL is waiting on

sys.dm_os_performance_counters - Returns performance monitor counters related to SQL Server

-------------------------------------

DBCC: DBCC (Database consistency checker) act as Database console commands for SQL Server to check database consistency.

- DBCC CHECKDB :

CHECKS ALLOCATION AND CONSISTENCY errors

--DBCC CHECKCATALOG

Used to perform consistency check for a table/catalog for a particular database.

--DBCC CHECKALLOC

Used to check page usage and allocation of a database.

--DBCC CHECKTABLE(tablename)

Used to verify data page integrity, page offsets, data page links, page pointers, index pointers, index sort order for a specified table.

- DBCC SQLPERF (LOGPSACE) -

Displays T-log size and space used % for all databases

- DBCC INPUTBUFFER(sessionid)

Used to get the last statement sent by session to SQL server.

-DBCC HELP(<dbcc command>)

Displays the syntax of a DBCC command. DBCC HELP ('?')

To know syntax of DBCC SQLPERF, execute DBCC HELP ('SQLPERF')

- DBCC SHRINKDATABASE ('DBNAME', 10): DONT USE BECAUSE OF FAGMENTAION HAPPEND IN THE Db

Used to shrink data and log files for a particular database.

It release the free space to OS

- DBCC SHRINKFILE ('DBNAME', 5) : Used to shrink individual database files.

- DBCC LOGINFO

Displays virtual log file information contained in a T-log file.

- DBCC SHOWFILESTATS

Display Extent information for a database.

- DBCC TRACEON/TRACEOFF: USED TO ENABLE TRACING/ DISABLE TRACING.

- DBCC TRACESTATUS

Displays the trace status.

- DBCC USEROPTIONS

Displays the active SET options for a particular database

- DBCC MEMORYSTATUS

Displays SQL Server memory allocation status.

- DBCC Opentran

Displays information about oldest active transaction and oldest distributed and non-distributed replication transaction

CLUSTER: A Group of Servers can act as a sigle server.

----------------------

CLUSTER NODE : A Cluster node is a server with in the cluster and it has windows server and the cluster service installed.

CLUSTER Service: The cluster service manages all the activities that is specific to the cluster.

One instance of cluster service runs on each node in the cluster.

the Cluster service does the activities like

- Manages Cluster objects and configurations

- Manages the local restart policy

- Perform failover operations

cluster Group: A Cluster Group is a collection of logically grouped cluster resorces. It may contain cluster-aware application services

like SQL Server Group, file server etc.

Private network is used for cluster communication(LooksAlive and IsAlive checks) and Public network is used for client applications to connect.

When the private network goes down cluster communications will carry on through the public network. Loss of a private network does not initiate a failover.

The loss of the public network will initiate a failover since this is the network clients connect through.

Quorum is a count of voting members— a quorum is a way of taking attendance of cluster members who are present.

The cluster uses a quorum to determine who should be online.

When an instance of SQL Server is running in a Clustered Environment, Microsoft Cluster Service performs two checks to confirm if the SQL Server resource is still available.

One is LooksAlive and the other one is IsAlive check.

LooksAlive is a basic check in which the Cluster service queries the Windows Service Control Manager to check if the SQL Server service is still running.

By default this check happens every 5 seconds.

During IsAlive check the Cluster Service connects to the SQL Server instance with the help of c:\windows\system32\sqsrvres.dll and

runs SELECT @@SERVERNAME against the instance.

This check does not check if the user databases are online or not.

It just checks the SQL Server instance availability. This check happens every 60 seconds by default.

AlwaysOn:

---------

- WFC : Windows failover cluster required

- the SQL Servers what ever needs to participate alwaysOn that needs add in WFC

- configuration manager - AlwaysOn feture needs to enable

- DB fulll recovery, Recent full backup  needs to complete

Performance counters:

-------------------------

- Memory: avilable in bites counter indicates how many bytes of memory currently avilable for use by processes

High means good heath

low means shortage of memory

- Memory: pages\sec:

high: indicates execive paging

low: good health

- buffer manager: beffer cache hit ratio: high > 90 - good health,

- Total Server Memory is almost same as Target Server Memory - Good Health

Total Server Memory is much smaller than Target Server Memory

– There is a Memory Pressure or Max Server Memory is set to too low.

Page Life Expectancy:  the number of seconds the average page of data has been in the buffer pool.

High: Good Health

Low: Memory Pressure or Max Server Memory is not allocated properly

Estimated time Completion for Redoqueue in Secondary Replica in AON

--------------------- Minutes  -------------------------------------------
-- to execute the query on secondary replica  when ever he primary replica database log space is not decreased and log_reuse_wait_desc shown as AVAILABILITY_REPLICA
SELECT ar.replica_server_name,
       adc.database_name,
       ag.name AS ag_name,
       drs.is_local,
       drs.synchronization_state_desc,
       drs.synchronization_health_desc,
       drs.last_redone_time,
       drs.redo_queue_size,
       drs.redo_rate,
       (drs.redo_queue_size / drs.redo_rate) / 60.0 AS est_redo_completion_time_min ,
       drs.last_commit_lsn,
       drs.last_commit_time
FROM sys.dm_hadr_database_replica_states AS drs
INNER JOIN sys.availability_databases_cluster AS adc
       ON drs.group_id = adc.group_id AND
       drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag
       ON ag.group_id = drs.group_id
INNER JOIN sys.availability_replicas AS ar
       ON drs.group_id = ar.group_id AND
       drs.replica_id = ar.replica_id
    where (drs.redo_queue_size / drs.redo_rate) / 60.0 >10  --- Redo completion delays to More than 10mins it will get the alert
       
ORDER BY
       ag.name,
       ar.replica_server_name,
       adc.database_name;

----------------------- hours based ---------------

SELECT ar.replica_server_name,
       adc.database_name,
       ag.name AS ag_name,
       drs.is_local,
       drs.synchronization_state_desc,
       drs.synchronization_health_desc,
       drs.last_redone_time,
       drs.redo_queue_size,
       drs.redo_rate,
       (drs.redo_queue_size / drs.redo_rate) / 60.0/60.0 AS est_redo_completion_time_hours ,
       drs.last_commit_lsn,
       drs.last_commit_time
FROM sys.dm_hadr_database_replica_states AS drs
INNER JOIN sys.availability_databases_cluster AS adc
       ON drs.group_id = adc.group_id AND
       drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag
       ON ag.group_id = drs.group_id
INNER JOIN sys.availability_replicas AS ar
       ON drs.group_id = ar.group_id AND
       drs.replica_id = ar.replica_id
    where (drs.redo_queue_size / drs.redo_rate) / 60.0/60.0 >1  --- Redo completion delays to More than 1 hour it will get the alert
       
ORDER BY
       ag.name,
       ar.replica_server_name,
       adc.database_name;
  

What SQL is Executing

--- What SQL is Executing

-- select GETDATE()
-- select * from sysprocesses

use master  --  master..xp_readerrorlog 1 -- sp_who2 72 85 kill 201 --  dbcc inputbuffer(76) -- kill 59 dbcc opentran sp_lock dbcc tracestatus(-1)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select    --  DBCC MEMORYSTATUS dbcc inputbuffer(58) DBCC TRACESTATUS (-1)
      r.session_id,
   s.loginame,
      r.start_time,
      s.hostname,
      --r.blocking_session_id,
   s.blocked,
      r.command,
      db_name(r.database_id) as Database_name,
      r.wait_type,
      --r.wait_time,
      --r.open_transaction_count,
      r.cpu_time,
      r.total_elapsed_time as 'time(ms)',
      r.percent_complete,
      SUBSTRING(t.text, (r.statement_start_offset/2)+1,
        ((CASE r.statement_end_offset
          WHEN -1 THEN DATALENGTH(t.text)
         ELSE r.statement_end_offset
         END - r.statement_start_offset)/2) + 1) AS CurrentQuery,
   t.text as ParentQuery     
from sys.dm_exec_requests r
 cross apply sys.dm_exec_sql_text(r.sql_handle) t
 join sys.sysprocesses s on (r.session_id = s.spid)
where r.session_id > 50
and r.session_id != @@spid

--  select * from  sys.dm_exec_requests  select * from sys.sysprocesses

/*
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --  master..xp_readerrorlog 1 -- sp_who2 active 79 kill 70 --  dbcc inputbuffer(216) -- kill 216

    -- What SQL Statements Are Currently Running?
    SELECT [Spid] =    session_Id
    ,start_time
 , ecid
 , [Database] = DB_NAME(sp.dbid)
 , [User] = nt_username
 , [Status] = er.status
 , [Wait] = wait_type
 /*, [Individual Query] = SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2) + 1)*/
 ,[Parent Query] = qt.text
 , Program = program_name
 , Hostname
 , nt_domain
 , start_time
    FROM sys.dm_exec_requests er
    INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
    WHERE session_Id > 50              -- Ignore system spids.
    AND session_Id NOT IN (@@SPID)     -- Ignore this current statement.
    ORDER BY 1, 2
*/

--select * from sys.dm_os_waiting_tasks
/*
SELECT * FROM sys.dm_exec_query_memory_grants where grant_time is null

SELECT mg.granted_memory_kb, mg.session_id, t.text, qp.query_plan
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY 1 DESC OPTION (MAXDOP 1)

--waits grouped together as a percentage of all waits on the system, in decreasing order

WITH Waits AS
    (SELECT
        wait_type,
        wait_time_ms / 1000.0 AS WaitS,
        (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
        signal_wait_time_ms / 1000.0 AS SignalS,
        waiting_tasks_count AS WaitCount,
        100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
        ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT IN (
        'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
        'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
        'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
        'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
        'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
        'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE',
        'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES',
        'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK')
    )
SELECT
    W1.wait_type AS WaitType,
    CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
    CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
    CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
    W1.WaitCount AS WaitCount,
    CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage,
    CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S,
    CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S,
    CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_S
FROM Waits AS W1
    INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold
GO

*/