Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

We are trying to get a new Web Server up and running but are getting horrible performance when attempting to query our SQL server.

New Web Server is a HyperV virtual machine running Server 2008 R2 64 bit, IIS 7.5 with 3GB memory.

Our SQL Server is dedicated Server 2003 64 bit machine with 4 physical processors and 16GB RAM running SQL 2005.

Both the new web server and the existing production web server are in their own separate DMZs

Our existing production web server and all development clients can access the SQL server without any problems, but a query that runs in under a second on those machines takes 5 minutes to run on the new test server.

We've verified that it has the correct SQL client protocols and opened up the Firewall between the web server and the SQL server, but queries still take forever. Not sure what to even look for next. Figure if it was a firewall problem, that queries wouldn't run at all.

Any suggestions on what to look for next?

share|improve this question
    
What sort of web app -- and how does it connect to the DB? –  Chris_K Jul 6 '10 at 21:49

3 Answers 3

up vote 2 down vote accepted

This ended up being related to the Windows Server 2008's TCP Window Scaling "feature." As soon as we disabled this service, the Queries (along with all other network traffic) is running much faster.

http://www.intel.com/support/motherboards/server/sb/CS-030717.htm

share|improve this answer

A bit more information would be helpful:

Your queries take x seconds and they are expected take y based upon an old environment, etc. Assume your queries are using search arguements, proper indexes are in place, index statistics are current, etc.

Can you post a sample query?

share|improve this answer
    
Very simple query... select * from core_person where last_name = 'turner' This query takes less than a second from our existing web server accessing same sql server/database, so pretty sure it has nothing to do with things like indexes, statistics, etc. Did the query with Client Statistics turned on for both machines and the difference was the "Client processing time," "Total execution time," and "Wait time on server replies." So, pretty sure it's some sort of network bottleneck, but no idea what since we've opened the firewall and verified SQL client protocols. –  VORTracker Jul 6 '10 at 21:51
    
Statistics for existing web server: Client processing time - 59 (miliseconds) Total execution time - 60 Wait time on server replies - 1 Statistics for new web server: Client processing time - 276893 (miliseconds) Total execution time - 276712 Wait time on server replies - 722 –  VORTracker Jul 6 '10 at 21:54
select * from core_person where last_name = 'turner' 

Does the web app by any chance runs:

select * from core_person where last_name = @last_name;

and passes in a @last_name variable of type nvarchar? This is a very common blunder with ADO.Net and can slip in unnoticed with a simple sqlCommand.Parameteres.AddWithValue("@last_name", someStringVariable);. The results are disastrous as the Data Type Precedence rules clearly state that the comparuiison must occur between NVARCHAR types so the query is equivalent to select * from core_person where cast(last_name as NVARCHAR(...)) = @last_name; and this ignores any existing index on last_name and forces a table scan. Running the similar query from the SSMS window runs absolutely fine, as the conversion is not forced in that case.

This is just a shot in the dark, and is all one can provide given the complete lack of investigation on your part. I recommend you follow a well established performance troubleshooting methodology, like Waits and Queues.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.