SansSQL

Saturday, June 24, 2017

T-SQL to find Memory Used by Database and its Objects

When a data page is read from disk, the page is copied into the SQL Server buffer pool and cached for reuse. Each cached data page has one buffer descriptor. Buffer descriptors uniquely identify each data page that is currently cached in an instance of SQL Server and we use this for calculating the memory used by the Databases and its objects.

-- Memory Used by All Databases
SELECT 
 CAST (COUNT(1)*8/1024 AS decimal(8,2)) AS [MemoryUsedByAllDB's(MB)]
,CAST (COUNT(1)*8/1024/1024 AS decimal(8,2)) AS [MemoryUsedByAllDB's(GB)]
FROM sys.dm_os_buffer_descriptors
GO

-- Memory Used by Individual Databases
SELECT
 CASE WHEN database_id = 32767 
      THEN 'ResourceDB' 
      ELSE DB_NAME(database_id) 
 END AS [DatabaseName]
,COUNT(1) AS [CachedPages]
,CAST (COUNT(1)*8/1024 AS decimal(8,2)) AS [MemoryUsed(MB)]
,CAST(COUNT(1)*8/1024/1024 AS decimal(8,2)) AS [MemoryUsed(GB)]
,CASE WHEN is_modified = 1 
      THEN 'Dirty Page' 
      ELSE 'Clean Page' 
 END AS [PageState]
FROM
sys.dm_os_buffer_descriptors
GROUP BY database_id
	,is_modified
ORDER BY CachedPages DESC
	,DB_NAME(database_id)
GO

-- Memory Used by Objects within a Database. Change the <<database name>> to your desiged Database Name
USE <<database name>>
GO
SELECT
 CASE WHEN database_id = 32767 
      THEN 'ResourceDB' 
      ELSE DB_NAME(database_id) 
 END AS [DatabaseName],
 InnerData.ObjectName,
 InnerData.ObjectType,
 InnerData.IndexName,
 InnerData.IndexType
,COUNT(1) AS [CachedPages]
,CAST (COUNT(1)*8/1024 AS decimal(8,2)) AS [MemoryUsed(MB)]
,CAST(COUNT(1)*8/1024/1024 AS decimal(8,2)) AS [MemoryUsed(GB)]
,CASE WHEN is_modified = 1 
      THEN 'Dirty Page' 
      ELSE 'Clean Page' 
 END AS [PageState]
FROM
sys.dm_os_buffer_descriptors SBD INNER JOIN 
(
SELECT
SAU.allocation_unit_id, 
SO.name AS [ObjectName],
SO.type_desc AS [ObjectType],
SI.name AS [IndexName],
SI.type_desc AS [IndexType]
FROM sys.allocation_units SAU INNER JOIN sys.partitions SP
ON SAU.container_id = SP.partition_id INNER JOIN sys.objects SO
ON SP.object_id = SO.object_id INNER JOIN sys.indexes SI
ON SO.object_id = SI.object_id
WHERE SO.is_ms_shipped=0
) AS InnerData
ON SBD.allocation_unit_id = InnerData.allocation_unit_id
WHERE DB_NAME(SBD.database_id) = DB_NAME()
GROUP BY database_id
	,is_modified
	,InnerData.ObjectName
	,InnerData.ObjectType
	,InnerData.IndexName
	,InnerData.IndexType
ORDER BY CachedPages DESC
	,InnerData.ObjectName
GO

Related Posts Widget For Blogger with ThumbnailsBlogger Templates

Monday, May 29, 2017

Find Isolation Level of transaction and Database

Find Isolation level of a transaction
SELECT  session_id AS SessionID, 
  program_name AS ProgramName,
  DB_NAME(database_id) AS DatabaseName, 
  CASE transaction_isolation_level 
   WHEN 0 THEN 'Unspecified' 
   WHEN 1 THEN 'ReadUncommitted' 
   WHEN 2 THEN 'ReadCommitted' 
   WHEN 3 THEN 'Repeatable' 
   WHEN 4 THEN 'Serializable' 
   WHEN 5 THEN 'Snapshot' 
  END AS Transaction_Isolation_Level
FROM sys.dm_exec_sessions

Changing the Transaction Isolation level
Using the below statement the isolation level of a transaction can be changed.

Isolation Level
  • Read uncommitted
  • Read committed
  • Repeatable read
  • Serializable
  • Read committed snapshot
  • Snapshot
SET TRANSACTION ISOLATION LEVEL <<Isolation Level>>

Find Isolation level of a Database
USE <<Database Name>>
GO
DECLARE @UserOptions TABLE ([Set Option] NVARCHAR(50), [Value] NVARCHAR(50))
INSERT INTO @UserOptions Execute ('DBCC USEROPTIONS')
SELECT * FROM @UserOptions WHERE [Set Option] = 'isolation level'

You can download the script from here.

Thursday, April 13, 2017

T-SQL to List all Permissions for a Given Role

This script lists all the permissions for a given role.

SELECT DB_NAME() AS DatabaseName
      ,DatabasePrincipals.name AS PrincipalName
      ,DatabasePrincipals.type_desc AS PrincipalType
      ,DatabasePrincipals2.name AS GrantedBy
      ,DatabasePermissions.permission_name AS Permission
      ,DatabasePermissions.state_desc AS StateDescription
      ,SCHEMA_NAME(SO.schema_id) AS SchemaName
      ,SO.Name AS ObjectName
      ,SO.type_desc AS ObjectType
  FROM sys.database_permissions DatabasePermissions LEFT JOIN sys.objects SO
    ON DatabasePermissions.major_id = so.object_id LEFT JOIN sys.database_principals DatabasePrincipals
    ON DatabasePermissions.grantee_principal_id = DatabasePrincipals.principal_id LEFT JOIN sys.database_principals DatabasePrincipals2
    ON DatabasePermissions.grantor_principal_id = DatabasePrincipals2.principal_id
WHERE DatabasePrincipals.name = 'Test' -- Change the Role Name

You can download the script from here.

Thursday, April 6, 2017

T-SQL to get Get Database Files Usage information

Here is an handy T-SQL script to get Database files usage Information
DECLARE @FreeSpaceThreshold int
SET @FreeSpaceThreshold = 20 -- Change this Threshold Value. Any number between 0 to 100

CREATE TABLE #GetDetails
( 
 ServerName nvarchar(100),
 DatabaseName nvarchar(100),
 LogicalFileName nvarchar(100),
 FileType nvarchar(10),
 FileLocation nvarchar(max),
 [FileSize(MB)] decimal (15,2),
 [UsedSpace(MB)] decimal (15,2),
 [FreeSpace(MB)] decimal (15,2),
 [UsedSpace(%)] decimal (15,2),
 [FreeSpace(%)] decimal (15,2)
 )

EXEC sp_MSforeachdb 'Use [?];
INSERT INTO #GetDetails
SELECT @@SERVERNAME AS ServerName,
    DB_NAME() AS DatabaseName,
    name AS LogicalFileName, 
    type_desc AS FileType, 
    physical_name AS FileLocation,
    CAST(size/128.0 AS decimal(15,2)) AS [FileSize(MB)],
    CAST(FILEPROPERTY(name, ''SpaceUsed'')/128.0 AS decimal(15,2)) AS [UsedSpace(MB)],
    CAST(size/128.0 AS decimal(15,2)) - CAST(FILEPROPERTY(name, ''SpaceUsed'')/128.0 AS decimal(15,2)) AS [FreeSpace(MB)],
    CAST(((FILEPROPERTY(name, ''SpaceUsed'')/128.0 ) /  (size/128.0))*100 AS decimal(15,2)) AS [UsedSpace(%)],
    CAST((((size/128.0) - FILEPROPERTY(name, ''SpaceUsed'')/128.0 ) /  (size/128.0))*100 AS decimal(15,2)) AS [FreeSpace(%)]
FROM sys.database_files'

SELECT * FROM #GetDetails Where [FreeSpace(%)] < @FreeSpaceThreshold
DROP TABLE #GetDetails

Download this script from https://gallery.technet.microsoft.com/T-SQL-to-Get-Database-f16f60c7?redir=0