[SQL Snacks Video] Performance Tuning 103 – T-Logs and VLFs

RSS

[SQL Snacks Video] Performance Tuning 103 – T-Logs and VLFs

31 Dec

Finally the arrival of Part 3 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. Also, if you missed part two from yesterday you can review it here.

If you are still interested in learning more about Performance tuning with SQL Server, I will be giving an hour long presentation with the PASS DBA Fundamentals Virtual Chapter on January 6, 2015 (11 am Central Time/Noon Eastern Time). For more information please visit http://dbafundamentals.sqlpass.org/ and join PASS for a great way to learn more about SQL Server.

Code from Demo below:

Code for VLF Demo 1

DBCC Traceon (3004,3605,-1)
GO

EXEC sp_cycle_errorlog ;
GO
EXEC sp_readerrorlog
--Turn on Trace Flag to view IFI info

Set Statistics TIME ON
CREATE DATABASE GoodVLF
 ON  PRIMARY 
( NAME = N'GoodVLF', FILENAME = N'Z:\SQLDATA\GoodVLF.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'GoodVLF_log', FILENAME = N'Y:\SQLLOGS\GoodVLF_log.ldf' , SIZE = 800MB , FILEGROWTH = 100MB)
GO
---  CPU time = 16 ms,  elapsed time = 2632 ms.
USE GoodVLF
DBCC LOGINFO
---Number of Rows = # VLFs
--Size ~100MB for each VLF
--8 VLFs Size 64MB each
 

CREATE DATABASE BadVLF
 ON  PRIMARY 
( NAME = N'BadVLF', FILENAME = N'Z:\SQLDATA\BadVLF.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'BadVLF_log', FILENAME = N'Y:\SQLLOGS\BadVLF_log.ldf' , SIZE = 1MB , FILEGROWTH = 10%)
GO
--CPU time = 0 ms,  elapsed time = 164 ms.

--Took less time to create, but will grow more and may affect other DB Log growth occuring at the same time
--IFI does not affect Log Files

--Initial backup so DBs are set to grow log otherwise it behaves like SIMPLE Recovery
ALTER DATABASE GoodVLF SET RECOVERY FULL
ALTER DATABASE BADVLF SET RECOVERY FULL
BACKUP DATABASE GoodVLF to DISK = 'NUL'
BACKUP DATABASE BadVLF to DISK = 'NUL'

USE BadVLF
DBCC LOGINFO
--4 VLFs Size of 256KB each

---Get free space in Logs
DBCC SQLPERF (LOGSPACE)

--Used space for GoodVLF less than 1%
--Used space for BadVLF ~55%

USE GoodVLF
Create Table Dummy
(ID int,
Text varchar(100))

Create Clustered Index IDX_ID on dbo.Dummy(ID)
GO


SET STATISTICS TIME OFF
SET NOCOUNT ON
Declare @counter int = 0
Declare @counter2 int = 0

PRINT Format(Getdate(),'M/dd/y hh:mm:ss')

While @counter &amp;lt;50000
Begin	



WHILE @counter2&amp;lt;100000
BEGIN
		
		Insert Into Dummy(ID,Text) values (RAND()*10000,'Test')
		set @counter2+=1
		---same as set @counter2 = @counter2 + 1
END

	ALTER INDEX IDX_ID ON dbo.Dummy REORGANIZE
	
	Delete From Dummy

	Set @counter+=1

END


PRINT Format(Getdate(),'M/dd/y hh:mm:ss')
--1 minute 4 Seconds

DBCC LOGINFO
--8 VLFs



USE BADVLF
Create Table Dummy
(ID int,
Text varchar(100))

Create Clustered Index IDX_ID on dbo.Dummy(ID)
GO

SET STATISTICS TIME OFF
SET NOCOUNT ON
Declare @counter int = 0
Declare @counter2 int = 0

PRINT Format(Getdate(),'M/dd/y hh:mm:ss')

While @counter &amp;lt;50000
Begin	



WHILE @counter2&amp;lt;100000
BEGIN
		
		Insert Into Dummy(ID,Text) values (RAND()*10000,'Test')
		set @counter2+=1
		---same as set @counter2 = @counter2 + 1
END

	ALTER INDEX IDX_ID ON dbo.Dummy REORGANIZE
	
	Delete From Dummy

	Set @counter+=1

END

PRINT Format(Getdate(),'M/dd/y hh:mm:ss')
--1 Minute 16 Seconds

DBCC LOGINFO
--68 VLFs, uneven in size

---Do The Same for BADVLF

---Fix the problem
---Truncate and Shrink LogFile first

---Clears Log
ALTER DATABASE BadVLF SET RECOVERY SIMPLE

USE BadVLF
DBCC ShrinkFile ('BadVLF_log',0)

ALTER DATABASE BadVLF MODIFY FILE ( NAME = N'BadVLF_log', SIZE = 200MB )
GO

ALTER DATABASE BadVLF MODIFY FILE ( NAME = N'BadVLF_log', SIZE = 400MB )
GO

ALTER DATABASE BadVLF MODIFY FILE ( NAME = N'BadVLF_log', SIZE = 600MB )
GO

ALTER DATABASE BadVLF MODIFY FILE ( NAME = N'BadVLF_log', SIZE = 800MB )
GO

ALTER DATABASE BadVLF MODIFY FILE ( NAME = N'BadVLF_log', MAXSIZE = UNLIMITED, FILEGROWTH = 100MB)
GO


/*Clean Up*/
Use MASTER
GO
DROP Database GoodVLF
DROP Database BadVLF

DBCC Traceoff (3004,3605,-1)
GO

Code for VLF Demo 2

CREATE DATABASE [FireMeVLF]
 ON  PRIMARY 
( NAME = N'FireMeVLF', FILENAME = N'Z:\SQLDATA\FireMeVLF.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'FireMeVLF_log', FILENAME = N'Y:\SQLLOGS\FireMeVLF_log.ldf' , SIZE = 1MB , MAXSIZE = 400MB,FILEGROWTH = 1KB)
GO

ALTER database FiremeVLF SET RECOVERY FULL



USE FireMEVLF
GO

BACKUP DATABASE FireMeVLF to DISK = 'NUL'

Create Table WorstTable
(Data char(8000))
GO

SET NOCOUNT ON

Insert into WorstTable Values ('Terrible DBA')
Go 1000

declare @counter int = 1
Declare @counter2 int = 0

While @counter &amp;lt; 100000
BEGIN
Delete from WorstTable

	WHILE @counter2&amp;lt;1000
	BEGIN
		Insert into WorstTable Values ('Terrible DBA')
		set @counter2 +=1
	END

set @counter2=0
set @counter +=1
END
---takes 2 minutes
DBCC LOGINFO
---1600 VLFs


CREATE DATABASE [FireMeVLF2]
 ON  PRIMARY 
( NAME = N'FireMeVLF2', FILENAME = N'Z:\SQLDATA\FireMeVLF2.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'FireMeVLF2_log', FILENAME = N'Y:\SQLLOGS\FireMeVLF2_log.ldf' , SIZE = 400MB , MAXSIZE = 400MB,FILEGROWTH = 1KB)
GO
---takes 4 seconds

ALTER database FiremeVLF2 SET RECOVERY FULL

USE FireMEVLF2
GO

BACKUP DATABASE FireMeVLF2 to DISK = 'NUL'

Create Table WorstTable
(Data char(8000))
GO


SET NOCOUNT ON

Insert into WorstTable Values ('Terrible DBA')
Go 1000

declare @counter int = 1
Declare @counter2 int = 0

While @counter &amp;lt; 100000
BEGIN
Delete from WorstTable

	WHILE @counter2&amp;lt;1000
	BEGIN
		Insert into WorstTable Values ('Terrible DBA')
		set @counter2 +=1
	END
set @counter2=0
set @counter +=1
END
---Takes 10 seconds

DBCC LOGINFO
---8 VLFs

USE master
BACKUP LOG FIREMEVLF
TO DISK = 'X:\SQLBACKUPS\FIREMEVLF.bak'
WITH COMPRESSION
---Takes 9 seconds

USE master
BACKUP LOG FIREMEVLF2
TO DISK = 'X:\SQLBACKUPS\FIREMEVLF2.bak'
WITH COMPRESSION
---Takes 3 seconds

---Create an open transaction to demonstrate longer recovery time
USE FireMeVLF

SET NOCOUNT ON
Begin Transaction

declare @counter int = 1
Declare @counter2 int = 0

While @counter &amp;lt; 100000
BEGIN
Delete from WorstTable

	WHILE @counter2&amp;lt;1000
	BEGIN
		Insert into WorstTable Values ('Terrible DBA')
		set @counter2 +=1
	END

set @counter2=0
set @counter +=1
END
---Takes 25-28 Seconds
---No end
--ROLLBACK Transaction

---Take this to a new window
DBCC LOGINFO
DBCC SQLPERF (LOGSPACE)


USE FireMeVLF2

SET NOCOUNT ON
Begin Transaction

declare @counter int = 1
Declare @counter2 int = 0

While @counter &amp;lt; 100000
BEGIN
Delete from WorstTable

	WHILE @counter2&amp;lt;1000
	BEGIN
		Insert into WorstTable Values ('Terrible DBA')
		set @counter2 +=1
	END

set @counter2=0
set @counter +=1
END

--Takes 20-25 seconds

--ROLLBACK TRANSACTION


EXEC sp_cycle_errorlog ;
GO
exec sp_readerrorlog

alter database FireMeVLF SET OFFLINE 
alter database FireMeVLF2 SET OFFLINE

alter database FireMeVLF SET ONLINE
--4 seconds
alter database FireMeVLF2 SET ONLINE
--~0 seconds

----CLEAN UP
USE master
DROP database [FireMeVLF]
DROP database [FireMeVLF2]

The SQL Pro