Today, I learnt something new with SQL Server Profiler while doing performance testing of an internal Microsoft application. It is about SQL Server Blocked Process Report. Using SQL Server Blocked Process report, one can find the portion of the code that is spending most time in blocked state.
The Errors and Warnings Event has a sub-event called Blocked Process Report in SQL Profiler. Generally, in the traces obtained from SQL Profiler, you won’t notice this event being recorded if you have enabled all the sub-events under Errors and Warnings. This is because by default Blocked process report event is disabled on the SQL Server.
To turn-on the Blocked Process report on the SQL Profiler, run the following commands.
This will turn-on the advance options. Now, run this command to turn-on the Blocked process report.
The threshold value indicates the sampling interval between two successive blocked process reports i.e. it shows how often do you want to generate the successive blocked process report. In our case, we have selected 5 seconds.
The text data of the blocked process report contains the blocked code and other related details in XML format. To know more details about Blocked process report, go through this excellent article by Brad McGehee on identifying blocking problems with SQL Profiler.
Performance Development Lifecycle for IT (PDL-IT) is systematic approach developed by Microsoft’s ACE team in order to improve the application performance of Microsoft’s line of business (LOB) and web applications built on top of Microsoft technologies. The process encompasses the addition of a series of performance focused activities and deliverables at each of the phases of Microsoft IT’s software delivery life cycle (SDLC). These activities and deliverables include the architecture review from the performance standpoint, code profiling, end-to-end load testing server health monitoring. The PDL-IT process holds good and can be baked with any generic software development life cycle of web applications.
For more details on PDL-IT, check my three posts on ACE Team blog on PDL-IT.
Post-1: Basic Overview
Post-2: Discussion on Envision and Design Phase
Post-3: Discussion on Build, Stabilize, Deploy and Production Phase.
Regards,
Mohd Abubakr.
PerformancePoint planning server 2007 is a multi-tier architecture consisting of technologies such as Microsoft SQL Server 2005, SQL Analysis services, web services and ASP.NET web applications hosted on IIS and Microsoft Office Excel plug-in. With all the different technologies involved, it is important to establish equilibrium between user load and system resources for the given PerformancePoint application.
Performance testing of PerformancePoint planning server not only helps in determining the capacity of the system but also helps in identifying performance bottlenecks under user load. During development and functional testing, then load on the servers is very less, hence the applications appears to run fast. During Performance testing, the servers are put under stress by applying heavy user load. This test will determine the capacity of the system i.e. answers questions such as but not limited to
1. How many concurrent users does the planning server support?
2. What is the average response time observed by the end user opening a connection from Excel plug-in during average load and peak load?
3. What is the average response time observed by the end user in saving/uploading a model/report through Excel plug-in?
4. What is the average response time to deploy a model using Planning Business Modeler?
5. Is there any need for additional system resources to meet the expected user load?
During this stress test, analysis is done on how the application makes use of system resources by monitoring each system resource. It has been often noticed that performance is affected not due to hardware but because of code issues, configuration settings, connection settings etc. In a nutshell, this test will determine the performance problems faced by the application i.e. answers questions such as but not limited to
1. How much network/bandwidth is being used by the PerformancePoint planning server? Are there any recommendations to reduce it?
2. Is there a potential performance problem in the code that is causing high CPU/memory/disk usage?
3. Is there any connection leak problem occurring during peak load?
4. What are the areas that need to be re-looked to improve the performance? Is there any best practice that is not being followed?
5. What is the system behavior at different user loads?
6. Which tier needs to be tuned to improve the performance?
My future posts will include different performance testing and tuning techniques for PerformancePoint Planning Server 2007.
Several white papers have been published by the PerformancePoint Product team on the best practices of PerformancePoint Server 2007. We recommend the architects, developers, testers, deployment engineers and Operational engineers to read and follow best practices presented in the concerned white papers. This will be highly beneficial in avoiding bugs at the very early stage and enrich the performance of the application.
PerformancePoint Server 2007 Planning & Architecture Guide
http://go.microsoft.com/fwlink/?LinkID=106544
Deployment Guide for PerformancePoint Server 2007
http://go.microsoft.com/fwlink/?LinkId=106633
PerformancePoint Server 2007 Hyper-V recommendations guide
http://go.microsoft.com/fwlink/?LinkID=136352&clcid=0x409
PerformancePoint Server 2007 Operations Guide
http://go.microsoft.com/fwlink/?LinkID=106545
White papers on Performance testing and Tuning PerformancePoint Server 2007
Performance testing guidelines for PerformancePoint Server 2007
http://go.microsoft.com/fwlink/?LinkId=124064
Performance tuning and capacity planning for PerformancePoint Monitoring Server
http://go.microsoft.com/fwlink/?LinkId=100208&clcid=0x409
Performance tuning scenario for PerformancePoint Planning Server
http://go.microsoft.com/fwlink/?LinkID=100209&clcid=0x409
Related material
Best practices for OLAP cube design and MDX querying
http://technet.microsoft.com/en-us/library/dd285464.aspx
Analysis Services Query Performance Top 10 Best Practices
http://go.microsoft.com/fwlink/?LinkId=134598
The Microsoft Anti-Cross Site Scripting Library V3.0 (Anti-XSS V3.0) is an encoding library designed to help developers protect their ASP.NET web-based applications from XSS attacks. It differs from most encoding libraries in that it uses the white-listing technique -- sometimes referred to as the principle of inclusions -- to provide protection against XSS attacks. This approach works by first defining a valid or allowable set of characters, and encodes anything outside this set (invalid characters or potential attacks). The white-listing approach provides several advantages over other encoding schemes.
New features in this version of the Microsoft Anti-Cross Site Scripting Library include: - An expanded white list that supports more languages - Performance improvements - Performance data sheets (in the online help) - Support for Shift_JIS encoding for mobile browsers - A sample application - Security Runtime Engine (SRE) HTTP module
Anti-XSS V3.0 can be downloaded from here. More details about Anti-XSS V3.0 can be found at CISG's blog.
This blog post contains the results of the Performance testing of Anti-XSS V3.0. These results are also included in the Performance data sheets (in the online help) of Anti-XSS V3.0. Performance testing of the Anti-XSS library was carried out for a sample application that uses AntiXSSLibrary.dll over a varying user load. Under the same load conditions, comparisons of AntiXSSLibrary.dll were done using .NET's HttpUtility.HtmlEncode() as well as with no encoding as a baseline.
Sample Application Overview
The sample ASP.NET application was written to take strings as input and then to output the encoded form of the string using AntiXSSLibrary.dll in the background.
The input string applied on the sample application consisted of
· 64 character string with all safe characters (a-z,A-Z,0-9 etc.)
· 64 character string with approx. 25% of encoding characters
· 128 character string with approx. 25% of encoding characters
· 512 character string with approx. 25% of encoding characters
· 1024 character string with approx. 25% of encoding characters
· 64 character international string (any language like Chinese or Hebrew) with no encoded strings
· 64 character international string (any language like Chinese or Hebrew) with 25% encoded strings
Performance Environment
Performance testing of the Anti-XSS library and SRE were carried out in the Microsoft ACE Performance labs. The tests were conducted using 64-Bit and 32-Bit environments. The following are the machine specifications:
Load Test Overview
VSTS 2008 was used to generate a load of 200 concurrent users. The following table depicts the user load distribution:
Each transaction consisted of two ASP.NET requests. For this sample application, one transaction comprised:
1. User logs on to the homepage and enters the string
2. The application returns the encoded version of the string
Load Test Results
For a 200-user concurrent load, the end-to-end transaction time for the AntiXss.HtmlEncode() (yellow "Antixss Encode" in the illustration below) encoding scenario is 3.9 milliseconds in 32-bit web server and 4.2 milliseconds in 64-bit web server. Under the same user load conditions, the end-to-end transaction time for the scenario using HttpUtility.HtmlEncode() (red "Html Encode" in the illustration below) is 3.8 milliseconds and 4.1 milliseconds for 32-bit and 64-bit web servers respectively. Hence, there is only a 0.1 millisecond delta between encoding with the AntiXss.HtmlEncode() and the .NET HttpUtility.HtmlEncode() methods.
In terms of throughput, Anti-XSS generated around 85 ASP.NET Requests/sec in the 32-bit environment. Since each transaction consisted of two ASP.NET requests, the system throughput at a 200-user concurrent load for the Anti-XSS scenario came to 42.5 Requests/sec.
Performance Test Results
Summary
· The average transaction time for Anti-XSS under given conditions was obtained as 3.9 and 4.2 milliseconds on 32-bit and 64-bit web servers respectively.
· The average delta between AntiXss.HtmlEncode() and HttpUtility.HtmlEncode() is +0.1 milliseconds per transaction.
· On 200 User concurrent loads, the throughput of the Anti-XSS library is 42.5 and 38 requests per second on 32-bit and 64-bit web servers respectively.
In several of the performance tuning projects I have done, the web pages with security settings such as SSL or Windows authentication are generally slow compared to other pages. In this post, I analyze how SSL performance can be monitored in IIS 6.0
As per the architecture of IIS 6.0, security related operations such as SSL, Windows Authentication, Kerberos, etc are executed by lsass.exe process, whereas all other operations are executed under a process called w3wp.exe. Whenever, a web page with SSL is requested, IIS transfers the control to lsass.exe process to encode or decode.
Architecture of IIS 6.0
Analyzing SSL performance:
For the web applications having SSL enabled, the performance of the SSL can be isolated from the performance of the normal web pages by monitoring lsass.exe process. One of the simple techniques to monitor the SSL performance is logging the Perfmon lsass.exe % processor time counter which is available under process counters. Below is the sample graph that shows the Overall CPU utilization observed on the web server and %CPU utilization contributed by the SSL.
In the above graph, the green curve represents the overall CPU obtained on the Web server and red curve represents the %CPU utilization of lsass.exe process. One can clearly see in the above graph that lsass.exe process is one of the major contributors for CPU. Monitor w3wp.exe process for pages with SSL disabled.
Note: Lsass.exe process is not used in the IIS 7.0. The architecture of IIS 7.0 is different and doesn’t utilize lsass.exe process for SSL authentication.
I happened to use this new performance tool called Visual Round trip analyzer for one of my recent projects. Here’s a little description given on the VRTA download page.
The Visual Round Trip Analyzer tool helps web developers and testers visualize the download of their page, identify best practices and changes that improve web performance. The network Round-Trip between the client and server(s) is the single biggest impact to web page performance – much greater than server response time. VRTA examines the communications protocol, identifying the causes of excessive round-trips, and recommending solutions. Performance engineers, testers, developers, operations personnel should use VRTA to conduct their web performance analysis. VRTA sits on top of NetMon 3.2 which is also released publically for free. VRTA abstracts the use of Netmon so the user does not need to know the details of Netmon but can simply click to start/stop the capture. VRTA has three primary features 1) a main chart which displays http traffic in 3 dimensions, 2) an All Files view that shows critical measurements for each file loading, and 3) an Analysis report that indicates which file transfers are exceptions to best practice rules.
More information on uses of VRTA can be found at
http://msdn.microsoft.com/en-us/magazine/dd188562.aspx
Simple steps to use VRTA
1. Install VRTA.
2. Do the Netmon capture of the web application you want to analyze.
3. Load the netmon capture .cap file in VRTA.
4. Generate the VRTA report.
One of the coolest feature of VRTA is the Performance Scorecard it generates. This scorecard gives details on where you application stands and how the performance of the application can be improved. Following is the scored card I obtained for one of the projects I worked on.
Bandwidth Efficient Rating (Score =F)
Bandwidth Efficiency Rate is a primary indication of how well a web page is able to utilize the available bandwidth. Applications that do not follow the best practices of downloading on parallel TCP ports, using Keep-Alives, larger files, etc will not sustain high bit rates on longer latency WANs. A score of "A" is given to 80% BER and above. BERs of 40% and below are given a score of "F". The math for this rule is BER = AvgBitRate / MaxBitRate / Avg Compressibility. Uncompressed file content is discounted
Compressibility rule (Score =F)
This rule checks for uncompressed files. Most JS, CSS, HTM, and XML files can be compressed 2x or more. Compression of these static files reduces server CPU and Egress. A grade of A is given if all files are compressed. An F grade indicates an average compressibility of greater than 1.4. Please note, many proxy servers will forward uncompressed files to the user even if the original files were compressed. It's better to do Performance testing outside of corporate proxies.
Expiration date rule (Score =F)
This rule checks for expiration dates. When a user returns to a site, many of the needed files are already in the cache and will be reused, but first the browser will check to see if the file is still good. If no expiration dates is found, the browser will send a Get-If-Modified request to the server. Most files will not have been updated and will get a 304-Not-Modified. This wastes time and server resources. Set Expiration dates to 3 years out, then change the file name, path, or argument string to force a new file version download. Max-Age is an alternative to Expiration dates and is also checked. A grade of A is given for files that have greater than 1 year dates.
Average TCP port rule (Score =F)
This rule measures the average number of parallel connections that are used to download files. More ports in parallel instead of serial allows for better bandwidth utilization. The HTTP1.1 Protocol limits two parallel connection to each sub domain. To download more files in parallel one can download from several sub domains. Please note that IE8 and Firefox 3 do not adhere to the HTTP connection limitation anymore. Applications can also limit the number of ports by blocking on individual files. A score of "A" is given for an average of 5 concurrent ports and an "F" for 1 Port.
Redirect rule (Score = A)
This rule checks for excessive HTTP redirects. Each redirect causes two network roundtrips between browser and server, and therefore slows down page loading. A score of "A" is given if no redirects are used.
Unblock JS rule (Score = A)
This rule checks for excessive HTTP redirects. Each redirect causes two network roundtrips between browser and server, and therefore slows down page loading. A score of "A" is given if no redirects are used.
Time to first byte rule (Score = D)
This rule checks the Server response time. TTFB is the time from the Get packet until the first response packet. Subtracting the TCP connect time from the TTFB gives the Server response time. TTFB - TCP < 30ms is scored as an "A", while > 100ms is an "F".
Average file size rule (Score = A)
This rule checks for the average file size. Larger files are able to use TCP slow start to full advantage while small files spend most of their time round-tripping. An average file size of 5 KB gets an "A" while < 2KB is an "F".
HTTP Keep-Alive rule (Score = F)
This rule checks if HTTP Keep-Alives (KA) are on. Turning off KA may improve the server memory footprint but it causes the user to increase the number of round-trips, often doubling the number. If TCP ports are used to load only one file and then new ports to the same domain are opened, then the score will be "F", else "A".
Image Clustering rule (Score = A)
This rule checks the number of small image files. As in the 'Avg File Size' rule, small files download slower than larger files. Consolidate many small files into fewer larger files to take advantage of TCP slow-start.
Packet loss rule (Score = A)
This rule checks for retransmitted packets. Any packet loss significantly degrades page load time for users that are physically far away from the hosting datacenter, such users in China accessing a web site in California. A score of "F" is given if Packet loss is greater than 3%.
White spaces rule (Score = F)
This rule checks for excessive white space. These are tabs, newlines, remarks, etc. A score of "F" is given if more than 10% of the text downloaded is whitespace.
Favicon.ico rule (Score = A)
This rule checks for excessive white space. These are tabs, newlines, remarks, etc. A score of "F" is given if more than 10% of the text downloaded is whitespace.
Upstream single packet rule (Score = A)
This rule checks the size of the uploaded Get requests. The max payload for an Ethernet packet is 1460 Bytes. When large cookies or http headers are used this can expand the Get to more than one packet. Since upload bit rates are often much smaller than download, this becomes significant. A score of "F" is given if more than one packet is used for the Get request.
“Effects of Recession” is the burning topic of the moment and everyone is concerned about cost-cuts and other measures to overcome the crisis. I attended few talks by business leaders on economic collapse and crisis that’s going on. It is fascinating to see how people fail to recognize simple things at right time, especially the so called financial experts. In this blog post, I would like to point out some effects of recession on Internet and how improving your web application performance can help in tackling the situation effectively.
For a company, effective cost-cuts not just mean reducing costs associated with the manufacturing, production, development, testing, advertising and customer support but also needs to make sure that customers feel that it is cost-effective to purchase their products. During this process, there would be many tradeoffs. Most companies recognize that automation of manual work is one of the effective techniques to reduce costs associated with human resources. One would notice lot of work would be done in the field of automation in next few years, probably generating jobs in the automation sector.
Internet can play a very handful role in automating some of the critical processes associated with business. One of the bigger challenges before businesses is driving the teams situated at different geographic locations effectively. Collaboration tools such as SharePoint would add more value in driving the business at reduced costs.There is also a probability that companies would design in-house ad-hoc tools to support their needs. Most of these collaboration tools would make use of Internet to transfer data from one geographic location to another.
Another place where Internet can play an effective role in generating more revenue would be
a. Companies would create online applications to support their customers than agents/executives/support staff helping them resolve issues.
b. Companies would go for cost-effective and innovative online advertising techniques
c. Companies would prefer to create new online applications to lure the customers.
d. Companies would push for cloud computing
From the customer (common man) point of view, the following
e. Customer would prefer to do shopping online than visiting shopping places. This would save fuel costs.
f. Customer would make a trade-off decision between the best product and the cost-effective product. If the best product is too costly, he/she might rather choose a product with lesser functionality at a lower cost.
g. Customers would find Cloud computing as affordable technology
All these will collectively increase the Internet traffic. Internet being a finite resource can only allow traffics up to certain thresholds. If the thresholds are exceeded, it will lead to traffic congestion. If we analyze the growth of Internet in last few decades, there has been substantial amount of investment done on growth of Internet infrastructure. At the same time, even the Internet traffic has grown exponentially.
Internet Infrastructure v/s Internet Demand
It is important that Internet infrastructure should be in a position to handle Internet demand. If there Internet demand or the load on Internet is more than the handling capacity of Internet infrastructure, the effects could be catastrophic. Before economic recession, the growth of Internet infrastructure was more than demand for Internet services, however, there is a possibility that this might not be true after recession.
Internet service providers have also suffered due to recession and there is possibility that it will effect the growth of Internet infrastructure. If that is the case, it won’t take must time for the balance to swing. There will be more demand for Internet services but the infrastructure won’t be able to handle the demand.
Below is another reflection of how the demand for Internet services has been compensated with increase in Internet infrastructure in last few decades and how eventually the demand for Internet services might overtake the Growth of Internet infrastructure curve.
Once a situation is reached where demand for Internet services is more than capacity of the Internet infrastructure, it will results in severe congestion. The packets traveling over wire need to compete with each other in reaching the destination. This is results in severe performance problems. It will take more and more time for packets to reach from web servers to client machines.
How can we avoid this situation?
On one side, there is demand for rich Internet applications whereas there is also a responsibility on all the web application developers to reduce the strain on the Internet. One of the obvious technique is to increase the Internet infrastructure. Fully utilize the optical fiber bandwidths could be one of the solution. There is another very important thing to note here. Whenever, we discuss technology, we often think of locations such as USA, Europe, Japan, etc, whereas there exists major portions of earth where there is no proper Internet connectivity. With the businesses expanding, these developing countries provide new opportunities of increasing the profits. Hence, any new technologies that come up needs to also satisfy the existing situation in these countries.
“Most of the fundamental ideas of science are essentially simple, and may, as a rule, be expressed in a language comprehensible to everyone.” – Albert Einstein
Yes, essentially, the solution to this problem is very simple. However, it would require a collective effort from every web developer. Congestion occurs either due to bottleneck in network or due to flow of very large data than the capacity of the network. Reducing the data transfer on the Internet can help in increasing the overall performance of the web applications. Some of the simple techniques that can help in solving this problem…
1. Designing applications that would understand the network topology that’s connecting the web-server and client machine and work accordingly would immensely improve the performance.
2. Distributing the web servers across different targeted geographic locations
3. Transferring the data only if needed hence reducing the overall transfer of data
Summary:
Internet is a finite resource and it is the collective responsibility of all Internet users that we do not over-strain it. With recession, there is possibility that growth of Internet infrastructure might be slow and it could result in Internet traffic congestions and degradation of performance of web applications. Adopting simple techniques such as reduction in unnecessary data reduction can immensely help in reducing the strain on Internet infrastructure.
From my past experience I have learned that most of the project teams are not aware of App Pool properties of IIS. They tend to rely on the default values of App Pool. Even though the default values are chosen to give better performance in general, the values might not be suitable for your project.
By default, as per the property in App pool setting, the worker process gets shut down after remaining idle for 20 minutes. One of the reasons for this 20 minutes shut down duration is,
Case Study:
In one of my recent projects, the customer asked me to resolve slow page loading issue. I asked him for the production IIS logs in order to find out the slow running .aspx pages. I sorted the pages based on the time-taken field in IIS logs and I was surprised to notice that /sitehome.aspx page was consistently clocking over 120 seconds.
| URL | Time (ms) |
| \sitehome.aspx | 149312 |
| \sitehome.aspx | 149228 |
| \sitehome.aspx | 149218 |
| \sitehome.aspx | 149046 |
| \sitehome.aspx | 148640 |
| \sitehome.aspx | 142984 |
| \sitehome.aspx | 140093 |
| \sitehome.aspx | 139609 |
| \sitehome.aspx | 138562 |
| \sitehome.aspx | 130703 |
Woo hoo… there you go! There problem is that, this application is being used by different customers across the world. Even though per day, only 10 customers access this application, however all of them reside in different regions. Obviously, they all have different business hours. Each time a new user hits the website, the IIS worker process has already timed-out and it has to restart. That’s the reason, for every user the /sitehome.aspx page was consuming over 2 minutes of time. Starting a worker process all over again is a time-consuming process.
I recommended the customer to increase the idle timeout value to something reasonable, say 120 minutes. Bingo! The /sitehome.aspx page consumes less than 15 seconds now.
Some details about this setting:
In an environment, where multiple websites are hosted on a same web server (IIS), it is important to conserve resources. Each IIS worker process instance consumes server resources such as CPU, memory, etc. Hence, if a particular IIS worker process is remaining idle over duration, one can shut it down so that other worker process instances get more CPU/memory.
However, if only one website is hosted on the web server (IIS), small idle timeout value might not be suitable. In the above scenario, the customer had 200 users across the globe and around 10-15 unique users visit the web application per day. Considering that around 1 or 2 user access the website per hour, if the request timeout is set to 1 or 2 hours, none of the users are going to face a situation where they have to restart the whole IIS process.
There are various reasons for a deadlock can occur on the web server. In this blog post, I would be pointing out the various ways to resolve aspnet_isapi.dll related deadlocks on web server. If the deadlock has occurred on the web server, you should notice a warning or error on Event Viewer.
In one of the Apps I was working on the following was the warning message noticed on Event viewer.
“ISAPI ‘C:\windows\Microsoft.Net\Framework\v2.0.050727\aspnet_isapi.dll’ reported itself as unhealthy for the following reason: ‘Deadlock detected’.
One of the reason, a dead lock can occur on the web server is due to usage of COM Interop cross-context infertace in .NET 1.1 or .NET 2.0. However, this was not the problem on the application I was working. In case, your application is facing deadlock due to COM Interop cross-context interface, this KB articles would be helpful in resolving them.
http://support.microsoft.com/kb/921217/
http://support.microsoft.com/kb/915808/
Finally, after doing a bit of research here and there, I found the correct KB article that defines my problem. Here is the cause of this deadlock.
This problem might occur because ASP.NET limits the number of worker threads and completion port threads that a call can use to execute requests. Typically, a call to a Web service uses one worker thread to execute the code that sends the request and one completion port thread to receive the callback from the Web service. However, if the request is redirected or requires authentication, the call may use as many as two worker and two completion port threads. Therefore, you can exhaust the managed ThreadPool when multiple Web service calls occur at the same time.
For example, suppose that the ThreadPool is limited to 10 worker threads, and all 10 worker threads are currently executing code that is waiting for a callback to execute. The callback can never execute because any work items that are queued to the ThreadPool are blocked until a thread becomes available.
Another potential source of contention is the maxconnection parameter that the System.Net namespace uses to limit the number of connections. Generally, this limit works as expected. However, if many applications try to make many requests to a single IP address at the same time, threads may have to wait for an available connection.
This excellent KB article “Contention, poor performance, and deadlocks when you make Web service requests from ASP.NET applications” http://support.microsoft.com/kb/821268 clearly defines this problem and gives out solutions.
If the above solutions haven’t resolved your deadlock, than you can generate a dump about your deadlock, which might reveal inner characteristics of it.
How to generate a dump file when ASP.NET deadlocks in IIS 6.0
IIS 6.0 has a new feature that is named Orphan Worker Process. This feature lets you inspect a process that is scheduled to be recycled before the process is terminated. The Orphan Worker Process can be used to attach a debugger to the process and to generate a dump file for investigation.
OrphanWorkerProcess tells IIS not to shutdown the worker process. IIS will leave the process running but won't send the worker process any more requests to execute. This setting lets the process stay in memory so you can jump in with tools like a debugger and see where the deadlock is occurring.
For more details on this technique, read on the following KB article http://support.microsoft.com/kb/828222/
I was amazed when I observed “UTC” time tag associated with IIS logs. In this blog post, I would be explaining what UTC time is, how it is different from GMT, how it is been used in IIS logs and the difference it makes. Being physics enthusiastic, it was fascinating to know how geographical physics is rooted within our computer systems. I was under the belief that GMT (Greenwich Mean Time) is the standard accepted in internet and computer networks. However, that’s not true.
The accepted standard for Internet protocols used in many WWW applications is UTC but not GMT. UTC stands for Coordinated Universal Time. Even though, there isn’t much difference between GMT and UTC, however UTC is far more accurate compared to GMT. UTC time standard is based on atomic time units with leap seconds added at irregular time intervals. It has been calculated that Earth’s rotation is slowing down by margin of 2 milliseconds a day, hence increasing the day time to 86,400.002 seconds. 2 milliseconds overhead each day sums up to 1 second after around 18 months. Hence, in UTC standard, one extra leap second is added once 1 second overhead is accumulated. Certain research papers have indicated that there might be acceleration in earth’s rotation due to movements in earth’s crust. However, the margin of deceleration is higher than acceleration; hence the more and more leap seconds will get inserted in the coming years.
One of the internet standard that uses UTC time format for logging is W3C standard. (http://www.w3.org/TR/NOTE-datetime). W3C logging standard is being used in Internet Information Services (IIS). If you enable the IIS logs, then the time field you see is in UTC format. Occasionally, if you see 1 second leaps in it compared to your clock, then it’s due to UTC time format. However, these leaps happen once in 18 months and you should be really lucky to notice it.
Having read all that, if must be wondering, what’s so interesting in this? All I am trying to convey is that how much care we are taking making our data accurate. The technologists involved in making this happen should be applauded for their extra effort in perfecting the system.
Sort warnings are raised by the SQL server where there is insufficient available memory on the server to carry out sort operations within a query. In such cases, sort operation is divided into multiple steps affecting the overall performance of the query. Ideally, there should not be any sort warnings noticed on your server.
Detecting Sort Warnings:
In case, your SQL server is throwing up sort warnings, it can be detected using SQL profiler. Hash warnings are not enabled by default in the Events selection of SQL profiler therefore you need to select them explicitly. Hash Warning Event is available under Errors and Warning Events selection section. You can also select other errors and warning events as per your application requirements. Make sure you only select the required events, this would be helpful in avoiding the junk and also reduces the effort spent on analyzing the SQL Profiler trace. For further details on monitoring events on SQL Server, go through http://msdn.microsoft.com/en-us/library/ms190378.aspx
Enable the SQL Profiler traces during while running your application, preferably run a load test to stress the server at Peak load. If there are sort warnings thrown on your SQL server, you can notice them on the fly on SQL profiler. After the test is done, stop the SQL trace. In case you have a very large trace file, it is time consuming to scroll down each and every column of the Profiler trace. One quick way is to use “Organize Columns” options in SQL Profiler.
1. Go the File -> Properties Tab of SQL Profiler trace.
2. Click Events Selection Tab in Trace Properties Window and click “Organize Columns”.
3. Select the “Event Class” Column and use “Up” option to Move it up under “Groups” Section.
4. Select the “EventSubClass” Column and use “Up” option to Move it up until it’s first column under “Columns”.
5. Click OK.
This will quickly organize your trace file under different Event Class sections. Now your trace file on SQL profiler window should look like something similar to below screen shot. In a single go, you now know the different types of Events that have occurred on your SQL Server captured on trace file. Over here, you can see there were Deadlocks, hash warnings, missing join predicate, hash warnings etc were found on the SQL profiler trace. It’s rare that your application will have all these problems, for some demonstration I have purposefully made some problems to occur on SQL Server.
In the above trace file, there are 602 sort warnings thrown by the SQL server. Now, it is clear that there queries that are performing sort operation and there is insufficient memory on the SQL server to carry out the operation smoothly.
Sort warnings EventSubClass:
The sort warning on the SQL profiles comes with different data columns, giving you more information on the nature and complexity of the sort warning. One such important data column of sort warning is EventSubClass, which will help us in understanding the complexity of problem. To know more about SQL Server Sort Warning Event Class Data columns go through http://msdn.microsoft.com/en-us/library/ms178041.aspx.
EventSubClass of sort warning gives the intensity of problem caused by the particular sort warning. When there is insufficient memory on the SQL server to execute a particular sort operation within a query, the sort operation will be divided into multiple passes. Each pass is an overhead and would increase the time taken to complete the sort. EventSubClass of a sort warning will help you in determining whether a sort operation which threw a sort warning was executed in single pass or consumed multiple passes.
If there was no sort warning, then the sort operation was finished in 1 single step.
If EventSubClass = 1, then the sort operation was finished within one pass i.e. it was divided into 2 steps.
If EventSubClass = 2, then the sort operation was finished in multiple number of steps.
If you receive a sort warning with EventSubClass = 2, then make sure that you either tune the Sort operation or increase the memory on the SQL server.
Recommendations to resolve Sort warnings:
1. Increase the memory on the SQL Server.
2. Removing the unnecessary rows that being returned by the sort operation.
3. Also, make sure sorting is done only to the required columns.
One of the important process that you need to monitor while doing Performance testing is w3wp.exe process. It is an IIS related process and runs whenever IIS is configured in Worker Process Isolation mode. By default, IIS runs in Worker process Isolation mode. Each site hosted on IIS Server has it's own app pool. This app pool will have an instance of w3wp.exe process running.
When you are doing Performance testing of a web application, make sure that you log the Perfmon counter for %CPU utilization for the concerned w3wp.exe process. This counter is available under Process Object. This counter would give the %CPU consumed by w3wp.exe process while you were doing Performance testing.
Consider a situation where you haven't logged the counter for w3wp.exe process and you only have overall %CPU utilization on the web server. How can you be sure that obtained %CPU utilization is caused by IIS process? It could be due something running in the background on the Web server or maybe caused by Anti-Virus running on the server. Once you have w3wp.exe %CPU counter, you can accurately say that out of X% CPU utilization on web server, Y% was caused by w3wp.exe process.
In case, you find the %CPU of w3wp.exe much much lesser than the overall %CPU utilization, it means that there was another process that caused the high %CPU utilization. Below is the sample Perfmon graph comparing the overall %CPU utilization (red) and %CPU utilization of w3wp.exe process (brown).
That’s the phrase you see assigned to the hidden variable in hotmail passport authentication. You might notice this phrase when you record the web test for a webpage containing passport authentication. The form post parameters of the passport authentication URL (https://login.live.com/ppsecure/post.srf) contains variables such as Login (Email ID), Passwd (password), PwdPad etc.
Notoriously, the PwdPad variable is assigned a phrase called “IfYouAreReadingThisYou HaveTooMuchFreeTime”. However, if you observe the value recorded for PwdPad variable recorded in the web tests, it appears to be varying in length with some of the right most characters of the phrase clipped.
From the code point of view, the length of the phrase varies with the length of the password. The logic behind the variable length is, say, the password of the passport Id contains “7” characters, then 7 rightmost characters are clipped from the phrase “IfYouAreReadingThisYouHave TooMuchFreeTime” and the remaining phrase “IfYouAreReadingThisYouHaveTooMuchF” is assigned to the variable Pwdpad. In the screenshot shown above, the length of the password is 8 characters, hence the 8 right most characters are clipped from the phrase and “IfYouAreReadingThisYouHaveTooMuch” is assigned to PwdPad.
The maximum length of the Password in passport Hotmail Accounts is 16 characters, so you will definitely find atleast “IfYouAreReadingThisYouHav” assigned to the PwdPad.
