Recentemente, convertemos nosso sistema ERP de IBM Universe para SQL Server. O desempenho do aplicativo é geralmente tolerável, mas ocasionalmente degrada para terrível.
Estamos executando o banco de dados no Win Server 2012 em VMWare com 32 Gb de RAM e SQL Server 2012. SQL Max Memory é definido em 27 Gb. O servidor db está hospedando apenas este banco de dados e não está executando nenhuma outra função. O tamanho total do banco de dados é de aproximadamente 110 Gb. O aplicativo possui seu próprio servidor dedicado.
O fornecedor usou extensivamente CLRs para portar o código (mais de 36.000 funções escalares). Entendo que os CLRs individuais são executados corretamente em um contexto de OLTP de aplicativo, mas não aumentam bem ao tentar executar trabalhos em massa devido a operações linha por linha, em vez de definir operações baseadas. bem ... legal ... seguindo em frente.
Eu executei os scripts de Brent Ozar que identificaram alta memória livre como algo a ser observado, bem como um grande número de planos de execução para cada consulta. O fornecedor sugeriu adicionar mais RAM ao servidor, mas isso me incomoda, pois parece que o aplicativo não está usando o que tem agora.
O que me interessa é o desempenho e o comportamento geral do SQL. Vejo uma série de sintomas que sugerem que algo não está certo, mas não consigo identificar. É como se o servidor se recusasse a rodar . Está determinado a caminhar .
Grosso modo, parece-me que cerca de 10 Gb da memória está sendo usado pelo banco de dados para cache, cerca de 11 gb é gratuito, cerca de 3,5 Gb para cache de plano e não consigo explicar o resto. E estou um pouco inseguro sobre algumas das definições, como grátis x reservado x roubado, etc. Eles contam duas vezes?
O monitor de atividade mostra isso:
Quando executo esta consulta:
-- what's happening inside my buffer pool?
SELECT counter_name, instance_name, mb = cntr_value/1024.0
FROM sys.dm_os_performance_counters
WHERE (counter_name = N'Cursor memory usage' and instance_name <> N'_Total')
OR (instance_name = N'' AND counter_name IN
(N'Connection Memory (KB)', N'Granted Workspace Memory (KB)',
N'Lock Memory (KB)', N'Optimizer Memory (KB)', N'Stolen Server Memory (KB)',
N'Log Pool Memory (KB)', N'Free Memory (KB)')
) ORDER BY mb DESC;
Eu recebo:
+--------------------------------+---------------------+----------+
| Counter_name | instance_name | mb |
+--------------------------------+---------------------+----------+
| Free Memory (KB) | | 11,732 |
| Stolen Server Memory (KB) | | 5,426 |
| Lock Memory (KB) | | 59 |
| Log Pool Memory (KB) | | 4 |
| Optimizer Memory (KB) | | 2 |
| Connection Memory (KB) | | 2 |
| Cursor memory usage | TSQL Global Cursor | 1 |
| Cursor memory usage | TSQL Local Cursor | 0 |
| Cursor memory usage | API Cursor | - |
| Granted Workspace Memory (KB) | | - |
+--------------------------------+---------------------+----------+
Quando executo esta consulta:
-- which db's are using memory and how much.
SELECT
(CASE WHEN ([database_id] = 32767)
THEN N'Resource Database'
ELSE DB_NAME ([database_id]) END) AS [DatabaseName],
COUNT (*) * 8 / 1024 AS [MBUsed],
SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id];
Eu recebo:
+-------------------+----------+---------+
| DatabaseName | MBUsed | MBEmpty |
+-------------------+----------+---------+
| ERP | 10,764 | 626 |
| master | 2 | - |
| model | - | - |
| msdb | 11 | 3 |
| Resource Database | 16 | 5 |
| tempdb | 41 | 13 |
+-------------------+----------+---------+
Quando executo esta consulta:
SELECT TOP (12) Type, Name, pages_kb,
Virtual_Memory_reserved_kb, Virtual_Memory_committed_kb
FROM sys.dm_os_memory_clerks
ORDER BY pages_kb DESC;
Eu recebo:
+---------------------------+-----------------------+----------+----------------------------+-----------------------------+
| Type | Name | pages_kb | Virtual_Memory_reserved_kb | Virtual_Memory_committed_kb |
+---------------------------+-----------------------+----------+----------------------------+-----------------------------+
| MEMORYCLERK_SQLBUFFERPOOL | Default | 11224968 | 12999744 | 640296 |
| CACHESTORE_SQLCP | SQL Plans | 3519552 | 0 | 0 |
| CACHESTORE_CLRPROC | ClrProcCache | 110232 | 0 | 0 |
| CACHESTORE_OBJCP | Object Plans | 100776 | 0 | 0 |
| USERSTORE_DBMETADATA | ERP_Live | 93856 | 0 | 0 |
| USERSTORE_SCHEMAMGR | SchemaMgr Store | 87544 | 0 | 0 |
| CACHESTORE_PHDR | Bound Trees | 73464 | 0 | 0 |
| MEMORYCLERK_SOSNODE | SOS_Node | 62456 | 0 | 0 |
| OBJECTSTORE_LOCK_MANAGER | Lock Manager : Node 0 | 60792 | 131072 | 131072 |
| MEMORYCLERK_SQLCLR | Default | 40992 | 6327292 | 429408 |
| MEMORYCLERK_SQLSTORENG | Default | 28472 | 9472 | 9472 |
| MEMORYCLERK_SQLQUERYEXEC | Default | 20904 | 0 | 0 |
+---------------------------+-----------------------+----------+----------------------------+-----------------------------+
Parece que tenho 11 Gb de "memória livre". Isso é genuinamente gratuito para ser usado? Por que o SQL não está fazendo uso dele?
Parece-me que meu sistema ERP está usando apenas cerca de 10 Gb ou cerca de 1/3 da memória disponível. (o que parece errado .) Como posso encorajar meu aplicativo a usar a memória de forma mais eficaz
MEMORYCLERK_SQLCLR reservou 6,03 Gb de memória. Esse comportamento é normal para CLRs? Quando eles reservam memória? Quando são compilados/ registrados/ executados? Eles nunca lançam isso? Isso está dentro da 'memória livre'? (respondido por Srutzky)
Re: Alto número de planos de execução irá ajudar a limpar o cache ?
Há algum recurso que eu possa usar para influenciar o comportamento acima? Ou devo apenas aceitar que é assim que o aplicativo funciona.
Como faço para contabilizar o que realmente está segurando ou usando memória no meu servidor.
Consultas solicitadas por outros
Esses:
SELECT type,
SUM(pages_kb)/1024 AS [Memory utilized in MB],
SUM(awe_allocated_kb)/1024 AS [Memory allocated though Windows API]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY [Memory utilized in MB] DESC;
SELECT * FROM sys.dm_os_process_memory;
Retornar:
+----------------------------------+-----------------------+-------------------------------------+
| type | Memory utilized in MB | Memory allocated though Windows API |
+----------------------------------+-----------------------+-------------------------------------+
| MEMORYCLERK_SQLBUFFERPOOL | 4417 | 0 |
| CACHESTORE_SQLCP | 3437 | 0 |
| CACHESTORE_CLRPROC | 120 | 0 |
| USERSTORE_DBMETADATA | 100 | 0 |
| CACHESTORE_OBJCP | 99 | 0 |
| USERSTORE_SCHEMAMGR | 76 | 0 |
| CACHESTORE_PHDR | 72 | 0 |
| MEMORYCLERK_SOSNODE | 64 | 0 |
| OBJECTSTORE_LOCK_MANAGER | 59 | 0 |
| MEMORYCLERK_SQLCLR | 38 | 0 |
| MEMORYCLERK_SQLSTORENG | 26 | 0 |
| MEMORYCLERK_SQLQUERYEXEC | 14 | 0 |
| MEMORYCLERK_SQLGENERAL | 10 | 0 |
| OBJECTSTORE_SNI_PACKET | 9 | 0 |
| CACHESTORE_SYSTEMROWSET | 8 | 0 |
| USERSTORE_TOKENPERM | 7 | 0 |
| MEMORYCLERK_XE | 6 | 0 |
| MEMORYCLERK_SQLLOGPOOL | 4 | 0 |
| CACHESTORE_SEHOBTCOLUMNATTRIBUTE | 3 | 0 |
| MEMORYCLERK_SQLOPTIMIZER | 2 | 0 |
| MEMORYCLERK_SQLQERESERVATIONS | 2 | 0 |
| MEMORYCLERK_SQLCONNECTIONPOOL | 1 | 0 |
| OBJECTSTORE_LBSS | 1 | 0 |
| CACHESTORE_STACKFRAMES | 0 | 0 |
| MEMORYCLERK_SQLHTTP | 0 | 0 |
+----------------------------------+-----------------------+-------------------------------------+
+---------------------------+---------------------------+----------------------------+--------------------------------+-----------------------------------+------------------------------------+------------------------------------+------------------+-------------------------------+---------------------------+-----------------------------+----------------------------+
| physical_memory_in_use_kb | large_page_allocations_kb | locked_page_allocations_kb | total_virtual_address_space_kb | virtual_address_space_reserved_kb | virtual_address_space_committed_kb | virtual_address_space_available_kb | page_fault_count | memory_utilization_percentage | available_commit_limit_kb | process_physical_memory_low | process_virtual_memory_low |
+---------------------------+---------------------------+----------------------------+--------------------------------+-----------------------------------+------------------------------------+------------------------------------+------------------+-------------------------------+---------------------------+-----------------------------+----------------------------+
| 28571952 | 0 | 0 | 137438953344 | 77358808 | 28786620 | 137361594536 | 1014012259 | 99 | 3734268 | 0 | 0 |
+---------------------------+---------------------------+----------------------------+--------------------------------+-----------------------------------+------------------------------------+------------------------------------+------------------+-------------------------------+---------------------------+-----------------------------+----------------------------+
Este:
SELECT COUNT(*) AS [NumCachedObjects],
CONVERT(BIGINT, SUM(CONVERT(BIGINT, size_in_bytes)) / 1024.0) AS [CachedKBytes],
ISNULL(cacheobjtype, '<-- Totally Total') AS [CacheObjType],
ISNULL(objtype, '<-- TOTAL') AS [bytes]
FROM sys.dm_exec_cached_plans
GROUP BY cacheobjtype, objtype WITH ROLLUP;
Retorna:
+------------------+--------------+-------------------+-----------+
| NumCachedObjects | CachedKBytes | CacheObjType | bytes |
+------------------+--------------+-------------------+-----------+
| 3882 | 62112 | CLR Compiled Func | Proc |
| 3882 | 62112 | CLR Compiled Func | <-- TOTAL |
| 3 | 24 | CLR Compiled Proc | Proc |
| 3 | 24 | CLR Compiled Proc | <-- TOTAL |
| 50 | 4168 | Compiled Plan | Adhoc |
| 26911 | 3416232 | Compiled Plan | Prepared |
| 101 | 99584 | Compiled Plan | Proc |
| 5 | 1656 | Compiled Plan | Trigger |
| 27067 | 3521640 | Compiled Plan | <-- TOTAL |
| 17 | 136 | Extended Proc | Proc |
| 17 | 136 | Extended Proc | <-- TOTAL |
| 16 | 536 | Parse Tree | Check |
| 3 | 24 | Parse Tree | Default |
| 313 | 20632 | Parse Tree | UsrTab |
| 535 | 52520 | Parse Tree | View |
| 867 | 73712 | Parse Tree | <-- TOTAL |
| 31836 | 3657624 | <-- Totally Total | <-- TOTAL |
+------------------+--------------+-------------------+-----------+
Este:
SELECT * FROM sys.dm_clr_appdomains;
Retorna:
+--------------------+--------------+------------------------------------------+-------------------------+-------+---------+--------------------+-----------------+---------------+-----------+----------+---------------------+-------------------------+---------------------------+--------------------+
| appdomain_address | appdomain_id | appdomain_name | creation_time | db_id | user_id | state | strong_refcount | weak_refcount | cost | value | compatibility_level | total_processor_time_ms | total_allocated_memory_kb | survived_memory_kb |
+--------------------+--------------+------------------------------------------+-------------------------+-------+---------+--------------------+-----------------+---------------+-----------+----------+---------------------+-------------------------+---------------------------+--------------------+
| 0x00000003DECEC200 | 16 | ERP .CLRExtensionUser[runtime].111 | 2016-07-13 10:51:23.370 | 5 | 5 | E_APPDOMAIN_SHARED | 1 | 3236 | 130810392 | 11534336 | 110 | 15 | 112020591 | 206 |
+--------------------+--------------+------------------------------------------+-------------------------+-------+---------+--------------------+-----------------+---------------+-----------+----------+---------------------+-------------------------+---------------------------+--------------------+
Enquanto espero ouvir algumas perguntas que postei em um comentário sobre a pergunta, vou pelo menos reiterar uma de minhas perguntas: "O que sobre suas estatísticas atuais o leva a suspeitar que o uso do SQLCLR está de alguma forma relacionado a problemas de desempenho ?"
Pelo que vejo da sua saída, o SQLCLR está ocupando muito pouca memória. Tem 110 MB de RAM física para o
ClrProcCache. OK. Isso é apenas um pouco mais do que está sendo ocupado porObject Plans, e é uma fração dos 3,36 GB ocupados porSQL Plans. Sim,MEMORYCLERK_SQLCLRreservou cerca de 6,03 GB (não 6,3 -- é necessário obter valoreskbe aplicarvalue.0 / 1024 / 1024), mas isso é a) memória virtual, não física eb) pouco menos da metade dos 12,40 GB de memória virtual reservada pelo Buffer Pool . Se você rolar até oVirtual_Memory_committed_kbcampo, verá queMEMORYCLERK_SQLCLRestá usando ativamente apenas 419,34 MB de memória virtual .Para verificar o uso atual da memória SQLCLR, você deve ser capaz de executar:
e observe o
survived_memory_kbcampo ( não ototal_allocated_memory_kbcampo, pois devem ser alocações cumulativas, independentemente do que foi liberado).Para tentar responder às suas três perguntas:
Por que você suspeita que alguma coisa o está "segurando"? Você deu ao SQL Server 27 GB de RAM física para usar. Ele usará o que quiser, quando quiser.
Acho que esse valor é um erro de cálculo. Você afirmou que, embora o servidor tenha 32 GB de RAM física, você alocou apenas 27 GB para o SQL Server. Se 10 GB fosse o total real, isso equivaleria a cerca de 37%. Mas esse não é o total real. Se você observar o
pages_kbcampo de sua consulta final (contrasys.dm_os_memory_clerks), precisará somar todas essas linhas, o que resultará em: 15.424.008 kb. EntãoSELECT 15424008.0 / 1024 / 1024;, obtemos 14,71 GB de RAM sendo usados, de 27 GB. Se reduzirmos os 27 GB de RAM fornecidos ao SQL Server pelos 11,46 GB de memória "livre", restam 15,54 GB que deveriam ser "usados". Vemos que 14,71 GB estão sendo usados, mas isso se baseia em fazer umaTOP (12)consulta para obter a quantidade de memória usada. Suspeito que a diferença de 0,83 GB esteja oculta nas linhas filtradas e, portanto, remover oTOP (12)nos daria um número muito mais próximo de 15,54 GB. E nesse caso, a quantidade de memória "usada" é de aproximadamente 58% da RAM física permitida.Não exatamente. Há 6,03 GB de memória virtual reservada, não RAM física. Além disso, conforme declarado acima, isso é memória virtual reservada , não confirmada .
Não tenho certeza sobre "normal", mas certamente vi o SQLCLR preferir a memória virtual para armazenar grandes coleções.
O que você está vendo deve ser a memória de tempo de execução. Como é reservado , suponho que em determinado momento uma operação precisava de tanta memória e, portanto, o tamanho reservado cresceu para acomodá-la. Mas sua consulta também mostra que, atualmente, apenas 419,34 MB desses 6,03 GB estão sendo usados.
No mínimo, após a reinicialização do serviço. Mas possivelmente antes disso. Eu o vi segurando o espaço reservado por um longo tempo, mas não gasto muito tempo verificando se/quando ele será liberado.
Se você estiver preocupado com o fato de a coleta de lixo não estar em execução ou não com a frequência desejada, chame-a manualmente criando um Assembly simples que contém uma única função que chama a
GCclasse. Se você carregá-lo no mesmo banco de dados que o(s) outro(s) Assembly(s) e certificar-se de que ele tem o mesmo proprietário (ou seja, aAUTHORIZATIONcláusula deCREATE ASSEMBLY; verifique viaSELECT * FROM sys.assemblies;e certifique-se de queprincipal_idcorresponda), ele usará o mesmo AppDomain.Não. A memória "livre" refere-se à quantidade de RAM física não utilizada que o SQL Server pode usar por meio de "Max Server Memory". Os 6,03 GB de memória virtual reservada estão em um swapfile/pagefile.
Bem, como você vai fazer isso, exatamente? Se você quer dizer executando
DBCC FREESYSTEMCACHE('ALL');, ele deve descarregar TODOS os AppDomains, embora eu não tenha certeza se a memória virtual sempre é liberada. Não vejo nenhum mal em tentar, pelo menos uma vez, para ver o efeito real. Eu certamente não faria disso um hábito, já que o sistema incorrerá no custo inicial de recriar o AppDomain, carregar o Assembly (ou Assemblies) e tudo o mais que ele armazenar no arquivoClrProcCache.Não que eu saiba. E eu não acho que você gostaria que o SQL Server usasse toda a memória disponível, pois isso não deixaria nada para o processamento da consulta.
Não acho que você precise, nem deva, aceitar que a lentidão é assim mesmo. Como você disse, você substituiu vários UDFs SQLCLR por T-SQL puro e obteve grandes melhorias. Isso me diz que eles estão usando o SQLCLR de maneira incorreta e inadequada. E se eles encontraram ou criaram uma ferramenta para gerar esses UDFs (de que outra forma você obteria 36.000 deles!?!), então é duvidoso que sejam "ótimos", mesmo considerados individualmente.
Você pode ver isso usando o Gerenciador de tarefas (guia "Detalhes") e o Monitor de recursos (guia "Memória"). Procure uma coluna chamada "Conjunto de trabalho", que é a quantidade de RAM física usada, compartilhada e não compartilhada/privada.
If SQLCLR is suspected of performance issues due to seeing CLR-related wait types in Activity Monitor, please see my following DBA.StackExchange answer related to that: What is the SQLCLR wait type in Activity Monitor within SQL Server Management Studio (SSMS)?
Regarding the following comment in the Question:
I don't think this is a very accurate understanding ;-). The scalability issue for UDFs is not unique to SQLCLR. And in fact, SQLCLR scalar functions can do something that T-SQL UDFs cannot: participate in a parallel plan (if
IsDeterministicis set totrue); T-SQL UDFs force a serial plan. Still, for most operations that can be done in T-SQL, doing the operation inline (not abstracted in a UDF or Multistatement TVF -- T-SQL Inline TVF are fine) performs best.Looking at the number of cached plans and what types are using what, we can see that the majority of the cached plans are "prepared" -- almost 27,000 of them -- which indicates that they are likely using an ORM (e.g. Entity Framework, Hibernate / nHiberate, etc). That does seem like a high number, but there is not much you can do about it as it is the "nature of the beast" when it comes to ORMs (a significant drawback that developers rarely ever see, but hey, you can by more RAM, right?). We also see that there are almost 4000 SQLCLR UDFs (I wonder if any of the 36k UDFs are "dead code"?). These are areas that can be improved (by the vendor, not by you, unfortunately), but do not indicate any problems with memory.
Looking at the output of
sys.dm_clr_appdomainswe can see that the AppDomain, which I believe had only been created a few hours or so prior to getting that output, has used very little CPU, but has allocated (in one way or another) a cumulative total of 112,020,591 bytes (106.83 MB). However, only 206 Kb are still allocated, so those SQLCLR objects are not holding onto their memory.Approximately 60 "AppDomain Unloaded due to memory pressure" messages in 6 months is not perfect, but it's also quite far from bad. That's an average of 1 every 3 days. Low memory would cause this to happen many time per day. And it makes sense to happen during heavy activity periods when that memory is needed for run-time query processing. And that brings me back to the idea of the 10 GB of "free memory" only being "free" when not doing the ETL (or other times of increased activity).
You might get a clearer picture of this situation by running some of your tests during ETL time to see how much free memory is there, how much total KB is allocated to cached_objects, and how many cached plans per type there are. IN FACT, running these tests when the performance "degrades to terrible" would give the best indication of possibly running low on memory.
Currently I see no evidence that indicates this is an issue of memory constraints. It more so appears to be poor application architecture and misuse of a feature (i.e. SQLCLR). It could very well be that due to not understanding SQL Server and SQLCLR better, the vendor has done several things that nobody in their right mind would do (e.g. 36k scalar functions!).
That suggestion annoys me as well, but for a different reason: they are asking you to spend money on an absolute guess. They have no idea if more memory will help. If you have even half of your current 10 GB of free memory, and "repeated experience of queries taking minutes with CLRs reduced to a few seconds or even subsecond response times without them", then how could memory be the problem? If I am incorrect here, maybe they can provide you with queries and/or evidence to support the theory that this is RAM-related. But you already have pretty strong counter-evidence in the form of re-written queries taking "a few seconds or even subsecond response times without them". So, maybe the vendor should stand by their suggestion and purchase the RAM for you. If it doesn't help, you can hand it back to them. And if it does help, then you can work out whether you reimburse them fully for it, or split it 50/50 since perhaps you really não deveria precisar disso ;-).