SQL auto stats on or off

SELECT name AS 'Name', 

    is_auto_create_stats_on AS "Auto Create Stats",

    is_auto_update_stats_on AS "Auto Update Stats",

    is_read_only AS "Read Only" 

FROM sys.databases

WHERE database_ID > 4;

ALTER DATABASE YourDBName SET AUTO_CREATE_STATISTICS ON

get adhoc sql load in sql server

select objtype,p.size_in_bytes,sql.text
 from
sys.dm_exec_cached_plans p
outer apply sys.dm_exec_sql_text( p.plan_handle) sql

SQL Server 2016 New Features

• Real-Time Operational Analytics
• 
  
• In-Memory Optimization (OLTP) enhancements
• 
  
• Stretch Database
• 
  
• Always Encrypted
• 
  
• Query Data Store and Live Query Statistics ( Simile to Oracle Diagnostic Pack)
• 
  
• PolyBase  ( BigData query layer )
• 
  
• Row-Level Security and Dynamic Data Masking
• 
  
• High availability and disaster recovery using Microsoft Azure Virtual Machines

details coming sooooooooon

Manually determining the number of cores on your computer

You can use the Windows Management Instrumentation Command-line tool (WMIC) to determine how many physical cores your server has. This is useful if you do not know whether your computer will meet the minimum hardware requirements for installing Tableau Server.

1. Open a command prompt.
2. Enter the following command:
   WMIC CPU Get DeviceID,NumberOfCores
   The output will display the device id or ids and the number of physical cores the computer has:
   
   In the above example there are two CPUs, each with six cores, for a total of twelve physical cores.
   A longer command will list the logical processors as well as the physical cores:
   WMIC CPU Get DeviceID,NumberOfCores,NumberOfLogicalProcessors,SocketDesignation
   
   In the above example, in addition to the twelve physical cores, there are 24 logical cores. ( hyperthreading * physical cores e.g 2* 12) 

Calculate memory need for SQL Server

SQL Max Memory = TotalPhyMem - (NumOfSQLThreads * ThreadStackSize) - (1GB * CEILING(NumOfCores/4)) - OS Reserved

NumOfSQLThreads = 256 + (NumOfProcessors*- 4) * 8 (* If NumOfProcessors > 4, else 0)
ThreadStackSize = 2MB on x64 or 4 MB on 64-bit (IA64)
OS Reserved = 20% of total ram for under if system has 15GB. 12.5% for over 20GB

All database users and their roles associated

 -- all the database users and their roles

SELECT  members.name, roles.name,roles.type_desc,members.type_desc
FROM sys.database_role_members rolemem
INNER JOIN sys.database_principals roles
ON rolemem.role_principal_id = roles.principal_id
INNER JOIN sys.database_principals members
ON rolemem.member_principal_id = members.principal_id
ORDER BY members.name


 ---- Check SQL Server Audit level
 DECLARE @AuditLevel int
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
   N'Software\Microsoft\MSSQLServer\MSSQLServer',
   N'AuditLevel', @AuditLevel OUTPUT
SELECT CASE WHEN @AuditLevel = 0 THEN 'None'
   WHEN @AuditLevel = 1 THEN 'Successful logins only'
   WHEN @AuditLevel = 2 THEN 'Failed logins only'
   WHEN @AuditLevel = 3 THEN 'Both failed and successful logins'
   END AS [AuditLevel]
 

 ---Find failed login events in SQL Server error log
 
   EXEC master.dbo.xp_readerrorlog 0, 1, 'login failed', null, NULL, NULL, N'desc'
 
-- show all options
 
   EXEC sp_configure 'Show Advanced Options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure;

Resource governer

Pool:
 A resource pool, or pool, is a collection of system resources such as memory or CPU; it represents a portion of the physical resources of the server.
Depending on its settings, a pool may have a fixed size (its minimum and maximum resource usage settings are equal to each other) or have a part which is shared between multiple pools
(its minimum is less than its effective maximum). "Shared" in this case simply means that resources go to the pool that requests the resources first. In the default configuration all
resources are shared, thus maintaining backward compatibility with SQL Server 2005 policies. Two resource pools (internal and default) are created when SQL Server 2008 is installed.
Resource Governor also supports 18 user-defined resource pools. You specify MIN and MAX values for resources (CPU or Memory) which represents the minimum guaranteed resource availability
of the pool and the maximum size of the pool, respectively. The sum of MIN values across all pools cannot exceed 100 percent of the server resources. MAX value can be set anywhere in the
range between MIN and 100 percent inclusive. The internal pool represents the resources consumed by the SQL Server itself. This pool always contains only the internal group,
and the pool is not alterable in any way. Resource consumption by the internal pool is not restricted. Any workloads in the pool are considered critical for server function, and Resource Governor allows the internal pool to pressure other pools even if it means the violation of limits set for the other pools. The default pool is the first predefined user pool. Prior to any configuration the default pool only contains the default group. The default pool cannot be created or dropped but it can be altered. The default pool can contain user-defined groups in addition to the default group.

Group:
 A workload group, or group, is a user-specified category of requests that are similar according to the classification rules that are applied to each session request. A group defines the policies for its members. A resource pool is assigned to a Workload Group, which is in turn is assigned to the Resource Governor. Two workload groups (internal and default) are created and mapped to their corresponding resource pools when SQL Server 2008 is installed, apart from that the Resource Governor also supports user-defined workload groups. The internal workload group is populated with requests that are for internal SQL Server use only. You cannot change the criteria used for routing these requests and you cannot classify requests into the internal workload group whereas requests are mapped to default workload group, if there is a classification failure, an attempt to map to a non-existent workload group and there is no criteria to classify. If the Resource Governor is disabled, all new connections are automatically classified into the default group and System-initiated requests are classified into the internal workload group.

Classification:
Classification is a set of user-written rules that enable Resource Governor to classify session requests into the workload groups as described previously; for example classifying on the basis of user, application etc. It is implemented through a scalar Transact-SQL user-defined function (UDF) which is designated as a "classifier UDF" for the Resource Governor in the master database. Only one user-defined function can be designated as a classifier at a time

First I will create two resource pools to be used by OLTP and Reporting application,
then I will create two workload groups which will categorize the request coming from these applications.

Working with Resource Governor 

--Resource pool to be used by OLTP Application
CREATE RESOURCE POOL OLTPPool
WITH
(
 MIN_CPU_PERCENT=50, MAX_CPU_PERCENT=100,
 MIN_MEMORY_PERCENT=50, MAX_MEMORY_PERCENT=100
)
GO
--Resource pool to be used by Report Application
CREATE RESOURCE POOL ReportPool
WITH
(
 MIN_CPU_PERCENT=50, MAX_CPU_PERCENT=100,
 MIN_MEMORY_PERCENT=50, MAX_MEMORY_PERCENT=100
)
GO
--Workload Group to be used by OLTP Application
CREATE WORKLOAD GROUP OLTPGroup
    USING OLTPPool ;
GO
--Workload Group to be used by Report Application
CREATE WORKLOAD GROUP ReportGroup
    USING ReportPool ;
GO  


Next I will create the classifier UDF to route incoming request to different workload groups and finally I will enable Resource Governor with ALTER RESOURCE GOVERNOR RECONFIGURE statement.

Assumption here is, the OLTP application uses "OLTPUser" login whereas reporting application uses "ReportUser" login.

 USE master;
GO
CREATE FUNCTION dbo.ResourceClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
 --Declare the variable to hold the value returned in sysname.
 DECLARE @WorkloadGroup AS SYSNAME
 --If the user login is 'OLTPUser', map the connection to the
 --OLTPGroup workload group.
 IF (SUSER_NAME() = 'OLTPUser')
  SET @WorkloadGroup = 'OLTPGroup'
 --If the user login is 'ReportUser', map the connection to
 --the ReportGroup workload group.
 ELSE IF (SUSER_NAME() = 'ReportUser')
  SET @WorkloadGroup = 'ReportGroup'
 ELSE
  SET @WorkloadGroup = 'default'
 RETURN @WorkloadGroup
END
GO
--Register the classifier user-defined function and update the
--the in-memory configuration.
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION=dbo.ResourceClassifier);
GO
--Enabling Resource Governor(By default when you install
--SQL Server, Resource Governor is disabled)
--It loads the stored configuration metadata into memory
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
--Disabling Resource Governor
ALTER RESOURCE GOVERNOR DISABLE
GO
--It resets statistics on all workload groups and resource pools.
ALTER RESOURCE GOVERNOR RESET STATISTICS
GO


Resource Governor's Catalog Views and Dynamic Management Views

There are three new Catalog Views and three new Dynamic Management Views introduced for Resource Governor.

sys.resource_governor_configuration - used to display the Resource Governor configuration as stored in metadata.

sys.resource_governor_resource_pools - used to display resource pool configuration as stored in metadata.

sys.resource_governor_workload_groups - used to display workload group configuration as stored in metadata.

sys.dm_resource_governor_configuration - used to get the current in-memory configuration state of Resource Governor

sys.dm_resource_governor_resource_pools - used to get the current resource pool state, the current configuration of resource pools, and resource pool statistics.

sys.dm_resource_governor_workload_groups - used to get the workload group statistics and the current in-memory configuration of the workload group.