SQL Server: Instant Blocking Alert Using WMI in Your Mailbox

As a DBA, it is important for all of us to get real time alert on various issues in our inbox to be more proactive and to manage the database servers in better way.There are many third party tools which you can easily configure to monitor the servers and to get the alerts. I feel more happy when we implement the required alert by our self to get the alert. we have implemented many custom alerts in our environment. I will share the important ones in this blog.

There are many method that you can adopt to implement custom alert. I have done this using WMI (Windows Management Instrumentation) .WMI is very powerful one to monitor the performance of the servers and application.

Blocking will happen when one process need  to wait for a resource(example lock) which currently using by another process. The first process has to wait till  the second process completes its action and release the resources.

Let us see how we can set up the alert for blocking. The first step to create blocking alert is configure the blocked process threshold values.This is server wide configuration which can be set using sp_configure.The value of blocked process threshold specify the threshold, in seconds,at which blocked process reports are generated.By default no blocked process reports are produces.In simple words, if the value configured as 30 second, we will get alert when a process is blocked for more than 30 seconds. This can be done using the below code.

EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'blocked process threshold (s)',30
GO
RECONFIGURE WITH OVERRIDE
Another configuration change that we need to do in the SQL server agent. Right click on the agent node , select properties. On the left pane select Alert System. On bottom of that page tick the check box of Replace tokens for all job responses to alert. Read more about the this on MSDN.Restart the SQL server agent service.

The next step is create a simple table in one database.In all our environment we have a database to implement the administrative tasks. This table contains only four fields .One identity column, a date column to store date and time of blocking,a Xml column to store the blocked processed report and a integer column to store the blocking process spid. The script to create this table is available here

The next step is to create a new job.The specialty of this job is, it does not have a schedule.Let us create a job namely DBA_BlockedAlert. In the step add the second part of the script mentioned in here

The final step is to create an alert . Refer below screenshot to create the alert. Mention an appropriate name for the alert. Select alert type as WMI event alert.In the name space add

\\.\root\Microsoft\SqlServer\ServerEvents\INSTANCENAME . 
Replace the instance name with your instance name. 
In the query section add SELECT * FROM BLOCKED_PROCESS_REPORT

In the response page tick the Execute job check box and select the job that we have created earlier.

Now everything is set. Create a blocking and wait for 30+ seconds to get the alert in your mail box. In case if your are not receiving the alert follow below point to troubleshoot.

• Check alert history and see number of occurrences. If it is still zero either blocking is not passed 30 seconds or there is some problem with WMI. Check your WMI service status. if it is running , restart the WMI service. 
• If the number of occurrence is greater than zero, check the table BlockedEvents. If there is no entries, the token replacement may not be happening. Check the   Replace tokens for all job responses to alert of SQL server agent properties. Make sure that you have restarted the SQL Server agent service after making this change.
• If there is an entry in the BlockedEvents table, there is something wrong with your database mail configuration /mailbox. Check the database mail is working and you have mentioned the correct profile name in the job step.

Hope this will help you to implement custom blocking alert in your environment.

If you liked this post, do like my page on FaceBook 

SQL SERVER : SQL Server Scheduler

While troubleshooting the performance issues of SQL server, you might have seen the sessions are in different state like SUSPENDED,RUNNING,RUNNABLE etc. In this post let us try to explain this by going through the threading model of SQL server.

SQL server is like a operating system like windows.It has its own mechanism  to handle the memory management, IO and thread scheduling. SQL server might be using the windows API to do these task but still SQL server control how it works for it. 

Thread

 Thread is the lightweight process or it is part of process.For example running simple select statement can be considered as a process and it might  run in the processor as a single thread. Where as a complicated select statement (process) which returns thousands of rows might split into multiple threads which will run on different processor to complete the process much faster.Each thread will get a small amount of time in the processor,then it move out of the processor to give processor time to other thread and it has to wait for the processor time to complete it pending task. The process of moving threads to the processor and moving out of the processor is called as scheduling. This will give an impression that many process are happening but in reality only only one thread can be executed in the processor at any point of time.

SQL Server uses operating system thread to perform its action and it is called as worker thread.There are dedicated thread and pool of threads. There are dedicated thread for check point , dead lock monitoring,etc. The pool of thread is used to process the user request.

Thread Scheduling 

As mentioned earlier, SQL server controls its thread scheduling and it use non-preemptive scheduling  and windows can not interrupt these threads.When SQL server managing the scheduling of its thread instead operating system, it  has more control and it make  its own priority.The thread scheduling inside the SQL server done by the SQLOS which is an interface between the SQL server and operating system.Each processor core (logical or physical) which allowed to use by SQL server has a scheduler.For example in a machine with two physical core, with hyper threading enabled, will have four scheduler.There are scheduler for user threads and for internal operation.For some processor can have two scheduler one for user request and other one for internal for database engine. The list of schedulers can be seen by querying the DMV sys.dm_os_schedulers. The offline schedulers are mapped to the processors that are offline in the affinity mask and not being used to process any request. Visible schedules are used to process the user request where as hidden schedulers are used to process the internal request. There is one dedicated scheduler to process the DAC requests.When one of thread needs something from OS(calling a windows API) , that thread has to switch to preemptive mode which enable OS to control when that thread goes to sleep or any other state of the thread.

Scheduler

A schedule has three important component, Processor,Waiter List and Runnable queue. A Processor is the actual processor which process the thread one at a time.Waiter List is the list of threads waiting for resources.Runnable Queue is a queue of threads which has all the resource it need to process but waiting for its turn to get into the processor.Scheduler put the thread in to Runnable Queue then move the thread to Processor and migrate to Waiter List. Each thread keeps going through these three component until the thread works completes. 


The waiter list is a list of threads which are suspended and waiting for a resource. This is not a queue as there is no order in which the thread will get the resource.There is no parameter which define the maximum time a thread can be in the waiter list. Theoretically there is no limit but the timeout specified in the query execution session may take effect. While waiting in the Water List , the thread might get canceled due to the execution time out.The Waiter List can be examined by querying the DMV sys.dm_os_waiting_tasks.


The Runnable queue is a pure First-In-First-Out (FIFO) queue.When a thread moves from Waiter List it joins at the bottom of the Runnable queue. We can see the size of runnable queue by looking into the column of runnable_tasks_count column in sys.dm_os_schedules.There is special case when resource governor enabled and relative priorities assigned to multiple workload group for a resource pool.Possible values for priorities are High,Medium and Low which equate to 9,3 and 1. It means that 9 high priority thread and 3 Medium priority thread can override a low priority thread in the Runnable queue.

State of threads

A thread can have three states , Running ,Suspended and Runnable. Running is the state where the thread is currently in the Processor and utilizing the CPU. Only one thread per scheduler can have this state as the Processor can process only one thread at a time.When a thread need a  resource to process further it has to wait for the resource.The thread will be move to Waiter List and thread changes the state from Running to Suspended.When required resources are available after a period of time, nothing is stopping the thread from running.But eventually it has to wait for its turn in the Processor. The process that tells the thread that resources are available is called signaling. When resources are available for a thread (thread is signaled) which was in suspended mode it will move to the Runnable queue and wait for its turn with a state called Runnable.When the thread moves to processor it change the state from Runnable  to Running.The transition between these states keep happening till the thread completes its work.
There is case where thread by pass the Suspended state and directly move to the Runnable from Running and this is called quantum exhaustion.If a thread does not need to wait for any resources, it will continue to run till its quantum is exhausted. The quantum is fixed to 4ms and not configurable. The last column of the DMV sys.dm_os_schedulers define this value. Even if the thread does not need to wait for any resources,after the completion of its quantum time, it will move out of the processor and its state change from Running to Runnable. The thread move directly from Processor to bottom of Runnable queue bypassing the waiter list as it does not need to wait for a resource.

Below script shows the relationship between various DMV

SELECT
   [dot].[scheduler_id],
   [task_state],
   COUNT (*) AS [task_count]FROMsys.dm_os_schedulers dos INNER JOIN  sys.dm_os_workers  dow ON dos.scheduler_address=dow.scheduler_addressINNER JOIN  sys.dm_os_tasks AS [dot] ON dot.task_address=dow.task_addressINNER JOIN  sys.dm_exec_requests AS [der]    ON [dot].[session_id] = [der].[session_id]INNER JOIN  sys.dm_exec_sessions AS [des]    ON [der].[session_id] = [des].[session_id]WHERE [des].[is_user_process] = 1 GROUP BY
   [dot].[scheduler_id],
   [task_state]ORDER BY [task_state],[dot].[scheduler_id]





If you liked this post, do like my page on FaceBook 




  
  
  
  

SQL SERVER:How Can I Get Notification Alert When Fail Over Happened ?

It was long time I was searching for simple solution to get notification alert in my inbox when ever fail over happened in the cluster environment. SCOM will help us to do that , but I do not have direct control over it and the mail first goes to the Infrastructure support team and they forward the alert to the respective team.

Let us see how  DBA's can schedule an email alert with out depending on the SCOM or any other third party tool. When  fail over happen, both SQL server and Agent service will get restarted.In sql server job scheduler, there is an option to schedule a job when SQL server agent starts. For that we have to select the schedule type as "Start Automatically when sql server agent start".

To get an alert when a fail over happened, create a job with following code in the job step

DECLARE @importance AS VARCHAR(6)
DECLARE @body AS NVARCHAR(1000)
DECLARE @Subject  AS NVARCHAR(1000)
DECLARE @InstanceName AS VARCHAR(100)DECLARE @NodeName AS NVARCHAR(100)
DECLARE @recipientsList VARCHAR(100)SELECT  @recipientsList ='abc@yahoo.com,xyz@gmail.com'
SELECT  @InstanceName =@@SERVERNAME
SELECT  @NodeName = CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS NVARCHAR(200))
SELECT  @Subject  = 'Fail over Happened for Instance '+@InstanceName
SELECT  @body = 'Fail over Happened for Instance '+@InstanceName + '. This instance is currently running on the node '+@NodeName
SELECT  @importance ='High' 
EXEC msdb.dbo.sp_send_dbmail
     @profile_name ='MyMailProfile',
     @recipients=@recipientsList,
     @subject = @subject ,
     @body = @body,
     @body_format = 'HTML' ,
     @importance=@importance


In the schedule pane select schedule Type as "Start Automatically when sql server agent start".

Do this on all instances of the cluster and it is ready . Wait for next fail over. Mail will be there in your inbox.Please be careful that you will get a mail even if the instance got restarted in the same instance or you just stopped and started the agent service. But that can be easily ignored or can be avoid by tweaking the above code little bit.

If you liked this post, do like my page on FaceBook at http://www.facebook.com/practicalSqlDba

SQL SERVER : The Scene Behind NOLOCK

I have heard many time people talking about the NOLOCK hint. Some of them says it improves the performance and some of them says it reduce the blocking and deadlock as it will not acquire any locks. Let us see what is happening when we use NOCLOCK table hint in queries.

Let us see a query with out NOLOCK table hint and analyse the lock acquired by that query.

SELECT *FROM Sales.SalesOrderHeader a CROSS JOIN Sales.SalesOrderHeader b

The lock acquired by this session can be easily found using the below query

SELECT ResourceName = CASE resource_type
            WHEN 'database' THEN DB_NAME(resource_database_id)
            WHEN 'object' THEN OBJECT_NAME(resource_associated_entity_id, resource_database_id)
            ELSE ''
            END,request_mode,request_status, * FROM sys.dm_tran_locksWHERE request_session_id = 53




While analyzing the result, we can see a shared lock on database level. That is nothing to do with the NOLOCK  hint. While opening a connection to the database, it will always take shared connection on database used by that connection to make sure that other sessions will not drop the database while in use.

Next we can see an Intent Shared(IS) lock on the table level.An intent lock indicates that SQL Server wants to acquire a shared (S) lock or exclusive (X) lock on some of the resources lower down in the hierarchy. For example, a shared intent lock placed at the table level means that a transaction intends on placing shared (S) locks on pages or rows within that table. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive (X) lock on the table containing that page.This IS lock also make sure that this table will not be modified till the select statement complete the operation.Next you an see a shared lock on page. This is to make sure that the data in the page are not getting modified while reading the data.

Let us see how it will work along with an update statement.

BEGIN TRAN

UPDATE Sales.SalesOrderHeader SET status=5 WHERE SalesOrderID=43659

Now  run the select statement and lock analyzing script. We can see that the request for Share Lock on page  is in Wait status as it is conflicting with the Intent exclusive lock acquired by the update session on the page. This helps sql server to avoid the dirty read but it cause for blocking. You can see that the select statement is  blocked by the update statement.

Let us see the same with NOLOCK hint

SELECT *

FROM Sales.SalesOrderHeader a WITH (NOLOCK)
CROSS JOIN Sales.SalesOrderHeader b WITH (NOLOCK)  
In this case we can see only shared schema lock on the table. It is not taking a shared lock on the page and this lead to a dirty read.The shared schema lock om table level is important to make to sure that the schema of the table is not getting changed while reading the data. Let us try this select statement after the update statement and still the select statement will run without blocking as it is not trying to acquire the shared lock on pages and it also cause for the dirty read.

The READ UNCOMMITTED isolation level also works in the same way. Instead of specifying the table hint for each table we can set the isolation level to READ UNCOMMITTED. Theoretically NOLOCK hint improve the performance slightly as it  need to acquires less lock compared with statement does not have a NOLOCK hint.Be careful with the usage of NOLOCK hint as it perform dirty read and may give undesired result.

If you liked this post, do like my page on FaceBook at http://www.facebook.com/practicalSqlDba