Multi data files, same drive/FG perf question

Hello. Say you have a SQL 7 database with 1 MDF and 3 NDF
files... all in the same filegroup and drive (and
folder). Beyond the question of WHY it's like that, other
then that's the way thigns were done in that area back in
the 6.5 device days... question...
Since SQL Server will split the data and indexes across
these 4 files evenly, I'd think things would perform
slower then if these were one file. Wouldn't this be a
type of fragmentation since the data/index is not in a
contigous disk area? THanks, BruceThe MDF is the Primary data file and the NDFs are Secondary files. There are
numerous reasons for this type of design. Performance, Securtiy.
1 example might be that you have some lookup tables that are all static, to
ensure nobody is able to change the data you can create a Read-Only data
file. All the lookup tables are then placed on the Read-Only data file
--
HTH
Ryan Waight, MCDBA, MCSE
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:5e2701c37d19$5668d0b0$a601280a@.phx.gbl...
> Hello. Say you have a SQL 7 database with 1 MDF and 3 NDF
> files... all in the same filegroup and drive (and
> folder). Beyond the question of WHY it's like that, other
> then that's the way thigns were done in that area back in
> the 6.5 device days... question...
> Since SQL Server will split the data and indexes across
> these 4 files evenly, I'd think things would perform
> slower then if these were one file. Wouldn't this be a
> type of fragmentation since the data/index is not in a
> contigous disk area? THanks, Bruce|||If the IO subsystem can handle the extra IO, it is possible for things to
run faster... SQL will automatically fire of separate, parallel IO threads
to handle queries, one for each of the data files... Even if you do not get
any performance improvement now, setting up multiple files in a filegroup
will allow you ( at a later time) install a new raid array, backup and
restore ( or sp+_attach) the database into the new raid array and get IO
parallelism...
So this setup could be the result of an *old way* thinker doing things the
6.5 way, OR a forward thinker planning for the future..
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:5e2701c37d19$5668d0b0$a601280a@.phx.gbl...
> Hello. Say you have a SQL 7 database with 1 MDF and 3 NDF
> files... all in the same filegroup and drive (and
> folder). Beyond the question of WHY it's like that, other
> then that's the way thigns were done in that area back in
> the 6.5 device days... question...
> Since SQL Server will split the data and indexes across
> these 4 files evenly, I'd think things would perform
> slower then if these were one file. Wouldn't this be a
> type of fragmentation since the data/index is not in a
> contigous disk area? THanks, Bruce

MSSQL.3

On one server the report services folder exists here;
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer
On another server it exists here;
C:\Program Files\Microsoft SQL Server\MSSQL.4\Reporting
Services\ReportServer
Why MSSQL.3 on one and MSSQL.4 on the other?
Thanx in advance ,
GregOn Aug 29, 11:32 am, "SurferJoe" <Surfer...@.newsgroup.nospam> wrote:
> On one server the report services folder exists here;
> C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
> Services\ReportServer
> On another server it exists here;
> C:\Program Files\Microsoft SQL Server\MSSQL.4\Reporting
> Services\ReportServer
> Why MSSQL.3 on one and MSSQL.4 on the other?
> Thanx in advance ,
> Greg
This is common when you have a different number of SQL Server Services
installed (i.e., if you have the SQL Server Database Engine,
Integration Services and Reporting Services installed on one server,
SSRS might be in MSSQL.3; whereas, if you have the SQL Server Database
Engine, Integration Services, Analysis Services and Reporting Services
installed on the other server, SSRS might be in MSSQL.4). Hope this
helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

MSSQL.1, .2, .3....?

For some reason Sql 2005, by default, creates different folder paths (such as MSSQL.1, MSSQL,2, etc) for different services.

1. Is there a utiltiy that can tell me what service is stored in what folder?

2. I noticed that Integraiton Servcies does not seem to have a seperate ("MSSQL.x") folder. Why?

Any help much appreciated,

Barkingdog

"Is there a utiltiy that can tell me what service is stored in what folder?"

Yes, SQL Server Configuration Manager. Select SQL Server 2005 Services after you start the tool. Right-click on a particular service and select properties. Select the Service tab and the path is in the Binary Path attribute.

" I noticed that Integraiton Servcies does not seem to have a seperate ("MSSQL.x") folder. Why?"

My guess is that the sql directory structure is this way because SSIS does not require several other OS files as do the other services. But thats a guess. It is what it is :)

HTH,

Derek