Multiple SQL Instances

Posted on 2014-07-31
Last Modified: 2016-06-10
We have a client with one SQL Server 2012 license running an Active/Passive cluster. the server is running a few databases (about 20 mostly Sharepoint, Citrix PVS, Citrix XenAPP datastore etc.)

We have been told by a 3rd party that we should Create a second Instance on the same box and move our AX 2012 Databases to it (that its "critical"), however were not seeing any real issues that suggest any problem with the database server. What metrics can we monitor to ascertain whether this there are any issues that really would benefit from a separate instance.

We are aware that its "Best Practice" that's not what i am asking, I'm simply asking what performance indicators would identify a need for this.

Aside from memory isolation and a separate TempDB (were not suffering from much TempDB contention), what performance benefits does it provide?
Question by:graememutch
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40231778
Another point in favor of switching would be security.  You could have 2 SA accounts and have 2 different teams managing each instance (eg: 1 for the cluster and 1 for the new critical cluster).

If you're not running into performance issues though and if the above point isn't really that important I don't see a reason to switch.

Can the 3rd party provide any concrete reasons other than citing it's a best practice?

Author Comment

ID: 40231819
Hi Kyle, yeah I'm strictly looking at this from a performance point of view.

They haven't provided any performance reasons just hypothetical benefits i.e. memory management. I've got a consultation with their MS contact next week and I'm looking for some performance metrics to back up my argument as the client is now putting pressure on us that we havent adequately specced the SQL solution.

Am i right in saying that if there's no TempDB contention then memory is the only possible issue? Is there a way to analyze memory utilization within the Instance eg to show that nothing is waiting for memory, monitor memory grants pending? would that be enough?
LVL 40

Accepted Solution

Kyle Abrahams earned 250 total points
ID: 40231900
Everything I'm reading says it depends.  TempDB contention is certainly a concern, and I could see HD I/O also being a concern but that can be alleviated by putting different databases on different disks.

As some ammunition you can say that having multiple instances can create CPU contention . . . which is much more serious than memory management issues.

There is more memory consumption when you have multiple instances as each binary needs to be loaded in to RAM.

Author Comment

ID: 40231936
Yeah other databases have been spread around so that disk performance is within Microsoft's recommendations. and as you say this can all be done without a new SQL instance.

Yeah they initially asked for a separate server but for licensing reasons that was cost prohibitive, they're now saying that a separate SQL instance is essentially the same which it clearly isn't.

Assisted Solution

by:Damon Repton
Damon Repton earned 250 total points
ID: 40516266

Of course Microsoft best practice does say Dynamics AX should be on a dedicated SQL Server and not shared.

Only reason I can only have another instance could be

but if it was me I would do the following:

Performance Analyser for Dynamics 1.20 (DynamicsPerf)

Performance Analyser Version 1.20 for Microsoft Dynamics (DynamicsPerf) is a set of scripts to collect and analyze performance information from the database and application tiers of Microsoft Dynamics products.

Download from  or /

Installation Guide

1.      Performance Analyzer for Microsoft Dynamics 1.20 Deployment Guide Core Installation: 

2.      Performance Analyzer for Microsoft Dynamics 1.20 Deployment Guide Dynamics AX Installation 

Windows Performance Monitor

Performance Monitor provides a visual display of built-in Windows performance counters, either in real time or as a way to review historical data. You can add performance counters to Performance Monitor by dragging and dropping, or by creating custom Data Collector Sets. It features multiple graph views that enable you to visually review performance log data. You can create custom views in Performance Monitor that can be exported as Data Collector Sets for use with performance and logging features.

To launch Windows Performance Monitor
•      Click Start , click in the Start Search box, type perfmon , and press ENTER.

Performance Analysis of Logs (PAL)

The PAL (Performance Analysis of Logs) tool is a powerful tool that reads in a performance monitor counter log and analyzes it using known thresholds.

Download from
•      Thresholds files for most of the major Microsoft products such as IIS, MOSS, SQL Server, BizTalk, Exchange, and Active Directory.
•      An easy to use GUI interface which makes creating batch files for the PAL.ps1 script.
•      A GUI editor for creating or editing your own threshold files.
•      Creates an HTML based report for ease of copy/pasting into other applications.
•      Analyzes performance counter logs for thresholds using thresholds that change their criteria based on the computer's role or hardware specs.

from these tools you will see any issues

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question