Slow running query on my machine only

Question

I'm running a simple select * from table query against Microsoft SQL Server 2005. The table has about 41000 records with a PK clustered index and I'm using windows integrated authentication.

• When I run this on my machine using Microsoft SQL Server Management Studio the query completes in about 1h45m. I have tried swithing from grid to text output, running it from command line using SQLCMD also tried ececuting DBCC USEROPTIONS, DBCC DROPCLEANBUFFERS, DBCC FREEPROCCACHE prior the query... nothing changed the speed of the query.

• When I run it from colleague's workstation using my credentials the query completes normally in no time. If a colleague runs the query on my workstation using their credentials the query is very slow as described above.

I'm running out of ideas how to troubleshoot this problem. Somehow the problem seem to be linked to my machine, but I can not make sense out of it. Please also consider the following:

• I checked and made sure ODBC tracing is not enabled on my machine (Win7-64bit). I have installed SQL Server client tools with Microsoft Management Studio 2008 R2 and this is what I'm using to query the database.
• I also used VS2010 to run the same sql query and the result is identical.
• I run the query against copy of the same table/database but on a different server and the issue persists.
• When I run select count(*) from table - the query completes normaly with no delay.
• When I run a trace with SQL Server Profiler the query is just a long running query - I can not see anything abnormal.
• When I run the query in MS SQL Server Management Studio using text out put I can see after the initial buffer of about 85 records the subsequent records arrive in bulk of about 400 records at a time. For example 85 -> 407 -> 804 -> 1210 -> 1605 -> 1897 -> 2161 -> 2753 -> 3051 -> 3342 -> 3676 -> 4002 etc. In comparison of the coleagues workstation where you can see the records painted as continuous stream as the data arrives!???
• I also compared the actual Query Execution Plans on my machine and the other where the query is running normally - they appear to be identical.

Could anyone make sense what could be going wrong here? Why running this from my machine is very slow but it executes normally from another workstation?... Any suggested actions steps to troubleshoot this problem?

Thanks to everyone in advance!

Answer 1

I found the problem - it was a network adapter driver update. Once I rolled back the driver the query executed as expected no delays!

It took me quite a while to nail this down. For the record my network card is "Intel(R) 82579LM Gigabit Network Connection" the rogue network driver came through windows update. My workstation is HP 8200 Elite. After the driver roll back, I downloaded installed the latest network driver from the HP support site and it seems this is the solution - so far I do not have issues with slow running SQL queries :)

Answer 2

Are you hardwired to the LAN? Wifi? Check your network latency, everything you've provided seems normal. Ping your database server to check this.

The next thing I would check is to make sure that nothing else is running on the server or database at that time. Antivirus software, testing, anything that could slow down the DB. Also, are you running this query via virtual box? Grab an execution plan both from the local server and the remote box and compare them side-by-side. They should be the same. If they aren't, you know there's an issue.

Also check the user SET options under which each connection executed their query. That can have an effect. If none of these turn out be a problem, my next guess would be a hardware or configuration failure