Simple script to change all databases to 'muti user'

DECLARE mode_change CURSOR
FOR

SELECT [name]
FROM sys.databases
WHERE user_access_desc = 'SINGLE_USER'

DECLARE @dbname VARCHAR (200)
DECLARE @sql VARCHAR (MAX)

OPEN mode_change
FETCH NEXT FROM mode_change INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN

SET @sql = 'ALTER DATABASE ['+@dbname+'] SET MULTI_USER'

PRINT (@sql)

FETCH NEXT FROM mode_change INTO @dbname

END



CLOSE mode_change
DEALLOCATE mode_change

Examining the procedure cache for queries and execution plans

This query below gives the list of cached plans and their corresponding SQL statements. The usecount in this case is greater than one but it can be changed depending on the need. On clicking the hyper link in the query_plan column, SSMS will display the execution plan. The query skips system databases along with Distribution and RS databases

Note: The cache gets cleared when SQL Server is re-started or when DBCC FREEPROCCACHE is run. This will reset the procedure cahe


SELECT DB_NAME(dmqp.[dbid]) AS [database] , dmep.usecounts , dmep.size_in_bytes/1024 AS size_in_KB , dmep.cacheobjtype , dmep.objtype , dmst.text , dmqp.query_plan FROM sys.dm_exec_cached_plans dmep CROSS APPLY sys.dm_exec_query_plan(plan_handle) dmqp CROSS APPLY sys.dm_exec_sql_text (plan_handle) dmst WHERE dmqp.[dbid] > 4 AND DB_NAME(dmqp.dbid) NOT IN ('distribution', 'reportserver', 'reportservertempdb') AND dmep.usecounts > 1 ORDER BY dmep.objtype

SELECT DB_NAME(dmqp.[dbid]) AS [database]
, dmep.usecounts
, dmep.size_in_bytes/1024 AS size_in_KB
, dmep.cacheobjtype
, dmep.objtype
, dmst.text
, dmqp.query_plan
FROM sys.dm_exec_cached_plans dmep
CROSS APPLY sys.dm_exec_query_plan(plan_handle) dmqp
CROSS APPLY sys.dm_exec_sql_text (plan_handle) dmst
WHERE dmqp.[dbid] > 4
AND DB_NAME(dmqp.dbid) NOT IN ('distribution', 'reportserver', 'reportservertempdb')
AND dmep.usecounts > 1
ORDER BY dmep.objtype

Average Space Used in Pages

This script will give the average space used in percentage for indexes and heaps. The lesser the space used is, the more overheard in reading them from the disks. SQL Server will read pages that are partially empty and thus more round trips would be needed to the disk sub system

By re-building the indexes with a higher fill factor, the I/O system reads can be reduced. This is especially true for tables whose data gets added sequentially as fragmentation will not be much of a concern in such tables

SELECT DB_NAME(database_id) AS database_name
, so.[name] AS table_name
, dmv.index_id
, si.[name]
, dmv.index_type_desc
, dmv.alloc_unit_type_desc
, dmv.index_depth
, dmv.index_level
, avg_fragmentation_in_percent
, avg_page_space_used_in_percent
, page_count

FROM sys.dm_db_index_physical_stats (DB_ID(DB_NAME()), NULL, NULL, NULL, 'DETAILED') dmv
INNER JOIN sysobjects so
ON dmv.[object_id] = so.id
INNER JOIN sys.indexes si
ON
dmv.object_id = si.object_id
AND dmv.index_id = si.index_id
ORDER BY table_name, si.name

Look for the 'avg_page_space_used_in_percent' column in the above query's output