SQL Server 2008 running 50% faster on desktop than server - what to look for?

Question

SQL Server 2008 running 50% faster on desktop than server - what to look for?

isuckatsql

SSChampion

Points: 10381
More actions
February 16, 2010 at 8:54 am

#210885

I have a Table with approx 900k records and recently tested it on SQL Server 2008 express edition x64 on my Dell Quad core PC with 8GB RAM and a single SATA drive and Windows 7 x64.
Select * from tablename - it took 18 seconds to run the query.
I ran the same query on my Dell server, running Windows Server 2008 x64, SQL Server Developers edition x64, 24GB RAM, and
12 SAS 15k drives split into 4 seperate RAIDS(data, log, tempdb, program files), dual QUAD core processors.
On the server the same query took 27 seconds to run!
Any ideas on what to look for to determine why it is running 50% slower on the server with MUCH faster equipment?
Thanks

Viewing 15 posts - 1 through 15 (of 66 total)

You must be logged in to reply to this topic. Login to reply

NicHopper SSCrazy Eights Points: 9090 More actions · Answer 1

Assuming it's addressing all the resources correctly, is there any difference in the plans? I.e perhaps an index is missing from the server?

Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

Lowell SSC Guru Points: 323518 More actions · Answer 2

i'd suggest updating statistics right away on the server...that can often slow down execution plans.

another thing to consider is the time it takes to assemble the information and send it over the network: 900k records will take some time to transmit over the wire from the server, where that does not have to happen when you run the select locally.

now from the SERVER, if you do the reverse...open SSMS on the server desktop, and connect to the both itself and your dev instance, and run the same select....do you see that it takes the more time against the dev due to the network portion adding some transmit time , compared to the SERVER running the query against itself?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mike John SSCertifiable Points: 7216 More actions · Answer 3

I think you will find this is simply down to the speed of moving the data from the server to Management Studio. If running locally you have probably connected using shared memory rather than TCP/IP so the server process can throw the data back at the client as fast as possible. When connected to the server you have all the network latency plus TCP/IP overhead etc etc . With the query as you have it - all columns and all rows then no index would have helped anyway.

Hope that Helps

Mike

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 4

rdp to the server and repeat the query.

You might also want to make sure you're starting on a level playing field by stopping and starting the sql service on both before running the query.

You can also set execution stats on the query window to see what's happening. That's quite a network overhead though.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 5

I'm calling MAXDOP!!!!

you probably have more schedulers on your lappy than you do on the server so it can parallelize and split the select * amongst parallel threads better.

just a guess.

Craig Outcalt

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

I am curious to know if there was any query improvement on the subsequent runs of the query. Are these times the first run of the query?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

isuckatsql SSChampion Points: 10381 More actions · Answer 7

Both tests were done directly on the desktop and on the server, not across the network.

The times were provided by SSMS.

Tables, indexes etc.. were exactly the same.

isuckatsql SSChampion Points: 10381 More actions · Answer 8

I rebooted the server and ran one test - 29 secs, then immediately ran a second test - 25 secs.

Still quite a bit slower than the desktop!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Anything else running on the server besides SQL?

Are the statistics up to date on the server?

Are the indexes in a reasonable state of defragmentation?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

isuckatsql SSChampion Points: 10381 More actions · Answer 10

CirquedeSQLeil (2/16/2010)
Anything else running on the server besides SQL?
Nope, just SQL Server 2008.
Are the statistics up to date on the server?
I dont know how to check this? I have never done an update.
Are the indexes in a reasonable state of defragmentation?

I loaded both DB's from a backup yesterday to both versions of SQL Server on both machines, so the status would be the same on both machines, but i can do a reorg/rebuild if you think it would change things?

Thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

I would give that a shot.

Also, out of curiosity. I know the disk subsystem is better on the server. Is it a san or locally attached disks? I am wondering about the disk alignment for your server disks.

It would also be helpful to attach the actual execution plan for the query from both machines. That may or may not divulge anything more about this issue.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

isuckatsql SSChampion Points: 10381 More actions · Answer 12

I did a reorg and reindex on the server, but the query still took 25 secs.

Please find attached the Actual Execution Plan from the server query.

I cant provide the desktop one until tomorrow.

Thanks

isuckatsql SSChampion Points: 10381 More actions · Answer 13

CirquedeSQLeil (2/16/2010)

I would give that a shot.

Also, out of curiosity. I know the disk subsystem is better on the server. Is it a san or locally attached disks? I am wondering about the disk alignment for your server disks.

C - Program Files- RAID-1 - Server - 2xSAS

F - Data Files - RAID-10 - JBOD - 6xSAS

G - Log Files - RAID-1 - JBOD - 2xSAS

H - TempDB - RAID-0 - JBOD - 2xSAS

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

Here is a reference about disk alignment. SQL server likes the disks aligned and the cluster sizes to be larger than the default settings.

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

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events