Ruru's PlayGround :)

MSSQL을 사용하다 보면 가끔씩 Database가 '제한된 사용자' 라고 표시되고

정상적인 작동이 안될 때가 있다.

이럴 때 아래와 같은 명령으로 처리해주면 되겠다.

그런데 왠지 조금 불안한.... 느낌은 왜인지 =ㅅ =

ALTER DATABASE <Database Name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE <Database Name> SET MULTI_USER
GO

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

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-동일조건변경허락 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

SQL Server에서 특정 DB의 테이블에 저장된 데이터들의

단편화 상태를 확인하려면 다음과 같은 명령어를 사용한다.

USE DB명
GO
DBCC SHOWCONTIG
GO

예로 아래와 같은 형태로

리포트가 나타난다. 내용이 많고 모르는 것도 있지만 대충은 '검색 밀도' 부분을 보고 단편화가 얼마나 되어 있구나. 튜닝이 좀 필요하겠구나~

하는 정도는 알 수 있을 것이다.

DBCC SHOWCONTIG이(가) 'GST_Alert' 테이블을 검색하는 중...
테이블: 'GST_Alert'(178099675); 인덱스 ID: 1, 데이터베이스 ID: 25
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 2
- 검색한 익스텐트 ..............................: 2
- 익스텐트 스위치..............................: 1
- 익스텐트당 평균 페이지 수........................: 1.0
- 검색 밀도[최적:실제].......: 50.00% [1:2]
- 논리 검색 조각화 상태 ..................: 50.00%
- 익스텐트 검색 조각화 상태 ...................: 50.00%
- 페이지당 사용 가능한 평균 바이트 수.....................: 2931.0
- 평균 페이지 밀도(전체).....................: 63.79%
DBCC SHOWCONTIG이(가) 'GST_AlertError' 테이블을 검색하는 중...
테이블: 'GST_AlertError'(226099846); 인덱스 ID: 0, 데이터베이스 ID: 25
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 0
- 검색한 익스텐트 ..............................: 0
- 익스텐트 스위치..............................: 0
- 익스텐트당 평균 페이지 수........................: 0.0
- 검색 밀도[최적:실제].......: 100.00% [0:0]
- 익스텐트 검색 조각화 상태 ...................: 0.00%
- 페이지당 사용 가능한 평균 바이트 수.....................: 0.0
- 평균 페이지 밀도(전체).....................: 0.00%
DBCC SHOWCONTIG이(가) 'GST_AlertHistory' 테이블을 검색하는 중...
테이블: 'GST_AlertHistory'(258099960); 인덱스 ID: 0, 데이터베이스 ID: 25
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 6
- 검색한 익스텐트 ..............................: 4
- 익스텐트 스위치..............................: 3
- 익스텐트당 평균 페이지 수........................: 1.5
- 검색 밀도[최적:실제].......: 25.00% [1:4]
- 익스텐트 검색 조각화 상태 ...................: 75.00%
- 페이지당 사용 가능한 평균 바이트 수.....................: 7170.2
- 평균 페이지 밀도(전체).....................: 11.41%
DBCC SHOWCONTIG이(가) 'GST_BLOG' 테이블을 검색하는 중...
테이블: 'GST_BLOG'(290100074); 인덱스 ID: 1, 데이터베이스 ID: 25
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 0
- 검색한 익스텐트 ..............................: 0
- 익스텐트 스위치..............................: 0
- 익스텐트당 평균 페이지 수........................: 0.0
- 검색 밀도[최적:실제].......: 100.00% [0:0]
- 논리 검색 조각화 상태 ..................: 0.00%
- 익스텐트 검색 조각화 상태 ...................: 0.00%
- 페이지당 사용 가능한 평균 바이트 수.....................: 0.0
- 평균 페이지 밀도(전체).....................: 0.00%
DBCC SHOWCONTIG이(가) 'GST_Alert_Key' 테이블을 검색하는 중...
테이블: 'GST_Alert_Key'(322100188); 인덱스 ID: 0, 데이터베이스 ID: 25
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 0
- 검색한 익스텐트 ..............................: 0
- 익스텐트 스위치..............................: 0
- 익스텐트당 평균 페이지 수........................: 0.0
- 검색 밀도[최적:실제].......: 100.00% [0:0]
- 익스텐트 검색 조각화 상태 ...................: 0.00%
- 페이지당 사용 가능한 평균 바이트 수.....................: 0.0
- 평균 페이지 밀도(전체).....................: 0.00%
DBCC SHOWCONTIG이(가) 'GST_FILES' 테이블을 검색하는 중...
테이블: 'GST_FILES'(338100245); 인덱스 ID: 1, 데이터베이스 ID: 25
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 1
- 검색한 익스텐트 ..............................: 1
- 익스텐트 스위치..............................: 0
- 익스텐트당 평균 페이지 수........................: 1.0
- 검색 밀도[최적:실제].......: 100.00% [1:1]
- 논리 검색 조각화 상태 ..................: 0.00%
- 익스텐트 검색 조각화 상태 ...................: 0.00%
- 페이지당 사용 가능한 평균 바이트 수.....................: 6474.0
- 평균 페이지 밀도(전체).....................: 20.01%
DBCC SHOWCONTIG이(가) 'TB_MEMO' 테이블을 검색하는 중...
테이블: 'TB_MEMO'(370100359); 인덱스 ID: 1, 데이터베이스 ID: 25
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 1
- 검색한 익스텐트 ..............................: 1
- 익스텐트 스위치..............................: 0
- 익스텐트당 평균 페이지 수........................: 1.0
- 검색 밀도[최적:실제].......: 100.00% [1:1]
- 논리 검색 조각화 상태 ..................: 0.00%
- 익스텐트 검색 조각화 상태 ...................: 0.00%
- 페이지당 사용 가능한 평균 바이트 수.....................: 7930.0
- 평균 페이지 밀도(전체).....................: 2.03%
DBCC SHOWCONTIG이(가) 'GST_Receiver' 테이블을 검색하는 중...
테이블: 'GST_Receiver'(402100473); 인덱스 ID: 1, 데이터베이스 ID: 25
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 2
- 검색한 익스텐트 ..............................: 2
- 익스텐트 스위치..............................: 1
- 익스텐트당 평균 페이지 수........................: 1.0
- 검색 밀도[최적:실제].......: 50.00% [1:2]
- 논리 검색 조각화 상태 ..................: 50.00%
- 익스텐트 검색 조각화 상태 ...................: 50.00%
- 페이지당 사용 가능한 평균 바이트 수.....................: 804.0
- 평균 페이지 밀도(전체).....................: 90.07%
DBCC SHOWCONTIG이(가) 'GST_AlertStatus' 테이블을 검색하는 중...
테이블: 'GST_AlertStatus'(482100758); 인덱스 ID: 0, 데이터베이스 ID: 25
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 1
- 검색한 익스텐트 ..............................: 1
- 익스텐트 스위치..............................: 0
- 익스텐트당 평균 페이지 수........................: 1.0
- 검색 밀도[최적:실제].......: 100.00% [1:1]
- 익스텐트 검색 조각화 상태 ...................: 0.00%
- 페이지당 사용 가능한 평균 바이트 수.....................: 8059.0
- 평균 페이지 밀도(전체).....................: 0.43%
DBCC SHOWCONTIG이(가) 'GST_Sender' 테이블을 검색하는 중...
테이블: 'GST_Sender'(514100872); 인덱스 ID: 1, 데이터베이스 ID: 25
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 3
- 검색한 익스텐트 ..............................: 3
- 익스텐트 스위치..............................: 2
- 익스텐트당 평균 페이지 수........................: 1.0
- 검색 밀도[최적:실제].......: 33.33% [1:3]
- 논리 검색 조각화 상태 ..................: 66.67%
- 익스텐트 검색 조각화 상태 ...................: 33.33%
- 페이지당 사용 가능한 평균 바이트 수.....................: 1363.0
- 평균 페이지 밀도(전체).....................: 83.16%
DBCC SHOWCONTIG이(가) 'GST_SMS_USER' 테이블을 검색하는 중...
테이블: 'GST_SMS_USER'(1317579732); 인덱스 ID: 0, 데이터베이스 ID: 25
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 15
- 검색한 익스텐트 ..............................: 4
- 익스텐트 스위치..............................: 3
- 익스텐트당 평균 페이지 수........................: 3.8
- 검색 밀도[최적:실제].......: 50.00% [2:4]
- 익스텐트 검색 조각화 상태 ...................: 50.00%
- 페이지당 사용 가능한 평균 바이트 수.....................: 3540.4
- 평균 페이지 밀도(전체).....................: 56.26%
DBCC SHOWCONTIG이(가) 'GST_SMS_GROUP' 테이블을 검색하는 중...
테이블: 'GST_SMS_GROUP'(1381579960); 인덱스 ID: 1, 데이터베이스 ID: 25
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 1
- 검색한 익스텐트 ..............................: 1
- 익스텐트 스위치..............................: 0
- 익스텐트당 평균 페이지 수........................: 1.0
- 검색 밀도[최적:실제].......: 100.00% [1:1]
- 논리 검색 조각화 상태 ..................: 0.00%
- 익스텐트 검색 조각화 상태 ...................: 0.00%
- 페이지당 사용 가능한 평균 바이트 수.....................: 3429.0
- 평균 페이지 밀도(전체).....................: 57.64%
DBCC SHOWCONTIG이(가) 'GST_UserSetting' 테이블을 검색하는 중...
테이블: 'GST_UserSetting'(1506104406); 인덱스 ID: 1, 데이터베이스 ID: 25
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 0
- 검색한 익스텐트 ..............................: 0
- 익스텐트 스위치..............................: 0
- 익스텐트당 평균 페이지 수........................: 0.0
- 검색 밀도[최적:실제].......: 100.00% [0:0]
- 논리 검색 조각화 상태 ..................: 0.00%
- 익스텐트 검색 조각화 상태 ...................: 0.00%
- 페이지당 사용 가능한 평균 바이트 수.....................: 0.0
- 평균 페이지 밀도(전체).....................: 0.00%
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

그러면 이러한 조각화는 어떻게 해결할까?

단편화의 처리는 Index Deflagmenting & Index rebuilding 작업으로 진행합니다.


일반적으로 DBCC 명령어를 통해 작업하고

DBCC DBREINDEX명령과 DBCC INDEXDEFLAG 명령으로 진행하죠.

아래와 같은 구문을 사용하면 손쉽게 테이블 전체에 인덱스 rebuilding 작업을 할 수 있습니다.

use DB명
go
exec dbo.sp_MSforeachtable 'dbcc dbreindex(''?'') with no_infomsgs'
go

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-동일조건변경허락 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

SQL Server, Lock Objects (On Performance Counter)
http://msdn.microsoft.com/en-us/library/ms190216.aspx?wa=wsignin1.0


Locking in Database Engine (SQL Server 2008 R2)
http://msdn.microsoft.com/en-us/library/ms190615.aspx


Locking
http://msdn.microsoft.com/en-us/library/aa213037(SQL.80).aspx


http://msdn.microsoft.com/en-us/library/aa213029(SQL.80).aspx

• Lost or buried updates.
  
• Uncommitted dependency (dirty read).
  
• Inconsistent analysis (nonrepeatable read).
  
• Phantom reads.
  
  
  Displaying Locking Information
  http://msdn.microsoft.com/en-us/library/aa213025(SQL.80).aspx
  
  
  Deadlocking (What's Deadlock?)
  http://msdn.microsoft.com/en-us/library/ms177433.aspx
  

Minimizing Deadlock (How to avoid dealock?)
http://msdn.microsoft.com/en-us/library/ms191242.aspx

Detecting and Ending Deadlock
http://msdn.microsoft.com/en-us/library/ms178104.aspx

Handling Deadlocks
http://msdn.microsoft.com/en-us/library/ms177453.aspx


Optimizing Server Performance Using Memory Configuration Options
http://msdn.microsoft.com/en-us/library/ms177455.aspx


How to resolve a deadlock - SQL Server technical bulletin
http://support.microsoft.com/kb/832524/en-us

• Start SQL Server Enterprise Manager.
• Select, and then right-click the server.
• Click Properties.
• Click Startup Parameters.
• In the Startup Parameters dialog box, type -T1204 in the Parameters text box, and then click Add.
• In the Parameters text box, type -T3605, and then click Add.
• Click OK.

 

 

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-동일조건변경허락 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

MS SQL 서버에서 테이블을 정의할 때 Datetime Column에 'GETDATE()' 형식으로 기본 값을 지정하게 되죠.

Procedure에서는 이와 같이 적용되지가 않습니다.
아래 처럼 해줘야 기본 값을 현재 시간(GETDATE())으로 설정할 수 있습니다.

CREATE PROCEDURE TestProc
(
    @TestDate DATETIME = NULL
)
AS

    IF @TestDate IS NULL SET @TestDate = GETDATE()
    ...........
    RETURN
GO

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-동일조건변경허락 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

MSSQL 에서는 테이블 목록을 보기 위해서 다음과 같은 쿼리를 실행하거나 저장프로시저를 실행해 주어야 한다.

1. sp_tables

2. select * from information_schema.tables

3. select name from sysobjects where type='U'

MySQL 이나 Oracle이라면 DESC 이렇게 입력하면 될텐데...

명령어는 왜 다들 다르게 만드는건지 원 - _-..

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-동일조건변경허락 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

RUBY로  win32 OLE 를 이용해 ADO 연결하는 방법을 아래 소개합니다.
출처 : http://rubyonwindows.blogspot.com/search/label/sqlserver


require 'win32ole'

class SqlServer
    # This class manages database connection and queries
    attr_accessor :connection, :data, :fields

    def initialize
        @connection = nil
        @data = nil
    end

    def open
        # Open ADO connection to the SQL Server database
        connection_string =  "Provider=SQLOLEDB.1;"
        connection_string << "Persist Security Info=False;"
        connection_string << "User ID=USER_ID;"
        connection_string << "password=PASSWORD;"
        connection_string << "Initial Catalog=DATABASE;"
        connection_string << "Data Source=IP_ADDRESS;"
        connection_string << "Network Library=dbmssocn"
        @connection = WIN32OLE.new('ADODB.Connection')
        @connection.Open(connection_string)
    end

    def query(sql)
        # Create an instance of an ADO Recordset
        recordset = WIN32OLE.new('ADODB.Recordset')
        # Open the recordset, using an SQL statement and the
        # existing ADO connection
        recordset.Open(sql, @connection)
        # Create and populate an array of field names
        @fields = []
        recordset.Fields.each do |field|
            @fields << field.Name
        end
        begin
            # Move to the first record/row, if any exist
            recordset.MoveFirst
            # Grab all records
            @data = recordset.GetRows
        rescue
            @data = []
        end
        recordset.Close
        # An ADO Recordset's GetRows method returns an array
        # of columns, so we'll use the transpose method to
        # convert it to an array of rows
        @data = @data.transpose
    end

    def close
        @connection.Close
    end
end

You can then use this class as follows:

db = SqlServer.new
db.open
db.query("SELECT PLAYER FROM PLAYERS WHERE TEAM = 'REDS';")
field_names = db.fields
players = db.data
db.close

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-동일조건변경허락 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

select top 20  *
from  authors
where au_id not in (select top 10 au_id from authors  order by au_id)
order by au_id

데이터 상단의 20개 중에 처음 10개를 제외한 11~20개의 데이터를 가져온다.

MySQL 사용할 땐 limit 구문을 이용해서 limit 10, 10 이런식으로 쉽게 처리를 했는데..

이런 부분에선 약간 불편한 듯 하다..

대신 그 만큼 강한 기능 때문에 사용하지 않을까?

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-동일조건변경허락 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

ER-WIN 이나 기타 외부에서 MS-SQL에 연결하기 위해 DLL 파일이 필요하다.

이 DLL파일은 MSSQL 2000에서 제공되는 DLL 파일이지만 MSSQL 2005에서도 이용할 수 있다.

이 파일은 ER-WIN의 사례만을 얘기했지만, PHP에서 MSSQL을 연결하기 위해서도 위 DLL 파일이 필요하고 기타 IDE 등에서도 위 DLL 파일이 필요한 경우가 많음을 확인했다.

자주 사용되는 DLL 인데 매번 찾을 때마다 찾기 귀찮아서 여기에 달아둔다 ^ ^

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-동일조건변경허락 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.