How can I extract the perfmon counter "Memory: Pages/Sec" using t-sql?
Following @Srutzky's example of just looking at page faults/sec (not pages/sec), you can do this:

DECLARE @before BIGINT, @after BIGINT;

SELECT @before = page_fault_count FROM sys.dm_os_process_memory;

WAITFOR DELAY '00:00:10';

SELECT @after = page_fault_count FROM sys.dm_os_process_memory;

SELECT PageFaultsPerSec = (1.0*@after - @before)/10.0;


It is likely that you can just have Ignite poll for the following metric:

SELECT page_fault_count FROM sys.dm_os_process_memory


And then set up alerts based on thresholds or deltas. But I still recommend you contact them to find out how to best implement a custom metric based on a column from an arbitrary DMV, which might be different from the perf counters DMV.