| Author |
Topic |
|
medtech26
Posting Yak Master
169 Posts |
 Posted - 2009-08-24 : 14:54:12
|
Hello,We used to run both our online application and SQL Server 2005 on the same dedicated server, due to performance issues we decided to move SQL Server to it's own machine (dedicated server).Well, the process is complete and seems to work as expected (sites are much faster  ), however, I do have some concerns that I would like to clear before closing this chapter:- On our previous machine SQL used about 1.7GB of memory constantly (give or take 10s of MBs here and there
 ) and on the new server it takes more then 3.2GB. It's the same DB as well as same site traffic (with some growth, but not at the same rate as the memory  ). What cause SQL to unleash this massive memory usage and is this normal? - On our previews machine "the old" SQL is still running and as far as we know nothing connects to it. However, it shows like it is still using 1.7GB of memory. How can I verify that no application/process/whatever uses this DB before shooting it down?
- In addition to the previous question, is there a way to query all recent changes to the DB (in order to verify it's not being added/updated)?
If there's anything you would like to add and/or I forgot you're more then welcome to share  .Thanks in advance |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-08-24 : 15:01:52
|
| 1. SQL Server will use all the memory allocated to it. It is caching more now that it doesn't have to share with your app server. This isn't a problem. It is a good idea to set the max memory though, leaving 1 or 2 GB for the OS2. Not going to release the memory until you shut down the sqlserver service3. Run a profiler trace. |
 |
|
|
medtech26
Posting Yak Master
169 Posts |
Posted - 2009-08-24 : 16:19:13
|
| 1. The new server is solely dedicated to run SQL, do I need to leave so much memory (1 or 2GB) for the OS?2. I was actually planning on "shooting" it as it was very slow and annoying ... but cause I need the server to run our application I'll prob. just shut down SQL.3. The tracer confirms that nothing connects to the "old" server.Thanks russell! |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-08-24 : 23:29:55
|
| I'd leave at least a GB for OSHow much RAM do you have? If 8 GB or more, probably leave 2 GBFeels much better now that SQL is running on its' own server huh? |
|
|
|
medtech26
Posting Yak Master
169 Posts |
Posted - 2009-08-25 : 13:58:46
|
Currently we're only running on 4GB so I left 1GB for OS. It feels like taking a big load off your chest (directly into the wallet  ). Again, thanks for your help, it's highly appreciated. |
|
|
|
medtech26
Posting Yak Master
169 Posts |
Posted - 2009-10-14 : 15:39:50
|
| UPDATEAs traffic to site increased significantly it became very slow. The machine that's running the SQL Server showed 95%+ memory usage most of the time (task manager showed that sqlserver process is consuming 3.2GB constantly). Therefor we upgraded to 8GB (late Sep) hoping it's going to solve the issue. Since the upgrade it was slightly better for a short while, but as traffic growing (as well as spiders/boots grabbing data) on daily basis the site is slow again and many times too slow to response. Although I "allow"* SQL Server to use up to 6GB, it uses only 3.6GB constantly.It also fails when running time\memory consuming queries on the QA (the site goes down).Please advise with tips on how to improve memory usage or any useful info., THANKS.* I set up the 6GB by: right click on the server > properties > memory > checked "use AWE to alocate memory" as well as set 6144 in the "maximum server memory (in MB)" |
|
|
|
medtech26
Posting Yak Master
169 Posts |
Posted - 2009-10-15 : 11:49:34
|
| I restarted the server (SQL) and when coming back the memory consumption grew to 3.6GB and not a bit more. For some reason it wont use any more memory, any idea what can cause\limit it? |
|
|
|
medtech26
Posting Yak Master
169 Posts |
Posted - 2009-10-16 : 14:44:11
|
| I was told that the SQL Server 32bit supports up to 4GB of RAM and in order to use more RAM I need to upgrade to 64bit. Can someone please confirm?EDIT: If one of the moderators can move this topic to "SQL Server Administration (2005)" it would be highly appreciated, thanks. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-10-18 : 11:27:29
|
| Thats incorrect. 32-bit can support more memory if you have setup proper AWE configurations and Enable lock pages in Memory. But the caveat is you can't have memory used for sorting,hashing and internal operations. For that reason, people migrate to 64-bit. |
|
|
|
medtech26
Posting Yak Master
169 Posts |
Posted - 2009-10-19 : 13:47:37
|
| I have no idea what's (or how to) "Enable lock pages in Memory" but I did setup AWE to use more memory with no success. Anyway, we upgraded to the 64-bit platform and now memory usage grew to 5.7GB and the site is much faster [:-)]. |
|
|
|
medtech26
Posting Yak Master
169 Posts |
Posted - 2009-10-23 : 14:18:36
|
| Does anybody know if the 64-bit platform have any memory limitations (cap)? We grew 15% in traffic since last week and in result SQL server is maxed out again. I wonder if we should upgrade to more RAM (24GB) or get one more server and work out some sort of scaling, your opinion?Is there a tool to monitor SQL Server activities? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-10-23 : 16:03:50
|
| 64-bit SQL Server will be give maximum OS Memory. But you should assign some memory for OS with Max Memory Settings.We use SQL Diagnostic Manager to Monitor and generate reports. |
|
|
|
medtech26
Posting Yak Master
169 Posts |
Posted - 2009-10-23 : 16:41:45
|
| Thanks. Is SQL Diagnostic Manager part for SQL package (Microsoft) or third party? Google bring several tools, please provide a link to the one you're referring. |
|
|
|
|
Server Migration