A common table expression (CTE) names an intermediate query result inside a SQL statement. It helps separate data conversion from later calculations, making a complex administrative query easier to simplify and refine. section 10.6.2
Calculating memory utilization
A memory-monitoring query can use one CTE to convert settings such as 6G into megabytes, then a second CTE to calculate total memory in use. The final query filters systems using more than 75 percent of physical memory. The result can support a scheduled monthly SQL Server Agent report, but generated SQL should be validated and corrected when syntax or edge cases are wrong. section 10.6.2
-- CTE for converting memory settings to MB and calculating total memory
in use
WITH MemoryCalculations AS (
SELECT
[SERVERNAME],
[DB_NAME],
[PhysicalMemory],
CASE
WHEN RIGHT([memory_max_target], 1) = 'M' THEN CAST(SUBSTRING(
[memory_max_target], 1, LEN([memory_max_target]) - 1) AS INT)
WHEN RIGHT([memory_max_target], 1) = 'G' THEN CAST(SUBSTRING(
[memory_max_target], 1, LEN([memory_max_target]) - 1)
AS INT) * 1024
ELSE [memory_max_target]
END AS [memory_max_target_MB],
...
),
-- Second CTE for calculating total memory in use
FinalCalculations AS (
SELECT
[SERVERNAME],
[DB_NAME],
[PhysicalMemory],
CASE
WHEN [memory_max_target_MB] != 0 THEN [memory_max_target_MB]
ELSE [sga_max_size_MB] + [pga_aggregate_limit_MB]
END AS [TotalMemoryInUse_MB]
FROM
MemoryCalculations
)
SELECT
*,
([TotalMemoryInUse_MB] * 1.0 / [PhysicalMemory]) * 100
AS [TotalMemoryInUse_Percentage]
FROM
FinalCalculations
WHERE
([TotalMemoryInUse_MB] * 1.0 / [PhysicalMemory]) * 100 > 75
Inspect the two-stage CTE structure for unit conversion followed by total-memory calculation. section 10.6.2
