보미네

In this post from last year, I discussed how random I/Os are slower than sequential I/Os (particularly for conventional rotating hard drives).  For this reason, SQL Server often favors query plans that perform sequential scans of an entire table over plans that perform random lookups of only a portion of a table.  (See the last example in this post for a simple demonstration.)  In other cases, instead of performing a sequential scan, SQL Server introduces a sort operator whose sole purpose is to convert random I/Os into sequential I/Os.
Let's look at an example of such a sort.  To measure the performance effects, we'll need a reasonably large table.  The following script creates a 25.6 million row table that consumes about 3 GBytes of storage.

CREATE DATABASE IOTest
    ON ( NAME = IOTest_Data, FILENAME = '...\IOTest_Data.mdf', SIZE = 4 GB )
    LOG ON ( NAME = IOTest_Log, FILENAME = '...\IOTest_Log.ldf', SIZE = 200 MB )
GO
ALTER DATABASE IOTest SET RECOVERY SIMPLE
GO
USE IOTest
GO
CREATE TABLE T (
    PK INT IDENTITY PRIMARY KEY,
    RandKey INT,
    Flags TINYINT,
    Data INT,
    Pad CHAR(100))
GO
SET NOCOUNT ON
DECLARE @I INT
SET @I = 0
WHILE @I < 100000
  BEGIN
    WITH
      X2 (R) AS ( SELECT RAND() UNION ALL SELECT RAND() ),
      X4 (R) AS ( SELECT R FROM X2 UNION ALL SELECT R FROM X2 ),
      X8 (R) AS ( SELECT R FROM X4 UNION ALL SELECT R FROM X4 ),
      X16 (R) AS ( SELECT R FROM X8 UNION ALL SELECT R FROM X8 ),
      X32 (R) AS ( SELECT R FROM X16 UNION ALL SELECT R FROM X16 ),
      X64 (R) AS ( SELECT R FROM X32 UNION ALL SELECT R FROM X32 ),
      X128 (R) AS ( SELECT R FROM X64 UNION ALL SELECT R FROM X64 ),
      X256 (R) AS ( SELECT R FROM X128 UNION ALL SELECT R FROM X128 )
    INSERT T (RandKey, Flags, Data, Pad)
        SELECT R * 1000000000, 0xFF, 1, '' FROM X256
    SET @I = @I + 1
  END
GO
CREATE INDEX IRandKey on T (RandKey, Flags)
GO

Due to the fixed width Pad column, each row of T consumes 113 bytes (plus overhead).  Roughly 65 rows fit on a single 8 Kbyte page.  (The Flags column is unused in this example, but I will make use of it in a subsequent post.)

The RandKey column, as the name suggests, contains random values.  Notice that we have a non-clustered index on this column.  Given a predicate on the RandKey column, SQL Server can use this index to fetch qualifying rows from the table.  However, because the values in this column are random, the selected rows will be scattered randomly throughout the clustered index.

If we select just a few rows from the table using a filter on RandKey, SQL Server will use the non-clustered index:

SELECT SUM(Data)
FROM T
WHERE RandKey < 1000

  |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1011]=(0) THEN NULL ELSE [Expr1012] END))
       |--Stream Aggregate(DEFINE:([Expr1011]=COUNT_BIG([T].[Data]), [Expr1012]=SUM([T].[Data])))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([T].[PK], [Expr1010]) OPTIMIZED WITH UNORDERED PREFETCH)
                 |--Index Seek(OBJECT:([T].[IRandKey]), SEEK:([T].[RandKey] < (1000)) ORDERED FORWARD)
                 |--Clustered Index Seek(OBJECT:([T].[PK__T__...]), SEEK:([T].[PK]=[T].[PK]) LOOKUP ORDERED FORWARD)

The non-clustered index seek selects a few rows (the use of random keys means that the exact number may vary each time the table is loaded) and looks them up in the clustered index to get the value of the Data column for the SUM aggregate.  The non-clustered index seek is very efficient - it likely touches only one page - but the clustered index seek generates a random I/O for each row.

If we select a large number of rows, SQL Server recognizes that the random I/Os are too expensive and switches to a clustered index scan:

SELECT SUM(Data)
FROM T
WHERE RandKey < 10000000

  |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1009]=(0) THEN NULL ELSE [Expr1010] END))
       |--Stream Aggregate(DEFINE:([Expr1009]=COUNT_BIG([T].[Data]), [Expr1010]=SUM([T].[Data])))
            |--Clustered Index Scan(OBJECT:([T].[PK__T__...]), WHERE:([T].[RandKey]<(10000000)))

This query touches only 1% of the data.  Still, the query is going to touch more than half of the pages in the clustered index so it is faster to scan the entire clustered index than to perform on the order of 256,000 random I/Os.

Somewhere in between these two extremes things get a little more interesting:

SELECT SUM(Data)
FROM T
WHERE RandKey < 2500000

  |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1010]=(0) THEN NULL ELSE [Expr1011] END))
       |--Stream Aggregate(DEFINE:([Expr1010]=COUNT_BIG([T].[Data]), [Expr1011]=SUM([T].[Data])))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([T].[PK], [Expr1009]) WITH UNORDERED PREFETCH)
                 |--Sort(ORDER BY:([T].[PK] ASC))
                 |    |--Index Seek(OBJECT:([T].[IRandKey]), SEEK:([T].[RandKey] < (2500000)) ORDERED FORWARD)
                 |--Clustered Index Seek(OBJECT:([T].[PK__T__...]), SEEK:([T].[PK]=[T].[PK]) LOOKUP ORDERED FORWARD)

This query touches a mere 0.25% of the data.  The plan uses the non-clustered index to avoid unnecessarily touching many rows.  Yet, performing 64,000 random I/Os is still rather expensive so SQL Server adds a sort.  By sorting the rows on the clustered index key, SQL Server transforms the random I/Os into sequential I/Os.  Thus, we get the efficiency of the seek - touching only those rows that qualify - with the performance of the sequential scan.

It is worth pointing out that sorting on the clustered index key will yield rows that are in the logical index order.  Due to fragmentation or due simply to the multiple layers of abstraction between SQL Server and the actual hard drives, there is no guarantee that the physical order on disk matches the logical order.

In my next post, I'll run some of these queries and demonstrate the performance implications of the sort.

In my last post, I discussed how SQL Server can use sorts to transform random I/Os into sequential I/Os.  In this post, I'll demonstrate directly how such a sort can impact performance.  For the following experiments, I'll use the same 3 GByte database that I created last week.

The system I'm using to run this test has 8 GBytes of memory.  To exaggerate the performance effects and simulate an even larger table that does not fit in main memory, I'm going to adjust the ‘MAX SERVER MEMORY' SP_CONFIGURE option to allow SQL Server to use just 1 GByte of memory.  I'm going to use CHECKPOINT to ensure that the newly created database is completely flushed to disk before running any experiments.  Finally, I'm going to run DBCC DROPCLEANBUFFERS before each test to ensure that none of the data is cached in the buffer pool between tests.

CHECKPOINT

EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS', '1'
RECONFIGURE
EXEC SP_CONFIGURE 'MAX SERVER MEMORY', '1024'
RECONFIGURE

DBCC DROPCLEANBUFFERS

Note that you will NOT want to run these statements on a production server.

As I discussed last week, SQL Server can use one of three plans for the following query depending on the value of the constant:

SELECT SUM(Data)
FROM T
WHERE RandKey < constant

To recap, if the constant is small, SQL Server uses a non-clustered index seek and a bookmark lookup.  If the constant is large, SQL Server uses a clustered index scan to avoid performing many random I/Os.  Finally,  if the constant is somewhere in the middle, SQL Server uses the non-clustered index seek but sorts the rows prior to performing the bookmark lookup to reduce the number of random I/Os.  You can review last week's post to see examples of each of these plans.  I'm going to focus on the third and final plan with the sort.

To demonstrate the benefit of the sort, I need to be able to run the same query with and without the sort.  A simple way to make SQL Server remove the sort is to use the following UPDATE STATISTICS statement to trick SQL Server into believing that the table is really small.  To ensure that I still get the plan with the non-clustered index seek and the bookmark lookup, I need to add an INDEX hint.  I'm also adding a RECOMPILE query hint to ensure that SQL Server generates a new plan after I've altered the statistics.

UPDATE STATISTICS T WITH ROWCOUNT = 1, PAGECOUNT = 1

SELECT SUM(Data)
FROM T WITH (INDEX (IRandKey))
WHERE RandKey < constant
OPTION (RECOMPILE)

I can also reset the statistics using the following statement:

UPDATE STATISTICS T WITH ROWCOUNT = 25600000, PAGECOUNT = 389323

Here is an example of the default plan with the real statistics and with the sort:

  |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1010]=(0) THEN NULL ELSE [Expr1011] END))
       |--Stream Aggregate(DEFINE:([Expr1010]=COUNT_BIG([T].[Data]), [Expr1011]=SUM([T].[Data])))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([T].[PK], [Expr1009]) WITH UNORDERED PREFETCH)
                 |--Sort(ORDER BY:([T].[PK] ASC))
                 |    |--Index Seek(OBJECT:([T].[IRandKey]), SEEK:([T].[RandKey] < (2000000)) ORDERED FORWARD)
                 |--Clustered Index Seek(OBJECT:([T].[PK__T__...]), SEEK:([T].[PK]=[T].[PK]) LOOKUP ORDERED FORWARD)

Here is an example of the plan after running UPDATE STATISTICS and without the sort:

  |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1009]=(0) THEN NULL ELSE [Expr1010] END))
       |--Stream Aggregate(DEFINE:([Expr1009]=COUNT_BIG([T].[Data]), [Expr1010]=SUM([T].[Data])))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([T].[PK]))
                 |--Index Seek(OBJECT:([T].[IRandKey]), SEEK:([T].[RandKey] < (2000000)) ORDERED FORWARD)
                 |--Clustered Index Seek(OBJECT:([T].[PK__T__...]), SEEK:([T].[PK]=[T].[PK]) LOOKUP ORDERED FORWARD)

Here are my results running this query with two values of the constant both with and without the sort.  Keep in mind that these results depend greatly on the specific hardware.  If you try this experiment, your results may vary.

 
 Execution Time
 % Increase
 
with Sort
 without Sort
 
Constant
 2,000,000
(0.2% of rows)
 91 seconds
 352 seconds
 286%
 
4,000,000
(0.4% of rows)
 97 seconds
 654 seconds
 574%
 
% Increase
 100%
 6%
 86%
 
 

There are a two points worth noting regarding these results.  First, it should be very clear that the plan with the sort is significantly faster (up to 7 times faster) than the plan without the sort.  This result clearly shows the benefit of sequential vs. random I/Os.  Second, doubling the number of rows touched had hardly any effect on the execution time for the plan with the sort but nearly doubled the execution time for the plan without the sort.  Adding additional I/Os to the plan with the sort adds only a small incremental cost since the I/Os are sequential and the disk head will pass over the required data exactly once either way.  Adding additional I/Os to the plan without the sort adds additional disk seeks and increases the execution time proportionately to the increase in the number of rows.  In fact, if the constant is increased further, the execution time of the plan with the sort will continue to increase only gradually with the execution time of the plan without the sort will continue to increase rapidly.

In my past two posts, I explained how SQL Server may add a sort to the outer side of a nested loops join and showed how this sort can significantly improve performance.  In an earlier post, I discussed how SQL Server can use random prefetching to improve the performance of a nested loops join.  In this post, I'm going to explore one more nested loops join performance feature.  I'll use the same database that I used in my two prior posts.  Let's start with the following simple query:
SELECT SUM(Data)
FROM T
WHERE RandKey < 1000

  |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1011]=(0) THEN NULL ELSE [Expr1012] END))
       |--Stream Aggregate(DEFINE:([Expr1011]=COUNT_BIG([T].[Data]), [Expr1012]=SUM([T].[Data])))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([T].[PK], [Expr1010]) OPTIMIZED WITH UNORDERED PREFETCH)
                 |--Index Seek(OBJECT:([T].[IRandKey]), SEEK:([T].[RandKey] < (1000)) ORDERED FORWARD)
                 |--Clustered Index Seek(OBJECT:([T].[PK__T__...]), SEEK:([T].[PK]=[T].[PK]) LOOKUP ORDERED FORWARD)

Notice that the nested loops join includes an extra keyword: OPTIMIZED.  This keyword indicates that the nested loops join may try to reorder the input rows to improve I/O performance.  This behavior is similar to the explicit sorts that we saw in my two previous posts, but unlike a full sort it is more of a best effort.  That is, the results from an optimized nested loops join may not be (and in fact are highly unlikely to be) fully sorted.

SQL Server only uses an optimized nested loops join when the optimizer concludes based on its cardinality and cost estimates that a sort is most likely not required, but where there is still a possibility   that a sort could be helpful in the event that the cardinality or cost estimates are incorrect.  In other words, an optimized nested loops join may be thought of as a "safety net" for those cases where SQL Server chooses a nested loops join but would have done better to have chosen an alternative plan such as a full scan or a nested loops join with an explicit sort.  For the above query which only joins a few rows, the optimization is unlikely to have any impact at all.

Let's look at an example where the optimization actually helps:

SELECT SUM(Data)
FROM T
WHERE RandKey < 100000000 AND
    Flags & 0x1 = 0x1 AND
    Flags & 0x2 = 0x2 AND
    Flags & 0x4 = 0x4 AND
    Flags & 0x8 = 0x8

  |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1014]=(0) THEN NULL ELSE [Expr1015] END))
       |--Stream Aggregate(DEFINE:([Expr1014]=COUNT_BIG([T].[Data]), [Expr1015]=SUM([T].[Data])))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([T].[PK], [Expr1013]) OPTIMIZED WITH UNORDERED PREFETCH)
                 |--Index Seek(OBJECT:([T].[IRandKey]), SEEK:([T].[RandKey] < (100000000)),  WHERE:(([T].[Flags]&(1))=(1) AND ([T].[Flags]&(2))=(2) AND ([T].[Flags]&(4))=(4) AND ([T].[Flags]&(8))=(8)) ORDERED FORWARD)
                 |--Clustered Index Seek(OBJECT:([T].[PK__T__...]), SEEK:([T].[PK]=[T].[PK]) LOOKUP ORDERED FORWARD)

The Flags column contains the value 0xFF in every row.  Thus, every one of the bitwise AND predicates evaluates to true and this query returns about 2.5 million rows or 10% of the table.  Ordinarily, when faced with a query like this one, SQL Server would resort to a sequential scan of the entire table.  Indeed, if you try this query without the extra bitwise filters, you will get a sequential scan.  However, SQL Server does not realize that these predicates are always true, estimates a much lower cardinality of less than 10,000 rows, and chooses a simple nested loops join plan.  Note that I would generally recommend against using predicates like these ones in a real world application precisely because they will lead to cardinality estimation errors and poor plans.

To see what effect the optimized nested loops join has, let's compare the above plan with an "un-optimized" nested loops join.  We can eliminate the optimization by using the following UPDATE STATISTICS statement to trick SQL Server into believing that the table is very small:

UPDATE STATISTICS T WITH ROWCOUNT = 1, PAGECOUNT = 1

I'll compare the above query with the following simpler query which uses essentially the same plan and touches the same data but has an "un-optimized" nested loops join:

SELECT SUM(Data)
FROM T WITH (INDEX (IRandKey))
WHERE RandKey < 100000000

  |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1009]=(0) THEN NULL ELSE [Expr1010] END))
       |--Stream Aggregate(DEFINE:([Expr1009]=COUNT_BIG([T].[Data]), [Expr1010]=SUM([T].[Data])))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([T].[PK]))
                 |--Index Seek(OBJECT:([T].[IRandKey]), SEEK:([T].[RandKey] < (100000000)) ORDERED FORWARD)
                 |--Clustered Index Seek(OBJECT:([T].[PK__T__...]), SEEK:([T].[PK]=[T].[PK]) LOOKUP ORDERED FORWARD)

We can reset the statistics using the following statement:

UPDATE STATISTICS T WITH ROWCOUNT = 25600000, PAGECOUNT = 389323

As in my last post, I'm going to simulate a larger table by reducing the memory available to the server to 1 GByte with SP_CONFIGURE 'MAX SERVER MEMORY' and I'm also going to flush the buffer pool between runs with DBCC DROPCLEANBUFFERS.

Note that you will NOT want to run these statements on a production server.

I ran both of the above queries with three different constants.  Here are my results.  Keep in mind that these results depend greatly on the specific hardware.  If you try this experiment, your results may vary.

 
 Execution Time
 Increase
 
OPTIMIZED
 "un-OPTIMIZED"
 
Constant
 10,000,000
(1% of rows)
 6.5 minutes
 26 minutes
 4x
 
100,000,000
(10% of rows)
 10.4 minutes
 4.3 hours
 25x
 
250,000,000
(25% of rows)
 11.3 minutes
 10.6 hours
 56x
 

Clearly the optimized nested loops join can have a huge impact on performance.  Moreover, as the plan touches more rows the benefit of the optimization grows dramatically.  Although a full scan or a nested loops join with an explicit sort would be faster, the optimized nested loops join really is a safety net protecting against a much worse alternative.

점검

Version 1.0.20110707

## OS 점검
@. 전원계획 선택 : 전원계획을 고성능으로 고정 하시길 바랍니다. (2008)
    Windows 2008 의 경우 제어판>전원>전원계획 선택> 고성능 으로 변경 하시면 됩니다.
    powercfg.cpl

@. NIC 전원 옵션 : 전원을 절약하기 위해 컴퓨터가 이 장치를 끌 수 있음 uncheck(2008)

@. NIC 옵션 정리 (보안팀 + 시스템 협의 후 반드시 설정 하도록 합니다.)
    netsh interface tcp set global chimney=disabled
    netsh interface tcp set global rss=disabled
    netsh interface tcp set global autotuninglevel=disabled
    netsh interface tcp set global netdma=disabled

@. HyperThread  1

@. 자동 업데이트 사용안함

@. 프로세서 사용계획 백그라운드 (0)
시스템 속성 > 성능 > 설정 > 성능 옵션 > 고급 > 프로세서 사용 계획
여기서 프로그램을 선택하시면 절대 아니되옵니다.
조금이라도 이해하고 있는 사람이라면 바보짓이라는 것을 쉽게 알 수 있습니다.
다음 아티클을 참고해 보세요
http://blogs.technet.com/b/sankim/archive/2009/06/10/windows-vs.aspx

@. 메모리 사용게획 프로그램
   http://blogs.technet.com/b/sankim/archive/2008/05/19/largesystemcache.aspx
    SQL Server  는 SingleBuffered Cache System 이다. 그러니 반드시 0으로 설정해주는 것이 좋다.
    Large system cache : 0

@. 네트워크 응용 프로그램을 위한 최대화

@. SynAttackProtect  설정
 http://support.microsoft.com/kb/899599
시작 을 누르고 실행 을 regedit 를 입력한 다음 확인 을 누릅니다.
찾은 후 다음 레지스트리 키를 누릅니다.
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
편집 메뉴에서 새로 만들기를 가리킨 다음 DWORD 값 을 클릭하십시오.
SynAttackProtect 를 입력한 다음 Enter 키를 누릅니다.
편집 메뉴에서 수정 을 클릭하십시오.
값 데이터 상자에 00000000을 입력하십시오. 확인 을 클릭하십시오.
레지스트리 편집기를 종료하십시오.

@. 바이러스 제외폴더 설정

@. OS ServicePack  확인
   winver

@.  secpol
   볼륨 관리 작업을 수행,  AWE 설정,  등등

@. 방화벽 wf.msc
   포트 오픈
   DAC 등

## SQL 점검

@. SQL Service Pack
http://support.microsoft.com/kb/2534352 r2 cu8
http://blogs.msdn.com/b/sqlreleaseservices/
http://www.mskbarticles.com/index.php?last-updated-products

@. traceflag 1118, 1204, 1222, 845 설정
sqlservermanager10.msc > sql server 서비스 > 속성 > 고급 > 시작 매개 변수 끝에 삽입
;-T1118;-T845;-T1112;-T1204
1118 의 경우 의미를 알고 넣는것이 좋습니다.
845는 64bit SE 만 설정 합니다.
1112, 1204 는 deadlock
확인
dbcc tracestatus(-1)

@. SQL Alias 설정 (0)
   32bit 64bit alias 는 같게 설정한다.
   sqlservermanager10.msc

@. 자동통계 update , 비동기 통계 업데이트
   http://msdn.microsoft.com/en-us/library/dd535534(v=sql.100).aspx

   SELECT NAME AS "NAME",
   IS_AUTO_CREATE_STATS_ON AS "AUTO CREATE STATS",
   IS_AUTO_UPDATE_STATS_ON AS "AUTO UPDATE STATS",
   IS_READ_ONLY AS "READ ONLY",
   CASE IS_AUTO_CREATE_STATS_ON WHEN 0 THEN 'ALTER DATABASE ['+NAME+'] SET  AUTO_CREATE_STATISTICS ON' ELSE '' END CMD1,
   CASE IS_AUTO_UPDATE_STATS_ON WHEN 0 THEN 'ALTER DATABASE ['+NAME+'] SET  AUTO_UPDATE_STATISTICS ON' ELSE '' END CMD2
   FROM SYS.DATABASES
   WHERE DATABASE_ID > 4;

   SELECT NAME AS "NAME",
   IS_AUTO_UPDATE_STATS_ASYNC_ON AS "ASYNCHRONOUS UPDATE" ,
   CASE IS_AUTO_UPDATE_STATS_ASYNC_ON WHEN 0 THEN 'ALTER DATABASE ['+NAME+'] SET  AUTO_UPDATE_STATISTICS_ASYNC ON' ELSE '' END CMD
   FROM SYS.DATABASES
   WHERE DATABASE_ID > 4;

@. db 파일 자동증가 옵션 전체 모두 50MB ~ 500MB  설정
    충분히 확장해 둘것
    datafile 40 GB 로 확장
    ldf file 5GB

@. tempdb size 확장 -> MDF 3GB,  LDF 3GB
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 3072000KB , FILEGROWTH = 51200KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 3072000KB , FILEGROWTH = 51200KB )
GO

@. backup 설정
    새벽2시 fullbackup
    30분마다 logbackup
    masterkey backup

@. identity 최대값 확인

@. Service 확인 fulltext off , integration off, sql browser on

@. SQL 오류로그개수 30개

@. VLF 확인

@. msdb 백업관련 프로시저 수정 (암호 설정,  index 필요하면)

@. remote admin connections 활성화
exec sp_configure 'show advanced options', 1
reconfigure with override
go
exec sp_configure 'remote admin connections', 1
reconfigure with override
go
exec sp_configure 'remote admin connections'
go

@. sp_configure
  exec sp_configure 'optimize for ad hoc workloads', 1
  exec sp_configure 'max server memory (MB)', 700 -- 적절히 셋팅하세요~
  exec sp_configure 'min server memory (MB)', 700 --

@. 원본서버의  sp_configure 확인
declare @tbl_configuration_value table
(configuration_id int
,value_in_use bigint
)

insert into @tbl_configuration_value (configuration_id, value_in_use) values (101,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (102,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (103,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (106,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (107,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (109,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (114,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (115,1)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (116,1)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (117,1)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (124,29)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (400,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (503,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (505,4096)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (518,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (542,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (544,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1126,1042)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1127,2049)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1505,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1517,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1519,20)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1520,600)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1531,-1)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1532,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1534,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1535,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1536,65536)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1537,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1538,5)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1539,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1540,1024)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1541,-1)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1543,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1544,2147483647)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1545,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1546,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1547,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1548,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1550,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1555,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1556,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1557,60)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1562,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1563,4)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1564,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1565,100)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1566,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1567,100)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1568,1)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1569,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1570,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1576,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1577,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1578,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1579,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1580,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1581,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1582,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1583,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (16384,1)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (16385,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (16386,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (16387,1)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (16388,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (16390,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (16391,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (16392,0)

select 'exec sp_configure ''show advanced options'', 1' diff_config
union all select 'exec sp_configure ''allow updates'', 1'
union all select 'reconfigure with override '
union all
select 'exec sp_configure '''+name+''',' + ' '+cast(a.value_in_use as varchar(100))diff_config-- , a.*, b.value_in_use
from master.sys.configurations a
join @tbl_configuration_value b
on b.configuration_id = a.configuration_id
where b.value_in_use <> cast(a.value_in_use as bigint)
and a.configuration_id not in (102,518)
--select * from sys.configurations where configuration_id = 1547
union all select 'reconfigure with override '

아래와 같은 6번 쿼리를 던지면 컴파일이 일어나거나 리컴파일이 일어난 2번만 플랜이 찍히게 된다.
너무 많은 플랜이 찍혀 힘들다면 사용해 볼 수 있다. 그러나 실제 실행계획이 아니라 이 또한 예상 실행 계획 이다.

산술 오류의 변경 SQL SERVER 2000 VS. 2005

아래 쿼리는 SQL SERVER 2000 에서는 에러가 없다.
그런데 SQL SERVER 2005 이상에서는 에러가 난다. 
이 얼마나 황당한가? 왜 그렇게 변경 되었을지 의견을 달아 봐라.

CREATE TABLE T1 (A INT, B CHAR(8))
INSERT T1 VALUES (0, '0')
INSERT T1 VALUES (1, '1')
INSERT T1 VALUES (99, 'ERROR')

CREATE TABLE T2 (X INT)
INSERT T2 VALUES (1)
INSERT T2 VALUES (2)
INSERT T2 VALUES (3)
INSERT T2 VALUES (4)
INSERT T2 VALUES (5)

set showplan_text on

SELECT T1.A, CONVERT(INT, T1.B) AS B_INT
FROM T2 JOIN T1  ON T1.A = T2.X
GO

StmtText
----------------------------------------------------------------------------
SELECT T1.A, CONVERT(INT, T1.B) AS B_INT
FROM T2 JOIN T1  ON T1.A = T2.X

(1개 행이 영향을 받음)

StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------
  |--Hash Match(Inner Join, HASH:([IOTest].[dbo].[T1].[A])=([IOTest].[dbo].[T2].[X]), RESIDUAL:([IOTest].[dbo].[T2].[X]=[IOTest].[dbo].[T1].[A]))
       |--Compute Scalar(DEFINE:([Expr1008]=CONVERT(int,[IOTest].[dbo].[T1].[B],0)))
       |    |--Table Scan(OBJECT:([IOTest].[dbo].[T1]))
       |--Table Scan(OBJECT:([IOTest].[dbo].[T2]))

(4개 행이 영향을 받음)

--메시지 245, 수준 16, 상태 1, 줄 1
--VARCHAR 값 'ERROR   '을(를) 데이터 형식 INT(으)로 변환하지 못했습니다.

SELECT T1.A, CONVERT(INT, T1.B) AS B_INT
  FROM T2
  JOIN T1 
    ON T1.A = T2.X
OPTION (FORCE ORDER)
GO

-- 정상수행

backup log dbname to disk ='c:\xxx' with norecovery, init, copy_only 옵션을 주면 데이터베스가 복원중 으로 변한다.  

copy 이후 실제 서버의 transaction log 를 apply 시켜 restore 를 했다.

emergency 모드에서 데이터를 확인해 봤다. 여기서 확인되는 데이터만 살릴수 있고,
delete update 는 잘 적용 되어 있고, insert 는 적용되지 않은것을 알 수 있다.

리커버리 후 상태이다.

결국 PFS, GAM만 복구하면 입력한 모든 데이터를 살릴수 있을듯도 하다.
본 테스트에서는 PFS 만 깨어졌지만 insert 용량이 크면 GAM 페이지도 깨어졌다.
그러므로 파일 copy 를 이용한 recovery 모드 변경후 transaction apply 시나리오는 불완전 복구만 가능하다.

이번 테스트를 통해서 비상로그 백업의 용도를 정확히 인지 할 수 있다.

오라클 데이터베이스의 경우 Hot Backup 모드를 지원한다. begin backup 모드로 들어가고 파일 복사 +  log apply 를 통해 데이터베이스를 완전한 상태로 복구 할 수 있다. 정말 부럽지 않은가?

SQL Server 아직 멀었다. 이게 쓸만하게 되려면 한 10년쯤 지나야 하지 않을까 싶다. 그때도 난 SQL 할꺼다...^^; 취미니깐



스크립트는 아래를 참조 한다.


use master
go

alter database CopyOnlyRecovery set single_user with rollback immediate
go

drop database CopyOnlyRecovery
go

use master
go

create database CopyOnlyRecovery
go

use CopyOnlyRecovery
go

create table tblx
(idx int identity(1,1)
,c1 char(5000)
)
go

create clustered index cl_tblx on tblx (idx)
go

insert into tblx values (1)
go 100

select top 1 * from tblx
go

backup database CopyOnlyRecovery to disk ='c:\f1.bak' with init
go

insert into tblx values (2)
go 100

backup log CopyOnlyRecovery to disk ='c:\l1.bak' with init
go

select top 1 * from tblx order by c1 desc
go

insert into tblx values (3)
go 100

select top 1 * from tblx order by c1 desc

checkpoint
go

select * from tblx

-- sqlserver stop
-- 위와 같은 상황에서 database shutdown 후 CopyOnlyRecovery 의 mdf 와 ldf 를 다른곳으로 copy!
-- sqlserver start

use CopyOnlyRecovery
go

insert into tblx values (4)
go 100

update tblx set c1 = 111 where idx = 1
go

select * from tblx
go

backup log CopyOnlyRecovery to disk ='c:\l2.bak' with init
go

insert into tblx values (5)
go 100

backup log CopyOnlyRecovery to disk ='c:\l3.bak' with init
go

update tblx set c1 = 222 where idx = 2
go

delete from tblx where idx = 3
go

select * from tblx where idx < 10

backup log CopyOnlyRecovery to disk ='c:\l4.bak' with init
go

checkpoint

-- 이제부터 예전 copy 한 파일로 부터 복원을 해보자.

--use master
--go
-- ***************************************************
-- 예전에 복사해둔 데이터베이스를 attach 한다. 
-- stop
-- copy
-- start

use master
go

-- 아래 명령을 날리면 3까지 복원된 상태에서 norecovery 모드로 변경된다. 마치 full backup 에서 복원한것 처럼
-- 사실 아래의 명령어는 mission critical server 에서 log backup 후 더이상의 transaction 이 일어나지 못하게
-- 막는 옵션이다.

alter database CopyOnlyRecovery set single_user with rollback immediate
go

backup log CopyOnlyRecovery to disk = 'c:\fail2.bak' with norecovery , init , COPY_ONLY
go

-- 데이터베이스는 복구중 모드로 변경된다. 여기에서 이후 로그를 어플라이 시킨다.

restore log CopyOnlyRecovery from disk = 'c:\fail2.bak' with norecovery
restore log CopyOnlyRecovery from disk = 'c:\l2.bak' with norecovery , continue_after_error
restore log CopyOnlyRecovery from disk = 'c:\l3.bak' with norecovery , continue_after_error
restore log CopyOnlyRecovery from disk = 'c:\l4.bak' with norecovery , continue_after_error
restore database CopyOnlyRecovery with recovery , continue_after_error

use master
go

alter database CopyOnlyRecovery set emergency
go

use CopyOnlyRecovery
go

select count(*) from tblx
go
-- 299

select top 10 * from tblx where idx < 10 order by idx
go

ALTER DATABASE CopyOnlyRecovery SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go

dbcc checkdb ('CopyOnlyRecovery', repair_allow_data_loss)
go

현재 데이터베이스를 다시 시작하지 못했습니다. 현재 데이터베이스가 master로 전환됩니다.
경고: 데이터베이스 'CopyOnlyRecovery'의 로그가 다시 작성되었습니다. 트랜잭션에 일관성이 없습니다. RESTORE 체인이 끊어져 서버에 이전 로그 파일에 대한 컨텍스트가 더 이상 없으므로 해당 로그 파일이 어떤 파일인지 알아야 합니다. DBCC CHECKDB를 실행하여 물리적 일관성을 확인해야 합니다. 데이터베이스가 dbo 전용 모드로 전환되었습니다. 데이터베이스를 사용할 수 있는 준비가 되면 데이터베이스 옵션을 다시 설정하고 모든 추가 로그 파일을 삭제하십시오.
메시지 2510, 수준 16, 상태 17, 줄 1
DBCC checkdb 오류: This system table index cannot be recreated.
'CopyOnlyRecovery'의 DBCC 결과입니다.
Service Broker 메시지 9675, 상태 1: 분석된 메시지 유형: 14.
Service Broker 메시지 9676, 상태 1: 분석된 서비스 계약: 6.
Service Broker 메시지 9667, 상태 1: 분석된 서비스: 3.
Service Broker 메시지 9668, 상태 1: 분석된 서비스 큐: 3.
Service Broker 메시지 9669, 상태 1: 분석된 대화 끝점: 0.
Service Broker 메시지 9674, 상태 1: 분석된 대화 그룹: 0.
Service Broker 메시지 9670, 상태 1: 분석된 원격 서비스 바인딩: 0.
Service Broker 메시지 9605, 상태 1: 대화 우선 순위 분석: 0.
'sys.sysrscols'의 DBCC 결과입니다.
7개 페이지에 개체 "sys.sysrscols"에 대한 행이 634개 있습니다.
'sys.sysrowsets'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysrowsets"에 대한 행이 92개 있습니다.
'sys.sysallocunits'의 DBCC 결과입니다.
2개 페이지에 개체 "sys.sysallocunits"에 대한 행이 104개 있습니다.
'sys.sysfiles1'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysfiles1"에 대한 행이 2개 있습니다.
'sys.syspriorities'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.syspriorities"에 대한 행이 0개 있습니다.
'sys.sysfgfrag'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysfgfrag"에 대한 행이 2개 있습니다.
'sys.sysphfg'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysphfg"에 대한 행이 1개 있습니다.
'sys.sysprufiles'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysprufiles"에 대한 행이 2개 있습니다.
'sys.sysftinds'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysftinds"에 대한 행이 0개 있습니다.
'sys.sysowners'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysowners"에 대한 행이 14개 있습니다.
'sys.sysprivs'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysprivs"에 대한 행이 130개 있습니다.
'sys.sysschobjs'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysschobjs"에 대한 행이 54개 있습니다.
'sys.syscolpars'의 DBCC 결과입니다.
8개 페이지에 개체 "sys.syscolpars"에 대한 행이 485개 있습니다.
'sys.sysnsobjs'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysnsobjs"에 대한 행이 1개 있습니다.
'sys.syscerts'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.syscerts"에 대한 행이 0개 있습니다.
'sys.sysxprops'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysxprops"에 대한 행이 0개 있습니다.
'sys.sysscalartypes'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysscalartypes"에 대한 행이 34개 있습니다.
'sys.systypedsubobjs'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.systypedsubobjs"에 대한 행이 0개 있습니다.
'sys.sysidxstats'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysidxstats"에 대한 행이 111개 있습니다.
'sys.sysiscols'의 DBCC 결과입니다.
메시지 8948, 수준 16, 상태 1, 줄 1
데이터베이스 오류: 페이지 (1:93)이(가) PFS 페이지 (1:1)에서 잘못된 유형으로 표시되어 있습니다. PFS 상태 0x0에는 0x60이(가) 필요합니다.
        오류가 복구되었습니다.
메시지 8948, 수준 16, 상태 1, 줄 1
데이터베이스 오류: 페이지 (1:94)이(가) PFS 페이지 (1:1)에서 잘못된 유형으로 표시되어 있습니다. PFS 상태 0x0에는 0x60이(가) 필요합니다.
        오류가 복구되었습니다.
2개 페이지에 개체 "sys.sysiscols"에 대한 행이 262개 있습니다.
CHECKDB이(가) 테이블 'sys.sysiscols'(개체 ID 55)에서 2개의 할당 오류와 0개의 일관성 오류를 찾았습니다.
CHECKDB이(가) 테이블 'sys.sysiscols'(개체 ID 55)에서 2개의 할당 오류와 0개의 일관성 오류를 수정했습니다.
'sys.sysbinobjs'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysbinobjs"에 대한 행이 23개 있습니다.
'sys.sysaudacts'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysaudacts"에 대한 행이 0개 있습니다.
'sys.sysobjvalues'의 DBCC 결과입니다.
복구: 데이터베이스 "CopyOnlyRecovery"에서 개체 "sys.sysobjvalues"의 Clustered 인덱스가 다시 작성되었습니다.
메시지 8945, 수준 16, 상태 1, 줄 1
테이블 오류: 개체 ID 60, 인덱스 ID 1을(를) 다시 작성합니다.
        오류가 복구되었습니다.
메시지 8976, 수준 16, 상태 1, 줄 1
테이블 오류: 개체 ID 60, 인덱스 ID 1, 파티션 ID 281474980642816, 할당 단위 ID 281474980642816(In-row data 유형). 부모 (1:142)과(와) 이전의 (1:152)이(가) 페이지 (1:568)을(를) 참조하지만 이 페이지가 검색에 없습니다. 이전 오류를 확인하십시오.
        오류가 복구되었습니다.
메시지 8980, 수준 16, 상태 1, 줄 1
테이블 오류: 개체 ID 60, 인덱스 ID 1, 파티션 ID 281474980642816, 할당 단위 ID 281474980642816(In-row data 유형). 인덱스 노드 페이지 (1:142), 슬롯 16에서 자식 페이지 (1:569)과(와) 이전 자식 페이지 (1:568)을(를) 참조하지만 해당 페이지가 없습니다.
        오류가 복구되었습니다.
메시지 8980, 수준 16, 상태 1, 줄 1
테이블 오류: 개체 ID 60, 인덱스 ID 1, 파티션 ID 281474980642816, 할당 단위 ID 281474980642816(In-row data 유형). 인덱스 노드 페이지 (1:142), 슬롯 17에서 자식 페이지 (1:570)과(와) 이전 자식 페이지 (1:569)을(를) 참조하지만 해당 페이지가 없습니다.
        오류가 복구되었습니다.
15개 페이지에 개체 "sys.sysobjvalues"에 대한 행이 89개 있습니다.
CHECKDB이(가) 테이블 'sys.sysobjvalues'(개체 ID 60)에서 0개의 할당 오류와 3개의 일관성 오류를 찾았습니다.
CHECKDB이(가) 테이블 'sys.sysobjvalues'(개체 ID 60)에서 0개의 할당 오류와 3개의 일관성 오류를 수정했습니다.
'sys.sysclsobjs'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysclsobjs"에 대한 행이 16개 있습니다.
'sys.sysrowsetrefs'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysrowsetrefs"에 대한 행이 0개 있습니다.
'sys.sysremsvcbinds'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysremsvcbinds"에 대한 행이 0개 있습니다.
'sys.sysxmitqueue'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysxmitqueue"에 대한 행이 0개 있습니다.
'sys.sysrts'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysrts"에 대한 행이 1개 있습니다.
'sys.sysconvgroup'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysconvgroup"에 대한 행이 0개 있습니다.
'sys.sysdesend'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysdesend"에 대한 행이 0개 있습니다.
'sys.sysdercv'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysdercv"에 대한 행이 0개 있습니다.
'sys.syssingleobjrefs'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.syssingleobjrefs"에 대한 행이 146개 있습니다.
'sys.sysmultiobjrefs'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysmultiobjrefs"에 대한 행이 106개 있습니다.
'sys.sysguidrefs'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysguidrefs"에 대한 행이 0개 있습니다.
'sys.syscompfragments'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.syscompfragments"에 대한 행이 0개 있습니다.
'sys.sysftstops'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysftstops"에 대한 행이 0개 있습니다.
'sys.sysqnames'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysqnames"에 대한 행이 97개 있습니다.
'sys.sysxmlcomponent'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysxmlcomponent"에 대한 행이 99개 있습니다.
'sys.sysxmlfacet'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysxmlfacet"에 대한 행이 112개 있습니다.
'sys.sysxmlplacement'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysxmlplacement"에 대한 행이 18개 있습니다.
'sys.sysobjkeycrypts'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysobjkeycrypts"에 대한 행이 0개 있습니다.
'sys.sysasymkeys'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysasymkeys"에 대한 행이 0개 있습니다.
'sys.syssqlguides'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.syssqlguides"에 대한 행이 0개 있습니다.
'sys.sysbinsubobjs'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysbinsubobjs"에 대한 행이 3개 있습니다.
'sys.syssoftobjrefs'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.syssoftobjrefs"에 대한 행이 0개 있습니다.
'sys.queue_messages_1977058079'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.queue_messages_1977058079"에 대한 행이 0개 있습니다.
'sys.queue_messages_2009058193'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.queue_messages_2009058193"에 대한 행이 0개 있습니다.
'sys.queue_messages_2041058307'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.queue_messages_2041058307"에 대한 행이 0개 있습니다.
'sys.filestream_tombstone_2073058421'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.filestream_tombstone_2073058421"에 대한 행이 0개 있습니다.
'sys.syscommittab'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.syscommittab"에 대한 행이 0개 있습니다.
'tblx'의 DBCC 결과입니다.
메시지 8914, 수준 16, 상태 1, 줄 1
개체 ID 2105058535, 인덱스 ID 0, 파티션 ID 72057594038779904, 할당 단위 ID 72057594039828480(In-row data 유형)의 페이지 (1:77)에 대한 사용 가능한 PFS 공간 정보가 잘못되었습니다. 값   0_PCT_FULL이(가) 필요한데 실제 값은  80_PCT_FULL입니다.
        오류가 복구되었습니다.
300개 페이지에 개체 "tblx"에 대한 행이 299개 있습니다.
CHECKDB이(가) 테이블 'tblx'(개체 ID 2105058535)에서 0개의 할당 오류와 1개의 일관성 오류를 찾았습니다.
CHECKDB이(가) 테이블 'tblx'(개체 ID 2105058535)에서 0개의 할당 오류와 1개의 일관성 오류를 수정했습니다.
CHECKDB이(가) 데이터베이스 'CopyOnlyRecovery'에서 2개의 할당 오류와 4개의 일관성 오류를 찾았습니다.
CHECKDB이(가) 데이터베이스 'CopyOnlyRecovery'에서 2개의 할당 오류와 4개의 일관성 오류를 수정했습니다.
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

use CopyOnlyRecovery
go

alter database CopyOnlyRecovery set multi_user
go

select top 100 * from tblx order by idx 

select count(*) from tblx

select * from tblx

 
-- 기타
DBCC SHOWFILESTATS
DBCC EXTENTINFO(CopyOnlyRecovery, TBLX, -1)
DBCC TRACEON(3604)
go
DBCC PAGE('CopyOnlyRecovery',1, 1,2)
go
EXEC SP_RESETSTATUS 'CopyOnlyRecovery';
go
dbcc checkdb (CopyOnlyRecovery)
go
use master
go

restore headeronly from disk = 'e:\backupProject20121108.bak'
restore filelistonly from disk = 'e:\backupProject20121108.bak'

restore database backupProject from disk = 'e:\backupProject20121108.bak'
with recovery
, move 'backupProject' to 'l:\mssql\backupProject.mdf'
, move 'backupProject_log' to 'l:\mssql\backupProject_log.ldf'

Next up in the Inside the Storage Engine series is a discussion of page structure. Pages exist to store records. A database page is an 8192-byte (8KB) chunk of a database data file. They are aligned on 8KB boundaries within the data files, starting at byte-offset 0 in the file. Here's a picture of the basic structure:

Header

The page header is 96 bytes long. What I'd like to do in this section is take an example page header dump from DBCC PAGE and explain what all the fields mean. I'm using the database from the page split post and I've snipped off the rest of the DBCC PAGE output.

DBCC

TRACEON (3604)

DBCC

PAGE ('pagesplittest', 1, 143, 1);

GO

m_pageId = (1:143) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 68 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594042384384
Metadata: PartitionId = 72057594038386688 Metadata: IndexId = 1
Metadata: ObjectId = 2073058421 m_prevPage = (0:0) m_nextPage = (1:154)
pminlen = 8 m_slotCnt = 4 m_freeCnt = 4420
m_freeData = 4681 m_reservedCnt = 0 m_lsn = (18:116:25)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 1333613242

Note that I didn't include the fields starting with Metadata:. That's because they're not part of a page header. During SQL Server 2005 development I did some major work rewriting the guts of DBCC PAGE and to save everyone using it from having to do all the system table lookups to determine what the actual object and index IDs are, I changed DBCC PAGE to do them internally and output the results.

Records

See this blog post for details.

Slot Array

It's a very common misconception that records within a page are always stored in logical order. This is not true. There is another misconception that all the free-space in a page is always maintained in one contiguous chunk. This also is not true. (Yes, the image above shows the free space in one chunk and that very often is the case for pages that are being filled gradually.)

If a record is deleted from a page, everything remaining on the page is not suddenly compacted - inserters pay the cost of compaction when its necessary, not deleters.

Consider a completely full page - this means that record deletions cause free space holes within the page. If a new record needs to be inserted onto the page, and one of the holes is big enough to squeeze the record into, why go to the bother of comapcting it? Just stick the record in and carry on. What if the record should logically have come at the end of all other records on the page, but we've just inserted it in the middle - doesn't that screw things up somewhat?

No, because the slot array is ordered and gets reshuffled as records are inserted and deleted from pages. As long as the first slot array entry points to the logically first record on the page, everything's fine. Each slot entry is just a two-byte pointer into the page - so its far more efficient to manipulate the slot array than it is to manipulate a bunch of records on the page. Only when we know there's enough free space contained within the page to fit in a record, but its spread about the page do we compact the records on the page to make the free space into a contiguous chunk.

One interesting fact is that the slot array grows backwards from the end of the page, so the free space is squeezed from the top by new rows, and from the bottom by the slot array.