To follow up with my previous post, Looking at Query IO stats, I thought I would add in one more performance notion that you are able to negotiate with your hosting provider (talking about majority, not specialists): SQL Server file groups.
What does additional file groups help solve? Many things, but the basic concept from a performance perspective is to let SQL Server use parallel processing by splitting the IO tasks over multiple threads. Even file groups on the same logical disk can improve throughput for a given query (all other variables held static).
Of course, if you do get to the point where this really is a necessity, hopefully you are doing well enough with your venture that you can get a more customized SQL Server environment; either way, the script below will help you take a look at your objects per file group.
Some of the basic concepts inside SQL Server file groups have not changed for a long time. Take, for example, the stored procedure sp_objectfilegroup, which has been around since SQL Server 7.0, and has a created date of 1996-08-30.
Here is the basic script:
select ds.name, o.name, o.type_desc
from sys.objects o
inner join sys.indexes i
on i.object_id = o.object_id
and o.is_ms_shipped = 0
and i.index_id < 2 –- excludes non-clustered indexes
inner join sys.data_spaces ds
on ds.data_space_id = i.data_space_id
If you want to build upon this to get information on the sizes of individual tables for planning, you can utilize the stored procedure sp_spaceused to get details on the space a table is using. I posted some detail on this back in May in post: Tables and Sizes.
I would be interested to hear from anyone that is using a hoster that is friendly to data intensive web applications; most host providers I have come across are geared heavily towards web and web platform offerings, but tend to skimp on features you can purchase database side. I have one that I am happy with for now, but if anyone else has an experience they would like to share, please feel free to comment or e-mail me.