Asger Blond
ab-mi at post3.tele.dk
Thu Dec 17 17:34:19 CST 2009
AND you use OR in your queries... SO the ultimate-extreme-surplus-fast way would be 500+ flash drives, one for each of your indexes... Question is: can you outrun your creditors with this solution... Guess not, but one flash drive hosting all 500+ indexes would be much better than placing the indexes on the same normal HD as your data file. Asger -----Oprindelig meddelelse----- Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Asger Blond Sendt: 17. december 2009 22:49 Til: 'Discussion concerning MS SQL Server' Emne: Re: [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 _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com