This article is half-done without your Comment! *** Please share your thoughts via Comment ***
If you are finding most top 10 steps for SQL Server Performance tuning, you could get one of the items in the list is “multiple file-groups and various data-files increases the query performance”.This is correct.
You can move your fat table from master or primary data group to another custom file group.
It gives you more performance and very easy to do maintenance.
I am also asking this question to SQL DBA like “how to move a table from one File Group to another File Group?” and most of the times I am getting answers like “Create new file group -> Replicate table schema -> Move data -> Drop old table”.
The above answer is right, but instead of this great exercise, we can move a table to new file group by creating a clustered index only.
Here, You can access full demonstration.
Create a sample table:
|
1 2 3 4 5 6 7 |
CREATE TABLE tbl_Students ( StudID INT IDENTITY(1,1) ,Name VARCHAR(10) ,CONSTRAINT pk_tbl_Students_StudID PRIMARY KEY(StudID) ) GO |
Insert few dummies record:
|
1 2 |
INSERT INTO tbl_Students(Name) VALUES ('Anvesh') GO 100000 |
Check the table Information:
|
1 2 |
EXEC SP_HELP 'tbl_Students' GO |
Alter a database to add new file group:
|
1 2 |
ALTER DATABASE Database_Name ADD FILEGROUP New_FileGroup GO |
Add new data file (.ndf):
|
1 2 3 4 5 6 7 8 |
ALTER DATABASE Database_Name ADD FILE ( NAME = 'New_DataFile', FILENAME = 'C:\TempSQLData\New_DataFile.ndf', SIZE = 100000KB, FILEGROWTH = 65000KB ) TO FILEGROUP New_FileGroup GO |
Create a new CLUSTERED index on table within created new file group:
|
1 2 3 4 5 6 7 |
CREATE UNIQUE CLUSTERED INDEX pk_tbl_Students_StudID ON tbl_Students(StudID) WITH ( DROP_EXISTING = ON ) ON New_FileGroup GO |
Check the table information:
|
1 2 |
EXEC SP_HELP 'tbl_Students' GO |
If you need a space of old table, you can shrink your database:
|
1 2 |
DBCC SHRINKFILE ('Database_Name' , 0) GO |
