Moving 400k records per month into and out of tables shouldn't cause all that many problems from a performance perspective. As SQL removes all the data from pages those pages will be reused for something else.
You shouldn't be getting inserts or updates in the middle of pages all that often as SQL should just be putting the new rows at the end of the table and deleting the old rows from where ever they are. (I'm assuming you are just deleting the oldest records when you delete records.)
If it is that much of a worry, I would put the historical tables into one filegroup and the other tables into another file group.
What you're trying to do would leave the database in a (transactionally) inconsistent state, hence it isn't possible.
The Partial Database Availability whitepaper is a useful reference guide and includes an example of how to check whether a particular table or file is online. If your data access were through stored procedures, you could relatively easily incorporate that check.
One alternative (but somewhat hacky) approach that might be worth a look in your scenario would be to hide the table and replace it with a view.
-- NB: SQLCMD script
:ON ERROR EXIT
:setvar DatabaseName "TestRename"
:setvar FilePath "D:\MSSQL\I3\Data\"
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
SET NOCOUNT ON;
GO
USE master;
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'$(DatabaseName)')
DROP DATABASE $(DatabaseName)
GO
CREATE DATABASE $(DatabaseName)
ON PRIMARY
(
NAME = N' $(DatabaseName)'
, FILENAME = N'$(FilePath)$(DatabaseName).mdf'
, SIZE = 5MB
, MAXSIZE = UNLIMITED
, FILEGROWTH = 1MB
)
, FILEGROUP [FG1] DEFAULT
(
NAME = N' $(DatabaseName)_FG1_File1'
, FILENAME = N'$(FilePath)$(DatabaseName)_FG1_File1.ndf'
, SIZE = 1MB
, MAXSIZE = UNLIMITED
, FILEGROWTH = 1MB
)
, FILEGROUP [FG2] CONTAINS FILESTREAM
(
NAME = N'$(DatabaseName)_FG2'
, FILENAME = N'$(FilePath)Filestream'
)
LOG ON
(
NAME = N'$(DatabaseName)_log'
, FILENAME = N'$(FilePath)$(DatabaseName)_log.ldf'
, SIZE = 1MB
, MAXSIZE = UNLIMITED
, FILEGROWTH = 1MB
)
GO
USE $(DatabaseName);
GO
CREATE TABLE [dbo].[BinaryContent](
[BinaryContentID] [int] IDENTITY(1,1) NOT NULL
, [FileName] [varchar](50) NOT NULL
, [BinaryContentRowGUID] [uniqueidentifier] ROWGUIDCOL UNIQUE DEFAULT (NEWSEQUENTIALID()) NOT NULL
, [FileContentBinary] VARBINARY(max) FILESTREAM NULL
) ON [PRIMARY] FILESTREAM_ON [FG2]
GO
-- Insert test rows
INSERT
dbo.BinaryContent
(
[FileName]
, [FileContentBinary]
)
VALUES
(
CAST(NEWID() AS VARCHAR(36))
, CAST(REPLICATE(NEWID(), 100) AS VARBINARY)
);
GO 100
USE master;
GO
-- Take FILESTREAM filegroup offline
ALTER DATABASE $(DatabaseName)
MODIFY FILE (NAME = '$(DatabaseName)_FG2', OFFLINE)
GO
USE $(DatabaseName);
GO
-- Rename table to make way for view
EXEC sp_rename 'dbo.BinaryContent', 'BinaryContentTable', 'OBJECT';
GO
-- Create view to return content from table but with NULL FileContentBinary
CREATE VIEW dbo.BinaryContent
AS
SELECT
[BinaryContentID]
, [FileName]
, [BinaryContentRowGUID]
, [FileContentBinary] = NULL
FROM
[dbo].[BinaryContentTable];
GO
-- Check results as expected
SELECT TOP 10
*
FROM
dbo.BinaryContent;
GO
Best Answer
To answer your questions in order:
You can check for the physical fragmentation of the disk by going to the drive, right clicking on it and selecting tools, then defragment. Or close to that, depending on which version of the OS you are using your specific steps may be different. But you will only be able to see the fragmentation on a volume level, not specific files.
For the most part, no, you don't need to worry about physical fragmentation of your files if you are using a SAN (unless the SAN is dedicated to this one purpose). Due to how most SANs are used, your IO is going to be random; which is fine since you should have LOTS of spindles to spread the load out.
Larger physical database files would prevent fragmentation, but again, you shouldn't worry about that if you are on a SAN. The more important thing to make sure of is that when the file grows, it grows at a set size (rather than percentage) and that size is sufficient to handle a good amount of growth. You would want to grow the file to what you estimate the required size will be in X days and THEN defragment the volume.
This link - Brent Ozar goes into more detail and should include some more information you can use to either prove or disprove that the SAN is the reason for your bottleneck.