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

SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

how did you get those numbers? which tool/qry?

Vitor Montalvão

Please script the indexes from the table and post it here so we can check it for you.

curiouswebster

ASKER

I ran this:
exec sp_spaceused 'Log'

to generate the sizes. An index size of 20,993,832 seems rather huge for a LogID plus a row pointer for 13.4 million rows.

> Please script the indexes from the table and post it here so we can check it for you.

I do not know how to do this...I have really needed to see the contents of the index but do not know how.

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

curiouswebster

ASKER

I am planning on clustering the index based on your suggestions. But I have no idea how to view the contents of an index file or to discover why my index_size was: 20,993,832 KB

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

curiouswebster

ASKER

> that's the important part of clustering the table properly, to avoid reading rows you never intent to display.

OK, that is the first time I have heard why clustering will help.

Does the existing index stay intact even as the clustered index gets created?

> long column means a lot of rows? is there are 13.4 million rows in the Log table, shouldn't that mean there are exactly the same number of index values?

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

curiouswebster

ASKER

thanks