January 2007
Monthly Archive
Internet Information Services Diagnostic Tools
Posted by Chris Alcock on 11 Jan 2007 | Tagged as: Development, IIS, SysAdmin
When investigating performance problems on production servers it is always very useful to have as much information about the actual work that the server is performing at any given time. Out of the box IIS 6 does not give you much to work with – at best you can identify the virtual host that is causing the issues by putting it into its own application pool, and then using Task Manager you can see PID of the w3wp.exe instance which is occupying your servers CPU. Once you have that, the iisapp.vbs administrative script will reveal the name of the application pool which is misbehaving.
I have often wished to be able to see in real time what is actually going on within the worker process or IIS instance, and with the discovery of the Internet Information Services Diagnostic Tools trace tools I have found something that comes pretty close to what I would like.
The IIS trace tools contain a command line tool that will return the details of the executing requests as XML (even obtaining the information from a remote IIS server), however the jewel in the crown is the Request Viewer – a window app that with the click of a tool bar button reveals the requests currently executing on the server. (see screen shot below)
Unfortunately the tool does not show you the name of the host that the requests relate to, just the site ID and application pool pid, but these are easily converted into the application pool name (as mentioned above, use iisapp) or the site (look the is up in the IIS Manager.
Another problem with the request viewer is that when I first ran and clicked the refresh now button it all I got was an error and no details of the requests currently running. Thankfully I found the solution on the web, and it was as simple as making sure the temp environment variable was set to a path that didn’t use long filenames.
As the IIS viewer leaves a command window in the background when it runs, I thought that the best solution would be to have a simple batch file that set the environment up, ran IISApp.VBS and then started the Request Viewer:
@echo off
set temp=c:\temp
iisapp
"C:\Program Files\IIS Resources\TraceDiag\reqviewer.exe"
So now that empty command window contains the PID values for all my application pools so its not wasting space in my RDP window 🙂
links for 2007-01-11
Posted by Chris Alcock on 11 Jan 2007 | Tagged as: Links
-
How To Be Agile
Comments Off on links for 2007-01-11
Careful what you subselect for
Posted by Chris Alcock on 11 Jan 2007 | Tagged as: Database, Development, SQL
I quite often find myself using a subselect to get some metadata key value to use as part of a where clause. Some thing like the following:
select * from product where producttype_fkey=(select producttype_key from ProductType where TypeId='TEST')
I had been under the impression that the database engine would be clever enough to work out that the value the subselect returned was constant and that it would simply work in an equivalent way to this query:
declare @prodType int;
select @prodtype=producttype_key from ProductType where TypeId='TEST';
select * from product where producttype_fkey=@prodType
Infact this is not the case. The first query ends running as though the product table and the productType table were being inner joined together, giving an execution plan like this:
The second query (using the variable) has two parts to its execution, however the join is no longer there:
The significant thing about this is that when these two types of query are run together, the first query accounts for almost 60% of the execution, where as the two stage with variable version only accounts for a little over 40%, so it is significantly better to select the value out into a variable rather than subselecting (especially where the same value is required multiple times in a stored procedure.
