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 - 31 through 45 (of 66 total)

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

isuckatsql SSChampion Points: 10381 More actions · Answer 1

Roy Ernest (2/17/2010)
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?

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

Where would i check any Wait for resources ?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

What are the indexes on those two tables?

Looking at the execution plans once again, I noticed that the plan from the server does not contain an output list, nor is it fully qualified like your desktop plan.

Desktop you have

select * from go4.dbo.zip

From the server you have

select * from zip

It shouldn't make a lot of difference in execution time, but it is a difference that we should eliminate.

Please attach table create scripts and index create scripts related to this table from both the desktop and the server.

Also, what happens in your query time if you replace the * with specific column names in both machines? Typically it should improve - but I am curious how it will relate to each other.

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 3

Desktop using names instead of * .... 18 seconds

Server using names instead of * ... 26 seconds

Server using Select * with fully qualified table name.... 25 seconds

isuckatsql SSChampion Points: 10381 More actions · Answer 4

isuckatsql

SSChampion

Points: 10381

February 17, 2010 at 6:38 pm

#1120599

Desktop script.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

When running the query on the server, do you experience any cpu usage spikes? Are there disk io spikes as well during query usage?

What is the size of your tempdb?

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

WayneS SSC Guru Points: 95460 More actions · Answer 6

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

Jason,

Kudos for knowing about how the offset can affect the performance of a query. Well done!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

Hmmm... wait a minute. One more question to ask... in relation to the "fast" desktop server, where is the "slow" SQL Server? Here's what I mean by that question...

In a previous job, I developed a PoP (Proof-of-Principle) system on my desktop server. As expected, it produced result sets nasty fast. I moved it to the production system and it suddenly went from almost instantaneous returns to taking more than 19 seconds. When I RDC'd into the production server itself, almost instantaneous returns came back to be.

What was the problem? My local desktop was in Ann Arbor, Michigan. The production server was in New York City. Because of some very necessary security requirements, there were routers, switches, firewalls, and all manner of time consuming things between my Michigan desktop and the NYC production server. When I RDC'd directly into the production server, all of that stuff went away and I returned to nearly instant results.

It's import to also know that the amount of CPU time never changed no matter how I was logged in. The 19 seconds was all display delay time because it took that long to get through all the security on our WAN. Even if the production server is in your basement, there may be that much of a delay just to make it through a firewall system if you're on the other side of the firewall.

Similar problems could happen if the "file" you're trying to save to is anywhere but on the server (or its SAN) itself.

--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)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

Sorry... I have one more question... why are you trying to return 900k rows to the display, anyway? It's not like you're going to be able to read that much this week. 😉

--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)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Jeff Moden (2/17/2010)
Hmmm... wait a minute. One more question to ask... in relation to the "fast" desktop server, where is the "slow" SQL Server? Here's what I mean by that question...
In a previous job, I developed a PoP (Proof-of-Principle) system on my desktop server. As expected, it produced result sets nasty fast. I moved it to the production system and it suddenly went from almost instantaneous returns to taking more than 19 seconds. When I RDC'd into the production server itself, almost instantaneous returns came back to be.
What was the problem? My local desktop was in Ann Arbor, Michigan. The production server was in New York City. Because of some very necessary security requirements, there were routers, switches, firewalls, and all manner of time consuming things between my Michigan desktop and the NYC production server. When I RDC'd directly into the production server, all of that stuff went away and I returned to nearly instant results.
...

I believe he is running the queries directly on the server as well. Based on his statement.

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

Now if it is not being done that way, I hope he would inform us to that fact.

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

I have the same desktops in two locations, and the results are the same on both desktops.

The server tests are being done via a RDT session onto the server as admin.

Local, as in same building, 50 feet away, and remote locations.

All server testing provides the same results.

After recently installing SQL Server 2008 Express on my desktop, i noticed the faster desktop performance on the sql queries.

That was the basis for further examination.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

Thanks for the update I am still looking at things on this side.

I am still inclined to think it has something to do with hardware setup.

How is your tempdb setup? How many files? How large are the files (data and log) for tempdb?

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 18, 2010 at 11:53 am

#1121222

Eight(8) TempDB files - Each file is 2 GB.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Those are all data files, right? What is the size of the log file?

And I don't recall, did you have CPU spikes or DISK IO spikes while running this query on the server?

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 14

Data File - 500MB

Log File - 1 GB

Those are TempDB files...one of my other DB's is 60 GB.

"And I don't recall, did you have CPU spikes or DISK IO spikes while running this query on the server?"

How do i test for this?

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

You can use performance monitor to look for CPU spike and disk IO spikes.

Just for information, can you also check what is the WAIT Type that is happening when you run the query on the server? You could look at master.dbo.sysprocesses to get the Waittypes and lastwaittype details. It might not be important but we could rule out quite a few things

-Roy