SQL Server Internal Memory Notifications

Below Query helps you to read the notifications that SQL Server has generated internally.



SELECT CONVERT (varchar(30), GETDATE(), 121) as runtime,

DATEADD (ms, -1 * (sys.ms_ticks - a.[Record Time]), GETDATE()) AS Notification_time,

a.* , sys.ms_ticks AS [Current Time]

FROM

(SELECT x.value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') AS [Notification_type],

x.value('(//Record/MemoryRecord/MemoryUtilization)[1]', 'bigint') AS [MemoryUtilization %],

x.value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS [TotalPhysicalMemory_KB],

x.value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS [AvailablePhysicalMemory_KB],

x.value('(//Record/MemoryRecord/TotalPageFile)[1]', 'bigint') AS [TotalPageFile_KB],

x.value('(//Record/MemoryRecord/AvailablePageFile)[1]', 'bigint') AS [AvailablePageFile_KB],

x.value('(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS [TotalVirtualAddressSpace_KB],

x.value('(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS [AvailableVirtualAddressSpace_KB],

x.value('(//Record/MemoryNode/@id)[1]', 'bigint') AS [Node Id],

x.value('(//Record/MemoryNode/ReservedMemory)[1]', 'bigint') AS [SQL_ReservedMemory_KB],

x.value('(//Record/MemoryNode/CommittedMemory)[1]', 'bigint') AS [SQL_CommittedMemory_KB],

x.value('(//Record/@id)[1]', 'bigint') AS [Record Id],

x.value('(//Record/@type)[1]', 'varchar(30)') AS [Type],

x.value('(//Record/ResourceMonitor/Indicators)[1]', 'bigint') AS [Indicators],

x.value('(//Record/@time)[1]', 'bigint') AS [Record Time]

FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers

WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR') AS R(x)) a

CROSS JOIN sys.dm_os_sys_info sys

ORDER BY a.[Record Time] ASC



...Happy SQLing / Thanks for Visiting and Sharing your Views

Mechanism to Seperate Indexes from Actual Data pages

This is helpful while planning to Seperate Indexes from Actual data pages and moving Indexes to a New data File on to a New Disk.

1) Create New Filegroup for Indexes "Index_FG"
2) Create New Secondary Data file pointing to a new or existing disk and assign the .ndf file to the new Filegroup
3) Identify the Indexes that are residing on the Primary FileGroup (Most of the cases Data pages reside on the Primary FileGroup by Default)

SELECT DB_name() as DB,OBJECT_NAME(k.id) AS TableName,i.name AS IndexName,c.name AS ColumnName, i.reserved,i.groupid, groupname,c.colid,k.indid AS IndexIDFROM sys.sysindexes ijoin sys.sysfilegroups g ON i.groupid = g.groupidjoin sys.sysindexkeys k ON i.id = k.idand k.indid = i.indidjoin sys.syscolumns c ON k.id = c.idand k.colid = c.colidwhere i.groupid NOT IN ('5', '3')ORDER BY OBJECT_NAME(k.id),i.name, i.indid, k.keyno

4) Identify the Tables and their FileGroups

select OBJECT_NAME(object_id) as ObjectName, d.name AS FileGroup from sys.data_spaces d left join sys.indexes i on i.data_space_id = d.data_space_idwhere i.index_id<2
/**** The above query returns all the system tables as well. If you want only user tables then use below *****/
SELECT OBJECT_NAME(t.object_id) AS ObjectName, d.name AS FileGroup FROM sys.data_spaces d JOIN sys.indexes i on i.data_space_id = d.data_space_idJOIN sys.tables t on t.object_id = i.object_idWHERE i.index_id<2AND t.type = 'U'


5) Find the Indexes and thier Size. may be in some cases you may only need to Seperate the Large sized Indexes. use below Query to find the Indexes and their Size

SELECT .name AS IndexName, SUM(s.used_page_count) * 8 AS IndexSizeKB FROM sys.dm_db_partition_stats AS s JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id WHERE s.[object_id] = object_id('dbo.Clientinvoice') GROUP BY i.name ORDER BY i.name

6) Rebuild the Indexes by Pointing to the New FileGroup.


...Happy SQLing
Thanks for Visiting and Sharing your Views

Re-Building the Database Log File

WHAT IF THE LDF GET DELETED OR CORRUPTED….

If  the log file get deleted or corrupted in sql server 2000 we can rebuild the log file with

SQL

DBCC REBUILD LOG

But in sql server 2005 this DBCC command is not there. So we should follow series of steps

When the log file gets corrupted we should check the status of the database. It can be done through following sql statement

select state_desc,* from sys.databases where name='LogDB'

//here LogDB is the database name

The status of the database would be “RECOVERY PENDING”

Then we should set the database in Emergency mode followed by single user mode with the following sql statement

ALTER database LogDB set EMERGENCY

ALTER database LogDB set single_user

Then we should run a DBCC command that allows the Loss of Data

DBCC CHECKDB(LogDB,REPAIR_ALLOW_DATA_LOSS)

DBCC CHECKDB(LogDB)

Then the last step is to bring it online through  making the database multi user

ALTER database LogDB set multi_user

...Happy SQLing
Thanks for Visiting and Sharing your Views

SQL Server Memory usage Statistics

It is always important to know how much memory is being consumed by SQL processes and Windows Processes. Below set of queries will help you pull the memory Usage Statistics


DECLARE
 SELECT
 SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio'
PRINT '----------------------------------------------------------------------------------------------------'
PRINT 'Memory usage details for SQL Server instance ' + @@SERVERNAME + ' (' + CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - ' + SUBSTRING(@@VERSION, CHARINDEX('X',@@VERSION),4) + ' - ' + CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'
PRINT '----------------------------------------------------------------------------------------------------'
SELECT 'Memory Configuration on the Server visible to Operating System'
SELECT physical_memory_in_bytes/1048576.0 as [Physical Memory_MB], physical_memory_in_bytes/1073741824.0 as [Physical Memory_GB], virtual_memory_in_bytes/1048576.0 as [Virtual Memory MB] FROM sys.dm_os_sys_info
SELECT 'Buffer Pool Usage at the Moment'
SELECT (bpool_committed*8)/1024.0 as BPool_Committed_MB, (bpool_commit_target*8)/1024.0 as BPool_Commit_Tgt_MB,(bpool_visible*8)/1024.0 as BPool_Visible_MB FROM sys.dm_os_sys_info
SELECT 'Total Memory used by SQL Server instance from Perf Mon '
SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM sys.dm_os_performance_counters WHERE counter_name = 'Total Server Memory (KB)'
SELECT 'Memory needed as per current Workload for SQL Server instance'
SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM sys.dm_os_performance_counters WHERE counter_name = 'Target Server Memory (KB)'
SELECT 'Total amount of dynamic memory the server is using for maintaining connections'
SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM sys.dm_os_performance_counters WHERE counter_name = 'Connection Memory (KB)'
SELECT 'Total amount of dynamic memory the server is using for locks'
SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM sys.dm_os_performance_counters WHERE counter_name = 'Lock Memory (KB)'
SELECT 'Total amount of dynamic memory the server is using for the dynamic SQL cache'
SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM sys.dm_os_performance_counters WHERE counter_name = 'SQL Cache Memory (KB)'
SELECT 'Total amount of dynamic memory the server is using for query optimization'
SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM sys.dm_os_performance_counters WHERE counter_name = 'Optimizer Memory (KB) '
SELECT 'Total amount of dynamic memory used for hash, sort and create index operations.'
SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM sys.dm_os_performance_counters WHERE counter_name = 'Granted Workspace Memory (KB) '
SELECT 'Total Amount of memory consumed by cursors'
SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM sys.dm_os_performance_counters WHERE counter_name = 'Cursor memory usage' and instance_name = '_Total'
SELECT 'Number of pages in the buffer pool (includes database, free, and stolen).'
SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM sys.dm_os_performance_counters WHERE object_name= @Instancename+'Buffer Manager' and counter_name = 'Total pages'
SELECT 'Number of Data pages in the buffer pool'
SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM sys.dm_os_performance_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Database pages'
SELECT 'Number of Free pages in the buffer pool'
SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM sys.dm_os_performance_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Free pages'
SELECT 'Number of Reserved pages in the buffer pool'
SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM sys.dm_os_performance_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Reserved pages'
SELECT 'Number of Stolen pages in the buffer pool'
SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM sys.dm_os_performance_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Stolen pages'
SELECT 'Number of Plan Cache pages in the buffer pool'
SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM sys.dm_os_performance_counters WHERE object_name=@Instancename+'Plan Cache' and counter_name = 'Cache Pages' and instance_name = '_Total'

...Happy SQLing
Thanks for Visiting and Sharing your Views @pg_size = low from master..spt_values where number = 1 and type = 'E' @pg_size INT, @Instancename varchar(50)