DB Library

Perl 설치중 Perl MakeFile.PL명령을 실행하니 아래와 같은 오류가 발생했다.

Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: /usr/local/lib64/perl5 
/usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl 
/usr/lib64/perl5 /usr/share/perl5 .) at [Makefile.PL](http://makefile.pl/) line 7.

해결방법

• 해결방법은 간단하다. CPAN을 설치해주자.

 yum install perl-CPAN

참고 : https://jinseongsoft.tistory.com/221 [진성 소프트]

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @dbname SYSNAME
SET @dbname = QUOTENAME(DB_NAME());


WITH XMLNAMESPACES
	(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT	X.*
	,	t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)') as [Schema]
	,	t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)') as [Table]
	,	t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)') as [Column]
	,	ic.DATA_TYPE AS ConvertFrom
	,	ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength
	,	t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo
	,	t.value('(@Length)[1]', 'int') AS ConvertToLength
FROM
(
	SELECT	db_name(qt.dbid) AS 'db_name'
		,	qt.text AS 'sp_text'
		,	substring(qt.text, (qs.statement_start_offset/2)+1
		,	((  case qs.statement_end_offset
				when -1 then datalength(qt.text)
				else qs.statement_end_offset
				end - qs.statement_start_offset)/2) + 1) as statement_text
		,	qs.creation_time
		,	qs.execution_count AS 'ExecutionCount'
		,	ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second'
		,	DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache(min)'
		,	ISNULL(qs.total_elapsed_time/qs.execution_count, 0) /1000.0AS 'avg_Elapsed_Time(ms)'
		,	qs.total_elapsed_time/1000.0/1000.0 AS 'total_Elapsed_Time(sec)'
		,	max_elapsed_time /1000.0 AS 'max_Elapsed_Time(ms)'
		,	(qs.total_worker_time/qs.execution_count) /1000.0 AS 'avg_Worker_Time(ms)'
		,	qs.total_worker_time/1000.0 AS 'total_Worker_Time(ms)'
		,	max_worker_time /1000.0 as 'max_worker_time(ms)'
		,	ISNULL(qs.total_logical_reads/qs.execution_count, 0) AS 'avg_logical_reads'
		,	total_logical_reads
		,	qs.max_logical_reads
		,	ISNULL(qs.total_physical_reads/qs.execution_count, 0) AS 'avg_physical_reads'
		,	total_physical_reads
		,	qs.max_physical_reads
		,	ISNULL(qs.total_logical_writes/qs.execution_count, 0) AS 'avg_physical_writes'
		,	qs.total_logical_writes
		,	qs.max_logical_writes
		,	cast(qp.query_plan as xml) as query_plan
		,	OBJECT_NAME(qp.objectid,qp.dbid) as sp_name
    FROM sys.dm_exec_query_stats AS qs
	CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
	CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle,qs.statement_start_offset,qs.statement_end_offset) qp
)X
--sys.dm_exec_cached_plans AS cp
--CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n(t)
JOIN INFORMATION_SCHEMA.COLUMNS AS ic
ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)')
AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)')
AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)')
WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1

mssql db를 사용하다보면 vlf count가 증가하는 것을 볼수 있다.

vlf count가 증가하면 DML 및 로그 백업시 지연 이슈가 발생 할 수 있기 때문에 적절하게 축소해 주는것이 유리하다

2019 버전에서는 계속해서 증가하지 않도록 개선이 되었으나, 이하 버전에서는 관리가 필요

vlf 확인

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 사용자 DB + TempDB 이름을 임시 테이블로 저장, 변수 선언
IF OBJECT_ID(N'tempdb..#DB') IS NOT NULL
BEGIN
	DROP TABLE #DB
END

CREATE TABLE #DB (name sysname, dbid smallint)

INSERT INTO #DB (name, dbid)
SELECT name, dbid FROM master.dbo.sysdatabases
--WHERE name not in ('master', 'msdb', 'model')
GO

print  '*** TR Log VLFs'
DECLARE @dbname SYSNAME
DECLARE @min_dbid SMALLINT
DECLARE @SQL VARCHAR(8000)
DECLARE @productversion VARCHAR(128)
DECLARE @Majorversion INT

SET @productversion = convert(VARCHAR(128), SERVERPROPERTY('ProductVersion'))
SET @Majorversion = convert(INT, left(@productversion, charindex('.', @productversion) - 1))

IF OBJECT_ID(N'tempdb..#LOGFILECNT') IS NOT NULL 
BEGIN
     DROP TABLE #LOGFILECNT
END


CREATE TABLE #LOGFILECNT (
	seq INT identity
	,dbname SYSNAME
	,logcount INT
	)

SET @min_dbid = 0

WHILE (@min_dbid IS NOT NULL)
BEGIN
	SELECT @min_dbid = MIN(dbid)
	FROM #DB
	WHERE dbid > @min_dbid

	IF (@min_dbid IS NULL)
		BREAK

	SELECT @dbname = NAME
	FROM #DB
	WHERE dbid = @min_dbid

	INSERT INTO #LOGFILECNT (dbname)
	SELECT @dbname

	SET @SQL = 'USE [' + @dbname + ']; '

	IF @Majorversion < 11
	BEGIN
		SET @SQL = @SQL + '
  CREATE TABLE #T1 (c1 nvarchar(30), c2 nvarchar(30), c3 nvarchar(30), c4 nvarchar(30), c5 nvarchar(30), c6 nvarchar(30), c7 nvarchar(30))
  INSERT INTO #T1 EXEC (''DBCC LOGINFO'')
  UPDATE #LOGFILECNT SET logcount = (SELECT count(*) FROM #T1)
  WHERE dbname = ''' + @dbname + '''
  DROP TABLE #T1
  '
	END
	ELSE
	BEGIN
		SET @SQL = @SQL + '
  CREATE TABLE #T1 (c1 nvarchar(30), c2 nvarchar(30), c3 nvarchar(30), c4 nvarchar(30), c5 nvarchar(30), c6 nvarchar(30), c7 nvarchar(30), c8 nvarchar(30))
  INSERT INTO #T1 EXEC (''DBCC LOGINFO'')
  UPDATE #LOGFILECNT SET logcount = (SELECT count(*) FROM #T1)
  WHERE dbname = ''' + @dbname + '''
  DROP TABLE #T1
  '
	END

	EXEC (@SQL)
END

SELECT dbname
	,logcount
FROM #LOGFILECNT
ORDER BY seq

IF OBJECT_ID(N'tempdb..#LOGFILECNT') IS NOT NULL 
BEGIN
     DROP TABLE #LOGFILECNT
END
GO

vlf 축소

-- checkpoit 발생
CHECKPOINT

-- 버퍼 클린
DBCC DROPCLEANBUFFERS

-- Log Backup
BACKUP LOG TestDB TO  DISK = N'e:\BACKUP\TestDB_log.trn' WITH NOFORMAT, NOINIT,  NAME = N'TestDB Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

-- DB Shrink
USE TestDB
GO
DBCC SHRINKFILE (N'TestDB_log' , 0, TRUNCATEONLY)
GO



USE [master]
GO
ALTER DATABASE TestDB MODIFY FILE ( NAME = N'TestDB_log', SIZE = 10GB )
GO


dbcc loginfo(TestDB)