CODEWITHSUNDEEP
sql-serverasp.net-membership
AndreasN
AndreasN

Reputation: 2897

aspnet_Users table with huge indexsize

We have an aspnet_Users table from aspnet membership table that shows up with almost 18 gb index size.

rows 251172
datasize 56472 KB
indexsize 17800536 KB

This is just the standard aspnet membership table, but we do have an other table with a foreign key to this table (userid column).

Anyone seen this problem before?

How can i reduce the index size?

the aspnet_Users table is defined as

CREATE TABLE [dbo].[aspnet_Users](
    [ApplicationId] [uniqueidentifier] NOT NULL,
    [UserId] [uniqueidentifier] NOT NULL,
    [UserName] [nvarchar](256) NOT NULL,
    [LoweredUserName] [nvarchar](256) NOT NULL,
    [MobileAlias] [nvarchar](16) NULL,
    [IsAnonymous] [bit] NOT NULL,
    [LastActivityDate] [datetime] NOT NULL,
PRIMARY KEY NONCLUSTERED 
(
    [UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [aspnet_Users_Index] ON [dbo].[aspnet_Users] 
(
    [ApplicationId] ASC,
    [LoweredUserName] ASC
)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
CREATE NONCLUSTERED INDEX [aspnet_Users_Index2] ON [dbo].[aspnet_Users] 
(
    [ApplicationId] ASC,
    [LastActivityDate] ASC
)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

ALTER TABLE [dbo].[aspnet_Users] ADD  DEFAULT (newid()) FOR [UserId]
GO

ALTER TABLE [dbo].[aspnet_Users] ADD  DEFAULT (NULL) FOR [MobileAlias]
GO

ALTER TABLE [dbo].[aspnet_Users] ADD  DEFAULT ((0)) FOR [IsAnonymous]
GO

ALTER TABLE [dbo].[aspnet_Users]  WITH NOCHECK ADD FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
GO

Upvotes: 1

Views: 440

Answers (1)

gbn
gbn

Reputation: 432431

This works out at 70k per row though which is more then a single extent (8 pages, 64k) which implies massive fragmentation.

Have you ever run index maintenance on it? Run this and see what happens

ALTER INDEX ALL ON aspnet_Users REBUILD

Alternatively, has the table ever been extended with a LOB (pictures, XML etc) column? Or has someone added dozens of indexes? So please add the actual in-use table definition

Edit: remove the index on LastActivityDate, or change it to smalldatetime to keep minute accuracy, or only update it if changed more then xx seconds/minutes

Upvotes: 1

Related Questions