SQL Server DMV: sys.dm_exec_requests

Today, I’ve received an email from friend asking that that is there any way to find out the progress of following operation using T- SQL query. As we know, with the release of SQL Server 2005, Microsoft provides set of dynamic management views (DMVs) which helps ease the administration SQL Server Database Engine. These sets of new DMVs include one particular DMV that is sys.dm_exec_requests, which we can use to return information about the requests that are currently executing on SQL Server instance. I used this DMV to write the following query, which helps to find the progress of following operations programmatically:

  • ALTER INDEX REORGANIZE
  • AUTO_SHRINK
  • BACKUP DATABASE
  • DBCC CHECKDB
  • DBCC CHECKFILEGROUP
  • DBCC CHECKTABLE
  • DBCC INDEXDEFRAG
  • DBCC SHRINKDATABASE
  • DBCC SHRINKFILE
  • RECOVERY
  • RESTORE DATABASE
  • ROLLBACK
  • TDE ENCRYPTION
  • RESTORE LOG
  • BACKUP LOG
SELECT dmv1.[session_id] AS [UserSessionID]
	,dmv2.[login_name] AS [SessionLoginName]
	,dmv2.[original_login_name] AS [ConnectionLoginName]
	,dmv1.[command] AS [TSQLCommandType]
	,est.[text] AS [TSQLCommandText]
	,dmv2.[status] AS [Status]
	,dmv2.[cpu_time] AS [CPUTime]
	,dmv2.[memory_usage] AS [MemoryUsage]
	,dmv1.[start_time] AS [StartTime]
	,dmv1.[percent_complete] AS [PercentComplete]
	,dmv2.[program_name] AS [ProgramName]
	,CAST(((DATEDIFF(s, dmv1.[start_time], CURRENT_TIMESTAMP)) / 3600) AS [varchar](32)) + ' hour(s), ' + CAST((DATEDIFF(s, dmv1.[start_time], CURRENT_TIMESTAMP) % 3600) / 60 AS [varchar](32)) + 'min, ' + CAST((DATEDIFF(s, dmv1.[start_time], CURRENT_TIMESTAMP) % 60) AS [varchar](32)) + ' sec' AS [RunningTime]
	,CAST((dmv1.[estimated_completion_time] / 3600000) AS [varchar](32)) + ' hour(s), ' + CAST((dmv1.[estimated_completion_time] % 3600000) / 60000 AS [varchar](32)) + 'min, ' + CAST((dmv1.[estimated_completion_time] % 60000) / 1000 AS [varchar](32)) + ' sec' AS [TimeRequiredToCompleteOperation]
	,dateadd(second, dmv1.[estimated_completion_time] / 1000, CURRENT_TIMESTAMP) AS [EstimatedCompletionTime]
FROM [sys].[dm_exec_requests] dmv1
CROSS APPLY [sys].[dm_exec_sql_text](dmv1.[sql_handle]) est
INNER JOIN [sys].[dm_exec_sessions] dmv2
	ON dmv1.[session_id] = dmv2.[session_id]
WHERE dmv1.[command] IN ('ALTER INDEX REORGANIZE', 'AUTO_SHRINK', 'BACKUP DATABASE', 'DBCC CHECKDB', 'DBCC CHECKFILEGROUP', 'DBCC CHECKTABLE', 'DBCC INDEXDEFRAG', 'DBCC SHRINKDATABASE', 'DBCC SHRINKFILE', 'RECOVERY', 'RESTORE DATABASE', 'ROLLBACK', 'TDE ENCRYPTION', 'RESTORE LOG', 'BACKUP LOG')

Then following is the resultset this query returned, when I executed against server where I’m performing backup of the database (Note: To fit the resultset on the page, I splitted into two images):

progress_1

progress_2

For more information about this DMV, see sys.dm_exec_requests (Transact-SQL).

Advertisements

Find missing indexes using SQL Servers index related DMVs

Today, we experienced performance issues with few databases that are hosted on one of our most critical production SQL Server. Upon reviewing the query execution plan and querying index related dynamic management views (DMVs), I noticed the problem is related with potential missing indexes on columns. The index related dynamic management views (DMVs) I queried are as follow:

  • sys.dm_db_missing_index_details — Returns detailed information about missing indexes, including the table, columns used in equality operations, columns used in inequality operations, and columns used in include operations.
  • sys.dm_db_missing_index_group_stats — Returns information about groups of missing indexes, which SQL Server updates with each query execution (not based on query compilation or recompilation).
  • sys.dm_db_missing_index_groups — Returns information about missing indexes contained in a missing index group.

Using these dynamic management views (DMVs), I wrote the following query, which returns the list of possible missing indexes for all SQL Server user databases. The results are ordered by index advantage that helps you to identify how beneficial each index would be, if we create them on the table.

SELECT CAST(SERVERPROPERTY('ServerName') AS [nvarchar](256)) AS [SQLServer]
	,db.[database_id] AS [DatabaseID]
	,db.[name] AS [DatabaseName]
	,id.[object_id] AS [ObjectID]
	,id.[statement] AS [FullyQualifiedObjectName]
	,id.[equality_columns] AS [EqualityColumns]
	,id.[inequality_columns] AS [InEqualityColumns]
	,id.[included_columns] AS [IncludedColumns]
	,gs.[unique_compiles] AS [UniqueCompiles]
	,gs.[user_seeks] AS [UserSeeks]
	,gs.[user_scans] AS [UserScans]
	,gs.[last_user_seek] AS [LastUserSeekTime]
	,gs.[last_user_scan] AS [LastUserScanTime]
	,gs.[avg_total_user_cost] AS [AvgTotalUserCost]
	,gs.[avg_user_impact] AS [AvgUserImpact]
	,gs.[system_seeks] AS [SystemSeeks]
	,gs.[system_scans] AS [SystemScans]
	,gs.[last_system_seek] AS [LastSystemSeekTime]
	,gs.[last_system_scan] AS [LastSystemScanTime]
	,gs.[avg_total_system_cost] AS [AvgTotalSystemCost]
	,gs.[avg_system_impact] AS [AvgSystemImpact]
	,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage]
	,'CREATE INDEX [Missing_IXNC_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE 
		WHEN id.[equality_columns] IS NOT NULL
			AND id.[inequality_columns] IS NOT NULL
			THEN '_'
		ELSE ''
		END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE 
		WHEN id.[equality_columns] IS NOT NULL
			AND id.[inequality_columns] IS NOT NULL
			THEN ','
		ELSE ''
		END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex]
	,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]
FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK)
INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK)
	ON gs.[group_handle] = ig.[index_group_handle]
INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK)
	ON ig.[index_handle] = id.[index_handle]
INNER JOIN [sys].[databases] db WITH (NOLOCK)
	ON db.[database_id] = id.[database_id]
WHERE id.[database_id] > 4 -- Remove this to see for entire instance
ORDER BY [IndexAdvantage] DESC
OPTION (RECOMPILE);

01
02
03
Obviously these missing indexes are the ones that the SQL Server optimizer identified during query compilation, and these missing index recommendations are specific recommendation targeting a specific query.  Consider submitting your workload and the proposed index to the Database Tuning Advisor for further evaluation that include partitioning, choice of clustered versus non-clustered index, and so on.

For more information about Database Tuning Advisor, see Start and Use the Database Engine Tuning Advisor and Tutorial: Database Engine Tuning Advisor.

SQL Server Guest User – Still a Serious Security Threat

One of the security recommendation inside SQL Server Security Best Practice white paper for guest user is that, to disable guest access in every database (expect “master”, “msdb” and “tempdb”) on SQL Server instance, and it should not be used in any circumstances. By default, guest user exists in all user and system databases. Having guest user enabled inside databases, leaves a security risks of unauthorized (or unaudited) access to the data because Guest user allows database access to logins who do not have associated users inside SQL Server databases. By disabling guest user access from the user databases will ensure, that member of PUBLIC server role may not be able to access user databases on SQL Server instance, unless they have access to the database explicitly.

I have written following script which you can use to list all databases with guest user access enabled:

USE [master]
GO

SET NOCOUNT ON

DECLARE @First [smallint]
	,@Last [smallint]
	,@DBName [varchar] (200)
	,@SQLCommand [varchar] (500)
	,@DBWithGuestAccess [nvarchar] (4000)

IF OBJECT_ID('tempdb..#GuestUsersReport') IS NOT NULL
	DROP TABLE #GuestUsersReport

CREATE TABLE #GuestUsersReport (
	[Database] [varchar](256)
	,[UserName] [varchar](256)
	,[HasDbAccess] [varchar](10)
	)

DECLARE @DatabaseList TABLE (
	[RowNo] [smallint] identity(1, 1)
	,[DBName] [varchar](200)
	)

INSERT INTO @DatabaseList
SELECT [name]
FROM [master]..[sysdatabases] WITH (NOLOCK)
WHERE [name] NOT IN ('master', 'tempdb', 'msdb')
ORDER BY [name]

SELECT @First = MIN([RowNo])
FROM @DatabaseList

SELECT @Last = MAX([RowNo])
FROM @DatabaseList

WHILE @First <= @Last
BEGIN
	SELECT @DBName = [DBName]
	FROM @DatabaseList
	WHERE [RowNo] = @First

	SET @SQLCommand = 'INSERT INTO #GuestUsersReport ([Database], [UserName], [HasDbAccess])' + CHAR(13) + 'SELECT ' + CHAR(39) + @DBName + CHAR(39) + ' ,[name], CASE [hasdbaccess] WHEN 0 THEN ''N'' WHEN 1 THEN ''Y'' END ' + CHAR(13) + 'FROM [' + @DBName + ']..[sysusers] WHERE [name] LIKE ''guest'' AND [hasdbaccess] = 1'

	EXEC (@SQLCommand)

	SET @First = @First + 1
END

SELECT *
FROM #GuestUsersReport WITH (NOLOCK)

SET NOCOUNT OFF

IF OBJECT_ID('tempdb..#GuestUsersReport') IS NOT NULL
	DROP TABLE #GuestUsersReport

Well after running the above script, if you find any databases with guest access enabled, then use the REVOKE CONNECT statement to revoke the access of guess access from user database:

--Specify database name in USE statement
USE [<SpecifyDatabaseName>]
GO

REVOKE CONNECT TO [guest]
GO

This recommendation does not apply to “master”, “msdb” and “tempdb” database. For more information, see “KB #2539091 : You should not disable the guest user in the msdb database in SQL Server” and “KB #2186935 : Guidelines on revoking Guest user access in a database“.

Find failed SQL Agent jobs using Transact-SQL script

Today, I’m sharing the following T-SQL script which you can use to find which SQL Server Agent Jobs failed yesterday. I use this script as part of my daily server monitoring SSIS package, which executes this script on all production SQL server and then sends email to our group:

SET NOCOUNT ON;

DECLARE @Value [varchar] (2048)
	,@JobName [varchar] (2048)
	,@PreviousDate [datetime]
	,@Year [varchar] (4)
	,@Month [varchar] (2)
	,@MonthPre [varchar] (2)
	,@Day [varchar] (2)
	,@DayPre [varchar] (2)
	,@FinalDate [int]
-- Declaring Table variable
DECLARE @FailedJobs TABLE ([JobName] [varchar](200))

-- Initialize Variables
SET @PreviousDate = DATEADD(dd, - 1, GETDATE())
SET @Year = DATEPART(yyyy, @PreviousDate)

SELECT @MonthPre = CONVERT([varchar](2), DATEPART(mm, @PreviousDate))

SELECT @Month = RIGHT(CONVERT([varchar], (@MonthPre + 1000000000)), 2)

SELECT @DayPre = CONVERT([varchar](2), DATEPART(dd, @PreviousDate))

SELECT @Day = RIGHT(CONVERT([varchar], (@DayPre + 1000000000)), 2)

SET @FinalDate = CAST(@Year + @Month + @Day AS [int])

-- Final Logic
INSERT INTO @FailedJobs
SELECT DISTINCT j.[name]
FROM [msdb].[dbo].[sysjobhistory] h
INNER JOIN [msdb].[dbo].[sysjobs] j
	ON h.[job_id] = j.[job_id]
INNER JOIN [msdb].[dbo].[sysjobsteps] s
	ON j.[job_id] = s.[job_id]
		AND h.[step_id] = s.[step_id]
WHERE h.[run_status] = 0
	AND h.[run_date] > @FinalDate

SELECT @JobName = COALESCE(@JobName + ', ', '') + '[' + [JobName] + ']'
FROM @FailedJobs

SELECT @Value = 'Failed SQL Agent job(s) found: ' + @JobName + '. '

IF @Value IS NULL
BEGIN
	SET @Value = 'None.'
END

SELECT @Value

I hope you will find this script useful 😉

How to list all CLR objects inside your database?

I wrote the following query that returns the list of all CLR functions/procedures/objects exists inside your database:

SELECT o.object_id AS [Object_ID]
	,schema_name(o.schema_id) + '.' + o.[name] AS [CLRObjectName]
	,o.type_desc AS [CLRType]
	,o.create_date AS [Created]
	,o.modify_date AS [Modified]
	,a.permission_set_desc AS [CLRPermission]
FROM sys.objects o
INNER JOIN sys.module_assembly_usages ma
	ON o.object_id = ma.object_id
INNER JOIN sys.assemblies a
	ON ma.assembly_id = a.assembly_id

How to find SQL Agent Jobs without Notification Operator Configured?

We can either use SQL Server Management Studio or use Transact-SQL query to find any SQL Agent jobs that have been setup without notification operator.

The following are the steps to find SQL Agent jobs without notification operator via SQL Server Management Studio:

  1. In Object Explorer, expand SQL Agent, then expand Jobs folder (see below):

  2. Next, right-click any SQL Agent Job and choose Properties and then click Notifications (see below):



  3. This is a good idea only if we have a small number of SQL Agent Jobs on SQL Server. However, most production systems literally contain hundreds of SQL Agent Jobs, and it is quite difficult to find this information like that. In this situation, you might use the following Transact-SQL query, which I’ve written a while ago to find SQL Agent Jobs without notification operator:

    USE [msdb]
    GO
    
    SET NOCOUNT ON;
    
    SELECT 'SQL Agent job(s) without notification operator found:' AS [Message]
    
    SELECT j.[name] AS [JobName]
    FROM [dbo].[sysjobs] j
    LEFT JOIN [dbo].[sysoperators] o ON (j.[notify_email_operator_id] = o.[id])
    WHERE j.[enabled] = 1
    	AND j.[notify_level_email] NOT IN (1, 2, 3)
    GO
    

    I hope you will find this information useful 😉

Transact-SQL Script to Monitor Replication Status

Today, I’ve written the following T-SQL script which you can use to monitor the status of transactional replication and performance of publications and subscriptions. This script helps you to answer common questions such as:

  • Is my transactional replication healthy?
  • How far behind is my transactional subscription?
  • How long will it take a transaction committed now to reach a Subscriber in transactional replication?

Prerequisites

To execute this script, you must have SELECT permission on the following tables inside distribution and master databases:

Code

Below is the code of this script:

USE [distribution]

IF OBJECT_ID('Tempdb.dbo.#ReplStats') IS NOT NULL
	DROP TABLE #ReplStats

CREATE TABLE [dbo].[#ReplStats] (
	[DistributionAgentName] [nvarchar](100) NOT NULL
	,[DistributionAgentStartTime] [datetime] NOT NULL
	,[DistributionAgentRunningDurationInSeconds] [int] NOT NULL
	,[IsAgentRunning] [bit] NULL
	,[ReplicationStatus] [varchar](14) NULL
	,[LastSynchronized] [datetime] NOT NULL
	,[Comments] [nvarchar](max) NOT NULL
	,[Publisher] [sysname] NOT NULL
	,[PublicationName] [sysname] NOT NULL
	,[PublisherDB] [sysname] NOT NULL
	,[Subscriber] [nvarchar](128) NULL
	,[SubscriberDB] [sysname] NULL
	,[SubscriptionType] [varchar](64) NULL
	,[DistributionDB] [sysname] NULL
	,[Article] [sysname] NOT NULL
	,[UndelivCmdsInDistDB] [int] NULL
	,[DelivCmdsInDistDB] [int] NULL
	,[CurrentSessionDeliveryRate] [float] NOT NULL
	,[CurrentSessionDeliveryLatency] [int] NOT NULL
	,[TotalTransactionsDeliveredInCurrentSession] [int] NOT NULL
	,[TotalCommandsDeliveredInCurrentSession] [int] NOT NULL
	,[AverageCommandsDeliveredInCurrentSession] [int] NOT NULL
	,[DeliveryRate] [float] NOT NULL
	,[DeliveryLatency] [int] NOT NULL
	,[TotalCommandsDeliveredSinceSubscriptionSetup] [int] NOT NULL
	,[SequenceNumber] [varbinary](16) NULL
	,[LastDistributerSync] [datetime] NULL
	,[Retention] [int] NULL
	,[WorstLatency] [int] NULL
	,[BestLatency] [int] NULL
	,[AverageLatency] [int] NULL
	,[CurrentLatency] [int] NULL
	) ON [PRIMARY]

INSERT INTO #ReplStats
SELECT da.[name] AS [DistributionAgentName]
	,dh.[start_time] AS [DistributionAgentStartTime]
	,dh.[duration] AS [DistributionAgentRunningDurationInSeconds]
	,md.[isagentrunningnow] AS [IsAgentRunning]
	,CASE md.[status]
		WHEN 1
			THEN '1 - Started'
		WHEN 2
			THEN '2 - Succeeded'
		WHEN 3
			THEN '3 - InProgress'
		WHEN 4
			THEN '4 - Idle'
		WHEN 5
			THEN '5 - Retrying'
		WHEN 6
			THEN '6 - Failed'
		END AS [ReplicationStatus]
	,dh.[time] AS [LastSynchronized]
	,dh.[comments] AS [Comments]
	,md.[publisher] AS [Publisher]
	,da.[publication] AS [PublicationName]
	,da.[publisher_db] AS [PublisherDB]
	,CASE 
		WHEN da.[anonymous_subid] IS NOT NULL
			THEN UPPER(da.[subscriber_name])
		ELSE UPPER(s.[name])
		END AS [Subscriber]
	,da.[subscriber_db] AS [SubscriberDB]
	,CASE da.[subscription_type]
		WHEN '0'
			THEN 'Push'
		WHEN '1'
			THEN 'Pull'
		WHEN '2'
			THEN 'Anonymous'
		ELSE CAST(da.[subscription_type] AS [varchar](64))
		END AS [SubscriptionType]
	,md.[distdb] AS [DistributionDB]
	,ma.[article] AS [Article]
	,ds.[UndelivCmdsInDistDB]
	,ds.[DelivCmdsInDistDB]
	,dh.[current_delivery_rate] AS [CurrentSessionDeliveryRate]
	,dh.[current_delivery_latency] AS [CurrentSessionDeliveryLatency]
	,dh.[delivered_transactions] AS [TotalTransactionsDeliveredInCurrentSession]
	,dh.[delivered_commands] AS [TotalCommandsDeliveredInCurrentSession]
	,dh.[average_commands] AS [AverageCommandsDeliveredInCurrentSession]
	,dh.[delivery_rate] AS [DeliveryRate]
	,dh.[delivery_latency] AS [DeliveryLatency]
	,dh.[total_delivered_commands] AS [TotalCommandsDeliveredSinceSubscriptionSetup]
	,dh.[xact_seqno] AS [SequenceNumber]
	,md.[last_distsync] AS [LastDistributerSync]
	,md.[retention] AS [Retention]
	,md.[worst_latency] AS [WorstLatency]
	,md.[best_latency] AS [BestLatency]
	,md.[avg_latency] AS [AverageLatency]
	,md.[cur_latency] AS [CurrentLatency]
FROM [distribution]..[MSdistribution_status] ds
INNER JOIN [distribution]..[MSdistribution_agents] da ON da.[id] = ds.[agent_id]
INNER JOIN [distribution]..[MSArticles] ma ON ma.publisher_id = da.publisher_id
	AND ma.[article_id] = ds.[article_id]
INNER JOIN [distribution]..[MSreplication_monitordata] md ON [md].[job_id] = da.[job_id]
INNER JOIN [distribution]..[MSdistribution_history] dh ON [dh].[agent_id] = md.[agent_id]
	AND md.[agent_type] = 3
INNER JOIN [master].[sys].[servers] s ON s.[server_id] = da.[subscriber_id]
--Created WHEN your publication has the immediate_sync property set to true. This property dictates 
--whether snapshot is available all the time for new subscriptions to be initialized. 
--This affects the cleanup behavior of transactional replication. If this property is set to true, 
--the transactions will be retained for max retention period instead of it getting cleaned up
--as soon as all the subscriptions got the change. 
WHERE da.[subscriber_db] <> 'virtual'
	AND da.[anonymous_subid] IS NULL
	AND dh.[start_time] = (
		SELECT TOP 1 start_time
		FROM [distribution]..[MSdistribution_history] a
		INNER JOIN [distribution]..[MSdistribution_agents] b ON a.[agent_id] = b.[id]
			AND b.[subscriber_db] <> 'virtual'
		WHERE [runstatus] <> 1
		ORDER BY [start_time] DESC
		)
	AND dh.[runstatus] <> 1

SELECT 'Transactional Replication Summary' AS [Comments];

SELECT [DistributionAgentName]
	,[DistributionAgentStartTime]
	,[DistributionAgentRunningDurationInSeconds]
	,[IsAgentRunning]
	,[ReplicationStatus]
	,[LastSynchronized]
	,[Comments]
	,[Publisher]
	,[PublicationName]
	,[PublisherDB]
	,[Subscriber]
	,[SubscriberDB]
	,[SubscriptionType]
	,[DistributionDB]
	,SUM([UndelivCmdsInDistDB]) AS [UndelivCmdsInDistDB]
	,SUM([DelivCmdsInDistDB]) AS [DelivCmdsInDistDB]
	,[CurrentSessionDeliveryRate]
	,[CurrentSessionDeliveryLatency]
	,[TotalTransactionsDeliveredInCurrentSession]
	,[TotalCommandsDeliveredInCurrentSession]
	,[AverageCommandsDeliveredInCurrentSession]
	,[DeliveryRate]
	,[DeliveryLatency]
	,[TotalCommandsDeliveredSinceSubscriptionSetup]
	,[SequenceNumber]
	,[LastDistributerSync]
	,[Retention]
	,[WorstLatency]
	,[BestLatency]
	,[AverageLatency]
	,[CurrentLatency]
FROM #ReplStats
GROUP BY [DistributionAgentName]
	,[DistributionAgentStartTime]
	,[DistributionAgentRunningDurationInSeconds]
	,[IsAgentRunning]
	,[ReplicationStatus]
	,[LastSynchronized]
	,[Comments]
	,[Publisher]
	,[PublicationName]
	,[PublisherDB]
	,[Subscriber]
	,[SubscriberDB]
	,[SubscriptionType]
	,[DistributionDB]
	,[CurrentSessionDeliveryRate]
	,[CurrentSessionDeliveryLatency]
	,[TotalTransactionsDeliveredInCurrentSession]
	,[TotalCommandsDeliveredInCurrentSession]
	,[AverageCommandsDeliveredInCurrentSession]
	,[DeliveryRate]
	,[DeliveryLatency]
	,[TotalCommandsDeliveredSinceSubscriptionSetup]
	,[SequenceNumber]
	,[LastDistributerSync]
	,[Retention]
	,[WorstLatency]
	,[BestLatency]
	,[AverageLatency]
	,[CurrentLatency]

SELECT 'Transactional Replication Summary Details' AS [Comments];

SELECT [Publisher]
	,[PublicationName]
	,[PublisherDB]
	,[Article]
	,[Subscriber]
	,[SubscriberDB]
	,[SubscriptionType]
	,[DistributionDB]
	,SUM([UndelivCmdsInDistDB]) AS [UndelivCmdsInDistDB]
	,SUM([DelivCmdsInDistDB]) AS [DelivCmdsInDistDB]
FROM #ReplStats
GROUP BY [Publisher]
	,[PublicationName]
	,[PublisherDB]
	,[Article]
	,[Subscriber]
	,[SubscriberDB]
	,[SubscriptionType]
	,[DistributionDB]

All comments and additions are welcome :-D.