Thursday, August 25, 2011

ASP.NET Performance Monitoring, and When to Alert Administrators

Monitoring Performance Counters

There are many performance counters available for monitoring applications. Choosing which ones to include in performance logs can be tricky, and learning how to interpret them is an art. This article should help you feel more comfortable with both of these tasks.
At a minimum, the following performance counters should be monitored for Microsoft® ASP.NET applications:
  • Processor(_Total)\% Processor Time
  • Process(aspnet_wp)\% Processor Time
  • Process(aspnet_wp)\Private Bytes
  • Process(aspnet_wp)\Virtual Bytes
  • Process(aspnet_wp)\Handle Count
  • Microsoft® .NET CLR Exceptions\# Exceps thrown / sec
  • ASP.NET\Application Restarts
  • ASP.NET\Requests Rejected
  • ASP.NET\Worker Process Restarts (not applicable to IIS 6.0)
  • Memory\Available Mbytes
  • Web Service\Current Connections
  • Web Service\ISAPI Extension Requests/sec
Below is a larger list of performance counters that are useful for monitoring performance. It's always good to have more performance data than not enough, especially when you experience a problem that is not easily reproduced. The list omits several performance counters that are generally not needed. For example, the session state and transactions performance counters are only necessary when the features are used.
A few thresholds are recommended based upon my experience with debugging and testing ASP.NET applications. You can search this article for "Threshold" to jump right to them. Administrators should determine whether to raise alerts when these thresholds are exceeded based upon their experience. In most cases, alerts are appropriate, especially if the threshold is exceeded for extended periods of time.

Monitoring the Event Log

It is critical to monitor the event log for messages from ASP.NET and Microsoft® Internet Information Server (IIS). ASP.NET writes messages to the application log, for example, each time the aspnet_wp worker process terminates. IIS 6.0 writes messages to both the application and/or system logs, for example, each time the w3wp worker process reports itself unhealthy or crashes. It is quite easy to write a .NET application that reads the application log and filters out messages from ASP.NET and IIS, and fires an alert (sends e-mail or dials a pager) if necessary.

Monitoring the W3C and HTTPERR Logs

First, enable W3C logging for IIS 5.0 and IIS 6.0 through the Internet Information Services (IIS) Manager. This log can be configured to include various data about the requests, such as the URI, status code, and so on. Scan the log for error codes such as 404 Not Found, and take action to correct links, if necessary. On IIS 6.0, the substatus code is included in the log and is useful for debugging. IIS uses substatus codes to indentify specific problems. For example, 404.2 indicates that the ISAPI extension handling the request is locked down. A list of status and substatus codes can be found in the About Custom Error Messages topic.
New for IIS 6.0, malformed or bad requests and requests that fail to be served by an Application Pool are logged to the HTTPERR log by HTTP.SYS, the kernel-mode driver for handling HTTP requests. Each entry includes the URL and a brief description of the error.
Check the HTTPERR log for rejected requests. Requests are rejected by HTTP.SYS when the kernel request queue is exceeded, and when the application is taken offline by the Rapid Fail Protection feature. When the first issue occurs, the URL is logged with the message QueueFull, and when the second occurs, the message is AppOffline. By default, the kernel request queue is set to 1,000, and can be configured on the Application Pool Properties page in IIS Manager. I recommend increasing this to 5,000 for a busy site, since the kernel request queue could easily surpass 1,000 if an Application Pool crashes while a site is under a very high load.
Check the HTTPERR log for requests lost due to a worker process crash or hang. When this occurs the URL will be logged with the message, Connection_Abandoned_By_AppPool, for each in-flight request. An in-flight request is one that was sent to a worker process for processing, but did not complete before the crash or hang.
Details on the HTTPERR Log can be found in Microsoft Knowledge Base Article 820729: INFO: Error Logging in HTTP API.

Other Resources Used to Monitor ASP.NET

Performance counters and the event logs do not catch all errors that occur, and therefore are not entirely sufficient for monitoring ASP.NET. I recommend writing a simple application that sends an HTTP request for one or more pages and expects a certain response. This tool should monitor the time to last byte (TTLB) to ensure that pages are served in a timely manner. It should also record any errors that occur, as this information will be needed to analyze the problem.
The IIS 6.0 Resource Kit includes Log Parser 2.1, a tool for parsing log files (W3C Log, HTTPERR Log, Event Logs) and storing the results in a file or database. The resource kit can be installed on Microsoft® Windows® XP and Microsoft® Windows Server™ 2003.
You might also write an application that collects performance data, filters the event log, and records key data in a Microsoft® SQL Server database. It is amazingly easy to do this using the System.Diagnostics namespace. You can even monitor worker process restarts using the System.Diagnostics.Process class.
To help you get started, use the link at the top of this article to download sample code for several useful tools:
  1. Source code for snap.exe, a command-line tool for logging performance data for processes. The file Snap.cs contains a brief description and explains how to compile the tool.
  2. Source code for HttpClient.exe, a simple client that records time to last byte (TTLB) for HTTP requests. The file HttpClient.cs contains a brief description and explains how to compile the tool.
  3. Source code for qqq.exe, a command-line tool for stress testing an ASP.NET application. When used in combination with a stress client, such as Microsoft® Application Center Test (ACT), this tool will attach debuggers to the worker process and monitor certain performance counters. It can be tuned to break into the debuggers when performance degrades. The file qqq.cs contains a breif description and explains how to compile the tool.
  4. The pminfo.aspx page uses the System.Web.ProcessModelInfo class to display information about process restarts of aspnet_wp. The history is maintained until the w3svc service is stopped.
  5. Source code for ErrorHandler.dll. This is an IHttpModule that you can add to the HTTP pipeline to log unhandled exceptions to the event log. It is better to log errors to a SQL Server database, but the example uses the event log for simplicity.
Another simple step is implementing Application_Error. You can add the following text to global.asax and immediately start logging most unhandled errors to the application log:
<%@ import namespace="System.Diagnostics" %>
<%@ import namespace="System.Text" %>

const string sourceName      = ".NET Runtime";
const string serverName      = ".";
const string logName         = "Application";
const string uriFormat       = "\r\n\r\nURI: {0}\r\n\r\n";
const string exceptionFormat = "{0}: \"{1}\"\r\n{2}\r\n\r\n";

void Application_Error(Object sender, EventArgs ea) {
    StringBuilder message = new StringBuilder();
    
    if (Request != null) {
        message.AppendFormat(uriFormat, Request.Path);
    }
  
    if (Server != null) {
        Exception e;
        for (e = Server.GetLastError(); e != null; e = e.InnerException) {
            message.AppendFormat(exceptionFormat, 
                                 e.GetType().Name, 
                                 e.Message,
                                 e.StackTrace);
        }
    }

    if (!EventLog.SourceExists(sourceName)) {
        EventLog.CreateEventSource(sourceName, logName);
    }

    EventLog Log = new EventLog(logName, serverName, sourceName);
    Log.WriteEntry(message.ToString(), EventLogEntryType.Error);

    //Server.ClearError(); // uncomment this to cancel the error
}

Application_Error will catch parser, compilation, and run-time errors within pages. It will not catch configuration issues, nor will it catch errors that occur within inetinfo while aspnet_isapi processes the request. Also, when using impersonation, the impersonated token must have permission to write to this event source. You may avoid the issue with impersonation by logging errors to a SQL Server database.
Last but not least, the Microsoft® Debugging Tools for Windows are very useful for debugging problems on a production Web server. These tools can be downloaded from http://www.microsoft.com/whdc/ddk/debugging/installx86.mspx. There is a debugger extension named sos.dll that you can load into the debugger windbg.exe or cdb.exe to debug managed code. It can dump contents of the garbage collection (GC) heap, show managed stack traces, aid investigation of contention for managed locks, display thread pool statistics, and much, much more. This can be downloaded as part of the Debugging Toolset mentioned in Production Debugging for .NET Framework Applications.

Understanding the Performance Counters

The following is a brief description of important performance counters and how to use them.

.NET CLR Exceptions Counter

The _Global_ counter instance should not be used with this counter, because it is updated by all managed processes. Instead, use the aspnet_wp instance.
  • #Exceps thrown / sec. The total number of managed exceptions thrown per second. As this number increases, performance degrades. Exceptions should not be thrown as part of normal processing. Note, however, that Response.Redirect, Server.Transfer, and Response.End all cause a ThreadAbortException to be thrown multiple times, and a site that relies heavily upon these methods will incur a performance penalty. If you must use Response.Redirect, call Response.Redirect(url, false), which does not call Response.End, and hence does not throw. The downside is that the user code that follows the call to Response.Redirect(url, false) will execute. It is also possible to use a static HTML page to redirect. Microsoft Knowledge Base Article 312629 provides further detail. In addition to monitoring this very useful performance counter, the Application_Error event should be used in order to alert administrators to problems.
    Threshold: 5% of RPS. Values greater than this should be investigated, and a new threshold should be set as necessary.

.NET CLR Loading Counters

The _Global_ counter instance should not be used with these performance counters, because it is updated by all managed processes. Instead, use the aspnet_wp instance.
  • Current AppDomains. The current number of AppDomains loaded in the process. The value of this counter should be the same as the number of Web applications plus 1. The additional AppDomain is the default domain.
  • Current Assemblies. The current number of assemblies loaded in the process. By default, ASPX and ASCX files in a directory are "batch" compiled. This typically yields one to three assemblies, depending upon dependencies. For example, if there are ASPX pages with parse-time dependencies on ASCX files, two assemblies will typically be generated. One will contain the ASPX files, the other ASCX files. Parse-time dependencies include those created by the <%@ import %>, <%@ reference %>, and <%@ register %> directives. A control loaded through the LoadControl method does not create a parse-time dependency. Note that the global.asax is compiled to an assembly by itself. Occasionally, excessive memory consumption is caused by an unusually large number of loaded assemblies. For example, a site that displays news articles will perform better using a small set of ASPX files that obtain the news from a database than it would were a single ASPX file used for each article. Site designers should attempt to generate content dynamically, make use of caching, and reduce the number of ASPX and ASCX pages.
    Assemblies cannot be unloaded from an AppDomain. To prevent excessive memory consumption, the AppDomain is unloaded when the number of re-compilations (ASPX, ASCX, ASAX) exceeds the limit specified by . Note that if the <%@ page debug=%> attribute is set to true, or if is set to true, batch compilation is disabled.
  • Bytes in Loader Heap. The number of bytes committed by the class loader across all AppDomains. This counter should reach a steady state. If this counter is continuously increasing, monitor the "Current Assemblies" counter. There may be too many assemblies loaded per AppDomain.

.NET CLR Memory Counters

The _Global_ counter instance should not be used with these performance counters, because it is updated by all managed processes. Instead, use the aspnet_wp instance.
  • # Bytes in all Heaps. The number of bytes committed by managed objects. This is the sum of the large object heap and the generation 0, 1, and 2 heaps. These regions of memory are of type MEM_COMMIT (see Platform SDK documentation for VirtualAlloc). The value of this counter will always be less than the value of Process\Private Bytes, which counts all MEM_COMMIT regions for the process. Private Bytes minus # Bytes in all Heaps is the number of bytes committed by unmanaged objects. The first step in the investigation of excessive memory usage is to determine whether it is being used by managed or unmanaged objects. To investigate excessive managed memory usage, I recommend WINDBG.EXE and SOS.DLL, which you can read about in Production Debugging for .NET Framework Applications. SOS.DLL has a "!dumpheap –stat" command that lists the count, size, and type of objects in the managed heap. You can use "!dumpheap –mt" to obtain the address of an object, and "!gcroot" to see its roots. The command "!eeheap" presents memory usage statistics for the managed heaps.
    Another useful tool for diagnosing memory usage is the CLR Profiler, discussed in more detail below.
    Excessive managed memory usage is commonly caused by:
    1. Reading large data sets into memory.
    2. Creating excessive cache entries.
    3. Uploading or downloading large files.
    4. Excessive use of regular expressions or strings while parsing files.
    5. Excessive ViewState.
    6. Too much data in session state or too many sessions.
  • # Gen 0 Collections. The number of times generation 0 objects have been garbage collected. Objects that survive are promoted to generation 1. A collection is performed when room is needed to allocate an object, or when someone forces a collection by calling System.GC.Collect. Collections that involve higher generations take longer, since they are preceded by collections of lower generations. Attempt to minimize the percentage of generation 2 collections. As a rule of thumb, the number of generation 0 collections should be 10 times larger than the number of generation 1 collections, and similarly for generation 1 and 2. The # Gen N Collections counters and the % Time in GC counter are the best for identifying performance issues caused by excessive allocations. See the description for % Time in GC for steps you can take to improve performance.
  • # Gen 1 Collections. The number of times generation 1 objects have been garbage collected. Objects that survive are promoted to generation 2. Threshold: one-tenth the value of # Gen 0 Collections. Applications that perform well follow the rule of thumb mentioned in the description for the # Gen 0 Collections counter.
  • # Gen 2 Collections. The number of times generation 2 objects have been garbage collected. Generation 2 is the highest, thus objects that survive collection remain in generation 2. Gen 2 collections can be very expensive, especially if the size of the Gen 2 heap is excessive. Threshold: one-tenth the value of # Gen 1 Collections. Applications that perform well follow the rule of thumb mentioned in the description for the # Gen 0 Collections counter.
  • % Time in GC. The percentage of time spent performing the last garbage collection. An average value of 5% or less would be considered healthy, but spikes larger than this are not uncommon. Note that all threads are suspended during a garbage collection. The most common cause of a high % Time in GC is making too many allocations on a per request basis. The second most common cause is inserting a large amount of data into the ASP.NET cache, removing it, regenerating it, and reinserting it into the cache every few minutes. There are often small changes that can be made to greatly reduce allocations. For example, string concatenation can be expensive on a per request basis, since the concatenated strings need to be garbage collected. StringBuilder, with an appropriate initial capacity, performs better than string concatenation. However, StringBuilder also needs to be garbage collected, and if used improperly, can result in more allocations than expected as the internal buffers are resized. Calling Response.Write multiple times on each string performs even better than combining them with StringBuilder, so if you can avoid StringBuilder altogther, please do.
    Applications often store data temporarily in a StringBuilder or MemoryStream while generating a response. Instead of recreating this temporary storage on each request, consider implemeting a reusable buffer pool of character or byte arrays. A buffer pool is an object with a GetBuffer and a ReturnBuffer routine. The GetBuffer routine attempts to return a buffer from an internal store of buffers, but creates a new buffer if the store is empty. The ReturnBuffer routine returns the buffer to the store if the maximum number of stored buffers has not yet been reached, but otherwise frees it. The downside to this buffer pool implementation is that it requires locking for thread-safety. Alternatively, you can avoid the performance impact of locking by using HttpContext.ApplicationInstance to access an instance field defined in global.asax. There is one instance of global.asax for each pipeline instance, and thus the field is accessible from only one request at a time, making it a great place to store a reusable character or byte buffer.
    To reduce % Time in GC, you need to know your allocation pattern. Use the CLR Profiler to profile either a single request or a light stress for at most a couple of minutes. (These tools are invasive and are not meant to be used in producton.) The Allocation Graph view displays the total memory allocated for each object type, and the call stack that performed the allocation. Use this to trim down excessive allocations. The Histogram by Size view (select Histogram Allocated Types from the View menu) summarizes the size of the allocated objects. Avoid allocating short-lived objects larger than 85,000 bytes. These objects are allocated in the large object heap, and are more expensive to collect. In the Histogram by Size view, you can select objects with your mouse and right-click to see who allocated them. Reducing allocations is an iterative process of code modifications and profiling.
    Threshold: an average of 5% or less; short-lived spikes larger than this are common. Average values greater than this should be investigated. A new threshold should be set as necessary.

ASP.NET Counters

Performance counters in this category are only reset to 0 when the w3svc service is started.
  • Application Restarts. The number of application restarts. Recreating the application domain and recompiling pages takes time, therefore unforeseen application restarts should be investigated. The application domain is unloaded when one of the following occurs:
    • Modification of machine.config, web.config, or global.asax.
    • Modification of the application's bin directory or its contents.
    • When the number of compilations (ASPX, ASCX, or ASAX) exceeds the limit specified by .
    • Modification of the physical path of a virtual directory.
    • Modification of the code-access security policy.
    • The Web service is restarted.
    For Web farms in production, it is recommended that a server be removed from rotation prior to updating content for best performance and reliability. For a single Web server in production, content can be updated while the server is under load. The hotfix described in Knowledge Base Article 810281 is of interest to anyone experiencing errors after an application restarts, such as sharing violations with an error similar to "Cannot access file because it is being used by another process."
    An issue involving anti-virus software and applications restarts is fixed in Knowledge Base Article 820746: FIX: Some Antivirus Programs May Cause Web Applications to Restart Unexpectedly for v1.0, and in Knowledge Base Article 821438 for v1.1.
    Threshold: 0. In a perfect world, the application domain will survive for the life of the process. Excessive values should be investigated, and a new threshold should be set as necessary.
  • Applications Running. The number of applications running.
  • Requests Current. The number of requests currently handled by the ASP.NET ISAPI. This includes those that are queued, executing, or waiting to be written to the client. This performance counter was added to v1.0 of ASP.NET in the pre-SP3 hotfix described in Knowledge Base Article 329959. ASP.NET will begin to reject requests when this counter exceeds the requestQueueLimit defined in the processModel configuration section. Note that requestQueueLimit applies to ASP.NET on IIS 5.0 when running in aspnet_wp, but perhaps surprisingly, it also applies on IIS 6.0 when running in w3wp. It is not well known that several processModel configuration settings still apply when running in IIS 6.0. These include requestQueueLimit, responseDeadlockInterval, maxWorkerThreads, maxIoThreads, minWorkerThreads, and minIoThreads. A bug in v1.1 of the Framework, fixed in ASP.NET 1.1 June 2003 Hotfix Rollup Package, allowed ASP.NET to handle an infinite number of requests when running in IIS 6.0. The fix causes ASP.NET to reject requests when Requests Current exceeds the requestQueueLimit.
    For classic ASP applications, Requests Queued provides a warning for when requests will be rejected. For ASP.NET, Requests Current, together with Requests in Application Queue, provide this functionality.This counter is also used by the ASP.NET deadlock detection mechanism. If Requests Current is greater than 0 and no responses have been received from the worker process for a duration greater than the limit specified by <processModel responseDeadlockInterval=/>, the process is terminated and a new process is started. In the pre-SP3 hotfix described in Knowledge Base Article 328267, the algorithm was modified so that Requests Current must be greater than the sum of maxWorkerThreads plus maxIoThreads, specified in the <processModel/> configuration section. Note that by default the request execution timeout is 90 seconds, and is intentionally less than responseDeadlockInterval. The request execution timeout can be modified through the <httpRuntime executionTimeout=/> configuration setting or the Server.ScriptTimeout property, but it should always be made less than responseDeadlockInterval.
  • Request Execution Time. The number of milliseconds taken to execute the last request. In version 1.0 of the Framework, the execution time begins when the worker process receives the request, and stops when the ASP.NET ISAPI sends HSE_REQ_DONE_WITH_SESSION to IIS. For IIS version 5, this includes the time taken to write the response to the client, but for IIS version 6, the response buffers are sent asynchronously, and so the time taken to write the response to the client is not included. Thus on IIS version 5, a client with a slow network connection will increase the value of this counter considerably. In version 1.1 of the Framework, execution time begins when the HttpContext for the request is created, and stops before the response is sent to IIS. Assuming that user code does not call HttpResponse.Flush, this implies that execution time stops before sending any bytes to IIS, or to the client for that matter.
    ASP.NET\Request Execution Time is an instance counter, and very volatile. On the other hand, time to last byte (TTLB) can be easily averaged and used to calculate a better estimate of performance over a period of time. TTLB can be calculated through a simple HTTP client written in managed code, or you can use one of the many HTTP clients available that calculate TTLB, such as Application Center Test (ACT).
    Note that if <compilation debug=/> is set to TRUE, then batch compilation will be disabled and the <httpRuntime executionTimeout=/> configuration setting as well as calls to Server.ScriptTimeout will be ignored. This can cause problems if the <processModel responseDeadlockInterval=/> setting is not set to Infinite, since requests for debug pages can theoretically run forever.
    Threshold: N.A. The value of this counter should be stable. Experience will help set a threshold for a particular site. When the process model is enabled, the request execution time includes the time required to write the response to the client, and therefore depends upon the bandwidth of the client's connection.
  • Requests Queued. The number of requests currently queued. When running on IIS 5.0, there is a queue between inetinfo and aspnet_wp, and there is one queue for each virtual directory. When running on IIS 6.0, there is a queue where requests are posted to the managed ThreadPool from native code, and a queue for each virtual directory. This counter includes requests in all queues. The queue between inetinfo and aspnet_wp is a named pipe through which the request is sent from one process to the other. The number of requests in this queue increases if there is a shortage of available I/O threads in the aspnet_wp process. On IIS 6.0 it increases when there are incoming requests and a shortage of worker threads. Note that requests are rejected when the Requests Current counter exceeds the . Many people think this occurs when the Requests Queued counter exceeds requestQueueLimit, but this is not the case. When this limit is exceeded, requests will be rejected with a 503 status code and the message "Server is too busy." If a request is rejected for this reason, it will never reach managed code, and error handlers will not be notified. Normally this is only an issue when the server is under a very heavy load, although a "burst" load every hour might also cause this. For the unusual burst load scenario, you might be interested in the hotfix described in Knowledge Base Article 810259, which will allow you to increase the minimum number of I/O threads from the default of 1 per CPU.
    Each virtual directory has a queue that it uses to maintain the availability of worker and I/O threads. The number of requests in this queue increases if the number of available worker threads or available I/O threads falls below the limit specified by <httpRuntime minFreeThreads=/>. When the limit specified by <httpRuntime appRequestQueueLimit=/> is exceeded, the request is rejected with a 503 status code and the client receives an HttpException with the message "Server too busy."
    By itself, this counter is not a clear indicator of performance issues, nor can it be used to determine when requests will be rejected. In Knowledge Base Article 329959, two new performance counters were introduced to address this problem: Requests Current and Requests In Application Queue. Please see the descriptions for these two counters, as well as for Requests Rejected.
  • Requests Rejected. The number of rejected requests. Requests are rejected when one of the queue limits is exceeded (see description of Requests Queued). Requests can be rejected for a number of reasons. Backend latency, such as that caused by a slow SQL server, is often preceded by a sudden increase in the number of pipeline instances and a decrease in CPU utilization and Requests/sec. A server may be overwhelmed during times of heavy load due to processor or memory constraints that ultimately result in the rejection of requests. An application's design may result in excessive request execution time. For example, batch compilation is a feature in which all the pages in a directory are compiled into a single assembly when the first request for a page is received. If there are several hundred pages in a directory, the first request into this directory may take a long time to execute. If <compilation batchTimeout=/> is exceeded, the batch compilation will continue on a background thread and the requested page will be compiled individually. If the batch compilation succeeds, the assembly will be preserved to disk and can be reused after an application restart. However, if the global.asax, web.config, machine.config, or an assembly in the application's bin folder is modified, the batch compilation process will execute again due to the dependency change.
    Careful design of a large site can have a significant impact upon performance. In this case, it is better to have only a few pages that vary behavior based upon query string data. In general, you need to minimize request execution time, which is best monitored by averaging time to last byte (TTLB) using an HTTP client that requests one or more pages from the Web site.
    The following performance counters are best suited toward discovering the cause of rejected requests:
    • Process\% Processor Time
    • Process\Private Bytes
    • Process\Thread Count
    • Web Service\ISAPI Extension Requests/sec
    • ASP.NET\Requests Current
    • ASP.NET\Requests Queued
    • ASP.NET\Request Wait Time
    • ASP.NET Applications\Pipeline Instance Count
    • ASP.NET Applications\Requests in Application Queue
    Threshold: 0. The value of this counter should be 0. Values greater than this should be investigated.
  • Request Wait Time. The number of milliseconds that the most recent request spent waiting in the queue, or named pipe, that exists between inetinfo and aspnet_wp (see description of Requests Queued). This does not include any time spent waiting in the application queues. Threshold: 1000. The average request should spend 0 milliseconds waiting in the queue.
  • Worker Processes Running. The current number of aspnet_wp worker processes. For a short period of time, a new worker process and the worker process that is being replaced may coexist. Although rare, sometimes processes deadlock while they are exiting. If you suspect this, consider using a tool to monitor the number of instances of the worker process. Alternatively, the Memory\Available Mbytes performance counter can be used, since these hanging processes will consume memory. Threshold: 2. During shutdown of the previous worker process, there may be two instances. If webGarden is enabled, the threshold should be #CPUs plus 1. Values greater than this may indicate excessive process restarts within a very short period of time.
  • Worker Process Restarts. The number of aspnet_wp process restarts. Threshold: 1. Process restarts are expensive and undesirable. Values are dependent upon the process model configuration settings, as well as unforeseen access violations, memory leaks, and deadlocks. If aspnet_wp restarts, an Application Event Log entry will indicate why. Requests will be lost if an access violation or deadlock occurs. If process model settings are used to preemptively recycle the process, it will be necessary to set an appropriate threshold.

ASP.NET Applications Counters

The performance counters in this category are reset to 0 when either the application domain or Web service is restarted.
  • Cache Total Entries. The current number of entries in the cache (both User and Internal). Internally, ASP.NET uses the cache to store objects that are expensive to create, including configuration objects, preserved assembly entries, paths mapped by the MapPath method, and in-process session state objects.
    Note   The "Cache Total" family of performance counters is useful for diagnosing issues with in-process session state. Storing too many objects in the cache is often the cause of memory leaks.
  • Cache Total Hit Ratio. The total hit-to-miss ratio of all cache requests (both user and internal).
  • Cache Total Turnover Rate. The number of additions and removals to the cache per second (both user and internal). A high turnover rate indicates that items are being quickly added and removed, which can be expensive.
  • Cache API Entries. The number of entries currently in the user cache.
  • Cache API Hit Ratio. The total hit-to-miss ratio of User Cache requests.
  • Cache API Turnover Rate. The number of additions and removals to the user cache per second. A high turnover rate indicates that items are being quickly added and removed, which can be expensive.
  • Output Cache Entries. The number of entries currently in the Output Cache.
  • Output Cache Hit Ratio. The total hit-to-miss ratio of Output Cache requests.
  • Output Cache Turnover Rate. The number of additions and removals to the output cache per second. A high turnover rate indicates that items are being quickly added and removed, which can be expensive.
  • Pipeline Instance Count. The number of active pipeline instances. Only one thread of execution can be running within a pipeline instance, so this number gives the maximum number of concurrent requests that are being processed for a given application. The number of pipeline instances should be steady. Sudden increases are indicative of backend latency (see the description of Requests Rejected above).
  • Compilations Total. The number of ASAX, ASCX, ASHX, ASPX, or ASMX files that have been compiled. This is the number of files compiled, not the number of generated assemblies. Assemblies are preserved to disk and reused until either the create time, last write time, or length of a file dependency changes. The dependencies of an ASPX page include global.asax, web.config, machine.config, dependent assemblies in the bin folder, and ASCX files referenced by the page. If you restart the application without modifying any of the file dependencies, the preserved assembly will be reloaded without requiring any compilation. This performance counter will increment only when a file is initially parsed and compiled into an assembly. By default, batch compilation is enabled, however, this counter will increment once for each file that is parsed and compiled into an assembly, regardless of how many assemblies are created.
    If compilation fails, the counter will not be incremented.
  • Errors During Preprocessing. The total number of configuration and parsing errors. This counter is incremented each time a configuration error or parsing error occurs. Even though configuration errors are cached, the counter increments each time the error occurs.
    Note    Do not rely solely upon the "Errors" performance counters to determine whether the server is healthy. They are reset to zero when the AppDomain is unloaded. They can, however, be used to dig deeper into a specific issue. In general, use the Application_Error event in order to alert administrators to problems.
  • Errors During Compilation. The total number of compilation errors. The response is cached, and this counter increments only once until recompilation is forced by a file change. Implement custom error handling to raise an event.
  • Errors During Execution. The total number of run-time errors.
  • Errors Unhandled During Execution. The total number of unhandled exceptions at run time. This does not include the following:
    1. Errors cleared by an event handler (for example, by Page_Error or Application_Error).
    2. Errors handled by a redirect page.
    3. Errors that occur within a try/catch block.
  • Errors Unhandled During Execution/sec. The total number of unhandled exceptions per second at run time.
  • Errors Total. The sum of Errors During Preprocessing, Errors During Compilation, and Errors During Execution.
  • Errors Total/sec. The total of Errors During Preprocessing, Errors During Compilation, and Errors During Execution per second.
  • Requests Executing. The number of requests currently executing. This counter is incremented when the HttpRuntime begins to process the request and is decremented after the HttpRuntime finishes the request. In v1.1 of the Framework, there is a bug in this performance counter that is fixed in the ASP.NET 1.1 June 2003 Hotfix Rollup Package. Unfortunately the bug is not described in the Knowledge Base Article. Prior to the fix, the counter included the time taken to write the response to the client.
  • Requests In Application Queue. The number of requests in the application request queue (see description of Requests Queued above). In addition to Requests Current, Requests in Application Queue provides a warning for when requests will be rejected. If there are only a couple virtual directories, increasing the default appRequestQueueLimit to 200 or 300 may be suitable, especially for slow applications under heavy load.
  • Requests Not Found. The number of requests for resources not found.
  • Requests Not Authorized. The number of request failed due to unauthorized access.
  • Requests Timed Out. The number of requests that have timed out.
  • Requests Succeeded. The number of requests that have executed successfully.
  • Requests Total. The number of requests since the application was started.
  • Requests/Sec. The number of requests executed per second. I prefer "Web Service\ISAPI Extension Requests/sec" because it is not affected by application restarts.

Process Counters

With these counters, the processes of interest are aspnet_wp and inetinfo.
  • % Processor Time. The percentage of time the threads of this process spend using the processors. Threshold: 70%. Values greater than this for extended periods of time indicate a need to purchase hardware or optimize your application.
  • Handle Count. Threshold: 10000. A handle count of 2000 in aspnet_wp is suspicious, and 10,000 is far beyond acceptable limits. Noticeable performance degradation will occur if the total handle count for all processes exceeds approximately 40,000, which is entirely achievable during a denial-of-service attack against IIS.
  • Private Bytes. The current size, in bytes, of the committed memory owned by this process. Memory leaks are identified by a consistent and prolonged increase in Private Bytes. This is the best performance counter for detecting memory leaks. When running on IIS 5.0, a memory limit for Private Bytes should be set in the configuration section. When running on IIS 6.0, the memory limit should be set in IIS Manager. Open Properties for the application pool, and on the Recycling tab, specify a limit for Maximum used memory (in megabytes). This limit corresponds to Private Bytes. Private Bytes for the worker process is compared with the memory limit to determine when to recycle the process. System.Web.Caching.Cache also uses Private Bytes and the memory limit to determine when to expunge items from the cache, and thus avoid recycling the process. A memory limit of 60% of physical RAM is recommended to avoid paging, especially when a new process replaces the old one due to excessive memory consumption. Note that Knowledge Base Article 330469 resolves a problem with ASP.NET in which it fails to monitor Private Bytes on servers with a large number of running processes. This hotfix also enables the cache memory manager to function properly when there are a large number of running processes.
    It is important to adjust the memory limit on machines with large amounts of physical RAM, so that the cache memory manager and process recycling function properly. For example, assume you have a server with 4 gigabytes (GB) of physical RAM that is using the default memory limit. This is a problem. Sixty percent of physical RAM is 2.4 GB, which is larger than the default virtual address space of 2 GB. So what should the memory limit be set to?
    There are a couple things to consider: First, the likelihood of experiencing an OutOfMemoryException begins to increase dramatically when "Process\Virtual Bytes" is within 600 MB of the virtual address space limit (generally 2 GB), and secondly, tests have shown that "Process\Virtual Bytes" is often larger than "Process\Private Bytes" by no more than 600 MB. This difference is due in part to the MEM_RESERVE regions maintained by the GC, allowing it to quickly commit more memory when needed. Taken together this implies that when "Process\Private Bytes" exceeds 800 MB, the likelihood of experiencing an OutOfMemoryException increases. In this example the machine has 4 GB of physical RAM, so you need to set the memory limit to 20% to avoid out-of-memory conditions. You might experiment with these numbers to maximize the usage of memory on a machine, but if you want to play it safe, the numbers in the example will work.
    To summarize, set the memory limit to the smaller of 60% of physical RAM or 800 MB. Since v1.1 supports 3 GB virtual address space, if you add /3GB to boot.ini, you can safely use 1,800 MB instead of 800 MB as an upper bound.
    Note that when running tests, if you would like to force a GC and stabilize managed memory, you can call System.GC.GetTotalMemory(true) once. This method will call GC.Collect and WaitForPendingFinalizers() repeatedly until the memory stabilizes within 5%.
    Threshold: the minimum of 60% of physical RAM and 800 MB. Values greater than 60% of total physical RAM begin to have an impact upon performance, especially during application and process restarts. The liklihood of an OutOfMemoryException greatly increases when Private Bytes exceeds 800 MB in a process with a virtual address space limit of 2 GB.
  • Thread Count. The number of threads active in this process. Thread count often increases when the load is too high. Threshold: 75 + ((maxWorkerThread + maxIoThreads) * #CPUs). The threshold should be increased if aspcompat mode is used: Threshold: 75 + ((maxWorkerThread + maxIoThreads) * #CPUs * 2).
  • Virtual Bytes. The current size, in bytes, of the virtual address space for this process. The virtual address space limit of a user mode process is 2 GB, unless 3 GB address space is enabled by using the /3GB switch in boot.ini. Performance degrades as this limit is approached, and typically results in a process or system crash. The address space becomes fragmented as the 2 GB or 3 GB limit is approached, and so I recommend a conservative threshold of 1.4 or 2.4 GB, respectively. If you're running into issues here, you will see System.OutOfMemoryException being thrown, and this may or may not crash the process.
    When running on IIS 6.0, a virtual memory limit can be set in IIS Manager. However, setting this improperly can cause problems for ASP.NET. ASP.NET expunges items from the cache to avoid exceeding the Private Bytes limit, but the algorithm uses Private Bytes and the Private Bytes limit in this determination. It does not monitor Virtual Bytes or the Virtual Bytes limit. Given that the difference between Virtual Bytes and Private Bytes is typically no more than 600 MB, you could set the Virtual Bytes limit to a value 600 MB larger than the Private Bytes limit if you are concerned about the possibility of virtual memory leaks or fragmentation. If this is desirable, set a limit for Maximum virtual memory (in megabytes), found on the Recycling tab for the Properties of the application pool.
    Version 1.0 of the Framework does not support 3 GB address space in the worker process or the state service. However, see Knowledge Base Article 320353 for instructions to enable 3 GB address space within inetinfo.exe. Version 1.1 fully supports 3 GB address space for the worker process and state service.
    Threshold: 600 MB less than the size of the virtual address space; either 1.4 or 2.4 GB.

Processor Counter

  • % Processor Time. The percentage of time all threads spend using the processors. Threshold: 70%. Values greater than this for extended periods of time indicate a need to purchase hardware or optimize your application.

Memory Counter

  • Available Mbytes. The amount of physical RAM available. Threshold: 20% of physical RAM. Values less than this should be investigated and may indicate a need to purchase hardware.

System Counter

  • Context Switches/sec. The rate at which the processors switch thread contexts. A high number may indicate high lock contention or transitions between user and kernel mode. Context Switches/sec should increase linearly with throughput, load, and the number of CPUs. If it increases exponentially, there is a problem. A profiler should be used for further investigation.

Web Service Counters

  • Current Connections. A threshold for this counter is dependent upon many variables, such as the type of requests (ISAPI, CGI, static HTML, and so on), CPU utilization, and so on. A threshold should be developed through experience.
  • Total Method Requests/sec. Used primarily as a metric for diagnosing performance issues. It can be interesting to compare this with "ASP.NET Applications\Requests/sec" and "Web Service\ISAPI Extension Requests/sec" in order to see the percentage of static pages served versus pages rendered by aspnet_isapi.dll.
  • ISAPI Extension Requests/sec. Used primarily as a metric for diagnosing performance issues. It can be interesting to compare this with "ASP.NET Applications\Requests/sec" and "Web Service\Total Method Requests/sec." Note that this includes requests to all ISAPI extensions, not just aspnet_isapi.dll.

Conclusion

Careful stress and performance testing of an application before going live can prevent a major headache. There seem to be two major stumbling blocks that many people encounter:
  1. You need to use an HTTP client capable of simulating the traffic and load that you expect the Web site to experience.
  2. You need to test the entire application in an environment nearly identical to the production environment.
It's not easy simulating real Web site traffic, but I can honestly say that most of the applications that experience trouble were never adequately stress tested. This article should help you understand performance counters and create some useful tools for monitoring performance. To apply the load, I recommend Microsoft Application Center Test (ACT), which is included with Microsoft® Visual Studio® .NET. You can read more about this stress tool at the Microsoft Application Center Test 1.0, Visual Studio .NET Edition. I also recommend Microsoft® Web Application Stress Tool (WAST). This can be downloaded for free from TechNet. If your application uses ViewState, you'll need to use ACT since WAST cannot dynamically parse the response.
I don't know what it is about production environments, but there is definitely something special about them. I cannot count the times I've heard the statement, "The problem only occurs on our production site." Typically the difference is the application itself. There is often some part of the application that cannot be simulated in the lab. For example, the ad server was omitted from testing, or the database used to simulate the real database is substantially different. Sometimes network or DNS differences are the cause, and sometimes it's a difference in the hardware on which the servers run.
I've been debugging and monitoring the performance of ASP.NET applications for several years, yet there are still times when I need help. If you find yourself in this position, the forums on the ASP.NET Web site are a good place to go for answers. But if you're really in a bind, don't hesitate to contact Microsoft Product Support using the contact information supplied on that site. Note that if a problem is determined by Microsoft to be the result of a defect in a Microsoft product, you will not be charged for that incident.
Hopefully this document has equipped you with the tools and information that you need to ensure the reliability and performance of your application. If you have any questions, post them on ASP.NET Web and I'll do my best to answer them. Good luck!

About the Author

Thomas Marquardt is a developer on the ASP.NET team at Microsoft. He's been debugging and investigating performance issues with ASP.NET applications since the winter of 2000. Thomas would like to thank Dmitry Robsman, the Development Manager for ASP.NET at Microsoft, for hours and hours of help and guidance over the years.

Possibly a faster DBCC CHECKDB

This article describes a utility that is able to report the most common DBCC CHECKDB errors significantly faster than DBCC CHECKDB does itself. This can be very important in determining quickly the correctness of data in large databases.
Now that I have your attention I should point out this utility is NOT a replacement for DBCC CHECKDB. However, I believe it will report on the most common cause of errors significantly faster that the usual DBCC CHECKDB routines.

Background

DBCC CHECKDB is great utility for ensuring data is correct. In my experience, most shops that have large databases run it on a weekly basis, since it can be a time consuming operation. It is a typical and recommended practice to restore a backup of the production database to another server and the DBCC CHECKDB is performed against this, thus freeing up the production server for other work.

The Problem

The main problem I hear about DBCC CHECKDB is that for large databases in particular, it can take a long time to run. This is exacerbated by the fact that if errors are found, it digs deeper into the data and takes even longer to complete.
I've known a typical DBCC CHECKDB on a 5 terabyte databases to take more than 10 hours to complete. However, when there were errors, the DBCC CHECKDB took in excess of 48 hours to complete.
It is possible to run a cut down version of DBCC CHECKDB using the PHYSICAL_ONLY option, however, even this takes significantly longer to run than the utility proposed in this article.

The Utility

The T-SQL code used to create this utility is attached to this article. The utility described here has the following advantages over DBCC CHECKDB:
  • It runs much faster (typically in 10% of the time of DBCC CHECKDB)
  • It stops immediately you get an error. This can represent a considerable saving in time over DBCC CHECKDB. On average it would identify an error in less than 5% of the time DBCC CHECKDB would take. Additionally DBCC CHECKDB takes even longer if an error is found.
  • It processes heaps and clustered indexes first, this is important in determining how critical a problem is. A problem with heaps and clustered indexes is more serious since non-clustered indexes there can be rebuild from the heaps/clustered indexes.
There are also some limitations:
  • It only reports on IO errors. However, a short email conversation with Paul Randal (who has written much of the DBCC CHECKDB code), confirmed that most errors from DBCC are due to IO problems. So maybe this limitation is not much of a disadvantage.
  • Assumes the databases's page_verify_option is set to 2 (CHECKSUM). This is a typical and sensible setting.

How it works

This utility reads every page of the databases' heaps, clustered and non-clustered indexes, reporting immediately when it encounters any IO problems.
The first part of the script clears the buffer pool. This is the area of memory that contains data that has been read from the underlying physical disks. The buffer pool allows much faster access to data that is needed again. By clearing the buffer pool, using the command DBCC DROPCLEANBUFFERS, we ensure any data is read from the underlying physical disks, and this is where we want to find any errors.
The next part of the script gets details of the heaps, clustered indexes and non-clustered indexes to check. These details are stored in a temporary table named #IndexDetails.
Next we dynamically build up the SQL we want to execute. For each heap, clustered index and non-clustered index in #IndexDetails, we create the SQL that will count the number of rows in the underlying table, using an index hint. This index hint forces the underlying data to be read using the index number associated with the named heap, clustered index, or non-clustered index. Since we have cleared the buffer pool, the data is read from the physical disk, thus allowing us to check for any IO errors.
A typical example of the SQL we want to execute is:
SELECT COUNT_BIG(*) AS [TableName: NameOfTable. IndexName: NameOfIndex. IndexId: 1] 
 FROM [dbo].[NameOfTable] WITH (INDEX( 1));
The keyword COUNT_BIG is used because the tables may have more rows than the maximum value of the int data type.
The heap and clustered indexes are processed first (as shown by the ORDER BY statement, heaps have an indexid of 0 and clustered indexes have an indexid of 1). This ensures the most important underlying data structures (i.e. heaps and clustered indexes) are processed first.
For each heap, clustered index and non-clustered index processed, the name of the table, index, and indexid is output. This can be used to show the progress of the executing SQL. An example of this output is shown in figure 1 below:

Figure 1: example output showing the underlying table, index and indexid.
If an error occurs, the script stops, and an error message output. You can determine the heap/index in error by comparing the expected normal output (shown in figure 1) with the debug output shown in the message tab of SQL Server management Studio. Additionally inspecting the table msdb..suspect_pages will also provide error details.
An example error message is given below:
Msg 824, Level 24, State 2, Line 3
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:28254611; actual 0:0). It occurred during a read of page (1:28254611) in database ID 10 at offset 0x000035e4326000 in file 'K:\MSSQL.1\MSSQL\Data\Paris_Paris3.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Conclusion

The utility provided in this article can identify the most common IO errors significantly faster than DBCC CHECKDB. As such it should be useful to quickly identify errors. It should be noted again, this is NOT a replacement for DBCC CHECKDB which does many more things.

Credits

Ian Stirk has been working in IT as a developer, designer, and architect since 1987. He holds the following qualifications: M.Sc., MCSD.NET, MCDBA, and SCJP. He is a freelance consultant working with Microsoft technologies in London England. His new book, SQL Server DMVS in Action, was published in May 2011. He can be contacted at Ian_Stirk@yahoo.com.

The SQL Code

-- I. Stirk. ian_stirk@yahoo.com LightweightPageChecker utility...
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED-- Ensure buffer pool is empty.
DBCC DROPCLEANBUFFERS
-- Get details of all heaps, clustered indexes and non-clustered indexes to check.
SELECT 
 ss.name AS SchemaName
 , st.name AS TableName
 , s.name AS IndexName 
 , s.rowcnt AS 'Row Count' 
 , s.indidINTO #IndexDetailsFROM sys.sysindexes s INNER JOIN sys.tables st ON st.[object_id] = s.[id]
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
WHERE s.id > 100 -- Only user tables
AND s.rowcnt >= 1 -- Ignore stats rows 
ORDER BY s.indid, [Row Count] DESC -- Do heaps and clustered first
DECLARE @CheckIndexesSQL NVARCHAR(MAX)
SET @CheckIndexesSQL = ''
-- Build SQL to read each page in each index (including clustered index).
SELECT @CheckIndexesSQL = @CheckIndexesSQL + CHAR(10) 
 + 'SELECT COUNT_BIG(*) AS [TableName: ' + SchemaName + '.' 
 + TableName + '. IndexName: ' + ISNULL(IndexName, 'HEAP') 
 + '. IndexId: ' + CAST(indid AS VARCHAR(3)) + '] FROM ' 
 + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) 
 + ' WITH (INDEX(' + CAST(indid AS VARCHAR(3)) + '));'
FROM #IndexDetails-- Debug.
DECLARE @StartOffset INT
DECLARE @Length INT
SET @StartOffset = 0
SET @Length = 4000
WHILE (@StartOffset < LEN(@CheckIndexesSQL))
BEGIN
 PRINT SUBSTRING(@CheckIndexesSQL, @StartOffset, @Length)
 SET @StartOffset = @StartOffset + @LengthEND
PRINT SUBSTRING(@CheckIndexesSQL, @StartOffset, @Length)
-- Do work.
EXECUTE sp_executesql @CheckIndexesSQL-- Tidy up.
DROP TABLE #IndexDetails

Resources:

A faster DBCC.doc

Friday, August 19, 2011

Resize my browser

Hi All,

Ever tried to resize your browser to check how your website adapts with different resolution "in a flash"? Here is the link that helps you do that.

EPPlus - Open Source Excel export library for Excel 2007/2010 using .NET

Create advanced Excel 2007/2010 spreadsheets on the server

EPPlus is a .net library that reads and writes Excel 2007/2010 files using the Open Office Xml format (xlsx).
 
EPPlus supports:
  • Cell Ranges
  • Cell styling (Border, Color, Fill, Font, Number, Alignments)
  • Charts
  • Pictures
  • Shapes
  • Comments
  • Tables
  • Protection
  • Encryption
  • Pivot tables
  • Data validation
  • Many more...

Overview

This project started with the source from ExcelPackage. It was a great project to start from.
It had the basic functionality needed to read and write a spreadsheet.
Advantages over other:
  • Totally rewritten using dictionaries
  • Can now load 50 000 cells in seconds
  • Complete integration with .NET

Examples

To see how this works let’s do a short walkthrough of sample 6 that creates a report on a directory in the file system.
The spreadsheet is created without any template.
First sheet is a list of subdirectories and files, with an icon, name, size, and dates. The second sheet contains some statistics...
Version 2.8 has added support for enumeration of cells....
 Heres an example how you can use EPPlus in a webapplication...
 Here's a few screenshots from the sample project...

Find more here...

Wealth of Sql and Relational DB resources

Hi All,

Here is the link to get the wealth of information for Sql and Relational DB System

Sql Wealth

Understanding the SQL Server NOLOCK hint

Problem

I see the use of the NOLOCK hint in existing code for my stored procedures and I am not exactly sure if this is helpful or not.  It seems like this has been a practice that was put in place and now is throughout all of the code wherever there are SELECT statements.  Can you explain the what NOLOCK does and whether this is a good practice or not?

Solution

It seems that in some SQL Server shops the use of the NOLOCK (aka READUNCOMMITED) hint is used throughout the application.  In this tip we take a closer look at how this works and what the issues maybe when using NOLOCK.

Example

Let's walk through some simple examples to see how this works. (These queries are run against the AdventureWorks database.)
Here is a query that returns all of the data from the Person.Contact table. If I run this query I can see there is only one record that has a Suffix value for ContactID = 12.
SELECT * FROM Person.Contact WHERE ContactID < 20

Let's say another user runs the below query in a transaction.  The query completes and updates the records, but it is not yet committed to the database so the records are locked.
-- run in query window 1
BEGIN TRAN
UPDATE Person.Contact SET Suffix = 'B' WHERE ContactID < 20
-- ROLLBACK or COMMIT
If I run the same query from above again you will notice that it never completes, because the UPDATE has not yet been committed.
-- run in query window 2
SELECT * FROM Person.Contact WHERE ContactID < 20
If I run sp_who2 I can see that the SELECT statement is being blocked.  I will need to either cancel this query or COMMIT or ROLLBACK the query in window one for this to complete.  For this example I am going to cancel the SELECT query.

To get around the locked records, I can use the NOLOCK hint as shown below and the query will complete even though the query in window 1 is still running and has not been committed or rolled back.
-- run in query window 2
SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20
If you notice below the Suffix column now has "B" for all records.  This is because the UPDATE in window 1 updated these records.  Even though that transaction has not been committed, since we are using the NOLOCK hint SQL Server ignores the locks and returns the data.  If the UPDATE is rolled back the data will revert back to what it looked like before, so this is considered a Dirty Read because this data may or may not exist depending on the final outcome in query window 1.

If I rollback the UPDATE using the ROLLBACK command and rerun the SELECT query we can see the Suffix is back to what it looked like before.
-- run in query window 1
ROLLBACK

-- run in query window 2
SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20
-- or
SELECT * FROM Person.Contact WHERE ContactID < 20

So the issue with using the NOLOCK hint is that there is the possibility of reading data that has been changed, but not yet committed to the database.  If you are running reports and do not care if the data might be off then this is not an issue, but if you are creating transactions where the data needs to be in a consistent state you can see how the NOLOCK hint could return false data.

Locks

So what kind of locking is used when the NOLOCK hint is used.
If we run our SELECT without NOLOCK we can see the locks that are taken if we use sp_lock.  (To get the lock information I ran sp_lock in another query window while this was running.)
SELECT * FROM Person.Contact WHERE ContactID < 20

If we do the same for our SELECT with the NOLOCK we can see these locks.
SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20


The differences are that there is a "S" shared access lock that is put on the page (PAG) that we are reading for the first 19 rows of data in the table when we don't use NOLOCK.  Also, we are getting a Sch-S lock versus an IS lock for the table (TAB). 
So another thing to point out is that even when you just SELECT data SQL Server still creates a lock to make sure the data is consistent.
These are the lock types and the lock modes that are used for the above two queries.

Lock Types

  • MD - metadata lock
  • DB - database lock
  • TAB - table lock
  • PAG - page lock

Mode

  • S - Shared access
  • Sch-S - Schema stability makes sure the schema is not changed while object is in use
  • IS - Intent shared indicates intention to use S locks

READUNCOMMITED

The NOLOCK hint is the same as the READUNCOMMITED hint and can be used as follows with the same results.
SELECT * FROM Person.Contact WITH (READUNCOMMITTED)

SELECT statements only

The NOLOCK and READUNCOMMITED hints are only allowed with SELECT statements. If we try to use this for an UPDATE, DELETE or INSERT we will get an error.
UPDATE Person.Contact with (NOLOCK) SET Suffix = 'B' WHERE ContactID < 20

Msg 1065, Level 15, State 1, Line 15
The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE, DELETE or MERGE statements.

Schema Change Blocking

Since a NOLOCK hint needs to get a Sch-S (schema stability) lock, a SELECT using NOLOCK could still be blocked if a table is being altered and not committed. Here is an example.
-- run in query window 1
BEGIN TRAN
ALTER TABLE Person.Contact ADD column_b VARCHAR(20) NULL ;
If we try to run our SELECT statement it will be blocked until the above is committed or rolled back.
-- run in query window 2
SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20

Issues

We mentioned above how you can get dirty reads using the NOLOCK hint.  These are also other terms you may encounter for this hint.
  • Dirty Reads - this occurs when updates are done, so the data you select could be different.
  • Nonrepeatable Reads - this occurs when you need to read the data more than once and the data changes during that process
  • Phantom Reads - occurs where data is inserted or deleted and the transaction is rolled back.  So for the insert you will get more records and for the delete you will get less records.
To learn more about these issues read this article: http://msdn.microsoft.com/en-us/library/ms190805.aspx

Isolation Level

You can also set the Isolation Level for all queries instead of using the NOLOCK or READUNCOMMITTED hint. The isolation level will apply the READUNCOMMITTED to all SELECT statements that are performed from when this is turned on until it is turned off.
In the example below, the two SELECT statements will use the READUNCOMMITED or NOLOCK hint and the UPDATE will still function as normal.  This way you can set a whole batch of statements instead of modifying each query.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- turn it on

   SELECT * FROM Person.Contact WHERE ContactID < 20

   UPDATE Person.Contact SET Suffix = 'B' WHERE ContactID = 1

   SELECT * FROM Person.Contact WHERE ContactID < 20

SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- turn it off

Next Steps

  • Now that you have a better understanding of how this works check your code to see if there are instances where the NOLOCK hint doesn't make sense.

Friday, August 12, 2011

Creating and Using a CAPTCHA in ASP.NET MVC

A CAPTCHA is a challenge-response system wherein typically a dynamically generated image consisting of random letters and numbers is displayed to the users and the users are asked to re-enter the text in an attempt to verify that both of them match. Any ASP.NET developer would want to prevent spam and automated form submissions in the website being developed and that is where CAPTCHA is extremely useful. The primary purpose of CAPTCHA is to ensure that data is being submitted by humans and not by an automated software system. CAPTCHA is frequently used in user registration and contact forms to prevent spam and abuse of the facility. In this article you will learn how a simple CAPTCHA system can be developed in ASP.NET MVC.

Basic Steps

Before you delve into further details create a new ASP.NET MVC 3 application with ASPX views. Once created add a new controller named Home into the Controllers folder. You will be adding the required action methods to the Home controller later.

Creating a Custom Action Result

An action result indicates the response from an action method and is represented by System.Web.Mvc.ActionResult class. Though most of the action methods can use ActionResult class as their return value you can also create a custom action result class. In fact, the MVC framework includes several classes that inherit from the ActionResult base class. Many of them are shown in the following figure.
The MVC framework includes several classes
Figure 1: The MVC framework includes several classes

In order to create a CAPTCHA system it is necessary that we emit a dynamically generated image with random text onto the response stream. To meet this requirement you will create a custom Action Result class. The new action result will inherit from ActionResult base class. To create a new action result, add a new class in the Models folder and name it CaptchaImageResult. The following listing shows the empty CaptchaImageResult class.


public class CaptchaImageResult:ActionResult
{
}

Generating Random Strings

The CaptchaImageResult class needs to display random string values consisting of letters and numbers. To generate such random strings you will need to create a method inside the CaptchaImageResult class that does the job for you. The GetCaptchaString() method as shown below is responsible for creating fixed length strings consisting of alpha-numeric values.
public string GetCaptchaString(int length)
{
    int intZero = '0';
    int intNine = '9';
    int intA = 'A';
    int intZ = 'Z';
    int intCount = 0;
    int intRandomNumber = 0;
    string strCaptchaString="";
 
    Random random = new Random(System.DateTime.Now.Millisecond);
 
    while (intCount < length)
    {
        intRandomNumber = random.Next(intZero, intZ);
        if (((intRandomNumber >= intZero) && (intRandomNumber <= intNine) || (intRandomNumber >= intA) && (intRandomNumber <= intZ)))
        {
            strCaptchaString = strCaptchaString + (char)intRandomNumber;
            intCount = intCount + 1;
        }
    }
    return strCaptchaString;
}
As you can see, the GetCaptchaString() method accepts a single parameter - length - that indicates the length of the resultant CAPTCHA string. The four variables intZero, intNine, intA and intZ simply store ASCII values for numbers (0-9) and letters (A-Z). Note that we are using only upper case letters in the above logic. You can, of course, add lower case letters if required. Inside the while loop, Random class is used to generate random numbers between a range of 0-9 and A-Z. The random number obtained from the Random class is converted into a character value and stored in strCaptchaString variable. Finally, the complete CAPTCHA string is returned back.

Outputting an Image Dynamically in the Response Stream

Generating a random string is just one part of the story. More important is to emit this random string as an image in the response stream. Emitting the random string as an image will make it difficult for automated software programs to read the CAPTCHA string from the HTML source. To generate an image dynamically, you need to override ExecuteResult() method of the ActionResult base class. The ExecuteResult() method is responsible for any custom processing that the child class wants to plug-in. The complete ExecuteResult() method is shown below:
public override void ExecuteResult(ControllerContext context)
{
    Bitmap bmp = new Bitmap(100, 30);
    Graphics g = Graphics.FromImage(bmp);
    g.Clear(Color.Navy);
    string randomString = GetCaptchaString(6);
    context.HttpContext.Session["captchastring"] = randomString;
    g.DrawString(randomString, new Font("Courier", 16), new SolidBrush(Color.WhiteSmoke), 2, 2);
    HttpResponseBase response = context.HttpContext.Response;
    response.ContentType = "image/jpeg";
    bmp.Save(response.OutputStream,ImageFormat.Jpeg);
    bmp.Dispose();
}
The ExecuteResult() method has one parameter - ControllerContext - that provides the context information in which the method is being called. For example, the HTTP response stream is accessed using the HttpContext provided by the ControllerContext parameter. The method first creates a Bitmap of size 100 X 30. It then washes it with a Navy background. A random CAPTCHA string with a length of 6 characters is generated by calling the GetCaptchaString() method. This random string is then stored in a Session variable so that you can compare the user input later. The DrawString() method of the Graphics object draws the random string onto the image. The image is then saved onto the response stream in JPEG format.
In the above example, you are using a simplistic approach of generating CAPTCHA images. You can also make use of certain techniques (distorting the characters or emitting two sets of characters for example) that make it even more difficult for the automated software programs to read them.

Showing a CAPTCHA Image on a View

Next, you need to create an action method in the Home controller as shown below:
public CaptchaImageResult ShowCaptchaImage()
{
    return new CaptchaImageResult();
}
The ShowCaptchaImage() method simply returns a new instance of CaptchaImageResult class you created earlier. Note that the MVC framework takes care of calling the ExecuteResult() overridden method for you.
The ShowCaptchaImage() action method is used in the Index view as shown below:

Captcha in ASP.NET MVC

<% using (Html.BeginForm("index", "home")) { %> Please enter the string as shown above: <%= Html.TextBox("CaptchaText")%> <% } %> <%= ViewBag.Message %>
As you can see, the above markup creates an HTML form. Notice how the src attribute of the tag is pointing to the ShowCaptchaImage action method. This way whenever the image is rendered it invokes the ShowCaptchaImage() method and a CAPTCHA image is outputted. The TextBox allows the user to enter the text as seen on the CAPTCHA.
The following figure shows the Index view in action:
The Index View
Figure 2: The Index View

Verifying the CAPTCHA Value

Before you run the Index view, you need to add Index() action methods as shown below:
public ActionResult Index()
{
    ViewBag.Message = "A fresh CAPTCHA image is being displayed!";
    return View();
}
 
[HttpPost]
public ActionResult Index(string CaptchaText)
{
    if (CaptchaText == HttpContext.Session["captchastring"].ToString())
        ViewBag.Message = "CAPTCHA verification successful!";
    else
        ViewBag.Message = "CAPTCHA verification failed!";
 
    return View();
}
The first Index() action method takes care of GET requests whereas the second one takes care of POST requests (as indicated by [HttpPost] attribute). The first Index() method simply puts a Message in the ViewBag and shows the Index view (Figure 2). When the user enters the CAPTCHA value in the TextBox and submits the form, the second Index() action method compares the submitted value with the value stored in the Session earlier (recollect that ExecuteResult() method stores the CAPTCHA text in a session variable). Depending on the outcome of the comparison either a success or failure message is displayed in the view.

Summary

A CAPTCHA is a challenge-response system frequently used to keep away spammers and automated software programs from web pages accepting data from the end users. Common examples of such pages include user registration forms and contact forms. In order to create your own CAPTCHA system in ASP.NET MVC you create a custom ActionResult class that generates a random string value and then emits it as an image on the response stream. You can add more features to the CAPTCHA you developed in this article to make it more difficult for automated software programs to read the CAPTCHA text.

Thursday, August 11, 2011

Save Performance Counters in Windows Server 2008 or Windows 7

If you’re writing multi-user applications like just about any ASP.NET application, it’s probably worth your time to get familiar with performance counters.  Performance Monitor, or perfmon for short, is the tool you use to view these counters on your computer or server.  You can add it to an MMC instance (run MMC and add the performance snap-in) or you can just run perfmon.  (Sidenote – I once got dinged by a manager for mis-spelling perfmon in a proposal for a client.  I forget what he thought I was trying to spell but it made no sense.  In any event, trust me, perfmon is really how it’s spelt).  There are about 3 counters that you can work with in Windows.  Wait, did I say 3?  I meant about 3000+.  Of these, there really are only a few that are going to be of interest to you at any particular moment, and the rest are just noise.  Once you’ve identified the ones you care about, it’s nice to be able to save those to the desktop or a sync folder so that you can easily view them again later.
Performance Monitor
In the old days…
Once upon a time, it was possible to just save the settings of a perfmon session.  Then you could just double-click on the saved file, and voila! You had your settings running again.
Sadly, this no longer works,  and if you’re used to this simple approach, it can be quite difficult to figure out how to achieve this same functionality using the latest Windows OSes (Windows Server 2008, Windows 7, et al).  Thanks to James Kehr (of ORCSWeb fame) for providing me with the answer to this. 
Saving and Reloading Performance Counters with Perfmon
There are actually a few ways you can achieve this.  The first one is the one I find myself using the most, although the latter is really the way I was familiar with in previous OSes.  You can copy the properties of a given perfmon session by clicking on the Copy Properties icon ( copy) next to the highlight icon above the graph.  Next, open up Notepad and paste into it.  Save this file somewhere handy.  Next time you want to get back to the settings you were using, just open the file, copy all of the text in it, and click on the Paste Properties icon (image).  If you’re interested, here’s what the data looks like (click to enlarge):
Performance Monitor Properties
Another way you can save your settings it through Data Collector Sets.  Once you have one defined and have run it to completion, you’ll have Reports you can view.  You can copy and paste counters from one report to another.  So, if you define a Data Collector Set and add some performance counters to it, then run it, you’ll have some reports to look at.  You can look at any of these reports and simply highlight the counters you want, Copy, and then go back up to Performance Monitor (the live view) and Paste, and you’ll have the counters.  This is what I do now in practice, since the counters I want to watch are in user defined Data Collector Sets now already (so I can view a whole day or arbitrary period in a day after-the-fact). 
The third solution is to use the MMC.  Run mmc from Start-Run, then go to File – Add or Remove Snap-ins.  Add Performance to the selected snap-ins:
mmc - performance
Add whatever counters you want, then simply save it.  Back when I used to do this all the time I would have a perfmon.msc file on my server’s desktop that had the counters I was most interested in.
MMC-Save
Close the MMC and you should be able to open the saved .msc file by simply double-clicking on it.  This is exactly the behavior that I was used to on previous versions of Windows Server.  Apparently the change is that perfmon no longer launches the performance monitor tool inside an MMC instance, but rather as a standalone process (which doesn’t directly support Save).
Ref: http://stevesmithblog.com/blog/how-do-i-save-performance-counters-in-windows-server-2008-or-windows-7/