June 17, 2010 at 8:27 am
Two speed issues I don't understand and would love some insight:
Environment:
SQL Server 2008 R1
100 NIC cards
SQL Server - Windows Server 2003
Other machine - Widows Server 2008 data center
Scenario:
1) Run 'select * from customer' (25K record count) on SQL server and on other machine - both from mgmt studio. SQL server 5 seconds - other machine 8 seconds.
2) Run 'select customer_num, name from customer' - SQL server - > 1 second - other machine - 3 seconds.
Questions:
Why in #1 is other machine slower?
Why in #2 is a field list faster than *?
Is this always true that a field list is faster than *?
Should I list all fields instead of star to get it faster?
Thanks,
Mike
June 17, 2010 at 8:36 am
It depends on a number of things,
-your statistics could be different on the machines,
- there could be other things running.
- you may not have been clearing the proc cache between tests so the results could be off.
the best way to work out where the difference is would be to get the actual exexution plan from the query run on both servers and compare them.
You could also try and write the query so it is not returning the rows to SSMS as this adds an overheard and after all 25k rows displayed in SSMS is not really that useful
Aslo the best advice is to never use select * and always select the specific columns
June 17, 2010 at 8:36 am
A single column will always be faster than multiple columns. It's quantity of data down a fixed pipe.
June 17, 2010 at 8:40 am
steveb. (6/17/2010)
It depends on a number of things,-your statistics could be different on the machines,
- there could be other things running.
- you may not have been clearing the proc cache between tests so the results could be off.
the best way to work out where the difference is would be to get the actual exexution plan from the query run on both servers and compare them.
You could also try and write the query so it is not returning the rows to SSMS as this adds an overheard and after all 25k rows displayed in SSMS is not really that useful
Aslo the best advice is to never use select * and always select the specific columns
What do the "statistics" do? How do they help? I ran a # of SQL statements through the Database Tuning Advisor and I wanted me to create about 20 statistics.
Thanks.
June 17, 2010 at 8:47 am
http://msdn.microsoft.com/en-us/library/ms190397.aspx
June 17, 2010 at 8:58 am
Have a look at the link posted above, statistics are critical for the query optimizer to work effectively.
Also i would take any advice from the Database Tuning Advisor with a very large grain of salt. You are usually better off doing your own performance tuning and testing.
June 18, 2010 at 7:35 am
First off, you didn't state whether or not the hardware was different. Unless the memory, cpu and disks are identical, you'll see fundamental differences in behavior between machines.
Second, index fragementation, the existence of statistics and the age of statistics can all affect query performance. Again, it wasn't clear if the indexes on both machines were defragmented the same way at the same time with the same sets of data. What other queries have been run against the machines becuase this may have created other statistics to make the machines different. Do you have statistics maintenance in place because the stats could age differently between machines.
Statistics are the fundamental building blocks for query execution plans. They determine what operations the engine will use to retrieve your data. They really do matter.
The Tuning Advisor is notorious for giving really lousy advice. Be very, very judicious in performing actions that it proposes. Test them thoroughly before applying them in a production environment.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 18, 2010 at 10:16 am
It is only one SQL server. The time difference is between running the query locally and running the query on a machine attached via the network.
Currently, the network is 100BaseT. We are going to upgrade it to Gigabit and see if that helps.
Mike
June 18, 2010 at 10:55 am
mike 57299 (6/18/2010)Currently, the network is 100BaseT. We are going to upgrade it to Gigabit and see if that helps.
No matter how fast the network is it will always add overhead - remote connection will always be perceived as slower than a query executed on the host.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 18, 2010 at 12:01 pm
Ah, different information. I would check the ANSI settings first, see if they're the same.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply