RSS

[SQL Snacks Video] Performance Tuning 102 – Files, FileGroups, and TempDB

30 Dec
[SQL Snacks Video] Performance Tuning 102 – Files, FileGroups, and TempDB

Finally the arrival of Part 2 of my SQL Snack Pack on Performance Tuning! The series is dedicated to help beginners understand how to start performance tuning with SQL Server. The first video was about performing a baseline using the PAL tool.I would highly recommend you review that video as well as my SQL Snack on Instant File Initialization. This second video discusses the importance of properly sizing Data files, placement, and how the Proportional Fill-Algorithm works for data insertion. I’m hoping you get some last minute Performance tuning in before 2015 and so I will be posting the third video within the next 24 hours.

Code from Demo below:

USE Master

Drop database DBMulti

CREATE DATABASE DBMulti
ON PRIMARY
( NAME = N'DBMulti1', FILENAME = N'Z:\SQLDATA\DBMulti1.mdf' , SIZE = 3MB , FILEGROWTH = 128KB ),
( NAME = N'DBMulti2', FILENAME = N'Z:\SQLDATA\Disk2\DBMulti2.mdf' , SIZE = 3MB , FILEGROWTH = 128KB )
LOG ON
( NAME = N'DBMulti_log', FILENAME = N'Y:\SQLLOGS\DBMulti_log.ldf' , SIZE = 1MB , FILEGROWTH = 1MB)
GO

ALTER DATABASE DBMulti SET RECOVERY SIMPLE
USE DBMulti
GO

Create Table TestRRPFA
(ID INT identity (1,1),
Name Char(4000) DEFAULT 'AYMAN',
Name2 Char(4049) DEFAULT 'AYMAN')

----http://www.mssqltips.com/sqlservertip/1805/different-ways-to-determine-free-space-for-sql-server-databases-and-database-files/

---Thanks Greg Robidoux for the free space code
---I added FreeSpaceKB and NumberOfFreePages
SELECT DB_NAME() AS DbName,
name AS FileName,
size as NumberOfPages,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB,
size*8- CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)*8 AS FreeSpaceKB,
size- CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) AS NumberOfFreePages
FROM sys.database_files;

SET NOCOUNT ON
--Initial Load 20 seconds
INSERT INTO TestRRPFA DEFAULT VALUES
GO 1000

---After initial load

INSERT INTO TestRRPFA DEFAULT VALUES
GO 8

SELECT DB_NAME() AS DbName,
name AS FileName,
size as NumberOfPages,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB,
size*8- CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)*8 AS FreeSpaceKB,
size- CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) AS NumberOfFreePages
FROM sys.database_files;
---Each file is growing one at a time
DBCC TraceON (1117)
--DBCC TraceOFF (1117)
---This trace flag causes both files to grow together
----ADD SECONDARY FILEGROUP
---ADD TWO FILES each on a "Different" Drive

ALTER DATABASE DBMulti ADD FILEGROUP SecondFG

ALTER DATABASE [DBMulti] ADD FILE ( NAME = N'DBMulti3', FILENAME = N'Z:\SQLDATA\DBMulti3.ndf' , SIZE = 3072KB , FILEGROWTH = 128KB ) TO FILEGROUP [SecondFG]
GO
ALTER DATABASE [DBMulti] ADD FILE ( NAME = N'DBMulti4', FILENAME = N'Z:\SQLData\DISK2\DBMulti4.ndf' , SIZE = 3072KB , FILEGROWTH = 128KB ) TO FILEGROUP [SecondFG]
GO

---Table created on the other file group
Create Table TestRRPFASecondFG
(ID INT identity (1,1),
Name Char(4000) DEFAULT 'AYMAN',
Name2 Char(4049) DEFAULT 'AYMAN')
ON SecondFG
SELECT DB_NAME() AS DbName,
name AS FileName,
size as NumberOfPages,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB,
size*8- CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)*8 AS FreeSpaceKB,
size- CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) AS NumberOfFreePages
FROM sys.database_files;

SET NOCOUNT ON
--Initial Load 20 seconds
INSERT INTO TestRRPFASecondFG DEFAULT VALUES
GO 1000

---After initial load

INSERT INTO TestRRPFASecondFG DEFAULT VALUES
GO 8

SELECT DB_NAME() AS DbName,
name AS FileName,
size as NumberOfPages,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB,
size*8- CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)*8 AS FreeSpaceKB,
size- CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) AS NumberOfFreePages
FROM sys.database_files;

---Create index on different file group
ALTER DATABASE DBMulti ADD FILEGROUP IndexFG
ALTER DATABASE [DBMulti] ADD FILE ( NAME = N'IndexFG', FILENAME = N'Z:\SQLDATA\IndexFG.ndf' , SIZE = 3072KB , FILEGROWTH = 128KB ) TO FILEGROUP IndexFG
GO

--_WARNING POOR INDEX DESIGN
---Index Created on the primary table
CREATE NONCLUSTERED INDEX NCIDX_TEST on TestRRPFA(ID) INCLUDE (Name) ON INDEXFG
CREATE NONCLUSTERED INDEX NCIDX_TEST2 on TestRRPFA(ID) INCLUDE (Name2) ON INDEXFG
--Redo Inserts on first created table
--Creating clustered index on another file group moves the table and its data to the other file group and its data file(s)

/* Other Free Space info

EXEC sp_HelpDB 'DBMulti'
USE master

----Size of individual files on each DB
select size*8/1024 AS SIZEMB,*
FROM sys.master_files m
INNER JOIN sys.databases d ON m.database_id=d.database_id
where d.name = 'DbMulti'
ORDER BY d.name

*/
Advertisements
 

Tags: , , , , , ,

4 responses to “[SQL Snacks Video] Performance Tuning 102 – Files, FileGroups, and TempDB

  1. LondonDBA

    December 31, 2014 at 4:37 AM

    In the video I can see the slides but I can’t see the demo that starts at 9 minutes 30 seconds.

     
    • Ayman El-Ghazali

      December 31, 2014 at 6:43 AM

      Thanks for the feedback. I’m going to have to edit and re-upload the video, hopefully sometime today. Sorry about that, the recording software sometimes does weird stuff.

       
    • Ayman El-Ghazali

      December 31, 2014 at 2:09 PM

      I updated the video and the URL on this page. Here is a copy of the URL for the new video: http://youtu.be/Nnbo2AWrh5o

      Thank you for your feedback it helped me fix the issue. It was a “technical” difficultly with the recording software 🙂
      I’ve also included the code for the demo above.

       

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: