ronascentes

USE master
GO
DECLARE @memoryNeeded INT = 16
SELECT percentNeeded = @memoryNeeded / 
		ROUND(((committed_target_kb/1024)/1024) * 
		(CASE  
			WHEN (committed_target_kb/1024)/1024 <= 8 THEN 0.7
			WHEN (committed_target_kb/1024)/1024 <= 16 THEN 0.75
			WHEN (committed_target_kb/1024)/1024 <= 32 THEN 0.8
			WHEN (committed_target_kb/1024)/1024 <= 96 THEN 0.85

ronascentes

SELECT CONVERT(xml, event_data).query('/event/data/value/child::*') as xml1,
       CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]','datetime') as Execution_Time
into #temp
FROM sys.fn_xe_file_target_read_file('system_health*.xel', null, null, null)
WHERE object_name like 'xml_deadlock_report'

select Row_Number() OVER(ORDER BY execution_time desc) AS RowNumber,*  into #temp2 from #temp

ronascentes

SELECT N'Perfmon' as [Category], [object_name],counter_name, cntr_value
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Memory Manager%' 
AND (counter_name LIKE N'Total Server Memory (KB)%' OR counter_name LIKE N'Target Server Memory (KB)%' 
		OR counter_name = N'Memory Grants Outstanding' OR counter_name = N'Memory Grants Pending')
OR ([object_name] LIKE N'%Buffer Manager%' AND counter_name = N'Page life expectancy')
OR ([object_name] LIKE N'%General Statistics%' AND counter_name = N'Transactions')
OR ([object_name] LIKE N'%General Statistics%' AND counter_name = N'User Connections')
OR ([object_name] LIKE N'%SQL Statistics%' AND counter_name = N'Batch Requests/sec')

ronascentes

USE [master]
GO
ALTER DATABASE [<db_name>] SET QUERY_STORE = ON
GO
ALTER DATABASE [<db_name>] SET QUERY_STORE (
OPERATION_MODE = READ_WRITE, 
DATA_FLUSH_INTERVAL_SECONDS = 3600, 
MAX_STORAGE_SIZE_MB = 5120,
INTERVAL_LENGTH_MINUTES = 60,
SIZE_BASED_CLEANUP_MODE = AUTO,

ronascentes

-- Got from https://blogs.msdn.microsoft.com/sql_pfe_blog/2009/06/11/three-usage-scenarios-for-sys-dm_db_index_operational_stats/

SELECT      TOP 10
            OBJECT_NAME(object_id, database_id) object_nm,
            index_id,
            partition_number,
            leaf_allocation_count,
            nonleaf_allocation_count
FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL)
ORDER BY leaf_allocation_count DESC

ronascentes

-- get from https://blogs.msdn.microsoft.com/sql_pfe_blog/2009/06/11/three-usage-scenarios-for-sys-dm_db_index_operational_stats
SELECT      TOP 3
            OBJECT_NAME(object_id, database_id) object_nm,
            index_id,
            partition_number,
            index_lock_promotion_attempt_count,
            index_lock_promotion_count
FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL)
ORDER BY index_lock_promotion_count DESC

ronascentes

-- https://blogs.msdn.microsoft.com/sql_pfe_blog/2009/06/11/three-usage-scenarios-for-sys-dm_db_index_operational_stats
SELECT      TOP 3
            OBJECT_NAME(o.object_id, o.database_id) object_nm,
            o.index_id,
            partition_number,
            page_lock_wait_count,
            page_lock_wait_in_ms,
            case when mid.database_id is null then 'N' else 'Y' end as missing_index_identified
FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) o
LEFT OUTER JOIN (SELECT DISTINCT database_id, object_id

ronascentes

USE master
GO
ALTER DATABASE [ASPState] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [ASPState] MODIFY NAME = ASPState_NEW
GO
ALTER DATABASE ASPState_NEW SET MULTI_USER 
GO

ronascentes

# Import the SQL Server Module.    
Import-Module Sqlps -DisableNameChecking;

# To check whether the module is installed.
Get-Module -ListAvailable -Name Sqlps;

ronascentes

#2014 SP2:
D:\Temp\SQLServer2014SP2-KB3171021-x64-ENU.exe /qs /IAcceptSQLServerLicenseTerms /Action=Patch /AllInstances

#2014 CU3:
D:\Temp\SQLServer2014-KB3204388-x64.exe /qs /IAcceptSQLServerLicenseTerms /Action=Patch /AllInstances