November 12, 2008 at 9:38 pm
Hi,
I currently have a dedicated web server and a dedicated SQL server 2005. The specs shouldn't be of real importance at the moment cause I am the only user. When I try to update data it takes forever if at all. It doesn't matter if it's a large amount or nothing at all.
I've run profiler on the SQL server and the scripts are tiny that are being sent to the server and the requests from a SQL point of view are taking no time at all to execute but the web page doesn't come back saying it's complete. It still acts as though it is still processing even though SQL isn't doing anything. In saying that SQL is at 100% CPU the whole time even though it's not executing anything. The web server's CPU doesn't do a lot at all, occasionally jumps up but not for long and not to 100%
The profiler templates I used was both standard and tuning and it shows the queries being executed quickly and I have implemented any stats and/or indexes it has recommended which makes no difference to the performance.
Can anyone please shed some light on this problem.
Kris
November 12, 2008 at 10:08 pm
Kris (11/12/2008)
Hi,I currently have a dedicated web server and a dedicated SQL server 2005. The specs shouldn't be of real importance at the moment cause I am the only user. When I try to update data it takes forever if at all. It doesn't matter if it's a large amount or nothing at all.
I've run profiler on the SQL server and the scripts are tiny that are being sent to the server and the requests from a SQL point of view are taking no time at all to execute but the web page doesn't come back saying it's complete. It still acts as though it is still processing even though SQL isn't doing anything. In saying that SQL is at 100% CPU the whole time even though it's not executing anything. The web server's CPU doesn't do a lot at all, occasionally jumps up but not for long and not to 100%
The profiler templates I used was both standard and tuning and it shows the queries being executed quickly and I have implemented any stats and/or indexes it has recommended which makes no difference to the performance.
Can anyone please shed some light on this problem.
do the following at the web part
1. use the web part only for fetching and displaying data.
2. create one more layer DAL
3. write your dB related functions there
4. never forgot to close the dB connection after its use.
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 17, 2008 at 3:10 pm
This is a third party web site and database. SQL sites on one box and the web app sits on the other. As a general rule all the SQL boxes are dedicated SQL boxes. Would this make a difference?
Sorry I'm not very familiar with web apps or IIS for that matter. Any help is appreciated.
Kris
December 2, 2008 at 4:00 pm
is this just updating data ?...so inserting , deleteing and retrieving is acceptable ?
You will have to find out why SQL is 100% ....
In profiler use your own templete and just add rcp completed and sql batch completed and sqlstatement completed and order the trace by decending order on duration \ cpu column ... rememeber sql 2005 profiler displays duration in microseconds ..
Look for queries that have excessive CPU values and duration....
also look at waits and queues :
CXpacket waits
Also use DMV's and profiler to look for recompiles(CPU hungry operation)
make sure that you have sufficient CPU resources for your solution.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply