Avatar of curiouswebster
curiouswebster
Flag for United States of America

asked on 

Trying to understand why my Index is so large

I ran this:
exec sp_spaceused 'Log'

against this table:
USE [Logging]
GO

/****** Object:  Table [dbo].[Log]    Script Date: 4/27/2017 4:53:34 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Log](
	[LogID] [int] IDENTITY(1,1) NOT NULL,
	[EventID] [int] NULL,
	[Priority] [int] NOT NULL,
	[Severity] [nvarchar](32) NOT NULL,
	[Title] [nvarchar](256) NOT NULL,
	[Timestamp] [datetime] NOT NULL,
	[MachineName] [nvarchar](32) NOT NULL,
	[AppDomainName] [nvarchar](512) NOT NULL,
	[ProcessID] [nvarchar](256) NOT NULL,
	[ProcessName] [nvarchar](512) NOT NULL,
	[ThreadName] [nvarchar](512) NULL,
	[Win32ThreadId] [nvarchar](128) NULL,
	[Message] [nvarchar](1500) NULL,
	[FormattedMessage] [ntext] NULL,
 CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED 
(
	[LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Open in new window




and got:

name: Log      
rows: 13481381                  
reserved: 55532040 KB      
data: 34530600 KB      
index_size: 20993832 KB      
unused: 7608 KB

I need to create a new index against the column timestamp (date/time) field, but first need to understand what I already have for indexes.

How can I view the contents of an Index?

Thanks
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
curiouswebster

8/22/2022 - Mon