requirements on multi instanses

Question

requirements on multi instanses

Krasavita

SSC-Insane

Points: 23138
More actions
January 5, 2010 at 8:36 am

#386043

Hello friends,
I have 9 instances for my dev enviroment and we are having performance problems(very slow)Can you please provide me the requirements,what you need to do for the requirements when you have so many instances.
Thank you

Viewing 15 posts - 1 through 15 (of 55 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

What is the configuration of the development server (total server memory, disk drives (raid type or not, etc)? How are the instances configured (min/max memory, etc)?

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

Krasavita (1/5/2010)
Hello friends,
I have 9 instances for my dev enviroment and we are having performance problems(very slow)Can you please provide me the requirements,what you need to do for the requirements when you have so many instances.
Thank you

With 9 instances, unless you have a server with a lot of processors and cores and memory and a substantial disk IO system behind it, you can expect to have performance issues. For development, 1-1.5GB of RAM per instance would be bare minimum depending on the size of the databases and workloads being developed. Having 9 instances is going to result in processor context switching if all of the instances are being used concurrently. Make sure that you actually need 9 instances. In most scenarios, you would be better off having 1 instance with multiple databases for development purposes. If you apply security correctly, the databases are still isolated from developers that shouldn't have access to them.

Krasavita SSC-Insane Points: 23138 More actions · Answer 3

With 9 instances, unless you have a server with a lot of processors and cores and memory and a substantial disk IO system behind it, you can expect to have performance issues. For development, 1-1.5GB of RAM per instance would be bare minimum depending on the size of the databases and workloads being developed. Having 9 instances is going to result in processor context switching if all of the instances are being used concurrently. Make sure that you actually need 9 instances. In most scenarios, you would be better off having 1 instance with multiple databases for development purposes. If you apply security correctly, the databases are still isolated from developers that shouldn't have access to them.

Is there any documentations that I can find out on that. What should I run to see how much memory used for all instanses.

Thank you

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

Krasavita (1/5/2010)
Is there any documentations that I can find out on that. What should I run to see how much memory used for all instanses.
Thank you

Which specific part are you asking for documentation on? To check memory usage by instance look at the Total Server Memory and Target Server Memory performance counters in the SQL Server:Memory Manager object for each of the instances in perfmon. You can also look at the Process\sqlservr*\Working Set counter.

From SQL Server, you can query sys.dm_os_performance_counters if it is SQL Server 2005/2008 and see the Total Server Memory and Target Server Memory counters.

select *

from sys.dm_os_performance_counters

where object_name = 'SQLServer:Memory Manager'

The rest of the information is more or less experience running multi-instance SQL Servers for production use and an understanding of how SQL Server commits and utilizes memory for caching.

Krasavita SSC-Insane Points: 23138 More actions · Answer 5

I am looking for documentation, what do I need to have on 9 instanses, not to have performance issues. Right now we are using all of them and it is slow. It is only for development server.

Thank you

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

Lynn Pettis (1/5/2010)
What is the configuration of the development server (total server memory, disk drives (raid type or not, etc)? How are the instances configured (min/max memory, etc)?

The information that Lynn asked for initially would be a good start.

Krasavita SSC-Insane Points: 23138 More actions · Answer 7

How can I see in sql server all instanses?

This is what I have from your code,but it is not showing instanse name:

object_name counter_name instance_namecntr_valuecntr_type

SQLServer:Memory Manager Connection Memory (KB) 216 65792

SQLServer:Memory Manager Granted Workspace Memory (KB) 0 65792

SQLServer:Memory Manager Lock Memory (KB) 44865792

SQLServer:Memory Manager Lock Blocks Allocated 355065792

SQLServer:Memory Manager Lock Owner Blocks Allocated 505065792

SQLServer:Memory Manager Lock Blocks 2165792

SQLServer:Memory Manager Lock Owner Blocks 2465792

SQLServer:Memory Manager Maximum Workspace Memory (KB) 1680065792

SQLServer:Memory Manager Memory Grants Outstanding 065792

SQLServer:Memory Manager Memory Grants Pending 065792

SQLServer:Memory Manager Optimizer Memory (KB) 48065792

SQLServer:Memory Manager SQL Cache Memory (KB) 20865792

SQLServer:Memory Manager Target Server Memory (KB) 819265792

SQLServer:Memory Manager Total Server Memory (KB) 819265792

Steve Jones - SSC Editor SSC Guru Points: 736281 More actions · Answer 8

You need to look in the properties for each instance to determine what the memory settings are, not the Windows System Monitor.

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 9

Jonathan Kehayias (1/5/2010)
Krasavita (1/5/2010)
Is there any documentations that I can find out on that. What should I run to see how much memory used for all instanses.
Thank you
Which specific part are you asking for documentation on? To check memory usage by instance look at the Total Server Memory and Target Server Memory performance counters in the SQL Server:Memory Manager object for each of the instances in perfmon. You can also look at the Process\sqlservr*\Working Set counter.
From SQL Server, you can query sys.dm_os_performance_counters if it is SQL Server 2005/2008 and see the Total Server Memory and Target Server Memory counters.
select *
from sys.dm_os_performance_counters
where object_name = 'SQLServer:Memory Manager'
The rest of the information is more or less experience running multi-instance SQL Servers for production use and an understanding of how SQL Server commits and utilizes memory for caching.

I am afraid just a document would help in your case. I suggest you answer the questions Lynn posted.

Unless we know how many CPU's you have, the amount of memory and the type of RAID on your Development server I don't think you could get a concrete answer.

Try to get us those details.

Thanks..

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 10

Krasavita (1/8/2010)
How can I see in sql server all instanses?
This is what I have from your code,but it is not showing instanse name:

Either use PerfMon and add the counters for each of the instances and use the Report View (Ctrl+R) to look at them side by side, or you have to login to each of the SQL Server Instances and query the information one by one. If you have SSMS 2008 you can use a Multi-Server Query[/url] to get the information.

Krasavita SSC-Insane Points: 23138 More actions · Answer 11

Krasavita

SSC-Insane

Points: 23138

January 8, 2010 at 8:23 am

#1100925

Is each instance contains their own RAM?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

Krasavita (1/8/2010)
Is each instance contains their own RAM?

It depends,

PLEASE ANSWER THE FOLLOWING:

What is the configuration of the development server (total server memory, disk drives (raid type or not, etc)? How are the instances configured (min/max memory, etc)?

Krasavita SSC-Insane Points: 23138 More actions · Answer 13

Server memory: Min 0, max:2147483647 MB, 1 processor

Krasavita SSC-Insane Points: 23138 More actions · Answer 14

What is the configuration of the development server (total server memory, disk drives (raid type or not, etc)? How are the instances configured (min/max memory, etc)?

How can I check these, I can't get to the server it is timing out on me.Can I run someting from query analyzer