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?

Viewing 15 posts - 16 through 30 (of 66 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

I'm thinking that it will all boil down to the video card which is likely quite a bit cheaper on the server than on anything else. It takes time to display 900k rows on the screen.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 2

CirquedeSQLeil (2/16/2010)
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

good call on disk alignment.

I notice the maxdop on your server's plan was 1, so 1 thread read all the rows from row 1-n million.

Do you have the plan for your laptop posted or did I miss it?

my gut still says maxdop is the culprit.

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

isuckatsql SSChampion Points: 10381 More actions · Answer 3

Please find attached the Actual Execution Plan from my home desktop.

Thanks

SQLRNNR SSC Guru Points: 281324 More actions · Answer 4

Jeff Moden (2/16/2010)
I'm thinking that it will all boil down to the video card which is likely quite a bit cheaper on the server than on anything else. It takes time to display 900k rows on the screen.

It's worth a shot. There could be any number of factors hardware related that could cause this. Low quality ram being another.

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

Lowell SSC Guru Points: 323482 More actions · Answer 5

for the ziptestserver plan, the QueryPlan DegreeOfParallelism="1", but for the homecexecution plan, it's QueryPlan DegreeOfParallelism="0";

it looks like the rest of the plan is exactly the same though;

Jeff mentioned it might be the video card; if we send the results to file form SSMS, wouldn't that take the display out of the equation?

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!

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 6

DegreeOfParallelism="0" means unlimited parallelism (up to number of procs)

I do also note that parallelism = "false" in the xml of both. Not 100% sure if it means what we think.

Try running the same query on both environments with the maxdop query hint.

select * from [dbo].[zip] Option (MAXDOP 1)

If the execution time isn't similar, then I'm totally barking up the wrong tree... no offense to Lowell's Avatar.

:hehe:

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: 281324 More actions · Answer 7

After the MaxDOP suggestion, try barking up Lowell's suggestion to output to file.

Both are good suggestions.

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 8

SQLBOT (2/17/2010)
DegreeOfParallelism="0" means unlimited parallelism (up to number of procs)
I do also note that parallelism = "false" in the xml of both. Not 100% sure if it means what we think.
Try running the same query on both environments with the maxdop query hint.
select * from [dbo].[zip] Option (MAXDOP 1)
If the execution time isn't similar, then I'm totally barking up the wrong tree... no offense to Lowell's Avatar.
:hehe:
I ran the server query to screen with MAXDOP set to 0, which is the same as the desktop, and the time to run the query was the same as when MAXDOP was set to 1 on the server.

SQLRNNR SSC Guru Points: 281324 More actions · Answer 9

isuckatsql (2/17/2010)
SQLBOT (2/17/2010)
DegreeOfParallelism="0" means unlimited parallelism (up to number of procs)
I do also note that parallelism = "false" in the xml of both. Not 100% sure if it means what we think.
Try running the same query on both environments with the maxdop query hint.
select * from [dbo].[zip] Option (MAXDOP 1)
If the execution time isn't similar, then I'm totally barking up the wrong tree... no offense to Lowell's Avatar.
:hehe:
I ran the server query to screen with MAXDOP set to 0, which is the same as the desktop, and the time to run the query was the same as when MAXDOP was set to 1 on the server.

That raises a few questions.

1. How many procs are allocated to SQL server in the properties?

2. Did you run the query only once, or multiple times?

3. And with the maxdop change did you clear the cache prior to executing 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 10

CirquedeSQLeil (2/17/2010)
isuckatsql (2/17/2010)
SQLBOT (2/17/2010)
DegreeOfParallelism="0" means unlimited parallelism (up to number of procs)
I do also note that parallelism = "false" in the xml of both. Not 100% sure if it means what we think.
Try running the same query on both environments with the maxdop query hint.
select * from [dbo].[zip] Option (MAXDOP 1)
If the execution time isn't similar, then I'm totally barking up the wrong tree... no offense to Lowell's Avatar.
:hehe:
I ran the server query to screen with MAXDOP set to 0, which is the same as the desktop, and the time to run the query was the same as when MAXDOP was set to 1 on the server.
That raises a few questions.
1. How many procs are allocated to SQL server in the properties? - Eight
2. Did you run the query only once, or multiple times? - Multiple times.
3. And with the maxdop change did you clear the cache prior to executing the query?

I used this query to make the change as the one suggested above did not change the MAXDOP.

How do i clear the cache?

sp_configure 'show advanced options', 1;

GO

RECONFIGURE WITH OVERRIDE;

GO

sp_configure 'max degree of parallelism', 0;

GO

RECONFIGURE WITH OVERRIDE;

GO

SQLRNNR SSC Guru Points: 281324 More actions · Answer 11

The query provided used a query hint that forces max degree of parallelism for that query only.

Through SSMS, right click the server, select properties from the context menu. Click on Processors on the left of the new window. How many processors show in the screen on the right now?

As for clearing cache:

DBCC FreeProcCache is one method.

Since it appears this is a dev server - you should be ok to perform that command. If it is a prod server, I would be careful about when you run that command since it clears the cache.

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

isuckatsql

SSChampion

Points: 10381

February 17, 2010 at 12:03 pm

#1120333

Eight processors

SQLRNNR SSC Guru Points: 281324 More actions · Answer 13

isuckatsql (2/17/2010)
Eight processors

Go ahead and try the query with output to file.

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 14

Just couple of stupid questions. I already know the answer but just for the sake of it..

1. Do you see any Wait for resources while running on the server?

2. What is the RAID configuration you are using?

-Roy

isuckatsql SSChampion Points: 10381 More actions · Answer 15

Server SSMS query to file - 35 seconds

Desktop SSMS query to file - 25 seconds