Tuesday, February 22, 2011

Control Focus Is Lost After AutoPostBack

When you press the TAB key on an ASP.NET Web Server control, the Web page is posted back to the Web server. After the operation is complete, the focus is not set to any control. You expect the focus to be set to the next control.

This problem occurs because the AutoPostBack property of the control is set to True.


This behavior is by design.


If the AutoPostBack property is set to True, postback occurs when you press ENTER or when the control loses the focus after the contents are modified.

To work around this behavior:
  1. In the HTML view of WebForm1.aspx, replace the existing code with the following:

    Visual C# .NET Code

    <%@ Page Language="C#" AutoEventWireup="false" Codebehind="WebForm1.aspx.cs" Inherits="WebApplication11.WebForm1"%>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
    <HTML>
       <body>
          <form id="Form1" method="post" runat="server">
      <asp:Label id="Label1" runat="server" style="LEFT: 10px; POSITION: absolute; TOP: 20px">Label</asp:Label>
      <asp:textbox id="TextBox1" runat="server" AutoPostBack="True" style="LEFT: 85px; POSITION: absolute; TOP: 23px"></asp:textbox>
      <asp:Label id="Label2" runat="server" style="LEFT: 11px; POSITION: absolute; TOP: 54px">Label</asp:Label><asp:textbox id="TextBox2" runat="server" AutoPostBack="True" style="LEFT: 85px; POSITION: absolute; TOP: 58px"></asp:textbox>
      <asp:Label id="Label3" runat="server" style="LEFT: 12px; POSITION: absolute; TOP: 95px">Label</asp:Label><asp:textbox id="TextBox3" runat="server" AutoPostBack="True" style="LEFT: 85px; POSITION: absolute; TOP: 96px"></asp:textbox>
      <asp:Button id="Button1" runat="server" Text="Button" style="LEFT: 85px; POSITION: absolute; TOP: 136px"></asp:Button>
            <script language="javascript">
       document.all["<%= Focus %>"].focus();
       if("<%= ControlType %>" == "TextArea")
        document.all["<%= Focus %>"].select();
      </script>
     </form>
       </body>
    </HTML>
    Visual Basic .NET Code

    <%@ Page Language="vb" AutoEventWireup="false" Codebehind="WebForm1.aspx.vb" Inherits="WebApplication11.WebForm1"%>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
    <HTML>
       <body>
          <form id="Form1" method="post" runat="server">
      <asp:Label id="Label1" runat="server" style="LEFT: 10px; POSITION: absolute; TOP: 20px">Label</asp:Label>
      <asp:textbox id="TextBox1" runat="server" AutoPostBack="True" style="LEFT: 85px; POSITION: absolute; TOP: 23px"></asp:textbox>
      <asp:Label id="Label2" runat="server" style="LEFT: 11px; POSITION: absolute; TOP: 54px">Label</asp:Label><asp:textbox id="TextBox2" runat="server" AutoPostBack="True" style="LEFT: 85px; POSITION: absolute; TOP: 58px"></asp:textbox>
      <asp:Label id="Label3" runat="server" style="LEFT: 12px; POSITION: absolute; TOP: 95px">Label</asp:Label><asp:textbox id="TextBox3" runat="server" AutoPostBack="True" style="LEFT: 85px; POSITION: absolute; TOP: 96px"></asp:textbox>
      <asp:Button id="Button1" runat="server" Text="Button" style="LEFT: 85px; POSITION: absolute; TOP: 136px"></asp:Button>
      <script language="javascript">
       document.all["<%= Focus %>"].focus();
       if("<%= ControlType %>" == "TextArea")
        document.all["<%= Focus %>"].select();
      </script>
          </form>
       </body>
    </HTML>
  2. In Solution Explorer, right-click WebForm1.aspx, and then click View Code.
  3. In the code-behind class, replace the existing code with the following:

    Visual C# .NET Code

    using System;
    using System.Collections;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Web;
    using System.Web.SessionState;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.HtmlControls;
    
    namespace WebApplication22
    {
     public class WebForm1 : System.Web.UI.Page
     {
      protected System.Web.UI.WebControls.Label Label1;
      protected System.Web.UI.WebControls.TextBox TextBox1;
      protected System.Web.UI.WebControls.Label Label2;
      protected System.Web.UI.WebControls.TextBox TextBox2;
      protected System.Web.UI.WebControls.Label Label3;
      protected System.Web.UI.WebControls.TextBox TextBox3;
      protected System.Web.UI.WebControls.Button Button1;
      protected string Focus;
      protected string ControlType;
    
      private void Page_Load(object sender, System.EventArgs e)
      {
       Focus = "TextBox1";
       ControlType = "TextArea";
      }
    
      #region Web Form Designer generated code
      override protected void OnInit(EventArgs e)
      {
       InitializeComponent();
       base.OnInit(e);
      }
      
      private void InitializeComponent()
      {    
       this.TextBox1.TextChanged += new System.EventHandler(this.TextBox1_TextChanged);
       this.TextBox2.TextChanged += new System.EventHandler(this.TextBox2_TextChanged);
       this.TextBox3.TextChanged += new System.EventHandler(this.TextBox3_TextChanged);
       this.Load += new System.EventHandler(this.Page_Load);
      }
      #endregion
    
      private void TextBox1_TextChanged(object sender, System.EventArgs e)
      {
       Focus = "TextBox2";
       ControlType = "TextArea";
      }
      
      private void TextBox2_TextChanged(object sender, System.EventArgs e)
      {
       Focus = "TextBox3";
       ControlType = "TextArea";
      }
      
      private void TextBox3_TextChanged(object sender, System.EventArgs e)
      {
       Focus = "Button1";
       ControlType = "Button";
      }
     }
    }
    Visual Basic .NET Code

    Public Class WebForm1
       Inherits System.Web.UI.Page
       Protected WithEvents Label1 As System.Web.UI.WebControls.Label
       Protected WithEvents TextBox1 As System.Web.UI.WebControls.TextBox
       Protected WithEvents Label2 As System.Web.UI.WebControls.Label
       Protected WithEvents TextBox2 As System.Web.UI.WebControls.TextBox
       Protected WithEvents Label3 As System.Web.UI.WebControls.Label
       Protected WithEvents TextBox3 As System.Web.UI.WebControls.TextBox
       Protected WithEvents Button1 As System.Web.UI.WebControls.Button
       Protected Focus As String
       Protected ControlType As String
    
    #Region " Web Form Designer Generated Code "
    
       'This call is required by the Web Form Designer.
       <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
    
       End Sub
    
       Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
          'CODEGEN: This method call is required by the Web Form Designer
          'Do not modify it using the code editor.
          InitializeComponent()
       End Sub
    
    #End Region
    
       Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
          Focus = "TextBox1"
          ControlType = "TextArea"
       End Sub
       Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
          Focus = "TextBox2"
          ControlType = "TextArea"
       End Sub
    
       Private Sub TextBox2_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox2.TextChanged
          Focus = "TextBox3"
          ControlType = "TextArea"
       End Sub
    
       Private Sub TextBox3_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox3.TextChanged
          Focus = "Button1"
          ControlType = "Button"
       End Sub
    End Class
  4. On the Build menu, click Start to view WebForm1.aspx in the browser.
  5. Type text in the text box, and then press the TAB key. The focus is set to the next control after the post back operation.

Steps to Reproduce the Behavior


  1. Create a new ASP.NET Web Application project. By default, WebForm1.aspx is created.
  2. In the HTML view of WebForm1.aspx, replace the existing code with the following code:

    Visual C# .NET Code

    <%@ Page Language="C#" AutoEventWireup="false" Codebehind="WebForm1.aspx.cs" Inherits="WebApplication11.WebForm1"%>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
    <HTML>
       <body>
          <form id="Form1" method="post" runat="server">
      <asp:Label id="Label1" runat="server" style="LEFT: 10px; POSITION: absolute; TOP: 20px">Label</asp:Label>
      <asp:textbox id="TextBox1" runat="server" AutoPostBack="True" style="LEFT: 85px; POSITION: absolute; TOP: 23px"></asp:textbox>
      <asp:Label id="Label2" runat="server" style="LEFT: 11px; POSITION: absolute; TOP: 54px">Label</asp:Label><asp:textbox id="TextBox2" runat="server" AutoPostBack="True" style="LEFT: 85px; POSITION: absolute; TOP: 58px"></asp:textbox>
      <asp:Label id="Label3" runat="server" style="LEFT: 12px; POSITION: absolute; TOP: 95px">Label</asp:Label><asp:textbox id="TextBox3" runat="server" AutoPostBack="True" style="LEFT: 85px; POSITION: absolute; TOP: 96px"></asp:textbox>
      <asp:Button id="Button1" runat="server" Text="Button" style="LEFT: 85px; POSITION: absolute; TOP: 136px"></asp:Button>
          </form>
       </body>
    </HTML>
    Visual Basic .NET Code

    <%@ Page Language="vb" AutoEventWireup="false" Codebehind="WebForm1.aspx.vb" Inherits="WebApplication11.WebForm1"%>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
    <HTML>
       <body>
          <form id="Form1" method="post" runat="server">
      <asp:Label id="Label1" runat="server" style="LEFT: 10px; POSITION: absolute; TOP: 20px">Label</asp:Label>
      <asp:textbox id="TextBox1" runat="server" AutoPostBack="True" style="LEFT: 85px; POSITION: absolute; TOP: 23px"></asp:textbox>
      <asp:Label id="Label2" runat="server" style="LEFT: 11px; POSITION: absolute; TOP: 54px">Label</asp:Label><asp:textbox id="TextBox2" runat="server" AutoPostBack="True" style="LEFT: 85px; POSITION: absolute; TOP: 58px"></asp:textbox>
      <asp:Label id="Label3" runat="server" style="LEFT: 12px; POSITION: absolute; TOP: 95px">Label</asp:Label><asp:textbox id="TextBox3" runat="server" AutoPostBack="True" style="LEFT: 85px; POSITION: absolute; TOP: 96px"></asp:textbox>
      <asp:Button id="Button1" runat="server" Text="Button" style="LEFT: 85px; POSITION: absolute; TOP: 136px"></asp:Button>
          </form>
       </body>
    </HTML>
  3. On the Debug menu, click Start to run the project. 
SOLUTION IN ASP.NET 3.5 SP1
--------------------------------
In the HTML view of DEFAULT.ASPX, replace the existing code with the following:

Visual C# .NET Code

<%@ Page Language="C#" AutoEventWireup="false" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
   
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:Label id="Label1" runat="server" style="LEFT: 10px; POSITION: absolute; TOP: 20px">Label</asp:Label>
  <asp:textbox id="TextBox1" runat="server" AutoPostBack="True" style="LEFT: 85px; POSITION: absolute; TOP: 23px"></asp:textbox>
  <asp:Label id="Label2" runat="server" style="LEFT: 11px; POSITION: absolute; TOP: 54px">Label</asp:Label>
        <asp:textbox id="TextBox2" runat="server" AutoPostBack="True" 
            style="LEFT: 85px; POSITION: absolute; TOP: 58px" 
            ontextchanged="TextBox2_TextChanged1"></asp:textbox>
  <asp:Label id="Label3" runat="server" style="LEFT: 12px; POSITION: absolute; TOP: 95px">Label</asp:Label><asp:textbox id="TextBox3" runat="server" AutoPostBack="True" style="LEFT: 85px; POSITION: absolute; TOP: 96px"></asp:textbox>
  <asp:Button id="Button1" runat="server" Text="Button" style="LEFT: 85px; POSITION: absolute; TOP: 136px"></asp:Button>
        
<script language="javascript">
   document.all["<%= Focus %>"].focus();
   if("<%= ControlType %>" == "TextArea")
    document.all["<%= Focus %>"].select();
  </script>

    </div>
    </form>
</body>
</html>
  1. In Solution Explorer, right-click DEFAULT.ASPX and then click View Code.
  2. In the code-behind class, replace the existing code with the following:

    Visual C# .NET Code 
  3. using System;
    using System.Configuration;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Xml.Linq;

    public partial class _Default : System.Web.UI.Page
    {
        //protected System.Web.UI.WebControls.Label Label1;
        //protected System.Web.UI.WebControls.TextBox TextBox1;
        //protected System.Web.UI.WebControls.Label Label2;
        //protected System.Web.UI.WebControls.TextBox TextBox2;
        //protected System.Web.UI.WebControls.Label Label3;
        //protected System.Web.UI.WebControls.TextBox TextBox3;
        //protected System.Web.UI.WebControls.Button Button1;
        protected string Focus;
        protected string ControlType;

            private void Page_Load(object sender, System.EventArgs e)
            {
              //  if (!IsPostBack)
               // {
                    Focus = "TextBox1";
                    ControlType = "TextArea";
                //}
            }

            #region Web Form Designer generated code
            override protected void OnInit(EventArgs e)
            {
                InitializeComponent();
                base.OnInit(e);
            }
           
            private void InitializeComponent()
            {   
                this.TextBox1.TextChanged += new System.EventHandler(this.TextBox1_TextChanged);
                this.TextBox2.TextChanged += new System.EventHandler(this.TextBox2_TextChanged);
                this.TextBox3.TextChanged += new System.EventHandler(this.TextBox3_TextChanged);
                this.Load += new System.EventHandler(this.Page_Load);
            }
            #endregion

            private void TextBox1_TextChanged(object sender, System.EventArgs e)
            {
                Response.Write("abc.d.");
                Focus = "TextBox2";
                ControlType = "TextArea";
                TextBox2.Focus();
                Response.Write("abc..");
            }
           
            private void TextBox2_TextChanged(object sender, System.EventArgs e)
            {
                Focus = "TextBox3";
                ControlType = "TextArea";
                TextBox3.Focus();
              
            }
           
            private void TextBox3_TextChanged(object sender, System.EventArgs e)
            {
                Focus = "Button1";
                ControlType = "Button";
                Button1.Focus();
           
            }
            protected void TextBox2_TextChanged1(object sender, EventArgs e)
            {

            }
    }
  4. On the Build menu, click Start to view WebForm1.aspx in the browser.
  5. Type text in the text box, and then press the TAB key. The focus is set to the next control after the post back operation.

For additional information,
visit the following Microsoft Developer Network (MSDN) Web site:
TextBox.AutoPostBack Property
http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.textbox.autopostback(vs.71).aspx (http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.textbox.autopostback(vs.71).aspx)


Tab order problem in asp.net

q1-At the time of load a page tab index is not maintain.
solution:-
then you can write one line in aspx.cs file's page load event.
TabcontainerName.ActiveTabIndex = 0


q2- how to focus on the edit control

replace of :-txtname.Focus();
if it is not working proper than use focus through  javascript.

<script type="text/javascript">

function dofocus()
{
document.getElementbyId(“<%txtname%>”).focus();
}

</script>


Tab Key in ASP.NET

To make your Web application more efficient and easier to use, you need to remove any difficulty in web form navigation that your user could experience. User interface and web site navigation must be logical, easy to use and if possible: already well-known from other applications. Of course, nobody wants to learn some new complicated and unnecessary things and crawl through a large help files. Users wants self-explanatory interface that they can start to use immediately.


One of common standards is using a keyboard TAB key to move focus forward (or Tab + Shift key to move focus back) between controls on form, instead of forcing your Web site visitor to use a mouse click for changing a focus. Of course, your visitor still CAN use a mouse to set focus in some text box, but many of them, and they are usually experienced users like to use a tab key to move focus, because it is faster and more efficient way.

Tab order in web form

So, you need to define a logical navigation through a web form using a Tab key. There is a TabIndex property of server controls. First control in form should get lowest TabIndex. With every next tab, focus will move to the control with next higher value of TabIndex. It is pretty simple to set tab order, you need only to care about few common problems:
Problem 1: When we run the page, we can see there is no control with focus when web page is loaded. Much better solution is to have a focus on the right place immediately after page is loaded. Research about how you can set a focus initially and other focus issues you can see in Focus in ASP.NET tutorial. One solution, which shows how you can set a focus to textbox named txtFirstName, when page is loaded, could be a code like this:

[VB.NET]

Page.RegisterStartupScript("SetInitialFocus", _
"<script>document.getElementById('" & txtFirstName.ClientID & _
"').focus();</script>")

[C#]

Page.RegisterStartupScript("SetInitialFocus", "<script>document.getElementById('" + txtFirstName.ClientID + "').focus();</script>");

Problem 2: Focus moves through a web form, but also goes to Web browser address bar, Google or Yahoo toolbar etc. It is more expected that focus should move only inside the web form, instead to goes to browser's address bar.
Let say the last focus in on the button named "btnOK" and when user press tab key, we want to move focus again to the first control, textbox "txtFirstName" and skip focus in browser toolbars, address bars and other distraction things. You can do it with next piece of code:

[VB.NET]
btnOK.Attributes.Add("onkeydown", _
"if(event.which || event.keyCode)" & _
"{if ((event.which == 9) || (event.keyCode == 9)) " & _
"{document.getElementById('" & txtFirstName.ClientID & _
"').focus();return false;}} else {return true}; ")


[C#]

btnOK.Attributes.Add("onkeydown", "if(event.which || event.keyCode)" +
"{if ((event.which == 9) || (event.keyCode == 9)) " +
"{document.getElementById('"+ txtFirstName.ClientID +
"').focus();return false;}} else {return true}; ");

You don't want to move a focus to specific control?

For some reason, you don't want to some control receive a focus. That could be a case if, for example you have an invisible text box or some search text box on top of the web page, but you want to allow tab functionality only to few main controls on the web form. Simply set TabIndex property of server control, or tabindex attribute of HTML control to -1. When you run web page and hit tab key, controls with TabIndex = -1 will never get a focus. Although, user can click into this control with a mouse and set focus on that way.

Web form sample project of using TAB key

You can download sample "Contact form" Visual Studio 2003 or Visual Studio 2005 project which shows you how you can manage focus on page load and move the focus using the Tab key. The sample contains simple contact form. When you start web application, textbox named "txtFirstName" gets a focus when page is loaded. If you press Tab key, focus will move to the next control, depending of value of control's TabIndex property. When focus comes to the last control with the biggest TabIndex, it goes again to the first control, txtFirstName. There is also txtSearch textbox on the web form which never can be "tabbed" and can receive focus only with a mouse click in it.

How to type TAB key in textbox or textarea 

Sometimes, you don't need to move cursor to the next control when hit Tab key. What if you need to type Tab character into text box or text area as a part of the text? Of course, your user can type tab in Notepad or some other text editor and simply do copy/paste to your form, but it is not so nice solution and users could say your application is not professional :). Instead of that, to type tab key in text box, you can use script like this (text box which accepts tab keys is named txtLongText):


[VB.NET]

txtLongText.Attributes.Add("onkeydown", _
"if(event.which || event.keyCode){if ((event.which == 9)" & _
"|| (event.keyCode == 9)) {document.getElementById('" & _
txtLongText.ClientID + "').selection = " & _
document.selection.createRange();" & _
txtLongText.ClientID & ".selection.text = " & _
" String.fromCharCode(9);return false;}} else {return true}; ")

[C#]

txtLongText.Attributes.Add("onkeydown",
"if(event.which || event.keyCode){if ((event.which == 9)" +
"|| (event.keyCode == 9)) {document.getElementById('"+
txtLongText.ClientID + "').selection = document.selection.createRange();" +
txtLongText.ClientID + ".selection.text = String.fromCharCode(9);return false;}} else {return true}; ");
Or better, to avoid hard coding, you can put this code to function named EnableTabType. Function has only one parameter, which specifies what is TextBox control where you need to enable typing of Tab characters.

[VB.NET]

Public Sub EnableTabType(tb As TextBox)
    tb.Attributes.Add("onkeydown", _
    "if(event.which || event.keyCode){if((event.which == 9)" & _
    "|| (event.keyCode == 9)) {document.getElementById('" & _
    tb.ClientID & "').selection=document.selection.createRange();" & _
    tb.ClientID & ".selection.text = " & _
    " String.fromCharCode(9);return false;}}else{return true};")
End Sub

[C#]

public void EnableTabType(TextBox tb)
{
    tb.Attributes.Add("onkeydown",
    "if(event.which || event.keyCode){if ((event.which == 9)" +
    "|| (event.keyCode == 9)) {document.getElementById('"+
    tb.ClientID + "').selection = document.selection.createRange();" +
    tb.ClientID + ".selection.text = String.fromCharCode(9);return false;}} else {return true}; ");
}

Tab key endnote

Default value of TextBox AutoPostBack property is false. If you set it to true and if text in TextBox control is changed, tab key will cause form to submit. After submitting, it is possible to lose a focus on current control, especially if you used javascript on page load to set focus initially. In that specific case, you can register client script dynamically when Page.IsPostBack value is true.
With Bean Software Shortcut Controls you can use TAB key as a keyboard shortcut to call ASP.NET server side code. Also, with these controls, you can manipulate different keyboard shortcuts like simple Enter, Tab or placing a focus in specific text box, or more complicated e.g. Ctrl + Shift + O or similar, just like shortcuts used in Visual Studio or Microsoft Word. Source code is included.

 

 





Sunday, February 20, 2011

Introduction to SQL Server 2008 Extended Events

SQL Server 2008 Extended Events are the new low level, high performance eventing system in SQL Server. They use less system resources and provide better tracking of SQL Server performance than previous methods like Perfmon and SQL Trace/Profiler events.

Performance Analysis history - SQL Server 2000 & 2005

In versions prior to SQL Server 2008 the tools for performance analysis were good but they had their cons. The methods and tools got better with each new version. However the common problem with all these is that the data is mostly pre-aggregated.

In SQL Server 2000 we used

System tables:  A wide array of tables that hold data with various system information.
Perfmon: A monitoring utility for server status with a wide variety of counters.
SQL Server Profiler/SQL Trace: A way to monitor SQL Server activity in real time.
PSSdiag: A general purpose diagnostic collection utility that natively collects Performance Monitor logs, SQL Profiler traces, SQL Server blocking script output, Windows Event Logs, etc...

With SQL server 2005 we got

Dynamic Management Views (DMV's) and Functions (DMF's): This new feature is a big leap forward in performance troubleshooting. These provide server state information that can be used to monitor the health of a server instance or diagnose problems.
Event Notification: We could get asynchronous notifications when subscribing to a subset of profiler events. This was and still is one of my favorite ways of debugging deadlocks.
SQLdiag & SQL Nexus: SQLdiag is a successor of PSSdiag. SQL Nexus is a tool used to load and analyze performance data collected by SQLdiag (SQL Server 2005) and PSSdiag (SQL Server 2000).
DMVStats: An application that can collect, analyze and report on SQL Server 2005 DMV performance data.
Event Tracing for Windows: ETW is a low level event tracing used to troubleshoot a wide variety of applications.  SQL Server 2005 has full support for this and can be used to troubleshoot problems. This feature isn't well known and is worth getting to know.

The problem with pre-aggregated data

Pre-aggregated data means that the counters are cumulative. In SQL Server 2000 and 2005 90+ percent of all counters were pre-aggregated. To get meaningful information we had to query those counters at different times which meant we actually had a sampling based system. And by definition sampling looses data. The other problem was that sampling too often would result in system strain.
To see this just look at the sysperfinfo system table (SQL Server 2000) or sys.dm_os_performance_counters DMV (SQL Server 2005). They both hold various performance counters for the system but all time base counters are cumulative and need sampling.
To get around this problem we had to use SQL Server Profiler/SQL Trace to get real time information on system activity. However there are a lot of stories of how SQL Server Profiler took a production system down in one way or another. Just google it up :)
In SQL Server 2008 this problem was addressed in a very cool way. Enter SQL Server 2008 Extended Events.

What are SQL Server 2008 Extended Events

SQL Server 2008 Extended Events are a completely new general eventing system. They are defined based on Event Tracing for Windows (ETW) which allows us to integrate SQL Server tracing with ETW traces from the operating system, IIS and other ETW enabled applications.
Events themselves fire synchronously but can be processed either synchronously or asynchronously depending on the use case. They are extremely fast and don't put noticeable strain on the system. For example a reported figure by Microsoft is that 20,000 events/sec on a 2 GHZ Pentium with 1 GB RAM takes less than 2% of the CPU. This means that on a concrete heavy duty server the performance impact is very close to zero.
Another good thing is that the Microsoft PSS team introduced a system_health Extended Events Session, which is always on and collects different high severity error data when it happens. This gives them an instant idea about system stability and problems speeding up the whole problem tracing and solving.
For now the only SQL Server 2008 feature built on top of the Extended Events is the the Auditing capability but the next version will probably have all other eventing structures moved to Extended Events.

SQL Server 2008 Extended Events Building Blocks

As they are a completely new capability of SQL Server 2008 we have to learn some new terminology.

Packages

A package holds all components needed for Extended Events. A package exists in an EXE or DLL. Currently there are 4 packages and they all reside inside SQLSERVER.EXE. Those 4 packages are: Package0 (default), Sqlos, Sqlserver, SecAudit (is private and used only for the built-in SQL Server auditing). Existing packages can be viewed with this SQL statement:
select * from sys.dm_xe_packages

Events

Events are points in code that we can trace. They always execute synchronously. However forwarding them to a target is done either synchronously or asynchronously. Currently there are 254 events defined in SQL Server 2008. I expect that with with future service packs and versions more will be added. Each event contains a set of columns called a payload. The payload is the default collected data for the event. Since each event is defined with the ETW model they easily integrate into the broader ETW tracing outside the SQL Server. Existing events and their payload can be viewed with these SQL statements:
-- view all defined events in sql server
SELECT * FROM sys.dm_xe_objects WHERE object_type = 'event' ORDER BY name
-- view all columns for each event - the event payload
SELECT * FROM  sys.dm_xe_object_columns

Predicates

Predicates are used to filter events based on some conditions before they are sent to the target. They fully support short-circuiting so it's a good idea to first specify simple predicates followed by more complex ones. All predicates are processed synchronously so be aware that heavy duty conditions can cause some performance degradation. It's best to keep predicates simple like filtering the event based on a transaction ID or object ID or something similar. Existing predicates can be viewed with this SQL statement:
SELECT * FROM sys.dm_xe_objects WHERE object_type in ('pred_compare', 'pred_source') ORDER BY name

Actions

Actions are commands that provide additional information for an event. Actions provide additional data like SQL statement text, SQL statement execution plan handle or a resource governor pool that the SQL statement is being run in and are appended to the event payload. Any action can be linked to any event. They are also processed synchronously after all predicates have been evaluated and before the event is sent to its target. Existing actions can be viewed with this SQL statement:
SELECT * FROM sys.dm_xe_objects WHERE object_type = 'action' ORDER BY name

Targets

There are currently 6 targets for event consuming events.
Event bucketing
The event bucketing can be viewed as a histogram of values. We pick events to monitor and the target creates a histogram (actually an XML document) of how many times each event was fired. This target is both synchronous and asynchronous.
Event pairing
The event pairing show us incomplete events that can be paired. So for example if we're monitoring lock_acquired and lock_released events the event pairing target will show only payload of lock_acquired events for which lock_released event hasn't happened yet. This target is only synchronous.
ETW target
The ETW target is a file that can be merged with other ETW logs from OS or IIS or some other ETW enabled application. For it to work the SQL Server service startup account has to be in the "Performance Log Users" group. This is a binary file that has to be formatted with tracerpt.exe to make it human readable. This target is only synchronous.
Event file
The event file is just a binary file that all events are written to. We can set the size of the file, the maximum of rolled over files and the file increment size in MB. The event files can be read by using the sys.fn_xe_file_target_read_file built in function. This target is only asynchronous.
Synchronous event counter
The synchronous event counter target, as its name suggests, only counts how many times an event has been fired. It does not collect any other data. This makes it very useful for getting an overview of system activity. This target is only synchronous.
Ring buffer
The ring buffer target is a circular in-memory only target that is represented by a FIFO queue. It can work in two modes: strict FIFO and per-event FIFO. The strict FIFO acts like a normal FIFO queue where older events are removed when new ones arrive and the buffer is full. The per-event FIFO holds a specific number of each event type in the queue and removes them after the buffer is full . We set the ring buffer size (max_memory) and the number of held events (occurrence_number) when defining a session. This target is only asynchronous.
  All targets can be viewed with this SQL statement:
SELECT * FROM sys.dm_xe_objects WHERE object_type = 'target' ORDER BY name
Note that the security audit target is private and can only be used by SQL Server for built in auditing functionality.

Sessions

Sessions are means of grouping events, actions, predicates and targets. Any events can be linked with any action within a session. This means that we can have one event defined in multiple sessions independently of each other.
For each session we have to define how much memory is used for event buffering, how many events can be disregarded if there is memory pressure, how long will the events stay in the buffer before being sent to the target, whether the session will be auto started when SQL Server service starts and if we want to enable causality tracking. Causality tracking enables correlating related events on different connections. After a session is created we always have to start it using the ALTER EVENT SESSION statement since they are always created in the stopped state. Existing sessions can be viewed with this SQL statement:
SELECT * FROM sys.dm_xe_sessions
Unless it was dropped there should always be the built-in system_health session running.

Types and Maps

A type is a simple or complex data type that is used in the event payload. Maps are dictionaries that map some numeric value to a text. Existing types and maps can be viewed with these SQL statements:
SELECT * FROM sys.dm_xe_objects WHERE object_type in ('map', 'type') ORDER BY name; 
SELECT * FROM sys.dm_xe_map_values;
You can look through the Extended Events DMV's you can query them to get any information you desire. The below picture shows the grouping of all Extended Events terminology we've learned above:

Open Source SQL Server 2008 Extended Events Management Software

Extended Events Manager: A GUI to create and manage Extended Events since their manipulation is pure T-SQL for now.
NTraceA library that provides flexible, high-performance, and zero-configuration trace logging for your C# applications. With this you enable ETW for your applications and thus can integrate their trace with SQL Server 2008 Extended Events ETW target for complete trace picture.
XPerf: A tool that eases managing Windows ETW traces. You can use this to ETW trace IIS, while your application uses NTrace and SQL Server 2008 uses an Extended Events ETW target. Then you can combine all 3 traces and analyze them.

Simple Example

This is the full code needed for running a simple Extended events session. The comments in code provide info about what each part does.
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE Name='BucketizerExampleSession')
    DROP EVENT SESSION BucketizerExampleSession ON SERVER
GO
-- create the event session
CREATE EVENT SESSION BucketizerExampleSession ON SERVER
    -- will only check for any lock_acquired event. 
    -- this includes any kind of lock (S, X, IX, etc...)
    ADD EVENT sqlserver.lock_acquired 
    (
        -- 8 is the AdventureWorks DB_ID on my machine
        WHERE database_id = 8 
    ) 
    ADD TARGET package0.synchronous_bucketizer 
    ( 
        -- only show us lock_acquired events
        SET filtering_event_name='sqlserver.lock_acquired', 
            -- source for the bucketizer data comes from event (0), 
            -- could come from action (1)
            source_type=0, 
            -- resource_0 is actually an object_id
            source='resource_0' 
    )
    -- set any of the session properties which are:
    -- MAX_MEMORY, EVENT_RETENTION_MODE, MAX_DISPATCH_LATENCY, MAX_EVENT_SIZE, 
    -- MEMORY_PARTITION_MODE, TRACK_CAUSALITY, STARTUP_STATE
    WITH (MAX_MEMORY = 60MB, MAX_EVENT_SIZE = 30MB);
GO
-- we have to manually start the session
ALTER EVENT SESSION BucketizerExampleSession ON SERVER 
STATE = START
GO
-- run some simple test queries
SELECT TOP 1 * FROM Sales.vIndividualCustomer
SELECT TOP 1 * FROM Sales.vIndividualDemographics
SELECT TOP 1 * FROM Production.vProductModelInstructions
GO

-- The XML data of the Bucketizer target
SELECT    CAST(t.target_data AS XML) AS xmlLockData
FROM    sys.dm_xe_session_targets t
        JOIN sys.dm_xe_sessions s ON s.Address = t.event_session_address
        JOIN sys.server_event_sessions ss ON s.Name = ss.Name
WHERE    t.target_name = 'synchronous_bucketizer' 
        AND s.Name = 'BucketizerExampleSession'

-- tabular data from the upper XML
SELECT  name, OBJECT_ID, lock_count 
FROM (
        SELECT  LockStats.value('.','bigint') AS lock_object_id, 
                LockStats.value('@count', 'bigint') AS lock_count
        FROM    (
                 SELECT CAST(t.target_data AS XML) AS xmlLockData
                 FROM    sys.dm_xe_session_targets t
                         JOIN sys.dm_xe_sessions s ON s.Address = t.event_session_address
                         JOIN sys.server_event_sessions ss ON s.Name = ss.Name
                 WHERE   t.target_name = 'synchronous_bucketizer' 
                         AND s.Name = 'BucketizerExampleSession'
                ) Locks
                CROSS APPLY xmlLockData.nodes('//BucketizerTarget/Slot') AS T(LockStats)
     ) LockedObjects 
     -- join to sys.objects to get object name
     JOIN sys.objects o ON LockedObjects.lock_object_id = o.object_id 
WHERE o.Type = 'U' -- get results only for user defined tables
ORDER BY lock_count DESC
GO

-- manually stop the session
ALTER EVENT SESSION BucketizerExampleSession ON SERVER
STATE = STOP
GO
All information about Extended events can be seen with sys.dm_xe_* DMV’s and sys.server_event_* catalog views. Also remember to start the session after creating since it’s always created in the stopped state.

Conclusion

This is an introductory article to get acquainted with the new SQL Server 2008 Extended Events infrastructure and terminology. Although they are so far the most powerful troubleshooting method yet in SQL Server, they have a few laps to go to really get there and be fully useful.

Scheduling Jobs in SQL Server Express - Part 2

In my previous article Scheduling Jobs in SQL Server Express we saw how to make simple job scheduling in SQL Server 2005 Express work. We limited the scheduling to one time or daily repeats. Sometimes this isn't enough. In this article we'll take a look at how to make a scheduling solution based on Service Broker worthy of the SQL Server Agent itself.

We will try to imitate scheduled jobs provided by SQL Server Agent like making a scheduled job made up of multiple job steps. Every job will have it's own custom job schedule similar to the one you can set up with SQL Server Agent. We'll also be able to add and remove scheduled jobs and job schedules and start and stop a scheduled job.
Note: All scheduled job dates HAVE TO BE handled in UTC time since conversation timer works only in UTC date format.

Security

In part 1 we were limited to making jobs run only in our database. Because of that we couldn't backup any other databases. Since that is a big drawback for a complete scheduling solution we need a fix for this. This happens because the activation stored procedure always runs under the EXECUTE AS security context which is more restrictive than normal db_owner context we use. This is well explained in the Books Online article Extending Database Impersonation by Using EXECUTE AS. There are two ways to go about this.
The first one which is also used in this article is to set our scheduling database to trustworthy:
ALTER DATABASE TestScheduledJobs SET TRUSTWORTHY ON
Setting the database to trustworthy gives the members of the db_owner role sysadmin privileges. So be careful with this setting.
The second option is to sign the activation stored procedure with a certificate. How to do this is excellently explained by Remus Rusanu, a former developer of Service Broker on his blog here. This is extremely useful if you need finer access control in your database.
Solving the security issue allows us to have one database for all scheduling which is what a proper scheduling engine should have.

Required Tables

We need 4 tables for this to work well. You can add more tables such as job history but you'll have to modify the stored procedures to use them.

ScheduledJobs Table

This holds information about our scheduled jobs such as job name, enabled status, etc...
CREATE TABLE ScheduledJobs
(
    ID INT IDENTITY(1,1), 
    JobScheduleId INT NOT NULL,
    ConversationHandle UNIQUEIDENTIFIER NULL,
    JobName NVARCHAR(256) NOT NULL DEFAULT (''),
    ValidFrom DATETIME NOT NULL,
    LastRunOn DATETIME, 
    NextRunOn DATETIME, 
    IsEnabled BIT NOT NULL DEFAULT (0),
    CreatedOn DATETIME NOT NULL DEFAULT GETUTCDATE()
)

ScheduledJobSteps Table

This holds the job step name, the SQL statement to run in the step, whether to retry the step on failure and how many times, step duration, etc...
CREATE TABLE ScheduledJobSteps
(
    ID INT IDENTITY(1,1),
    ScheduledJobId INT NOT NULL,    
    StepName NVARCHAR(256) NOT NULL DEFAULT (''), 
    SqlToRun NVARCHAR(MAX) NOT NULL, -- sql statement to run
    RetryOnFail BIT NOT NULL DEFAULT (0), -- do we wish to retry the job step on failure
    RetryOnFailTimes INT NOT NULL DEFAULT (0), -- if we do how many times do we wish to retry it
    DurationInSeconds DECIMAL(14,4) DEFAULT (0), -- duration of the step with all retries 
    CreatedOn DATETIME NOT NULL DEFAULT GETUTCDATE(),
    LastExecutedOn DATETIME
)

JobSchedules Table

This holds the job's schedule. Multiple jobs can have the same schedule. Here we specify an absolute or relative scheduling frequency. This is mimicking most of the SQL Server Agent scheduling options. For weekly scheduling number 1 is Monday and number 7 is Sunday. This is because the ISO standard says that a week starts on Monday.
CREATE TABLE JobSchedules
(
    ID INT IDENTITY(1, 1) PRIMARY KEY,
    FrequencyType INT NOT NULL CHECK (FrequencyType IN (1, 2, 3)), 
        -- daily = 1, weekly = 2, monthly = 3. "Run once" jobs don't have a job schedule 
    Frequency INT NOT NULL DEFAULT(1) CHECK (Frequency BETWEEN 1 AND 100),
    AbsoluteSubFrequency VARCHAR(100), 
        -- '' if daily, '1,2,3,4,5,6,7' day of week if weekly, '1,2,3,...,28,29,30,31' if montly    
    MontlyRelativeSubFrequencyWhich INT, 
    MontlyRelativeSubFrequencyWhat INT,
    RunAtInSecondsFromMidnight INT NOT NULL DEFAULT(0) 
        CHECK (RunAtInSecondsFromMidnight BETWEEN 0 AND 84599), -- 0-84599 = 1 day in seconds
    CONSTRAINT CK_AbsoluteSubFrequency CHECK 
        ((FrequencyType = 1 AND ISNULL(AbsoluteSubFrequency, '') = '') OR -- daily check
         (FrequencyType = 2 AND LEN(AbsoluteSubFrequency) > 0) OR -- weekly check (days of week CSV)
         (FrequencyType = 3 AND (LEN(AbsoluteSubFrequency) > 0 -- monthly absolute option (days of month CSV)
             AND MontlyRelativeSubFrequencyWhich IS NULL 
             AND MontlyRelativeSubFrequencyWhat IS NULL)
             OR ISNULL(AbsoluteSubFrequency, '') = '') -- monthly relative option
                ), 
    CONSTRAINT MontlyRelativeSubFrequencyWhich CHECK -- only allow values if frequency type is monthly
         (MontlyRelativeSubFrequencyWhich IS NULL OR 
           (FrequencyType = 3 AND 
            AbsoluteSubFrequency IS NULL AND 
            MontlyRelativeSubFrequencyWhich IN (1,2,3,4,5)) -- 1st-4th, 5=Last
                                              ), 
    CONSTRAINT MontlyRelativeSubFrequencyWhat CHECK  -- only allow values if frequency type is monthly
      (MontlyRelativeSubFrequencyWhich IS NULL OR 
       (FrequencyType = 3 AND 
        AbsoluteSubFrequency IS NULL AND
        MontlyRelativeSubFrequencyWhich IN (1,2,3,4,5,6,7,-1)) -- 1=Mon to 7=Sun, -1=Day
                                              )
)
For monthly relative scheduling you can easily set the first/second/third/fourth/last day of the week or of the month.

SchedulingErrors Table

This table contains information about any errors that have happened in our job execution. Once a job errors out it will stop without any further execution.
CREATE TABLE SchedulingErrors
(
    Id INT IDENTITY(1, 1) PRIMARY KEY,
    ScheduledJobId INT, 
    ScheduledJobStepId INT,    
    ErrorLine INT,
    ErrorNumber INT,
    ErrorMessage NVARCHAR(MAX),
    ErrorSeverity INT,
    ErrorState INT,    
    ErrorDate DATETIME NOT NULL DEFAULT GETUTCDATE()
)

Required User-Defined Functions

dbo.GetNextRunTime

This calculates the next time our job will be run based on the schedule we created and the last run time or last ValidFrom time of the job. In the function we use an excellent calendar table F_TABLE_DATE and a user defined function called dbo.F_ISO_WEEK_OF_YEAR that gets the ISO Week number for a date. Both were created by Michael Valentine Jones, a regular on the SQLTeam.com forums. The GetNextRunTime function is quite complex but the comments in code should provide enough information for complete understanding. If they don't, ask questions in the comments.
The following code shows the part of the UDF that calculates the next date to run for the daily scheduling type:
-- ...  
-- DAILY SCHEDULE TYPE
IF @FrequencyType = 1
BEGIN        
    SELECT TOP 1 @NextRunTime = DATE
    FROM (
            SELECT  DATEADD(s, @RunAtInSecondsFromMidnight, DATE) 
                AS DATE, ROW_NUMBER() OVER(ORDER BY DATE) - 1 AS CorrectDaySelector
            FROM    dbo.F_TABLE_DATE(@LastRunTime, DATEADD(d, 2*@Frequency, @LastRunTime))
         ) t
    WHERE   DATE > @LastRunTime
            AND CorrectDaySelector % @Frequency = 0
    ORDER BY DATE
END
-- ...

Required Stored Procedures

These stored procedures make a nice and friendly interface to the scheduling functionality.
  • usp_AddJobSchedule: Adds a new job schedule.
  • usp_RemoveJobSchedule: Removes an existing job schedule.
  • usp_AddScheduledJob: Adds a new scheduled job. For "Run once" job types we don't need a Job Schedule so for @JobScheduleId parameter we have to pass -1 and for @NextRunOn we have to set a date in the future in UTC time.
  • usp_RemoveScheduledJob: Removes an existing scheduled job.
  • usp_AddScheduledJobStep: Adds a new scheduled job step for a job. It also calculates the next run time of the job.
  • usp_RemoveScheduledJobStep: Removes an existing scheduled job step from a job.
  • usp_StartScheduledJob: Used when starting a new or job or re-enabling an old disabled job by passing a new ValidFrom date and in the activation stored procedure to start the job anew for the next scheduled run.
  • usp_StopScheduledJob: Stops the scheduled job run by ending the conversation for it and setting it to disabled.
  • usp_RunScheduledJobSteps: Runs every job step and repeats it the set number of times if that option is enabled. After each successful job step execution the step duration time and last run on time is set.
  • usp_RunScheduledJob:  Activation stored procedure that is NOT meant to be run by hand. You should run it by hand only for debugging, when your messages are left in the queue. The stored procedure receives the dialog timer message from the queue and finds the scheduled job that corresponds with its conversation handle. After it gets the Scheduled job it runs its Job steps, sets the dialog timer for the next scheduled value and updates the last run time of the job.

Scheduling Code Examples

DECLARE @JobScheduleId INT, 
 @ScheduledJobId INT, 
 @validFrom DATETIME, 
 @ScheduledJobStepId INT, 
 @secondsOffset INT, 
 @NextRunOn DATETIME

SELECT    @validFrom = GETUTCDATE(), -- the job is valid from current UTC time
         -- run the job 2 minutes after the validFrom time. 
         -- we need the offset in seconds from midnight of that day for all jobs
        @secondsOffset = 28800, -- set the job time time to 8 in the morning of the selected day
        @NextRunOn = DATEADD(n, 1, @validFrom) -- set next run for once only job to 1 minute from now

-- SIMPLE RUN ONCE SCHEDULING EXAMPLE
-- add new "run once" scheduled job 
EXEC usp_AddScheduledJob @ScheduledJobId OUT, -1, 'test job', @validFrom, @NextRunOn
-- add just one simple step for our job
EXEC usp_AddScheduledJobStep @ScheduledJobStepId OUT, @ScheduledJobId, 'EXEC sp_updatestats', 'step 1'
-- start the scheduled job
EXEC usp_StartScheduledJob @ScheduledJobId 

-- SIMPLE DAILY SCHEDULING EXAMPLE
-- run the job daily
EXEC usp_AddJobSchedule @JobScheduleId OUT,
                        @RunAtInSecondsFromMidnight = @secondsOffset,
                        @FrequencyType = 1,
                        @Frequency = 1 -- run every day                      
-- add new scheduled job 
EXEC usp_AddScheduledJob @ScheduledJobId OUT, @JobScheduleId, 'test job', @validFrom
DECLARE @backupSQL NVARCHAR(MAX)
SELECT  @backupSQL = N'DECLARE @backupTime DATETIME, @backupFile NVARCHAR(512); 
    SELECT @backupTime = GETDATE(), 
    @backupFile = ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorks_'' + 
         replace(replace(CONVERT(NVARCHAR(25), @backupTime, 120), '' '', ''_''), '':'', ''_'') + 
            N''.bak''; 
    BACKUP DATABASE AdventureWorks TO DISK = @backupFile;'

EXEC usp_AddScheduledJobStep @ScheduledJobStepId OUT, @ScheduledJobId, @backupSQL, 'step 1'
-- start the scheduled job
EXEC usp_StartScheduledJob @ScheduledJobId 

-- COMPLEX WEEKLY ABSOLUTE SCHEDULING EXAMPLE
-- run the job on every tuesday, wednesday, friday and sunday of every second week
EXEC usp_AddJobSchedule @JobScheduleId OUT,
                        @RunAtInSecondsFromMidnight = @secondsOffset,
                        @FrequencyType = 2, -- weekly frequency type
                        @Frequency = 2, -- run every every 2 weeks,
                        @AbsoluteSubFrequency = '2,3,5,7' 
     -- run every Tuesday(2), Wednesday(3), Friday(5) and Sunday(7)    
-- add new scheduled job 
EXEC usp_AddScheduledJob @ScheduledJobId OUT, @JobScheduleId, 'test job', @validFrom
-- add three steps for our job
EXEC usp_AddScheduledJobStep @ScheduledJobStepId OUT, @ScheduledJobId, 'EXEC sp_updatestats', 'step 1'
EXEC usp_AddScheduledJobStep @ScheduledJobStepId OUT, @ScheduledJobId, 'DBCC CHECKDB', 'step 2'
EXEC usp_AddScheduledJobStep @ScheduledJobStepId OUT, @ScheduledJobId, 
  'select 1,', 'step 3 will fail', 1, 2 -- retry on fail 2 times
-- start the scheduled job
EXEC usp_StartScheduledJob @ScheduledJobId 

-- COMPLEX RELATIVE SCHEDULING SCHEDULING EXAMPLE
DECLARE @relativeWhichDay INT, @relativeWhatDay INT
SELECT    @relativeWhichDay = 4, -- 1 = First, 2 = Second, 3 = Third, 4 = Fourth, 5 = Last
        @relativeWhatDay = 3 -- 1 = Monday, 2 = Tuesday, ..., 7 = Sunday, -1 = Day
-- run the job on the 4th monday of every month 
EXEC usp_AddJobSchedule @JobScheduleId OUT,
                        @RunAtInSecondsFromMidnight = @secondsOffset, -- int
                        @FrequencyType = 3, -- monthly frequency type
                        @Frequency = 1, -- run every month,
                        @AbsoluteSubFrequency = NULL, -- no aboslute frequence if relative is set
                        @MontlyRelativeSubFrequencyWhich = @relativeWhichDay,
                        @MontlyRelativeSubFrequencyWhat = @relativeWhatDay
/*
some more relative monthly scheduling examples
run on:
the first day of the month:
  - @MontlyRelativeSubFrequencyWhich = 1, @MontlyRelativeSubFrequencyWhat = -1
the third thursday of the month:
  - @MontlyRelativeSubFrequencyWhich = 3, @MontlyRelativeSubFrequencyWhat = 4
the last sunday of the month:
  - @MontlyRelativeSubFrequencyWhich = 5, @MontlyRelativeSubFrequencyWhat = 7
the second wedensday of the month:
  - @MontlyRelativeSubFrequencyWhich = 2, @MontlyRelativeSubFrequencyWhat = 3
*/
-- add new scheduled job 
EXEC usp_AddScheduledJob @ScheduledJobId OUT, @JobScheduleId, 'test job', @validFrom
-- add just one simple step for our job
EXEC usp_AddScheduledJobStep @ScheduledJobStepId OUT, @ScheduledJobId, 'EXEC sp_updatestats', 'step 1'
-- start the scheduled job
EXEC usp_StartScheduledJob @ScheduledJobId 

-- SEE WHAT GOING ON WITH OUR JOBS
-- show the currently active conversations
-- look at dialog_timer column (in UTC time) to see when will the job be run next
SELECT GETUTCDATE(), dialog_timer, * FROM sys.conversation_endpoints
-- shows the number of currently executing activation procedures
SELECT * FROM sys.dm_broker_activated_tasks
-- see how many unreceived messages are still in the queue. should be 0 when no jobs are running
SELECT * FROM ScheduledJobQueue WITH (NOLOCK)
-- view our scheduled jobs' statuses
SELECT * FROM ScheduledJobs  WITH (NOLOCK)
SELECT * FROM ScheduledJobSteps WITH (NOLOCK)
SELECT * FROM JobSchedules  WITH (NOLOCK)
SELECT * FROM SchedulingErrors WITH (NOLOCK)

Conclusion

When we look at this solution we can see it's a pretty powerful scheduling engine. If needed it can be extended for hourly or minute-level scheduling but that is an exercise for the reader. The script might look overly complex but once you have it set up it's use is fast and easy