Asger Blond
ab-mi at post3.tele.dk
Thu Dec 17 13:08:38 CST 2009
>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 -----Oprindelig meddelelse----- Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af jwcolby Sendt: 17. december 2009 16:15 Til: Discussion concerning MS SQL Server Emne: Re: [dba-SQLServer] Indexes not all listed Francisco, > Flash drives do not do well with very large files. Why do you say this? I have never heard anything like this ever espoused. I have seen file TRANSFER numbers that display this, but transferring files from one disk to another is fundamentally different from reading pieces of a large file for internal processing by a program (database). From a technology perspective flash drives are a page at a time block read system interfaced to a very large cache ram interfaced to an SATA disk controller. Because of this, flash drives have two huge advantages over rotating media - (random) access time and IOPS. Getting at any block of data occurs at the speed of the electronics, but is typically around 100 nanoseconds. Exactly because of this phenomena, the number of IOs that can be processed in any given second skyrockets. Basically IOPs are dispatched instead of being queued waiting for the media to rotate and heads to move. Examine the numbers for flash vs rotating and the REAL IOPS go from the low hundreds to the mid thousands. That is a LOT more data accessed per second. In fact flash drives rapidly become "disk controller bound" precisely because they can shovel data faster than the SATA interface can move it. Even so, rotating media only uses about 10% of the capacity of the SATA interface, so if you turn around and saturate the interface with a flash drive you are getting 10 times the data moved. That is impressive and something worth shooting for. From what I have read, databases absolutely SCREAM when placed on flash drives, in fact some of the big databases are moving there exactly because of the performance gains. Databases need exactly this kind of "read a block from over here" technology that flash drives just naturally excel at. From what I am reading, placing your databases on flash is getting close to "memory resident". In my case, my databases are not transactional, they just sit there handling read requests. As a result even the known issues of wear should not be an issue. Set the db files up on a standard disk and then copy them to a flash drive, sit back and watch it fly. As for the details, what I would LIKE to do is make a raid 0 array using my intelligent raid controllers. If I could get 4 or 5 (or more) "spindles" of flash raid zero... Now back to reality. I haven't done this yet because of cost, I am money bound. ;) When I do, I will do some real life testing and publish results here. 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. John W. Colby www.ColbyConsulting.com Francisco Tapia wrote: > Flash drives do not do well with very large files. Flash drives excell > at random access wich for SQL makes them good with both tempdb and > transaction log files because they both have random inserts and reads. > To improve on what you have you will want to double the number of hdd > spindles. The more spindles you have the bettertheperformance. We have > an enterprise database riding on 40 spindles, this in turn is able to > crank out searches for a 1.5 terabyte db in usually 10 seconds to ??? > Depending on the query my developers need. We have discussed as an > architecture change that we may soon upgrade to 80 spindles. _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com