SQL Server 2014 Real Time Query Monitoring

Problem

You have a SQL Server query that always takes too long to execute. You have the execution plan so you know what the query does. But what is the query doing right now? Keep reading and you will know how to monitor query progress in real time.

Solution

How many times have you been asked the same question: how much longer until the process is complete? A few days ago at my work an analyst came to my desk to ask me that question about a step of a process. After a few queries I saw the step in question was a stored procedure performing an update, but that was the only thing I was able to say. Instantly a thought came to my mind: it would be good if this process was running on a SQL Server 2014 instance instead of this old and deprecated SQL Server 2000 version.
The fact is that in SQL Server 2014 we have a new Dynamic Management View to track what a query is actually doing at a physical operator level.  I am referring to the sys.dm_exec_query_profiles Dynamic Management View (DMV).

The sys.dm_exec_query_profiles DMV

In order for this DMV to work we need to enable the capture of the query's Actual Execution Plan. So as you may guess, it doesn't work for plans stored in the Plan Cache. At this point maybe you are asking yourself why can't we get a real time status of any query running on an instance, and the answer is simple: nothing is free.  I mean capturing these metrics has a performance cost due to the fact that SQL Server is collecting fine grained statistics of the query being analyzed.
A plan stored in the Plan Cache doesn't contain execution statistics. It contains estimations that are used to build the plan per se. When we talk about execution statistics we are talking about actual number of reads, writes, CPU time and so on.

Different Ways to Capture Real Time SQL Server Performance Data

We have three methods to capture the actual SQL Server Execution Plan:

• Setting either SET STATISTICS PROFILE or SET STATISTICS XML to ON into the Query being analyzed.
• Create a server side trace including one of these two events: "Showplan Statistics Profile" or "Showplan Statistics XML". Also if you feel more comfortable you can use SQL Server Profiler.
• Create an Extended Events Session and add sqlserver.query_post_execution_showplan as a target event.

The last two methods are useful when you cannot add a set statement to the query you want to analyze, but those methods should be used with caution because they affect server performance in a detrimental way.

Sample SQL Server Server Side Trace Script

Below is a sample trace creation script with the event "Showplan XML Statistics Profile" filtered by database name.

USE master
GO
-- Create a Queue
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT
SET @maxfilesize = 5 

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

EXEC @rc = sp_trace_create @TraceID OUTPUT, 0, N'InsertFileNameHere',
    @maxfilesize, NULL 
IF ( @rc != 0 )
    GOTO error

-- Client side File and Table cannot be scripted

-- Set the events
DECLARE @on BIT
SET @on = 1
EXEC sp_trace_setevent @TraceID, 146, 12, @on
EXEC sp_trace_setevent @TraceID, 146, 35, @on


-- Set the Filters
DECLARE @intfilter INT
DECLARE @bigintfilter BIGINT

EXEC sp_trace_setfilter @TraceID, 35, 0, 6, N'AdventureWorks2012'
-- Set the trace status to start
EXEC sp_trace_setstatus @TraceID, 1

-- display trace id for future references
SELECT  TraceID = @TraceID
GOTO finish

error: 
SELECT  ErrorCode = @rc

finish: 

-- Stops Capturing Events
EXEC sp_trace_setstatus @TraceID, 0

GO

-- Delete trace definition from Server
EXEC sp_trace_setstatus @TraceID, 2

GO
GO

Sample SQL Server Extended Events Session Script

The next script will create an Extended Events Session with event "sqlserver.query_post_execution_showplan" filtered by database name and using the Ring Buffer as a target.

USE master
GO

--Create Extended Events Session
CREATE EVENT SESSION [Capture_Query_Plan] ON SERVER
ADD EVENT sqlserver.query_post_execution_showplan(
    WHERE ([database_name]=N'AdventureWorks2012')) 
ADD TARGET package0.ring_buffer
WITH ( MAX_MEMORY = 4096 KB ,
        EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS ,
        MAX_DISPATCH_LATENCY = 30 SECONDS ,
        MAX_EVENT_SIZE = 0 KB ,
        MEMORY_PARTITION_MODE = NONE ,
        TRACK_CAUSALITY = OFF ,
        STARTUP_STATE = OFF )
GO

--Start Extended Events Session
ALTER EVENT SESSION [Capture_Query_Plan] ON SERVER STATE = START
GO

--Stop Extended Events Session
ALTER EVENT SESSION [Capture_Query_Plan] ON SERVER STATE = STOP
GO

--Drop Extended Events Session
DROP  EVENT SESSION [Capture_Query_Plan] ON SERVER
GO

Samples

For the purposes of this tip I am using the AdventureWorks2012 database which you can download for free from CodePlex at this link http://msftdbprodsamples.codeplex.com/releases/view/55330.
Suppose we have the following query and let's see what we can do.

USE AdventureWorks2012
GO

SET STATISTICS XML ON

SELECT  *
FROM    Sales.SalesOrderHeader OH
    INNER JOIN Sales.SalesOrderDetail OD 
  ON OD.SalesOrderID = OH.SalesOrderID
    INNER JOIN Sales.Customer C 
  ON C.CustomerID = OH.CustomerID
    INNER JOIN Production.Product P 
  ON P.ProductID = OD.ProductID
    INNER JOIN Production.ProductSubcategory PS 
  ON pS.ProductSubcategoryID = P.ProductSubcategoryID

GO

Calculate Estimated Percentage of Completion

We can get an idea about the estimated percentage of completion of our query by looking at the percentage of completion of its physical operators. But you won't find that information in a column of the sys.dm_exec_query_profilesDynamic Management View. We have to do some basic math using the estimate_row_count and row_count columns by assuming that estimate_row_count represents the 100 percent of the task.
Here we have an issue; the reliability of that percentage is given by the accuracy of the estimation of rows given by the Query Optimizer and this depends on how up to date our statistics are. But even if the statistics are up to date the Optimizer can overestimate or underestimate the cardinality of results, so don't be alarmed if you get a percentage of completion higher than 100.
The following query will give us the percentage of completion for every physical operator in the Query Plan, the number of threads used by each operator, the time spent by operator and its dependent objects if applicable.

SELECT  session_id ,
        node_id ,
        physical_operator_name ,
        SUM(row_count) row_count ,
        SUM(estimate_row_count) AS estimate_row_count ,
        IIF(COUNT(thread_id) = 0, 1, COUNT(thread_id)) [Threads] ,
        CAST(SUM(row_count) * 100. / SUM(estimate_row_count) AS DECIMAL(30, 2)) [% Complete] ,
        CONVERT(TIME, DATEADD(ms, MAX(elapsed_time_ms), 0)) [Operator time] ,
        DB_NAME(database_id) + '.' + OBJECT_SCHEMA_NAME(QP.object_id,
                                                        qp.database_id) + '.'
        + OBJECT_NAME(QP.object_id, qp.database_id) [Object Name]
FROM    sys.dm_exec_query_profiles QP
GROUP BY session_id ,
        node_id ,
        physical_operator_name ,
        qp.database_id ,
        QP.OBJECT_ID ,
        QP.index_id
ORDER BY session_id ,
        node_id
GO

The next screen capture is the output of the analysis of the query we previously defined as our profiling subject.

View Operator's Associated Task Status

The sys.dm_exec_query_profiles Dynamic Management View can be joined with sys.dm_os_tasks andsys.dm_os_waiting_tasks by using the task_address column. Look at the next query.

SELECT  QP.session_id ,
        QP.node_id ,
        QP.physical_operator_name ,
        DB_NAME(database_id) + '.' + OBJECT_SCHEMA_NAME(QP.object_id,
                                                        qp.database_id) + '.'
        + OBJECT_NAME(QP.object_id, qp.database_id) [Object Name] ,
        OT.task_state ,
        MAX(WT.wait_duration_ms) [wait_duration_ms] ,
        WT.wait_type
FROM    sys.dm_exec_query_profiles QP
        INNER JOIN sys.dm_os_tasks OT 
   ON OT.task_address = QP.task_address
        LEFT  JOIN sys.dm_os_waiting_tasks WT 
   ON WT.waiting_task_address = QP.task_address
GROUP BY QP.session_id ,
        QP.node_id ,
        QP.physical_operator_name ,
        OT.task_state ,
        QP.database_id ,
        QP.object_id ,
        WT.wait_type
GO        
 

As you can see on the image below with the previous query we have information about the task state for every physical operator, its wait type and duration, if applicable.

SQL Performance Kit

1.SQL Query Tuning

Problem Statement

We have this Update query that is taking much longer to run compared to before. No schema changes have been made though its quite possible data changes have been happening. The update query goes like this,

update msgboard set mb_status =10, mb_changed_by ='SATCOM'  
from msgboard 
where mb_type = 210 

Table Schema

I was easily able to reproduce this issue on my instance, here is how my table setup looks.

/****** Object:  Table [dbo].[msgboard]    Script Date: 12/20/2012 01:52:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[msgboard](
    [mb_id] [int] NOT NULL,
    [mb_type] [int] NOT NULL,
    [mb_status] [int] NOT NULL,
    [mb_changed_by] [varchar](50) NULL,
    [mb_changed_dt]  AS (getdate())
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE UNIQUE CLUSTERED INDEX [pk_msgboard] ON [dbo].[msgboard] 
(
    [mb_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_msgboard_type] ON [dbo].[msgboard] 
(
    [mb_type] ASC,
    [mb_status] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

I populated this table with some sample data of 1000 rows using the following script.

declare @counter int
set @counter=0
declare @mbid int
set @mbid=1
declare @mbtype int
set @mbtype=200
declare @mbstatus int
set @mbstatus=10
while(@counter<1000)
begin
INSERT INTO [dbo].[msgboard]
           ([mb_id]
           ,[mb_type]
           ,[mb_status]
           ,[mb_changed_by])
     VALUES
           (@mbid,@mbtype,@mbstatus,'THESQLDUDE')
set @mbid+=1
set @counter+=1
end
GO

INSERT INTO [test].[dbo].[msgboard]
           ([mb_id]
           ,[mb_type]
           ,[mb_status]
           ,[mb_changed_by])
     VALUES
           (1001,210,10,'MANWITHNONAME')

So, I have 2 indexes on this table:-

1) A unique Clustered Index pk_msgboard on the mb_id column

2) A composite non-clustered index IX_msgboard_type on the columns mb_type and mb_status (in this order).

Query Execution Details

Here is what I observed when I ran the following update statement.

update msgboard set mb_status =10, mb_changed_by ='SATCOM'  
from msgboard 
where mb_type = 210 

To actual execution plan looks like this,

Total Cost: 0.03124485

As you can see, this query is using the Clustered Index pk_msgboard and its performing a Scan. At this point, two questions should pop into your head

1. Why is the query using the clustered index?

2. Why is it performing a Scan operation?

Answer to question #1

Let’s look at the update query carefully. The query is updating the columns mb_status and mb_changed_by, and there is a filter on column mb_type. You might think, well, I have an index IX_msgboard_type on the mb_type column, why is SQL Server not using this non-clustered index?

To prove I was smarter than SQL Server optimizer, I forced it to use the non-clustered index by adding an index hint. The query looks like this,

update msgboard set mb_status =10, mb_changed_by ='SATCOM'  
from msgboard WITH(INDEX(IX_msgboard_type))
where mb_type = 210 

Total Cost: 0.03651106

• Notice that the Cost for the query with index hint is higher than the one without! If the table had more rows, we would have seen a bigger difference in cost .
• Additionally, a Table Spool operator is now part of the execution plan. The table spool operator caches the row to be updated (In tempdb) and then feeds it to the Clustered Index update operator.
• This proves that SQL Server optimizer was actually choosing the cheapest and best plan it could come up with.

The reason SQL Server picked the clustered index is because the query is updating the columns mb_status =10 and mb_changed_by. This column mb_status is not the leading column in the NC index IX_msgboard_type, but the 2nd column. Why does this matter? It does because, the statistics for this NC index would be built on the leading column which is mb_type. Here is how the histogram looks.

dbcc show_statistics ('dbo.msgboard','IX_msgboard_type')

To avoid a bookmark lookup (Key Lookup operator from SQL 2005 onwards), SQL Server decided to use the clustered index since it covers all the columns in the table. If SQL Server were to use the NC index, it would be do a lookup or cache the results (ahem, Table Spool operator!) and then find the matching row in the clustered index to actually update the 2 columns. This is an additional cost, and this is why SQL decided not to go with this plan.

Coming to the 2nd question of why a Scan? It might surprise some of you, but a Seek does not imply good performance always. My favourite answer – “It depends”. In this case, the table only had 1001 rows and of which only 1 row (mb_id=1001) qualified for the update. When using the CI Scan, SQL Server was applying the filter on mb_type=210 and that returned 1 row back. A Clustered Index Seek in this case would not make any positive difference in query execution time.

OBJECT:([test].[dbo].[msgboard].[pk_msgboard]), WHERE:([test].[dbo].[msgboard].[mb_type]=(210)) ORDERED

To test this, I added the FORCESEEK table hint to this update query and as expected the query did not run faster.

update msgboard set mb_status =10, mb_changed_by ='SATCOM'  
from msgboard WITH (FORCESEEK) where mb_type = 210 

In fact it has the same cost as the query with index hint –> 0.03651106

Solution

Instead of trying to second guess the query optimizer and trying to tweak the execution plan it is producing, this issue needs a different approach –> back to basics. Based on the query design, if the column being updated mb_status was “covered” then it would help. In this case that was also also take care of, since IX_msgboard_type also includes the column mb_status, though as a non-key column. This is an important thing and this was why the “included columns” feature was introduced.

So, I modified the index IX_msgboard_type to remove the column mb_status. Instead, I added the column back as an included column

DROP INDEX [IX_msgboard_type] ON [dbo].[msgboard] WITH ( ONLINE = OFF )
GO
CREATE NONCLUSTERED INDEX [IX_msgboard_type] ON [dbo].[msgboard] 
(
    [mb_type] ASC
)
INCLUDE ( [mb_status]) WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

The biggest advantage of having an included column in a non-clustered index is that, the query optimizer can locate all the column values within the index. The base table or clustered index data is not accessed resulting in fewer disk I/O operations and hence faster query execution. Also, the index size is lesser since non-key columns are only added to the leaf level. Many advantages indeed.

Here is how the update query plan looked after added mb_status as an included column.

My oh my, this plan looks so simple, clean and yeah faster. The actual cost is now 0.0132843, which is a big improvement over the original plan of 0.03651106

So we have achieved our original goals of

a) Getting SQL Server to use the Non-clustered index IX_msgboard_type, instead of the clustered index.

b) Getting an Seek operation instead of a Scan.

c) Query cost is cheaper.

2.T-SQL Script to find the size of every index in the database

CREATE PROCEDURE uspIndexSizeinDB
AS 
-- Author: Sudarshan (TheSQLDude.com)
-- Data Created: Dec 15, 2012
-- Description: Calculate the SQL Server index size for all indexes in the current database
  BEGIN 
    DECLARE  @IndexSize BIGINT, 
             @IndexID   INT,
             @IndexName nvarchar(200),
             @IndexType nvarchar(50),
             @ObjectID  INT
     
    SET @IndexSize = 0
    
    create table #tmpresults (ObjName nvarchar(100), IndexID int, IndexName nvarchar(200),[IndexSize(MB)] int, IndexType nvarchar(50))
    
    DECLARE curIndex CURSOR  FOR 
    SELECT sysind.object_id, sysind.index_id, sysind.name, sysind.type_desc 
    FROM sys.indexes sysind 
    join sys.sysobjects sysobj
    on sysind.object_id = sysobj.id
    where sysind.type>0 and sysobj.type not in ('S','IT')
    order by sysobj.id asc
   
    OPEN curIndex 
     
    FETCH NEXT FROM curIndex 
    INTO @ObjectID, @IndexID, @IndexName, @IndexType
     
    WHILE (@@FETCH_STATUS = 0) 
      BEGIN 
        SELECT @IndexSize = sum(avg_record_size_in_bytes * record_count) 
        FROM   sys.dm_db_index_physical_stats(DB_ID(),@ObjectID, @IndexID, NULL, 'detailED') 
         
        insert into #tmpresults (ObjName, IndexID, IndexName, [IndexSize(MB)], IndexType)
        SELECT TableName = OBJECT_NAME(@ObjectID), 
               IndexID = @IndexID, 
               IndexName = @IndexName,
               [IndexSize(MB)] = CONVERT(DECIMAL(16,1),(@IndexSize / (1024.0 * 1024))), IndexType = @IndexType
         
        FETCH NEXT FROM curIndex 
        INTO @ObjectID, @IndexID, @IndexName, @IndexType
      END 
    CLOSE curIndex 
    DEALLOCATE curIndex 
    
    select UPPER(ObjName) as ObjectName, IndexID, IndexName, IndexType, [IndexSize(MB)]  from #tmpresults order by [IndexSize(MB)] desc
  END 
GO

3.Scripts to monitor SQL Server Memory usage in Buffer Pool, Plan Cache and identify Query Memory grants & waits by session

I often use a multitude of T-SQL queries and scripts when I am troubleshooting memory issues and even for monitoring SQL Server Memory usage. I am posting these scripts here in the spirit of sharing, as I’m sure there are other variations of these same scripts out there already. For this purpose of this blog post, these scripts are only useful in troubleshooting out-of-memory (OOM) and other issues in the Buffer Pool. This blog post does not cover Virtual Address/Virtual Memory or memory tracking outside of the Buffer Pool. That will be covered in a later post.

In case you have other scripts for memory monitoring, please leave behind a comment

---

– Query to find the Buffer Pool usage per each Database
– Each of these pages are present in the Buffer Cache, meaning they are IN_RAM pages.

DECLARE @total_buffer INT; SELECT @total_buffer = cntr_value
   FROM sys.dm_os_performance_counters
   WHERE RTRIM([object_name]) LIKE‘%Buffer Manager’
   AND counter_name = ‘Total Pages’;

;WITH BufCount AS (
  SELECT
       database_id, db_buffer_pages = COUNT_BIG(*)
       FROM sys.dm_os_buffer_descriptors
       WHERE database_id BETWEEN 5 AND 32766
       GROUP BY database_id
) SELECT
   [Database_Name] = CASE [database_id] WHEN 32767
       THEN‘MSSQL System Resource DB’
       ELSE DB_NAME([database_id]) END,
   [Database_ID],
   db_buffer_pages as [Buffer Count (8KB Pages)],
   [Buffer Size (MB)] = db_buffer_pages / 128,
   [Buffer Size (%)] = CONVERT(DECIMAL(6,3),
       db_buffer_pages * 100.0 / @total_buffer) FROM BufCount
ORDER BY [Buffer Size (MB)] DESC; 

Output

---

-- Query to identify objects that are taking up most of that memory in Buffer Pool.
-- This is only for the current database context. Please prefix <USE DBNAME> as per your requirement

SELECT TOP 25 
 DB_NAME(bd.database_id) as DBNAME,
 obj.[name] as [Object Name],
 sysobj.type_desc as [Object Type],
 i.[name]   as [Index Name],
 i.[type_desc] as [Index Type],
 count(*)AS Buffered_Page_Count ,
 count(*) * 8192 / (1024 * 1024) as Buffer_MB,
 bd.page_type as [Page Type] -- ,obj.name ,obj.index_id, i.[name]
FROM sys.dm_os_buffer_descriptors AS bd 
    INNER JOIN 
    (
        SELECT object_name(object_id) AS name 
            ,index_id ,allocation_unit_id, object_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.hobt_id 
                    AND (au.type = 1 OR au.type = 3)
        UNION ALL
        SELECT object_name(object_id) AS name   
            ,index_id, allocation_unit_id, object_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.hobt_id 
                    AND au.type = 2
    ) AS obj 
        ON bd.allocation_unit_id = obj.allocation_unit_id
LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id
LEFT JOIN sys.objects sysobj on i.object_id = sysobj.object_id
WHERE database_id = DB_ID()
and sysobj.type not in ('S','IT')
GROUP BY DB_NAME(bd.database_id), obj.name, obj.index_id , i.[name],i.[type_desc],bd.page_type,sysobj.type_desc
ORDER BY Buffered_Page_Count DESC

Output

---

-- Query to show current memory requests, grants and execution plan for each active session
-- This shows memory granted & requested for currently active sessions on the instance level
-- This can be used in a script to capture information over a period of time.

SELECT mg.session_id, mg.requested_memory_kb, mg.granted_memory_kb, mg.used_memory_kb, t.text, qp.query_plan 
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY 1 DESC OPTION (MAXDOP 1)

Output

Note: When you click on the query_plan, it opens up the XML Showplan in SSMS.

---

-- Query to search plan cache for queries with memory grants completed

SELECT top 50 t.text, cp.objtype ,qp.query_plan, cp.usecounts, cp.size_in_bytes as [Bytes Used in Cache]
FROM sys.dm_exec_cached_plans AS cp
JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE qp.query_plan.exist('declare namespace n="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; //n:MemoryFractions') = 1
order by cp.size_in_bytes desc
OPTION (MAXDOP 1)

Output

---

-- Queries that have requested memory or waiting for memory to be granted
SELECT  DB_NAME(st.dbid) AS [DatabaseName] ,
        mg.requested_memory_kb ,
        mg.ideal_memory_kb ,
        mg.request_time ,
        mg.grant_time ,
        mg.query_cost ,
        mg.dop ,
        st.[text]
FROM    sys.dm_exec_query_memory_grants AS mg
        CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE   mg.request_time < COALESCE(grant_time, '99991231')
ORDER BY mg.requested_memory_kb DESC ;

Note: This query can be used when there are active sessions waiting on memory to be granted and they are waiting. These sessions will have a wait_type of RESOURCE_SEMAPHORE. You can calculate the wait time for memory grant, by subtracting request_time and grant_time

---

-- SQL Server 2005/2008/R2 version
-- Top clerks ordered by memory used
SELECT TOP(20) [type] as [Memory Clerk Name], SUM(single_pages_kb) AS [SPA Memory (KB)],
SUM(single_pages_kb)/1024 AS [SPA Memory (MB)]
FROM sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY SUM(single_pages_kb) DESC;

-- SQL Server 2012 version
-- Top clerks ordered by memory used
SELECT TOP(20) [type] as [Memory Clerk Name], SUM(pages_kb) AS [SPA Memory (KB)],
SUM(pages_kb)/1024 AS [SPA Memory (MB)]
FROM sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY SUM(pages_kb) DESC;

Output