Description:

•  Amazon DocumentDB is a document database service that supports MongoDB workloads and provides
•  virtually unlimited storage with real-time scalability. Functionality and capabilities that enable Amazon
•  DocumentDB to deliver MongoDB level performance include:
•  Apache 2.0 open source MongoDB 3.6 API lets you use existing MongoDB drivers and tools with
•  Amazon DocumentDB to facilitate a smooth transition.
•  Decoupled storage and compute, allowing independent scaling to meet dynamic workload demands.
•  Ability to add as many as 15 low-latency read replicas to handle millions of requests per second.
•  Guaranteed 99.99% availability by replicating six copies of data across three AWS Availability
•  Zones.
•  Rapid (less than 30 seconds) automatic failover to a read replica in the event of failure. 

 

Advantages:

•  Amazon DocumentDB integrates deeply with AWS services, and when combined with these services,
•  offers advantages that provide compelling reasons to migrate from MongoDB to AWS DocumentDB:
•  Multiple levels of database security: network isolation using Amazon VPC, encryption at rest
•  via AWS Key Management Service (KMS), auditing, TLS for encryption-in-transit, encrypted
•  automated backups, snapshots, and replicas.
•  Automated monitoring and backups to Amazon S3 that allow point-in-time recovery.
•  Compliance with industry standards like PCI DSS, ISO 9001, 27001, 27017, and 27018, as well as SOC 1, 2, and SOC 3, and HIPAA.
•  AWS DocumentDB reduces operational overhead

 Migrating to Amazon DocumentDB

We can migrate data from any MongoDB database, either on-premises or in the cloud (e.g. a MongoDB database running on Amazon EC2), to Amazon DocumentDB. There are three primary approaches for migrating your data to Amazon DocumentDB.

 Offline

 Online

 Hybrid

 Offline migration:

The offline approach uses the mongodump and mongorestore tools to migrate data from source MongoDB deployment to Amazon DocumentDB cluster. The offline method is the simplest migration approach, but it also incurs the most downtime for your cluster.

 The basic process for offline migration is as follows:

 1. Quiesce writes to your MongoDB source.

 2. Dump collection data and indexes from the source MongoDB deployment.

 3. Restore indexes to the Amazon DocumentDB cluster.

 4. Restore collection data to the Amazon DocumentDB cluster.

 5. Change your application endpoint to write to the Amazon DocumentDB cluster.

 Online Migration:

For migration of production workloads with minimal downtime, we can use the online approach or the hybrid approach. With the online migration approach, use AWS Database Migration Service (DMS) to migrate the data from MongoDB to Amazon DocumentDB. DMS performs an initial full load of the data from the MongoDB source to Amazon DocumentDB. During the full load, source database is available for operations. Once the full load is completed, DMS switches to change data capture (CDC) mode to keep the source (MongoDB) and destination (Amazon DocumentDB) in sync. Once the databases are in sync,switch your applications to point to Amazon DocumentDB with near zero downtime.

Hybrid Approach:

The hybrid approach is a combination of the offline and online migration approaches. The hybrid approach is useful in a scenario where you need minimal downtime during migration, but the size of the source database is large or sufficient bandwidth is not available to migrate the data in a reasonable amount of time. The hybrid approach has two phases. In the first phase, you export the data from the source MongoDB using the mongodump tool, transfer it to AWS (if the source is on premises), and restore it to Amazon DocumentDB. You can use AWS Direct Connect or AWS Snowball to transfer the export dump to AWS. During this phase, the source (MongoDB) is available for operations and the data restored to Amazon DocumentDB does not contain the latest changes. In the second phase, you use DMS in CDC mode to copy the changes from the source (MongoDB) to Amazon DocumentDB and keep them in sync. Once the databases are in sync, you can switch your applications to point to Amazon DocumentDB with near zero downtime.

 

for Migration use below tools

https://github.com/awslabs/amazon-documentdb-tools

 1) Index exported:

    python migrationtools/documentdb_index_tool.py --host 127.0.0.1 --port 2700 --username sysAdmin --password sysadm1298 --auth-db admin --dump-indexes --dir C:\Junk\mongoindexes

 2) copy indexes to mongo instance on a backup folder using winscp.

 3) index imported: (Login into Mongodb node)

cd /imdb001/sw/mongo-3.4.17/bin

    ./mongorestore --dryRun  --ssl --host='docdb.cluster-ccr6zchg1pp7.us-east-1.docdb.amazonaws.com' --sslCAFile=rds-combined-ca-bundle.pem --username=docdbadmin --password='<password>' --db=qstaflsdb01 --port=27017  --numParallelCollections=4  --maintainInsertionOrder   --port=27017 /imdb001/data/backup/mongoindexes/qstaflsdb01

 4) Run mongo restore for data from mongodump: (noindexRestore)

 ./mongorestore --gzip --ssl --host='docdb.cluster.us-east-1.docdb.amazonaws.com' --sslCAFile=rds-combined-ca-bundle.pem --username=docdbadmin --password='<password>' --db=test  --port=27017  --numParallelCollections=8  --numInsertionWorkersPerCollection=4 --maintainInsertionOrder --noIndexRestore   --port=27017 /imdb001/data/backup/

5) table dump and restore

 ./mongodump  --gzip --db=test --port=27101 --username=sysAdmin --authenticationDatabase=admin --out=/imdb001/data/qausersroles --numParallelCollections=4 --dumpDbUsersAndRoles  --exclude CollectionsWithPrefix Account --exclude CollectionsWithPrefix Fin --excludeCollectionsWithPrefix TEST --excludeCollectionsWithPrefix Gring --excludeCollectionsWithPrefix fin --excludeCollectionsWithPrefix users --password=  --sslAllowInvalidHostnames

 

 ./mongorestore --gzip --ssl --host='docdb.cluster-ccr6zchg1pp7.us-east-1.docdb.amazonaws.com' --sslCAFile=rds-combined-ca-bundle.pem --username=docdbadmin --password='<password>' --db=test  --port=27017  --numParallelCollections=4  --numInsertionWorkersPerCollection=4 --maintainInsertionOrder --noIndexRestore --restoreDbUsersAndRoles --port=27017 /imdb001/data/qausersroles/

FUll BACKUP DUMP

mongo_dump = [root@ip-10.0.0.0 bin]# ./mongodump --host=hostname:27101  --username=sysAdmin --password=password  --out=/backupvolume

INDEX DUMP

[root@ip-10.0.0.0 tools]#  python3 documentdb_index_tool.py --host hostname:27101 --port 27107 --username sysAdmin --password passport  --auth-db admin --dump-indexes --dir /backupvolume/main_back/indexdump

INDEX RESTORE

./mongorestore --dryRun  --ssl --host=docdb.cluster.us-east-1.docdb.amazonaws.com  --sslCAFile=rds-combined-ca-bundle.pem --username=username admin --password='password' --db=test --port=27017  --numParallelCollections=8  --numInsertionWorkersPerCollection=8 --maintainInsertionOrder   --port=27017 /backupvolume/indexdump/

             How Vacuum and Analyze will work in Redshift

Since redshift is designed by Postgres most of the features and commands will work in redshift too.First In order to find any tables which is out of stats we need to find in a way how many dead tuples are there in a schema 

DEAD TUPLES :

If We have 10k records in a table,it will occupies 10k locations. if  We deleted 5k records Now 5k memory location should become free but by default it will not become free, it will just marked as record deleted, so these type of records we will call it as dead tuples,you can't reclaim space at the same time you can't reuse those memory locations.

VACUUM :

VACUUM operation will go remove and the dead tuples and make the location as ready for use, it will not reclaim any space to os.but location will be free, anyone can use.

ANALYZE:

It will Keep stats up to date to Generate Better execution Plan 

VACUUM FULL:

VACUUM will not reclaim any space, It will just removes dead tuples, now if you think data not distributed proper, huge % of bloats are there, so if you are seeing any performance impact, we will run this vacuum full to remove bloats and distribute data properly.

Query to find unsorting value to do Vacuum or analyze or vacuum full

--svv_table_info

SELECT "schema", table_id,

 "table",diststyle,sortkey1,size,tbl_rows,unsorted,stats_off,skew_sortkey1,skew_rows

FROM svv_table_info

WHERE "schema" IN ('Schemaname')

AND   (unsorted > 0 OR stats_off > 0)

ORDER BY stats_off DESC;

• By default, Redshift can skip the tables from vacuum Sort if the table is already at least 95 percent sorted. So we have to care about this if the tables have billions(or any huge numbers) of rows, then just 5% is a huge number of rows. So while running a vacuum, make you are defining the threshold percentage.

• Similarly, Analyze also will skip the tables from Analyzing if the out of stats is up to 10%. So we need run the following command to set this value to very low and the analyze will not skip any tables

How to find stats information

SELECT * FROM SVV_VACUUM_SUMMARY;

SELECT * FROM SVV_VACUUM_PROGRESS;

 

RedShift Workload management (WLM) 

Amazon Redshift workload management (WLM) enables flexible management priorities within workloads so that short, fast-running queries don't get stuck in queues behind long-running queries. Amazon Redshift creates query queues at runtime according to service classes, which define the configuration parameters for various types of queues, including internal system queues and user-accessible queues. From a user perspective, a user-accessible service class and a queue are functionally equivalent. For consistency, this documentation uses the term queue to mean a user-accessible service class as well as a runtime queue.

Q Priorities 

 

 

  Custom Rules

 

 

Add rule from template

 

DMV to Find the Ports that SQL Server is Listening On

Query

SELECT      e.name,
            e.endpoint_id,
            e.principal_id,
            e.protocol,
            e.protocol_desc,
            ec.local_net_address,
            ec.local_tcp_port,
            e.[type],
            e.type_desc,
            e.[state],
            e.state_desc,
            e.is_admin_endpoint
FROM        sys.endpoints e
            LEFT OUTER JOIN sys.dm_exec_connections ec
                ON ec.endpoint_id = e.endpoint_id
GROUP BY    e.name,
            e.endpoint_id,
            e.principal_id,
            e.protocol,
            e.protocol_desc,
            ec.local_net_address,
            ec.local_tcp_port,
            e.[type],
            e.type_desc,
            e.[state],
            e.state_desc,
            e.is_admin_endpoint 

Root Blocker Query in SQL Server

Root Blocker Query in SQL Server

SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF
GO
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH
INTO #T
FROM SYS.SYSPROCESSES R CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) T
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
AS
(
SELECT SPID,
BLOCKED,
CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
BATCH FROM #T R
WHERE (BLOCKED = 0 OR BLOCKED = SPID)
AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)
UNION ALL
SELECT R.SPID,
R.BLOCKED,
CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,
R.BATCH FROM #T AS R
INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
)
SELECT N' ' + REPLICATE (N'| ', LEN (LEVEL)/4 - 2) + CASE WHEN (LEN (LEVEL)/4 - 1) = 0 THEN 'HEAD - ' ELSE '|------ ' END + CAST (SPID AS NVARCHAR (10)) + ' ' + BATCH AS BLOCKING_TREE FROM BLOCKERS ORDER BY LEVEL ASC
GO
DROP TABLE #T

GO

Adding Artical in transaction replication with schema Name change at subscriber (Not required re-initializing of snapshot)

How to add Articles in transaction replication with out reinitializing snapshot

Step-1

Please run the below command and make sure values must be '0' for the database which is participating in Replication

select immediate_sync , allow_anonymous from syspublications

If You find the values are '1' please change the values from the below commands

EXEC sp_changepublication @publication = 'Publisher name', @property =
N'allow_anonymous', @value='true'
Go
EXEC sp_changepublication @publication = 'Publisher name', @property =
N'immediate_sync', @value='true'
Go

Step-2

Adding articles to Publisher , run the below script.

exec sp_addarticle @publication = N'Publisher name',
@article = N'Publisher Articles',
@source_owner = N'dbo',
@source_object = N'Source Publisher Table',
@type = N'logbased',
@description = N'',
@creation_script = N'',
@pre_creation_cmd = N'delete',
@schema_option = 0x000000000803509F,
@identityrangemanagementoption = N'none',
@destination_table = N'Destination Table',
@destination_owner = N'Schema Name',
@status = 24,
@vertical_partition = N'false',
@ins_cmd = N'CALL [dbo].[sp_MSins_12Name_insert_sp]',
@del_cmd = N'CALL [dbo].[sp_MSdel_12Name_delete_sp]',
@upd_cmd = N'SCALL [dbo].[sp_MSupd_12Name_Update_sp]',
@force_invalidate_snapshot=1

Note;

@destination_owner = N'Schema Name', --->change the schema name here for subscriber table.

There were three sp will use in replication for moving data from PUB to SUB so please adde some name to the below red coloured space for recognising sp's

   @ins_cmd = N'CALL [dbo].[sp_MSins_12Name_insert_sp]',
   @del_cmd = N'CALL [dbo].[sp_MSdel_12Name_delete_sp]',
   @upd_cmd = N'SCALL [dbo].[sp_MSupd_12Name_Update_sp]'

Step-3

Add the subscription to publisher which we have added before.

EXEC sp_addsubscription @publication = 'Publication Name',
           @subscription_type = N'push',
           @article = 'Articles name',
           @subscriber ='Subscriber server name', @destination_db = 'Subscriber Database Name',
           @reserved='Internal'


Step-4

select * from msdb..sysjobs where name like '%Publisher name%'


Run the job of snapshot which contain step-1 '‘Snapshot Agent startup message’

Make sure that you have see only added articles only snapshot will run.see the below screen shot.

From the above we have added 4 articles at a time.

when you launch the replication monitor,

First .sre and .sch ,.bcp scripts on subscriber end  then bulk inserts will inserts data from PUB to SUB find the screen shot.

Sql Server Information Part-1

Need to know all the information about Sql server


SELECT SERVERPROPERTY('MachineName') AS [MachineName], SERVERPROPERTY('ServerName') AS [ServerName],
SERVERPROPERTY('InstanceName') AS [Instance], SERVERPROPERTY('IsClustered') AS [IsClustered],
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS],
SERVERPROPERTY('Edition') AS [Edition], SERVERPROPERTY('ProductLevel') AS [ProductLevel],
SERVERPROPERTY('ProductVersion') AS [ProductVersion], SERVERPROPERTY('ProcessID') AS [ProcessID],
SERVERPROPERTY('Collation') AS [Collation], SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled],

SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly];


CPU  and RAM Information

SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count],
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)]

FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

Configuration Values of Sql Server

SELECT name, value, value_in_use, [description]
FROM sys.configurations WITH (NOLOCK)

ORDER BY name  OPTION (RECOMPILE);

Calculates average stalls per read, per write, and per total input/output for each database file

SELECT DB_NAME(fs.database_id) AS [Database Name], mf.physical_name, io_stall_read_ms, num_of_reads,
CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],io_stall_write_ms,
num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms],
io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes AS [total_io],
CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1))
AS [avg_io_stall_ms]
FROM sys.dm_io_virtual_file_stats(null,null) AS fs
INNER JOIN sys.master_files AS mf WITH (NOLOCK)
ON fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]

ORDER BY avg_io_stall_ms DESC OPTION (RECOMPILE);

-- Helps you determine which database files on the entire instance have the most I/O bottlenecks
-- This can help you decide whether certain LUNs are overloaded and whether you might
-- want to move some files to a different location

Get CPU utilization by database

WITH DB_CPU_Stats
AS
(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]
 FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
 CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
              FROM sys.dm_exec_plan_attributes(qs.plan_handle)
              WHERE attribute = N'dbid') AS F_DB
 GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
       DatabaseName, [CPU_Time_Ms],
       CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_Stats
WHERE DatabaseID > 4 -- system databases
AND DatabaseID <> 32767 -- ResourceDB

ORDER BY row_num OPTION (RECOMPILE);

Get total buffer usage by database for current instance

SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id > 4 -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);

-- Tells you how much memory (in the buffer pool)
-- is being used by each database on the instance
-- Clear Wait Stats

-- DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

Top waits for server instance since last restart

WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats WITH (NOLOCK)
WHERE wait_type NOT IN (N'CLR_SEMAPHORE',N'LAZYWRITER_SLEEP',N'RESOURCE_QUEUE',N'SLEEP_TASK',
N'SLEEP_SYSTEMTASK',N'SQLTRACE_BUFFER_FLUSH',N'WAITFOR', N'LOGMGR_QUEUE',N'CHECKPOINT_QUEUE',
N'REQUEST_FOR_DEADLOCK_SEARCH',N'XE_TIMER_EVENT',N'BROKER_TO_FLUSH',N'BROKER_TASK_STOP',N'CLR_MANUAL_EVENT',
N'CLR_AUTO_EVENT',N'DISPATCHER_QUEUE_SEMAPHORE', N'FT_IFTS_SCHEDULER_IDLE_WAIT',
N'XE_DISPATCHER_WAIT', N'XE_DISPATCHER_JOIN', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'ONDEMAND_TASK_QUEUE', N'BROKER_EVENTHANDLER', N'SLEEP_BPOOL_FLUSH'))
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 99 OPTION (RECOMPILE); -- percentage threshold

-- Common Significant Wait types with BOL explanations

-- *** Network Related Waits ***
-- ASYNC_NETWORK_IO        Occurs on network writes when the task is blocked behind the network

-- *** Locking Waits ***
-- LCK_M_IX                Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock
-- LCK_M_IU                Occurs when a task is waiting to acquire an Intent Update (IU) lock
-- LCK_M_S                Occurs when a task is waiting to acquire a Shared lock

-- *** I/O Related Waits ***
-- ASYNC_IO_COMPLETION  Occurs when a task is waiting for I/Os to finish
-- IO_COMPLETION        Occurs while waiting for I/O operations to complete.
--                      This wait type generally represents non-data page I/Os. Data page I/O completion waits appear
--                      as PAGEIOLATCH_* waits
-- PAGEIOLATCH_SH        Occurs when a task is waiting on a latch for a buffer that is in an I/O request.
--                      The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.
-- PAGEIOLATCH_EX        Occurs when a task is waiting on a latch for a buffer that is in an I/O request.
--                      The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.
-- WRITELOG             Occurs while waiting for a log flush to complete.
--                      Common operations that cause log flushes are checkpoints and transaction commits.
-- PAGELATCH_EX            Occurs when a task is waiting on a latch for a buffer that is not in an I/O request.
--                      The latch request is in Exclusive mode.
-- BACKUPIO                Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data

-- *** CPU Related Waits ***
-- SOS_SCHEDULER_YIELD  Occurs when a task voluntarily yields the scheduler for other tasks to execute.
--                      During this wait the task is waiting for its quantum to be renewed.

-- THREADPOOL            Occurs when a task is waiting for a worker to run on.
--                      This can indicate that the maximum worker setting is too low, or that batch executions are taking
--                      unusually long, thus reducing the number of workers available to satisfy other batches.
-- CX_PACKET            Occurs when trying to synchronize the query processor exchange iterator

--                        You may consider lowering the degree of parallelism if contention on this wait type becomes a problem

Signal Waits for instance

SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits],
       CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%resource waits]
FROM sys.dm_os_wait_stats OPTION (RECOMPILE);



-- Signal Waits above 10-15% is usually a sign of CPU pressure

Get logins that are connected and how many sessions they have

SELECT login_name, COUNT(session_id) AS [session_count]
FROM sys.dm_exec_sessions WITH (NOLOCK)
GROUP BY login_name
ORDER BY COUNT(session_id) DESC OPTION (RECOMPILE);

-- This can help characterize your workload and

-- determine whether you are seeing a normal level of activity

Page Life Expectancy (PLE) value for each NUMA node in current instance 

SELECT @@SERVERNAME AS [Server Name], [object_name], instance_name, cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Buffer Node%' -- Handles named instances
AND counter_name = N'Page life expectancy' OPTION (RECOMPILE);

-- PLE is a good measurement of memory pressure.
-- Higher PLE is better. Watch the trend, not the absolute value.

-- This will only return one row for non-NUMA systems.

Memory Grants Pending value for current instance

SELECT @@SERVERNAME AS [Server Name], [object_name], cntr_value AS [Memory Grants Pending]                                                                                                      
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Memory Manager%' -- Handles named instances
AND counter_name = N'Memory Grants Pending' OPTION (RECOMPILE);


-- Memory Grants Pending above zero for a sustained period is a very strong indicator of memory pressure