Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 SQL Server Slow Performance From ASP.NET Web App

Author  Topic 

bmcclurg64
Starting Member

6 Posts

Posted - 2014-08-28 : 17:07:52
Hello,

I am pulling my hair out over this issue. My company just had 3 servers setup as a Data Center. We have a dev server and a live server for our web application. We also have a sql server for them to connect to. I am getting very bad performance with new data center sql server from my web app. From one of the web servers, I connect the web app to a SQL Server 2012 database that we had already installed on a Windows 2008 R2 server in house. When I run my web app connecting to our internal SQL server, I get a quick response. When I run my web app on the other web server connecting to SQL Server 2012 Database on our new Data center server for Sql, it takes about 30-40 seconds to get a response. I even switched the web servers. Meaning, I switched the SQL connection strings in the web.config to go to the other sql server. The one server getting slow response to data center sql server now connects to our other server and now has quick response. And the other one that had quick response is now very slow. I should mention that these data center servers are within our domain.

Any clue as to what I should be looking at as to why very slow response to sql server housed on data center server? The Server OS and the SQL server are the exact same. I even ran QueryExpress.exe from data center web servers and got a very quick response. I should also mention that I am only return 548/54000 records. So it is not a very large dataset. I suspect it has to be something to do with how SQL server and IIS/ASP.NET connects. My connection string is identical connecting either sql server aside from the server name. Any thoughts as to what the problem could be?

Thanks in advance,

Brad

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-28 : 17:45:25
You need to determine where the slowness is, if there are bottlenecks, etc. It might be as simple as a bad execution plan that could be resolved by recompiling the stored procedure or freeing the procedure cache.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

bmcclurg64
Starting Member

6 Posts

Posted - 2014-08-29 : 08:08:50
I think I found what is causing the slow speed, however do not know why or how to fix it. It looks like it is the views I have created. When I run a view from our one sql server, it is very fast. However, when I run that same view from the other sql server it is extremely slow (~20-25 seconds) and says "Executing Query. Waiting for response from data source". Why would it be so slow on this other sql server. It would have the same data, because this sql server's tables are replicated from the other sql server. Therefore same amount of records being processed. Any thoughts???

Thanks,

Brad
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-29 : 12:27:46
Still sounds like a bad execution plan.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-08-30 : 02:04:30
Assuming the resources are equal on both sql server - ensure the databases are both maintained through statistics and indexes.


Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
   

- Advertisement -