Bora.Choi

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 

 SET STATISTICS IO ON 을 실행하고 프로시저나 쿼리를 실행할때

 logical reads 820340, physical reads 0, read-ahead reads 820333 현상이 보인다. 이때 read-ahead는 무엇인가?

• read-head는 페이지를 읽을때 메모리에 이미 있으면 그 값을 반환하는것 같음
• 병렬 처리 실행에서는 사용할 수 없음
•  결국, 높다고 안 좋은건 아닌듯 함

SQL Server 2008

http://msdn.microsoft.com/en-us/library/ms191475.aspx

• SQL Server 2000

• SQL Server 2000 uses ReadFileScatter to perform read-ahead operations. SQL Server uses sophisticated algorithms to retrieve data pages that are going to be used in the immediate future.

  For example, if you run a query that can use an index to determine applicable rows, a read-ahead may occur on the actual data pages that are needed to complete the select list. As index entries are identified, SQL Server can post OVERLAPPED (async) I/O operations for the data pages that will be used in upcoming steps of the query plan. This is how a query using a bookmark lookup operator uses read-ahead.

  This example is just one of many read-ahead situations that SQL Server can use. Allowing the index searching to continue while the data page I/O is in progress maximizes the CPU and I/O of the system. The I/O is often completed by the time it is needed so other steps in the plan have direct memory access to the needed data and do not have to stall while waiting on I/O.

  When a read-ahead is posted, it can be from 1 to 1,024 pages. SQL Server limits a single read-ahead request depth to 128 pages on most editions. However, Microsoft SQL Server Enterprise Edition raises the limit to 1,024 pages.

  SQL Server uses the following steps to set up read-ahead.

  1. Obtain the requested amount of buffers from the free list.

  2. For each page:

     1. Determine the in-memory status of the page by doing a hash search.

     2. If found to be already in memory, set up the read-ahead request to immediately return the buffer to the free list upon I/O completion.

     3. Establish the proper I/O request information for ReadFileScatter invocation.

     4. Acquire I/O latch to protect buffer from further access.

     5. If the page is not found in hash search then insert it into the hash table.

  3. Issue the ReadFileScatter operation to read the data.

  When the I/O operation is complete, each page is sanity checked for a valid page number and torn page errors. In addition, various other data integrity and safety checks are performed. The I/O latch is then released so the page is available for use if it is located on the hash chain. If the page was determined to be already in memory, the page is immediately discarded to the free list.

  This process shows the key factors of SQL Server I/O patterns. Read-ahead goes after pages that can already be in memory or not allocated. Because SQL Server maintains the in-memory buffers and hash chains, SQL Server tracks the page’s state. Importantly, read-ahead processing opens the door for overlapping read and write requests at the hardware level.

  If a page is already in memory when the read-ahead request is posted, the contiguous read is still needed and is faster than breaking up read requests into multiple physical requests. SQL Server considers the read to be unusable for the page in question, but many of the pages around it may be usable. However, if a write operation is in progress when the read is posted, the subsystem has to determine which image of the read to return. Some implementations return the current version of the page before the write is complete; others make the read wait until the write completes; and yet others return a combination, showing partially new data and partially old data. The key is that SQL Server will discard the read as unusable but the subsystem needs to maintain the proper image for subsequent read operations. The in-progress write, when completed, must be the next read image returned to the server running SQL Server.

  Do not confuse read-ahead with parallel query plans. Read-ahead occurs independently of the parallel query plan selection. The parallel plan may drive I/O harder because multiple workers are driving the load, but read-ahead occurs for serial and parallel plans. To ensure that parallel workers do not work on the same data sets, SQL Server implements the parallel page supplier to help segment the data requests.

  SQL Server has added increased diagnostics to report previously unreported read failures. The Microsoft Web site contains the following Knowledge Base article that provides diagnostic installation and usage instructions.

  • Additional diagnostics added to SQL Server to detect unreported read failures
    http://support.microsoft.com/default.aspx?scid=kb;en-us;841776

원본출처: http://blogs.msdn.com/bartd/attachment/747119.ashx

Below you’ll see sample -T1204 output for a very simple deadlock between two spids, spid 51 and spid 52.   The deadlock is represented as a list of “nodes”.  Each node represents one resource – most commonly a lock – that is involved in the deadlock.  For each node, or lock, information is provided about both the spid that is blocked waiting for access to this resource, and about the spid is already has a lock on the resource that is blocking the first spid.  A simple deadlock involves two spids and two locked resources, but deadlocks involving 3 or more spids are also possible. 

The -T1204 output is annotated to explain the most relevant parts of the output.  If you are viewing this page in Internet Explorer, you may need to enable active content for the page before you can see the annotations.  Hover over the comment markers to see the annotations. 

By using the info highlighted in the first node above, and the same items from the second node, you can reconstruct the following more readable description of the deadlock scenario:

Spid 52 is running a DELETE statement on line 6 of the stored proc spClearItemStatus.  He holds an X lock on the key resource KEY: 7:2121058592:2 (a70064fb1eac).  This lock is blocking spid 51, who is waiting to acquire a U lock on the same key.  

Spid 51 is running an UPDATE statement on line 47 of the stored proc spUpdateItemProp.  He holds an X lock on key KEY: 7:1977058079:1 (02014f0bec4e).  His X lock is blocking spid 52, who is waiting to acquire an X lock on the same key. 

So, spid 51 is blocked by 52, while spid 52 is blocked by 51.  This is a circular blocking chain, which is another name for a deadlock. 

It would be better to identify the key lock using a more meaningful table name and index name instead of using cryptic resource IDs like “KEY: 7:2121058592:2 (a70064fb1eac)”.  It is not possible to figure out what tables and indexes are involved from -T1204 output alone because we don’t have the necessary data to decode these lock resource IDs.  For example, the resource “KEY: 7:2121058592:2 (a70064fb1eac)” corresponds to a particular index key in nonclustered index 2, on a table with object ID 2121058592, in the database with dbid 7.  You can figure out what table and index this is by looking at sysobjects and sysindexes for the relevant database.  After decoding the lock resource IDs and identifying the specific queries involved, the description of the first node might look like this:

http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-TF-1118.aspx

익스텐트 공간 할당  참고

http://ceusee.textcube.com/23

SQL Server의 데이터베이스는 데이터 파일과 로그 파일로 구성됨.

로그 파일은 앞에서 설명한 VLF로 구성된다.

반면, 데이터 파일의 가장 작은 단위는 페이지이다.

연속적인  8개의 페이지로 extent가 구성된다.

extents는 'single-extent' (균일 익스텐트), 'mixed extent'(혼합 익스텐트) 로 나뉜다.

균일 익스텐트는 하나의 개체가 8개의 페이지를 모두 사용 하며, 혼합 익스텐트는 하나의 익스텐트에 여러 개체가 들어간다.

테이블을 만들때는 익스텐트 단위로 일어나는데 SQL은 처음 테이블을 만들때는 항상 혼합 익스텐트 내에서 생성한다.

테이블이 커져가서 8 개의 page를 채훈 후 부터 균일 인스텐트를 할당한다.

* GAM, SGAM

SQL Server는 전역 할당 맵(GAM) 과 공유 전역 할당 맵(SGAM) 이라는 두가지 할당 맵을 사용하여 익스텐트, GAM, SGAM은 각 1bit로 하나의 익스텐트를 관리함으로 총 6400 익세텐트를 관리 (4GB)

GAM : 어떤 익스텐트가 할당되었는지 관리

SGAM : 어떤 익스텐트가 현재 혼합 익스텐트로 사용되는지, 빈 페이지를 가지는지 여부 관리

SGAM : 1인것이 존재 하지 않으면, 1:0 인것을 찾아서 할당하고 비트 패턴을 0:1 로 변경한다.

2. GAM과 SGAM은 데이터 파일 하나씩 있으므로 해당 DB의 파일 개수를 늘려주면 SGAM에 대한 요구가 분산되어 보다 좋은 성능을 낼 수 잇다.

임시테이블은 모두 tempdb에서 처리됨으로 tempdb의 데이터 파일을 여러개로 분리하는 것이 많은 수의 임시 테이블 요구에 대한 할당 잠금 측면에서 유리하다.

* PFS (Page Free Space)

힙, ntext, text, image 컬럼의 페이지가 할당되었는지 여부와 각 페이지의 빈 공간 크기를 기록한다.

하나의 PFS 페이지는 약 8000 page = 1000 익스텐트 관리하며, 파일이 커지면 연결 리스트 구조로 새로운 PFS 페이지가 추가된다.

* BCM (Bulked Change Map)

마지막 Backup log 문 이후 대량 기록 작업에 의해 수정된 익스텐트를 관리, 대량 로그 복구 모델일 때 사용됨

* DCM

마지막 bakcup dataase (full backup) 이후에 변경된 익스텐트를 관리하는 페이지이며, 차등 백업의 대상 익스텐트를 찾기 위해 사용,

BCM, DCM도 1bit로 하나의 익스텐트를 관리함으로 총6400 익스텐트 관리하게 된다.

* IAM (Index Allocation Map)

개체가 사용하는 데이터베이스 파일의 익스텐트를 매핑한다.

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.

http://msdn.microsoft.com/ko-kr/library/ms190952(SQL.90).aspx

복원중  WITH CONTINUE_AFTER_ERROR 쓰게 되면 에러가 있어도 계속 복원한다.

http://www.microsoft.com/korea/eseminar/Seminar_player7.aspx?number=1316&asf=300K

온라인 등록하고 들으면 들을 수 있습니다.

출처 http://www.mssqltips.com/tip.asp?tip=960

비교적 쉬운 영어길래 그대로 가져옵니다.

Problem
SQL Server offers the ability to use startup parameters when your SQL Server instance starts. The default items include:

• location of  master database data file (-d)
• location of  master database log file (-l)
• location of  SQL server error logs (-e)

In addition, you can set startup parameters for trace flags, starting your instance in single user mode, etc...here is a link to startup options for SQL Server 2005 which is pretty similar to options for SQL Server 2000.

With SQL Server 2000 the startup parameters are available right from Enterprise Manager, but not so with SQL Server 2005.  So how do you set the startup parameters for SQL Server 2005?

Solution
With SQL Sever 2000 access to the startup parameters is available right within Enterprise Manager.  You can get to the startup parameters by right clicking on the server name and selecting "Properties".  The following screen appears and there is a great big button "Startup Parameters..." which you can click and it will allow to see and set the startup parameters.

After clicking on "Startup Parameters" you get the following window.  From here you can click on the parameter and click "Remove" to remove it or click on "Add" to add a new parameter. It is pretty straight forward.

With SQL Server 2005 a lot of things have been moved to other applications and are not all available through SQL Server Management Studio.  One of these things is the startup parameters.  To find the startup parameters you need to go to the SQL Server Configuration Manager tool.  This can be found in the menus through: Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager.

Once the application is launched you will see the following screen.

• select the SQL Server service that you want to set the startup parameters for such as "SQL Server (TEST1)" for our example
• right click and select "Properties" and the following window will pop up
• go to the "Advanced" tab

• select "Startup Properties" and the following window will pop up

• from here you can add, change or remove your startup properties
• note: each startup parameter is separated by a semi colon (;)
• once you are done click "Apply" to save your changes

Deadlock 발생시 원인과 해결법.

1. Deadlock 이유를 알고 싶으면 trace 1204 를 켜 준다.

2. Deadlock 발생되면 SQL의 에러로그에 로그가 남게 된다.

3.  에러로그를 확인해 보면 'Example_stored_proc'  프로시저에서 update 시 exclusive lock  이 발생하고 있다는 것을 확인 할 수 있다.

4. sp_helptext 'Example_stored_proc'  를 실행하여 프로시저의 내용을 확인 한다.

5. 이와 관련있는 프로시저를 확인하고 deadlock 이 발생하는 page를 확인할 수 있다.

Node:1 PAG: 9:1:18061 CleanCnt:2 Mode: SIU Flags: 0x2

6. 해당 페이지를 조사해 본다. , DBCC Page 사용

MSDN : http://support.microsoft.com/kb/83065

7.  object 확인

Use DBNAME
Select object_name(OBJECT_ID)

8. 프로시저에서 해당 object를 확인하고  update 문을 찾아 그대로 update 테스트 해 본다.

이때, 해당 컬럼에 인덱스가 있는지 확인 한다. update 시 인덱스가 없으면 table scan 을 하게되고 exclusive lock 이 발생한다.

9. Key 되는 컬럼에 인덱스를 생성 해 준다.

10. 후에 다시 실행해봐서 deadlock 이 발생하는지 확인 한다.

좋은 소식!!

이제 2005, 2008의 스탠다드 에디션에서도 Locak Page Memory 사용이 가능해 졌다.

CU2 for SQL Server 2008 SP1 => http://support.microsoft.com/kb/970315/en-us

http://support.microsoft.com/kb/970279/ko
CU4 for SQL Server 2005 SP3 => http://support.microsoft.com/kb/970279/en-us

http://support.microsoft.com/kb/970279/ko

http://blogs.msdn.com/sqlprogrammability/archive/2007/01/20/trouble-shooting-query-performance-issues-related-to-plan-cache-in-sql-2005-rtm-and-sp1.aspx

SQL  SP3  CU4 부터 2005 X64 스탠다드 에디션에서 Lock Page in Memory 기능을 사용할 수 있게 되었음.

증상

• 미러링 걸려있던 DB의 로그가 90% 이상.
• 활성 트랜잭션이 없음에도  log 백업을 해도 로그가 줄어들지 않음
• 주 서버에서 미러가 일시 중지됨

• 파트너 서버에 에러 로그 파일 에 아래 errorlog 발생

원인

• significant part of sql server process memory has been paged out. This may result in a performance degradation.
• Windows 운영체제 페이지를 SQL Server 프로세스의 작업 집한 제한  때문에 발생합니다.
• http://support.microsoft.com/kb/918483
• 데이터베이스 엔진 오류 : 1204
• SQL Server 데이터베이스 엔진 인스턴스에서 지금 LOCK 리소스를 가져올 수 없습니다. 활성 사용자가 적을 때 문을 다시 실행하십시오. 데이터베이스 관리자에게 이 인스턴스의 잠금 및 메모리 구성이나 장기 실행 트랜잭션을 확인하도록 요청하십시오.
• max server memory 옵션 증가시킴

• 위는 전부 페이지 잠금 문제로 발생 했다.

해결

1. 시작 을 클릭하고 실행 을 클릭합니다 gpedit.msc 를 입력한 다음 확인 을 클릭하십시오. 그룹 정책 대화 상자가 나타납니다.
2. 확장 컴퓨터 구성 를 확장한 다음 Windows 설정 .
3. 보안 설정 을 확장한 다음 로컬 정책 을 확장하십시오.
4. 사용자 권한 할당 을 클릭한 다음 메모리의 페이지 잠그기 두 번 클릭하십시오.
5. 로컬 보안 정책 설정 대화 상자에서 사용자 또는 그룹 추가 클릭합니다.
6. 사용자 또는 그룹 선택 대화 상자에서 Sqlservr.exe 파일을 실행할 권한이 있는 계정을 추가하고 확인 을 클릭하십시오.
7. 그룹 정책 대화 상자를 닫습니다.
8. SQL Server 서비스를 다시 시작하십시오