Tanto quanto eu posso dizer, aumentar o limite máximo de memória do SQL Server não tem desvantagens, desde que haja memória suficiente para o sistema operacional e outros aplicativos.
Supondo que esse seja o caso, pode haver algum efeito prejudicial em aumentar o limite máximo de memória?
A única coisa que vi é este artigo da KB da Microsoft, que afirma:
Ao configurar a opção sp_configure de memória máxima do servidor no Microsoft SQL Server para um valor grande, você pode observar que uma determinada consulta pode ser executada lentamente. Mas se você diminuir o valor dessa opção, a mesma consulta poderá ser executada muito mais rapidamente.
mas só se aplica a versões antigas do SQL Server (<=2008).
De um modo geral, não, não há efeito prejudicial de maximizar a memória depois que o sistema operacional e outros aplicativos foram contabilizados. Isso é exemplificado pelas configurações padrão do SQL Server:
Configurações de memória do SQL Server (MSDN)
Embora essa configuração permita o redimensionamento dinâmico da memória que o SQL Server adquire, ela também mostra que não há um número "demais" (além das considerações de sistema operacional/aplicativo/versão). O SQL Server usará apenas a memória necessária, embora possa reservar mais com base nas configurações min/max. Dar a ele o máximo de acesso possível simplesmente dá a ele o espaço de que precisa para operar com eficiência.
Como Shanky mencionou, a partir de 2012, o SQL também usa essa configuração para pilhas de threads, alocações de CLR e muito mais , o que pode ter outros efeitos em cenários específicos quando se trata de otimização de memória.
Antes de começar, sou defensor da FIXING MAX SERVER MEMORY sempre
1. Abordarei primeiro as versões do SQL Server anteriores ao SQL Server 2012. Isso significa que, se você estiver usando SQL Server 2005, SQL Server 2008 e SQL Server 2008 R2 (não, não incluirei o SQL Server 2000)
Se você estiver usando a versão do SQL Server
before 2012e seu sistema operacional for o Windows Server 2008 R2updated with the latest Service Packe, claro, o sistema for um sistema dedicado para o mecanismo de banco de dados do SQL Server, então vocêmight not need to set max server memorypode permitir que o buffer pool do SQL Server cresça dinamicamente. Consulte este link para obter uma lista de problemas documentados de corte do conjunto de trabalho com várias edições do Windows Server.Se você estiver usando o SQL Server 2005 no Windows Server 2003, que não foi corrigido para o Service Pack mais recente, você pode ser vítima do corte do SQL Server pelo Windows. Este link descreve o bug.
Existem muitos drivers de terceiros aos quais o SQL Server não oferece suporte e que podem ser carregados no espaço de endereço do SQL Server. Se você estiver usando consulta de servidor vinculado, isso pode causar vazamentos de memória.
Considerando todos esses bugs e correções, a Microsoft recomenda limitar a memória do buffer pool do SQL Server (antes de 2012) para o valor ideal.
Uma razão muito clássica pela qual você não deve permitir que o SQL Server aumente a memória dinamicamente:
Você pode ter várias instâncias. E se alguma consulta não autorizada começar a ser executada em uma instância e começar a consumir muita memória? Todos os outros aplicativos/instâncias sofreriam por causa disso, o que tenho certeza de que você não gostaria.
Considere um cluster de várias instâncias de dois nós que passa por um failover e agora ambas as instâncias estão no mesmo nó e uma começa a consumir mais memória do que a outra. O outro teria que sofrer e a instância do cluster se tornaria extremamente lenta
Se você não corrigir a memória máxima do servidor, poderá ser vítima do corte do conjunto de trabalho e do sistema operacional deixar de responder.
Considere um cenário em que você está executando o SQL Server Enterprise, com o SQL Server sendo executado com a conta Local System ou com uma conta que tenha o privilégio Locked Pages in Memory. Você está condenado se alguma consulta pesada for executada no sistema quando já estiver sob carga. Ele tentaria consumir toda a memória e, como o LPIM não pode ser paginado, o SQL Server tentaria cortar conforme o SQLOS solicitaria. Mas se a pressão de memória for grave, pode levar algum tempo para o SQL Server reagir e, nesse caso, os processos do sistema operacional podem ser paginados e eventualmente travar, levando a uma reinicialização do sistema operacional.
2. A partir do SQL Server 2012
O código de memória foi alterado para que um único gerenciador de memória seja usado para alocar memória para ambos os processos que requerem páginas de memória menores que 8 KB e para processos que requerem mais de 8 KB. A memória máxima do servidor controla a alocação de memória do SQL Server, incluindo buffer pool, memória de compilação, todos os caches, concessões de memória qe, memória do gerenciador de bloqueio e memória CLR (basicamente qualquer funcionário encontrado em
dm_os_memory_clerks). A memória para pilhas de threads, heaps, provedores de servidor vinculados diferentes do SQL Server ou qualquer memória alocada por uma DLL não SQL Server não é controlada pela memória máxima do servidor.Então, novamente, como eu disse, todos os processos carregados no espaço de endereço do SQL Server que não são monitorados pelos funcionários da memória ainda levariam a memória para fora da memória máxima do servidor. Portanto, novamente, você deve, como prática recomendada, definir o valor ideal para a memória máxima do servidor.