sql 2005 express edition
my question deals with the sqlservr.exe process generated when accessing the sql 2005 express database. When querey's are being sent out to my database in a development environment with windows xp pro being the operating system the sequel 2005 express is installed on the sqlservr.exe process spikes to around 97 to 99% cpu usage and I still haven't figured out how to solve this problem completely. I had gotten a few suggestions from this forum as a result of a similiar question and those suggestions were as follows:
--------------------------
ensure that the max memory setting of sql server is set to a realistic value for your desktop, like 600-700MB (by default, it is set to us up all memory, ie the full 2GB).
next step, use the query profiler, and see what queries run long, use much reads/writes/duration, and check those using the Management Studio, to see what execution plans, if there are any using full table scans or clustered index scans, and check that there are the necessary indexes on the tables involved.
this process is usually solving high cpu problems within an hour, with a bit of experience...
--------------------------
In 2005 express you can't allocate a set amount of memory as you can in sql 2005 standard also I couldn't find any querey profiler in this version howbeit I found an independant project that was at its first stages with something similiar but not near what I need to get to the bottom of this problem The rest of the suggestions of what to do I am not sure how to go about doing them using the sql 2005 management since I am fairly new to microsoft sequel and troubleshooting processing issues.
Another suggestion given by another person who seemed to be having the same issue was to run update statistics but again I don't know how to do that in swl2005 express or if it can be done. I did see a blog about putting a check mark on update statistics when you are about ready to detach a database from the server but nothing on how to run an update statistics on multiple or single tables without doing it as a process of detaching the current database.
I don't know maybe sql2005 express has this drawback. Their is only 1 user accessing this database at a time as it is only on my development computer so I am the only one accessing it for development purposes and yet even with a single click in my localhost/dotnetnuke area I find the sqlservr.exe process in the task manager spiking from almost 0% cpu to a wopping 97% and sometimes this process is up that high even when no internet browser is even open and asking info from the database.
The live site also uses dotnetnuke and while it is slower than many websites it certainly is not near the sluggishness of my development site using sql 2005 express. The live site uses sql 2000 regular edition and I am soon moving the site over to sql 2005 production server.
Lastly I was wondering if any of you felt that buying the sql 2005 standard server and using that for development purposes as it is used for the live production might solve this sqlservr.exe problem as it is packaged with tools to analyze tables such as profiler and querey analyzers and the ability to allocate memory instead of it deciding to utlize all the memory although I don't know how utilizing all the memory would account for spiked cpu usage for long periods of time.
Any way any help you might be able to give would be greatly appreciated as the way things are now development goes very slow do to having to deal with this sqlservr.exe process taking up almost the whole cpu.
My computer specs in case you need to know is a Athlon 64 bit (though using a 32 bit windows xp pro) 3000 (equivalent to about 2.0 GHZ
2 GB of RAM
Server is on a sata 10000 rpm 75 GB hard drive although dotnetnuke files that are not in a database are located in the c:\dotnetnuke directory which is not the same hard drive as the server is on only the server resides on that drive and perhaps some folders used for temp work when I am utilizing those specific programs like photoshop scratch disk, etc..
My mother board is an Asus K8n.
Hopefully this gives you any info you might requre to be able to help me tackle this issue.
Again thanks for spending the time to look at this question
--------------------------
ensure that the max memory setting of sql server is set to a realistic value for your desktop, like 600-700MB (by default, it is set to us up all memory, ie the full 2GB).
next step, use the query profiler, and see what queries run long, use much reads/writes/duration, and check those using the Management Studio, to see what execution plans, if there are any using full table scans or clustered index scans, and check that there are the necessary indexes on the tables involved.
this process is usually solving high cpu problems within an hour, with a bit of experience...
--------------------------
In 2005 express you can't allocate a set amount of memory as you can in sql 2005 standard also I couldn't find any querey profiler in this version howbeit I found an independant project that was at its first stages with something similiar but not near what I need to get to the bottom of this problem The rest of the suggestions of what to do I am not sure how to go about doing them using the sql 2005 management since I am fairly new to microsoft sequel and troubleshooting processing issues.
Another suggestion given by another person who seemed to be having the same issue was to run update statistics but again I don't know how to do that in swl2005 express or if it can be done. I did see a blog about putting a check mark on update statistics when you are about ready to detach a database from the server but nothing on how to run an update statistics on multiple or single tables without doing it as a process of detaching the current database.
I don't know maybe sql2005 express has this drawback. Their is only 1 user accessing this database at a time as it is only on my development computer so I am the only one accessing it for development purposes and yet even with a single click in my localhost/dotnetnuke area I find the sqlservr.exe process in the task manager spiking from almost 0% cpu to a wopping 97% and sometimes this process is up that high even when no internet browser is even open and asking info from the database.
The live site also uses dotnetnuke and while it is slower than many websites it certainly is not near the sluggishness of my development site using sql 2005 express. The live site uses sql 2000 regular edition and I am soon moving the site over to sql 2005 production server.
Lastly I was wondering if any of you felt that buying the sql 2005 standard server and using that for development purposes as it is used for the live production might solve this sqlservr.exe problem as it is packaged with tools to analyze tables such as profiler and querey analyzers and the ability to allocate memory instead of it deciding to utlize all the memory although I don't know how utilizing all the memory would account for spiked cpu usage for long periods of time.
Any way any help you might be able to give would be greatly appreciated as the way things are now development goes very slow do to having to deal with this sqlservr.exe process taking up almost the whole cpu.
My computer specs in case you need to know is a Athlon 64 bit (though using a 32 bit windows xp pro) 3000 (equivalent to about 2.0 GHZ
2 GB of RAM
Server is on a sata 10000 rpm 75 GB hard drive although dotnetnuke files that are not in a database are located in the c:\dotnetnuke directory which is not the same hard drive as the server is on only the server resides on that drive and perhaps some folders used for temp work when I am utilizing those specific programs like photoshop scratch disk, etc..
My mother board is an Asus K8n.
Hopefully this gives you any info you might requre to be able to help me tackle this issue.
Again thanks for spending the time to look at this question
ASKER
Hello David where is the autoclose property in sql express?
Also concerning the sqlservr.exe process it can hang at around 98 to 98% anywhere from about 20 sec. to close to a minute depending on what part of the website that database was working on.
However in the live site while it is slower than I would like it isn't near as slow as it is or takes near as long as it takes when totally run off my computer using sql2005 express.
After your response I also had someone give me the idea of needing to fine tune my windows xp pro for server types of processes so I studied out how to do this and found under the system in control panel their was in the advanced tab a performance setting in which you could change the priority from programs to background processes and change the system memory usage from programs to system cache. I changed both options to give priority to background processes and system cache.
Once I did this it seemed like I didn't get as much of the application hang as I was experiencing prior to it but I still didn't see a major decrease in hogging the cpu as it relates to this sqlservr.exe file I still saw long periods os 95% usage of cpu with this file
So now when sql2005 express is receiving a querey the sqlservr.exe process spikes to about 95 to 99% cpu usage for about 30 seconds or so and the memory usage goes up from about 127000 K to 130,000 K. Not a big leap in the memory but certaily in the cpu it was an extravagant leap.
Thanks again for your help in this
Bo
Also concerning the sqlservr.exe process it can hang at around 98 to 98% anywhere from about 20 sec. to close to a minute depending on what part of the website that database was working on.
However in the live site while it is slower than I would like it isn't near as slow as it is or takes near as long as it takes when totally run off my computer using sql2005 express.
After your response I also had someone give me the idea of needing to fine tune my windows xp pro for server types of processes so I studied out how to do this and found under the system in control panel their was in the advanced tab a performance setting in which you could change the priority from programs to background processes and change the system memory usage from programs to system cache. I changed both options to give priority to background processes and system cache.
Once I did this it seemed like I didn't get as much of the application hang as I was experiencing prior to it but I still didn't see a major decrease in hogging the cpu as it relates to this sqlservr.exe file I still saw long periods os 95% usage of cpu with this file
So now when sql2005 express is receiving a querey the sqlservr.exe process spikes to about 95 to 99% cpu usage for about 30 seconds or so and the memory usage goes up from about 127000 K to 130,000 K. Not a big leap in the memory but certaily in the cpu it was an extravagant leap.
Thanks again for your help in this
Bo
Hi,
This option is set at the database level.
It can be changed through SSMS. I haven't used SSMS Express, so I'm not sure there.
The Setting Database Options entry in BOL
T-SQL to turn autoclose off is
alter database [dgt-workbench]
set auto_close off
;
Think about making this a standard change, and do this on the model database as well, so subsequent databases have auto_close off as well.
You still might get CPU spikes, but if you are using SQL Express, my guess is that the database is closing, and it does take a finite time to open a database. Setting auto-close to off will reduce this, and I'm guessing reduce the amount of CPU involved.
HTH
David
This option is set at the database level.
It can be changed through SSMS. I haven't used SSMS Express, so I'm not sure there.
The Setting Database Options entry in BOL
T-SQL to turn autoclose off is
alter database [dgt-workbench]
set auto_close off
;
Think about making this a standard change, and do this on the model database as well, so subsequent databases have auto_close off as well.
You still might get CPU spikes, but if you are using SQL Express, my guess is that the database is closing, and it does take a finite time to open a database. Setting auto-close to off will reduce this, and I'm guessing reduce the amount of CPU involved.
HTH
David
ASKER
ok I found where that property was and by default it is already turned off. The model database is also turned off in regards to its autoclose feature.
What else do you think could be causing spiked cpu readings at prolonged intervals thanks for your help in this regard
Bo
What else do you think could be causing spiked cpu readings at prolonged intervals thanks for your help in this regard
Bo
Hi,
Just a thought, but you wouldn't be using software raid or disk compression or encryption on this box would you?
I suggest that you get the SQL 2005 tools installed, and use profiler to compare what SQL is running vs teh performance monitor.
Cheers
David
Just a thought, but you wouldn't be using software raid or disk compression or encryption on this box would you?
I suggest that you get the SQL 2005 tools installed, and use profiler to compare what SQL is running vs teh performance monitor.
Cheers
David
ASKER
no I don't have any raid or encryption on this box I have the server loaded on the fastest hard drive sata going at 10,000 rpm.
I have installed the tools which installed under the 90 directory and then dts. There I have utilized the import export tool but have not found a profiler tool for 2005 express. I did find some third part profiler tool in its first version written specfically for sql express though it wasn't that great I didn't think as it wasn't a tool by microsoft made for their server it was a tool a third party company open source team created do to the lack of a profiler that was not included in the express edition.
Thanks
Bo
I have installed the tools which installed under the 90 directory and then dts. There I have utilized the import export tool but have not found a profiler tool for 2005 express. I did find some third part profiler tool in its first version written specfically for sql express though it wasn't that great I didn't think as it wasn't a tool by microsoft made for their server it was a tool a third party company open source team created do to the lack of a profiler that was not included in the express edition.
Thanks
Bo
ASKER
Ok David I decided to give this sql express profiler a whirl again. The only thing I can see this profiler allows you to do is created traces. I chose some of the things in this profiler such as full text querey, audit login, audit log out, sql transaction, scan started scan stopped. These are a few of the events I decided to check on with profiler and the specific things I chose to check on concerning these events were things like cpu, application name, database id, write, read, start time, end time. I didn't add a filter as I wasn't sure exactly what kind of filters I should use but I did run it with what I stated earlier.
What I got was info back on the database id, application name, duration, start time, stop time and row number. the columns that were to give me cpu, read, write ended up being blank.
The application named turned up .net sql client data provider.
For the most part under duration column I got things like 800, 700 etc... A few rows turned up numbers like 24000, 32000 and 31000. Are these duration numbers in milleseconds, seconds or what is this duration in.
This profiler I am using only will do traces and that is all. I am not sure the significance of traces as I am fairly new to this database thing but I am finding I will be an expert in server speed before you know it.
Question I would have is what events should I have profiled, and what parts of the events are most useful for determining this slow down concern (ie. duration, read, write, etc...).
What kind of filers would be most appropriate and how do I interpret the data that comes back in the profiler in terms of troubleshooting the speed issue or processing issue.
I have used the performance monitor and yes idle system gets the biggest chunk until sql2005 is accessed via the website in my development environment than I see the cpu bar graph spike to near 100% and the idle system graph go to almost nothing.
Thanks again for your help and I know this has got to be some simple thing that we are just not hitting on yet
Bo
What I got was info back on the database id, application name, duration, start time, stop time and row number. the columns that were to give me cpu, read, write ended up being blank.
The application named turned up .net sql client data provider.
For the most part under duration column I got things like 800, 700 etc... A few rows turned up numbers like 24000, 32000 and 31000. Are these duration numbers in milleseconds, seconds or what is this duration in.
This profiler I am using only will do traces and that is all. I am not sure the significance of traces as I am fairly new to this database thing but I am finding I will be an expert in server speed before you know it.
Question I would have is what events should I have profiled, and what parts of the events are most useful for determining this slow down concern (ie. duration, read, write, etc...).
What kind of filers would be most appropriate and how do I interpret the data that comes back in the profiler in terms of troubleshooting the speed issue or processing issue.
I have used the performance monitor and yes idle system gets the biggest chunk until sql2005 is accessed via the website in my development environment than I see the cpu bar graph spike to near 100% and the idle system graph go to almost nothing.
Thanks again for your help and I know this has got to be some simple thing that we are just not hitting on yet
Bo
Hi Bo,
I haven't done it myself, but MS present the latest profiler as able to compare the SQL with the performance events. That is, when the CPU Spikes you can see the Query that started that.
In performance Monitor, are there other things that spike around the same time, diskqueuelength.
Cheers
David
I haven't done it myself, but MS present the latest profiler as able to compare the SQL with the performance events. That is, when the CPU Spikes you can see the Query that started that.
In performance Monitor, are there other things that spike around the same time, diskqueuelength.
Cheers
David
ASKER
not really some other things jump from 00 to 04 or perhaps 05 but nothing like the 00 to 99 % jump I'll look around and see if I can locate this tool that allows you to compare sql with performance monitor because it sounds like that would certainly be useful although it seems like if it was all to do with the structure of the querey's then I should be seeing the same kind of sluggishness on the live site not just my development computer.
ASKER
I have also noticed that if I have been interacting with the database for a while and than shut down the website that was used to interact with the website (aka localhost/dotnetnuke) that sometimes the sqlservr.exe will spike up and stay up to 96% making my system crawl like a snail until I reboot the computer. Note this sqlservr.exe situation I am referring to now happens after I have stopped quereing the database. Maybe not closing the database has something to do with this. I have got think it has to do with some setting on the database opposed to the operating system but it sure is a strange one why querey's that should take a couple of seconds takes 30 seconds to a minute to complete and than after using the database for some time even when the site interacting with it is closed causes this sqlservr.exe to act as if it is still running querey's for some reason.
Anyway thought it might help in trying to solve the problem thanks
Bo
Anyway thought it might help in trying to solve the problem thanks
Bo
ASKER
Ann ad on to what I have already stated I found a site dealing with sql performance issues and noted that changing the page_verify option from torn page to checksum balanced database protection and performance a little more as well as changing the resize % of the log file from 10% to 7%. I also turned on auto update stats but left the create stats as true. These have not however really tapped into the speed issue though they were other avenues I was exploring so if you have any other ideas I would love to hear them and experiment with them thanks
Bo
Bo
Hi Bo,
I've used Brad McGehee's series 'How to Perform a SQL Server Performance Audit' quite extensively.
What is the cache-hit ratio? Can you easily get more ram - its the cheapest hardware upgrade - if there is ram you can borrow and see if that helps some.
Is the production system running on separate boxes - SQL on one and web on another?
Cheers
David
I've used Brad McGehee's series 'How to Perform a SQL Server Performance Audit' quite extensively.
What is the cache-hit ratio? Can you easily get more ram - its the cheapest hardware upgrade - if there is ram you can borrow and see if that helps some.
Is the production system running on separate boxes - SQL on one and web on another?
Cheers
David
ASKER
in the production server I believe yes as it is hosted by another party but on the development server sql server express and windowx xp pro and all is on the same box. My motherboard can handle up to 4 GB but I now have 2 GB you think ram resources would cause a process to take up the whole cpu like this sqlservr.exe.
Also I was reading up today in terms of either converting my account or another from a windows xp pro account to a .net server account with the same settings on sql 2005 express. You think that perhaps my pc is not fine tuned enough. In other words would a pc that is not fully fined tuned as a server cause any type of bottleneck on sql2005 express such as i/o files or lock/block and deadlock things to occur or is attempting to turn a windows xp pro user or create a new user to be tuned to a .net server environment most likely not the issue that is causing the spike.
I could acquire up to 4 GB on this box of ram but being that the process only used like 12 MB or so was wondering if more memory would lessen the strain on the 1 cpu I have that is equivalent to a 2.0 GHz and is actually an athlon 64 bit 3000 that is not using the 64 bit capabilities as it is running on a 32 bit windows xp pro system thanks again
Bo
Also I was reading up today in terms of either converting my account or another from a windows xp pro account to a .net server account with the same settings on sql 2005 express. You think that perhaps my pc is not fine tuned enough. In other words would a pc that is not fully fined tuned as a server cause any type of bottleneck on sql2005 express such as i/o files or lock/block and deadlock things to occur or is attempting to turn a windows xp pro user or create a new user to be tuned to a .net server environment most likely not the issue that is causing the spike.
I could acquire up to 4 GB on this box of ram but being that the process only used like 12 MB or so was wondering if more memory would lessen the strain on the 1 cpu I have that is equivalent to a 2.0 GHz and is actually an athlon 64 bit 3000 that is not using the 64 bit capabilities as it is running on a 32 bit windows xp pro system thanks again
Bo
Hi Bo,
Its just that memory is easy to test and least disruptive and expensive to fix. In this case I'd suggest that the problem is least likely to be memory. If system was short on memory I'd expect the CPU to be more idle than busy as it waits for disk access.
There are two things here: tune SQL from the hardware through to SQL to the database, and tune the queries.
Finding Queries to Tune:
From your profile trace, find the querys that have the biggest CPU time. It doesn't matter what the units are - the bigger numbers are what you want to look at.
In terms of bigger, you are looking for a couple of things - the query(s) with the longest individual time(s), and the query(s) with the total longest time(s). Easiest to do with the profile trace in a table, and then just query the table.
HTH
David
Its just that memory is easy to test and least disruptive and expensive to fix. In this case I'd suggest that the problem is least likely to be memory. If system was short on memory I'd expect the CPU to be more idle than busy as it waits for disk access.
There are two things here: tune SQL from the hardware through to SQL to the database, and tune the queries.
Finding Queries to Tune:
From your profile trace, find the querys that have the biggest CPU time. It doesn't matter what the units are - the bigger numbers are what you want to look at.
In terms of bigger, you are looking for a couple of things - the query(s) with the longest individual time(s), and the query(s) with the total longest time(s). Easiest to do with the profile trace in a table, and then just query the table.
HTH
David
ASKER
how would I tune sql from the hardware through to sql to the database. in terms of the querey's is their an easy tool to do this with using sql2005 express as I know the express version seems to have limited analysis types of tools and while I can use the third party trace tool I don't believe I can attach it to any given table but rather just able to select events and properties of those events such as sql full test as an event and duration as a property thanks
Bo
Bo
Hi,
There is a free download from MS for SQL Server Management Studio Express, but if you have now gotten the standard tools installed included SSMS, then you can't install the express edition.
I think you might have foudn by now that the standard tools connect to Express fine.
The standard profiler for SQL 2000 could either display the results of a trace, send them to a file, or insert in a table. Look for the check box that saves Save to table:
Then simply query the table as outlined above.
HTH
David
There is a free download from MS for SQL Server Management Studio Express, but if you have now gotten the standard tools installed included SSMS, then you can't install the express edition.
I think you might have foudn by now that the standard tools connect to Express fine.
The standard profiler for SQL 2000 could either display the results of a trace, send them to a file, or insert in a table. Look for the check box that saves Save to table:
Then simply query the table as outlined above.
HTH
David
ASKER
hello David,
I was able to install the 2005 dts but as I looked non of these included the profiler the tools that came with this dts were as follows
1. DTattach.exe
2.DTexec.exe
3dtsdebughost.exe
4. dtshost.exe
5.dtsmigrationwizard.exe
6.dtswizard.exe
7.dtutil.exe
8.user control test container
9.Microsoft.SqlServer.Expr
10. ssmsee.exe (which is the same as sequel server management studio)
That is all the tools I have to work with in my tool set coming with 2005 express. As far sql 2000 profiler is concerned I looked into trying to get the 2000 profiler and apparently that comes only as an integrated component of sql 2000 server and not as a seperate tool set.
Unfortunately I can't really test this thing with server 2000 as some components of the site require either sql 2005 or 2005 express.
Do you know where I could download it I looked but couldn't find just 2000 profiler thanks a lot.
This problem is probably something simple but just haven't hit it yet. Thanks again
Bo
I was able to install the 2005 dts but as I looked non of these included the profiler the tools that came with this dts were as follows
1. DTattach.exe
2.DTexec.exe
3dtsdebughost.exe
4. dtshost.exe
5.dtsmigrationwizard.exe
6.dtswizard.exe
7.dtutil.exe
8.user control test container
9.Microsoft.SqlServer.Expr
10. ssmsee.exe (which is the same as sequel server management studio)
That is all the tools I have to work with in my tool set coming with 2005 express. As far sql 2000 profiler is concerned I looked into trying to get the 2000 profiler and apparently that comes only as an integrated component of sql 2000 server and not as a seperate tool set.
Unfortunately I can't really test this thing with server 2000 as some components of the site require either sql 2005 or 2005 express.
Do you know where I could download it I looked but couldn't find just 2000 profiler thanks a lot.
This problem is probably something simple but just haven't hit it yet. Thanks again
Bo
Hi Bo,
If you have somewhere, access to SQL 2005, then you have a licence for the SQL 2005 Workstation tools and documentation. If your SQL 2005 is hosted the licence might be a bit different. You may need to talk to your hosting provider.
So the advice is to obtain a SQL 2005 install media CDs or DVD, and install the workstation tools.
An evaluation of SQL 2005 will do the trick. I'm not aware if the tools are time sensitive, but woudl expect the server component to be time-limited.
HTH
David
If you have somewhere, access to SQL 2005, then you have a licence for the SQL 2005 Workstation tools and documentation. If your SQL 2005 is hosted the licence might be a bit different. You may need to talk to your hosting provider.
So the advice is to obtain a SQL 2005 install media CDs or DVD, and install the workstation tools.
An evaluation of SQL 2005 will do the trick. I'm not aware if the tools are time sensitive, but woudl expect the server component to be time-limited.
HTH
David
ASKER
Ok I got the sql 2005 standard on my system now
You are right their is a lot more to it. as I write I am running a trace to a table called analysis_berkshires and used the standard (default) template too do this. Was that the right thing to do or should I be using a different kind of template other than standard?
Also how do I tune SQL from the hardware through to SQL to the database. upon finding the information as you indicated in another comment that I have copied below
--------------------------
From your profile trace, find the querys that have the biggest CPU time. It doesn't matter what the units are - the bigger numbers are what you want to look at.
In terms of bigger, you are looking for a couple of things - the query(s) with the longest individual time(s), and the query(s) with the total longest time(s). Easiest to do with the profile trace in a table, and then just query the table.
--------------------------
Once I gather this information from the trace table how do I go about tackling the issue. What do I do with tables or instances that seem to be taking a lot of cpu time, individual and collective time etc...
I think the first suggestion about tuning the hardware is an important one and obviously I wouldn't use profiler for tuning the hardware so how would I go about that task as that might be the issue even though my hardware is fairly fast.
Obviuosly I do have a lot of hardware on my computer such as tv card, sound card, dvd encoder, agi video card geforce 440 mx. I have about 5 pci cards connected to my computer that are working properly could it be that the hardware itself it causing this slow down.
What if I was to disable some of these things in my device manager via windows xp would that be another angle to go in on trying to beat this problem thanks
Bo
You are right their is a lot more to it. as I write I am running a trace to a table called analysis_berkshires and used the standard (default) template too do this. Was that the right thing to do or should I be using a different kind of template other than standard?
Also how do I tune SQL from the hardware through to SQL to the database. upon finding the information as you indicated in another comment that I have copied below
--------------------------
From your profile trace, find the querys that have the biggest CPU time. It doesn't matter what the units are - the bigger numbers are what you want to look at.
In terms of bigger, you are looking for a couple of things - the query(s) with the longest individual time(s), and the query(s) with the total longest time(s). Easiest to do with the profile trace in a table, and then just query the table.
--------------------------
Once I gather this information from the trace table how do I go about tackling the issue. What do I do with tables or instances that seem to be taking a lot of cpu time, individual and collective time etc...
I think the first suggestion about tuning the hardware is an important one and obviously I wouldn't use profiler for tuning the hardware so how would I go about that task as that might be the issue even though my hardware is fairly fast.
Obviuosly I do have a lot of hardware on my computer such as tv card, sound card, dvd encoder, agi video card geforce 440 mx. I have about 5 pci cards connected to my computer that are working properly could it be that the hardware itself it causing this slow down.
What if I was to disable some of these things in my device manager via windows xp would that be another angle to go in on trying to beat this problem thanks
Bo
ASKER
I read some where that their were 4 main bottlenecks when it comes to database servers one being cpu another memory, another i/o files and the other lockout/block and deadlock in regards to what the database does.
I think a athlon 3000 should be a powerful enough cpu to handle one user doing querey's and certainly 2 GB of ram both the same size but what is the i/o file thing the speed that files are input or output? Would that be effected by the kind of hard drive one has like mine is the sata 10,000 rpm hard drive.
I don't think the lockout/block and deadlock would be the issue if it is strictly a server issue as the speed of this website on the live production site is 4 times or greater the speed I get via my development environment.
I want to say that the issue has to do somehow with:
A. how my software is tuned to perform (i.e. operating system, sql software, any other main programs that are responsible for the operations of the computer.
B. Either how the hardware is tuned or perhaps hardware that interferes with heavy activity such as database is. What do you think?
Thanks for continuing to help me troubleshoot this issue as when it is resolved it will save loads of time in regards to the development environment.
Bo
I think a athlon 3000 should be a powerful enough cpu to handle one user doing querey's and certainly 2 GB of ram both the same size but what is the i/o file thing the speed that files are input or output? Would that be effected by the kind of hard drive one has like mine is the sata 10,000 rpm hard drive.
I don't think the lockout/block and deadlock would be the issue if it is strictly a server issue as the speed of this website on the live production site is 4 times or greater the speed I get via my development environment.
I want to say that the issue has to do somehow with:
A. how my software is tuned to perform (i.e. operating system, sql software, any other main programs that are responsible for the operations of the computer.
B. Either how the hardware is tuned or perhaps hardware that interferes with heavy activity such as database is. What do you think?
Thanks for continuing to help me troubleshoot this issue as when it is resolved it will save loads of time in regards to the development environment.
Bo
ASKER
I was wondering if the problem I am having with the speed and cpu consumption could be concerning the operating system not being as tuned as it ought for a server environment. See I am using the computer I use for my everyday things such as design, office stuff, etc... to run sql express or in this case for the time being enterprise 2005 trial server.
Though it is running on a different hard drive than windows xp pro is running on it still is running off the same operating system same processor and ram count.
I know windows 2003 server obviously has some differences than windows xp pro that makes it perform better as a server. I was wondering if you knew first of all what those things were and secondly if you could replicate the things in windows 2003 that make it perform so well as a server environment in windows xp pro.
I have a feeling that my problems may lie in windows xp pro not totally tuned to perform as a background server type of application while I set cpu to give priority to the background and ram to give memory to system cache opposed to normal front end programs I wonder if their are other considerations other than those things that need to be addressed when trying to create an efficient development environment for asp.net or in this case dotnetnuke.
for example what kinds of bottlenecks could windows xp pro have that I am not seeing or understanding that could stifle performance.
I will say while I have two sticks of 1 GB each one is samsung and the other is generic. Coudl ths fact play a role into why this sqlservr.exe is taking up so much process resource?
Just recently I disabled all my hardware I felt I could such as cd drives, audio devices, usb ports, monitors, modems, floppy disk drives, tv cards, input devices, etc... to see if their was some kind of hardware bottleneck that was causing the sql 2005 to return results so slowly.
Also I did do as you said concerning the proffiler and I am afraid it kept resetting thought I did get a few large duration hits and the largest number on cpu I could get was about 16 and maybe a 15. For the most part they were 0.
However I am clueless as to what to do with the data I found that was saved into this table I have a mind of trying to run this profiler again while the computer is doing nothing per say and see if I get a lot of resets or different results.
By the way thanks for all the help you are giving me and if you have any suggestions hardware or software wise that could be the cause of a single process taking up so much of a cpu resource for so long please let me in on it as I need to know all I can about preparing this development environment in a way that I can efficiently work in.
Could using your home computer (that you use for anything and everything) as a server be a bottleneck in itself. Because like I stated earlier the only transitions I make from being a regular desktop user on this box to a development environment is simply transferring cpu priority from programs to background tasks and memory from programs to system cache. If their is anything else I could do to tweak my desktop box into a development box for the period of time I use it as a development environment which constantly is accessing sql 2005 database installed on the same box but on another hard drive going 10,000 rpm opposed to 7200 rpm let me know or if you know of anyone else who might have a clue let me know because this sure can be a headache to develop for this website and wait approx. a minute a times just for one click on the website that is heavily reliant on the database since it is a framework environment which does everything it does dynamically and verry little statically thanks
Bo
Though it is running on a different hard drive than windows xp pro is running on it still is running off the same operating system same processor and ram count.
I know windows 2003 server obviously has some differences than windows xp pro that makes it perform better as a server. I was wondering if you knew first of all what those things were and secondly if you could replicate the things in windows 2003 that make it perform so well as a server environment in windows xp pro.
I have a feeling that my problems may lie in windows xp pro not totally tuned to perform as a background server type of application while I set cpu to give priority to the background and ram to give memory to system cache opposed to normal front end programs I wonder if their are other considerations other than those things that need to be addressed when trying to create an efficient development environment for asp.net or in this case dotnetnuke.
for example what kinds of bottlenecks could windows xp pro have that I am not seeing or understanding that could stifle performance.
I will say while I have two sticks of 1 GB each one is samsung and the other is generic. Coudl ths fact play a role into why this sqlservr.exe is taking up so much process resource?
Just recently I disabled all my hardware I felt I could such as cd drives, audio devices, usb ports, monitors, modems, floppy disk drives, tv cards, input devices, etc... to see if their was some kind of hardware bottleneck that was causing the sql 2005 to return results so slowly.
Also I did do as you said concerning the proffiler and I am afraid it kept resetting thought I did get a few large duration hits and the largest number on cpu I could get was about 16 and maybe a 15. For the most part they were 0.
However I am clueless as to what to do with the data I found that was saved into this table I have a mind of trying to run this profiler again while the computer is doing nothing per say and see if I get a lot of resets or different results.
By the way thanks for all the help you are giving me and if you have any suggestions hardware or software wise that could be the cause of a single process taking up so much of a cpu resource for so long please let me in on it as I need to know all I can about preparing this development environment in a way that I can efficiently work in.
Could using your home computer (that you use for anything and everything) as a server be a bottleneck in itself. Because like I stated earlier the only transitions I make from being a regular desktop user on this box to a development environment is simply transferring cpu priority from programs to background tasks and memory from programs to system cache. If their is anything else I could do to tweak my desktop box into a development box for the period of time I use it as a development environment which constantly is accessing sql 2005 database installed on the same box but on another hard drive going 10,000 rpm opposed to 7200 rpm let me know or if you know of anyone else who might have a clue let me know because this sure can be a headache to develop for this website and wait approx. a minute a times just for one click on the website that is heavily reliant on the database since it is a framework environment which does everything it does dynamically and verry little statically thanks
Bo
ASKER
One last thing and that is I currently have a dsl connection that is very good but obviously not something like a t1 or t3 connection. My guess is this would not be a factor on a localhost development type of situation but just another thing to through out there thanks
Bo
Bo
ASKER
I was wondering if you could give me an indication if I am experiencing a cpu bottleneck on my box (running 1 athlon 64 3000+ equivalent to 2.g GHZ) by giving you some statistics my server pulled up from the sys.dm_os_wait_stats.
--------------------------
signal_wait_time_ms: 65996
%signal(cpu)waits: 0.63
resource_wait_time_ms: 10329623
%resource waits: 99.37%
--------------------------
could this be a cpu bottleneck or an indication of an i/o file bottleneck or is this information given any clue as to the cause of performance issues thanks
Bo
--------------------------
signal_wait_time_ms: 65996
%signal(cpu)waits: 0.63
resource_wait_time_ms: 10329623
%resource waits: 99.37%
--------------------------
could this be a cpu bottleneck or an indication of an i/o file bottleneck or is this information given any clue as to the cause of performance issues thanks
Bo
ASKER
hello
Just added some counters on the performance monitor in terms of memory.
What I find is that cpu is always at about 100% and in regards to memory counters here is what I have gathered:
commit limit
commit bytes
available kB
system cache resident bytes
All of the above are at a static 100% in a bar grapgh view they do not seem to fluctuate.
I notice that pages/sec reading does not fluctuate either however it remains at about 0.
I don't know if these readings are good or bad. For some pages I do get almost 100% on the Pages/sec but only for 20 seconds or so and than it drops to 0 but on these other memory attributes it doesn't fluctuate. I am not sure if that indicates that their is no memory bottleneck or that there is thanks for looking at these stats
Bo
Just added some counters on the performance monitor in terms of memory.
What I find is that cpu is always at about 100% and in regards to memory counters here is what I have gathered:
commit limit
commit bytes
available kB
system cache resident bytes
All of the above are at a static 100% in a bar grapgh view they do not seem to fluctuate.
I notice that pages/sec reading does not fluctuate either however it remains at about 0.
I don't know if these readings are good or bad. For some pages I do get almost 100% on the Pages/sec but only for 20 seconds or so and than it drops to 0 but on these other memory attributes it doesn't fluctuate. I am not sure if that indicates that their is no memory bottleneck or that there is thanks for looking at these stats
Bo
ASKER
Hi David,
One more comment on my part till I hear from you or someone else and that is I did about 3 traces on the standard, duration and tuning profiles. I didn't find anything out of the ordinary I mean duration time at most went to1 their were some considerable read times that went into the 3344 but not all the time in fact most of the traces were 0 across the board.
One thing I did notice however was that whne I was running the tracer while executing the database via this dotnetnuke website I noticed their was some sort of overload with the profiler accessing the database and the dotnetnuke site quereying it at the same time as it timed out more than once.
However CPU wise the biggest hits I got were about 15 or 16 as I mentioned in an earlier post.
I am wondering if my only bottle neck is either not having this windows xp pro server tuned as much as I am supposed to concerning system cache resources and other such things or that no tuning of xp pro will be sufficient for a development type environment and my only solution is to go perhaps server 2003 because upon doing performance monitors, profillers and simple process activity I couldn't really discover any bottlenecks or faulty database designs via the querey's I got that would lead me to believe any abnormal activity was happening with querey's or hardware which leaves me to wonder if the operating system I have is the issue be it needing further tuning I am not sure how to do or simply an operating system like 2003 that is written to act in a server sort of way and hence allow sql server to gain superior performance.
I don't know let me know your take after reading all of these posts as to what the next shoudl be in your reckining
Thanks
Bo
One more comment on my part till I hear from you or someone else and that is I did about 3 traces on the standard, duration and tuning profiles. I didn't find anything out of the ordinary I mean duration time at most went to1 their were some considerable read times that went into the 3344 but not all the time in fact most of the traces were 0 across the board.
One thing I did notice however was that whne I was running the tracer while executing the database via this dotnetnuke website I noticed their was some sort of overload with the profiler accessing the database and the dotnetnuke site quereying it at the same time as it timed out more than once.
However CPU wise the biggest hits I got were about 15 or 16 as I mentioned in an earlier post.
I am wondering if my only bottle neck is either not having this windows xp pro server tuned as much as I am supposed to concerning system cache resources and other such things or that no tuning of xp pro will be sufficient for a development type environment and my only solution is to go perhaps server 2003 because upon doing performance monitors, profillers and simple process activity I couldn't really discover any bottlenecks or faulty database designs via the querey's I got that would lead me to believe any abnormal activity was happening with querey's or hardware which leaves me to wonder if the operating system I have is the issue be it needing further tuning I am not sure how to do or simply an operating system like 2003 that is written to act in a server sort of way and hence allow sql server to gain superior performance.
I don't know let me know your take after reading all of these posts as to what the next shoudl be in your reckining
Thanks
Bo
ASKER
Database Analysis
Before causing the sqlservr.exe process to reach heights of approx. 99% by accessing my development site at localhost/dotnetnuke
I did the following querey on my sql server to get a feel for its overall performance in regards to cpu pressure.
Here was my findings. Before accessing the development site via internet explorer I performed the folloiwng querey on sys.dm_os_wait_stats table.
Select signal_wait_time_ms=sum(si
,'%signal (cpu) waits' = cast(100.0 * sum(signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2))
,resource_wait_time_ms=sum
,'%resource waits'= cast(100.0 * sum(wait_time_ms - signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2))
From sys.dm_os_wait_stats
The results were similiar to the following statistics as I did this about 5 or 6 times before accessing localhost/dotnetnuke:
--------------------------
signal_wait_time_ms: 451
%signal(cpu)waits:0.04%
resource_wait_time_ms:1,21
%resource waits: 99.96 %
--------------------------
The results of this same querey differed when I type http://localhost/dotnetnuke into the address bar of internet explorer thereby accessing my development site. The more times I clicked the higher the signal_waite_time_ms and %signal(cpu)waits became however in contrast the resource_wait_time_ms and %resource waits dropped hear is the final querey that I got just as the home page of dotnetnuke showed up about 40 seconds to a minute later (note: I turned off the antivirus this time when I first tried this the antivirus was on and the signal wait time was almost 10 times higher
--------------------------
signal_wait_time_ms: 33686
%signal(cpu)waits:1.65%
resource_wait_time_ms:2,00
%resource waits: 98.35 %
--------------------------
After pressing the login button in order to get the username and password window to complete this request took approx. 32 seconds and cpu signal rose a little more here is the end resault from first making the querey and seeing the results on the browser
--------------------------
signal_wait_time_ms: 67,092
%signal(cpu)waits:2.58%
resource_wait_time_ms:2,53
%resource waits: 97.42 %
--------------------------
After typing my username and password from the time a pressed submit (which quereyed the table for logging into the framework till the time the browser should me logged in was about 1 minute and 5 seconds or 70 seconds. Once I noticed I was logged in I did another final querey to check out cpu pressure here are the stats
--------------------------
signal_wait_time_ms: 135,170
%signal(cpu)waits:4.17%
resource_wait_time_ms:3,10
%resource waits: 95.83 %
--------------------------
clicked on a link called web cams waited about 2 minutes and 25 seconds. Seemed like the page might of hung for a few seconds but from the time I clicked on the web cam link to the time I actually got the data from it to the browser it was about 2 minutes 25 seconds and once the data successfully transferred I did another querey on cpu pressure and here is what I found
--------------------------
signal_wait_time_ms: 292,278
%signal(cpu)waits: 7.01%
resource_wait_time_ms:3,87
%resource waits: 92.99 %
--------------------------
clicked on the edit text/html link for the web cam page took 1 minute 49 seconds before I got the page and did another cpu pressure querey this is what I found remember not even my antivirus is running in the background at this time sqlservr.exe is the main thing that is slowing down the system here are the results
--------------------------
signal_wait_time_ms: 471824
%signal(cpu)waits: 8.96%
resource_wait_time_ms:4,79
%resource waits: 91.04 %
--------------------------
A quick look at memory and i/o usages from the processes section of task manager gives us the following in regards to this sqlservr.exe at this time after the above querey's were executed.
VM Size: 110,796 K
Memory usage: 102,776 K
Page pool 149K
i/O reads: 2,548
I/O writes: 1,912
I/O other: 6,099
I/O read bytes: 43,834,150
I/O write bytes: 5,054,742
I/O other bytes: 91,330
--------------------------
Looking at performance monitor:
while a querey is being initiated the cpu goes to 100% but not disk time for the hd that the server is on.
The I/O quoted in the above section may not reflect the hd which is constantly getting activity from data transfer but it does reflect the those I/O writes specifically done via sqlservr.exe process.
--------------------------
Clicked on update on the page where I can edited some web cams after I had to querey the database to get back to that page since somehow I got off of it and it took 1 minute and 33 seconds from the time I clicked update to the time I saw the result at the time of the result I did another cpu pressure querey and found this:
--------------------------
signal_wait_time_ms: 1,303,700
%signal(cpu)waits: 14.93%
resource_wait_time_ms:7,42
%resource waits: 85.07 %
--------------------------
I typed in an erronous address in the address bar to get a cannot find web site. Than without clearing cache or anything typed in http://localhost/dotnetnuke again and in 1 second was brought to the home page. I wonder if this page somehow gets cached after the first time you querey the database for this page at any rate here was the cpu pressure as you will see not much change
--------------------------
signal_wait_time_ms: 1,341,144
%signal(cpu)waits: 14.45%
resource_wait_time_ms:7,93
%resource waits: 85.55 %
--------------------------
After doing a couple querey's I didn't document here I started on the same path as I did when I began this querey test eariler in this document without clearing cookies, cache, files, etc... from the browser just to see if the time would be in a couple seconds as it was to initally go to localhost/dotnetnuke once I initially queried the area.
What I found was a tad quicker querey but didn't hang this time. it took this time 52 seconds to do the same querey using the same open browser (not another browser window) that it took almost three minutes last time. I didn't process a cpu pressure on this round as I wanted to click on edit text again (an area that didn't hang the first time around) to get a more accurate picture concerning the idea that certain areas are in cache or once accessed become quicer the second time in a browser session.
This test failed as the application hung. So I decided without restarting windows to open a new browser and start this process over again to see if:
1. the localhost/dotnetnuke opens up immediately
2. if other activity the requires interaction with sql server.exe is lessoned or takes longer
--------------------------
Opened a new browser window and I think it opened a tad bit faster but not by much as I had an approximation the first it was 32 seconds this time not much difference but still hadn't restarted windows since my test and decided to do another cpu pressure querey and got these results
--------------------------
signal_wait_time_ms: 2,696,793
%signal(cpu)waits: 20.79%
resource_wait_time_ms:10,2
%resource waits: 79.21 %
--------------------------
From the first stats to the one above was approximately 2 hours of quereying so using the sql server as it is now for about 2 hours causes the signal_wait_time, %signal(cpu)waits, and resource_wait_time to slowly need to wait more and more time while some how the %resource waits decreases.
I/O processes seem similar as does VM and actual memory usage. All of these are a bit higher but revisiting this process window about 45 minutes later or so no drastic increases have been seen a couple more thousand reads and writes, another thousand bytes of memory and VM but really nothing to make me think I am seeing significant problems in the I/O department as resource wait time would suggest.
It is significant that the %of resource waits slowly decreases upon continually quereying of the database while all the other three fields slowly increase in time.
--------------------------
Last thing I tried was deleting all temprary files from the browser and than shutting it down and starting a new window to see what effect that had on processing. It was significant as this time instead of close to 40 seconds since all temp files and browser files were deleted it took a little over 2 minutes to process and than I did one last cpu pressure querey to see if that was any more or less significant here is what I found
--------------------------
signal_wait_time_ms: 4,142,777
%signal(cpu)waits: 24.58%
resource_wait_time_ms:12,7
%resource waits: 75.42 %
--------------------------
I don't know if these stats indicate bottlenecks of cpu, memory, i/o files, or simply just an inefficent operating system. However thought this might help you understand what is happening in terms of trend opposed to just a one time querey.
Like I mentioned earlier I could not do this using profiler on the database that I am accessing for these files as somehow running profiler and querying the database via the browser causes sql to freeze or at least to hang and I get a dotnetnuke error that the page timed out but I could do this trend when quereying system tables which aren't in the database that is used for this development site. Thanks again for your help
Bo
Before causing the sqlservr.exe process to reach heights of approx. 99% by accessing my development site at localhost/dotnetnuke
I did the following querey on my sql server to get a feel for its overall performance in regards to cpu pressure.
Here was my findings. Before accessing the development site via internet explorer I performed the folloiwng querey on sys.dm_os_wait_stats table.
Select signal_wait_time_ms=sum(si
,'%signal (cpu) waits' = cast(100.0 * sum(signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2))
,resource_wait_time_ms=sum
,'%resource waits'= cast(100.0 * sum(wait_time_ms - signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2))
From sys.dm_os_wait_stats
The results were similiar to the following statistics as I did this about 5 or 6 times before accessing localhost/dotnetnuke:
--------------------------
signal_wait_time_ms: 451
%signal(cpu)waits:0.04%
resource_wait_time_ms:1,21
%resource waits: 99.96 %
--------------------------
The results of this same querey differed when I type http://localhost/dotnetnuke into the address bar of internet explorer thereby accessing my development site. The more times I clicked the higher the signal_waite_time_ms and %signal(cpu)waits became however in contrast the resource_wait_time_ms and %resource waits dropped hear is the final querey that I got just as the home page of dotnetnuke showed up about 40 seconds to a minute later (note: I turned off the antivirus this time when I first tried this the antivirus was on and the signal wait time was almost 10 times higher
--------------------------
signal_wait_time_ms: 33686
%signal(cpu)waits:1.65%
resource_wait_time_ms:2,00
%resource waits: 98.35 %
--------------------------
After pressing the login button in order to get the username and password window to complete this request took approx. 32 seconds and cpu signal rose a little more here is the end resault from first making the querey and seeing the results on the browser
--------------------------
signal_wait_time_ms: 67,092
%signal(cpu)waits:2.58%
resource_wait_time_ms:2,53
%resource waits: 97.42 %
--------------------------
After typing my username and password from the time a pressed submit (which quereyed the table for logging into the framework till the time the browser should me logged in was about 1 minute and 5 seconds or 70 seconds. Once I noticed I was logged in I did another final querey to check out cpu pressure here are the stats
--------------------------
signal_wait_time_ms: 135,170
%signal(cpu)waits:4.17%
resource_wait_time_ms:3,10
%resource waits: 95.83 %
--------------------------
clicked on a link called web cams waited about 2 minutes and 25 seconds. Seemed like the page might of hung for a few seconds but from the time I clicked on the web cam link to the time I actually got the data from it to the browser it was about 2 minutes 25 seconds and once the data successfully transferred I did another querey on cpu pressure and here is what I found
--------------------------
signal_wait_time_ms: 292,278
%signal(cpu)waits: 7.01%
resource_wait_time_ms:3,87
%resource waits: 92.99 %
--------------------------
clicked on the edit text/html link for the web cam page took 1 minute 49 seconds before I got the page and did another cpu pressure querey this is what I found remember not even my antivirus is running in the background at this time sqlservr.exe is the main thing that is slowing down the system here are the results
--------------------------
signal_wait_time_ms: 471824
%signal(cpu)waits: 8.96%
resource_wait_time_ms:4,79
%resource waits: 91.04 %
--------------------------
A quick look at memory and i/o usages from the processes section of task manager gives us the following in regards to this sqlservr.exe at this time after the above querey's were executed.
VM Size: 110,796 K
Memory usage: 102,776 K
Page pool 149K
i/O reads: 2,548
I/O writes: 1,912
I/O other: 6,099
I/O read bytes: 43,834,150
I/O write bytes: 5,054,742
I/O other bytes: 91,330
--------------------------
Looking at performance monitor:
while a querey is being initiated the cpu goes to 100% but not disk time for the hd that the server is on.
The I/O quoted in the above section may not reflect the hd which is constantly getting activity from data transfer but it does reflect the those I/O writes specifically done via sqlservr.exe process.
--------------------------
Clicked on update on the page where I can edited some web cams after I had to querey the database to get back to that page since somehow I got off of it and it took 1 minute and 33 seconds from the time I clicked update to the time I saw the result at the time of the result I did another cpu pressure querey and found this:
--------------------------
signal_wait_time_ms: 1,303,700
%signal(cpu)waits: 14.93%
resource_wait_time_ms:7,42
%resource waits: 85.07 %
--------------------------
I typed in an erronous address in the address bar to get a cannot find web site. Than without clearing cache or anything typed in http://localhost/dotnetnuke again and in 1 second was brought to the home page. I wonder if this page somehow gets cached after the first time you querey the database for this page at any rate here was the cpu pressure as you will see not much change
--------------------------
signal_wait_time_ms: 1,341,144
%signal(cpu)waits: 14.45%
resource_wait_time_ms:7,93
%resource waits: 85.55 %
--------------------------
After doing a couple querey's I didn't document here I started on the same path as I did when I began this querey test eariler in this document without clearing cookies, cache, files, etc... from the browser just to see if the time would be in a couple seconds as it was to initally go to localhost/dotnetnuke once I initially queried the area.
What I found was a tad quicker querey but didn't hang this time. it took this time 52 seconds to do the same querey using the same open browser (not another browser window) that it took almost three minutes last time. I didn't process a cpu pressure on this round as I wanted to click on edit text again (an area that didn't hang the first time around) to get a more accurate picture concerning the idea that certain areas are in cache or once accessed become quicer the second time in a browser session.
This test failed as the application hung. So I decided without restarting windows to open a new browser and start this process over again to see if:
1. the localhost/dotnetnuke opens up immediately
2. if other activity the requires interaction with sql server.exe is lessoned or takes longer
--------------------------
Opened a new browser window and I think it opened a tad bit faster but not by much as I had an approximation the first it was 32 seconds this time not much difference but still hadn't restarted windows since my test and decided to do another cpu pressure querey and got these results
--------------------------
signal_wait_time_ms: 2,696,793
%signal(cpu)waits: 20.79%
resource_wait_time_ms:10,2
%resource waits: 79.21 %
--------------------------
From the first stats to the one above was approximately 2 hours of quereying so using the sql server as it is now for about 2 hours causes the signal_wait_time, %signal(cpu)waits, and resource_wait_time to slowly need to wait more and more time while some how the %resource waits decreases.
I/O processes seem similar as does VM and actual memory usage. All of these are a bit higher but revisiting this process window about 45 minutes later or so no drastic increases have been seen a couple more thousand reads and writes, another thousand bytes of memory and VM but really nothing to make me think I am seeing significant problems in the I/O department as resource wait time would suggest.
It is significant that the %of resource waits slowly decreases upon continually quereying of the database while all the other three fields slowly increase in time.
--------------------------
Last thing I tried was deleting all temprary files from the browser and than shutting it down and starting a new window to see what effect that had on processing. It was significant as this time instead of close to 40 seconds since all temp files and browser files were deleted it took a little over 2 minutes to process and than I did one last cpu pressure querey to see if that was any more or less significant here is what I found
--------------------------
signal_wait_time_ms: 4,142,777
%signal(cpu)waits: 24.58%
resource_wait_time_ms:12,7
%resource waits: 75.42 %
--------------------------
I don't know if these stats indicate bottlenecks of cpu, memory, i/o files, or simply just an inefficent operating system. However thought this might help you understand what is happening in terms of trend opposed to just a one time querey.
Like I mentioned earlier I could not do this using profiler on the database that I am accessing for these files as somehow running profiler and querying the database via the browser causes sql to freeze or at least to hang and I get a dotnetnuke error that the page timed out but I could do this trend when quereying system tables which aren't in the database that is used for this development site. Thanks again for your help
Bo
Hi,
I'm wondering if a large contributor is the web-page itself.
What does the cpu look like if re-playing a saved trace?
HTH
David
I'm wondering if a large contributor is the web-page itself.
What does the cpu look like if re-playing a saved trace?
HTH
David
ASKER
I just opened a saved trace since I saved it as a table on the sql server and it jumped to like 45% for a second but than back down to 00. Now in this case I was not accessing the localhsot/dotnetnuke via the web browser which is what points to the dynamic website in my development environment generated fully via the sql server.
I would be surprised in some sense if it were the web page itself because if that were the case than why wouldn't it take about a minute per click when on the production site which isn't hosted by my computer obviously but by a host site running dual quad core xeon 3.0 GHZ on a windows 2003 server.
Of course in their case I believe the operating system is on one box and database server on another.
I wonder would installing windows server 2003 on one hard drive and the sql server 2005 on another increase performance. In principle that is the structure I am already using with the sql server being on the faster 10,000 rpm hard drive but I am really wondering is the fact I am running windows xp pro opposed to windows server 2003 plays a role in this.
upon a second replay of the trace or opening of the table I get the following results in the process windows in regards to sqlservr.exe and cpu usage:
I get about 39% for a half second when I querey a similar or open a similar analysis table(which are the saved traces).
hmm don't think I am running into any hardware bottlenecks do you. Thanks for your continual help
Bo
I would be surprised in some sense if it were the web page itself because if that were the case than why wouldn't it take about a minute per click when on the production site which isn't hosted by my computer obviously but by a host site running dual quad core xeon 3.0 GHZ on a windows 2003 server.
Of course in their case I believe the operating system is on one box and database server on another.
I wonder would installing windows server 2003 on one hard drive and the sql server 2005 on another increase performance. In principle that is the structure I am already using with the sql server being on the faster 10,000 rpm hard drive but I am really wondering is the fact I am running windows xp pro opposed to windows server 2003 plays a role in this.
upon a second replay of the trace or opening of the table I get the following results in the process windows in regards to sqlservr.exe and cpu usage:
I get about 39% for a half second when I querey a similar or open a similar analysis table(which are the saved traces).
hmm don't think I am running into any hardware bottlenecks do you. Thanks for your continual help
Bo
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
it is running off of iis in xp pro yes the 5.1 version I believe.
It is only running one development site now which is this dotnetnuke however in regards to performance you think maybe performance issues in terms of speed could originate from the acutal iis server? hmm, that would be interesting if it is found that all this stuff about trying to tune windows and sql server was actual not the source but iis was.
Well if you know of any area of iis that might deal with the performance of data retreival from a database server let me know but that is an itneresting twist on this whole issue
Bo
It is only running one development site now which is this dotnetnuke however in regards to performance you think maybe performance issues in terms of speed could originate from the acutal iis server? hmm, that would be interesting if it is found that all this stuff about trying to tune windows and sql server was actual not the source but iis was.
Well if you know of any area of iis that might deal with the performance of data retreival from a database server let me know but that is an itneresting twist on this whole issue
Bo
Hi Bo,
My thought is that maybe you need to post a question about dotnetnuke in a web or IIS area, with a link to this discussion, and possibly close this one in a couple of days.
We've pretty well proved that the issue isn't with SQL or the operating system, so while you still have your issue, the question as asked is answered.
Sorry we couldn't get a better result.
Cheers
David
My thought is that maybe you need to post a question about dotnetnuke in a web or IIS area, with a link to this discussion, and possibly close this one in a couple of days.
We've pretty well proved that the issue isn't with SQL or the operating system, so while you still have your issue, the question as asked is answered.
Sorry we couldn't get a better result.
Cheers
David
ASKER
Well I appreciate the help you have given me and the consistant dialogue we have. I'll close it soon unless you have any other closing thoughts that may have popped into your mind since the last writing othereise if not I will close this and call it answered because you have been a great help to me in this journey in trying to understand this whole situation.
So in conclusion you are basically saying that you don't think it has any thing to do with the version of sql I have or the fact I am using windows xp pro but rather either has to do with iis settings or just the way the dotnetnuke is running.
Have you sense from our conversation that their may be a cpu, i/o, memory, or any other kinds of bottlenecks that could factor into this?
Let me know the last thoughts of this and than I will close our discussion here but thank you for giving it your best effort anyway
Bo
So in conclusion you are basically saying that you don't think it has any thing to do with the version of sql I have or the fact I am using windows xp pro but rather either has to do with iis settings or just the way the dotnetnuke is running.
Have you sense from our conversation that their may be a cpu, i/o, memory, or any other kinds of bottlenecks that could factor into this?
Let me know the last thoughts of this and than I will close our discussion here but thank you for giving it your best effort anyway
Bo
Hi Bo,
It does appear that you do have a good amount of memory, and 2.0GHz CPU isn't too bad, but do bear in mind that a new computer - which will probably come with Vista - is likely to be at least dual core, and the buses and everything else are faster as well. But I wouldn't think that what you are trying to do would be a problem on your hardware.
In one sense you are about due for a new computer soon, but if you don't need it, why spend the money, and I don't get the sense that you need it from the SQL side of things.
Cheers
David
It does appear that you do have a good amount of memory, and 2.0GHz CPU isn't too bad, but do bear in mind that a new computer - which will probably come with Vista - is likely to be at least dual core, and the buses and everything else are faster as well. But I wouldn't think that what you are trying to do would be a problem on your hardware.
In one sense you are about due for a new computer soon, but if you don't need it, why spend the money, and I don't get the sense that you need it from the SQL side of things.
Cheers
David
ASKER
thanks again for all your help and your speedy responses I am giving you the full 500 points because I think anybody that is will to endure a week of troubleshooting even if the issue isn't fully resolved deserves a good "grade" if you will LOL any way take care and once again thanks a lot.
I have posted this same question in a different perspective on the iis, web application and sql server 2005 categories of this forum so we will see what I get for a response.
I worded the question more in the troubleshooting area of iis instead of sql server so we'll see if this performance issue can be fully cracked using that approach. Once again thanks and have a merry Christmas
Bo
I have posted this same question in a different perspective on the iis, web application and sql server 2005 categories of this forum so we will see what I get for a response.
I worded the question more in the troubleshooting area of iis instead of sql server so we'll see if this performance issue can be fully cracked using that approach. Once again thanks and have a merry Christmas
Bo
ASKER
very helpful, speedy at brain storming solutions, concerned about the problem at hand.
Have you turned the autoclose property off?
How long does the CPU usage stay at 95% +?
Cheers
David