Speed Optimization

Question

Speed Optimization

Jas_The_Ace

Valued Member

Points: 53
More actions
August 7, 2009 at 5:36 am

#133796

Please could you give me a few pointers....
I have two instances of MSSQL running on the same server. One's really slow.
I know this because I have copied a database from one to the other and run them side by side.
It's got to be pretty easy to fix. All I have to do is make sure they are set up exactly the same.
Please tell me which config settings I need to check.
Thanks
Jason

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 1

First, two instances on the same server are likely to run into contention. In all likelihood, one of them is using all the memory and the other is starving. As a matter of fact, you won't be able to set both to the faster running one, because of this issue. Instead, what you really need to do is divide your memory in half (while leaving about a gb for the OS) and then making sure that each of the instances can only use half the available memory.

After that, I'd also suggest setting affinity for the processors (assuming you have multiple processors) to each instance to avoid contention there.

After that, you should check things like parallelism threshold, max degree of parallelism, the configuration of tempdb (one file for each processor that that instance can use), and maybe the default connection settings (I'd suggest using the ANSI standards).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jas_The_Ace Valued Member Points: 53 More actions · Answer 2

Brilliant. Thanks - That gives me a few things to look at.

The slow instance has 1-5 users on it. The fast instance has just one (me). Therefore I don't think it's a resource sharing issue.

The instances are running on a file server. The fast one has the sharepoint database on it (which isn't being used). I guess it was set up when they installed sharepoint server. My NetAdministrator didn't want me to use it for production databases so he set up this new instance on a different drive, and he says it's exactly the same.

I forgot to mention I'm a novice at DBAdmin, so please tell me a few obvious things that I might have overlooked

Jason

Paul White SSC Guru Points: 150467 More actions · Answer 3

On quick way to give us some hard data about the instances is to attach the output from the following command:

SELECT * FROM sys.configurations;

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jas_The_Ace Valued Member Points: 53 More actions · Answer 4

Thanks. Here it is. both servers give the same output

Paul White SSC Guru Points: 150467 More actions · Answer 5

The things that leap out are (just as Grant predicted) that the memory per instance has not been configured correctly.

You don't say how much memory is on the physical server, nor what edition it is, so I'm going to assume it is two instances of 32-bit Standard Edition running on a server with 2GB RAM.

All things being equal (which they rarely are) I would probably set both servers to something like this:

EXECUTE sp_configure 'show advanced options', 1;

RECONFIGURE;

EXECUTE sp_configure 'min server memory (MB)', 256

RECONFIGURE;

EXECUTE sp_configure 'max server memory (MB)', 768

RECONFIGURE;

EXECUTE sp_configure 'awe enabled', 1;

RECONFIGURE;

EXECUTE sp_configure 'show advanced options', 0;

RECONFIGURE;

The min and max server memory options will take effect without a service restart. Do this first.

Enabling AWE requires that the accounts the SQL Servers run under each have the 'lock pages in memory' Windows privilege. See both the following articles for details on how to configure that: http://technet.microsoft.com/en-us/library/ms190730.aspx and http://technet.microsoft.com/en-us/library/ms190961.aspx

You may need to restart the physical server to get the "Address Windowing Extensions enabled" message, depending on whether you have to add the lock pages in memory privilege or not. Note that if the SQL Server service runs as Local System, that step is unnecessary.

Enabling AWE is usually beneficial even if less than 4GB memory is installed (before anyone says anything!)

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 6

I agree with Paul. Split up the machine and you should be good to go. However, it's worth noting, the "good" instance might not be quite so good any more.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jas_The_Ace Valued Member Points: 53 More actions · Answer 7

Thanks,

I just checked with my administrator. He says:

Do these settings look OK for SQL Server 2005 Workgroup edition running on a Small Business Server 2003 system with 4GB RAM?

Paul White SSC Guru Points: 150467 More actions · Answer 8

For that configuration, try:

EXECUTE sp_configure 'show advanced options', 1;

RECONFIGURE;

EXECUTE sp_configure 'min server memory (MB)', 512

RECONFIGURE;

EXECUTE sp_configure 'max server memory (MB)', 1536

RECONFIGURE;

EXECUTE sp_configure 'awe enabled', 1;

RECONFIGURE;

EXECUTE sp_configure 'show advanced options', 0;

RECONFIGURE;

I am assuming that the server is dedicated to SQL Server.

In any case, just tweak the max server memory setting until you see 256MB to 512MB free once everything has been up and running for long enough to reach a steady state.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi