Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
737 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
15732 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  
 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.08 seconds. Powered By: Snitz Forums 2000