Would a filegroup with many files located on the same drive outperform if it had just one file on SQL Server 2008 R2?

I've a couple of questions regarding filegroups and their files (.ndf).

1. A filegroup with many files is faster than a filegroup with just one file? (All files are located on the same drive, i.e., same access time for their data)
开发者_Go百科
2. If (1) is false. A filegroup with many files spread over drives A and B is faster than one with just one file on drive A or B? (let's suppose drives A and B are of the same model)

---

Usually, you'd have one file per filegroup, one filegroup per disk set/volume/controller

With multiple files per filegroup you have proportional fill which means IO may not be distibuted as you expect. Whether same disk or different disks

The "one thread per file" concept is somewhat of a myth

---

The only way to verify any opinion is with cold, hard statistical information that you create yourself. Use Sticks. If you have a few USB 3 Ports on your computer, go and buy 4 USB Memory Sticks of 4-GB. May sure they are identical. Create a database with the LDF files on one Stick, and the PRIMARY filegroup on another. Then, create two more FILEGROUPS, one on each remaining stick. Then, add files to each FILEGROUP. It is suggested by CubeSpark to add one file per CORE, up to 4 files per FILEGROUP. If you want to test 8 files, then add 8. Then, add tables to the FILEGROUPS, and load them with massive amounts of data. Create some timings. Don't forget to tweak the file settings (initial size, growth rate). Maybe you can play with this code. You will need to change the path for your Sticks, as the mount points will be different (the drive letters). Have fun.

USE master;
GO

IF DB_ID('CubeSpark') IS NOT NULL
BEGIN
    DROP DATABASE [CubeSpark]
END

--  GB to start?
CREATE DATABASE [CubeSpark] CONTAINMENT = NONE
 ON PRIMARY 
  -- Proportional fill. This must NOT be used as the default FILEGROUP. PLEASE DO NOT CHANGE THAT! 
  -- PRIMARY is USED ONLY FOR SYSTEM METATDATA, NOT USER DATA!!!!
( NAME = N'CubeSpark_SYSTEM_0', FILENAME = N'C:\OLTP_DATA\CubeSpark\CubeSpark_sys_0.mdf' , SIZE = 10MB , MAXSIZE = 40MB, FILEGROWTH = 5MB), 
( NAME = N'CubeSpark_SYSTEM_1', FILENAME = N'C:\OLTP_DATA\CubeSpark\CubeSpark_sys_1.mdf' , SIZE = 10MB , MAXSIZE = 40MB, FILEGROWTH = 5MB), 

 FILEGROUP [FACTS] DEFAULT 
 -- 60 GB to start.
( NAME = N'CubeSpark_FACTS_0', FILENAME = N'C:\OLTP_DATA\CubeSpark\CubeSpark_FACTS_0.ndf' , SIZE = 1GB , MAXSIZE = UNLIMITED, FILEGROWTH = 100MB), 
( NAME = N'CubeSpark_FACTS_1', FILENAME = N'C:\OLTP_DATA\CubeSpark\CubeSpark_FACTS_1.ndf' , SIZE = 1GB , MAXSIZE = UNLIMITED, FILEGROWTH = 100MB), 
( NAME = N'CubeSpark_FACTS_2', FILENAME = N'C:\OLTP_DATA\CubeSpark\CubeSpark_FACTS_2.ndf' , SIZE = 1GB , MAXSIZE = UNLIMITED, FILEGROWTH = 100MB), 
( NAME = N'CubeSpark_FACTS_3', FILENAME = N'C:\OLTP_DATA\CubeSpark\CubeSpark_FACTS_3.ndf' , SIZE = 1GB , MAXSIZE = UNLIMITED, FILEGROWTH = 100MB), 
( NAME = N'CubeSpark_FACTS_4', FILENAME = N'C:\OLTP_DATA\CubeSpark\CubeSpark_FACTS_4.ndf' , SIZE = 1GB , MAXSIZE = UNLIMITED, FILEGROWTH = 100MB), 
( NAME = N'CubeSpark_FACTS_5', FILENAME = N'C:\OLTP_DATA\CubeSpark\CubeSpark_FACTS_5.ndf' , SIZE = 1GB , MAXSIZE = UNLIMITED, FILEGROWTH = 100MB), 

 FILEGROUP [MATERIALIZED]
 -- 1 GB to start - which is sized VERY large given materialized views data as of May 25th.
( NAME = N'CubeSpark_MATERIALIZED_0', FILENAME = N'C:\OLTP_DATA\CubeSpark\CubeSpark_MATERIALIZED_0.ndf' , SIZE = 100MB , MAXSIZE = UNLIMITED, FILEGROWTH = 25MB), 
( NAME = N'CubeSpark_MATERIALIZED_1', FILENAME = N'C:\OLTP_DATA\CubeSpark\CubeSpark_MATERIALIZED_1.ndf' , SIZE = 100MB , MAXSIZE = UNLIMITED, FILEGROWTH = 25MB), 
( NAME = N'CubeSpark_MATERIALIZED_2', FILENAME = N'C:\OLTP_DATA\CubeSpark\CubeSpark_MATERIALIZED_2.ndf' , SIZE = 100MB , MAXSIZE = UNLIMITED, FILEGROWTH = 25MB), 
( NAME = N'CubeSpark_MATERIALIZED_3', FILENAME = N'C:\OLTP_DATA\CubeSpark\CubeSpark_MATERIALIZED_3.ndf' , SIZE = 100MB , MAXSIZE = UNLIMITED, FILEGROWTH = 25MB), 

 FILEGROUP [INDICES] -- non-clustered
 -- 12 GB to start.
( NAME = N'CubeSpark_INDICES_0', FILENAME = N'C:\OLTP_DATA\CubeSpark\CubeSpark_INDICES_0.ndf' , SIZE = 1GB , MAXSIZE = UNLIMITED, FILEGROWTH = 100MB), 
( NAME = N'CubeSpark_INDICES_1', FILENAME = N'C:\OLTP_DATA\CubeSpark\CubeSpark_INDICES_1.ndf' , SIZE = 1GB , MAXSIZE = UNLIMITED, FILEGROWTH = 100MB), 
( NAME = N'CubeSpark_INDICES_2', FILENAME = N'C:\OLTP_DATA\CubeSpark\CubeSpark_INDICES_2.ndf' , SIZE = 1GB , MAXSIZE = UNLIMITED, FILEGROWTH = 100MB), 
( NAME = N'CubeSpark_INDICES_3', FILENAME = N'C:\OLTP_DATA\CubeSpark\CubeSpark_INDICES_3.ndf' , SIZE = 1GB , MAXSIZE = UNLIMITED, FILEGROWTH = 100MB), 

-- 4 GB to start.
 FILEGROUP [SPOKES] -- 40 GB to Start
( NAME = N'CubeSpark_DIMS_0', FILENAME = N'C:\OLTP_DATA\CubeSpark\CubeSpark_DIMS_0.ndf' , SIZE = 1GB , MAXSIZE = UNLIMITED, FILEGROWTH = 100MB), 
( NAME = N'CubeSpark_DIMS_1', FILENAME = N'C:\OLTP_DATA\CubeSpark\CubeSpark_DIMS_1.ndf' , SIZE = 1GB , MAXSIZE = UNLIMITED, FILEGROWTH = 100MB), 
( NAME = N'CubeSpark_DIMS_2', FILENAME = N'C:\OLTP_DATA\CubeSpark\CubeSpark_DIMS_2.ndf' , SIZE = 1GB , MAXSIZE = UNLIMITED, FILEGROWTH = 100MB), 
( NAME = N'CubeSpark_DIMS_3', FILENAME = N'C:\OLTP_DATA\CubeSpark\CubeSpark_DIMS_3.ndf' , SIZE = 1GB , MAXSIZE = UNLIMITED, FILEGROWTH = 100MB)

 LOG ON -- 8 GB to start. Log files fill sequentially, not in parallel
( NAME = N'CubeSpark_log_0', FILENAME = N'C:\OLTP_TLOG\CubeSpark\CubeSpark_log_0.ldf' , SIZE = 2GB , MAXSIZE = UNLIMITED, FILEGROWTH = 100MB ), 
( NAME = N'CubeSpark_log_1', FILENAME = N'C:\OLTP_TLOG\CubeSpark\CubeSpark_log_1.ldf' , SIZE = 2GB , MAXSIZE = UNLIMITED, FILEGROWTH = 100MB ),
( NAME = N'CubeSpark_log_2', FILENAME = N'C:\OLTP_TLOG\CubeSpark\CubeSpark_log_2.ldf' , SIZE = 2GB , MAXSIZE = UNLIMITED, FILEGROWTH = 100MB ),
( NAME = N'CubeSpark_log_3', FILENAME = N'C:\OLTP_TLOG\CubeSpark\CubeSpark_log_3.ldf' , SIZE = 2GB , MAXSIZE = UNLIMITED, FILEGROWTH = 100MB )
GO

ALTER DATABASE [CubeSpark] SET RECOVERY SIMPLE 
ALTER DATABASE [CubeSpark] SET COMPATIBILITY_LEVEL = 110
ALTER DATABASE [CubeSpark] SET ANSI_NULL_DEFAULT ON 
ALTER DATABASE [CubeSpark] SET ANSI_NULLS ON 
ALTER DATABASE [CubeSpark] SET ANSI_PADDING ON 
ALTER DATABASE [CubeSpark] SET ANSI_WARNINGS OFF 
ALTER DATABASE [CubeSpark] SET ARITHABORT ON 
ALTER DATABASE [CubeSpark] SET NUMERIC_ROUNDABORT OFF 
ALTER DATABASE [CubeSpark] SET CONCAT_NULL_YIELDS_NULL ON 
ALTER DATABASE [CubeSpark] SET QUOTED_IDENTIFIER ON 
ALTER DATABASE [CubeSpark] SET DATE_CORRELATION_OPTIMIZATION ON 
ALTER DATABASE [CubeSpark] SET AUTO_CLOSE OFF 
ALTER DATABASE [CubeSpark] SET AUTO_CREATE_STATISTICS ON 
ALTER DATABASE [CubeSpark] SET AUTO_UPDATE_STATISTICS ON 
ALTER DATABASE [CubeSpark] SET AUTO_SHRINK OFF 
ALTER DATABASE [CubeSpark] SET CURSOR_CLOSE_ON_COMMIT OFF 
ALTER DATABASE [CubeSpark] SET CURSOR_DEFAULT GLOBAL 
ALTER DATABASE [CubeSpark] SET RECURSIVE_TRIGGERS OFF 
ALTER DATABASE [CubeSpark] SET DISABLE_BROKER 
ALTER DATABASE [CubeSpark] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
ALTER DATABASE [CubeSpark] SET TRUSTWORTHY OFF 
ALTER DATABASE [CubeSpark] SET ALLOW_SNAPSHOT_ISOLATION OFF 
ALTER DATABASE [CubeSpark] SET PARAMETERIZATION SIMPLE 
ALTER DATABASE [CubeSpark] SET READ_COMMITTED_SNAPSHOT OFF 
ALTER DATABASE [CubeSpark] SET HONOR_BROKER_PRIORITY OFF 
ALTER DATABASE [CubeSpark] SET PAGE_VERIFY CHECKSUM  
ALTER DATABASE [CubeSpark] SET DB_CHAINING OFF 
ALTER DATABASE [CubeSpark] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
ALTER DATABASE [CubeSpark] SET TARGET_RECOVERY_TIME = 0 SECONDS 
ALTER DATABASE [CubeSpark] SET  MULTI_USER 
ALTER DATABASE [CubeSpark] SET  READ_WRITE 
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) 
BEGIN
    EXEC [CubeSpark].[dbo].[sp_fulltext_database] @action = 'enable'
END
GO