블로그 이미지
LifeisSimple

calendar

 «  » 2024.4
일 월 화 수 목 금 토
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

Notice

2012. 2. 13. 18:10 Brain Trainning/DataBase

[MSSQL] SQL Server Hardware 관련 좋은 책 한권...


SQL 서버 하드웨어 관련해서 좋은 책 한권이 올라왔네요... 
RED GATE에서 올리는 책인데... 내용이 좋은 것 같습니다. Adobe Digital Edition 이나 Epub 을 읽을 수 있는 Reader 로 보시면 됩니다. 

SQLServerHardware.epub


SQL Server HardwareSQL Server Hardware by Glenn Berry

Buy Printed Book: $29.99
Buy Kindle Edition: $14.99
Download ePub: Free

SQL Server Hardware will provide the fundamental knowledge and resources you need to make intelligent decisions about choice, and optimal installation and configuration, of SQL Server hardware, operating system and the SQL Server RDBMS.

Editorial Review

Relational databases place heavy demands on their underlying hardware and many of these databases are mission-critical resources for multiple applications, where performance bottlenecks are immediately noticeable and often very costly to the business. Despite this, many database administrators are not very knowledgeable about server hardware. Many medium-to-large companies have completely separate departments that are responsible for hardware selection, configuration, and maintenance, and the DBA with no knowledge of hardware is often completely at their mercy.

Likewise, many DBAs are also unaware of the performance implications of the various options and configurations for SQL Server, and the Operating System on which it is installed. Glenn Berry is a highly experienced Database Architect, teacher and SQL Server MVP, and his book is designed to provide the fundamental knowledge and resources you need to make intelligent choices about optimal installation and configuration of SQL Server hardware, operating system and the SQL Server RDBMS. It covers:

  • Current, and upcoming hardware, including processors, chipsets, memory, and storage subsystems
  • How to evaluate hardware for use with different types of SQL Server workloads
  • Strengths and weaknesses of the various versions and editions of Windows Server, and their suitability for use with different versions and editions of SQL Server
  • How to install, patch and configure the operating system for use with SQL Server
  • SQL Server editions and licenses
  • Installing and configuring SQL Server, including how to acquire and install service packs, cumulative updates, and hotfixes
  • Methods for quickly and easily upgrading to newer versions of the operating system and SQL Server with minimal downtime

With what you learn in this book, you'll be able to ensure that your SQL Server instances can handle gracefully the CPU, memory and IO workload generated by your applications, and that the operating system and SQL Server itself are installed, patched, and configured for maximum performance and reliability.
저작자표시 비영리 변경금지

'Brain Trainning > DataBase' 카테고리의 다른 글

[MSSQL] How to Document and Configure SQL Server Instance Settings (펌)  (0) 2012.02.14
[MSSQL] SQL Server 2012 Active/Active Cluster in Hyper-V  (0) 2012.02.14
[MSSQL] Backup VLDB Over Network (MS Case Study 자료)  (0) 2012.02.13
[Oracle] Load Generator For Oracle - Swingbench  (0) 2012.02.12
[MSSQL] DMV - sp_who2 Version  (0) 2012.02.07
posted by LifeisSimple
2010. 12. 3. 17:27 Brain Trainning/PRG Language

Connection String 세팅

아무 생각없이 고정문자열을 Cut & Paste 하는 Connection String 이지만 
많은걸 지원하고 있습니다. 잘 읽어두면 Connection 관련 문제 해결할때 많은 도움이 됩니다. 

요건 관련 사이트 : 

Keywords

There are two ways OLE DB applications can initialize data source objects:

  • IDBInitialize::Initialize

  • IDataInitialize::GetDataSource

In the first case, a provider string can be used to initialize connection properties by setting the property DBPROP_INIT_PROVIDERSTRING in the DBPROPSET_DBINIT property set. In the second case, an initialization string can be passed to IDataInitialize::GetDataSource method to initialize connection properties. Both methods initialize the same OLE DB connection properties, but different sets of keywords are used. The set of keywords used by IDataInitialize::GetDataSource is at minimum the description of properties within the initialization property group.

Boolean properties set in provider strings via DBPROP_INIT_PROVIDERSTRING values are set using the values "yes" and "no". Boolean properties set in initialization strings using IDataInitialize::GetDataSource are set using the values "true" and "false".

Applications using IDataInitialize::GetDataSource can also use the keywords used by IDBInitialize::Initialize but only for properties that do not have a default value. If an application uses both the IDataInitialize::GetDataSource keyword and the IDBInitialize::Initialize keyword in the initialization string, the IDataInitialize::GetDataSource keyword setting is used. It is strongly recommended that applications do not use IDBInitialize::Initialize keywords in IDataInitialize:GetDataSource connection strings, as this behavior may not be maintained in future releases.

Connection strings used by OLE DB applications using DBPROP_INIT_PROVIDERSTRING with IDBInitialize::Initialize have the following syntax:

connection-string ::= empty-string[;] | attribute[;] | attribute; connection-string

empty-string ::=

attribute ::= attribute-keyword=[{]attribute-value[}]

attribute-value ::= character-string

attribute-keyword ::= identifier

Attribute values can optionally be enclosed in braces, and it is good practice to do so. This avoids problems when attribute values contain non-alphanumeric characters. The first closing brace in the value is assumed to terminate the value, so values cannot contain closing brace characters.

The following table describes the keywords that may be used with DBPROP_INIT_PROVIDERSTRING.

Keyword Initialization property Description

Addr

SSPROP_INIT_NETWORKADDRESS

Synonym for "Address".

Address

SSPROP_INIT_NETWORKADDRESS

The network address of an instance of SQL Server in the organization.

APP

SSPROP_INIT_APPNAME

The string identifying the application.

AttachDBFileName

SSPROP_INIT_FILENAME

The name of the primary file (include the full path name) of an attachable database. To use AttachDBFileName, you must also specify the database name with the provider string Database keyword. If the database was previously attached, SQL Server does not reattach it (it uses the attached database as the default for the connection).

Auto Translate

SSPROP_INIT_AUTOTRANSLATE

Synonym for "AutoTranslate".

AutoTranslate

SSPROP_INIT_AUTOTRANSLATE

Configures OEM/ANSI character translation. Recognized values are "yes" and "no".

Database

DBPROP_INIT_CATALOG

The database name.

DataTypeCompatibility

SSPROP_INIT_DATATYPECOMPATIBILITY

Specifies the mode of data type handling to use. Recognized values are "0" for provider data types and "80" for SQL Server 2000 data types.

Encrypt

SSPROP_INIT_ENCRYPT

Specifies whether data should be encrypted before sending it over the network. Possible values are "yes" and "no". The default value is "no".

FailoverPartner

SSPROP_INIT_FAILOVERPARTNER

The name of the failover server used for database mirroring.

FailoverPartnerSPN

SSPROP_INIT_FAILOVERPARTNERSPN

The SPN for the failover partner. The default value is an empty string. An empty string causes SQL Server Native Client to use the default, provider-generated SPN.

Language

SSPROPT_INIT_CURRENTLANGUAGE

The SQL Server language.

MarsConn

SSPROP_INIT_MARSCONNECTION

Enables or disables multiple active result sets (MARS) on the connection if the server is SQL Server 2005 or later. Possible values are "yes" and "no". The default value is "no".

Net

SSPROP_INIT_NETWORKLIBRARY

Synonym for "Network".

Network

SSPROP_INIT_NETWORKLIBRARY

The network library used to establish a connection to an instance of SQL Server in the organization.

Network Library

SSPROP_INIT_NETWORKLIBRARY

Synonym for "Network".

PacketSize

SSPROP_INIT_PACKETSIZE

Network packet size. The default is 4096.

PersistSensitive

DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO

Accepts the strings "yes" and "no" as values. When "no", the data source object is not allowed to persist sensitive authentication information

PWD

DBPROP_AUTH_PASSWORD

The SQL Server login password.

Server

DBPROP_INIT_DATASOURCE

The name of an instance of SQL Server in the organization.

When not specified, a connection is made to the default instance on the local computer.

ServerSPN

SSPROP_INIT_SERVERSPN

The SPN for the server. The default value is an empty string. An empty string causes SQL Server Native Client to use the default, provider-generated SPN.

Timeout

DBPROP_INIT_TIMEOUT

The amount of time (in seconds) to wait for data source initialization to complete.

Trusted_Connection

DBPROP_AUTH_INTEGRATED

When "yes", instructs the SQL Server Native Client OLE DB provider to use Windows Authentication Mode for login validation. Otherwise instructs the SQL Server Native Client OLE DB provider to use a SQL Server username and password for login validation, and the UID and PWD keywords must be specified.

TrustServerCertificate

SSPROP_INIT_TRUST_SERVER_CERTIFICATE

Accepts the strings "yes" and "no" as values. The default value is "no", which means that the server certificate will be validated.

UID

DBPROP_AUTH_USERID

The SQL Server login name.

UseProcForPrepare

SSPROP_INIT_USEPROCFORPREP

This keyword is deprecated, and its setting is ignored by the SQL Server Native Client OLE DB Provider.

WSID

SSPROP_INIT_WSID

The workstation identifier.

Connection strings used by OLE DB applications using IDataInitialize::GetDataSource have the following syntax:

connection-string ::= empty-string[;] | attribute[;] | attribute; connection-string

empty-string ::=

attribute ::= attribute-keyword=[quote]attribute-value[quote]

attribute-value ::= character-string

attribute-keyword ::= identifier

quote ::= " | '

Attribute values can optionally be enclosed in single or double quotes, and it is good practice to do so. This avoids problems when values contain non-alphanumeric characters. The quote character used can also appear in values, provided that it is doubled.

저작자표시 비영리 변경금지

'Brain Trainning > PRG Language' 카테고리의 다른 글

[MSSQL] JDBC 연결  (0) 2011.02.25
[MongoDB] Comparison of data serialization formats  (0) 2011.01.18
MSSQL + PHP 연동  (0) 2010.12.21
Programming Windows Phone 7, by Charles Petzold  (0) 2010.10.29
WidowsClient.Net  (0) 2010.05.12
posted by LifeisSimple
2010. 8. 17. 16:23 Brain Trainning/DataBase

sp_readerrorlog (xp_readerrorlog)

sp_readerrorlog

CREATE PROC [sys].[sp_readerrorlog]( 
   @p1     INT = 0, 
   @p2     INT = NULL, 
   @p3     VARCHAR(255) = NULL, 
   @p4     VARCHAR(255) = NULL) 
AS 
BEGIN 

   IF (NOT IS_SRVROLEMEMBER(N'securityadmin') = 1) 
   BEGIN 
      RAISERROR(15003,-1,-1, N'securityadmin') 
      RETURN (1) 
   END 
    
   IF (@p2 IS NULL) 
       EXEC sys.xp_readerrorlog @p1 
   ELSE 
       EXEC sys.xp_readerrorlog @p1,@p2,@p3,@p4 
END


xp_readerrorlog

Even though sp_readerrolog accepts only 4 parameters, the extended stored procedure accepts at least 7 parameters.

If this extended stored procedure is called directly the parameters are as follows:

  1. Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
  2. Log file type: 1 or NULL = error log, 2 = SQL Agent log
  3. Search string 1: String one you want to search for
  4. Search string 2: String two you want to search for to further refine the results
  5. Search from start time  
  6. Search to end time
  7. Sort order for results: N'asc' = ascending, N'desc' = descending
EXEC master.dbo.xp_readerrorlog 6, 1, '2005', 'exec', NULL, NULL, N'desc' 
EXEC master.dbo.xp_readerrorlog 6, 1, '2005', 'exec', NULL, NULL, N'asc'


- 날짜 조회 방법
exec master.dbo.xp_readerrorlog 0, 1, null, null, '2010-08-15', '2010-08-17', N'asc'

저작자표시 비영리 변경금지

'Brain Trainning > DataBase' 카테고리의 다른 글

Understanding How Restore and Recovery of Backups Work in SQL Server 2008 R2  (0) 2010.08.19
Understanding How Restore and Recovery of Backups Work in SQL Server 2005  (0) 2010.08.19
특정이름의 테이블/SP 등등 삭제  (0) 2010.08.13
버전 호환성 Upgrade 작업 후 해야할 작업  (0) 2010.08.12
SP_Helpindex2  (0) 2010.08.11
posted by LifeisSimple
2010. 4. 19. 16:29 Brain Trainning/DataBase

페이징쿼리 속도 비교 (펌글)

페이징 쿼리 종류별 성능 비교 자료

페이징 쿼리 종류별로 성능을 비교한 좋은 자료가 있어서 상당부분 인용했습니다.

"ex)"에 들어가는 샘플 쿼리는 바투 락커룸 DB에 파일첨부(TB_AttachFile) 테이블을 대상으로 

페이징 쿼리를 작성해 봤습니다.


5번에 표시된 내용이 데이터 건수에 상관없이 실질적으로 가장 빠르지만 우리 시스템에 적용할 수 있는지는

의문이 갑니다.

테이블 별로 인덱스 생성이 필요할 수 있고 중간에 데이터가 삭제되거나 어떤 반응이 생길 때 문제가 될 

소지가 있는지도 조사해야 할 것 같습니다.

참고로 웹젠 빌링에서 사용하던 페이징 쿼리는 4번을 이용했었습니다.

그리고 2번에 샘플 쿼리를 보면 TB_AttachFile 테이블에 FileGuid가 None Clustered Index로 걸려 있어서 

서브쿼리 내에서도 ORDER BY FileGuid DESC를 해줘야합니다.(즉 ORDER BY 를 2번 해야한다는..)

대부분의 기본키들이 None Clustered Index 라 이 점에 대한 이슈도 있습니다.



1. SELECT TOP [불러올 총 게시물수] [출력 필드명] FROM [테이블 명]  

  레코드셋의 AbsolutePage를 이용해서 페이징을 했습니다.

ex)

SELECT TOP 10 FileGuid, FileName, Capacity, RegDate 
FROM TB_AttachFile 



2. SELECT TOP [불러올 총 게시물수] [출력 필드명] FROM [테이블 명]
   WHERE [글번호필드] NOT IN (SELECT TOP [제거할 게시물수] [글번호필드] FROM [테이블명])

   예전에 태요 사이트에서 보았던 쿼리 구문입니다.. NOT IN 때문에.. 문제시 되었던 쿼리구문이죠.

ex)

SELECT TOP 10 FileGuid, FileName, Capacity, RegDate 
FROM TB_AttachFile 
WHERE FileGuid NOT IN 
(
 SELECT TOP 0 FileGuid 
 FROM TB_AttachFile
 ORDER BY FileGuid DESC
) 
ORDER BY FileGuid DESC



3. SELECT TOP [불러올 총 게시물수] [출력 필드명] FROM [테이블 명]
   WHERE [글번호] IN (SELECT TOP [페이지출력 갯수] [글번호] FROM
   (SELECT TOP [불러올 총 게시물수] [글번호] FROM [테이블 명]) AS A ORDER BY [글번호])
   ORDER BY [글번호] DESC

   이 쿼리 구문은 2번의 쿼리 구문의 문제점을 보완한 구문입니다. NOT IN 대신에 IN을 사용 했습니다.

ex)

SELECT TOP 10 FileGuid, FileName, Capacity, RegDate 
FROM TB_AttachFile 
WHERE FileGuid IN 
(
 SELECT TOP 123 FileGuid -- 총 데이터수 - ( (페이지수 - 1) * 10) // 1페이지 : 123 - 0, 2페이지 : 123 - 10 ...
 FROM 
 (
  SELECT FileGuid 
  FROM TB_AttachFile 
 )AS A 
 ORDER BY FileGuid
) 
ORDER BY FileGuid DESC



4. SELECT TOP [페이지 출력갯수] [출력 필드명] FROM [테이블 명]
   WHERE [글번호] <= (SELECT MIN([글번호])
   FROM (SELECT TOP [제거할 게시물수] + 1 [글번호] FROM [테이블명]) AS A)

   4번째 쿼리 구문은 IN, NOT IN이 아닌 출력할 마지막 글번호 바로 앞이 글번호를 찾아서 처리를 해주는
   쿼리 구문입니다.

ex)

SELECT TOP 10 FileGuid, FileName, Capacity, RegDate 
FROM TB_AttachFile 
WHERE FileGuid <=
(
 SELECT MIN(FileGuid)
 FROM 
 (
  SELECT TOP 11 FileGuid -- (현재페이지수-1) * 10 + 1  
  FROM TB_AttachFile
  ORDER BY FileGuid DESC
 )AS A 
) 
ORDER BY FileGuid DESC



5. SELECT TOP [페이지 출력갯수] [출력 필드명] FROM [테이블명]
   WHERE [글번호] <= (SELECT MIN([글번호])
   FROM (SELECT TOP [제거할 게시물수] + 1 [글번호]
   FROM [테이블명] WHERE [인덱스] = [시작인덱스번호] AND [인덱스] = [끝인덱스번호]) AS A
   WHERE [인덱스] = [시작인덱스번호] AND [인덱스] = [끝인덱스번호])
   AND [인덱스] = [시작인덱스번호] AND [인덱스] = [끝인덱스번호]

   5번재 쿼리는.. 글에 인덱스(가칭)라는 필드를 하나 더 추가 해서.. 글 기본 2000개마다 (가변적입니다)
   인덱스를 증가 시켰습니다. 즉 2000개를 하나의 묶음으로 만든것입니다.
   그 인덱스를 기준으로 처리를 해주었습니다.

ex)

SELECT TOP 10 FileGuid, FileName, Capacity, RegDate 
FROM TB_AttachFile 
WHERE FileGuid <=
(
 SELECT MIN(FileGuid)
 FROM 
 (
  SELECT TOP 11 FileGuid -- (현재페이지수-1) * 10 + 1  
  FROM TB_AttachFile
  WHERE IDX_FileGuid > 350  -- 350은 이전 페이지의 끝 게시물 번호. (350보다 큰 10개를 얻음.)
  ORDER BY FileGuid DESC
 )AS A 
) 
ORDER BY FileGuid DESC



결과.

게시물은 100만개를 넣고 테스트를 했습니다
서버정보 : CPU : p4-1.8, RAM : 768Mb, 컴팩 프리자리오 2820AP, 환경 : 윈도우2003 MSSQL2000
처음페이지(1), 마지막 페이지(50000) 처리 시간이 아래와 같습니다.(단위 ms)

 첫페이지 실행    끝페이지 실행
1 :  273                 11476.56
2 :  289                 4406.25
3 :  289                 2695.31
4 :  289                 1218.75
5 :  7.81                23.44

<출처> http://tit99hds.egloos.com/888201

저작자표시 비영리 변경금지

'Brain Trainning > DataBase' 카테고리의 다른 글

SQL Server Error Logs from Multiple Instances Using SSIS (펌)  (0) 2010.04.28
Managing Max Degree of Parallelism (펌)  (0) 2010.04.28
Not In Query  (0) 2010.04.15
Job List 적용  (0) 2010.04.14
Partition Table 의 구현  (0) 2009.11.30
posted by LifeisSimple
prev 1 next

티스토리툴바