| Author |
Topic |
|
rav3n0u5
Yak Posting Veteran
88 Posts |
 Posted - 2006-03-25 : 15:12:03
|
| My current SQL server has 24GB RAM, about 1 TB of fast local storage (2 raid 5 arrays though), and 2x 3.16Ghz Xeons.The procs have I think 2MB cache each. They might even have 1MB. I think that processor speed is the worst bottleneck we have on the server, and it is within my budget to get an upgrade. I think I have 3 options:1 - get 2 more of these procs for a total of 42 - dump the current 2, and buy two 8MB cache procs3 - convince my boss to up the budget a little, and get 2 dual-core procsOption 3 is a little unreasonable, but not entirely. I have no idea what to do... We have maxed out the chassis at this point. It is all copper, and the *ss who bought it, bought a tower rather than something rack-mountable. This isn't to say it hasn't served us well. Anyway, I don't know what kind of recommendation to make. I thought that increasing the parallelism would help considering the number of database users has increased, and will continue to do so. There are more queries running more often, and the server can't really handle more than just a handful of requests at once. But I was also told that large cache procs make a huge difference.Thoughts? Ideas? References? Help is much appreciated, as always. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-03-25 : 16:11:39
|
| How about looking at optimising the way the server is used?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
rav3n0u5
Yak Posting Veteran
88 Posts |
Posted - 2006-03-25 : 16:25:09
|
| I can't reduce the number of users, or the availability of the server to them. The people accessing the server need it realtime, and the reports that come out, need to be done in realtime as well. Unfortunately about 8 hours a day the server is running as hard as it can, and the other 16 hours it is almost sitting idle (obviously I have backup and system maintenance being performed at this point). There is nothing I can do to spread this out because it is the nature of the business.Unless, nr, you meant something entirely different? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-03-25 : 16:31:40
|
i think he meant you need to look at the queries that are being run and optimise them  if he didn't I do.Go with the flow & have fun! Else fight the flow  |
|
|
|
rav3n0u5
Yak Posting Veteran
88 Posts |
Posted - 2006-03-25 : 16:51:51
|
| Response:Unfortunately, much of the activity just involves pure inserts. If they aren't inserts, then they are very basic - SELECT OneField FROM someTable WHERE verySpecificCondition. There isn't much that can be done aside from removing indexes and statistics, that can speed up this process, to my limited knowledge.But I am being given the option to upgrade hardware, and my original question has not yet been addressed.Sob story:I understand fully that the server is not performing at 100% efficiency. I wish I was a seasoned SQL guru who had studied with someone who knew what they were doing. I know that minor query modifications can take tons of burden off the server. I have only been working with SQL for 6 months, and before that, I was trying to make it as a screenwriter!! Original question (rephrased of course):4 procs with small cache versus 2 procs with huge cache versus 2 procs with dual-cores and medium cache. Which would you buy, given that the chassis is old and a little hairy to work with? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-03-25 : 17:11:53
|
| insert needs a high disk IO time.raid 5 isn't good for heavy insert enviroment.read this:http://www.sql-server-performance.com/q&a116.aspi don't think CPU cache will help much here.screenwriter, huh?anything good? Go with the flow & have fun! Else fight the flow |
|
|
|
rav3n0u5
Yak Posting Veteran
88 Posts |
Posted - 2006-03-25 : 17:36:19
|
| If I had written something good I wouldn't be posting here asking for help because I am still a psuedo-dba :(The chassis has space for 12 harddrives. I would LOVE to throw everything into RAID 10, but I think our Head of IT would kill me. We would also lose the necessary storage capacity we are getting out of the two RAID 5 arrays. Each array is 546GB. I have log files on one, and data files on the other. I have seen the logfile get as large as 300 GB. Unfortunately the server also has a few other databases on it that have to be there. They take up the rest of the space...It has been my observation (using perfmon) that a query that takes advantage of the most useful indexes and has a very small Execution Plan (reads right from a non clustered index for instance), will use 50% of the available processing power. If another query similar to that is run in tandem, together, they still only use 50% of the processing power. If a query's execution plan starts to deviate - includes hash tables, numerous sorts, and nested loops - the query tends to put the processor usage at 80-95% of the max. For the most commonly run queries, I have thrown them into the Tuning Advisor, picked and poked indexes, and they are running pretty fast. But recently, as the number of users has increased and the overall activity level increased, I have noticed that instead of a "good" query running at 50% of the proc, it runs at about 60%. The line isn't very straight with predictable dips and peaks - it is kind of all over the place. I thought that this might indicate that the processors are being bogged down... no? |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-03-25 : 17:49:46
|
Questions like these are almost impossible to help someone with online. This is becuase yu have not given us the right information. Not only that, but once we tell you what to do, you will (most likely) come up with an excuse as to why you won't do that. :) Anyway, I am assuming that hyperthreading is turned on on this server. You should immediately disable it. Hyperthreading is not good with SQL Server.I will also tell you to get RAID 10 for all your disks. WIthout even knowing anything about what your are doing, the fact that you are using RAID 5 on an insert-heavy system is going to kill your performance. There is no question about this. Have your boss pony up for the fastest disks possible and throw the most expensive RAID controller you possibly can in front of it (preferably two controllers). Better, get a mini SAN like an HP MSA1000 or equivalent.This thread could go 4 pages deep with all the analysis, but then we will wind up with the same conclusion. So, get the disk. (and turn off the hyperthreading) and save us some time  .-ec |
|
|
|
rav3n0u5
Yak Posting Veteran
88 Posts |
Posted - 2006-03-25 : 18:45:13
|
| Hyperthreading has been off.The chassis cannot handle a SAN, DAS, or new RAID controllers (we happen to be using pretty good controllers though). Swapping over to RAID 10 would put us through more downtime than we can afford. We do not have a failover server, nor do we have a server we can use temporarily. I understand that RAID 10 would be great. I want it more than anyone else wants it for me. It is not an option.Perhaps my original question needs to be completely revised... In what conditions does SQL server historically perform better? Using 4 crappy processors, 2 large cache processors, or 2 dual-core processors?Eyechart, I love you. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-03-25 : 19:00:14
|
| i'd say 2 dual core.Go with the flow & have fun! Else fight the flow |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-03-25 : 23:01:26
|
| ok, now we get into the 4 page long thread...question, how do you know that the CPU is your bottleneck?-ec |
|
|
|
IanIppolito
Starting Member
1 Post |
Posted - 2006-04-18 : 23:32:03
|
| I got a 10% boost from hyperthreading...see my test results here: [url]http://rentacoder.com/CS/blogs/real_life_it/archive/2006/04/18/SQL_Server_2005_performance_testing_with_hyperthreading_and_MAX_DOP.aspx[/url]The bottom line is to test yourself and see what applies to you.Ian Ippolito |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-04-19 : 00:21:31
|
quote:
Originally posted by IanIppolito
I got a 10% boost from hyperthreading...see my test results here: [url]http://rentacoder.com/CS/blogs/real_life_it/archive/2006/04/18/SQL_Server_2005_performance_testing_with_hyperthreading_and_MAX_DOP.aspx[/url]The bottom line is to test yourself and see what applies to you.Ian Ippolito
Your tests are flawed. YOu did not set max degree of parallelism to the number of physical cores in your system. You should have set it to 8 instead of 4 for your final test. Please re-run your test and post your results.Also, how are you measuring performance? by percent of CPU used?-ec |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-04-19 : 10:32:21
|
| It sounds like you are more I/O bound than processor bound. I read that you had split your data and your logfiles out, but I would recommend that you also split out your indexes. That would enable the system to split the I/O for each and every insert. I can't recall exactly what the option is in performance monitor you could check, but I think it is Disk Queue. It will show you how many requests are queued up and waiting to be responded to. The higher the number (I think BOL indicates above 4 or something) the worse the I/O system is performing. If your RAID array is currently using 10K RPM drives, perhaps you could upgrade them to 15K drives one at a time after hours. If the system is doing very heavy transaction load you could consider having a second server that is used just for reports that don't need the access to real time data if that is an option. In our environment there are "real time" data reports, but most are fine with 24 hour old data. Off loading those (about 75% of the reports in our case) reports freed up our system I/O and CPU load quite a bit. I wish I knew more about CPU's for the performance as that seems to be what you are looking but unfortunately I don't.Hope it helps,Dalton |
|
|
|
rav3n0u5
Yak Posting Veteran
88 Posts |
Posted - 2006-04-19 : 13:21:38
|
| Thanks Dalton.Actually, just two days ago (this thread is a little old!) I created an "Indexes" filegroup and moved a good number of the larger indexes off to that group. Unfortunately I don't have a useful third physical partition, so I arbitrarily dumped this group onto the same partition as the logs, and changed my backup structure to bulk-logged instead of full. I don't like this solution long-term, but it seems to doing MUCH better.As for reporting, the reports generated need to be real-time at least to the 5th minute. I considered a second reporting server - and then the one blade I was using for that ran out of HD space (at 76GB, that was easy). It was a crappy SQL server anyway because it only accepts 2 mirrored HDs. I ended up going with 2 additional crappy procs for an extra 2k. Dual core and large cache procs would have set me back 10-16k (possibly much more) if I had 4 of them installed. THe server is not rack-mountable, and it is using internal storage. We really need to move to a SAN (other storage solution? a few DASs?), but I dont think we are willing to spend more than 40k. Bleh.As for hyperthreading, I will experiment some. I have to agree with eyechart that it seems the tests conducted by Ian seem a little vague. They aren't invalid, and maybe sql2k5 takes advantage of hyperthreading in some new way. However the evidence is far from conclusive (some screenshots? some numbers? stats? anything? pie charts?). Thanks for all the help everyone! |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-04-19 : 13:50:00
|
| I think you did the right thing in moving some of the indexes off. The best way I've found to figure out how many indexes to move was to use Performance Monitor and monitor the # of Bytes/Write and Read from each of the different disks. When I saw the load balanced over an hour or two period (or close to it) then I stopped moving indexes. We recently moved to a 2 Terrabyte SAN and what we didn't know going into it (probably our fault for not researching it more thoroughly) was tha tthe 2 TB was the "RAW" space. We configured the SAN with 2 controllers for redunancy. As it turned out each controller needed a lot of administrative space/overhead and we ended up with 2 454 QB configurations and weren't even close to what we thought we'd have. We ended up removing 1 of the controllers and now we have about 1.5 TB of available space, but of course we then lost our automatic failover ability. But we didn't buy 2 TB to have less than 1 TB of available space. So be careful and don't fall into that same trap. The really cool thing with the SAN is that it can have ISCSI and Fibre Channel interfaces to it so we were able to access it from some machines that we needed a lit bit more space on without having to drop 1,800 cards into. |
|
|
|
Krankensteins
Starting Member
24 Posts |
Posted - 2006-04-20 : 01:31:52
|
| I think,answer is in caind of database end qury types.4 crappy processors - is for paralelism.2 large cache processors - is needed when evry Query works with large amount of memory.2 dual-core processors (4 cores) - will be beter, but not match as crappy processors. Becouse they have one socket per two cores, if you need reduce CPU you need socket count not core. |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2006-10-06 : 12:15:54
|
| This is interesting....Every week i get at least 3 locks on my database i now found it to bePWAIT_CXPACKET....So im looking in to thisWe have 2 CPUminmum query plan thresold default 5.So should i increase the 5 to 10 say |
|
|
|
|
More procs versus better procs ???