Bom, dada uma necessidade que terei em executar 6 instâncias de SQL2008 em um único servidor x64 como 64 cores e 1TB RAM, entrei em uma conversa bem legal com meu colega do time de suporte Microsoft para LATAM Roberto Cavalcanti(@rocavalc).
Eu sempre fui da opinião que nestes casos o ideal é você fixar a memória do SQL em cada instância e também definir quais CPUs devem atender cada instância (CPU Affinity Mask). Isso isolará as instâncias com relação ao consumo de CPU e memória, fazendo com que possíveis overheads em um instância não afete outras instâncias. O problema pode ser se o servidor não for dedicado pois neste caso outros processos pesados rodando no nível SO, como por exemplo, um client de backup pode afetar todas as instâncias (se vc acha que isso não é pesado experimente rodar o LiteSped e deixe ele usando todas as CPUs). Neste caso concordamos que uma boa pode ser deixar a CPU0 fora do Affinity e ao final a conversa rendeu um e-mail com alguns links bem interessantes…
O artigo abaixo é bastante antigo, cobre o SQL Server 2000 e o Windows 2003, porém segue princípios de teste e medição do ambiente que se aplicam mesmo para ambientes com o Windows 2008 e o SQL Server 2008.
Lessons Learned
http://technet.microsoft.com/en-us/library/cc917532.aspx
Este outro, fala sobre a configuração de memória em ambiente executando multiplas instâncias e destaco um ponto chave do artigo…
Server Memory Options
http://msdn.microsoft.com/en-us/library/ms178067(v=SQL.100).aspx
Running Multiple Instances of SQL Server
“When you are running multiple instances of the Database Engine, there are three approaches you can use to manage memory:
· Use max server memory to control memory usage. Establish maximum settings for each instance, being careful that the total allowance is not more than the total physical memory on your machine. You might want to give each instance memory proportional to its expected workload or database size. This approach has the advantage that when new processes or instances start up, free memory will be available to them immediately. The drawback is that if you are not running all of the instances, none of the running instances will be able to utilize the remaining free memory.
· Use min server memory to control memory usage. Establish minimum settings for each instance, so that the sum of these minimums is 1-2 GB less than the total physical memory on your machine. Again, you may establish these minimums proportionately to the expected load of that instance. This approach has the advantage that if not all instances are running at the same time, the ones that are running can use the remaining free memory. This approach is also useful when there is another memory-intensive process on the computer, since it would insure that SQL Server would at least get a reasonable amount of memory. The drawback is that when a new instance (or any other process) starts, it may take some time for the running instances to release memory, especially if they must write modified pages back to their databases to do so. You may also need to increase the size of your paging file significantly.
· Do nothing (not recommended). The first instances presented with a workload will tend to allocate all of memory. Idle instances or instances started later may end up running with only a minimal amount of memory available. SQL Server makes no attempt to balance memory usage across instances. All instances will, however, respond to Windows Memory Notification signals to adjust the size of their buffer pools. As of Windows Server 2003 SP1, Windows does not balance memory across applications with the Memory Notification API. It merely provides global feedback as to the availability of memory on the system.
You can change these settings without restarting the instances, so you can easily experiment to find the best settings for your usage pattern“
Este outro fala sobre a configuração de Affinity das CPUs e mostra uma boa razão para você configurar o Affinity para as instâncias…
CPU Affinity Mask and Hard Binding
http://blogs.solidq.com/rdyess/Post.aspx?ID=10&title=CPU%20Affinity%20Mask%20and%20Hard%20Binding
“
I will admit there are several cases when you need to set Affinity mask, but you should try everything you can before you actually do so. Cases when you might need it are usually reserved to having multiple instances of SQL Server on the same server and you want to make sure that the processes executing in one instance do not take up all the CPU resources and starve the processes in the other instances from being executed quickly. Think of an instance executing very large report queries that are consuming 100% of your CPUs. The second instance needs to run a very quick update query for a user on the web. This user may actually have to sit and wait on the report queries to leave some CPU available for it
The fourth thing you might try is to utilize SQL Server trace flag 8002. This trace flag will still respect the Affinity Mask but will not bind the schedulers to particular CPUs. This allows threads from the schedulers to utilize all the CPUs in the affinity mask “
Uma coisa que me chamou a atenção e que até então eu não conhecia é o trace flag 8002! O ponto é que com este trace Flag 8002 você conseguirá tratar o affinity das CPUs como se fossem servidores separados e não instancias no mesmo servidor. Interessante não!?
traceflag 8002, lets CPU mapped to an instance, but within the instance, the schedulers are not bound to a CPU
Bom, e para fechar um outro link falando um pouco mais sobre este trace flag…
FIX: Trace Flag -T8002 Treats an Affinity Mask Like a Process Affinity
http://support.microsoft.com/kb/818769
É isso aí pessoal….espero que os link sejam úteis para vocês..
Ao Roberto…thanks a lot !!
abraços
Nilton Pinheiro