GrzegorzO's questions

Alguém conseguiu importar o módulo dbatools para a conta de automação do Azure? Eu tentei fazer isso de várias maneiras, mas toda vez que a instalação falha:

• diferentes versões verificadas de 0.9.* até a mais recente 1.0.9
• do portal usando Contas de Automação -> Módulos de Automação -> Navegar na Galeria
• do portal usando contas de automação -> módulos de automação -> importar do arquivo

Toda vez que eu tenho:

Erro ao importar o módulo dbatools. Falha na importação com o seguinte erro: Orchestrator.Shared.AsyncModuleImport.ModuleImportException: Ocorreu um erro durante a validação do módulo. Ao importar o módulo para uma sessão interna do PowerShell, ele não pôde ser carregado pelo PowerShell. Provavelmente há um problema com o conteúdo do módulo que faz com que o PowerShell não consiga carregá-lo. Verifique se o módulo importa com êxito em uma sessão local do PowerShell, corrija quaisquer problemas e tente importar novamente.

Como você pode ver, a mensagem de erro não diz muito (e obviamente eu posso importá-la localmente), então eu me pergunto se alguém conseguiu passar por cima de alguma forma.

Recentemente, encontrei na sp_Blitzsaída uma nova descoberta 'Eventos gerais' no grupo de descobertas 'Eventos DBCC' :

15278 DBCC events have taken place between Aug  8 2017 12:15AM and Aug  8 2017  8:17AM. This does not include CHECKDB and other usually benign DBCC events.

Quando mergulhei nisso e verifiquei o rastreamento padrão, descobri que a cada minuto ele registra entradas como esta:

dbcc incrementinstance ("SQL Replication Distribution", "Dist:Delivered Cmds/sec", @agent_name, @new_delivered_commands)
dbcc setinstance ("SQL Replication Distribution", "Dist:Delivery Latency", @agent_name, @delivery_latency)
dbcc addinstance ("SQL Replication Logreader", @agent_name)

É claramente visível que isso está relacionado à replicação, mas como esses comandos não estão documentados, não tenho certeza se eles devem aparecer no rastreamento. No entanto, baseando-se puramente na saída, ele apenas mede a quantidade de comandos por segundo mais a latência. Acho que isso não é nada prejudicial.

Então, se você pudesse verificar seus servidores onde você executa a replicação e confirmar se você tem essas entradas e a frequência é mais ou menos a mesma?

A questão separada é se é normal ver esses comandos (e acho que é) no rastreamento padrão, talvez não deva ser considerado como uma descoberta sp_Blitze deve ser agrupado com outros DBCCcomandos inofensivos já excluídos no sp_Blitz.

Usamos o Sentry One para monitorar o SQL Server, mas não acho que esteja monitorando a replicação. Acho que pode ser algo interno. Existem alguns alertas padrão definidos quando você implanta a replicação (SQL Agent -> Alertas, por exemplo: Aviso de replicação: latência da replicação transacional (Limiar: latência)) Baseia-se no log de erros do SQL Server, mas a entrada no log de erros precisa ser inicialmente desencadeado por algo.

Eu abri um problema no Github para cobrir isso.

A cada poucos minutos até mais ou menos uma hora, o cache do proc está sendo liberado (mas não totalmente!). Se eu executar:

SELECT count (*) FROM sys.dm_exec_cached_plans

...após a limpeza acontecer, o número de planos cai para algumas centenas e então aumenta gradualmente para mais ou menos 2.000, então a limpeza acontece novamente e assim por diante.

O servidor roda em VMWare, tem 128 GB de RAM (SQL Server max server memory é definido como 102 GB , min. server memory é definido como 72 GB ). Com base na saída do SentryOne, posso ver que o pool de buffers consome ~61 GB . Não vejo nenhum indicador de pressão de memória.

Também observei que, quando reduzi a memória mínima do servidor para 16 GB, a frequência de limpeza do cache do proc aumentou drasticamente.

Minha versão do SQL Server é a seguinte:

Microsoft SQL Server 2016 (SP1-CU3) (KB4019916) - 13.0.4435.0 (X64)

O que mais:

• Ainda não experimentei o LPIM, pois requer que o SQL Server seja reiniciado, mas mesmo que seja a solução, gostaria muito de entender por que esse problema ocorre. Além disso, Brent sugeriu que ajuda apenas no caso de aparar o buffer pool. No meu caso, parece que o pool de buffers permanece intacto e apenas o cache do proc está sendo afetado.
• Não encontrei nenhum trabalho que possa ser responsável por isso, nem posso encontrar nenhum padrão real.
• Posso obter acesso ao vCenter se alguém achar isso útil em termos de correção/investigação desse problema. Mas se for esse o caso, por favor, você poderia me informar o que devo verificar lá.

Suspeito que talvez o VMWare desempenhe algum papel aqui, mas quando verifiquei os contadores de perfmon disponíveis no lado do convidado, não encontrei nada suspeito.

Também coletei a saída do sp_Blitz conforme a sugestão de Brent :

**Priority 10: Performance**:

- Query Store Disabled - The new SQL Server 2016 Query Store feature has not been enabled on this database.

    * Database1

    * Database2

    * distribution

    * Database3

    * Database3Integration

    * Database3IntegrationSandBox

    * Database4

    * Database5

    * Database6

    * Database6Sandbox

    * Database7

    * Database8

    * Database9


**Priority 50: Performance**:

- Poison Wait Detected: Serializable Locking  - 0:04:49:23 of LCK_M_R% waits have been recorded. This wait often indicates killer performance problems.


**Priority 50: Reliability**:

- Transaction Log Larger than Data File
    * Database2 - The database [Database2] has a 1 GB transaction log file, larger than the total data file sizes. This may indicate that transaction log backups are not being performed or not performed often enough.

    * Database4 - The database [Database4] has a 1 GB transaction log file, larger than the total data file sizes. This may indicate that transaction log backups are not being performed or not performed often enough.

    * Database8 - The database [Database8] has a 1 GB transaction log file, larger than the total data file sizes. This may indicate that transaction log backups are not being performed or not performed often enough.

    * Database9 - The database [Database9] has a 1 GB transaction log file, larger than the total data file sizes. This may indicate that transaction log backups are not being performed or not performed often enough.


**Priority 100: Performance**:

- Fill Factor Changed Database3 - The [Database3] database has objects with fill factor < 80%. This can cause memory and storage performance problems, but may also prevent page splits.

- Many Plans for One Query  - 180 plans are present for a single query in the plan cache - meaning we probably have parameterization issues.

- Stored Procedure WITH RECOMPILE distribution - [distribution].[dbo].[sp_MSdelete_dodelete] has WITH RECOMPILE in the stored procedure code, which may cause increased CPU usage due to constant recompiles of the code.


**Priority 110: Performance**:

- Active Tables Without Clustered Indexes
    * Database3 - The [Database3] database has heaps - tables without a clustered index - that are being actively queried.

    * Database3Integration - The [Database3Integration] database has heaps - tables without a clustered index - that are being actively queried.

    * Database3IntegrationSandBox - The [Database3IntegrationSandBox] database has heaps - tables without a clustered index - that are being actively queried.

    * msdb - The [msdb] database has heaps - tables without a clustered index - that are being actively queried.

    * Database6 - The [Database6] database has heaps - tables without a clustered index - that are being actively queried.

    * Database6Sandbox - The [Database6Sandbox] database has heaps - tables without a clustered index - that are being actively queried.

    * Database9 - The [Database9] database has heaps - tables without a clustered index - that are being actively queried.


**Priority 150: Performance**:

- Deadlocks Happening Daily  - 116 deadlocks have been recorded since startup.

- Foreign Keys Not Trusted
    * Database3 - The [Database3] database has foreign keys that were probably disabled, data was changed, and then the key was enabled again.  Simply enabling the key is not enough for the optimizer to use this key - we have to alter the table using the WITH CHECK CHECK CONSTRAINT parameter.

    * Database6 - The [Database6] database has foreign keys that were probably disabled, data was changed, and then the key was enabled again.  Simply enabling the key is not enough for the optimizer to use this key - we have to alter the table using the WITH CHECK CHECK CONSTRAINT parameter.

- Inactive Tables Without Clustered Indexes
    * distribution - The [distribution] database has heaps - tables without a clustered index - that have not been queried since the last restart.  These may be backup tables carelessly left behind.

    * Database3 - The [Database3] database has heaps - tables without a clustered index - that have not been queried since the last restart.  These may be backup tables carelessly left behind.

    * msdb - The [msdb] database has heaps - tables without a clustered index - that have not been queried since the last restart.  These may be backup tables carelessly left behind.

- Queries Forcing Join Hints  - 1106 instances of join hinting have been recorded since restart.  This means queries are bossing the SQL Server optimizer around, and if they don't know what they're doing, this can cause more harm than good.  This can also explain why DBA tuning efforts aren't working.

- Queries Forcing Order Hints  - 1106 instances of order hinting have been recorded since restart.  This means queries are bossing the SQL Server optimizer around, and if they don't know what they're doing, this can cause more harm than good.  This can also explain why DBA tuning efforts aren't working.

- Triggers on Tables
    * Database3 - The [Database3] database has 2 triggers.

    * Database9 - The [Database9] database has 2 triggers.


**Priority 170: Reliability**:

- Max File Size Set
    * Database3 - The [Database3] database file Database3Prod_log has a max file size set to 40960MB. If it runs out of space, the database will stop working even though there may be drive space available.

    * Database8 - The [Database8] database file db263426220 has a max file size set to 1024MB. If it runs out of space, the database will stop working even though there may be drive space available.

    * Database9 - The [Database9] database file Database9_log has a max file size set to 10000MB. If it runs out of space, the database will stop working even though there may be drive space available.


**Priority 200: Backup**:

- MSDB Backup History Not Purged msdb - Database backup history retained back to Feb 27 2017  3:48PM


**Priority 200: Informational**:

- Agent Jobs Starting Simultaneously  - Multiple SQL Server Agent jobs are configured to start simultaneously. For detailed schedule listings, see the query in the URL.

- Cluster Node  - This is a node in a cluster.

- Collation is SQL_Latin1_General_CP1_CI_AS - Collation differences between user databases and tempdb can cause conflicts especially when comparing string values

    * Database3Integration

    * Database3IntegrationSandBox

    * Database7

    * Database8

- Replication In Use
    * distribution - Database [distribution] is a replication publisher, subscriber, or distributor.

    * Database3 - Database [Database3] is a replication publisher, subscriber, or distributor.

- Tables in the MSDB Database
 - The SQLSentryAlertLog_20 table in the msdb database was created by end users on Mar 10 2017  4:40PM. Tables in the msdb database may not be restored in the event of a disaster.

 - The SQLSentryDBEmails_20 table in the msdb database was created by end users on Mar 10 2017  4:40PM. Tables in the msdb database may not be restored in the event of a disaster.

 - The SQLSentryDBEmails_Attachments_20 table in the msdb database was created by end users on Mar 10 2017  4:40PM. Tables in the msdb database may not be restored in the event of a disaster.

 - The SQLSentryEmails_20 table in the msdb database was created by end users on Mar 10 2017  4:40PM. Tables in the msdb database may not be restored in the event of a disaster.

 - The SQLSentryLogCache_20 table in the msdb database was created by end users on Mar 10 2017  4:40PM. Tables in the msdb database may not be restored in the event of a disaster.

 - The SQLSentryLogData_20 table in the msdb database was created by end users on Mar 10 2017  4:40PM. Tables in the msdb database may not be restored in the event of a disaster.

 - The SQLSentryObjectVersion_20 table in the msdb database was created by end users on Mar 10 2017  4:40PM. Tables in the msdb database may not be restored in the event of a disaster.

 - The SQLSentryQueueLog_20 table in the msdb database was created by end users on Mar 10 2017  4:40PM. Tables in the msdb database may not be restored in the event of a disaster.

- TraceFlag On  - Trace flag 3226 is enabled globally.


**Priority 200: Monitoring**:

- Agent Jobs Without Failure Emails
 - The job Agent history clean up: distribution has not been set up to notify an operator if it fails.

 - The job Database1_Job_DeleteExpiredSessions has not been set up to notify an operator if it fails.

 - The job CommandLog Cleanup has not been set up to notify an operator if it fails.

 - The job DatabaseBackup - SYSTEM_DATABASES - FULL has not been set up to notify an operator if it fails.

 - The job DatabaseBackup - USER_DATABASES - DIFF has not been set up to notify an operator if it fails.

 - The job DatabaseBackup - USER_DATABASES - FULL has not been set up to notify an operator if it fails.

 - The job DatabaseBackup - USER_DATABASES - LOG has not been set up to notify an operator if it fails.

 - The job DatabaseIntegrityCheck - SYSTEM_DATABASES has not been set up to notify an operator if it fails.

 - The job DatabaseIntegrityCheck - USER_DATABASES has not been set up to notify an operator if it fails.

 - The job Distribution clean up: distribution has not been set up to notify an operator if it fails.

 - The job Expired subscription clean up has not been set up to notify an operator if it fails.

 - The job IndexOptimize - USER_DATABASES has not been set up to notify an operator if it fails.

 - The job IntegrationAPI_SSIS_Integration has not been set up to notify an operator if it fails.

 - The job Output File Cleanup has not been set up to notify an operator if it fails.

 - The job Replication agents checkup has not been set up to notify an operator if it fails.

 - The job sp_cycle_errorlog has not been set up to notify an operator if it fails.


 - The job sp_delete_backuphistory has not been set up to notify an operator if it fails.

 - The job sp_purge_jobhistory has not been set up to notify an operator if it fails.

 - The job SSIS Server Maintenance Job has not been set up to notify an operator if it fails.

 - The job StatisticsUpdate - USER_DATABASES has not been set up to notify an operator if it fails.

- Alerts Disabled
 - The following Alert is disabled, please review and enable if desired: Peer-to-peer conflict detection alert

 - The following Alert is disabled, please review and enable if desired: Replication: agent custom shutdown

 - The following Alert is disabled, please review and enable if desired: Replication: agent retry

 - The following Alert is disabled, please review and enable if desired: Replication: agent success

 - The following Alert is disabled, please review and enable if desired: Replication: expired subscription dropped

 - The following Alert is disabled, please review and enable if desired: Replication: Subscriber has failed data validation

 - The following Alert is disabled, please review and enable if desired: Replication: Subscriber has passed data validation

 - The following Alert is disabled, please review and enable if desired: Replication: Subscription reinitialized after validation failure

- No Alerts for Corruption  - SQL Server Agent alerts do not exist for errors 823, 824, and 825.  These three errors can give you notification about early hardware failure. Enabling them can prevent you a lot of heartbreak.

- No Alerts for Sev 19-25  - SQL Server Agent alerts do not exist for severity levels 19 through 25.  These are some very severe SQL Server errors. Knowing that these are happening may let you recover from errors faster.

- No failsafe operator configured  - No failsafe operator is configured on this server.  This is a good idea just in-case there are issues with the [msdb] database that prevents alerting.

- Not All Alerts Configured  - Not all SQL Server Agent alerts have been configured.  This is a free, easy way to get notified of corruption, job failures, or major outages even before monitoring systems pick it up.


**Priority 200: Non-Default Server Config**:

- Agent XPs  - This sp_configure option has been changed.  Its default value is 0 and it has been set to 1.

- backup checksum default  - This sp_configure option has been changed.  Its default value is 0 and it has been set to 1.

- backup compression default  - This sp_configure option has been changed.  Its default value is 0 and it has been set to 1.

- clr enabled  - This sp_configure option has been changed.  Its default value is 0 and it has been set to 1.

- cost threshold for parallelism  - This sp_configure option has been changed.  Its default value is 5 and it has been set to 50.

- Database Mail XPs  - This sp_configure option has been changed.  Its default value is 0 and it has been set to 1.

- max degree of parallelism  - This sp_configure option has been changed.  Its default value is 0 and it has been set to 8.

- max server memory (MB)  - This sp_configure option has been changed.  Its default value is 2147483647 and it has been set to 106496.

- min server memory (MB)  - This sp_configure option has been changed.  Its default value is 0 and it has been set to 73728.

- optimize for ad hoc workloads  - This sp_configure option has been changed.  Its default value is 0 and it has been set to 1.

- remote admin connections  - This sp_configure option has been changed.  Its default value is 0 and it has been set to 1.

- scan for startup procs  - This sp_configure option has been changed.  Its default value is 0 and it has been set to 1.

- show advanced options  - This sp_configure option has been changed.  Its default value is 0 and it has been set to 1.


**Priority 200: Performance**:

- User-Created Statistics In Place
    * distribution - [distribution] has 10 user-created statistics. This indicates that someone is being a rocket scientist with the stats, and might actually be slowing things down, especially during stats updates.

    * Database3 - [Database3] has 7 user-created statistics. This indicates that someone is being a rocket scientist with the stats, and might actually be slowing things down, especially during stats updates.


**Priority 210: Non-Default Database Config**:

- ANSI NULL Default Enabled - This database setting is not the default.

    * Database3Integration

    * Database3IntegrationSandBox

- Read Committed Snapshot Isolation Enabled - This database setting is not the default.

    * Database6

    * Database6Sandbox


**Priority 240: Wait Stats**:

- 1 - CXPACKET  - 817.9 hours of waits, 259.3 minutes average wait time per hour, 0.7% signal wait, 45111405 waiting tasks, 65.3 ms average wait time.

- 2 - TRACEWRITE  - 188.8 hours of waits, 59.9 minutes average wait time per hour, 0.0% signal wait, 354542 waiting tasks, 1917.4 ms average wait time.


**Priority 250: Server Info**:

- Default Trace Contents  - The default trace holds 28 hours of data between Jul 31 2017  3:30AM and Aug  1 2017  7:55AM. The default trace files are located in: E:\MSSQL13.MSSQLSERVER\MSSQL\Log

- Drive C Space  - 35530.00MB free on C drive

- Drive E Space  - 129384.00MB free on E drive

- Drive F Space  - 135632.00MB free on F drive

- Drive G Space  - 13597.00MB free on G drive

- Drive H Space  - 102956.00MB free on H drive

- Drive I Space  - 4207.00MB free on I drive

- Drive Q Space  - 9619.00MB free on Q drive

- Drive R Space  - 255214.00MB free on R drive

- Hardware  - Logical processors: 8. Physical memory: 128GB.

- Hardware - NUMA Config  - Node: 0 State: ONLINE Online schedulers: 8 Offline schedulers: 0 Processor Group: 0 Memory node: 0 Memory VAS Reserved GB: 160

- Server Last Restart  - Jul 23 2017  8:07PM

- Server Name  - SecretServerName

- Services
 - Service: SQL Full-text Filter Daemon Launcher (MSSQLSERVER) runs under service account NT Service\MSSQLFDLauncher. Last startup time: not shown.. Startup type: Manual, currently Running.

 - Service: SQL Server (MSSQLSERVER) runs under service account SecretDomain\SQLSERVER. Last startup time: Jul 24 2017 10:39AM. Startup type: Manual, currently Running.

 - Service: SQL Server Agent (MSSQLSERVER) runs under service account SecretDomain\SQLAGENT. Last startup time: not shown.. Startup type: Manual, currently Running.

- SQL Server Last Restart  - Jul 24 2017 10:39AM

- SQL Server Service  - Version: 13.0.4435.0. Patch Level: SP1. Edition: Standard Edition (64-bit). AlwaysOn Enabled: 0. AlwaysOn Mgr Status: 2

- Virtual Server  - Type: (HYPERVISOR)

- Windows Version  - You're running a pretty modern version of Windows: Server 2012R2 era, version 6.3


**Priority 254: Rundate**:

 - Captain's log: stardate something and something...

Estas são as primeiras linhas da saída de:

SELECT *
FROM sys.dm_exec_query_stats WITH (NOLOCK)
ORDER BY creation_time ASC

Corrija-me se estiver errado, mas isso indica que algo aconteceu por volta das 15:38:48 e removeu os planos e, por algum motivo, não tocou no plano criado às 15:33. A partir das 15:38:48.117 os planos começaram a ser armazenados em cache novamente.

plan_handle creation_time   last_execution_time
0x0500070027F2492EC09D25603E00000001000000000000000000000000000000000000000000000000000000  2017-08-01 15:33:55.160 2017-08-01 15:59:18.223
0x05000700CD381A65E0AD58482900000001000000000000000000000000000000000000000000000000000000  2017-08-01 15:38:48.117 2017-08-01 15:59:50.737
0x05000700CD381A65E0AD58482900000001000000000000000000000000000000000000000000000000000000  2017-08-01 15:38:48.120 2017-08-01 15:59:50.737
0x0500070091273D3040B458482900000001000000000000000000000000000000000000000000000000000000  2017-08-01 15:38:48.120 2017-08-01 15:59:56.963
0x05000700CD381A65E0AD58482900000001000000000000000000000000000000000000000000000000000000  2017-08-01 15:38:48.127 2017-08-01 15:59:50.737
0x05000700AB256B35A0BC58482900000001000000000000000000000000000000000000000000000000000000  2017-08-01 15:38:48.133 2017-08-01 15:59:50.533
0x05000700B31CF10530BE58482900000001000000000000000000000000000000000000000000000000000000  2017-08-01 15:38:48.163 2017-08-01 15:59:58.687
0x0500070051C21425C0BE58482900000001000000000000000000000000000000000000000000000000000000  2017-08-01 15:38:48.167 2017-08-01 15:59:56.003
0x0500070055138E3B50BF58482900000001000000000000000000000000000000000000000000000000000000  2017-08-01 15:38:48.177 2017-08-01 15:59:55.017
0x05000700ED867A0F30FFB4B53200000001000000000000000000000000000000000000000000000000000000  2017-08-01 15:38:48.180 2017-08-01 15:59:56.643
0x050006005D05B621B0D1B6803C00000001000000000000000000000000000000000000000000000000000000  2017-08-01 15:38:48.183 2017-08-01 15:59:58.590
0x0500070036D29E2540D3B6803C00000001000000000000000000000000000000000000000000000000000000  2017-08-01 15:38:48.190 2017-08-01 15:59:44.230
0x050007008228A749A0DEB6803C00000001000000000000000000000000000000000000000000000000000000  2017-08-01 15:38:48.203 2017-08-01 15:59:58.620
0x050007002C6CA30830DFB6803C00000001000000000000000000000000000000000000000000000000000000  2017-08-01 15:38:48.207 2017-08-01 15:59:43.103
0x050007000F2BB316E06D90782E00000001000000000000000000000000000000000000000000000000000000  2017-08-01 15:38:48.230 2017-08-01 15:59:52.497
0x05000700022A9029706E90782E00000001000000000000000000000000000000000000000000000000000000  2017-08-01 15:38:48.237 2017-08-01 15:59:56.000
0x050007004B75B43FF0CEE1104200000001000000000000000000000000000000000000000000000000000000  2017-08-01 15:38:48.247 2017-08-01 15:59:50.907
0x050007002F3E632220DEE1104200000001000000000000000000000000000000000000000000000000000000  2017-08-01 15:38:48.260 2017-08-01 15:59:58.047
0x050007003EC92476307D90782E00000001000000000000000000000000000000000000000000000000000000  2017-08-01 15:38:48.263 2017-08-01 15:59:58.610
0x05000700119B6E78C07E90782E00000001000000000000000000000000000000000000000000000000000000  2017-08-01 15:38:48.283 2017-08-01 15:59:54.340
0x05000700B13E0511507F90782E00000001000000000000000000000000000000000000000000000000000000  2017-08-01 15:38:48.317 2017-08-01 15:59:50.640
0x05000700F888E46D30BFC37B3D00000001000000000000000000000000000000000000000000000000000000  2017-08-01 15:38:48.333 2017-08-01 15:59:50.903
0x050007006015F81980B9C37B3D00000001000000000000000000000000000000000000000000000000000000  2017-08-01 15:38:48.340 2017-08-01 15:59:44.077

e mais até 700+ linhas...

Atualização 02.08.2017

Finalmente me deparei com um artigo de Jonathan Kehayias e decidi verificar os ring buffers e boom!, acontece que tenho notificações de monitores de recursos dizendo: 'memória física baixa'. As ocorrências desta notificação se encaixam perfeitamente na limpeza do cache do proc. Agora o caso é como interpretar esses resultados e como encontrar o processo responsável. Como você pode encontrar no resultado da consulta:

SELECT 
    EventTime,
    record.value('(/Record/ResourceMonitor/Notification)[1]', 'varchar(max)') as [Type],
    record.value('(/Record/ResourceMonitor/IndicatorsProcess)[1]', 'int') as [IndicatorsProcess],
    record.value('(/Record/ResourceMonitor/IndicatorsSystem)[1]', 'int') as [IndicatorsSystem],
    record.value('(/Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS [Avail Phys Mem, Kb],
    record.value('(/Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS [Avail VAS, Kb]
FROM (
    SELECT
        DATEADD (ss, (-1 * ((cpu_ticks / CONVERT (float, ( cpu_ticks / ms_ticks ))) - [timestamp])/1000), GETDATE()) AS EventTime,
        CONVERT (xml, record) AS record
    FROM sys.dm_os_ring_buffers
    CROSS JOIN sys.dm_os_sys_info
    WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR') AS tab
ORDER BY EventTime DESC;

podemos observar o sinalizador de 'memória física baixa', embora o valor da memória física disponível permaneça no mesmo nível. Além disso, se não estou enganado, os resultados acima estão indicando pressão de memória interna (IndicatorsProcess = 2, o que é estranho para mim, pois o Sentry o tempo todo mostra que o SQL Server não utiliza totalmente a memória alocada. Este é o uso de memória capturado pelo Sentry para exemplo tirada às 8h:

Todas as linhas são bastante planas. O que também é estranho para mim é esse grupo de eventos:

• RESOURCE_MEMPHYSICAL_LOW
• RESOURCE_MEM_STEADY
• RESOURCE_MEMPHYSICAL_HIGH

acontece ao mesmo tempo. Portanto, essa pressão leva milissegundos ou menos (talvez essa seja também a razão pela qual o Sentry não captura nada, pois coleta dados com menos frequência).

Agora eu me pergunto como descobrir a causa raiz dessa pressão de memória?

Atenciosamente,

Greg