-- -- Table with 2.3 million rows
CREATE TABLE [dbo].[EventLog](
[EventID] [bigint] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[GroupID] [int] NULL,
[PostID] [bigint] NULL,
[FeedID] [int] NULL,
[EventChange] [int] NOT NULL,
[EventObject] [int] NOT NULL,
[EventType] [varchar](50) NOT NULL,
[EventSource] [int] NOT NULL,
[DataField] [nvarchar](max) NULL,
[ExtensionData] [xml] NULL,
[CreateDate] [datetime] NOT NULL,
[ParentEventID] [bigint] NULL,
[Deleted] [bit] NOT NULL,
CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
(
[EventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-- Non-clustered "covering index"
CREATE NONCLUSTERED INDEX [IX_EventLogNew_Cover1] ON [dbo].[EventLogNew]
(
[UserID] ASC,
[Deleted] ASC,
[CreateDate] ASC
)
INCLUDE ( [EventID],
[GroupID],
[PostID],
[FeedID],
[EventChange],
[EventObject],
[EventType],
[EventSource]) 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
-- Return IO Statistics
SET STATISTICS IO ON;
-- Clear out the procedure cache for instance
DBCC FREEPROCCACHE;
-- Don't do this on a production server!
DBCC DROPCLEANBUFFERS;
-- Run four identical stored procedures
-- against identical tables (except for index changes)
PRINT 'Query 1'; -- Clustered Index Scan
EXEC dbo.GetEventLogByUserID 137926;
PRINT 'Query 2'; -- Clustered Index Scan (compressed index)
EXEC dbo.GetEventLogCompressedByUserID 137926;
PRINT 'Query 3'; -- Index seek
EXEC dbo.GetEventLogNewByUserID 137926;
PRINT 'Query 4'; -- Index seek (compressed index)
EXEC dbo.GetEventLogNewCompressedByUserID 137926;
-- Statistics IO output
--Query 1 Clustered index scan against EventLog table
--Table 'EventLog'. Scan count 3, logical reads 23941, physical reads 176,
--read-ahead reads 23858, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Query 2 Clustered index scan against EventLogCompressed table
--Table 'EventLogCompressed'. Scan count 3, logical reads 5545, physical reads 45,
--read-ahead reads 5509, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Query 3 Index seek against EventLogNew
--Table 'EventLogNew'. Scan count 1, logical reads 3, physical reads 3,
--read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Query 4 Index seek against EventLogNewCompressed
--Table 'EventLogNewCompressed'. Scan count 1, logical reads 3, physical reads 2,
--read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Breaks down buffers used by current database
-- by object (table, index) in the buffer cache
SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName],
p.index_id, COUNT(*)/128 AS [buffer size(MB)],
COUNT(*) AS [buffer_count]
FROM sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors AS b
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p
ON a.container_id = p.hobt_id
WHERE b.database_id = DB_ID()
AND p.[object_id] > 100
GROUP BY p.[object_id], p.index_id
ORDER BY buffer_count DESC;
-- Buffer space results
--ObjectName index_id buffer size(MB) buffer_count
--EventLog 1 186 23855
--EventLogCompressed 1 43 5508
--EventLogNew 5 0 24
--EventLogNewCompressed 2 0 16
