For the last week or so I have been discussing a performance issue I have been having in regards to the delay time I have been experiencing when I click on my dotnetnuke site in my development environment. From the time I click on a link in the browser using localhost/dotnetnuke to the time I see results from the database onto the browser (as it is a fully dynamic site) is approximately 1 minute some pages take 30 seconds others when they hang more then 2 minutes.
I was going back and forth with a database specialist concerning the issue thinking it was a problem with my sql 2005 express server and so I changed over to an evaluation version of sql 2005 enterprise in which I used profiler to save traces to a table in order to try to diagnose the issue.
The issue is when I work on this dotnetnuke site in my development environment which consists of windows xp pro, athlon 64 bit 3000+ processor, 2 GB RAM and a 75 GB 10000 rpm sata drive (which the sql server is on) what happens is the process called sqlservr.exe takes up about 99% of cpu.
When I did a querey on a system table in sql server I found that the actual cpu signal was only around 4% at most where as the resource_wait_time was almost 100%. The more I clicked in the development environment the longer in ms was the resource wait time but the only thing that dropped was the %of wait time resources which didn't drop much.
Toi make a long story short the conclusion was that the performance issue was not do to the sql server or operating system, or bottlenecks like i/o file, cpu, etc.. (since only 1 person is accessing the database at a time and judging on performance monitors, process window, and profiler nothing would suggest i was dealing with hardware bottlenecks in my case) but was rather perhaps do in part either by the limitations of iis 5.1 which comes with xp pro or maybe the web page itself though on the production site which is hosted by a third party host running server 2003 xeon quad core processors the speed is 10X faster so I can't imagine the web page itself is at stake which leads me to believe it boils down to some kind of iis configuration.
So the question is does this make sense. Is it possible that iis plays a role in significantly slowing down system performance when dealing with a development environment that is heavily dynamic and dependant on sql server. If iis does play a role in this what kinds of tweaks could be done to iis to help with the performance if nothing can be done to iis than do you have any suggestions of what else could be the cause of over 1 minute delays per click in an in house development environment with the conditons listed above in which only 1 user is quereying the database at a time.
Thanks any suggestions would be greatly appreciated and if you need snippets from my conversation with a person who has familiarity with database administration I would be happy to paste some of those snippets in the comments to better help tackle the problem more thoroughly