Re: Filegroup recommendation
From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 01/21/05
- Next message: Eric Cárdenas [MSFT]: "RE: win server 2000 crash with blue screen after update sqlserver from 6.5 to 2000"
- Previous message: Eric Cárdenas [MSFT]: "RE: 12/07/04 13:54:10 Stack Overflow Dump not possible - Exception c00"
- In reply to: Michael C#: "Re: Filegroup recommendation"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 20 Jan 2005 22:29:20 -0500
Have one for me too<g>.
-- Andrew J. Kelly SQL MVP "Michael C#" <xyz@abcdef.com> wrote in message news:ly_Hd.8296$mF.6862@fe08.lga... > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > news:%23BNKsB1$EHA.3472@TK2MSFTNGP14.phx.gbl... >> Just wanted to clarify some things you mentioned. >> >>> Separate filegroups can give you better performance. I would separate >>> out >>> the data files into a filegroup, non-clustered indexes into their own >>> filegroup, and the transaction logs into their own filegroup. >>> >>> The advantage to this comes primarily from a system with multiple >>> processors >>> and multiple hard drives. In a multiple-processor system, SQL Server >>> can >>> access tables and associated non-clustered indexes in parallel. >> >> Actually with SQL 2000 you do not need separate files in order for SQL >> Server to read them with multiple threads. That was true in SQL 7.0 but >> no longer so in 2000. Even with 7.0 you don't need multiple filegroups, >> just multiple files. With a smaller db and a single drive array there is >> no performance gain by creating separate files. It is more of a >> maintenance aspect or if you plan on adding another drive array in the >> future it may make things easier. >> > > You're absolutely right. I was typing faster than I was thinking. Sorry > about that. You're absolutely right about the separate drives; and > ideally, separate controllers for those drives. I tried to bring that > point through. The physical non-linear movement of the hard drive heads is > a big performance killers, especially when you're mixing the non-linear > data read and write operations with constant jumps for the linear log > write operations on one drive. > > Having multiple parallel threads on separate processors doesn't help as > much if your single hard drive is thrashing like crazy. Again, for small > databases this might be alleviated somewhat by caching. But I think it > depends primarily on the ratio of read operations vs. write operations in > your particular database (I think I've seen the 'average' quoted at around > 7:1 [reads:writes] somewhere). > >> >>> filegroup, and the transaction logs into their own filegroup. >> >> Log files don't actually have a filegroup, they are simply individual >> files. >> > > Again, typing faster than I was thinking. I was actually on my way out to > the local bar with my co-workers when I decided to try to get an answer > out to the OP on this one. :) Beer on the brain. The log files should, > whenever possible, be on their own hard drive was the point I was trying > to get across. > > Thanks for the corrections, > Michael C#, MCDBA > >> >> >> -- >> Andrew J. Kelly SQL MVP > >
- Next message: Eric Cárdenas [MSFT]: "RE: win server 2000 crash with blue screen after update sqlserver from 6.5 to 2000"
- Previous message: Eric Cárdenas [MSFT]: "RE: 12/07/04 13:54:10 Stack Overflow Dump not possible - Exception c00"
- In reply to: Michael C#: "Re: Filegroup recommendation"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
