Bora.Choi

 January 2, 2008
New datetime datatypes in SQL Server 2008
By Muthusamy Anantha Kumar aka The MAK

From the beginning, Microsoft SQL Server DBAs have longed for two different datatypes, where you could store time and date separately. The long wait is over. In all of the older versions, we had only one data type that could store Datetime datatype, and it stored both the value of the date and the value of the time.

Finally, in Microsoft SQL Server 2008, Microsoft is introducing a set of new datatypes for storing date, time and both date and time together. The new data types store more data as well, which means you can store dates anywhere from 01-01-01 to 9999-12-31 and also store time up to the fraction of 9999999.

This article illustrates the usage and functionality of different datatypes in Microsoft SQL Server 2008, namely date, time, datetime, datetime2 and datetimeoffset datatypes.

Note: This article is written based on the Microsoft SQL Server 2008 – Nov CTP

TIME

The datatype TIME is primarily used for storing the time of a day. This includes Hours, minutes, Seconds etc. It is based on a 24-hour clock. The datatype TIME can store seconds up to the fraction of 9999999.

Let us declare a variable with the datatype TIME and store some data.

declare @mytime TIME
set @mytime = GETDATE()
select MyTime = @mytime

The result is shown below. [Refer Fig 1.0]

MyTime
01:48:18.4870000

When you declare a variable with datatype TIME with no precision, SQL Server assumes a 7 digit precision as shown above.

The time range is from 00:00:00 through 23:59:59.9999999.

We could limit the precision of the datatype as shown below.

declare @mytime TIME(3)
set @mytime = GETDATE()
select MyTime = @mytime 

The result is shown below. [Refer Fig 1.2]

Result

MyTime
01:51:25.580

DATE

The datatype DATE is used for storing the date only. This includes Month, Day and year.

The value of date can be anywhere from 01-01-01 to 9999-12-31.

Let’s declare a variable with the datatype DATE and store some data.

declare @mydate DATE
set @mydate = GETDATE()
Select Mydate = @MyDate
set @mydate = '01/01/1997'
Select Mydate = @MyDate
set @mydate = '01/31/9999'
Select Mydate = @MyDate
set @mydate = '01/01/0001'
Select Mydate = @MyDate

The range for date is from 0001-01-01 through 9999-12-31

The result is shown below. [Refer Fig 1.2]

Result

MyDate
2007-12-18
 
MyDate
1997-01-01
 
MyDate
9999-01-31
 
MyDate
0001-01-01

DATETIME2

The data type DATETIME2 is the combination of the datatype DATE and TIME. DATETIME2 is used to store both a date value ranging from 01-01-01 to 9999-12-31 as well as a time value up to the fraction of 9999999.

Let’s declare a variable with the datatype DATETIME2 and store some data as shown below.

declare @mydate DATETIME2
set @mydate = GETDATE()
Select Mydate = @MyDate
set @mydate = convert(datetime2,'01/01/1997 16:14:00.1234567')
Select Mydate = @MyDate
set @mydate = convert(datetime2,'01/01/0001 16:14:00.1234567')
Select Mydate = @MyDate
The range for DATETIME2 is from 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999

The result is shown below. [Refer Fig 1.3]

Result

MyDate
2007-12-18 02:11:45.3130000
 
MyDate
1997-01-01 16:14:00.1234567
 
MyDate
0001-01-01 16:14:00.1234567

We could limit the precision as shown below.

Declare @mydate DATETIME2(4)
set @mydate = GETDATE()
Select Mydate = @MyDate
set @mydate = convert(datetime2(4),'01/01/1997 16:14:00.1234567')
Select Mydate = @MyDate

The result is shown below. [Refer Fig 1.2]

Result

MyDate
2007-12-18 02:14:54.8130
 
MyDate
1997-01-01 16:14:00.1235

SMALLDATETIME and DATETIME

Microsoft SQL Server 2008 continues to support existing data types such as datetime and smalldatetime.

The range for the datatype smalldatetime is from 1900-01-01 through 2079-06-06. Execute the following query as shown.

Declare @Mydate datetime
Set @MyDate = getdate()
Select MyDate = @MyDate

The result is shown below. [Refer Fig 1.4]

Result

MyDate
2007-12-18 02:31:35.347

The range for the datatype datetime is from 1753-01-01 through 9999-12-31.

Execute the following query as shown.

Declare @Mydate smalldatetime
Set @MyDate = getdate()
Select MyDate = @MyDate

The result is shown below. [Refer Fig 1.5]

Result

MyDate
2007-12-18 02:34:00.000

DATETIMEOFFSET

Microsoft SQL Server 2008 introduces a new datetime datatype called datetimeoffset. Datetimeoffset actually defines the date with the combination of the time of a day that is timezone aware. In addition, the clock is a 24-hour cycle.

The timezone offset range is from -14:00 through +14:00

Execute the query below to get the timeoffset.

Select 
CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS 'datetimeoffset'

The result is shown below. [Refer Fig 1.6]

Result

datetimeoffset
2007-05-08 12:35:29.1234567 +12:15

You can use the convert and cast functions to convert to and from different datetime datatypes.

Example:

Declare @mydate DATETIME2(4)
set @mydate = GETDATE()
select @mydate as OriginalDate
select CONVERT (smalldatetime,@mydate ) as Smalldate
select CONVERT (time,@mydate ) as Justtime

Result

OriginalDate
2007-12-19 02:15:09.3130
 
Smalldate
2007-12-19 02:15:00.000
 
Justtime
02:15:09.3130000

Note: This article is written based on the Microsoft SQL Server 2008 – Nov CTP.

Conclusion

This article has illustrated the usage and function of the various datatypes related to Date and time, namely date, time, datetime, datetime2 and datetimeoffset.

이 글은 스프링노트에서 작성되었습니다.

Using DMO to Restore a Database

http://www.sqlservercentral.com/columnists/awarren/sqldmorestore.asp

이 글은 스프링노트에서 작성되었습니다.

Procedure Cache

SQL 서버는 두가지 타입의 cache에 메모리를 할당한다.

1. procedure cache
2. buffer cache

procedure cache는 실행한 stored procedure의 쿼리 실행 계획이 저장되는 공간이고,
buffer cache는 디스크로부터 읽어 들인 데이터를 저장하는 공간이다.

그런데 SQL Server를 세팅하고 관리할 때 SQL Server 인스턴스에 할당할 전체 메모리의 크기는 설정할 수 있지만, 위 두가지 메모리 영역 별로 메모리를 할당 하는 방법 및 수단은 존재하지 않는다.
(심지어 두 영역에 어떤 비율이나 공식이나 방법으로 메모리가 할당되는지에 대한 문서조차 없다.)

각설하고...

SQL Server가 메모리를 할당하는 방식은... procedure cache에 할당할 메모리 크기를 서버환경으로부터 일정 공식을 사용하여 계산하여 할당하고, 나머지 공간을 buffer cache에 할당하는 방식이다.

procedure cache 크기를 계산 방법은 아래와 같다.

- 32bit 플랫폼
procedure cache는 AWE (Address Windowing Extension)영역에 존재할 수 없다.
따라서 SQL인스턴스에 할당된 메모리의 첫 2GB까지의 영역만을 사용할 수 있는데, 이 영역의 50%와 1GB 중 작은 양이 procedure cache로  할당된다.

- 64bit 플랫폼
SQL Server 2005 SP1 또는 그 이전인 경우, "SQL 인스턴스에 할당된 첫 8GB까지의 75% + 이후 56GB까지의 50% + 나머지 메모리의 25%"를 procedure cache에 할당한다.
예를 들어 시스템 메모리가 16GB이고 SQL 인스턴스에 할당된 메모리 15GB인 경우, procedure cache의 크기는 9.5GB (8GB * 0.75 + 7GB * 0.5)가 된다.

단, SQL Server 2005 SP2에서 이 계산 방식이 "SQL 인스턴스에 할당된 첫 4GB까지의 75% + 나머지 메모리의 10%"로 바뀌어, 위의 예와 같은 상황에서 procedure cache의 크기는 4.1GB (4GB * 0.75 + 11GB * 0.1)가 된다.

(출처 : http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1316780,00.html

http://purumae.tistory.com/26)

이 글은 스프링노트에서 작성되었습니다.

DB를 설치하고 나서 collation 을 잘 못 생성하면, 골치가 아프게 됩니다. 이미 서비스이전이면 다시 설치하면 되겠지만

그렇지 못한 사정일 때  master을 rebuild 하게 되면 model이랑 tempdb의 collation도 바뀌게 됩니다.

예전에 시도해봤었는데 그대는 이번 처럼 체계적인 설명이 없었습니다. 단일모드로 접속해서 master db를 리빌드 하는 명령을 해봤지만 에러가 발생하고 적용이 되지 않더군요.

왠지 이번건 체계적인 것 같네요. 그때는 msdn의 support 였는데.

Part1 : http://www.mssqltips.com/tip.asp?tip=1531 : 하기 전 사전 작업입니다.

-      각 DB의 Attach, Detach 스크립트를  쉽게 만들 수 있고

-      사용자의 로그인 정보를 한번에 만들어 주네요. – 장비 이전 시 사용하면 좋을 것 같습니다.

n  http://support.microsoft.com/kb/918992 : 여기서 필요한 프로시저를 master에 생성하고  exec dbo.sp_help_revlogin 이용

part 2: http://www.mssqltips.com/tip.asp?tip=1528 : master를 리빌드 하는 명령어와 방법, 리빌드 후 백업 해야 하는 점

part 3: http://www.mssqltips.com/tip.asp?tip=1533 : 하고 나서 part1에서 생성된 자료를 실행합니다.

이 글은 스프링노트에서 작성되었습니다.

 Profiler

•  서버 및 데이터베이스 동작 모니터, 교착상태수, 치명적인 오류, 저자으로시저 및 Transact-SQL  문 추적, 로그인 동작 확인
• SQL 프로필러 데이터를 SQL Server 테이블이나 파일에 캡처해서 분석가능
• 캡처한 이벤트를 단계별로 재성
•  SQL 프로필러는 일괄처리나 트랜잭션 시작과 같은 엔진 프로세스 이벤트를 추적할 수 있다.

템플릿

• 나만의 템플릿을 만들어서 필요한 부분만 보자.
• 이벤트 에서는 보안 감사/세션 정보는 제외해도 무관
• 경고>> Execution Warnigs 는 성능에 나쁜 영향을 줄때 프로시저 내에서 발생하는 모든 에러를 반환한다. 한번씩 잡아 보는것도 괜찮다.
• 기본1.tdf : 기본에 충실한 추적 템플릿

이벤트 범주

• 저장프로시저 범주 : 저장프로시저 생성으로 발생하는 이벤트 컬랙션
  •  이벤트 클래스와 열의 상관 관계, 해당 이벤트를 가져와야 특정 정보가 발생한다.
  • 

• TSQL: 클라이언트로부터 SQL Server 인스턴스로 전달되는 Transact-SQL 문을 실행함으로 생성되는 이벤트 클래스 컬렉션입니다.

• 참고) 둘다 Batch가 아니라 단건을 해야 NestLevel, ObejctID를 가져올 수 있다.  이게 Read80Trace에서 있어야 분석이 가능한것 같음.
• 잠금: 잠금을 얻거나 취소, 해제 시 생성되는 이벤트 클래스의 컬렉션입니다.
  •  잠금은 자주 발생하기 때문에 잠금 이벤트를 캡쳐하면 추적되는 서비스에 중대한 오버해드를 발생 할 수 있다.
  • 

Profiler 바로가기 키

파일/테이블 저장

추적파일을 테이블에 저장할 수 있다. 파일로 저장해서 쿼리로 문제가 되는 부분을 필터링해서 개체를 찾는데 도움이 된다.

• 분석하기

Profiler 스크립트로 실행하기

• 프로필러를 스크립트로 실행할 수 있다. UI로 실행하게 되면 아무래도 부하가 걸린다.
• 필요한 이벤트, 컬럼을 지정해서 가능하다. 쉽게 하기 위해서는 자주 쓰는 템플릿을 불러와서 파일>>추적스크립트를 실행해서 변환을 하면 스크립트가 생성된다.
• 추적은 다음과 같은 순서로 실행하면 된다.  
  • sp_trace_create: Trace를 생성한다.
  • sp_trace_setevent: 해당 Trace에 이벤트와 컬럼 명을 지정한다.
  • sp_trace_setfilter: 필터를 지정해서 원하는 값만 볼수 있다.
• 추적이 실행되고 나서 상태확인/중지 
  • fn_trace_getfilterinfo : TraceId 정보를 확인할 수 있음
  •  sp_trace_status @TraceId, 0 : 추적중지 ->   sp_trace_status @TraceId, 2 : 추적 삭제
•   스크립트 저장프로시저로 만들기 
  • sp_start_trace.sql
  • sp_stop_trace.sql

이 글은 스프링노트에서 작성되었습니다.

 sp_MSforeachtable

Overview

Microsoft provides two undocumented Stored Procedures that allow you to process through all tables in a database, or all databases in a SQL Server instance. The first Stored Procedure (SP), "sp_MSforeachtable," allows you to easily process some code against every table in a single database. The other SP, "sp_MSforeachdb," will execute a T-SQL statement against every database associated with the current SQL Server instance. Let me go through each of these Stored Procedures in a little more detail.

sp_MSforeachtable

The "sp_MSforeachtable" SP comes with SQL Server, but it is not documented in Books Online. This SP can be found in the "master" database and is used to process a single T-SQL command or a number of different T-SQL commands against every table in a given database. To demonstrate how this SP works and how I think it is simpler to use then coding a CURSOR let me go through an example.

Say I want to build a temporary table that will contain a series of records; one for each table in the database and where each row contains the table name and the row count for the given table. To do this you would want to run a command like, "select '<mytable>', count(*) from <mytable>" where "<mytable>" was replaced with every table in your database and insert the results into my temporary table. So now, let's look at how we might do this using a CURSOR and then using the undocumented SP "sp_MSforeachtable".

Here is my code for getting the row counts for each table in a database using a CURSOR:

use pubs
go
set nocount on 
declare @cnt int
declare @table varchar(128)
declare @cmd varchar(500) 
create table #rowcount (tablename varchar(128), rowcnt int)
declare tables cursor for
select table_name from information_schema.tables
   where table_type = 'base table'
open tables
fetch next from tables into @table
while @@fetch_status = 0
begin
  set @cmd = 'select ''' + @table + ''', count(*) from ' + @table
  insert into #rowcount exec (@cmd)
  fetch next from tables into @table
end
CLOSE tables 
DEALLOCATE tables
select top 5 * from #rowcount
    order by tablename
drop table #rowcount

Here is the output of my CURSOR example when the above code in run on my machine:

tablename       rowcnt      
-------------   ----------- 
authors         23
discounts       3
employee        43
jobs            8
pub_info        8

Now here is my code that produces similar results using the undocumented SP "sp_MSforeachtable":

use pubs
go 
create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable 
   'insert into #rowcount select ''?'', count(*) from ?'
select top 5 * from #rowcount
    order by tablename
drop table #rowcount

Here is the output from the above code when run on my machine:

tablename         rowcnt      
----------------- -----------
[dbo].[authors]   23
[dbo].[discounts  3
[dbo].[employee]  43
[dbo].[jobs]      14
[dbo].[pub_info]  8

As you can see both the CURSOR example and the "sp_MSforeachtable" code produce relatively the same results. Which one do you think is easier to read and code? Let's look a little closer at how to use the undocumented SP "sp_MSforeachtable".

Below is the syntax for calling the sp_MSforeachtable SP:

exec @RETURN_VALUE=sp_MSforeachtable @command1, @replacechar, @command2, 
  @command3, @whereand, @precommand, @postcommand

Where:

• @RETURN_VALUE - is the return value which will be set by "sp_MSforeachtable"
• @command1 - is the first command to be executed by "sp_MSforeachtable" and is defined as a nvarchar(2000)
• @replacechar - is a character in the command string that will be replaced with the table name being processed (default replacechar is a "?")
• @command2 and @command3 are two additional commands that can be run for each table, where @command2 runs after @command1, and @command3 will be run after @command2
• @whereand - this parameter can be used to add additional constraints to help identify the rows in the sysobjects table that will be selected, this parameter is also a nvarchar(2000)
• @precommand - is a nvarchar(2000) parameter that specifies a command to be run prior to processing any table
• @postcommand - is also a nvarchar(2000) field used to identify a command to be run after all commands have been processed against all tables

As you can see, there are quite a few options for the "sp_MSforeachtable" SP. Let's go through a couple of different examples to explore how this SP can be used to process commands against all the tables, or only a select set of tables in a database.

First let's build on our original example above and return row counts for tables that have a name that start with a "p." To do this we are going to use the @whereand parameter. Here is the code for this example:

use pubs
go
create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable 
 @command1 = 'insert into #rowcount select ''?'', 
              count(*) from ?',
 @whereand = 'and name like ''p%'''
select top 5 * from #rowcount
    order by tablename
drop table #rowcount

On my machine, the above code produced the following output:

tablename          rowcnt      
------------------ ----------- 
[dbo].[pub_info]   8
[dbo].[publishers] 8

By reviewing the code above, you can see I am now using the @command1, and the @whereand parameter. The @whereand parameter above was used to constrain the WHERE clause and only select tables that have a table name that starts with a "p." To do this I specified "and name like ''p%''" for the @whereand parameter. If you needed to have multiple constraints like all tables that start with "p," and all the tables that start with "a," then the @whereand parameter would look like this:

and name like ''p%'' or name like ''a%''

Note, that in the @command1 string in the above example there is a "?". This "?" is the default replacement character for the table name. Now if for some reason you need to use the "?" as part of your command string then you would need to use the @replacechar parameter to specify a different replacement character. Here is another example that builds on the above example and uses the "{" as the replacement character:

create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable 
 @command1 = 'insert into #rowcount select 
             ''Is the rowcount for table {?'', 
             count(*) from {',
 @replacechar = '{',
 @whereand = 'and name like ''p%'''
select tablename as question, rowcnt from #rowcount
    order by tablename
drop table #rowcount

Here is the output from this command on my machine:

question                                         rowcnt      
------------------------------------------------ ----------- 
Is the rowcount for table [dbo].[pub_info]?      8
Is the rowcount for table [dbo].[publishers]?    8      

There are two more parameters to discuss, @percommand, and @postcommand. Here is an example that uses both of these commands:

exec sp_MSforeachtable 
 @command1 = 'print ''Processing table ?''', 
 @whereand = 'and name like ''p%''',
 @precommand = 'Print ''precommand execution '' ',
 @postcommand = 'Print ''postcommand execution '' '

Here is the output from this command when run on my machine:

precommand execution 
Processing table [dbo].[pub_info]
Processing table [dbo].[publishers]
postcommand execution

As you can see, the "PRINT" T-SQL command associated with the "@precommand" parameter was only executed once, prior to processing through the tables. Whereas, the "@postcommmand" statement was executed after all the tables where processed, and was only executed once. Using the pre and post parameters would be useful if I had some processing I wanted done prior to running a command against eachnly executed once. clause and table, and/or I needed to do some logic after all tables where processed.

sp_MSforeachdb

The SP "sp_MSforeachdb" is found in the "master" database. This SP is used to execute a single T-SQL statement, like "DBCC CHECKDB" or a number of T-SQL statements against every database defined to a SQL Server instance. Here is the syntax for calling this undocumented SP:

exec @RETURN_VALUE = sp_MSforeachdb @command1, @replacechar, 
  @command2, @command3, @precommand, @postcommand

Where:

• @RETURN_VALUE - is the return value which will be set by "sp_MSforeachdb"
• @command1 - is the first command to be executed by "sp_MSforeachdb" and is defined as nvarchar(2000)
• @replacechar - is a character in the command string that will be replaced with the table name being processed (default replacechar is a "?")
• @command2 and @command3 are two additional commands that can be run against each database
• @precommand - is a nvarchar(2000) parameter that specifies a command to be run prior to processing any database
• @postcommand - is also an nvarchar(2000) field used to identify a command to be run after all commands have been processed against all databases.

The parameters for sp_MSforeachdb are very similar to sp_MSforeachtable. Therefore, there is no need to go over some of the parameters since they provide the same functionality as I described in the sp_MSforeachtable section above.

To show you how the "sp_MSforeachdb" SP works let me go through a fairly simple example. This example will perform a database backup, then a "DBCC CHECKDB" against each database. Here is the code to perform this:

declare @cmd1 varchar(500)
declare @cmd2 varchar(500)
declare @cmd3 varchar(500)
set @cmd1 =
   'if ''?'' <> ''tempdb'' print ''*** Processing DB ? ***'''
set @cmd2 = 'if ''?'' <> ''tempdb'' backup database ? to disk=''c:\temp\?.bak'''
set @cmd3 = 'if ''?'' <> ''tempdb'' dbcc checkdb(?)'
exec sp_MSforeachdb @command1=@cmd1, 
                    @command2=@cmd2,
                    @command3=@cmd3

Here you can see that I am really processing three different commands. The first command is just a "PRINT" statement so you can easily review the output by database. Remember how "sp_MSforeachtable" SP had a parameter where you could constrain what tables where processed, there is no parameter that provides this type of functionality in the "sp_MSforeachdb" SP. Since SQL Server does not allow the "tempdb" database to be backed up I needed a way to skip that database. This is why I have used the "IF" statement in each of the commands I processed. The second command (@command2) processes the databases backup, and the last command (@command3) will run a "DBCC CHECKDB" command against all databases except the "tempdb" database.

Comments on Using Undocumented SQL Server Stored Procedures

Some level of testing and care should be taken when using undocumented code from Microsoft. Since these SP's are not documented, it means that Microsoft might change this code with any new release or patch without notifying customers. Because of this, you need to thoroughly test any code you write that uses these undocumented SPs against all new releases of SQL Server. This testing should verify that your code still functions as it did in old releases.

Conclusion

As you can see, these undocumented Stored Procedures are much easier to use than using a CURSOR. Next time you need to iteratively run the same code against all tables, or all databases consider using these undocumented Stored Procedures. Although, remember these Stored Procedures are undocumented and therefore Microsoft may change their functionality at anytime.

이 글은 스프링노트에서 작성되었습니다.

Riad 구성에 따른 성능 차이

 CREATE TABLE TestA (a int, b int)

 GO

 DECLARE @time DATETIME, @i INT

 SET @i = 1

SET @time = GETDATE()

WHILE 10000 >= @i

BEGIN

INSERT TestA (a, b) VALUES (@i, @i)

SET @i = @i + 1

END

SELECT DATEDIFF(ms, @time, GETDATE())

결과

결과를 보면, 레이드 구성은 하지 않는 것이 성능은 가장 좋습니다. 허나, 실제로는 레이드 구성을 하고 있고,

레이드 구성을 했을 경우에 적어도 트랜잭션 로그와 데이터 파일이 물리적으로 분리되어야 합니다. 부하테스트를 주고 있는 개발망 장비는 그렇지 못해서 정상적인 성능 테스트 결과를 얻을 수가 없습니다. 현재, 서비스망은 트랜잭션 로그와 데이터 파일이 분리되어 있습니다. (별차이가 없을 줄 알았는데 차이 많이 나네요.)

예를 들면) 개발망에 창고 차감의 경우 초당 50회 정도(insert/update 평균 3회) 나오는데 반해

트랜젝션 로그와 데이터 파일이 분리되어 있는 DB 장비의 경우에는 이론상으로 초당 300회가 가능한 것으로 보입니다.

이 글은 스프링노트에서 작성되었습니다.

 Collecting database usage information for free space and trending

I find it important in my environment to monitor the following database/database file level metrics:

• Server/Instance Name
• Database Name
• Database File Names (both logical and full physical path)
• File Size (In Megabytes)
• Database Status
• Recovery Mode
• Free Space (In Megabytes and Percent)

To collect this information I need to tap into the either the master.dbo.sysfiles system table in SQL 2000 or master.sys.sysfiles compatibility view in SQL 2005. I also need to make use of a few T-SQL functions at the DBA's disposal. First, let me present the query. Afterwards I'll explain the finer points.

CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB

The FreeSpaceMB calculation is simple once you understand what I explained above for the calculations associated with FileSize. To determine free space we need to know how much of the total file space is being consumed. This information is exposed via the FILEPROPERTY() function. FILEPROPERTY() expects the following parameters: file name, and property.

Expect a future tip on the various uses for FILEPROPERTY(), but for now we focus on the SpaceUsed property. This value is also stored in 8Kb pages, so the factor of 1024/8 (or 128) remains constant. Using the same basic rules we discussed above this is what the formula would look like this: Available Space in Mb = (File Size in Mb) - (Space Used in Mb). The formula in the query casts all variables as integer data types and converts the available values from pages to Mb.

I know that one of the major foundations of database normalization is not storing calculated values in a database. However, I like to be able to trend my metadata over time and with the complexity of the formulas I prefer to do all my thinking up-front. That being said, I don't just store the size information in the table and run calculations in my queries after the fact - I do my calculations directly in the query that populates the repository.

이 글은 스프링노트에서 작성되었습니다.

 DBCC 명령어, 문서와 되어 있거나 되어있지 않은것 정리

참조:  http://www.sql-server-performance.com/dbcc_commands.asp

DBCC MEMORYSTATUS

Lists a breakdown of how the SQL Server buffer cache is divided up, including buffer activity. Undocumented command, and one that may be dropped in future versions of SQL Server.

참고 자료 : http://support.microsoft.com/kb/271624

DBCC PROCCACHE

프로시저 캐시에 대한 정보를 테이블 형식으로 표시합니다.

DBCC CACHESTATS

개첵들의 현재 버퍼에 있는 캐쉬 정보, SQL 2000, 7.0 버전에 적용

1. Object Type Hit Ratio Object Count Avg Cost Avg Pages LW Object Count LW Avg Cost LW Avg Stay (ms) LW Avg Use Count
   Proc     0 0 0 0 0 0 0 0
   Prepared 0 0 0 0 0 0 0 0
   Adhoc    0 0 0 0 0 0 0 0
   ReplProc 0 0 0 0 0 0 0 0
   Trigger  0 0 0 0 0 0 0 0
   Cursor   0 0 0 0 0 0 0 0
   Exec Cxt 0 0 0 0 0 0 0 0
   View     0 0 0 0 0 0 0 0
   Default  0 0 0 0 0 0 0 0
   UsrTab   0 0 0 0 0 0 0 0
   SysTab   0 0 0 0 0 0 0 0
   Check    0 0 0 0 0 0 0 0
   Rule     0 0 0 0 0 0 0 0
   Summary  0 0 0 0 0 0 0 0

• Hit Ratio: Displays the percentage of time that this particular object was found in SQL Server's cache. The bigger this number, the better.
• Object Count: Displays the total number of objects of the specified type that are cached.
• Avg. Cost: A value used by SQL Server that measures how long it takes to compile a plan, along with the amount of memory needed by the plan. This value is used by SQL Server to determine if the plan should be cached or not.
• Avg. Pages: Measures the total number of 8K pages used, on average, for cached objects.
• LW Object Count, LW Avg Cost, WL Avg Stay, LW Ave Use: All these columns indicate how many of the specified objects have been removed from the cache by the Lazy Writer. The lower the figure, the better.

DBCC DROPCLEANBUFFERS

정확한 테스트를 할때 유용하게 사용되며 캐쉬의 버퍼를 깨끗하게 지운다. 그러나 dirty page를 처리하는것은 아니다. 단지 버퍼를 지우는 것이다.

버퍼 풀에서 빈 버퍼를 모두 제거합니다.

버퍼 풀에서 빈 버퍼를 삭제하려면 먼저 CHECKPOINT를 사용해 빈 버퍼 캐시를 만드십시오. 이 과정은 현재 데이터베이스에 대한 모든 커밋되지 않은 페이지를 디스크로 기록하고 버퍼를 비웁니다. 버퍼를 비운 후에 DBCC DROPCLEANBUFFERS 명령을 실행해 버퍼 풀에서 모든 버퍼를 제거할 수 있습니다.

 SQL 2000, 7.0 에서 사용

DBCC ERRORLOG

자주 sql 서버를 재 시작하지 않으면 서버 에러 로그 파일이 커지게 된다. 에러로그 파일 하나를 끝내고 다시 새로 시작하게 만듭니다. (순환시킴)

Job으로 작업을 걸어도 된다.  같은 작업을 하는 프로시저가 sp_cycle_errorlog 임

DBCC FLUSHPROCINDB

데이터베이스가 사용하는 특수한 저장 프로시저를 캐쉬에서 삭제한다. 모두 다 삭제하는것은 아니다.  데이터베이스 아이디를 입력해서 특정 데이터베이스만도 가능하다.

상세 내역은 How to Interact with SQL Server's Data and Procedure Cache 참조 하여 읽어본다.

DBCC INDEXDEFRAG

 인덱스 조각 모음.

온라인에서 사용가능하기 때문에 장시간의 테이블 lock을 걸지는 않는다. 해당 작업은 병렬로 처리 가능하지 않는다. 즉 한 테이블에 인덱스를 여러개 처리하라고 명령해도 한 인덱스 작업이 끝난 후에 다른 인덱스 조각모음을 실시한다.

2005에서는 ALTER INDEX 사용은 권고한다. 해당 명령어는 더 이상 사용하지 않는다.

1. DBCC INDEXDEFRAG (Database_Name, Table_Name, Index_Name)

DBCC FREEPROCCACHE

모든 프로시저를 캐쉬에서 삭제한다.  테스트 시작 전에 사용하면 유용하다.

DBCC OPENTRAN

An open transaction can leave locks open, preventing others from accessing the data they need in a database. This command is used to identify the oldest open transaction in a specific database.

1. DBCC OPENTRAN('database_name')

DBCC PAGE

Use this command to look at contents of a data page stored in SQL Server.

1. DBCC PAGE ({dbid|dbname}, pagenum [,print option] [,cache] [,logical])

DBCC PINTABLE & DBCC UNPINTABLE

By default, SQL Server automatically brings into its data cache the pages it needs to work with. These data pages will stay in the data cache until there is no room for them, and assuming they are not needed, these pages will be flushed out of the data cache onto disk. At some point in the future when SQL Server needs these data pages again, it will have to go to disk in order to read them again into the data cache for use. If SQL Server somehow had the ability to keep the data pages in the data cache all the time, then SQL Server's performance would be increased because I/O could be significantly reduced on the server.

The process of "pinning a table" is a way to tell SQL Server that we don't want it to flush out data pages for specific named tables once they are read in in the first place. This in effect keeps these database pages in the data cache all the time, which eliminates the process of SQL Server from having to read the data pages, flush them out, and reread them again when the time arrives. As you can imagine, this can reduce I/O for these pinned tables, boosting SQL Server's performance.

To pin a table, the command DBCC PINTABLE is used. For example, the script below can be run to pin a table in SQL Server:

1. DECLARE @db_id int, @tbl_id int
   USE Northwind
   SET @db_id = DB_ID('Northwind')
   SET @tbl_id = OBJECT_ID('Northwind..categories')
   DBCC PINTABLE (@db_id, @tbl_id)

이 기능은 SQL Server 버전 6.5에서 성능 개선을 위해 도입되었으나 DBCC PINTABLE은 아주 좋지 않은 부작용을 갖고 있습니다. 그 중에는 버퍼 풀 손상 가능성도 포함됩니다. DBCC PINTABLE은 더 이상 필요하지 않으며 추가적인 문제를 예방하기 위해 제거되었습니다. 이 명령의 구문은 계속 작동하지만 서버에 영향을 주지 않습니다.

DBCC REINDEX

Periodically (weekly or monthly) perform a database reorganization on all the indexes on all the tables in your database. This will rebuild the indexes so that the data is no longer fragmented. Fragmented data can cause SQL Server to perform unnecessary data reads, slowing down SQL Server's performance.

If you do a reorganization on a table with a clustered index, any non-clustered indexes on that same table will automatically be rebuilt.

Database reorganizations can be done scheduling SQLMAINT.EXE to run using the SQL Server Agent, or if by running your own custom script via the SQL Server Agent (see below).

Unfortunately, the DBCC DBREINDEX command will not automatically rebuild all of the indexes on all the tables in a database, it can only work on one table at a time. But if you run the following script, you can index all the tables in a database with ease.

1. DBCC DBREINDEX('table_name', fillfactor)

DBCC SHOWCONTIG 

인덱스에 대한 페이지 분할등 정보를 보여줌

 If the scan density is less than 75%, then you may want to reindex the tables in your database reindex 추천

1. DBCC SHOWCONTIG (Table_id, IndexID)

DBCC SHOW_STATISTICS

Used to find out the selectivity of an index. Generally speaking, the higher the selectivity of an index, the greater the likelihood it will be used by the query optimizer. You have to specify both the table name and the index name you want to find the statistics on.

1. DBCC SHOW_STATISTICS (table_name, index_name)

DBCC SQLMGRSTATS

Used to produce three different values that can sometimes be useful when you want to find out how well caching is being performed on ad-hoc and prepared Transact-SQL statements.

• Memory Used (8k Pages): If the amount of memory pages is very large, this may be an indication that some user connection is preparing many Transact-SQL statements, but it not un-preparing them.
• Number CSql Objects: Measures the total number of cached Transact-SQL statements.
• Number False Hits: Sometimes, false hits occur when SQL Server goes to match pre-existing cached Transact-SQL statements. Ideally, this figure should be as low as possible.

DBCC SQLPERF()

This command includes both documented and undocumented options. Let's take a look at all of them and see what they do.

DBCC UPDATEUSAGE

The official use for this command is to report and correct inaccuracies in the sysindexes table, which may result in incorrect space usage reports. Apparently, it can also fix the problem of unreclaimed data pages in SQL Server. You may want to consider running this command periodically to clean up potential problems. This command can take some time to run, and you want to run it during off times because it will negatively affect SQL Server's performance when running. When you run this command, you must specify the name of the database that you want affected.

DBCC UPDATEUSAGE는 테이블 또는 인덱스의 각 파티션에 대해 행, 사용된 페이지, 예약된 페이지, 리프 페이지 및 데이터 페이지의 개수를 수정합니다. 시스템 테이블에 부정확한 데이터가 없으면 DBCC UPDATEUSAGE는 데이터를 반환하지 않습니다. 부정확한 데이터를 검색 및 수정하고 WITH NO_INFOMSGS를 사용하지 않았으면 DBCC UPDATEUSAGE는 시스템 테이블에서 업데이트 중인 행과 열을 반환합니다.

1. DBCC UPDATEUSAGE ('databasename')

 DBCC TRACESTATUS

 추적 플래그 상태를 표시합니다.

이 글은 스프링노트에서 작성되었습니다.

Microsoft Windows PowerShell and SQL Server 2005 WMI Providers - Part 2

이 글은 스프링노트에서 작성되었습니다.

Microsoft Windows PowerShell and SQL Server 2005 WMI Providers - Part 1
 

이 글은 스프링노트에서 작성되었습니다.

 서버 수준 ID 번호

데이터베이스 수준 ID 번호

이 글은 스프링노트에서 작성되었습니다.