Scribd
Upload
23 views

C2-SQL Server Storage Architecture

This document discusses SQL Server storage architecture including data storage, filegroups, filestream filegroups, memory-optimized filegroups, and file and filegroup maintenance. It also covers log maintenance, shrinking files, and reducing log fragmentation.

Uploaded by

Quyên Trần Nguyễn Dịu
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
23 views

C2-SQL Server Storage Architecture

This document discusses SQL Server storage architecture including data storage, filegroups, filestream filegroups, memory-optimized filegroups, and file and filegroup maintenance. It also covers log maintenance, shrinking files, and reducing log fragmentation.

Uploaded by

Quyên Trần Nguyễn Dịu
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 7

Thực hành SQL Server Storage Architecture

Data Storage
Filegroups
Listing 6-1. SQL Server Round-Robin Allocation

USE Master
GO
--Create a database with three files in the primary filegroup.
CREATE DATABASE TEST
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'Test', FILENAME = N'D:\Data\SqlServer\TEST\TEST.mdf'),
( NAME = N'Test_File2',FILENAME = N'D:\Data\SqlServer\TEST\test_file2.ndf'),
( NAME = N'Test_File3',FILENAME = N'D:\Data\SqlServer\TEST\test_File3.ndf')
LOG ON
( NAME = N'Test_log',FILENAME = N'D:\Data\SqlServer\TEST\test_log.ldf');
GO

IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name =
N'PRIMARY')
ALTER DATABASE TEST MODIFY FILEGROUP [PRIMARY] DEFAULT;
GO

USE TEST
GO
--Create a table in the new database. The table contains a wide, fixed-length column
--to increase the number of allocations.
CREATE TABLE dbo.RoundRobinTable
(
ID INT IDENTITY PRIMARY KEY,
DummyTxt NCHAR(1000),
);
GO
--Create a Numbers table that will be used to assist the population of the table.
DECLARE @Numbers TABLE
(
Number INT
)
--Populate the Numbers table.
;WITH CTE(Number)
AS
(
SELECT 1 Number
UNION ALL
SELECT Number +1
FROM CTE
WHERE Number <= 99
)
INSERT INTO @Numbers
SELECT *
FROM CTE;

--Populate the example table with 100 rows of dummy text.


INSERT INTO dbo.RoundRobinTable
SELECT 'DummyText'
FROM @Numbers a
CROSS JOIN @Numbers b;

--Select all the data from the table, plus the details of the row’s physical location.
--Then group the row count.
--by file ID
SELECT b.file_id, COUNT(*) AS [RowCount]
FROM
(
SELECT ID, DummyTxt, a.file_id
FROM dbo.RoundRobinTable
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) a
) b
GROUP BY b.file_id;

FILESTREAM Filegroups
Listing 6-2. Adding a FILESTREAM Filegroup
ALTER DATABASE TEST ADD FILEGROUP [Test_FS_FG] CONTAINS FILESTREAM;
GO
ALTER DATABASE TEST ADD FILE ( NAME = N'Test_FA_File1',
FILENAME = N'D:\Data\SqlServer\TEST\Test_FA_File1' )
TO FILEGROUP [Test_FS_FG];
GO

Listing 6-3. Creating a Table with FILESTREAM Data


USE Test
GO
CREATE TABLE dbo.FilestreamExample
(
ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
PictureDescription NVARCHAR(500),
Picture VARBINARY(MAX) FILESTREAM
);
GO
INSERT INTO FilestreamExample
SELECT NEWID(), 'Figure 6-1. Diagram showing the SQL Server storage
hierachy.', * FROM OPENROWSET(BULK N'D:\Data\SqlServer\Image\DAccess.jpg',
SINGLE_BLOB)

AS MyAlias;

Listing 6-4. Setting the Nontransactional Access Level


ALTER DATABASE TEST
SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL,
DIRECTORY_NAME = N'Test_FileTable' );

Listing 6-5. Creating a FileTable


USE TEST
GO
CREATE TABLE dbo.FileTest_test AS FILETABLE
WITH
(
FILETABLE_DIRECTORY = 'Test_FileTable',
FILETABLE_COLLATE_FILENAME = database_default
);

Memory-Optimized Filegroups
Listing 6-6. Adding an In-Memory Filegroup and Container
ALTER DATABASE TEST ADD FILEGROUP [Test_InMemory] CONTAINS
MEMORY_OPTIMIZED_DATA;
GO
ALTER DATABASE Test ADD FILE ( NAME = N'InMemory',
FILENAME = N'D:\Data\SqlServer\TEST\InMemory') TO FILEGROUP [Test_InMemory];
GO

File and Filegroup Maintenance


Adding Files
Listing 6-7. Adding a New File Using T-SQL
ALTER DATABASE TEST ADD FILE ( NAME = N'Test_File4', FILENAME =
N'D:\Data\SqlServer\TEST\Test_File4.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO
FILEGROUP [PRIMARY];
GO

Listing 6-8. Adding Additional Rows to the RoundRobin Table


--Create a Numbers table that will be used to assit the population of the table
DECLARE @Numbers TABLE
(
Number INT
)
--Populate the Numbers table
;WITH CTE(Number)
AS
(
SELECT 1 Number
UNION ALL
SELECT Number +1
FROM CTE
WHERE Number <= 99
)
INSERT INTO @Numbers
SELECT *
FROM CTE;

--Populate the example table with 10000 rows of dummy text


INSERT INTO dbo.RoundRobinTable
SELECT 'DummyText'
FROM @Numbers a
CROSS JOIN @Numbers b;
--Select all the data from the table, plus the details of the rows' physical location.
--Then group the row count
--by file ID
SELECT b.file_id, COUNT(*)
FROM
(
SELECT ID, DummyTxt, a.file_id
FROM dbo.RoundRobinTable
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) a
) b
GROUP BY b.file_id;

Expanding Files
Listing 6-9. Calculating Free Space in Each File
SELECT
file_id
,unallocated_extent_page_count * 1.0 / 128 'Free Space (MB)'
FROM sys.dm_db_file_space_usage;

--Listing 6-10. Expanding a File


ALTER DATABASE TEST MODIFY FILE ( NAME = N'Test_File4',
SIZE = 20480KB );

--Listing 6-11. Shrinking a File with TRUNCATEONLY


USE TEST
GO
DBCC SHRINKFILE (N'Test_File4' , 0, TRUNCATEONLY);

Shrinking Files
--Listing 6-12. Shrinking a Database via T-SQL
USE TEST
GO
DBCC SHRINKDATABASE(N'TEST' );

Database Scoped Configurations


--Listing 6-14. Turn On Autogrow All Files
ALTER DATABASE TEST MODIFY FILEGROUP [Primary] AUTOGROW_ALL_FILES

--Listing 6-15. Turn Off Mixed Page Allocations


ALTER DATABASE TEST SET MIXED_PAGE_ALLOCATION OFF
Log Maintenance
Log File Count
Shrinking the Log
Listing 6-16. Shrinking a Log with TRUNCATEONLY
USE TEST
GO
DBCC SHRINKFILE (N'Test_log' , 0, TRUNCATEONLY);
GO

Log Fragmentation

Listing 6-17. Creating the TestLogFragmentation Database


--Create TestLogFragmentation database
CREATE DATABASE [TestLogFragmentation]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'TestLogFragmentation',
FILENAME = N'D:\Data\SqlServer\TestLogFragmentation.mdf' , SIZE = 5120KB ,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TestLogFragmentation_log',
FILENAME = N'D:\Data\SqlServer\TestLogFragmentation_log.ldf' , SIZE = 1024KB ,
FILEGROWTH = 10%);
GO

USE TestLogFragmentation
GO
--Create Inserts table
CREATE TABLE dbo.Inserts
(ID INT IDENTITY,
DummyText NVARCHAR(50)
);
--Create a Numbers table that will be used to assit the population of the table
DECLARE @Numbers TABLE
(
Number INT
)
--Populate the Numbers table
;WITH CTE(Number)
AS
(
SELECT 1 Number
UNION ALL
SELECT Number +1
FROM CTE
WHERE Number <= 99
)
INSERT INTO @Numbers
SELECT *
FROM CTE;
--Populate the example table with 100 rows of dummy text
INSERT INTO dbo.Inserts
SELECT 'DummyText'
FROM @Numbers a
CROSS JOIN @Numbers b
CROSS JOIN @Numbers c;

Listing 6-18. Size of Log and Number of VLFs


--Create a variable to store the results of DBCC LOGINFO
DECLARE @DBCCLogInfo TABLE
(
RecoveryUnitID TINYINT
,FieldID TINYINT
,FileSize BIGINT
,StartOffset BIGINT
,FseqNo INT
,Status TINYINT
,Parity TINYINT
,CreateLSN NUMERIC
);

--Populate the table variable with the results of DBCC LOGINFO


INSERT INTO @DBCCLogInfo
EXEC('DBCC LOGINFO');

--Display the size of the log file, combined with the number of VLFs and a VLFs to GB
ratio
SELECT
name
,[Size in MBs]
,[Number of VLFs]
,[Number of VLFs] / ([Size in MBs] / 1024) 'VLFs per GB'
FROM
(
SELECT
name
,size * 1.0 / 128 'Size in MBs'
,(SELECT COUNT(*)
FROM @DBCCLogInfo) 'Number of VLFs'
FROM sys.database_files
WHERE type = 1
) a;

--Listing 6-19. sys.databases


SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'TestLogFragmentation';

--Listing 6-20. Defragmenting the Transaction Log


USE TestLogFragmentation
GO
DBCC SHRINKFILE ('TestLogFragmentation_log' , 0, TRUNCATEONLY);
GO
ALTER DATABASE TestLogFragmentation MODIFY FILE ( NAME =
'TestLogFragmentation_log', SIZE = 512000KB );
GO

You might also like