| Author |
Topic |
|
mattaeus
Starting Member
3 Posts |
 Posted - 2008-06-17 : 05:45:08
|
| Salut,Apologies for yet another one of these posts - I've scrolled back through a few pages and seen a few similar posts, but I've yet to see something exactly the same as my issue; we had to upgrade our DB servers at the weekend as the hardware/software was not able to cope with the amount of concurrent traffic we were experiencing.So, we now have a server running Windows Server 2003 Enterprise 64 bit with SQL 2005 64 bit, 12GB RAM.When I set up the machine at the weekend it immediately consumed 6GB of the RAM, and chugged along happily with no performance issues.However, on Monday at 19:42pm (when we usually get the most traffic) the CPU hit 100% but RAM usage went up incredibly slowly, it got as high as 7GB before the traffic died down again, but it crept up so slowly the computers CPU never dropped from 100%. There was 4GB theoretically available (2GB if you factor in that I'd like to leave 2GB for the OS) that it just didn't use.So, reading in to it I found out about setting min and max memory usage and AWE.I set min memory usage to 8192MB, left the max at it's insane "limitless" value, did not do anything to AWE and asked my colleague in Australia to reboot the server at what would be 3:30am my time.When I came in this morning the CPU was at 100% again but the RAM usage was at 120MB and after 20 minutes it only got as high 130MB but CPU usage remained at 100%. Fearing that I'd done something wrong with the min memory setting I set it back to zero.About 30 minutes after the CPU settled back to bouncing between 20% and 40% and RAM usage has slowly dropped, now showing at 80MB.I suppose my fears are:1) If we hit a surge in traffic the machines are not going to utilise the memory quick enough.2) I was told with a 64bit machine you do not need to utilise AWE, is this the case?3) I was also told you should only set the min / max memory settings if you're using AWE, is that true?Is there anything drastically obvious I've missed here, or can someone link me to a good article or two with considerable detail about these memory settings as so far all I've found are still open forum posts?Cheers for any help. |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-06-17 : 06:00:23
|
| Just one thing, have you service packed SQL as you haven't mentioned it?SQL should grab memory and not release it unless asked to do so, so it should not come back down. The CPU useage can be caused by other issues. Do you have FTIs at all? Are the calls mostly reads or inserts? Is there a bottleneck somewhere (I/O queues etc)? |
 |
|
|
mattaeus
Starting Member
3 Posts |
Posted - 2008-06-17 : 07:28:00
|
| Thanks for the quick response.Just checked the install and it's not SP'd, which I'm a bit miffed about as it was one of the reqs I passed on to the hosting company when they set up the machine before I remote desktopped on to it to do my thing. Though admittedly it's my fault for not checking.Anyway, I'll get SP2 on first before focussing on the other suggestions.I will say that I know there's lots of indexing going on, but I'm not too sure about FTI; the problem being I "inherited" the database about 4 years ago from the previous head of IT, and I'm a web developer, not a DBA so I'm having to apply what little knowledge I've taught myself in the last 8 years I've been playing about with databases!Regarding the issue this morning the most accessed data was actually a read from a static table which only updates once a day with 5 rows of data, which I'm a bit perplexed about - last night I know it was related to a stored proc that involves reads and inserts wrapped in a transaction which is quite an intensive stored proc, but it usually isn't too taxing on the database, it's just I know last night we probably got at least 20,000 users accessing the database at once.There was a bottleneck on the old machine at this point, which is why we were forced to upgrade.Running tuning tools have already suggested ways of improving that stored proc which we can look at, but I'm just wondering if there's something else I've missed.Anyway, thanks for the response - we'll look in to these factors and I'll get my Aussie colleague to service pack it up tonight. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-06-17 : 09:02:24
|
| No worries, I know the original x64 release had quite a few problems, so applying the SP should hopefully fix it for you. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-17 : 09:07:32
|
| Apply SP2 with latest cumulative patches. |
|
|
|
mattaeus
Starting Member
3 Posts |
Posted - 2008-06-20 : 04:17:42
|
| Right, we have SP2 on and there's definately no Full Text Indexing going on.We worked out that Task Manager was lieing to us about the RAM usage and it was hovering at about 6GB, confirmed by running perfmon.However, after installing SP2 and rebooting the server about 6 hours ago RAM utilisation is at 1.5GB still crawling up slowly, despite setting the min memory to about 8GB. Have we misinterpretted the way that feature is used, or is it that we've not enabled AWE?Other than that there are two other things I'm looking at, namely until about a week ago this database was running on SQL 2000, the other thing is it utilises partitioned views (done whilst in SQL 2000).To be honest I'm beginning to think that the design of the DB isn't that efficient, and that we'll probably need a professional to come in (which would be annoying as I've been saying that for the last 2-3 weeks and the damage has already been done) |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-21 : 00:06:47
|
| Did you give sql service account 'lock pages in memory' user rights in windows on the server? |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2008-06-21 : 06:51:20
|
| Are you sure there aren't aren't any non-sql server processes chewing the CPU?Jack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com |
|
|
|
contrari4n
Starting Member
27 Posts |
Posted - 2008-06-21 : 15:35:34
|
| I have encountered similar issues when upgrading from one server/version/sp/whatever to another, and it was caused by different execution plans being generated. Have you checked whether one particular process is hogging all CPUs?If this is attached to a web server I'm guessing it is mainly OLTP. If that is the case it may be worth setting "max degree of parallelism" to 1. In an OLTP environment most connections do not benefit from parallelism, and setting this to 1 will prevent one stray process from affecting the others.Obviously you will want to try this in your test environment first. One good point about this change is that it can be changed back at any time without any downtime.It doesn't sound like memory is an issue on your server, or it would have quickly used up the max.Richard Fryarhttp://www.sql-server-pro.comSQL Server Articles and Tips |
|
|
|
|
SQL Server CPU 100% not utilising RAM.