March 5, 2010 at 11:27 am
I have a SP that runs a query in SSMS and takes on average 1 - 2 seconds to access 59k records.
When i access the web site that calls the same SP, it takes on average 9 seconds, to get the same information!
The web site performance does not improve if i am onsite or offsite.
I do not have this issue with other web sites connected to the same server!
What should i check for?
Thanks
March 5, 2010 at 11:57 am
What OS's are used on the DB Server and Web Server?
How many hops is it taking to go from the Web Server to SQL Server?
What protocol are you using?
Are you using WINS or DNS on the segment?
You can also enter the IP/Port in the connection string...
You can run profiler to identify what the actual execution time of the query is so that you can identify if there is a network delay?
Regards,
WC
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 5, 2010 at 12:02 pm
And...you are trying to returns 59k records to the web server?
March 5, 2010 at 12:08 pm
What OS's are used on the DB Server and Web Server?
The same server is running SQL Server 2008 DB and the Web application using IIS. Windows Server 2008 o/s. Is this a bad idea?
How many hops is it taking to go from the Web Server to SQL Server? Same server.
What protocol are you using? Not sure what you mean?
Are you using WINS or DNS on the segment? Not sure what you mean?
I also just did the same test on my desktop running SQL Server 2008 express.
SSMS SP execute - 2 seconds for 59k records
Running via a localhost web site - 6.5 seconds, which is waaay better than the 9.5 seconds for the server!
Thanks.
March 5, 2010 at 12:11 pm
And...you are trying to returns 59k records to the web server?
I dont have a dedicated web server but i am returning 59k records to the web site, but one page at a time, 10 records per page.
March 5, 2010 at 12:22 pm
Is this an internal or external Web Application?
It is recommended to have separate DB & Web Servers.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 5, 2010 at 12:44 pm
External application.
I know that SQL Server and Exchange need to be on different servers, but did not know that one needed to have a dedicated web server as well:(
Do you think that is what is casuing my latency?
Thanks
March 5, 2010 at 12:48 pm
It could be any number of things.
You need to take a look at the way memory and cpu are being handled, whether it's the page draw that's causing the issues, if you have IIS issues etc.
You'll have to crank up perfmon and get some counters going, find your bottleneck.
March 5, 2010 at 12:55 pm
Having SQL Server and your Web Applications is a big security risk...
Is this the production environment?
Why are you returning 59K records?
Are you calling the Web Application from your local PC? If so what happens when you call it from the Server?
Have you checked the performance on the server? System monitor, etc?
Can you get the same results in your development and or QA environment?
Do you have SQL Server and IIS on your local machine?
How about trying a scaled down version of your application, i.e. a simple C# or VB.NET ASP application that calls a SP with a very small number of records. Just a thought... 🙂
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 5, 2010 at 1:00 pm
Having SQL Server and your Web Applications is a big security risk...
.....Learning as i go, its not live yet so i will buy a web server.
Is this the production environment? Not yet.
Are you calling the Web Application from your local PC? If so what happens when you call it from the Server?
The performance does not seem any different running the web site from the server or within the network.
Have you checked the performance on the server? System monitor, etc? Rookie area for me!
Can you get the same results in your development and or QA environment? I only have one environment at the moment.
Do you have SQL Server and IIS on your local machine? yes.
How about trying a scaled down version of your application, i.e. a simple C# or VB.NET ASP application that calls a SP with a very small number of records. Just a thought...
I will give this a shot.
Thanks
March 5, 2010 at 1:01 pm
Nicholas Cain (3/5/2010)
It could be any number of things.You need to take a look at the way memory and cpu are being handled, whether it's the page draw that's causing the issues, if you have IIS issues etc.
You'll have to crank up perfmon and get some counters going, find your bottleneck.
Thanks!
March 5, 2010 at 1:10 pm
Please look into perfmon as was suggested to you.
That is a biggie.
You can Goggle of you have one of the DBA Books they all have sections on performance.
You are doing the right thing by purchasing a new machine for you Web Server.
It is also very important to have separate Development, QA & Production Environments for many reasons.
I have walked into so many shops and they do not have separate, DEV, QA & PROD Environments. :w00t:
Good luck!
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 5, 2010 at 1:14 pm
i am aware that you need seperate development and production environment. I just dont have this project finshed yet, which is why it is not production ready:)
It is my first foray into the web development world!
March 5, 2010 at 1:20 pm
No worries.
I was not being critical.
Have a nice day!
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 5, 2010 at 1:23 pm
Nicholas Cain (3/5/2010)
--------------------------------------------------------------------------------
It could be any number of things.
You need to take a look at the way memory and cpu are being handled, whether it's the page draw that's causing the issues, if you have IIS issues etc.
You'll have to crank up perfmon and get some counters going, find your bottleneck.
This is the best advice that you have been provided.
Good luck...
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply