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.