Posted by Greg D. Moore \(Strider\) on 02/20/07 16:39

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns98DD3281B42Yazorman@127.0.0.1...
> Dylan Parry (usenet@dylanparry.com) writes:
>> I used SQL Server 2005 on my development machine, and whilst this
>> machine isn't as powerful as the live server, it does at times seem a
>> little slower than I would expect. So I've been wondering if there is
>> any way for me to tune the machine so that SQL Server is better able to
>> make use of the resources? I am using WS2003.
>>
>> Short of tweaking with the actual database, which is still under
>> development, does anyone have any tips to increase peformance?
>
> Yes, SQL 2005 on a developer machine with low memory can be quite
> painful. I have had situations where a simple query from Northwind could
> take 10 seconds. When this happened, I noticed that SQL Server had only
> 30 MB of memory. When I closed down other application, SQL 2005 started
> to behave normally again.
>
> It can only also be a good idea to configure SQL Server to for a max
> server memory of 100-150 MB. You do like this:
>
> EXEC sp_configure 'max server memory', 100
> RECONFIGURE
>
> This prevents SQL Server for sending everything else to the page file.
> And, I have the impresssion that this can also improve the performance
> of SQL Server. If it knows it may not have more than 100 MB, it will
> not spend time on waiting to get more.

I'll add too that "memory is cheap". Most mobos these days will support 2
gig of memory (or more).

If you can, upgrade your physical RAM. You won't regret it.

--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

[Back to original message]