Configuring SQL Server?

Thanatos

Hey, my computer at work is running hella slow. Yesterday, it took Outlook literally ten minutes to load. The problem seems to be SQL Server, and the fact that it runs with the business contacts in Outlook (when I shut it off in MSConfig, Outlook loaded way faster, though I got a couple of error messages). I've Googled it, and apparently when it is poorly-configured, it can eat through system resources. So, how the hell do I properly configure it?

And before you say "talk to your office IT guy," I am our office IT guy. :P

exmello

The default setting for SQL Server is to consume as much memory as possible and free up resources as they are needed by other applications. In Enterprise Manager/Management Studio, right-click the server node go to properties and set the max memory to something like half of your total memory. It's alright for it to take up a lot, it's just reserving that space but not actually using it all.

Thanatos

I couldn't find any sort of setting like that in the configuration manager. Could you be more specific as to where to look? It's Microsoft SQL Server 2005, if that matters.

Nakatomi2010

Best way to solve SQL troubles is to add more memory to the system...

To edit SQL you need to get the management studio, which is a different installer than the traditional SQL Express that most people use... This guy here

GrimReaper

By default SQL loves gobbling memory, you can very easily change its memory settings. But to put it bluntly don't run it on your desktop machine unless it's in a VM, if you don't have a dev server hanging around that is.

Here are the memory settings, not particularly hard to find:



I doubt much is changed between that and 2005. (that's a screengrab from SQL 2000)

Yeah, quick google search turns up this:

(hotlink ho!)

So, the gui has had a minor change but the settings are still the same etc.

Jasconius

I don't think I've ever heard of SQL doing that. I'm running a DB locally right now with hundreds of thousands of records that I've been hitting all day, and its running right now at about 50 megs of RAM.

This is an out of the box installation of 2005.


It's probably the interaction between Outlook and SQL that's causing the problem. Microsoft have software interoperability problems? Unheard of.

Nakatomi2010

We've got an SQL box here that'll frequently consume about 500mb of RAM, but when it hits 1.5GB we have to reboot the box...

exmello

Don't worry about how much it's actually consuming on a server that only contains sql server. If you are running it on your desktop, sure you'll probably get a performance increase in your other applications by setting a max. I'm used to seeing one of our servers take up 6 gigs of page file, but it's not even an issue, because all it does is reserve as much memory as it can to perform smoothly and free things up when it other applications request.

A large memory footprint is not necessarily a bad thing.

Nakatomi2010

Well, the server is used for a number of things, but I also know it's a server's lot in life to work itself into a hole...

Feral

GrimReaper wrote: »

But to put it bluntly don't run it on your desktop machine unless it's in a VM

Tell that to Microsoft who has been merrily bundling Microsoft SQL Desktop Engine with Outlook for the last couple of years. :P

Thanatos: I'd remove Business Contact Manager and MSDE entirely. Are you using the special features of BCM at all?

necroSYS

Jasconius wrote: »

I don't think I've ever heard of SQL doing that. I'm running a DB locally right now with hundreds of thousands of records that I've been hitting all day, and its running right now at about 50 megs of RAM.

This is an out of the box installation of 2005.


It's probably the interaction between Outlook and SQL that's causing the problem. Microsoft have software interoperability problems? Unheard of.

Are you sure you're using SQL and not MySQL?

It's so well known it's practically gospel that SQL is a huge memory hog, unless you configure it otherwise.

Jasconius

Yeah.

MSSQL 2005


As a matter of fact I've been using MSSQL quite a bit over the past year on personal machines and I've not ever run into a performance or memory problem.

virgilsamms

Just a guess but the memory footprint may be related to the size of the databases and the kinds of tasks you're doing. So some might never have issues. But yeah, MSSQL is a memory hog that will eat your resources if you let it. I'm in the middle, running it locally is usually fine but I have to restart the service every fortnight or so because it sits all over my system.