Being a DBA, it is good to have some kind of automated health check report that gets delivered to inbox with quick glance of DB health. Hence this script will be very useful. Copy this script and schedule as a SQL agent job. Provide the mail profile and recipient addresses. The script was built with help from several colleagues and references from different SQL forums.
Continue reading to view and copy the script. Since the script is too long, it could not be posted in homepage.
Continue reading “SQL Server 2012 Daily Health Check”
There are several ways to configure and start monitoring page splits on SQL Server. But now in 2012, we could make use of extended events to easily track down page splits.
If you do not want to go through the session wizard, I am sharing the script with which you could create the session in one go.
CREATE EVENT SESSION [SherPageSplit] ON SERVER
ADD EVENT sqlserver.page_split(ACTION(sqlserver.client_hostname,sqlserver.client_pid,sqlserver.context_info,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.session_id,sqlserver.sql_text,sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'M:\Program Files\Microsoft SQL Server\MSSQL11.INST01\MSSQL\Log\SherPageSplit.xel',max_file_size=(100),max_rollover_files=(3))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
Continue reading “Monitor Page splits using Extended Events session in SQL Server 2012”