June 2, 2017 at 10:17 am
How does hardware/network choices/issues affect SQL performance and configuration?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 2, 2017 at 10:35 am
Absolutely directly.
Slower and fewer CPUs mean less processing power. Less memory means more disk I/O to process data. Slower and fewer disks means slower disk reads and writes. A narrower bandwidth on the network slows down data access. Higher network latency means longer query times. Obviously all this immediately impacts the configuration of the server too.
What are you looking for here? That's a very vague and open-ended question.
"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 3, 2017 at 10:24 am
By the same token, you can have a fire breathing monster of a server on a network with speeds that we could only dream of and still run into performance issues. Good code can frequently make up for slower hardware. Bad code can bring the best of hardware to its knees. Even MPP (Massively Parallel Processing) system manufacturers only advertise a 30X improvement and that's only after you've rewritten your code to take advantage of the MPP technology. Fixing bad code can easily result in 30-60X improvements with no hardware changes and frequently 100X and 1000X are sometimes fairly easy to accomplish..
The hardware upgrade we did last year, which doubled the number of CPUs from 16 to 32 (1.8Ghz to 2.4Ghz to boot), an increase of memory from 128GB to 256GB, and replacing physical platters with SSDs only improved performance by 2X and only on some of the code. Much of the code had no change in performance. Compare that to a fix I made for a 5 year report that was taking 45 minutes to run and, because it would cripple (8 CPUs slammed to the wall for the duration) all else running on the server, they would only run the report once per year and they had to standup a clone of the server to do it. It took me 2 hours to understand the underlying data (there were 40 tables in the join), 2 minutes to write the change for the code, and the resulting code ran so fast (8 seconds... that's 337 times faster), they thought it didn't actually run. Using Perfmon to monitor CPU usage, you couldn't actually tell when the report code started and ended. No hardware changes were made in the process and I didn't even have to add an index.
You can do even better for front end related database code. There was a particular function being used that took 3.6 seconds to execute and it was being called 40,000 times an 8 hour day day. In other words, it was consuming 40 CPU hours per day. After hammering on it for a little over an hour, I got it down to 3 MICRO seconds. That's a 1.2 MILLION X improvement. In other words, the 40,000 executions dropped from 40 Hours of CPU per 8 hours to 0.120 seconds total.
There's no question that it's good to have good hardware but, if you want real performance, remember that "Performance is in the code".
--Jeff Moden
Change is inevitable... Change for the better is not.
June 3, 2017 at 3:06 pm
Grant addressed hardware and Jeff addressed software, so I won't go into either one except to say that I have experience with what they've both said. Hardware and efficiency of the code are of great importance.
So is the other stuff you have running on the machine. Try to keep SQL Server pretty-much alone. Don't have, for example, a web server running on the same hardware. In short, you want to let SQL Server have as much of the hardware as possible because it'll use it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply