MS SQL Maintenance

Below is a list of practical SQL snippets to administer Microsoft databases:

-- Shrink Database:
-- WARNING: this is resource intensive and will cause index fragmentation as a tradeoff
DBCC SHRINKDATABASE
( database_name | database_id | 0
[ , target_percent ]
[ , { NOTRUNCATE | TRUNCATEONLY } ]
)
[ WITH NO_INFOMSGS ]

-- Example
DBCC SHRINKDATABASE(Test_CRM, 1);

-- Reorganize index:
ALTER INDEX { index_name | ALL }
ON <object>
{ REBUILD
[ PARTITION = ALL ]
[ WITH ( <rebuild_index_option> [ ,...n ] ) ]
| [ PARTITION = partition_number
[ WITH ( <single_partition_rebuild_index_option> ) [ ,...n ] ]
]
| DISABLE
| REORGANIZE
[ PARTITION = partition_number ]
[ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
| SET ( <set_index_option> [ ,...n ] )
}
[ ; ]

<object> ::=
{
[ database_name. [ schema_name ] . | schema_name. ]
table_or_view_name
}

<rebuild_index_option > ::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = { ON | OFF }
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| STATISTICS_INCREMENTAL = { ON | OFF }
| ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| MAXDOP = max_degree_of_parallelism
| DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
[ ON PARTITIONS ( { <partition_number_expression> | <range> }
[ , ...n ] ) ]
}
<range> ::=
<partition_number_expression> TO <partition_number_expression>

<single_partition_rebuild_index_option> ::=
{
SORT_IN_TEMPDB = { ON | OFF }
| MAXDOP = max_degree_of_parallelism
| DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE |
COLUMNSTORE_ARCHIVE} }
| ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
}

<set_index_option>::=
{
ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
}

<low_priority_lock_wait>::=
{
WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,
ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

--DB Integrity Check:
DBCC CHECKDB
[
[ ( database_name | database_id | 0
[ , NOINDEX
| , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
) ]
[ WITH
{
[ ALL_ERRORMSGS ]
[ , EXTENDED_LOGICAL_CHECKS ]
[ , NO_INFOMSGS ]
[ , TABLOCK ]
[ , ESTIMATEONLY ]
[ , { PHYSICAL_ONLY | DATA_PURITY } ]
}
]
]

-- Update Statistics:
UPDATE STATISTICS table_or_indexed_view_name
[
{
{ index_or_statistics__name }
| ( { index_or_statistics_name } [ ,...n ] )
}
]
[ WITH
[
FULLSCAN
| SAMPLE number { PERCENT | ROWS }
| RESAMPLE
[ ON PARTITIONS ( { <partition_number> | <range> } [, …n] ) ]
| <update_stats_stream_option> [ ,...n ]
]
[ [ , ] [ ALL | COLUMNS | INDEX ]
[ [ , ] NORECOMPUTE ]
[ [ , ] INCREMENTAL = { ON | OFF } ]
] ;

<update_stats_stream_option> ::=
[ STATS_STREAM = stats_stream ]
[ ROWCOUNT = numeric_constant ]
[ PAGECOUNT = numeric_contant ]

-- Verify maintenance plan schedules:

USE msdb
GO
SELECT s.name,
		CASE freq_type
		WHEN 1
			THEN 'Once'
		WHEN 4
			THEN 'Daily'
		WHEN 8
			THEN 'Weekly'
		WHEN 16
			THEN 'Monthly'
		WHEN 32
			THEN 'Monthly relative'
		WHEN 64
			THEN 'When SQL Server Agent starts'
		END 'freq_type',
	CASE s.freq_type
		WHEN 4
			THEN 'DAILY'
		WHEN 8
			THEN CASE s.freq_interval
					WHEN 1
						THEN ' on Sunday'
					WHEN 2
						THEN ' on Monday'
					WHEN 4
						THEN ' on Tuesday'
					WHEN 8
						THEN ' on Wednesday'
					WHEN 16
						THEN ' on Thursday'
					WHEN 32
						THEN ' on Friday'
					WHEN 64
						THEN ' on Saturday'
					END
		WHEN 16
			THEN CASE s.freq_interval
					WHEN 1
						THEN convert(VARCHAR, s.freq_interval) + 'st day of the month'
					WHEN 2
						THEN convert(VARCHAR, s.freq_interval) + 'nd day of the month'
					WHEN 3
						THEN convert(VARCHAR, s.freq_interval) + 'rd day of the month'
					ELSE convert(VARCHAR, s.freq_interval) + 'th day of the month'
					END
		WHEN 32
			THEN CASE s.freq_interval
					WHEN 1
						THEN 'Sunday'
					WHEN 2
						THEN 'Monday'
					WHEN 3
						THEN 'Tuesday'
					WHEN 4
						THEN 'Wednesday'
					WHEN 5
						THEN 'Thursday'
					WHEN 6
						THEN 'Friday'
					WHEN 7
						THEN 'Saturday'
					WHEN 8
						THEN 'Day'
					WHEN 9
						THEN 'Weekday'
					WHEN 10
						THEN 'Weekend day'
					END
		END 'days',
	left(right('0' + cast(active_start_time AS VARCHAR), 6), 2) + ':' + substring(right('0' + cast(active_start_time AS VARCHAR), 6), 3, 2) + ':' + right(cast(active_start_time AS VARCHAR), 2) 'Start Time',
	left(right('0' + cast(active_end_time AS VARCHAR), 6), 2) + ':' + substring(right('0' + cast(active_end_time AS VARCHAR), 6), 3, 2) + ':' + right(cast(active_end_time AS VARCHAR), 2) 'End Time'
FROM sysschedules s
ORDER BY name

-- Find waiting workflows for CRM:

SELECT DISTINCT asyncoperationid
,name
,statecodename
,statuscodename
,createdbyname
,createdon
,createdonutc
,modifiedbyname
,modifiedon
,modifiedonutc
,startedon
,startedonutc
,messagename
,operationtypename
,regardingobjectidname
,owneridname
FROM FilteredAsyncOperation
WHERE statuscodename = 'Waiting'

Find any column or table name per database:

SELECT COLUMN_NAME
	,TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS a
WHERE (COLUMN_NAME LIKE '%sent%')
ORDER BY TABLE_NAME
	,COLUMN_NAME
	
Check active user count for all databases at once:

DECLARE @command varchar(1000) 
SELECT @command = 'USE ? select OrganizationIdName, count(systemuserid) usercount from systemuser where IsDisabled = 0 group by OrganizationIdName' 
EXEC sp_MSforeachdb @command

-- List all SSRS reports:

USE [ReportServer];-- You may change the database name. 
GO

SELECT USR.UserName AS SubscriptionOwner
	,SUB.ModifiedDate
	,SUB.[Description]
	,SUB.EventType
	,SUB.DeliveryExtension
	,SUB.LastStatus
	,SUB.LastRunTime
	,SCH.NextRunTime
	,SCH.Name AS ScheduleName
	,CAT.[Path] AS ReportPath
	,CAT.[Description] AS ReportDescription
FROM dbo.Subscriptions AS SUB
INNER JOIN dbo.Users AS USR ON SUB.OwnerID = USR.UserID
INNER JOIN dbo.[Catalog] AS CAT ON SUB.Report_OID = CAT.ItemID
INNER JOIN dbo.ReportSchedule AS RS ON SUB.Report_OID = RS.ReportID
	AND SUB.SubscriptionID = RS.SubscriptionID
INNER JOIN dbo.Schedule AS SCH ON RS.ScheduleID = SCH.ScheduleID
ORDER BY USR.UserName
	,CAT.[Path];

-- Check database sizes:

SELECT d.NAME
	,ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
	,(SUM(mf.size) * 8 / 1024) / 1024 AS Size_GBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.name LIKE '%_MSCRM'
GROUP BY d.NAME
ORDER BY d.NAME

-- Find table heaps per database:

SELECT TOP 1000 o.name
	,i.type_desc
	,o.type_desc
	,o.create_date
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE o.type_desc = 'USER_TABLE'
	AND i.type_desc = 'HEAP'
ORDER BY o.name
GO

Check index  fragmentation per database:

SELECT dbschemas.[name] AS 'Schema'
	,dbtables.[name] AS 'Table'
	,dbindexes.[name] AS 'Index'
	,indexstats.avg_fragmentation_in_percent
	,indexstats.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
	AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent DESC

-- Check buffer cache usage for all databases:

WITH CTE_BUFFER_CACHE
AS (
	SELECT databases.name AS database_name
		,COUNT(*) AS total_number_of_used_pages
		,CAST(COUNT(*) * 8 AS DECIMAL) / 1024 AS buffer_cache_total_MB
		,CAST(CAST(SUM(CAST(dm_os_buffer_descriptors.free_space_in_bytes AS BIGINT)) AS DECIMAL) / (1024 * 1024) AS DECIMAL(20, 2)) AS buffer_cache_free_space_in_MB
	FROM sys.dm_os_buffer_descriptors
	INNER JOIN sys.databases ON databases.database_id = dm_os_buffer_descriptors.database_id
	GROUP BY databases.name
	)
SELECT *
	,CAST((buffer_cache_free_space_in_MB / NULLIF(buffer_cache_total_MB, 0)) * 100 AS DECIMAL(5, 2)) AS buffer_cache_percent_free_space
FROM CTE_BUFFER_CACHE
ORDER BY buffer_cache_free_space_in_MB / NULLIF(buffer_cache_total_MB, 0) DESC
Check buffer cache usage for all databases - clean vs dirty pages:

SELECT databases.name AS database_name
	,COUNT(*) AS buffer_cache_total_pages
	,SUM(CASE 
			WHEN dm_os_buffer_descriptors.is_modified = 1
				THEN 1
			ELSE 0
			END) AS buffer_cache_dirty_pages
	,SUM(CASE 
			WHEN dm_os_buffer_descriptors.is_modified = 1
				THEN 0
			ELSE 1
			END) AS buffer_cache_clean_pages
	,SUM(CASE 
			WHEN dm_os_buffer_descriptors.is_modified = 1
				THEN 1
			ELSE 0
			END) * 8 / 1024 AS buffer_cache_dirty_page_MB
	,SUM(CASE 
			WHEN dm_os_buffer_descriptors.is_modified = 1
				THEN 0
			ELSE 1
			END) * 8 / 1024 AS buffer_cache_clean_page_MB
FROM sys.dm_os_buffer_descriptors
INNER JOIN sys.databases ON dm_os_buffer_descriptors.database_id = databases.database_id
GROUP BY databases.name;

-- Check physical and virtual memory in use:

SELECT physical_memory_kb
	,virtual_memory_kb
	,committed_kb
	,committed_target_kb
FROM sys.dm_os_sys_info;

-- Check buffer cache usage per database by index:

SELECT indexes.name AS index_name
	,objects.name AS object_name
	,objects.type_desc AS object_type_description
	,COUNT(*) AS buffer_cache_pages
	,COUNT(*) * 8 / 1024 AS buffer_cache_used_MB
FROM sys.dm_os_buffer_descriptors
INNER JOIN sys.allocation_units ON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_id
INNER JOIN sys.partitions ON (
		(
			allocation_units.container_id = partitions.hobt_id
			AND type IN (
				1
				,3
				)
			)
		OR (
			allocation_units.container_id = partitions.partition_id
			AND type IN (2)
			)
		)
INNER JOIN sys.objects ON partitions.object_id = objects.object_id
INNER JOIN sys.indexes ON objects.object_id = indexes.object_id
	AND partitions.index_id = indexes.index_id
WHERE allocation_units.type IN (
		1
		,2
		,3
		)
	AND objects.is_ms_shipped = 0
	AND dm_os_buffer_descriptors.database_id = DB_ID()
GROUP BY indexes.name
	,objects.name
	,objects.type_desc
ORDER BY COUNT(*) DESC;
Check buffer cache usage for a database by table including page percentages:

WITH CTE_BUFFER_CACHE
AS (
	SELECT objects.name AS object_name
		,objects.type_desc AS object_type_description
		,objects.object_id
		,COUNT(*) AS buffer_cache_pages
		,COUNT(*) * 8 / 1024 AS buffer_cache_used_MB
	FROM sys.dm_os_buffer_descriptors
	INNER JOIN sys.allocation_units ON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_id
	INNER JOIN sys.partitions ON (
			(
				allocation_units.container_id = partitions.hobt_id
				AND type IN (
					1
					,3
					)
				)
			OR (
				allocation_units.container_id = partitions.partition_id
				AND type IN (2)
				)
			)
	INNER JOIN sys.objects ON partitions.object_id = objects.object_id
	WHERE allocation_units.type IN (
			1
			,2
			,3
			)
		AND objects.is_ms_shipped = 0
		AND dm_os_buffer_descriptors.database_id = DB_ID()
	GROUP BY objects.name
		,objects.type_desc
		,objects.object_id
	)
SELECT PARTITION_STATS.name
	,CTE_BUFFER_CACHE.object_type_description
	,CTE_BUFFER_CACHE.buffer_cache_pages
	,CTE_BUFFER_CACHE.buffer_cache_used_MB
	,PARTITION_STATS.total_number_of_used_pages
	,PARTITION_STATS.total_number_of_used_pages * 8 / 1024 AS total_mb_used_by_object
	,CAST((CAST(CTE_BUFFER_CACHE.buffer_cache_pages AS DECIMAL) / CAST(PARTITION_STATS.total_number_of_used_pages AS DECIMAL) * 100) AS DECIMAL(5, 2)) AS percent_of_pages_in_memory
FROM CTE_BUFFER_CACHE
INNER JOIN (
	SELECT objects.name
		,objects.object_id
		,SUM(used_page_count) AS total_number_of_used_pages
	FROM sys.dm_db_partition_stats
	INNER JOIN sys.objects ON objects.object_id = dm_db_partition_stats.object_id
	WHERE objects.is_ms_shipped = 0
	GROUP BY objects.name
		,objects.object_id
	) PARTITION_STATS ON PARTITION_STATS.object_id = CTE_BUFFER_CACHE.object_id
ORDER BY CAST(CTE_BUFFER_CACHE.buffer_cache_pages AS DECIMAL) / CAST(PARTITION_STATS.total_number_of_used_pages AS DECIMAL) DESC;

-- Check buffer cache usage for a database by table including index percentages:

SELECT indexes.name AS index_name
	,objects.name AS object_name
	,objects.type_desc AS object_type_description
	,COUNT(*) AS buffer_cache_pages
	,COUNT(*) * 8 / 1024 AS buffer_cache_used_MB
	,SUM(allocation_units.used_pages) AS pages_in_index
	,SUM(allocation_units.used_pages) * 8 / 1024 AS total_index_size_MB
	,CAST((CAST(COUNT(*) AS DECIMAL) / CAST(SUM(allocation_units.used_pages) AS DECIMAL) * 100) AS DECIMAL(5, 2)) AS percent_of_pages_in_memory
FROM sys.dm_os_buffer_descriptors
INNER JOIN sys.allocation_units ON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_id
INNER JOIN sys.partitions ON (
		(
			allocation_units.container_id = partitions.hobt_id
			AND type IN (
				1
				,3
				)
			)
		OR (
			allocation_units.container_id = partitions.partition_id
			AND type IN (2)
			)
		)
INNER JOIN sys.objects ON partitions.object_id = objects.object_id
INNER JOIN sys.indexes ON objects.object_id = indexes.object_id
	AND partitions.index_id = indexes.index_id
WHERE allocation_units.type IN (
		1
		,2
		,3
		)
	AND objects.is_ms_shipped = 0
	AND dm_os_buffer_descriptors.database_id = DB_ID()
GROUP BY indexes.name
	,objects.name
	,objects.type_desc
ORDER BY CAST((CAST(COUNT(*) AS DECIMAL) / CAST(SUM(allocation_units.used_pages) AS DECIMAL) * 100) AS DECIMAL(5, 2)) DESC;

-- Check buffer cache usage per database:

SELECT objects.name AS object_name
	,objects.type_desc AS object_type_description
	,COUNT(*) AS buffer_cache_pages
	,COUNT(*) * 8 / 1024 AS buffer_cache_used_MB
FROM sys.dm_os_buffer_descriptors
INNER JOIN sys.allocation_units ON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_id
INNER JOIN sys.partitions ON (
		(
			allocation_units.container_id = partitions.hobt_id
			AND type IN (
				1
				,3
				)
			)
		OR (
			allocation_units.container_id = partitions.partition_id
			AND type IN (2)
			)
		)
INNER JOIN sys.objects ON partitions.object_id = objects.object_id
WHERE allocation_units.type IN (
		1
		,2
		,3
		)
	AND objects.is_ms_shipped = 0
	AND dm_os_buffer_descriptors.database_id = DB_ID()
GROUP BY objects.name
	,objects.type_desc
ORDER BY COUNT(*) DESC;

-- Check buffer cache usage for all databases:

SELECT databases.name AS database_name
	,COUNT(*) * 8 / 1024 AS mb_used
FROM sys.dm_os_buffer_descriptors
INNER JOIN sys.databases ON databases.database_id = dm_os_buffer_descriptors.database_id
GROUP BY databases.name
ORDER BY COUNT(*) DESC;

Categories:

Database

Use PowerShell to Grant SysAdmin Role to Certain Users

$principle=$env:USERDOMAIN+'\Domain Admins' $sqlServer=$env:computername function includeSqlTools{ $ErrorActionPreference='stop' try{ $trustedPsgallery=(Get-PSRepository PSGallery).InstallationPolicy -eq 'Trusted' if(!$trustedPsgallery){ Set-PSRepository -Name PSGallery…

T-SQL: How To Rename Database in Microsoft SQL Server

The T-SQL USE master; GO ALTER DATABASE TEST_MSCRM SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO ALTER…

Leave a Reply Cancel reply

Search

Archives

Meta