[dba-SQLServer] Indexes not all listed

John,
Sure, having the index on a separate file sitting on a flash drive will give
you the best performance.
Separating the data part and index part on different files residing on
different spindles is a best practice for performance because both parts can
then be read in parallel.
But in your case the data part won't even be touched if your queries are
indeed covered by the indexes. So in your case parallelism wouldn't be the
argument for separation - the argument would be the extraordinary speed (and
expense...) of the flash drive hosting the index.

Asger

-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af jwcolby
Sendt: 17. december 2009 20:29
Til: Discussion concerning MS SQL Server
Emne: Re: [dba-SQLServer] Indexes not all listed

Asger,

Thanks for that, I can do that!

Next question.  I am looking at doing a vertical partition of this table.
The table logically 
groups on a set of 84 fields (name / age / education etc, plus another set
of about 150 fields that 
are heavily used, and the rest of the fields rarely (but occasionally) used.

Thus I could split this thing into at least three tables, related one to
one.  In looking at it 
closely I might in fact "rearrange the columns" such that all of the heavily
used fields are in a 
single table (including the name / age / education) and place all the rest
into a single "other" table.

My question then is whether the indexes for each field of these vertical
split tables should be in 
the same file as the data fields, or split out.  I guess I still don't have
a handle on what part 
the data field plays.  If the table has a clustered index, and then an index
on each field, with the 
data in the index, at what point is the actual data back in the field of the
table ever used?  I 
thought that with the data in the index, if the index is used (and the index
/ field makes it a lot 
more likely) then I thought that the data itself was actually plucked out of
the index leaf.

If that is the case, then having the index on a separate database file
(without the table itself), 
and having that database file sitting on a flash drive, I would have the
smallest possible footprint 
to store on the flash.

John W. Colby
www.ColbyConsulting.com


Asger Blond wrote:
>> On that note, I do have a question, whether it is possible to specify
what
>> database files a specific index goes into.  It happens that this table
from
>> hell uses specific columns much more frequently than others, and some
>> columns (so far) not at all, or only once or twice.  If I could create
>> multiple data files, and then move the indexes for the first 200 fields
> into one file and the rest into another, then I could move that heavily
used
>> index file onto a flash and leave the rest on rotating media.
> 
> Yes, it's possible and IMO would be a good solution in your case.
> Like any object in a SQL Server database an index is created on a
filegroup,
> and if you haven’t specifically created filegroups for your database it is
> created on a filegroup called Primary.
> 
> You can add a new filegroup and specify a file for it this way:
> ALTER DATABASE YourDB ADD FILEGROUP YourNewFilegroup
> GO
> ALTER DATABASE YourDB ADD FILE (NAME = 'YourDB_IndexFile', 
> FILENAME = 'X:\YourFolder\YourDB_IndexFile.ndf',
> SIZE = 500GB)
> TO FILEGROUP YourNewFilegroup
> GO
> 
> You then create the indexes on that filegroup (effectively on the
specified
> file) this way:
> CREATE INDEX YourIndex ON YourTable(YourColumn) ON YourNewFilegroup
> GO
> 
> Asger

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com