Tuesday, September 21, 2010

How to check query performance without profiler.

If you don’t have profiler installed and you just want to find out execution statistics of procedures, you can take help of DMVs. You can write T-SQL queries using DMV to gather execution statistics of SQL procedures.

You can extract following information using DMV regarding Procedure.

· How many times your stored procedure executed.

· Total number of cached plans.

· Identify Missing Indexes Using.

· Buffer Cache hit ratio example.

· SQL CLR Memory Usage

· And many more information we can find from below references.

Here is the sample query using DMV which if found from some good sites.

SELECT OBJECT_NAME(st.objectid,dbid) StoredProcedure
      ,max(cp.usecounts) Execution_count
 FROM sys.dm_exec_cached_plans cp
         CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'
   group by cp.plan_handle, DB_NAME(st.dbid),
            OBJECT_SCHEMA_NAME(objectid,st.dbid), 
   OBJECT_NAME(objectid,st.dbid) 
 order by Execution_count desc.

SELECT TOP ( 100 )
        p.name AS [SP Name] ,
        deps.total_logical_reads AS [TotalLogicalReads] ,
        deps.total_logical_reads / deps.execution_count AS [AvgLogicalReads],
        deps.execution_count,
        ISNULL(deps.execution_count / DATEDIFF(Second, deps.cached_time,
                                           GETDATE()), 0) AS [Calls/Second],
        deps.total_elapsed_time,
        deps.total_elapsed_time / deps.execution_count AS [avg_elapsed_time],
        deps.cached_time
FROM    sys.procedures AS p
        INNER JOIN sys.dm_exec_procedure_stats
                       AS deps ON p.[object_id] = deps.[object_id]
WHERE   deps.database_id = DB_ID()
ORDER BY  deps.execution_count DESC

References:

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!828.entry

http://technet.microsoft.com/en-us/library/cc966540.aspx

http://www.databasejournal.com/features/mssql/article.php/3687186/Monitoring-Stored-Procedure-Usage.htm

http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/23/4-0-useful-queries-on-dmv-s-to-understand-plan-cache-behavior.aspx

http://www.simple-talk.com/sql/t-sql-programming/dmvs-for-query-plan-metadata/

Though DMV are very powerful and we can find out any information about query execution. we can’t spend time to change them as our requirement. The thing we can do is keep DMV for some test cases ready and just run them when it comes to performance testing.

Profiler:

SQL profiler provides GUI to gather same data, only good thing about using SQL profiler will give you all the information separated by events, let’s say If you want to know that which statement in procedure force recompilation of plan?.

Well, if you prefer DMV you have to write DMV to find out how many time plans have re-used, and if you are running SQL profiler it will just log all the events and will show all the data at all point of execution. Also SQL profiler has many pre-defined templates which come handy when we are running profiler for collection different types of data.

You can see http://support.microsoft.com/kb/243586 for more details about Troubleshooting stored procedure recompilation

But the point when I would like to Use DMV over Profiler is, when your application is in development mode you can use SQL profiler wisely running and seeing data. But once application is running on production server it is very time cumbersome to run profiler and then find which procedure have taken maximum time to execute.

In this case you can just run one Query and result will be in front of you….NICE….

Hope this will help you.

Any input always invited.

Thursday, September 2, 2010

CustomValidator validation in Repeater with RegisterExpandoAttribute and ClientValidationFunction JavaScript

Hi All,

ASP.Net comes with many validators, one of the validator is CustomValidator. We can use custom validator in 2 ways, either client side or server side validation.

In this post I will provide information specifically on client side validation. Client side validation is achieved with JavaScript function. JavaScript function sample is as below

function MyClientValidation(source, arguments) {
       if ( your validation condition ){
            arguments.IsValid = true;
       } else {
            arguments.IsValid = false;
       }
}

 

Now we will call above function with ClientValidationFunction property of custom validator mentioned as below.

   1:  <asp:CustomValidator ID="MyValidator" runat="server" ClientValidationFunction="MyClientValidation"></asp:CustomValidator>

 

It is simple to call client validation function with a custom validator.

Now you must have question, why I am writing this basic thing for a client validation. Client validation function is really simple when we write validator and control which we are validating both are on page directly. But there is a scenario where this simple method will not work.

In this post I will explain how to use CustomValidator with client side validation when validator is within a Repeater or a ListView control.

We will go through scenario when a CustomValidator is inside a Repeater control. We will have 3 items in Repeater.

  1. A CheckBox control
  2. A TextBox control
  3. And a CustomValidator

Now as per scenario we need to validate TextBox text when check box for same row is selected.

Below will be html for the Repeater:

<asp:Repeater ID="rptItem" runat="server" OnItemDataBound="rptItem_ItemDataBound">
      <HeaderTemplate>
          <table>
      </HeaderTemplate>
      <ItemTemplate>
          <tr>
              <td>
                  <asp:CheckBox ID="chkSelectItem" runat="server" />
              </td>
              <td>
                  <asp:TextBox ID="txtToValidate" runat="server"></asp:TextBox>
              </td>
              <td>
                  <asp:CustomValidator ID="cValidation" runat="server" ClientValidationFunction="MyClientValidation"
                      ErrorMessage="Invalid"></asp:CustomValidator>
              </td>
          </tr>
      </ItemTemplate>
      <FooterTemplate>
          </table>
      </FooterTemplate>
</asp:Repeater>

I have registered item data bound event over here to do some code.

We have set the html for our scenario, now custom validator need to validate only those textbox for which corresponding checkbox is selected. We will achieve it with ClientScript.RegisterExpandoAttribute method. ClientScript.RegisterExpandoAttribute is used to register custom attributes for your ASP.Net server controls on runtime. Below is the code to register custom attributes.

protected void rptItem_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
      if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
      {
          CheckBox chkSelectItem = e.Item.FindControl("chkSelectItem") as CheckBox;
          TextBox txtToValidate = e.Item.FindControl("txtToValidate") as TextBox;
          CustomValidator cValidation = e.Item.FindControl("cValidation") as CustomValidator;

          if (chkSelectItem != null && txtToValidate != null && cValidation != null)
          {
              ClientScript.RegisterExpandoAttribute(cValidation.ClientID, "chkId", chkSelectItem.ClientID);
              ClientScript.RegisterExpandoAttribute(cValidation.ClientID, "txtId", txtToValidate.ClientID);
          }
      }
}

 

Once we register above method in code behind of page and run the page, it will register some script dynamically as below.

var MainContent_rptItem_cValidation_0 = document.all ? document.all["MainContent_rptItem_cValidation_0"] : document.getElementById("MainContent_rptItem_cValidation_0");
MainContent_rptItem_cValidation_0.chkId = "MainContent_rptItem_chkSelectItem_0";
MainContent_rptItem_cValidation_0.txtId = "MainContent_rptItem_txtToValidate_0";
MainContent_rptItem_cValidation_0.errormessage = "Invalid";
MainContent_rptItem_cValidation_0.evaluationfunction = "CustomValidatorEvaluateIsValid";
MainContent_rptItem_cValidation_0.clientvalidationfunction = "MyClientValidation";

 

Now we need to modify our JavaScript function in such a way that will use our custom registered attributes to validate the control. Modification is as below.

 
function MyClientValidation(source, arguments) {
      var chkBox = document.getElementById(source.attributes["chkId"].value);
      var txtBox = document.getElementById(source.attributes["txtId"].value);
      if (chkBox.checked == true) {
          var re = new RegExp("[0-9]+");
          if (re.test(txtBox.value)) {
              arguments.IsValid = true;
          }
          else {
              arguments.IsValid = false;
          }
      } else {
          arguments.IsValid = true;
      }
}

 

Now we have set all, our validation function will validate textbox with numeric value and non empty and should only validate those textboxes for which checkbox is checked.

Here is the output of our code.

image

You can modify the JavaScript function as per your requirement. also you can register more expando attributes if you require. But note that it will rendered on the client side and will considerably increase your client script.

Now if you are binding your repeater in an UpdatePanel then you need to register the expando attribute somewhat differently as below

   1:  ScriptManager.RegisterExpandoAttribute(MyUpdatePanel, cValidation.ClientID, "chkId", chkSelectItem.ClientID);
   2:  ScriptManager.RegisterExpandoAttribute(MyUpdatePanel, cValidation.ClientID, "txtId", txtToValidate.ClientID);

Hope you liked this post and it helped you.

Any question or suggestion is welcome.

 

Thanks,

Nirav