ronascentes’s gists

ronascentes / update_stats.sql

Created November 11, 2016 18:44

Update statistics

	EXEC SP_CONFIGURE

	EXEC SP_CONFIGURE 'max degree of parallelism', 8
	RECONFIGURE WITH OVERRIDE

	EXEC sp_updatestats @resample = 'resample'

	EXEC SP_CONFIGURE 'max degree of parallelism', 6
	RECONFIGURE WITH OVERRIDE

ronascentes / check_stats_fullscan_candidate.sql

Created November 21, 2016 17:57

Check statistics with less than 25% of sample and may require a full scan

	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	SELECT DISTINCT 'UPDATE STATISTICS ' + t.name + '.' + OBJECT_NAME(mst.[object_id]) + ' ' + ss.name + ' WITH FULLSCAN'
	FROM sys.objects AS o
	INNER JOIN sys.tables AS mst ON mst.[object_id] = o.[object_id]
	INNER JOIN sys.schemas AS t ON t.[schema_id] = mst.[schema_id]
	INNER JOIN sys.stats AS ss ON ss.[object_id] = mst.[object_id]
	CROSS APPLY sys.dm_db_stats_properties(ss.[object_id], ss.[stats_id]) AS sp
	WHERE sp.[rows] > 0
	AND CAST((sp.rows_sampled/(sp.[rows]1.00))100.0 AS DECIMAL(5,2)) < 25

ronascentes / run_sqlserverpatch.cmd

Created March 17, 2017 13:43

Run SQL Server patch from command line

	#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

ronascentes / import-sqlps.ps1

Created April 25, 2017 18:58

Import SQLPS Module

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

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

ronascentes / rename_db.sql

Last active October 31, 2017 18:01

Rename database

	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 / lock_waits.sql

Created October 31, 2017 17:18

Identify the top 3 objects associated with waits on page locks

	-- 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 / Lock_escalations.sql

Created October 31, 2017 17:20

Track how many attempts were made to escalate to table locks (index_lock_promotion_attempt_count), as well as how many times escalations actually succeeded (index_lock_promotion_count).

	-- 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 / page_split_tracking.sql

Last active October 31, 2017 17:24

Excessive page splitting can have a significant effect on performance. The following query identifies the top 10 objects involved with page splits (ordering by leaf_allocation_count and referencing both the leaf_allocation_count and nonleaf_allocation_count columns). The leaf_allocation_count column represents page splits at the leaf and the non…

	-- 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 / enable_query_store.sql

Last active March 17, 2020 17:22

Query Store Best Practices

	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 / perfmon.sql

Created November 24, 2017 18:12

	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')

Rodrigo Nascentes ronascentes