troubleshooting Question

Trying to understand why my Index is so large

Avatar of curiouswebster
curiouswebsterFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008SQL
12 Comments6 Solutions182 ViewsLast Modified:
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



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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 6 Answers and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 6 Answers and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros