October 3, 2012 at 11:20 am
I'd be interested to see if anyone had any comments on this.
Desktop - Win 7, SQL 2008 R2 Ent, 8 core 2.8Ghz, 10GB RAM, SSD for C: and tempdb, D: motherboard hardware RAID1 for the database.
Server - Win 8 Server, SQL 2008 R2 Ent, 12 core 3.0Ghz 32GB RAM, RAMdisk for Tempdb, software RAID 1 for the DB (have tried just unmirrored disk too). Was lots slower until I staged my tempdb onto RAM disk, that helped a lot !
My test DB and SP queries are identical on both platforms, and both servers work best for me with unlimited parallelism of CPUs. Most CPU is used in total during the queries. Have fiddled with about everything I can. And I've been through blogs re: perfmon, but I don't seem to have any obvious issues there with regard to the range of results I see.
This is so disappointing. I was looking for a hardware-based magic bullet !
Greg.
October 3, 2012 at 11:53 am
I don't know how many sprocs you are running during your test but the first thing I would do is look at the execution plans of the sprocs. The query optimizer might be coming up with drastically different plans for the same sproc on the two boxes. Given that you say performance improved when you moved tempdb to a faster disk system it may be that the execution plan of some of the sprocs is inefficient on the server and result in a higher use of tempdb due to work tables, sorts etc.
If you run a profile of both machines while you run your workload that should tell you pretty quickly if one or more sprocs are running slowly. Then drill compare the execution plans for the sprocs between the two system. The query optimizer will be influenced by hardware - especially the number of cpus.
October 3, 2012 at 12:04 pm
Along those lines, make sure the statistics are up to date on the tables on the server as well.
Also, a quick checklist of a few things to look at on the server:
disk controller "issues" (some settings can cause significant delays; naturally any genuine error in a controller will cause serious delays)
table fragmentation
verify that ifi is turned on
tempdb log size (make sure you pre-allocate enough log space)
# of tempdb data files (you can start with 4 and go from there)
maxdop global setting (I woud set it to something other than 0)
disk file fragementation
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 3, 2012 at 12:25 pm
Also - the desktop's hardware isn't all that different from the server, so I'd expect the performance to not be that far off. Frankly the hardware raid is probably beating the pants out of the software raid, so the gains from extra RAM are lost in slower disk access.
I'd be curious what kind of test you might be running - unless you're running a bunch of concurrent threads, I would be surprised to see any major perf difference.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 3, 2012 at 12:27 pm
greg.bull (10/3/2012)
I'd be interested to see if anyone had any comments on this.{snip}
This is so disappointing. I was looking for a hardware-based magic bullet !
Greg.
I've been through similar a couple of times now. The best that happened was about a 3 month improvement in performance and then right back into the toilet when the new "tipping point" was reached. Performance challenged code will be performance challenged code even on a new system.
Also consider the following. Some performance challenged code can be changed to literally run hundreds and even thousands of times faster. What kind of hardware would you have to buy to get that kind of performance improvement?
Sure, fire-breathing servers will help performance some but real performance gains are usually found in the code and such code changes usually weather hardwire migrations much better.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply