JOB 등록 하실 때 단계 실패 시 로그를 남기게 하는 방법이 몇 가지 있습니다.

SSIS 패키지 경우 LOG 파일을 지정해서 저장하게 하는 경우가 많은데요. (그럼 에러를 찾기가 조금 수훨 해 집니다.)

그에 관련한 내역입니다. 한번씩 보세요.

아래 그림 처럼 setting을 하곤 합니다.

여기에 기존 파일에 출력 추가를 하게 되면 해당 파일에 계속 커지면서 쌓입니다.

체크하지 않으면 실행될 때 내용을 모두 지우고 다시 쓰기 작업 합니다.

단계가 어떻게 셋팅 되어 있는지 JOB을 보지 않고 알 수 있는 쿼리입니다.  (1번 쿼리 )

많이 사용하지는 않을 수 있으나 JOB을 보기가 힘들 때는 유용할 것 같습니다.

2.만약 단계를 테이블 출력으로 저장해 두었다면(자주 사용하지는 않습니다 워낙 job이 많아서)

 아래 구문 처 럼 select 해서 보시면 진행 log 내역을 볼 수 있습니다.

테이블 출력으로 해 놓은 job은 sysjobstepslogs 에 저장됩니다.

관련 테이블은 msdb.dbo.sysjobs_view, msdb.dbo.sysjobsteps, sysjobstepslogs

 EXEC msdb.dbo.sp_help_jobsteplog  @job_name = N'job_명

1.     셋팅 방법 확인

;WITH Flags (FlagID, FlagValue) 
AS
(
   SELECT 0 AS FlagID, 'Overwrite output file' AS FlagValue UNION ALL
   SELECT 2 AS FlagID, 'Append to output file' AS FlagValue UNION ALL
   SELECT 4 AS FlagID, 'Write Transact-SQL job step output to step history' AS FlagValue UNION ALL
   SELECT 8 AS FlagID, 'Write log to table (overwrite existing history)' UNION ALL 
   SELECT 16 AS FlagID, 'Write log to table (append to existing history)'
),
JobsInfo (Job_Name, Jobstep_ID, Jobstep_Name, Flags)
AS
(
SELECT 
  j.name as [Job_Name]
  , js.step_name as [Jobstep_Name]
   , js.step_id as [Jobstep_ID]
   , flags 
FROM msdb.dbo.sysjobsteps js JOIN msdb.dbo.sysjobs j 
ON js.job_id = j.job_id
),
FinalData (Job_Name, JobStep_Name, [Jobstep_ID], FlagValue)
AS
(
SELECT 
   Job_Name
   , Jobstep_Name
   , [Jobstep_ID]
   , F.FlagValue
FROM JobsInfo JI CROSS JOIN Flags F 
WHERE JI.Flags & F.FlagID <> 0 
)

SELECT DISTINCT 
   JI.Job_Name
   , JI.[Jobstep_ID]
   , JI.Jobstep_Name
   , ISNULL(STUFF (( SELECT ', ' + FD2.FlagValue FROM FinalData FD2 
WHERE FD2.Job_Name = FD1.Job_Name AND FD2.Jobstep_Name = FD1.Jobstep_Name 
ORDER BY ', ' + FD2.FlagValue FOR XML PATH('')), 1, 1, ' '), 'Overwrite output file') AS OptionsSet
FROM FinalData FD1 RIGHT OUTER JOIN JobsInfo JI
ON FD1.Job_Name = JI.Job_Name AND FD1.Jobstep_Name = JI.Jobstep_Name
ORDER BY Job_Name, Jobstep_Name

복제에 문제가 생길 때 보실 수 있는 내역입니다. 어제 이 방법으로 확인했습니다.

평소 문제가 없을때는 MSrepl_commands 조회시 top 을 주시는 것이 좋습니다.

1.  해당 게시명은 아는데 agent를 모를 경우 쉽게 알 수 있는 법은 배포 장비의 job중에 해당 게시의 배포 agent 명을 보면 뒤에 붙어 있는 숫자가 Agent 아이디 입니다.

아니면  select id, name , publication from MSdistribution_agents 하면 게시에 대한 agent를 확인 할 수 있습니다.

 
2.  1번에서 실행된 agent에서 가장 마지막으로 실행된 xact_seqno 를 아셨으면 그 번호 이상인 명령이 무엇인지 확인하는 것입니다.

   이때 MSrepl_commands 테이블만으로 조해 해보면 모든 게시자 DB에 여러 개의 게시가 있을 경우 다른 게시에 대한 것도 보이니  아티클로 아셔서 보시거나 윗 처럼 조인해서 보시면 됩니다.

3.  최종적으로 비슷한 xact_seqno가 동일한게 여러 개 보이면 범위주어서 찾아보시면 됩니다. 

인자로 @agent_id 값을 넣으면 조회가 오래 걸리니 안 넣으시게 좋을 것 같습니다.

추가 )  지연된 명령어 수 확인

exec sp_replmonitorsubscriptionpendingcmds  

 @publisher ='Enter publisher server name',

 @publisher_db = 'Enter publisher database name',

 @publication ='Enter name of publication',

 @subscriber ='Enter subscriber server name',

 @subscriber_db ='Enter subscriber database name',

 @subscription_type ='0' --0 for push and 1 for pull 

http://msdn.microsoft.com/ko-kr/library/ms189310.aspx

 NUMA 노드에 TCP/IP 포트 매핑

http://msdn.microsoft.com/ko-kr/library/ms345346.aspx

소프트 NUMA를 사용하도록 SQL Server 구성

   http://msdn.microsoft.com/ko-kr/library/ms345357.aspx 

복원후 에러로그 내역 궁금

 marketd in LOAD

• 2007-07-26 03:00:10.00 spid55    Bypassing recovery for database 'event' because it is marked IN LOAD.
• 에러 로그 파일에 해당 내역이 복원하고 나서 찍히는데 무엇일까?
• 2000, 7.0 에만 적용된다.
•  http://support.microsoft.com/kb/822852#appliesto   : 모델 데이터베이스 에로 들어놨다.

SQL Server DBA Checklist

 Problem
I am looking for items that I should address on a daily basis on my SQL Servers.  As such, what are the critical aspects of SQL Server that should I check on a daily basis?  Should I perform additional checks on a weekly, monthly or yearly basis?  How can I automate some of these tasks so I do not spend my whole day reviewing SQL Servers rather working on the latest and greatest technologies?

Solution
Depending on your environment dictates all of the items that should be reviewed on a daily basis as well as their criticality in your specific organization.  Based on your environment, customize the list below to ensure it meets your needs:

Daily Checklist

• Backups - Check your backups to validate that they were successfully created per your process.
  • MSSQLTips.com Category: Backup and Recovery
• Nightly Processing - Review the nightly or early morning processes.
• SQL Server Error Log - Review the SQL Server error log for any errors or security issues (successful or failed logins) that are unexpected.
  • SQL Server 2005 Exposed - Log File Viewer
• Windows Event Log - Review the Application Event Log at a minimum to find out if any Windows or hardware related errors or warnings are being written.
  • Some of the hardware vendors write warnings to the Windows Event Log when they anticipate an error is going to occur, so this gives you the opportunity to be proactive and correct the problem during a scheduled down time, rather than having a mid day emergency.
  • SQL Server 2005 Exposed - Log File Viewer
• SQL Server Agent Jobs - Review for failed SQL Server Agent Jobs.
  • Finding SQL Server Agent Job Failures
• HA or DR Logs - Check your high availability and/or disaster recovery process logs.  Depending on the solution (Log Shipping, Clustering, Replication, Database Mirroring, CDP, etc.) that you are using dictates what needs to be checked.
• Performance Logs - Review the performance metrics to determine if your baseline was exceeded or if you had slow points during the day that need to be reviewed.
  • MSSQLTips.com Category: Performance Tuning
• Security Logs - Review the security logs from a third party solution or from the SQL Server Error Logs to determine if you had a breach or a violation in one of your policies.
• Centralized error handling - If you have an application, per SQL Server or enterprise level logging, then review those logs for any unexpected errors.
  • Standardized Error Handling and Centralized Logging
• Storage - Validate you have sufficient storage on your drives to support your databases, backups, batch processes, etc. in the short term.
• Service Broker - Check the transmission and user defined queues to make sure data is properly being processed in your applications.
  • MSSQLTips.com Category: Service Broker
• Corrective Actions - Take corrective actions based on the issues and/or errors that you found.
• Improvements - Look for opportunities to improve your environment based on the review and analysis you have performed.
• Learn something new - Although this review and correction process could be time consuming, take some time every day to learn something new to improve your knowledge of the technology you work on every day.

Weekly or Monthly Checklist

• Backup Verification (Comprehensive)- Verify your backups and test on a regular basis to ensure the overall process works as expected.  What is meant by this is to:
  • Contact your off site tape vendor to obtain a tape
  • Validate that the tape goes to the correct office
  • Validate that the vendor delivers the correct tape
  • Validate that the vendor delivers the tape in the correct time period
  • Validate that the software version you use to perform the restore is compatible with the version from the tape
  • Validate that the tape does not have any restore errors
  • Validate that sufficient storage is available to move the backup to the needed SQL Server
  • Validate that the SQL Server versions are compatible to restore the database
  • Validate that no error messages are generated during the restore process
  • Validate that the database is accurately restored and the application will function properly
• Backup Verification (Simple) - Verify your backups on a regular basis.
  • Maintenance Tasks: Automating the RESTORE VERIFYONLY Process
  • Verifying Backups with the RESTORE VERIFYONLY Statement
• Windows, SQL Server or Application Updates - Check for service packs/patches that need to be installed on your SQL Server from either a hardware, OS, DBMS or application perspective
  • MSSQLTips.com Category: Service Packs / Patches
• Capacity Planning - Perform capacity planning to ensure you will have sufficient storage for a specific period of time such as for 6, 12 or 18 months.
  • Easing the Capacity Planning Burden
• Fragmentation - Review the fragmentation for your databases to determine if you particular indexes must be rebuilt based on analysis from a backup SQL Server.
  • MSSQLTips.com Category: Fragmentation
• Maintenance - Perform database maintenance on a weekly or monthly basis.
  • MSSQLTips.com Category: Maintenance
• Security - Remove unneeded logins and users for individuals that have left the organization, had a change in position, etc.
• Shrink databases - If databases or transaction logs are larger, than necessary shrink those files to free up disk space.
  • SQL Server 2000 to 2005 Crosswalk - Database Maintenance Plan Wizard to SQL Server Integration Services (SSIS)

Opportunities for Automation

• Setup alerts for specific error levels or error messages that impact your SQL Servers in order to be notified automatically.
  • Database Backup and Restore Failure Notifications
• Setup Jobs to query for specific conditions in your tables to validate data was loaded or data is being added to specific tables based on your business processes throughout the day.
• Setup notification on Job success, failure or completion.
  • One word of warning is to check your business critical Jobs on a regular basis just to be sure they are working properly.  Nothing is worse than finding out a key process has been failing for days, weeks or months and the reason notifications have not been sent are due to an incorrect configuration, full mailbox, etc.  It may be 30 minutes on a weekly basis that is time well spent.
• Setup centralized error handling on a per SQL Server, application or enterprise basis then determine the business rules for specific error conditions.

Survive A SQL Server DBA Technical Interview

by Jeffrey Yao, 2006/07/10

Problem
I am a SQL Server DBA and I am very comfortable with the design and development side of SQL Server.  I have been complimented by my own team on many of my database designs as well as on much of the behind the scenes development that I have done in SQL Server.  Unfortunately, the tides have turned a little bit at work and we now have a stronger focus on the infrastructure components with some new people on the team.  I know the basics about hardware, but I get lost on some of the newer terms that some of the new folks bring up.  Can you help define some of the key hardware related items and the significance in SQL Server?

Solution
Let's try to break down some of the recent hardware trends that might be brought up related to SQL Server and outline some of the impacts to the SQL Server environment.  We have only a handful of topics in this tip and with the hardware markets changing so rapidly, do not be surprised that what is new today is old tomorrow.  Just the fact of life with processing power doubling every 1 to 2 years, the hardware technology changes rapidly and get rolled into environments at a rapid pace.

Platform Technologies

• 32 Bit vs. 64 Bit - The biggest over arching platform aspect is the 64 bit technology.  The predecessor was the 32 bit platform that was able to scale but had some inherent limitations, whereas with the 64 bit architecture the natively addressed resources well exceed the practical limit of most applications depending on the hardware platform and Windows version selected.  If you have an application that needs to scale, start with the 64 bit platform.  Your application and most notably your users will have met their match.
  • http://www.microsoft.com/servers/64bit/overview.mspx
• Blade Servers - A low profile or thin server that resides in a blade enclosure with 1 or more CPUs, 1 or more GB of memory,  controller cards, NICs and either has 1 or 2 local disks or is connected to a SAN or NAS device to store the SQL Server databases.

Central Processing Unit (CPU)

• Hyper Threaded CPUs - Two simultaneous threads on the CPU which Windows logically recognizes as 2 CPUs.  The advantage in using hyper threading is that multiple threads run simultaneously which improve the CPU response time and minimize idle time.
• Socket - The device that connects the physical CPU to the mother board.  The reason the socket has become important is primarily related to licensing for particular products including SQL Server.  Not too long ago all licensing was based on a per CPU paradigm.  Now that a CPU can have multiple threads (see Hyper Threaded CPUs) or cores, the logical CPUs compared to the physical CPUs can become confusing.  As such, with some vendors the CPU sockets are used to determine licensing as compared to the cores.  When it comes down to licensing, make sure you know the number of sockets and cores per CPU as well as if the CPUs are hyper threaded, to make sure you have appropriate licensing.
• Dual Core CPUs - On a single socket, two independent microprocessors exist which gives the machine the power of 2 CPUs on a single socket.  This is also another example of where SQL Server shows two logical CPUs in the machine as compared to one physical socket.
• Multi Core CPUs - As an example, Intel and AMD recently released a 4 core CPU which means that a single sockets has 4 microprocessors as the next step up from the Dual Core CPUs.
  • http://www.intel.com/products/processor/core2quad/index.htm?iid=homepage+qc
  • http://www.amd.com/us-en/Processors/ProductInformation/0,,30_118_8796_8800~119300,00.html

Disk Drives

• External Chassis - An external device with one or more cages that stores disk drives which can be access by one or more machines directly as local disk drives.
• Storage Area Network (SAN) - Centralized storage (disk drives) that can be vertically or horizontally partitioned and presented to a finite number of machines (i.e. SQL Servers).  Some of the SAN technologies have front end cache on the SAN to improve the data access by retrieving a cached value (memory) of the data as opposed to retrieving the data from the physical hard drives.  Disk drives can also be added to the SAN and then presented to the SQL Servers which streamlines the process to add storage for specific machines across the enterprise.  In many circumstances, the NAS (Network Attached Storage) devices are blurring across vendors, but historically the NAS devices were intended for file level access similar to a file server.
• Redundant Array of Inexpensive Disks (RAID) - Although this technology and some of the others in this tip have been around for a while, people still seem to want to know the differences among the RAID sets.  In general the RAID set provides redundancy so that N number of disks can fail, but the RAID set remains functional and can process user requests with or without degradation depending on the hardware or software solution.  Although, I have seen most RAID configurations managed by a hardware device, software versions are available as well, but are typically considered less optimal as compared to the corresponding hardware version.  So let's take a look at each RAID definition and some additional key points:
  • RAID 0
    • General - Disk striping without parity
    • Minimum number of disks - 0
    • Maximum number of disks - Many, but the limitation is ultimately based on the vendor's specifications i.e. cage, rack, closet, SAN, etc.
    • Redundancy - None - If 1 disk drive fails, the entire array fails
    • Purpose in SQL Server - Backups, batch file processing, etc.
  • RAID 1
    • General - Disk mirroring
    • Minimum number of disks - 2
    • Maximum number of disks - 2
    • Redundancy - Ability to lose a single disk and continue operations, but will need to replace the failed disk and rebuild the parity between the new set of disks
    • Purpose in SQL Server - Since a RAID 1 set is the most basic form of data protection, with the least amount of disk drives, at times all SQL Server functions are supported.  When it comes to performance, RAID 1 sets are ideal for transaction log processing because the processing is primarily sequential.  In addition, if a SQL Server instance has intense TempDB processing, in some circumstances TempDB is moved to a separate RAID 1 set, but that is to isolate the processing, even though the TempDB processing is typically more random then sequential.
  • RAID 10
    • General - Disk mirroring in a disk stripped set
      • Depending on the vendor, RAID 10 could be implemented and named as RAID 1 + 0 or RAID 0 + 1, so the general premise may be the same as RAID 10, but some of the underlying implementation details may be different, so check with the hardware vendor for the exact details.
    • Minimum number of disks -  2 or 4
    • Maximum number of disks - Many, in multiples of 2, but ultimately based on the hardware vendor's specifications.  I know most vendors can at least support a RAID 10 set of 4 or 8 disks.  Beyond that number it really boils down to the hardware device used.
    • Redundancy - As long as 1 disk remains functional in each mirrored set, the array is operational.
    • Purpose in SQL Server - Performance.  If a user defined database has intense processing demands, in general the best performance would be from the RAID 10 set.
  • RAID 5
    • General - Disk stripping with parity
    • Minimum number of disks - 3
    • Maximum number of disks - Many, but either in an odd or even number, but ultimately based on the hardware vendor's specifications.  I know most vendors can at least support a RAID 5 set of 10+ disks.  Beyond that number it really boils down to the hardware device used.
    • Redundancy - Ability to lose a single disk and continue operations, but 2 disks result in an array failure.
    • Purpose in SQL Server - Ideal for supporting user defined databases that are read intensive applications, which most are.
  • RAID - As a note, some hardware vendors also have other RAID configurations, so please contact them for the details or share them with us and we will update the tip.
  • Additional information about RAID:
    • http://www.acnc.com/raid.html
    • http://www.lascon.co.uk/d008005.htm
• Hot Spare - A disk drive in the chassis that can move to an online state in case of a failure in any of the arrays (RAID sets).
• Parity - Means of providing fault tolerance between the disk drives to ensure a single disk failure does not yield an inaccessible drive and/or data.
• 10K vs. 15K RPM - The drive speed for the disk drives with the general premise that the faster the better, but at a higher cost.
• iSCSI - Considered slower and much lower cheaper than Fibre Channel connectivity, iSCSI (Internet Small Computer System Interface) is a protocol used with SAN and NAS devices.  This protocol uses TCP\IP for sending and receiving the IO requests to the disk subsystem.
• Fibre Channel - Considered the high speed connectivity between a SQL Server machine and a SAN storage device.  Connectivity from the SQL Server to the SAN is via a HBA, cabling (fiber-optic or copper) to a switch and then to the SAN.  In many circumstances, there is redundancy between the SQL Server and SAN to ensure no single point of failure.
• HBA - Host Bus Adapter which is a device installed in the SQL Server to communicate with a SAN or NAS device.  The HBA acts as interface to manage the IO requests from the SQL Server and responses from the SAN or NAS device.
• SATA - Serial ATA (Advanced Technology Attachment ) which is a standard to read and write to local disks (internal cages) or direct attached storage devices (DAS\DASD).

Memory

• Hot Swappable Memory - For years, it has been possible to change a failed disk drive either as it is degrading or after failure.  Now the same concept is going to be applied to memory and processors according to this article - Microsoft demos Longhorn 'hot swap' feature In addition, Longhorn has been officially dubbed 'Windows Server 2008' in the next release of Windows.  For systems where the combination of uptime and high performance are critical, this new hardware feature should get more systems closer to the 5 nines of availability.
• Cache - Memory for temporary storage of data that is expected to be used again in the near term.  From a hardware perspective, CPUs and hard drive controllers typically have cache to improve the overall processing.  For example, L2 or L3 cache on CPUs is between 1 to 256 MB of storage to increase the speed of the CPU processing.

Virtualization

• Host - A single piece of hardware that typically has its own operating system and dedicated CPUs, memory, disk drives, etc. that is running a number of virtual machines.
• VM (Virtual machine) - One host typically has multiple virtual machines.  Each virtual machine operates as a separate physical entity on the network with its own NetBIOS name, IP address, operating system, applications, etc.  From an functional perspective, the virtual machine is no different than a physical machine, they have all of the same functional components, but do not necessarily have the same functional underlying hardware components.

Next Steps

• This tip is by no means a comprehensive list of hardware impacting a DBAs job on a daily basis.  This is only scratching the surface.  If there are other hardware related technologies that you work with on a regular basis that benefit your SQL Server environment like tape libraries, hardware encryption, etc, then shoot us an email (tips@mssqltips.com) with your input that we will share with the rest of the community.
• If you are unsure about how a specific hardware vendor implements one of these technologies, consider reviewing their web site or contact sales\support to obtain the correct understanding for that vendor's implementation of the technology.
• Depending on the size of your organization and your personal desires, hardware advancements can be very exciting or just a box that you know exists and not much else.  Regardless, knowing the basic building blocks and their impact to SQL Server is a valuable set of baseline knowledge.

대용량 VLDB의 성능관리
- VLDB와의 소리 없는 전쟁(2) -

대용량 VLDB의 성능관리에서 자주 마주치는 문제가 바로 응답 속도 저하현상이다. 대개 IT 사용자들이 시스템의 성능이 떨어질 때 하드웨어 탓으로 돌릴 때가 흔하다. 자신이 쓰고 있는 데이터베이스 성능을 유지하기 위해 땀을 흘려본 사람이라면, VLDB의 성능관리의 중요성을 다시 한번 되새겨보자.

 

권순용 | kwontra@hanmail.net

대개 대용량 데이터베이스인 VLDB에서 처음 접하게 되는 문제라면 아무래도 응답 속도 저하가 많다. 이는 비단 대용량 데이터베이스에 국한되는 것은 아니다. 데이터베이스를 이용하는 모든 업무에서 흔하게 발생할 수 있는 문제이기 때문이다. 하지만 VLDB는 응답 속도 저하가 더욱 심각하게 발생하게 된다. 그렇기 때문에 지금도 수많은 고객업체들이 VLDB에 대한 성능 관리를 고민하고 있다. 대부분 성능 관리의 중요성은 인지하지만, 어느 요소가 성능 관리의 중심인지를 모르는 경우가 많다. 이번 호에서는 많은 IT 사용자들이 고민도 하고 관심을 갖고 있는 대용량 VLDB의 성능 관리에 대해 살펴보자.

성능 관리의 중심에는 SQL이 있다

자신이 쓰고 있는 시스템에서 응답 속도 저하현상이 발생한다면 먼저 무엇을 확인해야 할까? 이럴 경우 일반적으로 해당 시스템의 관련자들이 한자리에 모이게 되는 것은 당연한 순서일 것이다. 모두 모여서 마라톤 회의도 진행되게 마련이다. 이 때 각 담당자들은 어떻게 처리해야 될지 과제 아닌 과제를 떠맡게 된다. 여기까지의 상황 설정은 성능 관리를 위한 일련의 활동이고 또 주위에서 자주 접하게 마련이다. 이럴 경우 회의에 참석해 보면 재미있는 현상이 있다.

개발자들은 서버의 자원이 부족하여 응답 속도가 저하되는 것이라고 이야기할 때가 많다. 또한, 현업은 서버 설정이나 또는 시스템 운영자들의 잘못된 관리에 의해 응답 속도 저하 현상이 발생하는 것이라고 의문을 가질 때가 흔하다. 여기까지는 그럴 수도 있는 일이다. 보다 중요한 것은 서버 담당자 또는 소프트웨어 담당자에게 응답 속도 저하의 원인 및 해결 방안을 도출하라고 요구하는 경우가 많다는 것이다. 하지만 운영 중이던 시스템이 갑자기 응답 속도 저하가 발생했다면 무엇이 문제였겠는가? 각 담당자들의 실수가 아니라면 서버 또는 소프트웨어에는 문제가 없다고 생각해야 하지 않을까? 이런 상황이라면 응답 속도 저하의 문제는 회의 때 어느 누구도 언급하지 않은 데이터의 증가 및 데이터베이스가 VLDB로 변하고 있기 때문에 생기는 성능 문제일 가능성이 높다. 현재 운영 중인 시스템의 응답 속도가 저하되었다면 시스템과 소프트웨어를 의심하지 말고 지금 시스템에 증가하는 데이터와 해당 데이터를 액세스하는 SQL을 확인해 보는 것이 좋다. VLDB의 성능 관리는 증가되는 데이터의 효과적인 관리와 데이터의 액세스 최적화에 의해 성공 여부가 좌우되기 때문이다.

어느 시스템에서 3개월 동안 CPU 사용량이 5% 가량 증가한 경우가 있었다. 이에 현업에서는 원인을 파악하려고 나섰다. 해당 시스템은 한 달에 70GB 정도의 데이터가 증가하는 시스템이었다. 결국에는 데이터의 증가와 데이터를 액세스하는 SQL의 비효율로 결론을 지었다. 하지만 현업에서는 데이터 증가에 따른 성능 저하는 이해가 안 된다고 반론을 제기하는 사람도 있었다. 그렇다면 이러한 현상이 왜 이해가 안 되는 것일까?

10GB의 데이터일 경우 이상이 없었던 데이터를 액세스하는 SQL이 데이터가 30GB가 되는 순간 악성 SQL로 변할 수 있는 것이 현실이다. 왜 이런 사실을 많은 사람들이 인지하지 못하는 것일까? 아직 늦지 않았다. VLDB의 성능 관리를 수립하기 위해 가장 중요한 요소가 데이터와 데이터를 액세스하는 SQL이라는 것을 이제라도 인지하고 성능 관리 체계를 수립한다면 VLDB를 현명하게 대처할 수 있을 것이다.

따라서, VLDB의 성능 관리는 대용량 데이터의 관리와 데이터를 엑세스하는 SQL의 관리로 구분하여 성능 관리 체계를 구축해야 할 것이다.

VLDB의 대용량 데이터 관리가 성능을 좌우한다

VLDB는 대용량 데이터베이스를 의미한다고 했다. 그러므로 관리해야 할 데이터는 TB 이상이 될 것이다. VLDB에서는 대용량 데이터를 효과적으로 관리하는 방안이 반드시 필요하다. 그렇다면 어떻게 대용량 데이터를 최적화하여 관리해야 하는 것일까?

첫 번째로 데이터를 저장하는 테이블 관리에 대한 방안이 정립되어 있어야 한다. 데이터를 저장하는 테이블에 대해 어떠한 생각 없이 생성하는 것에 익숙해져 있는 사람이 많다. 오라클 등의 데이터베이스에서 제공하는 테이블의 종류가 많다는 것을 아는가? 업무에 따라 어떤 구조의 테이블로 구성하는가에 따라 매우 효율적으로 업무를 구현할 수 있다. VLDB에는 많은 대용량 테이블들이 존재하게 된다. 이러한 대용량 데이터의 테이블들은 어떤 구조로 저장하는가에 따라 데이터 관리 및 성능에 많은 영향을 미치게 된다.

예를 들어, 통신회사의 통화 내역 테이블을 생각해 보자. 통화 내역 테이블의 경우에는 하루에도 엄청난 통화 내역 데이터가 발생하게 된다. 또한, 일반적으로 데이터의 보관 주기가 정해지게 된다. 보관 주기가 6개월이라고 한다면 현재로부터 6개월이 지난 데이터는 반드시 제거해야 할 것이다. 이와 같은 속성을 가지고 있는 데이터를 일반 테이블에 저장하게 되면 보관 주기 관리도 힘들며 성능도 저하될 수 밖에 없다.

이러한 데이터는 오라클의 경우 파티션(Partition) 테이블에 저장해야 된다. 파티션 테이블에 저장함으로써 보관 주기 관리뿐만 아니라 월별로 파티션을 구성했다면 월별 통계 작업은 엄청난 성능 향상을 기대할 수 있을 것이다. 이처럼 VLDB의 성능 관리를 수행하기 위해 대용량 데이터를 저장하는 테이블에 대한 아키텍처 관리는 반드시 수행되어야 한다.

두 번째로 디스크 I/O 분산 관리이다. 디스크 I/O 분산은 많은 사람들이 디스크 제조사 또는 운영체제 담당자에게 모든 것을 의지하는 경우가 많다. 물리적인 디스크 분산은 디스크 제조사 또는 운영체제 담당자가 수행할 것이다. 하지만, 데이터베이스에 저장되는 데이터를 모든 디스크로 분산하는 방법이 데이터베이스에 별도로 존재한다. 디스크 I/O의 최적화는 물리적인 디스크 I/O 분산과 그 위에 데이터들이 고르게 저장될 수 있도록 해야 한다.

필자가 어느 사이트에 지원나갔을 때의 일이다. 해당 사이트는 데이터가 특정 몇 개의 디스크로만 저장되어 있었다. 물론, 디스크 개수가 적다면 여러 디스크로 데이터가 고르게 분산되도록 하는 것이 어렵지는 않을 것이다. 하지만 VLDB라면 수천 개 또는 수 만개의 디스크에 데이터를 저장하게 된다. 디스크의 개수가 많기 때문에 전체 디스크로 데이터를 고르게 분산시키는 것이 어려울 수도 있다. 하지만, 디스크 I/O 분산의 문제를 최적화하는 것만으로도 성능은 3배~5배까지 향상되는 것을 보았다. 이 얼마나 놀라운 일인가?

동일한 테이블을 액세스할 경우 10개의 디스크를 액세스하는 것이 응답 속도가 빠르겠는가 아니면 1만개의 디스크를 액세스하는 것이 응답 속도가 더 빠르겠는가? 당연히 동일한 테이블을 액세스하는 경우 1만개의 디스크를 동시에 액세스하는 것이 유리할 것이다. 디스크가 1만개일지라도 10개의 디스크에 저장할 수도 있으며 1만개의 디스크로 고르게 분산하여 저장할 수도 있다. 이러한 디스크 I/O 분산 또한 성능 관리에 반드시 포함되어야 할 항목일 것이다.

세 번째로 데이터 모델링의 관리이다. 데이터 관리 차원에서의 데이터 모델링이라는 것은 각각의 테이블에 대한 컬럼들의 관리를 뜻한다.

어느 사이트에 튜닝을 지원했을 때의 이야기다. 해당 사이트는 모 카드 회사였으며 카드의 기본 정보를 저장하는 마스터 테이블이 존재하고 있었다. 거래내역을 조회하기 위해서는 고객이 소유하고 있는 모든 카드 번호를 확인한 후 거래내역 테이블과 조인을 수행하고 있었다. 해당 마스터 테이블에는 해당 카드가 사용했는지, 사용하지 않았는지를 기록하는 컬럼이 존재했다. 사용을 한번도 안 한 카드라면 해당 카드 번호로 거래내역 테이블을 액세스할 필요는 없을 것이다.

카드 사용 유무를 기록하는 컬럼을 이용하여 튜닝을 수행하고 난 이후 성능은 10배 정도가 향상됐다. 하지만, 실무자는 카드 사용 유무 컬럼을 사용할 수 없다는 말을 했다. 그 이유는 해당 컬럼에 어떤 가맹점은 사용 유무를 기록하지만 어떤 가맹점은 사용 유무를 기록하지 않기 때문에 카드 사용 유무 컬럼에 사용하지 않음이라고 되어 있더라도 해당 카드는 사용했을 수 있다는 것이다. 이 얼마나 어처구니 없는 일인가? 이런 사실을 실무자는 너무 자신 있게 이야기했다. 자신 있게 이야기하기 보다는 오히려 부끄러워해야 하는 것은 아닐까?

그런 이유에서 해당 테이블의 모든 컬럼을 확인하게 되었다. 해당 테이블에는 50개의 컬럼이 존재했지만 그 중 15개의 컬럼이 사용하기 힘든 상태였다. 이러한 컬럼을 왜 저장하고 있는지 모르겠다. 데이터의 정합성을 유지하기 힘든 컬럼들을 없앤다면 데이터는 감소한다. 이러한 컬럼들에 대해 데이터의 정합성을 맞추어 관리한다면 위와 같이 애플리케이션 성능을 향상시킬 수 있는 요소로 이용할 수 있을 것이다.

독자 여러분도 자신이 현재 관리하는 VLDB를 한번 확인해 보길 바란다. 진정으로 모든 테이블의 데이터를 다 사용하고 있는가? 분명 그렇지 않은 곳이 여기저기 존재할 것이다. 이는 VLDB에 대한 데이터 모델링 관리를 통한 성능 관리가 수행되지 않고 있기 때문이다.

대용량 데이터 관리를 위한 위의 세가지 요소는 시간과 비용이 많이 소요되는 항목들이다. 그렇기 때문에 프로젝트 초기부터 관리되어야만 VLDB의 성능 관리가 성공할 수 있다. 이러한 항목이 관리되지 않는다면 머지 않아 후회스러운 일이 발생할지도 모른다.

성능관리 핵심은 데이터를 엑세스하는 SQL이다

VLDB의 성능 관리는 데이터 관리와 함께 해당 데이터를 액세스하는 SQL의 관리이다. 많은 서적들을 참조해 보면 시스템을 최적화함에 있어 서버 튜닝 또는 데이터베이스 엔진 튜닝으로 얻을 수 있는 성능 향상은 전체 향상될 수 있는 부분의 10% 정도의 향상이 기대된다고 한다. 그렇다면 어떤 부분을 최적화 했을 때 나머지 90%의 효과를 얻을 수 있는 것일까? 그것은 바로 애플리케이션이다. 애플리케이션 중에서도 데이터를 액세스하는 부분인 SQL이 이에 해당된다. 애플리케이션에 사용되는 로직 또한 중요하다. 하지만 애플리케이션에 사용하는 로직이라는 것도 SQL에서 추출한 데이터를 조작하는 단계이기 때문에 여기서 말하는 데이터를 액세스하는 SQL이라는 것은 이러한 액플리케이션의 로직을 포함하는 것이다. 그렇다면 SQL의 어떤 항목을 통해 성능 관리를 수행해야 하는가?

첫 번째로 SQL 및 SQL의 실행 계획에 대한 계속적인 추적이 필요하다. 실행 계획이라는 것은 무엇인가? 우리가 서울역에서 분당까지 가기 위해 어떻게 가는 것이 빠른가? 여러 번의 경험이 있는 사람이라면 최적의 방법을 이용하여 목적지에 도착할 것이다. 하지만 처음 서울역에서 분당으로 가는 사람이라면 어떨까? 전철을 이용할지, 버스를 이용할 것인지 또는 버스와 전철을 동시에 이용하는 것이 적합한지 애매한 경우가 있다.

이러한 경우에도 서울역에서 분당까지 가고자 하는 그 사람의 의지가 중요할 것이다. SQL의 실행 계획도 이와 별반 다르지 않다. 인덱스를 이용하여 데이터를 추출할 것인지 또는 테이블을 전체 스캔하여 데이터를 추출할 것인지는 실행 계획을 생성하는 옵티마이저에 의해 결정된다. 이러한 실행 계획에 의해 동일한 데이터를 추출하는 SQL은 1분이 1시간으로 응답 속도가 저하될 수 있다. 최적화되어 수행된다면 1분이면 종료될 SQL이 악성 SQL로 수행되어 1시간이 소요된다면 이는 응답 속도의 저하뿐만 아니라 CPU 사용량도 증가시키게 된다.

결국, 이러한 실행 계획이 관리되지 않는다면 악성 SQL이 계속적으로 증가되어 CPU 사용량은 계속 높아만 질 것이다. 이 것이 해당 시스템의 CPU를 증설하게 되는 주범이라는 것을 눈여겨볼 필요가 있다.

결과적으로 VLDB의 성능 관리를 수행하기 위해서는 해당 VLDB를 액세스하는 SQL과 해당 SQL의 실행 계획을 반드시 추적하고 있어야 한다. 이와 같이 SQL과 SQL의 실행 계획을 추적해야 하는 또 하나의 이유가 존재한다. 그 이유는 SQL의 실행 계획이 변할 수 있기 때문이다. 서울역에서 분당을 갈 때 매번 버스를 이용하였지만 하루는 비가 많이 내렸다고 가정하자. 비가 많이 내린다면 버스는 일반적으로 정체 구간이 많이 발생한다.

그래서 이럴 경우에는 반대로 전철을 이용하여 분당에 갈수도 있을 것이다. 실행 계획 또한 이와 동일하다. 데이터베이스에 큰 변화가 발생한다면 언제든지 이러한 실행 계획은 변할 수 있다. 실행 계획이 변하면 SQL의 응답 속도가 향상될 수도 있지만 대부분 응답 속도가 저하되기 쉽다. 오라클의 경우 데이터베이스를 업그레이드 하게 되면 성능과 관련된 SQL의 실행 계획을 재점검해야 한다. 이 또한 데이터베이스의 큰 변화이므로 실행 계획이 변할 수 있기 때문이다. 급격히 데이터가 증가하는 것 또한 데이터베이스에 수많은 변화가 발생할 여지가 있게 마련이다. 이제 VLDB의 성능을 관리하고자 한다면 반드시 데이터를 액세스하는 SQL과 SQL의 실행 계획을 관리해야 한다.

두 번째로 SQL이 사용하는 인덱스의 관리이다. 이는 일반 데이터베이스에서도 동일하지만 VLDB에서는 매우 중요한 성능 관리 항목이다. 인덱스라는 것은 조회 성능을 향상시키기 위해 생성하게 된다. 인덱스는 데이터의 제거, 변경 및 저장에 대해서는 성능 저하를 발생시킨다. 단지, 조회를 위해 효율적으로 이용할 수 있는 것이 인덱스이다. 또 하나 고려할 것은 SQL이 인덱스를 이용했다고 해서 SQL이 최적화된 것이 절대 아니라는 것이다.

처리 범위를 최소화시킬 수 있는 인덱스를 이용한 것이 아니라면 해당 인덱스를 이용함으로써 응답 속도는 저하된다. 이러한 사실을 모르는 관리자 또는 개발자들이 많다는 것이 때로는 필자를 슬프게 만든다. 단지, SQL이 인덱스를 이용했다는 이유 하나만으로 최적화 되었다고 생각하지 말자. 이러한 생각은 VLDB를 최악으로 만드는 장본인이라는 것을 인지해야 할 것이다.

프로젝트 초기에는 인덱스의 생성 및 삭제가 가능할 수 있지만 시스템을 오픈하여 서비스를 하고 있다면 인덱스를 추가하거나 또는 삭제하는 것이 매우 힘들다. 그 이유는 인덱스를 생성하기 위해 많은 시간이 소요되고 생성하는 동안은 해당 테이블에 대해 서비스를 제공할 수 없기 때문이다. 또한 인덱스를 추가함으로써 다른 SQL에 어떤 영향을 미칠지 아무도 장담할 수 없기 때문이다. 전혀 다른 인덱스를 이용하게 됨으로써 발생하는 성능 저하는 해당 시스템을 마비시킬 수도 있다.

위와 같은 이유들로 VLDB의 대용량 테이블에 대해서는 프로젝트 초기부터 인덱스에 대한 전략과 운영 시 관리 방안 등이 수립되어 진행되어야만 성능 관리를 수행할 수 있다. 이러한 VLDB에서 인덱스의 관리가 수행되지 않는다면 건너서는 안 되는 강을 건너는 것과 같이 VLDB의 성능 저하에 대해 되돌릴 수 없는 상황이 된다.

세 번째로 VLDB에 대해 매일 또는 매주 I/O가 많은 SQL을 수집해야 한다. 이러한 일련의 활동은 VLDB의 성능 관리를 위해 반드시 필요한 단계이다. SQL은 데이터를 액세스하는 언어이며 데이터를 엑세스하기 때문에 I/O가 발생하게 된다. 이러한 I/O가 메모리에서 발생하던 또는 디스크에서 발생하던 I/O의 양이 많다면 성능은 반드시 저하된다. I/O를 많이 발생시키는 SQL의 수집은 VLDB의 성능 관리를 위한 많은 정보를 제시해 준다.

예를 들어, 해당 VLDB 시스템의 CPU 사용량이 갑자기 10%가 높아졌다고 가정하자. 이런 경우 서버 및 데이터베이스를 확인할 것이다. 이런 일련의 확인 작업을 수행한 후 아무런 문제가 없다면 무엇을 확인해야 하는가? 바로 I/O가 많은 SQL을 검토해야 한다. 십중팔구 데이터의 증가와 함께 I/O가 많은 SQL에 의해 이러한 CPU 사용량이 증가했을 것이다. 데이터베이스는 별도의 툴을 이용하지 않는다면 이러한 I/O가 많은 SQL을 저장하지 않게 된다. 단지, 실시간으로 I/O가 많은 SQL을 추출할 수 있다.

따라서, I/O가 많은 SQL에 대해서는 반드시 수집하는 스크립트 등을 작성하여 정해진 시간에 수행되게 해야 할 것이다. 이것이 VLDB의 성능 관리의 중요한 요소이자 또한 장애 관리에도 많은 도움이 될 것이다. 성능과 장애는 분리할 수 없는 관계에 있다. 이러한 I/O가 많은 SQL이 성능을 저하시키고 이것은 장애로 연결되기 때문이다.

앞에서 확인한 것과 같이 VLDB에서 SQL에 대한 관리는 성능 관리에 있어 중요한 요소가 된다. 그러므로 데이터를 액세스하는 SQL, SQL의 실행 계획 및 인덱스에 대한 관리 방안이 반드시 필요하다. 또한, I/O가 많은 SQL의 수집은 성능 관리를 하기 위한 기본이 될 것이다. 수집된 데이터를 바탕으로 분석을 수행하여 성능 관리를 수행해야 할 것이다.

성능 관리는 데이터 수집, 분석 반복되어야

VLDB의 성능 관리는 데이터 관리 및 데이터를 액세스하는 SQL의 관리에 대한 정확한 방안이 수립되어야 한다. 성능 관리는 성능 데이터의 수집부터 시작하게 된다. 성능 데이터가 수집된다면 내용을 분석하여 튜닝을 수행하게 된다. 정확한 분석을 통해 튜닝을 수행한다면 해당 VLDB는 성능 관리를 위한 1사이클(Cycle)이 종료될 것이다. VLDB의 성능 관리는 여기서 끝이 아니다. 이러한 사이클이 반복해서 수행되어야 한다는 것이다. 1년간 이러한 성능 관리 활동을 수행했다고 VLDB가 계속 최적화된다는 보장은 없다. 해당 시스템이 업무를 종료하는 그 날까지 성능 관리는 매일 수행되어야 한다. 이 것이 VLDB의 운명은 아닐까?

VLDB의 성능 관리에서 또 하나의 중요한 항목은 사람이다. 성능 데이터 수집은 방안만 수립된다면 간단한 작업일 것이다. 하지만 이러한 성능 데이터 수집 방안을 수립하고 추출된 성능 데이터를 분석하기 위해서는 많은 부분에서 사람이 관여할 수 밖에 없다. 사람이 관여해야 하기 때문에 중요하다는 것은 아니다.

다른 분야도 그렇겠지만 VLDB의 성능 관리는 담당자의 실력에 의해서 성공과 실패가 좌우된다. 이제라도 VLDB를 위한 성능 관리 전문가를 육성해야 할 시기라는 것을 이해하길 바란다.

필자소개
SKC&C에서 DBA를 수행하면서 여러 프로젝트에서 DB 아키텍처, 모델링, 튜닝을 담당하고 있다. 특히 DB 성능관리 문제가 발생하는 프로젝트를 처리하는 데 역점을 두고 있다. 저서로는 <Perfect 오라클 실전 튜닝>과 <초보자를 위한 오라클10g>가 있다.

제공 : DB포탈사이트 DBguide.net

 VLDB성능관리#2

VLDB와의 소리 없는 전쟁
- 대용량 DB의 개념 및 발생 환경 -

VLDB는 Very Large Database의 약자이다. 결국, VLDB는 매우 큰 데이터베이스를 뜻하며 이는 대용량 데이터베이스를 의미한다. 대용량 데이터베이스라는 말은 1990년대부터 나왔던 용어이다. 그 당시는 100GB 정도의 데이터만 되어도 대용량 데이터베이스라고 했었다. 하지만 이제는 100GB 정도의 데이터베이스를 대용량 데이터베이스라고 부르는 사람은 한 명도 없을 것이다.

 

글 _ 권순용｜kwontra@hanmail.net

 

IT시장도 세월이 흐르면서 대용량 데이터베이스에 대한 기준이 바뀌고 있다. 객관적인 기준은 없지만 필자는 대용량 데이터베이스를 GB 단위의 데이터베이스가 아닌 TB 단위의 데이터베이스라고 정의하고 싶다. 이번 호에서는 VLDB의 개념과 VLDB에서 발생하는 현상에 대해 알아 보도록 하겠다.

21세기 IT의 재앙은 VLDB인가?

언론에서 작년 말에 모 통신 회사의 가입 고객이 2,000만 명을 넘어섰다고 발표했다. 그것은 무엇을 의미하는 것일까? 이런 내용을 언론을 통해 알고 나면 습관처럼 계산하는 것이 하나 있다. 바로 데이터의 크기에 대한 계산이다. 2,000만 명이 하루에 다섯 통화씩 한다고 가정하면 하루 1억 건의 통화 데이터가 발생하게 되며 한 달에 30억 건의 데이터가 쌓이게 된다.

통화 데이터는 법적으로 보관해야 하는 개월 수가 정해져 있기 때문에 6개월을 보관한다고 가정하면 180억 건의 데이터가 쌓이는 대용량 테이블이 될 것이다. 이렇다면 해당 시스템은 누구나 VLDB라고 부르게 될 것이다. 또 하나의 예를 들어보자. 어느 카드 회사의 고객이 1,000만 고객이라고 가정하자. 카드 고객이 하루에 한번씩 사용한다고 가정하면 하루 발생 데이터는 1,000만 건이 된다. 한 달이면 3억 건의 데이터가 된다. 이 또한 어느 누가 VLDB라고 부르지 않겠는가?

예전에는 상상할 수 없었던 이러한 VLDB들이 현재는 흔하게 생겨나고 있다. 특히, 회사들의 DW 시스템은 해당 회사에서 발생하는 대부분의 데이터를 취합하기 때문에 거의 대부분 VLDB가 된다. 시대가 변화면서 우리 일상에는 많은 변화가 발생했다. 우리의 눈에는 보이지 않지만 시대가 변함으로써 변경된 것 중에 하나가 VLDB의 탄생이다.

데이터베이스가 VLDB로 다시 태어나는 순간 우리에게는 예상치 못한 일들이 발생하게 된다. 응답 속도 저하, 장애 증가, 백업과 복구 시간의 증가, 비용 증가 및 일반 작업 시간 증가 등의 현상이 발생하게 된다. 발생하는 현상들을 보면 데이터베이스가 VLDB로 변경되는 순간 악영향만 있을 뿐 좋아지는 현상은 없다. 데이터의 증가는 시스템적인 면에서는 어떠한 장점도 제공하지 않게 된다. 단지, 데이터의 증가에 의한 VLDB는 우리에게 해결해야만 하는 쉽지않은 과제만을 남기게 되었다.

수많은 회사들이 지금 VLDB에 대한 고민을 하고 있다. 해결 방안을 찾으려고 갖은 노력들을 하고 있는 것을 지켜보기도 했다. 하지만 데이터와 데이터베이스의 이해도가 부족한 사람들이 VLDB의 해결 방안을 찾고 있었다면 과연 VLDB의 해결 방안을 찾을 수 있을까? 필자는 데이터와 데이터베이스를 이해하는 사람이 그러한 방안을 찾아야 한다고 생각한다.

이러한 VLDB의 문제점에 대한 해결 방안을 찾지 못한다면 몇 년이 지나지 않아서 큰 문제를 발생시킬 것이다. 최악의 경우에는 해당 회사의 존재 유무에 영향을 줄 수 있을 것이다. 그렇게 된다면 이것이 21세기 IT의 재앙이 아니고 무엇이겠는가? VLDB가 발생시킬 수 있는 재앙에 대해 미리 준비하고 최적화해야 할 시간이 별로 많이 남아 있지 않다. VLDB의 재앙을 극복하는 회사가 결국 사업을 영속적으로 이끌어 갈수 있으리라 생각한다.

데이터는 운영 데이터와 이력 데이터로 구분된다

VLDB를 이해하기 위해서는 데이터베이스에 존재하는 데이터의 속성을 이해해야 한다. 데이터의 속성을 이해해야 VLDB에서 관리해야 할 데이터를 이해할 수 있을 것이다. 운영 중인 데이터베이스의 데이터는 운영 데이터와 이력 데이터로 구분할 수 있다.

운영 데이터는 실제 운영에 필요한 메타 데이터에 해당된다. 예를 들어, 카드 회사의 카드 마스터 테이블이라는 것이 존재한다고 가정하자. 신규 카드를 만들게 되면 해당 테이블에 한 건씩 데이터가 저장된다. 이렇다면 결국 해당 테이블의 총건수는 카드 회사에서 발급한 카드의 건수와 동일할 것이다. 어느 카드 회사가 1,000만 고객이라고 한다면 해당 테이블은 1,000만 건이 될 것이다.

그럼 카드 회사의 카드 마스터 테이블이 급격하게 증가할 수 있는가? 일반적으로 하루에 몇 천만 건씩 증가하지는 않을 것이다. 몇 천만 건이 아니라 몇 만 건도 증가 하지 않을 것이다. 통신 회사의 예를 들어 보자. 통신 회사에서 가입 고객의 수가 하루에 얼마나 늘어날까? 통계에 따르면 통신사별로 순수 증가 가입자가 한달 평균 10만 명 정도인 것으로 나타나고 있다. 한 달에 10만 건의 데이터가 발생하는 것은 그렇게 많은 데이터는 아닐 것이다. 카드 회사의 카드 마스터 테이블 또는 통신 회사의 가입 고객 테이블은 운영을 위한 기초 데이터를 가지고 있다.

논리 모델링시 키 엔티티(Key Entity)로 도출되는 테이블들이 운영 데이터의 역할을 수행하게 된다. 논리 모델링시 도출되는 운영 데이터를 보관하는 키 엔티티는 데이터베이스를 VLDB로 만드는 어느 정도의 역할은 수행하더라도 데이터베이스를 VLDB로 변경하는 근본적인 역할은 수행하지 않게 된다.

이력 데이터는 키 엔티티에 의해 발생하는 데이터에 해당될 것이다. 이를 논리적 모델링에서는 액션 엔티티(Action Entity)라고 한다. 예를 들어 카드 회사의 거래내역 테이블을 생각해보자. 거래내역 테이블의 데이터는 앞서 언급한 카드 마스터에 있는 각각의 카드에 의해서 만들어 지는 데이터이다. 거래 내역 테이블에는 각 카드가 사용한 데이터가 저장되며 하나의 카드는 하루에 여러 건의 데이터를 만들어낼 수 있다.

따라서 카드 회사의 거래내역 테이블은 대용량 테이블로 만들어질 것이다. 어디 이 뿐인가? 일반적인 거래내역 테이블은 일정 기간의 거래 내역을 보여주기 위해 과거의 데이터도 보관하고 있다는 것이다. 따라서 거래 내역 테이블의 크기는 상상을 초월하는 크기가 될 것이다. 이러한 카드회사의 거래 내역 테이블 뿐만 아니라 통신회사의 통화 내역 테이블도 동일하다. 통화 내역 테이블 역시 가입 고객에 의해 발생하는 논리 모델링에서 액션 엔티티에 해당된다.

통화 내역 테이블에는 가입 고객이 사용한 하나의 통화에 대해 하나의 데이터가 저장된다. 또한 통화 내역 테이블은 이력을 관리해야 하기 때문에 대용량 테이블로 탄생할 것은 명백한 사실일 것이다. 결국, 논리적 모델링에서 액션 엔티티로 표현되며 이력 데이터를 저장하는 테이블들이 데이터베이스를 VLDB로 만드는 주범일 것이다.

데이터의 증가와 응답 속도는 반비례 관계인가?

데이터베이스가 VLDB로 변하면서 발생할 수 있는 첫 번째 영향이 응답 속도이다. 일반적으로 데이터가 증가하면 응답 속도는 저하되기 쉽다. 이력 데이터가 1억 건이였을 경우 조회 쿼리의 응답속도가 1초였다고 가정하자.
이러한 이력 데이터가 2억 건이 된다면 이전 응답 속도인 1초 만에 해당 쿼리가 수행되기에는 힘들 것이다. 쿼리의 응답 속도는 디스크 블록 엑세스의 양과 밀접한 관계를 가진다.

디스크 블록 엑세스가 적다면 전체 데이터가 몇 건인가에 관계 없이 응답 속도를 보장 받을 수 있다. 하지만 데이터가 증가한다는 것은 엑세스해야 하는 디스크 블록의 증가를 암시하고 있다. 따라서 데이터 증가와 응답 속도 저하는 완전한 반비례 관계는 아니지만 VLDB에 대한 준비가 없다면 데이터 증가에 따라 응답 속도는 반비례 관계로 저하될 것이다.

예를 들어, 어느 통신회사의 통화 내역 테이블을 보자. 시대의 흐름에 따라 개인마다 한 대 이상의 휴대폰을 소지하면서 가입자가 증가하게 되었다. 가입자들은 개인적인 일뿐만 아니라 회사일 때문에도 많은 통화를 하게 된다. 이러한 한 통화 한 통화가 해당 통신 회사의 통화 내역 테이블에 저장된다. 통신 회사 가입 회원이 1,000만 명이라고 가정하자. 한 사람이 하루에 다섯 통화만 하게 되면 하루 데이터 량은 5,000만 건에 육박하게 된다. 또한 최근 6개월 전 통화 내역까지 고객에게 조회 가능하게 한다고 가정하자. 그렇다면 대용량의 통화 데이터가 생성될 것이다. 초기에는 특정 번호로 통화 내역 데이터를 엑세스하면 1초가 소요되었다고 가정하자.

그러나 계속적으로 가입자가 증가하게 되면 가입자들에 의해 발생하는 통화 데이터는 기하급수적으로 증가될 수 있다. 그렇다면 기존 쿼리의 응답 시간이 데이터의 증가에도 불구하고 1초가 유지될 것인가? 정답은 VLDB에 대한 최적화된 준비가 되어 있지 않다면 응답 속도는 저하된다는 것이다.

데이터의 증가는 증가된 데이터와 관련된 프로그램의 응답 속도를 저하시키는 것은 당연할 수 있다. 증가하는 데이터에 대해 어떠한 고려도 없다면 해당 시스템의 프로그램들은 점차 응답 속도가 저하되게 된다. 이를 해결하기 위해서는 기존 VLDB에 대한 성능 관리 고도화 및 새로 탄생하는 VLDB에 대한 최적화된 관리 방안 수립이 필요할 것이다.

VLDB에서 비용 증가는 왜 계속 되는가?

대부분의 회사에서는 매년 말 또는 매년 초 관리하고 있는 시스템에 대한 용량 증설을 계획한다. 용량 증설에는 보통의 경우 CPU, 메모리 및 디스크가 대상이 된다. 이러한 용량 증설을 위해 과연 무엇을 검토하는가? 용량 증설을 하기 위해서는 많은 데이터를 기초 데이터로 하여 용량 증설을 하게 된다.

CPU 용량 증설에 대한 기준은 보통의 경우 CPU 사용률이 된다. 디스크의 용량 증설의 기준은 현재 디스크 사용률과 데이터 증가량이다. 마지막으로 메모리 증설의 경우는 CPU 증설에 따라 결정되는 것이 보편적인 방법일 것이다.

필자가 지원했던 곳 중에 VLDB에 대해 매년 용량 증설로 10억 원 이상의 비용이 소요되고 있는 곳이 있었다. 그렇다면 막대한 비용이 매년 소요되는 용량 증설은 어쩔 수 없는 현상인가? 필자는 이 질문에 대해 아니라고 말하고 싶다. 이러한 비용이 적절하게 사용되는 부분도 있겠지만 어느 정도는 잘못 투자되는 부분도 존재한다고 생각한다.

CPU 사용률에 의해 CPU와 메모리가 증설된다. 중요한 점은 CPU 사용량만 보고 증설을 한다는 것이다. CPU 사용률이라는 것이 무엇인가? VLDB에서는 악성 SQL이 하나 수행되면 CPU 사용률은 많이 높아지게 된다. 그런 악성 SQL이 몇 개 수행된다면 해당 시스템은 용량 증설 대상이 될 것이다. 이런 이유에서 CPU 사용률이 높아진 시스템이 CPU 증설 대상이 되어야 하는 것인가? 시스템 관리자도 이러한 사실을 잘 인지하지 않고 있는 현실이 필자로서는 너무 아쉽다.

이런 악성 SQL을 최적화한다면 해당 VLDB 시스템은 CPU 사용량이 감소하게 된다. 그렇다면 CPU 또는 메모리 증설 비용이 추가되지 않을 수도 있을 것이다. 또한 배치 작업을 최적화하고 디스크 I/O 분산을 최적화한다면 이 또한 CPU 사용량이 감소하게 된다. 왜 이런 근본적인 문제를 해결하려 하지 않고 CPU와 메모리를 추가하려 하는가? 이와 같이 CPU 사용량만 보고 용량 증설을 한 시스템은 매년 이유도 모르고 용량 증설을 계속하게 마련이다.
이 얼마나 무서운 일인가? 필요한 용량 증설에는 비용을 투자해야 하는 것이 맞다. 하지만 VLDB에서 종종 발생하고 있는 잘못된 용량 증설에 의한 비용은 회사의 발전을 위해 사용해야 하는 비용을 엉뚱한 곳에 사용하고 있는 셈이다.

디스크 증설은 디스크 사용률과 데이터 증가량에 의해 결정된다. VLDB는 보통의 경우 대용량 이력 데이터의 증가로 용량을 증설하게 된다. 이러한 디스크 용량 증설이 반드시 필요한 것일까? 필요할 수도 있고 아닐 수도 있다. 집의 평수가 10평일지라도 이를 30평처럼 효과적으로 사용하는 사람이 있는가 하면, 30평에 살고 있어도 거의 10평 남짓한 집처럼 사용하는 사람이 있다.

VLDB의 디스크도 이와 별반 다르지 않다. 10TB의 VLDB가 진정한 10TB의 VLDB인지는 아무도 모른다.

요즘은 대부분의 데이터베이스 업체에서 데이터 압축 기술을 제공하고 있다. 이를 잘 이용한다면 10TB의 데이터가 5TB의 데이터로 변할 수 있는 것이 현실이다. 또한 데이터베이스의 특성 때문에 잘못 이용하여 불필요하게 낭비되는 공간도 있을 수 있다. 중요한 점은 논리적 모델링에서 정규화가 잘못되면 데이터는 기하급수적으로 증가하게 되며 인덱스 선정을 잘못하면 응답 속도 저하뿐만 아니라 엄청난 디스크를 사용하게 된다.

데이터베이스를 잘못 구성하고서 디스크가 부족하다는 이유로 디스크를 증설하는 것이 옳은 일인가? 이런 잘못된 데이터베이스 아키텍쳐와 논리적 모델링을 최적화하지 않는 한 해당 VLDB는 매년 대규모의 디스크를 증설해야 할 것이다.

VLDB에 대해 용량 증설을 하는 과정을 보면 필요한 부분에 대한 용량 증설을 하는 경우도 많지만 주먹 구구식으로 용량 증설을 하는 경우도 흔하다. 이런 경우 필자가 보기에는 귀찮아서가 아니라 VLDB에 대한 이해가 부족하기 때문에 발생하는 현상인 것 같다. 더 이상 VLDB를 간과할 수 없는 것이 대다수 기업의 현실이다. 체계적인 용량 증설에 대한 이해가 없다면 VLDB에 대한 용량 증설은 그 회사의 비용을 계속적으로 발생시키게 된다.

VLDB의 데이터 백업과 복구는 불가능 한가?

VLDB에 대해 데이터베이스가 너무 크기 때문에 백업 시간이 많이 소요되므로 업무에 지장을 초래한다는 얘기가 있다. 이는 백 번 맞는 말이다. 왜냐하면 백업을 수행하는 동안은 데이터베이스의 성능이 저하되기 때문이다. 그렇다고 VLDB여서 백업을 수행하지 못한다는 것은 이해되지 않는 말이다.

또한 VLDB는 장애 시 복구가 힘들 수 있지만 복구를 못하는 것은 아니다. 그렇다면 이러한 VLDB에 대한 백업과 복구에 대한 전략은 누가 수립해야 하는가?

어느 회사를 지원했을 때 필자가 경험했던 일이다. 그 회사는 시스템 관리자와 DBA 및 백업 관리자가 분리되어 있었다. VLDB에 대한 백업과 복구에 대한 전략을 개발자 또는 시스템 관리자가 수립하지는 않을 것이다. 그렇다면 백업 관리자인가? 이 또한 아닐 것이다. VLDB에 대한 백업과 복구에 대한 전략은 반드시 데이터베이스와 데이터를 이해하는 담당자가 계획하고 수행해야 한다는 사실이다. 데이터베이스의 아키텍쳐와 데이터의 구조를 모르는 백업 관리자가 어떻게 백업과 복구에 대해 전략을 세울 것인가? 필자가 지원했던 그 회사는 데이터베이스와 데이터를 이해하지 못하는 백업 관리자가 VLDB에 대한 백업과 복구의 모든 전략 및 계획을 수립했기 때문에 힘들어 하지 않았나 생각한다.

데이터베이스는 여러 가지 방법으로 데이터 백업을 지원한다. 어떤 데이터베이스는 데이터 증감 분에 대해 백업을 수행할 수 있게 해준다. 어떤 데이터베이스는 변경 작업이 발생하지 않는 데이터에 대해서는 한번 백업을 수행한 백업 본으로 언제든 복구를 수행할 수 있게 기능을 제공한다.

또 어떤 데이터베이스는 단순 파일 복사로만 백업을 대신할 수도 있다. 그리고 대부분의 데이터베이스는 데이터를 저장하고 있는 테이블 단위로 백업을 수행할 수 있게끔 기능을 제공하고 있다.

이렇게 다양한 방법의 백업방법이 제공되고 있지만, 이러한 방법을 효과적으로 이용하지 않고 전체 데이터에 대해 백업을 수행하는 경우를 자주 보아 왔다. 이렇게 전체 데이터를 매일 백업 받기 때문에 백업 시간이 많이 소요되고 이에 따라 시스템 성능 저하가 발생하게 되므로 백업을 수행하지 못한다는 얘기를 들을 때가 있다. 이 어찌 백업을 못 받는 것인가? 백업을 안 받는 거라고 해야 되지 않을까?

전체 데이터를 백업 받은 것으로 복구를 수행하는 경우는 그리 흔한 일은 아니다. 하지만 전체 데이터를 복구할 수 있는 백업은 반드시 백업 전략에 포함되어야 한다.

VLDB에서 전체 데이터를 복구하는 것은 데이터가 많기 때문에 당연히 많은 시간이 소요될 것이다. 하지만 전체 데이터베이스를 복구해야 하는 상황은 거의 발생하지 않는다. 보통의 경우 테이블스페이스 단위이거나 오브젝트 단위가 흔하게 발생하게 된다. 이런 방향으로 VLDB 복구 전략이 수립되고 복구를 수행한다면 빠르게 복구를 수행할 수 있을 것이다.

VLDB는 백업도 어렵지만 복구도 어려운 것이 사실이다. 하지만 데이터베이스에서 제공하는 많은 기능들을 효과적으로 사용하고 데이터의 감소를 성공적으로 달성하여 최적화된 백업 및 복구 전략을 수립할 수 있다면 VLDB의 백업과 복구가 어렵지 만은 않을 것이다. 이런 준비 없이 VLDB를 운영한다면 언젠가는 눈물을 흘리는 날이 올 것이다.

데이터가 증가함에 따라 일반적인 운영에 소요되는 시간은 증가하게 된다. 운영에 소요되는 시간이 증가하는 것이 문제인 것은 아니다. 오히려 문제는 운영에 필요한 많은 작업이 서비스 정지 시간을 필요로 한다는 사실이다. 이러한 서비스 정지 시간이 필요한 운영 작업이 데이터의 증가에 따라 소요시간이 증가되므로 서비스 정지 시간도 증가하게 될 것이다.

대부분의 업무 관리자들은 시스템이 무정지 시스템으로 운영되길 원한다. VLDB는 이러한 무정지 시스템을 구현하기 위한 제약 조건에 해당된다. 대용량 테이블에 인덱스를 하나 생성하는 것 조차 VLDB는 힘들 수 있다. 하지만 우리가 앞서 언급한 문제점들을 제거하는 과정을 거치게 되면 데이터는 감소하게 된다. 또한 데이터베이스에서 제공하는 수 많은 VLDB에 최적화된 기능을 사용한다면 서비스 정지 시간을 없애거나 감소시킬 수 있을 것이다.

CPU에 해당하는 Task 보기

select  
	 ss.scheduler_id ,ss.parent_node_id,object_name(sql_text.objectid, sql_text.dbid)  as 'SP명'
	,ses.session_id , ses.host_name , ses.program_name , ses.client_interface_name 
	,ses.login_name 
	 ,substring(sql_text.text,sr.statement_start_offset/2,
			(case when sr.statement_end_offset = -1
			then len(convert(nvarchar(max), sql_text.text)) * 2
			else sr.statement_end_offset end - sr.statement_start_offset)/2) as query
from sys.dm_os_schedulers as ss with (nolock) 
inner join sys.dm_os_tasks as st with (nolock) on ss.scheduler_id = st.scheduler_id
inner join sys.dm_exec_requests as sr with (nolock) on st.request_id = sr.request_id
inner join sys.dm_exec_sessions as ses with (nolock) on sr.session_id = ses.session_id 
cross apply sys.dm_exec_sql_text(sr.sql_handle) as sql_text
-- where ss.parent_node_id = 2 -- numa 노드에서 실행되는 쿼리
-- where ss.scheduler_id = 24  -- cpu 스케줄에 할당된 쿼리
 

ALTER DATABASE DB_Name SET EMERGENCY
go
ALTER DATABASE DB_Name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
DBCC CHECKDB('DB_Name',REPAIR_ALLOW_DATA_LOSS)
go
ALTER DATABASE DB_Name SET MULTI_USER WITH ROLLBACK IMMEDIATE

http://sqlblog.com/blogs/kalen_delaney/archive/2007/09/29/geek-city-clearing-a-single-plan-from-cache.aspx

DBCC FREESYSTEMCACHE(TokenAndPermUserStore)    

--보안 캐쉬를 제거

DBCC FREESYSTEMCACHE    

--사용하지 않는 항목을 모든 캐시에서 직접 제거

DBCC FREEPROCCACHE

--프로시져 캐시에서 모든 요소를 제거

DBCC FLUSHPROCINDB (dbid)

--지정한 데이터베이스에 있는 스토어드 프로시저 캐시를 제거

Microsoft Windows 2000 Server has an internal structure known as the master boot record (MBR) that limits the maximum number of hidden sectors to 63. This characteristic of the MBR causes the default starting sector for disks that report more than 63 sectors per track to be the 64th sector. Therefore, when programs transfer data to or from disks that have more than 63 sectors per track, misalignment can occur at the track level, with allocations starting at a sector other than the starting sector. This misalignment can defeat system optimizations of I/O operations that are designed to avoid crossing track boundaries.

Diskpar.exe is a command-line tool from the Windows 2000 Server Resource Kit that can explicitly set the starting offset in the MBR. By doing this, the track is aligned with the physical disk partition, which results in an improvement in disk performance. Exchange writes four kilobytes to the database and up to 32 kilobytes for the streaming data. Therefore, make sure that you set the starting offset to be a multiple of four kilobytes.

방법

http://support.microsoft.com/kb/929491/en-us

invalid-file