Moving Sql Db To New Vm

[NicholasHarvey]

Good morning!

 

Server specs:

 

OS: SBS 2008 r2, vm

24gb of ram

3tB of space

12 core Xeon x5650 @ 2.67

 

Problem:

83% memory load,

sqlservr.exe using 5.6gb ram

sqlservr.exe *32 using 1.5gb of ram

 

 

..Can I create another VM that runs JUST SQL server?

Will that have any benefit to my problem?

 

Thanks in advance =)

[ICTCity]

SQL uses as much ram as it has. Giving 5.6 ram of ram to SQL having 32 available is not too much, everything depends on your DB. Putting SQL on a single VM is a good idea depending on your problem. Are you experiencing any particular problem?

[NicholasHarvey]

Honestly? No problem really,

besides the fact of the Ram usage..

 

The craziest thing is..

right now we're using about 20.4gb (out of 24) of ram..but if we reboot the pc,

it goes down to like 8gb.. then over the course of a week or so, it goes up and up..

 

So we're a bit worried.

[F2 Staff]

Unless you are putting the vm on another box you'll get no benefit at all.

[ICTCity]

Honestly? No problem really,

besides the fact of the Ram usage..

 

The craziest thing is..

right now we're using about 20.4gb (out of 24) of ram..but if we reboot the pc,

it goes down to like 8gb.. then over the course of a week or so, it goes up and up..

 

So we're a bit worried.

 

Don't be worried.

 

If you right click the SQL instance (from sql management studio), you can set the maximum ram used by SQL.

 

SQL works in this way: when it starts, it uses some RAM (let's say 1-2 gb), everytime a query is executed, SQL tries to "load" the database in ram. This process is called buffering (it's like a video streaming, more or less). Once the query is executed, SQL doesn't free the used ram, it can overwrite it.

 

When you restart your server, the RAM is cleaned, but you should be able to do the same thing by restarting the SQL service.

 

Everything depends on how much your query are long and complicated.

 

 

In other words, you can move the SQL on another VM, but this scenario will be the same.

 

You should assure to your OS at least 4 GB, if you have 24 GB, limit SQL to use 19 GB (SQL is not really precise about memory limit :P).

 

There are people who load the entire DB on RAM, this is possible by adding RAM. If your DB is 30 GB, you can give SQL 35 GB of ram and SQL will load the entire DB in RAM.

 

Hope this help.

[NicholasHarvey]

Thank you SO much for the explanation.

 

I will try to limit the RAM from the Sql instance, on the server.

 

Silly question.. what can I limit the SQL to?

The Server is using the SQL db- not something we're using, third party like...

[NicholasHarvey]

I know the scenario will be the same, but the outcome we're looking to achieve is to NOT have this problem on our main server..

[ICTCity]

For SQL (on the same screen where you limit the memory), you can limit the NUMBER of cores SQL can use, and also check / uncheck "BOOST SQL PRIORITY" which basically gives priority to SQL process (system's calls). To change the number of CPU used, you must stop the SQL instance.

 

Well, SQL is not so easy to understand and tune.

First of all, if you are using SQL Server 2k8, you can use the performance monitor (NOT THE PERFOMANCE MONITOR OF WINDOWS), you must open SQL management studio and then there's a little icon on the top. Here you can find some useful infos and also collect data for a long time to analyze them in the future.

 

Now, if you don't plan to change something (like adding another app which uses SQL), performances cannot be affected from today to tomorrow. You can also consider to use a mirrored environment. Many people are concerned regaring performance and say: "I don't want that my server will be slow", this is correct, but if your server goes down because of an hardware failure? This is why MIRRORING or CLUSTERING should be implemented.

 

If you need more details, feel free to ask.