I've got a pretty complex batch processing procedure that takes hours to run on real data. I've found that some CPU and I/O counters are stored in the master.sys.dm_exec_sessions table, and so thought that maybe I can use them to measure which parts of the procedure are taking most resources. However, it seems that repeatedly querying this table within a single procedure always returns the same values. I know I can trigger update of these statistics by issuing the GO statement, but of course it is not possible to do that inside a procedure. Are there any other ways to read these counters that would make sure they are up to date? MWE: CREATE OR ALTER PROCEDURE dbo.MineBitcoins @Prefix INT AS BEGIN DECLARE @It INT = 0 WHILE COALESCE(TRY_CAST(LEFT(HASHBYTES('SHA2_256', HASHBYTES('SHA2_256', CONVERT(VARCHAR, @It))), @Prefix) AS INT), 1) 0 BEGIN PRINT @It SET @It = @It + 1 END SELECT @It END GO CREATE OR ALTER PROCEDURE dbo.MeasureTimeOfAStupidQuery @Prefix INT AS BEGIN SELECT cpu_time, reads, logical_reads, writes FROM master.sys.dm_exec_sessions WHERE session_id = @@SPID EXEC dbo.MineBitcoins @Prefix SELECT cpu_time, reads, logical_reads, writes FROM master.sys.dm_exec_sessions WHERE session_id = @@SPID END GO EXEC dbo.MeasureTimeOfAStupidQuery @Prefix = 3 (it takes about 30 seconds on my test database instance).

I've set up a SQL Server instance on a dedicated Windows server. The .mdf data file is placed on a dedicated disk configured as RAID 10, with the RAID controller cache ratio set to 90% write / 10% read. I'm observing unexpectedly high read latency based on metrics reported by both SQL Server (e.g., "Stalls/sec for Read") and Windows Performance Monitor (e.g., "Avg. Disk sec/Read"). SQL Server reports "Stalls/sec for Read" consistently above 20ms Performance Monitor shows "Avg. Disk sec/Read" at about 25ms, with only 5–10 disk reads/sec Write performance, in contrast, is consistently excellent. Latency remains under 1ms, which aligns with the RAID controller's write cache configuration. However, I’ve noticed that approximately once per minute, there's a write spike where Disk Writes/sec temporarily jumps above 5,000. Apart from these brief spikes, write performance is stable and fast. To validate storage performance independently, I used fio to simulate random read load against a 100GB data file. Surprisingly, fio reports read latency of only around 5ms, which is much lower than what SQL Server and Performance Monitor are reporting. Here's the fio command I used: fio --name=rand_read_test --rw=randread --bs=68k --size=1G --numjobs=4 --iodepth=1 --ioengine=windowsaio --direct=1 --time_based --runtime=60s --filename=testfile1g This server also has two other dedicated drives: - One for the .ldf log files - One for tempdb Both of these disks show normal latency and no unusual performance behavior. **My questions:** - What could explain this high read latency reported by SQL Server and Performance Monitor, despite relatively low read IOPS and healthy synthetic benchmarks? - Could the RAID controller's cache configuration be limiting read performance in ways not captured by fio? - Are there SQL Server-specific patterns or behaviors that might cause high logical read latency despite light physical disk usage?

First of all, I don't have enough reputation to comment on this post to ask my question, hence asking here. I have a SQL Server 2022 instance which is target of an ETL process, receiving multiple million records every 2 hours. There are also heavy calculations on the same instance resulting in heavy table truncate and writes right after ETL. I have heavy disk writes when ETL starts (Disk Write Bytes/sec performance counter from SQLServer:Resource Pool Stats), but Checkpoint pages/sec is constantly zero at the same time. Recovery interval is set to zero (automatic). How can I find why checkpoint pages/sec is zero? Other performance counters (e.g. Buffer cache hit ratio and PLE) return reasonable values.

I am trying to understand the memory management of SQL Server. I have a Windows Server 2019 on virtual environment with 64 GB of RAM. I also have SQL Server 2019 installed with Max Server Memory 59776 MB. By using the following query I found the list of all memory clerks: SELECT [type] AS [ClerkType], SUM(pages_kb) / 1024 AS [SizeMb] FROM sys.dm_os_memory_clerks WITH (NOLOCK) GROUP BY [type] ORDER BY SUM(pages_kb) DESC Total sum of all memory clerks was equal to 23523 MB. Value for MEMORYCLERK_SQLBUFFERPOOL memory clerk is 18387 MB. I also checked some performance counters: Total Server Memory (KB) = 32262 MB. Database Cache Memory (KB) = 18387 MB. Please correct me if any of the below statements are wrong, * Max Server Memory for SQL Server 2019 = Buffer Pool Memory + Non-Buffer Pool Memory. * Total Server Memory (KB) - The committed memory from the Buffer Pool. * Data Cache Memory (KB) performance counter is equivalent to MEMORYCLERK_SQLBUFFERPOOL memory clerk and both represents data cache used size. * Above query shows all memory clerks for both Buffer Pool Memory and Non-Buffer Pool Memory. My question related to this subject: If Total Server Memeory (KB) is related to Buffer Pool memory, then why its size (32262 MB) is bigger than Total sum memory clerks altogether (23523 MB) which represents both Buffer Poll Memory and Non-Buffer Pool Memory? **EDIT:** Result of select * from sys.dm_os_process_memory

I am trying to load test a simple insert stored procedure: CREATE TABLE _test(ID BIGINT) GO CREATE OR ALTER PROCEDURE dbo.test_sp AS BEGIN SET NOCOUNT ON; BEGIN INSERT INTO _test SELECT CAST(RAND() * 10000 AS BIGINT) END END When I execute this stored procedure with the SQL Stress tool, I get *SQL Compilations/sec* equal to *Batch Requests/sec*. Interestingly, *SQL Re-Compilations/sec* is zero. Both *optimize for ad-hoc workloads* and *forced parameterization* are enabled. The picture is the same even if I change my procedure to a simple SELECT 1. I am using Microsoft SQL Server 2016 (SP3) (KB5003279). A profiler trace shows the tool sends a simple EXEC dbo.test_sp

There was a problem time frame of 30 mins (22:00 to 22:30) during which queries had performance issues - bad execution plans, which led to high CPU. That's what monitoring tool shows: CPU: Checking other performance metrics for this time frame, they show a 30 min gap Batch Requests / sec: User Connections: Basically, all SQL Server related performance metrics (Batch Requests / sec, Memory Grants, Granted Workspace Memory, etc.) are missing for a problem time frame, while machine metrics (CPU, memory, network, disk etc.) are present Why could this be ?

I want to find average Memory USED per day. SQL server caches as much data as possible in RAM (Buffer Cache) - this is ALLOCATED RAM But how much of this data does it actually read subsequently - this is USED RAM Scenario: 1. Server with 128 GB RAM, 120 GB allocated to SQL 2. Day 1 : Run SELECT * FROM table_120GB (Copies 120GB from DISK into RAM, and Reads it from RAM) 3. Day 2-9 : Do Nothing 4. Day 10 : Run SELECT * FROM table_120GB WHERE ID = 1 (Reads 1 GB from RAM) Average Memory ALLOCATED = 120 * 10 / 10= 120 GB / day Average Memory USED = (120 + 1)/10 = 12.1 GB / day Which counter from sys.dm_os_performance_counters gets me this Memory USED value ?

We are trying to monitor SQL Worker Threads and in the past we used the following query: SELECT SUM(current_workers_count) as [Current worker thread] FROM sys.dm_os_schedulers We are now considering just using the PerfMon counter “Process>Thread Count” for each SQL Server Instance. During our analysis, we noticed that there is a slight difference between the PerfMon Thread Count and the Worker Threads as found in dm_os_schedulers or dm_os_workers. We also compared it to what was found in dm_os_threads and still saw a difference. Does anyone know the difference between what is being tracked in those Microsoft provided DMV’s vs what is being captured by PerfMon? The DMV’s are consistently lower than what is found in the Performance Monitor, so they must be "excluding" something. We need to understand what that something is. For example, the dm_os_threads dmv will show 137 but the perfmon counter will show 141 and the dm_os_scheduler and dm_os_workerss dmv's will show 122 and 123 respectively. We are on SQL Server 2017, CU22; Windows Server 2016

Below what my server's performance counters show: Questions: 1) What "Stolen Server Memory" consists of ? I can assume "Plan Cache", "Lock Memory", "SQL Cache", "Connection", "Optimizer" are included into "Stolen Server Memory", but what else is included ? 2) How can I query rest of things that are included into "Stolen Server Memory" ?

Is there any system catalog views or DMVs that can be used to query (in SSMS for example) below performance counters from SQL Server:Memory Manager ? Free Memory (KB) Target Server Memory Total Server Memory Maximum Workspace Memory (KB) Database Cache Memory (KB) Granted Workspace Memory (KB) Lock Memory (KB) Log Pool Memory (KB) Optimizer Memory (KB) Connection Memory (KB) SQL Cache Memory (KB) Reserved Server Memory (KB) Stolen Server Memory (KB)