How to monitor replication subscription errors

 One of my client has a big setup Server for SQL Server. He also has a lot of transactional replication and monitoring its is a really problem for him. Frequently he has problem of out of sync replications or failed replication

DMVs for Memory

Today we will look into DMVs related to memory

First we will look into sys.dm_os_sys_memory which will give us memory related basic informatio for the system
This will give us detail for physical memory, available memory , toatl page file and available page file and
high/low memory status. If  memory status is low then it means system is facing memory pressure.


SELECT total_physical_memory_kb / ( 1024.0 * 1024 )     total_physical_memory_gb,
       available_physical_memory_kb / ( 1024.0 * 1024 ) available_physical_memory_gb,
       total_page_file_kb / ( 1024.0 * 1024 )           total_page_file_gb,
       available_page_file_kb / ( 1024.0 * 1024 )       available_page_file_gb,
       system_high_memory_signal_state,
       system_low_memory_signal_state,
       system_memory_state_desc
FROM   sys.dm_os_sys_memory





dm_os_process_memory
This dmv is supprted on SQL 2008 and later versions
it provides some  more detailed information about memory in sql server
All details are in kb

SELECT physical_memory_in_use_kb,
       large_page_allocations_kb,
       locked_page_allocations_kb,
       total_virtual_address_space_kb,
       virtual_address_space_reserved_kb,
       virtual_address_space_committed_kb,
       virtual_address_space_available_kb,
       page_fault_count,
       memory_utilization_percentage,
       available_commit_limit_kb,
       process_physical_memory_low,
       process_virtual_memory_low
FROM   sys.dm_os_process_memory







This will give us physical memory , locked page allocation (should be 0 if AWE is not enabled) ,
total , reserved, committed and available virtual address space, page fault count , memory utilization percentage and process physical memory low if the server is facing memory pressure

xp_readerrorlog parameter detail

Recently we had  hardware and performance issues on SQL Server,with our client , so while monitoring the system we also looked at error log. We used xp_readerrorlog procedure.
This is really useful procedure and I feel I should share some information for this undocumented store procedures



use procedure as input in dataflow task

If we use procedure as  source in data flow task , this generally dont work in SSIS
so we have to pass
SET nocount OFF
SET fmtonly OFF

 before running the procedure


So sql command will be like this

SET nocount OFF
SET fmtonly OFF

EXEC Proc1



cheers :-)

Server details using DMV

Sometimes we need basic server  details , we can get this information from below DMV

SELECT *
FROM   sys.dm_os_sys_info





It provides valuable miscellaneous  hardware information which includes  number of logical cpu and hyperthread ratio for servers. Also provides information for physical memory , virtual memory  , buffer pool , sql server start time . In one of my previous article I have discussed how to get SQL server start time from tempdb
But from here we can get more accurate detail about sql server start time .


SELECT cpu_count                                         logical_cpu,
       hyperthread_ratio,
       cpu_count / hyperthread_ratio                     physcial_cpu,
       physical_memory_in_bytes / ( 1024 * 1024 * 1024 ) memory_in_gb,
       virtual_memory_in_bytes / ( 1024 * 1024 * 1024 )  virtual_memory_in_gb,
       bpool_committed                                   buffer_pool_committed,
       bpool_commit_target                               buffer_pool_commit_target,
       bpool_visible                                     buffer_pool_visible,
       sqlserver_start_time
FROM   sys.dm_os_sys_info





Popular Posts