Windows 에서SetFileValidData는단일메타데이터작업이다. VDL이설정되면쓰기(순차또는임의)는VDL == EOF로추가메타데이터업데이트가필요하지않다. Linux에서쓰기에는데이터쓰기및메타데이터쓰기가필요한익스텐트업데이트가필요하다. Linux 또는Windows에서가능한한빨리파일을쓰고확장할수있다. 그러나Linux에서처음쓰기를수행하면메타데이터가유지관리된다.
MariaDB 10.0은MySQL 5.6의기능을포함하고있기때문에MySQL 5.6 과매우유사하다. MySQL 5.6 부터는InnoDB 스토리지엔진을사용하는테이블에대한통계정보를영구적(Persistent)으로관리할수있다. MySQL 5.5 버전까지는show index from 으로인덱스칼럼의분포를확인할수있었지만MySQL 5.6 부터는 mysql 데이터베이스에서innodb_index_stats 테이블과innodb_table_stats테이블에서도인덱스를조회할수있다.
show index from nclick.nclick;
select * from mysql.innodb_index_stats;
select * from mysql.innodb_table_stats;
MySQL 5.6 부터는테이블을생성할때STATS_PERSISTENT 옵션을사용하여통계정보를영구보관할수있다.
이번포스트에서는QPI 라이브러리를사용하여관리형인스턴의대기통계를쉽게분석하고있다.QPI 라이브러리를설치하려면아래링크로이동하여SQL Server 버전에대한SQL 스크립트를다운로드한다. QPI 라이브러리는Query Store 보기에의존하기때문에SQL Server 2016이상, Azure SQL 에서사용이가능하다.
Disclaimer: QPI library is open source library provided as-is and not maintained by Microsoft. There are not guarantees that the results are correct and that there are not bugs in calculations. This is a helper library that can help you to more easily analyze performance of your Managed Instance, but you can do the same job by looking directly at DMVs.
SQL Server 2008 이후로SQL Server Agent Job에서는 syspolicy_purge_history라는job이등록되어있다. 해당Job은MSDB에쌓여있는히스토리를정리하는작업을진행한다. 가끔해당Job 실행시아래와같은오류가발생할수있는데, 오류가무엇인지확인해보고해결하는방법에대해서살펴본다.
Job이실패하였을때, 해당로그를살펴보면아래와같은오류로그가발생하였다.
Date7/29/2019 1:23:41 PM
LogJob History (syspolicy_purge_history)
Step ID3
Server
Job Namesyspolicy_purge_history
Step NameErase Phantom System Health Records.
Duration00:00:00
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: NT Service\SQLSERVERAGENT. The job script encountered the following errors. These errors did not stop the script:A job step received an error at line 1 in a PowerShell script. The corresponding line is 'import-module SQLPS'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The specified module 'SQLPS' was not loaded because no valid module file was found in any module directory.'A job step received an error at line 1 in a PowerShell script. The corresponding line is 'SQLSERVER:'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The term 'SQLSERVER:' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.'.Process Exit Code -1.The step failed.
Error Code: 1118. Row size too large (> 1982). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
