How to give my SQL Server more "snap"

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Spin - 22 Apr 2008 14:04 GMT

Gurus,

Running SQL Server 2005 SP2 on Windows Server 2003 SP2 as a guest OS inside
a VMWare session. Due to hardware constraints, I typically run this SQL
server at either 512 or 768MB RAM. It works OK, but I cannot give it any
more RAM b/c I need other guest OSes to run on the VMWare host. Outside of
adding more RAM, is there anything I can do to give my SQL Server more
"snap"?

Signature

Spin

Reply to this Message

Linchi Shea - 22 Apr 2008 14:34 GMT

The most effective approach is what is tried and true: first identify where
the key bottleneck is--the bottleneck that is constraining the performance,
and then add resources to reduce or eliminate that bottleneck.

Linchi

> Gurus,
>
[quoted text clipped - 4 lines]
> adding more RAM, is there anything I can do to give my SQL Server more
> "snap"?

Reply to this Message

Spin - 22 Apr 2008 14:46 GMT

> The most effective approach is what is tried and true: first identify
> where
[quoted text clipped - 3 lines]
>
> Linchi

I'm looking for software tricks only since I cannot add more hardware
resources. Outside of the built-in system databases I am running only one
application database which will not grow beyond 100MB. Given that scenario,
how can I improve performance?

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 22 Apr 2008 14:54 GMT

What exactly is slow? Typically in a VM you observe noticeably slower
responses to clicks and menu selections etc., but this is partly a video
driver problem and is not exclusive to the SQL Server instance running
inside the VM. Unless you are convinced that the server is responding
slowly because of memory pressure, my guess is that the perceived slowness
is a different problem. If you explain what you mean by "snap" and what
type of operations need to be "snappier"...

>> The most effective approach is what is tried and true: first identify
>> where
[quoted text clipped - 8 lines]
> application database which will not grow beyond 100MB. Given that
> scenario, how can I improve performance?

Reply to this Message

Spin - 22 Apr 2008 15:04 GMT

> What exactly is slow? Typically in a VM you observe noticeably slower
> responses to clicks and menu selections etc., but this is partly a video
[quoted text clipped - 3 lines]
> is a different problem. If you explain what you mean by "snap" and what
> type of operations need to be "snappier"...

I would be very happy if I could at least get the SQL Server Management
Studio to open faster. That's what I mean by "snap".

Reply to this Message

Greg D. Moore (Strider) - 22 Apr 2008 15:48 GMT

>> What exactly is slow? Typically in a VM you observe noticeably slower
>> responses to clicks and menu selections etc., but this is partly a video
[quoted text clipped - 6 lines]
> I would be very happy if I could at least get the SQL Server Management
> Studio to open faster. That's what I mean by "snap".

That's almost certainly a VM issue. How much CPU does the VM have
available? How much is it using?

Also, disk I/O is typically slower under VMs.

Signature

Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Reply to this Message

Ekrem Önsoy - 22 Apr 2008 18:12 GMT

If your problem is the slowness of the openning of SQL Server Management
Studio then refer to the following link:
http://support.microsoft.com/default.aspx/kb/555686

Signature

Ekrem Önsoy

Reply to this Message

Spin - 23 Apr 2008 14:52 GMT

> If your problem is the slowness of the openning of SQL Server Management
> Studio then refer to the following link:
> http://support.microsoft.com/default.aspx/kb/555686

Rock on! This article provided the kind of tip I was looking for!

RESOLUTION

First Step:

1. Go to "Start" -> "Programs" -> "Microsoft SQL Server 2005".

2. Open "SQL Server Management Studio".

3. Navigate to "Tools" -> "Options" -> "Help" -> "Online".

4. Mark the checkbox "Try local first, not online"

5. Press on "Ok" button.

6. Restart SQL Server Management Studio".

. Tip: In some cases you may need to disable "Check for publishers
certificate revocation" option

under the Internet Explorer -> "Internet Options" ->
Advanced".

Reply to this Message

thejamie - 23 Apr 2008 20:40 GMT

You may want to check to see if Integration and Reporting services are
running - if you don't need them, disable them. Ditto with other services
like the browser and full text.

Signature

Regards,
Jamie

> > If your problem is the slowness of the openning of SQL Server Management
> > Studio then refer to the following link:
[quoted text clipped - 23 lines]
> under the Internet Explorer -> "Internet Options" ->
> Advanced".

Reply to this Message

Spin - 24 Apr 2008 03:12 GMT

> You may want to check to see if Integration and Reporting services are
> running - if you don't need them, disable them. Ditto with other services
> like the browser and full text.

You're right. I am running a default SQL instance on port 1433 so there's
no need to run the SQL browser service - I believe that service is only
needed if you run dynamic ports, or if you want your SQL server to be
"discovered" on the network....I think.

Reply to this Message

Ekrem Önsoy - 28 Apr 2008 14:27 GMT

If your SQL Server instance is a Default Instance and if you do not have any
other Named Instances of SQL Server then you don't need the Browser Service.
It's used to let your clients or remote servers know the dynamic port of
your SQL Server instance. Even if you had a Named Instance (which is going
to be using a Dynamic Port by default) if you set its port as a static port,
you would not need the Browser Service. It's used when your Instance of SQL
Server uses a Dynamic Port.

Signature

Ekrem Önsoy

>> You may want to check to see if Integration and Reporting services are
>> running - if you don't need them, disable them. Ditto with other
[quoted text clipped - 5 lines]
> needed if you run dynamic ports, or if you want your SQL server to be
> "discovered" on the network....I think.

Reply to this Message

bobjackson2112@yahoo.com - 30 Apr 2008 06:40 GMT

Hi Spin,
You're probably correct in thinking that your VMWare Windows server
needs more memory, especially running SQL Server 2005.
512-768Mb of RAM is pretty low for a SQL Server box.
Dig out task manager to get simple confirmation that it's memory and
not CPU first, as low spec CPUs can get hit hard when running multiple
operating systems.

The most obvious solution I can think of if you can't add more memory
is to be more frugal with what you have got.

Have you tried stopping or disabling any services which you **know**
you do not need (don't stop them if in doubt !).
If you do this on both your SQL Server VM and the other VMs running on
the same server, you may find a significant difference in available
memory.
However, do exercise caution and make a note of what you have stopped
or disabled, in case it needs to be restarted. If you don't know what
a service does, leave it alone ar at the very least find out before
changing its status.

Bob.

Bob Jackson.
http://www.SQLServerClub.com

Reply to this Message