블로그 이미지
010-9967-0955 보미아빠

카테고리

보미아빠, 석이 (500)
밥벌이 (16)
싸이클 (1)
일상 (1)
Total
Today
Yesterday

달력

« » 2024.3
1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31

공지사항

최근에 올라온 글

i/o

카테고리 없음 / 2011. 7. 8. 02:49


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.

Posted by 보미아빠
, |
아래 그림의 붉은색 부분이 null 로 나올때



C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Profiler\Templates\Microsoft SQL Server
에 있는 버전별 템플릿을 카피해 넣는다.
안되는 서버에서 해보면 될듯 하여간 processMonitor 에서 보니 해당 폴더의 버전별로 모든 tdf 파일을 당기는 것을 볼 수 있다.

수동으로 만들려면, 있는 서버에서 저장하고
profiler > 파일 > 템플릿 > 템플릿 가져오기를 한다.

왜 지워졌을까?......-_- 궁금하네....
아마 2008 만 깔려있는 서버에서 2008 r2 의 템플릿을 부를려고 해서 에러난것 같다.

Posted by 보미아빠
, |


점검

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 '

 


 

 

Posted by 보미아빠
, |

최근에 달린 댓글

최근에 받은 트랙백

글 보관함