0

This is going to be my first attempt at fine tuning our SQL Server 2008R2, and I'd like a starting point based on the following.

When I view the resource monitor, I see (in KB): Commit: 843,948 Working Set: 718,648 Shareable: 26,276 Private: 692,372

Out of 2 gigs available on our virtual server, 1.6 is getting used up, and I suspect it is due SQL Server, and the memory gets chewed up when I initiate a service that does a bunch of TVP inserts and checks. I already added some GC.collect() in my c# service, however I'm not really seeing much of a change, which leads me back to the SQL Server.

Where would be a good starting point for me to learn more about optimizing based on this information, and some quick pointers?

Thanks.

Improve this question

3 Answers 3

Reset to default
5

Here is a quick pointer: buy more memory. 2GB is nothing today.

For the long answer: you need to understand how SQL Server allocates and uses memory. 1.6Gb on a 2Gb box is perfectly normal. See Dynamic Memory Management:

When SQL Server starts, it computes the size of virtual address space for the buffer pool based on a number of parameters such as amount of physical memory on the system, number of server threads and various startup parameters. SQL Server reserves the computed amount of its process virtual address space for the buffer pool, but it acquires (commits) only the required amount of physical memory for the current load.

The instance then continues to acquire memory as needed to support the workload. As more users connect and run queries, SQL Server acquires the additional physical memory on demand. A SQL Server instance continues to acquire physical memory until it either reaches its max server memory allocation target or Windows indicates there is no longer an excess of free memory; it frees memory when it has more than the min server memory setting, and Windows indicates that there is a shortage of free memory.

In other words, SQL Server will not release the 1.6Gb unless there is memory pressure notification from Windows.

And finally, about your question on where to look for info on optimizations: Waits and Queues is an excellent resource. It is a methodology that allows you to identify the bottlenecks and recommends the appropriate action for all common bottleneck cases.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

SQL Server is designed to pre-allocate and "eat up" all the memory you let it use. There really is no way to see any improvement except to reduce the SQL footprint in the configuration.

Improve this answer

Comments

1

If it's the default configuration, sql server will analyse usage and then grab as much memory as it can to optimise itself. If other apps then start asking for memory it gives it back.

There are a couple of values you can play with in terms of memory, a minimum which is the amount sql server will keep to itself, and a maximum which it will never grab more than. You can also play around with the number of threads it will run. You'll need some good stats for this. Depends on your usage patterns, and what else needs memory and how well it plays with others. Mess about and you can starve sql server, which is never a brill idea. I've always been a big fan of dedicated machines for dbms for any non trivial use.

As much art as science this, unless you find something horrible in there, slowing down your sql server will give your applications lots of memory to do nothing with because they are waiting for the Db....

More stuff to have a look at. MSDN - Sql Server Performance and Memory You need to get performamnce monitoring going and a have a good idea what sort of things are going on during the run. And you want an 'average' run. Peak hits, Out of office hours processing, holidays etc, no use at all.

PS don't forget performance monitoring is a significant hit on the machine.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.