Queries that run in 1 second take minutes on another server?

[mat]

I have a sql server db which runs on two different boxes, both with sql
server 2005 sp3. Some complex queries take minutes to complete on one
box and the other box completes them in a second. The backends are
identical copies and the queries are called from idential MS Access 2003
front ends. Access is local to the sql server boxes so no network
latency is involved.

The slow box is a virtual pc, os windows 2003 sp3, 2 gb ram.

The fast box is windows xp.

Both use the latest sql native client odbc driver from ms.

What kind of settings or factors might be responsible for the time
difference?

 

[Geoff Schaller]

Haven't you answered your own question?

The slow box is the virtual box. Why does that surprise you?

 

[Aaron Bertrand [SQL Server MVP]]

Did you look at the execution plans on both servers?

 

[mat]

Did you look at the execution plans on both servers?

No...can I look at an execution plan if the query is based in Access?
I've never tried that, I'll see if it's possible. I will do an odbc
trace also.

 

[Aaron Bertrand [SQL Server MVP]]

No...can I look at an execution plan if the query is based in Access?

Of course, you can take the query from Access and run it manually in
Management Studio. If it is not easy to get at the underlying SQL then you
could use profiler to capture the actual query.

 

[mat]

Of course, you can take the query from Access and run it manually in
Management Studio. If it is not easy to get at the underlying SQL then you
could use profiler to capture the actual query.

Thanks Aaron I'll try the profiler.

 

[Manpreet Singh]

Update statistics and check for index fragmentation on both the server. Ones
both server seems identical in all respect then rerun the tests. If problem
still persist try using perfmon while executing queries on virtual machine

http://msdn.microsoft.com/en-us/library/ms186819.aspx

Manpreet Singh
http://crazysql.co.nr
MCITP – DB Admin, DB Dev SQL Server 2005
MCTS – MOSS 2007, SQL server 2005