SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Huge IndexSize on aspnet_users
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

FrankPeterSteinmeyer
Starting Member

Switzerland
3 Posts

Posted - 03/13/2013 :  06:54:02  Show Profile  Reply with Quote
Hi

We've got a SQL-2005-Database in the back of an asp.net-Application. We are using the SQLMembershipProvider to manage the Users.

Now the problem is, that the aspnet_users-table with only about 500 Users uses 88MB space (I guess it's primarily index-space) - which I find is a lot. The customer has a very expensive database-hosting-provider, so every MB counts.

My first question: is it normal that so little user-records produce such a large amount of index-data? Second: how can I reduce the space needed?

I have to admit that I'm not really a proficient sql-server-user. Usually I create the database and tables without much additional optimization.

Regards,
Frank

ahmeds08
Aged Yak Warrior

India
572 Posts

Posted - 03/13/2013 :  07:07:24  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
check if the table has a composite clustered index.
Go to Top of Page

FrankPeterSteinmeyer
Starting Member

Switzerland
3 Posts

Posted - 03/13/2013 :  08:17:15  Show Profile  Reply with Quote
quote:
Originally posted by ahmeds08

check if the table has a composite clustered index.



Hi ahmeds08

How can I do this?
Go to Top of Page

robvolk
Most Valuable Yak

USA
15654 Posts

Posted - 03/13/2013 :  11:27:13  Show Profile  Visit robvolk's Homepage  Reply with Quote
EXEC sp_help 'aspnet_users-table'

There will be output on the columns, indexes, constraints, and other information. Look in the indexes section for the clustered index and which columns it has.

Also look for text/ntext/image/varchar(max)/varbinary(max) columns, if it's storing large data in such columns this could be what's taking up the space.
Go to Top of Page

FrankPeterSteinmeyer
Starting Member

Switzerland
3 Posts

Posted - 03/13/2013 :  12:19:30  Show Profile  Reply with Quote
quote:
Originally posted by robvolk

EXEC sp_help 'aspnet_users-table'



Thanks a lot for your help, I will check this!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000