STATISTICS_NORECOMPUTE – When would anyone want to use it?

.NET/MS-SQL
posted by 네코냥이 2014. 5. 29. 10:40

STATISTICS_NORECOMPUTE – When would anyone want to use it?

Oct 7, 2011by  in SQL Server Questions Answered
RSS

Question: Recently I noticed an option with CREATE/ALTER INDEX called STATISTICS_NORECOMPUTE? I'm not sure I understand this option or why you'd ever want to use it? Can you explain?

Answer: In general, I don't recommend this option. But, before I get to why - let me clarify what the option does. I've heard a few folks say that it stops SQL Server from updating the statistics at the time of a rebuild; that is NOT what it does. Statistics are ALWAYS updated at the time of a REBUILD; this cannot be turned off (nor would you want to).

Instead, STATISTICS_NORECOMPUTE stops the database-wide auto-updating (auto update statistics) from updating the specific statistics for an index (or column-level statistic) that was created (or rebuilt) with this option turned on. NOTE: STATISTICS_RECOMPUTE only works when creating or rebuilding an index. If you want to stop the auto-updating of a column-level statistic you use NORECOMPUTE when creating that particular statistic.

The more difficult question is if/when this would make sense to turn off – for either an index or a column-level statistic. And, yes, there are some cases where this can be appropriate.

Related: Making the Most of Automatic Statistics Updating

The case where it makes sense to be turned off is for tables that are large in size and have very skewed data distribution. For example, the ratio of line items per sale might be very evenly distributed between 1 and 3 items with an average of 2.1. Evenly distributed data tends to be easier to represent in a histogram (one of the pieces of information maintained in a statistic in SQL Server). However, sales per customer orsales per product might be much more uneven (and therefore skewed). And, when data distribution is skewed then then statistics are potentially less accurate in a histogram. This is further exacerbated by the process that SQL Server may choose of sampling your data. SQL Server will choose to sample your data (rather than reading every row) when the data they have to scan (ideally, an index but sometimes the table itself) is over 2,000 pages in size.

It’s further complicated because there are statistics on indexes as well as column-level statistics. So, I thought I’d put together a quick table to help you understand the combinations:

Statistics

Creation

Rebuild

Reorganize

Updating

Column-level

Can use sampling

n/a

n/a

Can use sampling

Index

FULLSCAN

FULLSCAN

Not updated

Can use sampling

When would you know it’s a problem? 
If you run a query and you evaluate the estimated rows vs. the actual rows and the estimate seems significantly different from the actual, then you might have a statistics problem. If you update statistics with the following code:

UPDATE STATISTICS tablename indexname (or statisticsname)

Then, try the query again. If this solved the problem, then you know that you’re not updating statistics enough. If it does not solve the problem then try updating the statistics will a fullscan:

UPDATE STATISTICS tablename indexname WITH FULLSCAN

If the estimate and actuals are more accurate (and typically result in better plans) then you might want to turn off the auto-updating of the statistic and solely control this particular statistics update with a job that updates the statistic with a full scan. The positive is that your statistics are likely to be more accurate and then your plans will be as well. The negative is that updating a statistic with a full scan can be a more costly maintenance operation. And, you have to be especially careful that your automated process doesn’t get removed or deleted leaving this statistic to never get auto-updated again. 

In summary, I don’t often recommend STATISTICS_NORECOMPUTE on indexes (or NORECOMPUTE on statistics) but there are cases when you might be able to better control the updating of statistics as well as allow for a FULLSCAN. In these cases (and when you’re certain that your automated routines will not be interrupted/deleted/modified), then using this option can be beneficial!

Related: Assessing File and Filegroup Metadata


저작자표시

'.NET > MS-SQL' 카테고리의 다른 글

[MS-SQL] 컬럼명 제약조건 추가하기  (0) 2014.01.06
SET ARITHABORT(Transact-SQL)  (0) 2014.01.02
SQL Server - Return value after INSERT  (0) 2013.12.30
SQL Server Stored Procedure capture return value in T-SQL  (0) 2013.12.30
인덱스 유형  (0) 2013.12.26

[MS-SQL] 컬럼명 제약조건 추가하기

.NET/MS-SQL
posted by 네코냥이 2014. 1. 6. 15:22

Unique제약조건추가

ALTERTABLE[테이블명]ADDCONSTRAINT[제약조건명]UNIQUE([칼럼이름])

Check제약조건추가

ALTERTABLE[테이블명]ADDCONSTRAINT[제약조건명]CHECK([칼럼이름]IN('Y','N'))

기본값추가

ALTERTABLE[테이블명]ADDCONSTRAINT[제약조건명]DEFAULT[기본값]FOR[칼럼이름]

저작자표시

'.NET > MS-SQL' 카테고리의 다른 글

STATISTICS_NORECOMPUTE – When would anyone want to use it?  (0) 2014.05.29
SET ARITHABORT(Transact-SQL)  (0) 2014.01.02
SQL Server - Return value after INSERT  (0) 2013.12.30
SQL Server Stored Procedure capture return value in T-SQL  (0) 2013.12.30
인덱스 유형  (0) 2013.12.26

SET ARITHABORT(Transact-SQL)

.NET/MS-SQL
posted by 네코냥이 2014. 1. 2. 09:50


http://technet.microsoft.com/ko-kr/library/ms190306(v=sql.105).aspx


SET ARITHABORT(Transact-SQL).pdf



SET ARITHABORT(Transact-SQL)

SQL Server 2008 R2
이 항목은 아직 평가되지 않았습니다.- 이 항목 평가

쿼리 실행 중 오버플로 또는 0으로 나누기 오류가 발생하면 쿼리를 종료합니다.

항목 링크 아이콘 Transact-SQL 구문 표기 규칙

SET ARITHABORT { ON | OFF }
[ ; ]

SET ARITHABORT 옵션과 SET ANSI WARNINGS 옵션을 ON으로 설정하면 이러한 오류 조건으로 인해 쿼리가 종료됩니다. SET ARITHABORT 옵션과 SET ANSI WARNINGS 옵션을 OFF로 설정하면 이러한 오류 조건으로 인해 일괄 처리가 종료됩니다. 트랜잭션에서 해당 오류가 발생하면 트랜잭션이 롤백됩니다. SET ARITHABORT 옵션이 OFF고 위 오류 중 하나가 발생하면 경고 메시지가 표시되고 산술 연산의 결과에 NULL이 할당됩니다.

참고참고

SET ARITHABORT 옵션과 SET ARITHIGNORE 옵션을 둘 다 설정하지 않으면 SQL Server 2005는 NULL을 반환하고 쿼리가 실행된 후 경고 메시지를 반환합니다.

ANSI_WARNINGS를 ON으로 설정하면 데이터베이스 호환성 수준이 90으로 설정된 경우 암시적으로 ARITHABORT가 ON으로 설정됩니다. 데이터베이스 호환성 수준이 80 이전으로 설정된 경우에는 ARITHABORT 옵션을 명시적으로 ON으로 설정해야 합니다.

SET ARITHABORT 옵션이 OFF일 때 INSERT, DELETE, UPDATE 문에서 식 평가 중 산술 오류(오버플로, 0으로 나누기 또는 도메인 오류)가 발생하면 SQL Server는 NULL 값을 삽입하거나 업데이트합니다. 대상 열이 Null 허용이 아니면 삽입이나 업데이트 동작이 실패하고 사용자에게 오류 메시지가 보내집니다.

SET ARITHABORT 옵션이나 SET ARITHIGNORE 옵션 중 하나가 OFF이고 SET ANSI_WARNINGS 옵션이 ON이면 SQL Server에서 0으로 나누기 또는 오버플로 오류가 발생할 경우 여전히 오류 메시지를 반환합니다.

SET ARITHABORT를 OFF로 설정한 경우 IF 문의 부울 조건을 평가하는 동안 중단 오류가 발생하면 FALSE 분기가 실행됩니다.

계산 열이나 인덱싱된 뷰에서 인덱스를 만들거나 변경할 때는 SET ARITHABORT 옵션을 ON으로 설정해야 합니다. SET ARITHABORT 옵션이 OFF면 계산 열의 인덱스가 있는 테이블이나 인덱싱된 뷰에서 CREATE, UPDATE, INSERT, DELETE 문이 실패합니다. 계산 열에 인덱싱된 뷰와 인덱스가 있을 경우 필요한 SET 옵션에 대한 자세한 내용은 결과에 영향을 주는 SET 옵션을 참조하십시오.

SET ARITHABORT 옵션은 실행 시간 또는 런타임에 설정되며, 구문 분석 시에는 설정되지 않습니다.

public 역할의 멤버 자격이 필요합니다.

다음 예에서는 두 SET ARITHABORT 옵션이 설정된 상태에서의 0으로 나누기 및 오버플로 오류를 보여 줍니다.

-- SET ARITHABORT
-------------------------------------------------------------------------------
-- Create tables t1 and t2 and insert data values.
CREATE TABLE t1 (
   a TINYINT, 
   b TINYINT
);
CREATE TABLE t2 (
   a TINYINT
);
GO
INSERT INTO t1 
VALUES (1, 0);
INSERT INTO t1 
VALUES (255, 1);
GO

PRINT '*** SET ARITHABORT ON';
GO
-- SET ARITHABORT ON and testing.
SET ARITHABORT ON;
GO

PRINT '*** Testing divide by zero during SELECT';
GO
SELECT a / b AS ab 
FROM t1;
GO

PRINT '*** Testing divide by zero during INSERT';
GO
INSERT INTO t2
SELECT a / b AS ab  
FROM t1;
GO

PRINT '*** Testing tinyint overflow';
GO
INSERT INTO t2
SELECT a + b AS ab 
FROM t1;
GO

PRINT '*** Resulting data - should be no data';
GO
SELECT * 
FROM t2;
GO

-- Truncate table t2.
TRUNCATE TABLE t2;
GO

-- SET ARITHABORT OFF and testing.
PRINT '*** SET ARITHABORT OFF';
GO
SET ARITHABORT OFF;
GO

-- This works properly.
PRINT '*** Testing divide by zero during SELECT';
GO
SELECT a / b AS ab  
FROM t1;
GO

-- This works as if SET ARITHABORT was ON.
PRINT '*** Testing divide by zero during INSERT';
GO
INSERT INTO t2
SELECT a / b AS ab  
FROM t1;
GO
PRINT '*** Testing tinyint overflow';
GO
INSERT INTO t2;
SELECT a + b AS ab 
FROM t1;
GO

PRINT '*** Resulting data - should be 0 rows';
GO
SELECT * 
FROM t2;
GO

-- Drop tables t1 and t2.
DROP TABLE t1;
DROP TABLE t2;
GO


저작자표시

'.NET > MS-SQL' 카테고리의 다른 글

STATISTICS_NORECOMPUTE – When would anyone want to use it?  (0) 2014.05.29
[MS-SQL] 컬럼명 제약조건 추가하기  (0) 2014.01.06
SQL Server - Return value after INSERT  (0) 2013.12.30
SQL Server Stored Procedure capture return value in T-SQL  (0) 2013.12.30
인덱스 유형  (0) 2013.12.26

SQL Server - Return value after INSERT

.NET/MS-SQL
posted by 네코냥이 2013. 12. 30. 14:59

No need for a separate SELECT...

INSERT INTO table (name)
OUTPUT Inserted.ID
VALUES('bob');

This works for non-IDENTITY columns (such as GUIDs) too



SQL Server - Return value after INSERT - Stack Overflow.pdf


저작자표시

'.NET > MS-SQL' 카테고리의 다른 글

[MS-SQL] 컬럼명 제약조건 추가하기  (0) 2014.01.06
SET ARITHABORT(Transact-SQL)  (0) 2014.01.02
SQL Server Stored Procedure capture return value in T-SQL  (0) 2013.12.30
인덱스 유형  (0) 2013.12.26
[MS-SQL] 인덱스 옵션  (0) 2013.09.01

SQL Server Stored Procedure capture return value in T-SQL

.NET/MS-SQL
posted by 네코냥이 2013. 12. 30. 14:56

I have a sql server stored procedure. I need to capture the return value from what the stored procedure returns

Is this the correct way of doing it

    declare valback varchar(30)

    set valback = exec storeproc1

In this case, storeproc1 is my stored procedure.



SQL Server Stored Procedure capture return value in T-SQL - Stack Overflow.pdf


저작자표시

'.NET > MS-SQL' 카테고리의 다른 글

SET ARITHABORT(Transact-SQL)  (0) 2014.01.02
SQL Server - Return value after INSERT  (0) 2013.12.30
인덱스 유형  (0) 2013.12.26
[MS-SQL] 인덱스 옵션  (0) 2013.09.01
[MS-SQL] 트리거 기초  (2) 2013.08.27

인덱스 유형

.NET/MS-SQL
posted by 네코냥이 2013. 12. 26. 10:22

-

인덱스 유형

SQL Server 2008 R2
이 항목은 아직 평가되지 않았습니다.- 이 항목 평가

다음 표에서는 SQL Server에서 사용할 수 있는 인덱스 유형을 나열하고 추가 정보에 대한 링크를 제공합니다.

인덱스 유형

설명

추가 정보

클러스터형

클러스터형 인덱스는 클러스터형 인덱스 키에 기반하여 테이블 또는 뷰의 데이터 행을 순서대로 정렬 및 저장합니다. 클러스터형 인덱스는 클러스터형 인덱스 키 값에 기반하여 행의 빠른 검색을 지원하는 B-트리 인덱스 구조로 구현됩니다.

클러스터형 인덱스 디자인 지침

클러스터형 인덱스 구조

비클러스터형 인덱스

비클러스터형 인덱스는 클러스터형 인덱스가 있는 테이블 또는 뷰에 정의하거나 힙에 정의할 수 있습니다. 비클러스터형 인덱스의 각 인덱스 행에는 비클러스터형 키 값과 행 로케이터가 있습니다. 이 로케이터는 클러스터형 인덱스 또는 키 값이 포함된 힙의 데이터 행을 가리킵니다. 인덱스 행은 인덱스 키 값의 순서대로 저장되지만 해당 테이블에 대해 클러스터형 인덱스를 만들지 않으면 데이터 행이 특정 순서대로 정렬되지 않습니다.

비클러스터형 인덱스 디자인 지침

비클러스터형 인덱스 구조

고유

고유 인덱스는 인덱스 키에 중복 값을 포함할 수 없으므로 테이블 또는 뷰의 모든 행이 고유합니다.

클러스터형 인덱스와 비클러스터형 인덱스 모두 고유 인덱스가 될 수 있습니다.

고유 인덱스 디자인 지침

포괄 열이 있는 인덱스

키 열과 함께 키가 아닌 열을 포함하도록 확장된 비클러스터형 인덱스입니다.

포괄 열이 있는 인덱스

전체 텍스트

SQL Server용 Microsoft 전체 텍스트 검색 엔진에서 작성 및 유지 관리하는 특수한 유형의 토큰 기반 인덱스입니다. 문자열 데이터에서의 복잡한 단어 검색을 효율적으로 지원합니다.

전체 텍스트 인덱스 채우기

공간

공간 인덱스는 geometry 데이터 형식 열의 공간 개체(공간 데이터)에서 특정 작업을 보다 효율적으로 수행할 수 있는 기능을 제공합니다. 공간 인덱스는 상대적으로 비용이 많이 드는 공간 작업에서 적용해야 하는 개체 수를 줄여 줍니다.

공간 인덱싱 개요

필터링됨

특히 데이터의 잘 정의된 하위 집합에서 선택하는 쿼리를 처리하는 데 적합한 최적화된 비클러스터형 인덱스입니다. 이 인덱스에서는 필터 조건자를 사용하여 테이블의 일부 행을 인덱싱합니다. 잘 디자인된 필터링된 인덱스는 전체 테이블 인덱스에 비해 쿼리 성능을 개선하고 인덱스 유지 관리 비용과 인덱스 저장소 비용을 줄일 수 있습니다.

필터링된 인덱스 디자인 지침

XML

xml 데이터 형식 열의 XML BLOB(Binary Large Object)를 영구적인 단편 형태로 표현한 것입니다.

XML 데이터 형식 열의 인덱스

-

저작자표시

'.NET > MS-SQL' 카테고리의 다른 글

SQL Server - Return value after INSERT  (0) 2013.12.30
SQL Server Stored Procedure capture return value in T-SQL  (0) 2013.12.30
[MS-SQL] 인덱스 옵션  (0) 2013.09.01
[MS-SQL] 트리거 기초  (2) 2013.08.27
[SQL CE] SQL Server Compact - 버전별 새로운 기능  (0) 2013.08.05

[MS-SQL] 인덱스 옵션

.NET/MS-SQL
posted by 네코냥이 2013. 9. 1. 18:18


COSMOS__[MSSQL] CREATE INDEX 옵션.pdf


저작자표시

'.NET > MS-SQL' 카테고리의 다른 글

SQL Server Stored Procedure capture return value in T-SQL  (0) 2013.12.30
인덱스 유형  (0) 2013.12.26
[MS-SQL] 트리거 기초  (2) 2013.08.27
[SQL CE] SQL Server Compact - 버전별 새로운 기능  (0) 2013.08.05
[MS-SQL] 임시테이블, 전역 임시테이블  (0) 2013.07.24

[MS-SQL] 트리거 기초

.NET/MS-SQL
posted by 네코냥이 2013. 8. 27. 10:14

http://blog.naver.com/primary_key/110023557784

[출처] MSSQL Trigger(기초)|작성자 푸른밤

[출처] MSSQL Trigger(기초)|작성자 푸른밤



오라클은 트리거를 탈때 칼럼마다 OLD, NEW가 생성되는데 MSSQL은 변형된 데이터가 테이블에 저장되는 것이다.

 

간단히 정리하자면

MSSQL 의 임시테이블 DELETED, INSERTED 는

 

데이터 INSERT의경우  : INSERTED 발생

데이터 UPDATE의경우  : DELETED, INSERTED 발생

데이터 DELETE의 경우 : DELETED 발생

 

한다.

 

간단한 테스튼 아래와 같다


ALTER TRIGGER [rzzzTest] on [dbo].[zzzTest]
FOR INSERT, UPDATE, DELETE NOT FOR REPLICATION
AS
BEGIN

 DECLARE @CNT_INS INT
  , @CNT_DEL INT
 
 SELECT @CNT_DEL = COUNT(*) FROM DELETED 
 SELECT @CNT_INS = COUNT(*) FROM INSERTED 
 
 IF @CNT_DEL > 0 AND @CNT_INS > 0 BEGIN --UPDATE된 것임 
   update c set
    c.B1 = 'update'
    ,c.G1 = @CNT_DEL
    ,c.F1 = @CNT_INS
   from zzzTest c, inserted b
   where c.itemcode = b.itemcode
   select 'update'
 END ELSE IF @CNT_DEL = 0 AND @CNT_INS > 0 BEGIN --INSERT 된 것임 
  update c set
    c.a1 = 'insert'
    ,c.G1 = @CNT_DEL
    ,c.F1 = @CNT_INS
   from zzzTest c, inserted b
   where c.itemcode = b.itemcode
  select 'insert'
 END 
 ELSE IF @CNT_DEL > 0 AND @CNT_INS = 0 BEGIN --DELETE 된 것임 
  SELECT 1 --아무 이벤트 없는 것임
 END 
end

[출처] MSSQL Trigger(기초)|작성자 푸른밤


저작자표시

'.NET > MS-SQL' 카테고리의 다른 글

인덱스 유형  (0) 2013.12.26
[MS-SQL] 인덱스 옵션  (0) 2013.09.01
[SQL CE] SQL Server Compact - 버전별 새로운 기능  (0) 2013.08.05
[MS-SQL] 임시테이블, 전역 임시테이블  (0) 2013.07.24
[MS-SQL] 성능모니터 간략설명  (0) 2013.07.24

[SQL CE] SQL Server Compact - 버전별 새로운 기능

.NET/MS-SQL
posted by 네코냥이 2013. 8. 5. 10:22


SQL Server Compact 4 기능설명.pdf


저작자표시

'.NET > MS-SQL' 카테고리의 다른 글

[MS-SQL] 인덱스 옵션  (0) 2013.09.01
[MS-SQL] 트리거 기초  (2) 2013.08.27
[MS-SQL] 임시테이블, 전역 임시테이블  (0) 2013.07.24
[MS-SQL] 성능모니터 간략설명  (0) 2013.07.24
[MS-SQL] Group By 예제  (0) 2013.07.24

[MS-SQL] 임시테이블, 전역 임시테이블

.NET/MS-SQL
posted by 네코냥이 2013. 7. 24. 13:49


Temporary and Global Temporary Table in SQL Server 2008.pdf


저작자표시

'.NET > MS-SQL' 카테고리의 다른 글

[MS-SQL] 트리거 기초  (2) 2013.08.27
[SQL CE] SQL Server Compact - 버전별 새로운 기능  (0) 2013.08.05
[MS-SQL] 성능모니터 간략설명  (0) 2013.07.24
[MS-SQL] Group By 예제  (0) 2013.07.24
[MS-SQL] 프로파일러 및 튜닝어드바이서 사용법  (0) 2013.06.14

티스토리툴바