Thursday, December 16, 2010

SQL Server - Parameter Sniffing

The very fact that there were not many articles about this indicates that it is not a very common issue and you might spend your whole career without getting to know about it. However this article is to help those few who are affected by this issue and scratching your head like I did.

                As explained by Microsoft site
             "Parameter sniffing" refers to a process whereby SQL Server's execution environment "sniffs" the
              current parameter values during compilation or recompilation, and passes it along to the query optimizer
              so that they can be used to generate potentially faster query execution plans
            
                 Yes SQL server uses the actual parameter of a stored procedure to come up with an optimized execution plan. Now this was never a problem until that one time I was hit by a very strange performance issue. When I searched internet for the solution there weren’t many similar cases but however came across this term by chance and it did hit the spot of our issue. When the Stored procedure is executed the first time, it is compiled and the execution plan is cached to be reused by the subsequent request. It is this first time that the parameter used to create the execution plan is sniffed along with it.  Consider the following procedure

Create Procedure [dbo].[GetTotalOrders]
@agentID int,
@location varchar(20)
AS

       ........
       ........

       Select * From Orders O
       Inner Join .....
       Where O.Agent = @agentID
       And L.Location = @location

GO
It is a simple procedure to get the list of all order for a given parameter. It was working well so far except for a new agent who got added very recently. The pages will timeout for that one agent all the time. To worsen the scenario, when I ran the select statement from the Management Studio it returned in seconds just as it did for other agents. It just got as strange as it could get.
The total number rows for this new agent was awfully lot more than any other agents in our system.  The timeout was due to Parameter Sniffing. When the procedure ran first it used the agentID parameter from that query to create the execution plan, however the execution plan wasn’t holding good for this new agent. When we recompiled the procedure with the new agent it returned as expected.
                Fortunately the solution for this was simple, to use a local variable instead of the parameter and magically the Parameter Sniffing issue goes away.
Create Procedure [dbo].[GetTotalOrders]
@agentID int,
@location varchar(20)
AS
       Declare @tempagentID int
       Declare @templocation varchar(20)
       Set @tempagentID = @agentID
       Set @templocation = @location
       ........
       ........
      
       Select * From Orders O
       Inner Join .....
       Where O.Agent = @tempagentID
       And L.Location = @templocation

GO

This article focuses on the stored procedure, but this issue can hit queries submitted using sp_executesql” as well. Being a web developer this was enough for me to rectify the issue, however if you are an enthusiastic SQL Developer or a DBA, you can research to find more for yourself.

Tuesday, December 14, 2010

ValidationGroup in ASP.NET

More than often we come across screens with multiple sections that can perform specific operations independently. For example a maintenance screen can be used to add more than one type of a Look Up entry. In such screens where certain control can be grouped together we can use ValidationGroup to perform the validations independently. Without this we might end up not using a validation control on the page and perform all the validations on the server side inside the button click event.

Consider the following screen that is used to Add Vendors and Agents to the system.








Vendor Name and Vendor Email are mandatory for adding Vendor, Agent Name and Agent ID are mandatory for adding Agent, However they are independent tasks by themselves. When I click on Add Agent it validates all control in the screen


Now I have added the ValidationGroup for the textboxes and associated them with the Button and ValidationControl. Now it validates only those controls that are in the validation group










The source code is:
<asp:ValidationSummary runat="server" ValidationGroup="VendorGroup"/>
<h2>Add New Vendor: </h2>
<table cellpadding="0" cellspacing="5" width="80%" border="1">
    <tr>
        <td>Vendor Name:
            <asp:TextBox ID="customerTextBox" runat="server"></asp:TextBox>
            <asp:RequiredFieldValidator ID="reqval" runat="server" Text="*" ControlToValidate="customerTextBox"
            ErrorMessage="Enter Vendor Name" ValidationGroup="VendorGroup"></asp:RequiredFieldValidator>
        </td>
        <td>Vendor Email:
            <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
            <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" Text="*" ControlToValidate="TextBox1"
            ErrorMessage="Enter Vendor Email" ValidationGroup="VendorGroup"></asp:RequiredFieldValidator>
        </td>
        <td>
            <asp:Button ID="customerButton" runat="server" Text="Add Vendor" ValidationGroup="VendorGroup"/>
        </td>
    </tr>
</table>
<br />
<br />
<br />
<asp:ValidationSummary ID="ValidationSummary1" runat="server" ValidationGroup="AgentGroup"/>
<h2>Add New Agent: </h2>
<table cellpadding="0" cellspacing="0" width="80%" border="1">
    <tr>
        <td>Agent Name:
            <asp:TextBox ID="orderIDTextBox" runat="server"></asp:TextBox>
            <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" Text="*" ControlToValidate="orderIDTextBox"
            ErrorMessage="Enter Agent Name" ValidationGroup="AgentGroup"></asp:RequiredFieldValidator>
        </td>
        <td>Agent ID:
            <asp:TextBox ID="customerIDTextBox" runat="server"></asp:TextBox>
            <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" Text="*" ControlToValidate="customerIDTextBox"
            ErrorMessage="Enter Agent ID" ValidationGroup="AgentGroup"></asp:RequiredFieldValidator>
        </td>
        <td>
            <asp:Button ID="orderButton" runat="server" Text="Add Agent" ValidationGroup="AgentGroup"/>
        </td>
    </tr>
</table>

As you can see all we have to do is add the ValidationGroup property to the validation controls, button and the Validationsummary control.  We must be cautious to provide a unique Name for the ValidationGroup across the page and use the same name in the button.

There might be scenario when more than one validation group must be validated when the page does the postback. In such cases we can validate the groups separately from the code behind using Page.Validate overload that takes the groupname
Page.Validate("VendorGroup");
Page.Validate("AgentGroup");

Note: Page.Validate() will validate all the validationgroups within the page.

Thursday, December 9, 2010

C# Nullables

                With the introduction of LINQ to SQL it has become inevitable to support the basic feature of  SQL server database that would help us convert queries to LINQ. It was a pain in the neck to deal with the DB null values since the value types such as Int, Decimal, DateTime etc cannot be assigned null. With the introduction of Nullable<T> life is more easier now. .NET also introduced a very user friendly semantic to represent the Nullable<T>  which is T?. Thus,

Nullable<int> and  int? are one and the same
Nullable<decimal> and  decimal? are the same

int? Usage:
Nullable variable can be declared as,
int? i = null;
My personally recommendation would be to use the default keyword to assign null like,
int? i = default(int?);
This will come in handy while using Nullable in Generics

The assignment operation will be just like old styled
    {
        int? i = default(int?);
        i = 100;
       
        int j;
        if (i == null)
            j = 0;
        else
            j = i.Value;

        (OR)

        int j = i == null? 0 : i.Value;
    }

?? Operator:
                If you are little familiar with TSQL you must have heard of coalesce. ?? is the C# equivalent to coalesce in SQL Server and it was made possible because of the Nullable<T> struct.  With these basics in place we can very easily convert datatable to custom objects using LINQ without much hassle.
Sample Implementation of LINQ query to with this operator usage is below.
    public class CustomObject
    {
        public int? Field1;
        public decimal? Field2;
    }

var cos = (from table1Row in table1.AsEnumerable()
                join table2Row in table2.AsEnumerable()
                on table1Row.Field<int>("JoinID") equals table2Row.Field<int>("JoinID")
                select new CustomObject
                {
  //Usage of Nullable struct
                    Field1 = table1Row.Field<int?>("ID"),
                    //Usage of coalesce operator
                    Field2 = table1Row.Field<decimal?>("Price") ?? table2Row.Field<decimal?>("Price")
                }).ToList<CustomObject>();

Alas we now the object with null values and right price without much hassle.

Summary
We have just discovered a new way to create objects in .NET that can closely represent the data value from SQL server. With the introduction of Nullable<T> and coalesce operator, life should be much easier and cleaner in handling the null values from the Database for .NET value types. However this is just a tip of the iceberg that I tasted. Hope you will see these in action with much complicated scenario and will appreciate the real value of it. Happy Coding.
Reference:  http://msdn.microsoft.com/en-us/library/b3h38hb0.aspx

Index - Fill Factor

                Indexes in Sql server are very effective and effortless feature to increase overall performance of the application. Adding indexes is the first thing that will come to our mind to reduce response time on any query. However it is equally important to keep in mind that indexes can very well degrade performance if not handled right. Indexes are can improve performance of select queries on the table which does not have frequent Inserts and Updates.
                I worked shortly on an ASP.NET, SQL Server application which does some data intensive business calculation and generates monthly reports. It was working fine for few months after which we started seeing timeout issues. The timeout issue was basically because the select queries are taking around 20 mins to return as against few seconds before. We ran the table stats and index tuning wizard and everything seem to be quite right. When we rebuilt the indexes and reran the query and it returned in 3 seconds. This was the hint.
There was an internal process that runs every month to read flat input file and generate the output. In the process it does bulk insert to the table and does some aggregation function to spit the final output report. It is this process that creating the issue.

                Though there was only a very few indexes in the table, after a while when the database was significantly big (we share database server with other applications), the insert have caused a lot of page fragmentation on the table and the select queries were timing out.
The solution was to rebuild the appropriate indexes using Alter statement after the inserts.

ALTER INDEX ALL ON TableName1 REBUILD

If the fragmentation is below 40% then consider Reorganizing the indexes
ALTER INDEX ALL ON TableName1 Reorganize

 Issue solved, but only for a few months, we were soon stumbled upon performance issue again, this time more badly, the CPU usage was spiking quite often. Index rebuilding takes a lot of CPU resource and locks the table as well. As the table grow the rebuild became a costly operation and practically impossible to do after every inserts. This is where fill factor came in handy. This option is often overlooked while creating the inserts but it is a very powerful option. It decides on how much empty space should be left on the page to accommodate inserts and updates to the table. We then updated the indexes with fill factor as 70% to accommodate the bulk inserts.

ALTER INDEX ALL ON TableName1 REBUILD WITH (FILLFACTOR = 70)

This may not be ideal for all application but consider creating the index with atleast 90% fill factor, default is always 0 that fills the page fully. Since fill factor can only be applied while creating or rebuilding the index we created a nightly job that run off hours to update the index once every week with 70 fillfactor.
Problem is resolved and everyone is happy.