'Transact-SQL'에 해당되는 글 32건

  1. index의 생성일을 알 수 있을까?
  2. 최근 통계 일자 DMV
  3. 장비 이동시 로그인 계정 SID값이 틀릴 경우~
  4. Table을 참조하는 object를 syscomments에서 DMV로 바꾸어 보아요
  5. xp_fixeddrives에서 sys.dm_os_volume_stats으로 바꾸어 보아요~~
  6. 프로시져 입력 매개변수 정보 보기
  7. 파싱? ...컴마로 자르기 응용편…
  8. 사용하지 않은 프로시져 찾기
  9. Plan cache, adhoc single-use plan cache 지우고 싶을 때~~
  10. SQL json 쿼리


index의 생성일을 알 수가 있을까?? 결론은 pk 인덱스를 제외한 생성일, 수정일은 알 수 없다.  
만약 있다면 공유해주세요




use test_db3
go


--Read more at http://www.sqlpanda.com/2013/10/how-to-check-index-creation-date.html#xxYFSV24Fy1YhwBX.99
SELECT 
	object_name(i.object_id) as TableName, i.object_id, i.name, i.type_desc, i.is_disabled
	, o.create_date																-- 테이블 생성일
	, o.modify_date																-- 테이블 수정일
	, c.type_desc AS obj_desc
	, c.create_date AS obj_create_date
	, c.modify_date AS obj_modify_date
FROM   sys.indexes i
        INNER JOIN sys.objects o 
			ON i.object_id = o.object_id
		LEFT OUTER JOIN sys.objects c
			on i.name = c.name
WHERE o.type NOT IN ('S', 'IT')
	and o.is_ms_shipped = 0 
	and i.name is not null
ORDER BY create_date DESC

-------------------------------------------------------------------------------------------
-- test case setup
-------------------------------------------------------------------------------------------

-- index 없는 테이블
create table dbo.case1_Test
(
	a int not null,
	b int,
	c int
)
go

-- primary key + clustered index
create table dbo.case2_Test
(
	a int not null 
		constraint idx_case2_Test primary key,
	b int,
	c int
)
go

-- primary key + nonclustered index
create table dbo.case3_Test
(
	a int not null,
	b int,
	c int

	constraint idx_case3_Test primary key nonclustered
	(
		a
	)
)
go

-- nonclustered index
create table dbo.case4_Test
(
	a int not null,
	b int,
	c int
)
go

create index idx_case4_Test on dbo.case4_Test(a)
go

-- clustered index
create table dbo.case5_Test
(
	a int not null,
	b int,
	c int
)
go

create clustered index idx_case5_Test on dbo.case4_Test(a)
go


-------------------------------------------------------------------------------------------
-- 초기
-------------------------------------------------------------------------------------------

-- 위의 쿼리에서 보면 PK 속성일 경우만 생성, 수정일을 알 수가 있다.  이외에는 테이블 생성/수정만 알 수가 있음 
--TableName	object_id	name	type_desc	is_disabled	create_date	modify_date	obj_desc	obj_create_date	obj_modify_date
--case4_Test	1077578877	idx_case5_Test	CLUSTERED	0	2013-10-29 11:16:23.777	2013-10-29 11:28:15.087	NULL	NULL	NULL
--case4_Test	1077578877	idx_case4_Test	NONCLUSTERED	0	2013-10-29 11:16:23.777	2013-10-29 11:28:15.087	NULL	NULL	NULL
--case3_Test	1029578706	idx_case3_Test	NONCLUSTERED	0	2013-10-29 11:12:22.380	2013-10-29 11:12:22.380	PRIMARY_KEY_CONSTRAINT	2013-10-29 11:12:22.380	2013-10-29 11:12:22.380
--case2_Test	981578535	idx_case2_Test	CLUSTERED	0	2013-10-29 11:11:41.153	2013-10-29 11:11:41.153	PRIMARY_KEY_CONSTRAINT	2013-10-29 11:11:41.153	2013-10-29 11:11:41.153


-- case1_Test 테이블에 pk 속성을 추가해 보자. case1 table은 테이블에 인덱스와 PK가 없는 테이블이다.
alter table dbo.case1_Test
	add constraint idx_case1_Test primary key (a)
go

-- 테이블 생성일과 PK 속성일의 경우 생성일과 수정일 추가 했을 경우 확인이 가능하다. 추가적으로 table의 modify_date가 변경된 것을 확인 할 수 있다. 
--TableName	object_id	name	type_desc	is_disabled	create_date	modify_date	obj_desc	obj_create_date	obj_modify_date
--case1_Test	1109578991	idx_case1_Test	CLUSTERED	0	2013-10-29 11:32:37.010	2013-10-29 11:32:39.790	PRIMARY_KEY_CONSTRAINT	2013-10-29 11:32:39.787	2013-10-29 11:32:39.787	 

-- 삭제
alter table dbo.case1_Test
	drop constraint idx_case1_Test 
go

select *
from sys.objects
where name = 'case1_Test'

-- PK 속성을 제거 했을 경우 테이블 modify_date값이 수정된다.  ( modify_date msdn : ALTER 문을 사용하여 개체를 마지막으로 수정한 날짜입니다. )
--name	object_id	principal_id	schema_id	parent_object_id	type	type_desc	create_date	modify_date	is_ms_shipped	is_published	is_schema_published
--case1_Test	1109578991	NULL	1	0	U 	USER_TABLE	2013-10-29 11:32:37.010	2013-10-29 11:34:58.560	0	0	0

create index idx_case1_Test on dbo.case1_Test( a)

select *
from sys.objects
where name = 'case1_Test'

-- 인덱스를 생성했을 경우에도 table object의 modify_date 값이 변경된다. 인덱스 생성일을 알 수 있는 방법은 없어 보인다.
--TableName	object_id	name	type_desc	is_disabled	create_date	modify_date	obj_desc	obj_create_date	obj_modify_date
--case1_Test	1109578991	idx_case1_Test	NONCLUSTERED	0	2013-10-29 11:32:37.010	2013-10-29 11:38:44.917	NULL	NULL	NULL



최근 통계 일자 DMV


http://basitaalishan.com/2013/04/15/determining-when-statistics-were-last-updated-in-sql-server/
http://technet.microsoft.com/ko-kr/library/ms177623.aspx
http://sqlblog.com/blogs/kalen_delaney/archive/2013/04/09/more-statistics-info.aspx 

SQL2012 버젼 이후부터 sys.dm_db_stats_properties 추가되었다.  이전 버젼은 STATS_DATE 함수로 사용해야 한다. 


SELECT 
	object_id
	, OBJECT_NAME(object_id) AS [ObjectName]
	, [name] AS [StatisticName]
	, STATS_DATE([object_id], [stats_id]) AS [StatisticUpdateDate]
FROM sys.stats
WHERE object_id > 100
ORDER BY STATS_DATE([object_id], [stats_id]) DESC



주로 장비 이동시 로그인 계정 SID값이 틀릴 경우 로그인 안 되는 경우. 아래와 같이 로그인 계정 생성할 때 기존 서버의 SID값으로 계정을 생성할 수 있다.

http://www.sqlskills.com/blogs/glenn/how-to-avoid-orphaned-database-users-with-sql-server-authentication


방법 1)

-- Use the sid from the old server instance 
CREATE LOGIN SQLAppUser WITH PASSWORD = N'YourStrongPassword#', sid = 0x2F5B769F543973419BCEF78DE9FC1A64,
DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO 

방법2)

내가 주로 사용하는 방법인데, 사용자 정보를 매핑(Microsoft SQL Server의 이후 버전에서는 이 기능이 제거됩니다)


select 'exec sp_change_users_login ''Update_One'', ''' + name + ''','''  + name + '''' from sysusers
where uid < 10000 and  uid > 2


변경 방법>>

select 

'ALTER USER ' + name + ' with login = ' + name 

from sys.sysusers



sys.dm_sql_referencing_entities DMV는 지정된 엔터티를 참조하는 현재 데이터베이스에 있는 다음과 같은 엔터티 유형의 행을 반환합니다 

참고 링크

   

사용가능 버전 SQL2008, SQL2008R2+, SQL2012   

지정된 엔터티를 참조하는 현재 데이터베이스에 있는 다음과 같은 엔터티 유형을 알 수 있습니다.

  • 스키마 바운드 또는 비스키마 바운드 엔터티
  • 데이터베이스 수준 DDL 트리거
  • 서버 수준 DDL 트리거

   

이 DMV는 언제 사용할까요? 사실 오늘 저도 처음 사용해 보았어요. 테이블 이름을 변경했지만 관련된 프로시져나 뷰가 어디서 사용하는지 찾는 방법을 이전에는 syscomments라는 것을 사용했습니다. 상세 정보는 여기를 참고하세요. SQL2008 이후 버전에서는 sys.dm_sql_referencing_entities DMV를 사용하면 됩니다.   

   

테스트 용 table, procedure 생성

CREATE TABLE [dbo].[tbl1](
	[Name] [char](10) NOT NULL,
	[Param] [float] NOT NULL
)
GO

create index idx_tbl1_name on dbo.tbl1 ( name );


CREATE TABLE [dbo].[tbl2](
	[name] [char](10) NOT NULL,
	[upper] [varchar](256) NOT NULL
)
GO

CREATE PROCEDURE dbo.UspGetParam
AS	
	SELECT 
	*
	FROM [dbo].[tbl1]
GO
CREATE PROCEDURE dbo.UspGetREParam
AS	
	SELECT 
	*
	FROM [dbo].[tbl2]
GO

   

SQL2000 버전

-- SQL 2000
-- 뷰, 규칙, 기본값, 트리거, CHECK 제약 조건, DEFAULT 제약 조건 및 저장 프로시저에 대한 항목
SELECT A.name ProcedureName
FROM sysobjects A, syscomments B
WHERE A.id = B.ID
	AND B.text LIKE '%' + @objNm + '%';	

   

SQL2008+ 버전

-- SQL 2008+
-- 위의 내용 포함 데이타베이스 수준 DLL 트리거, 서버 수준의 DLL 트리거
with cte_object
as
(
	select 
		objectnm = t2.name + '.' + t1.name, type_desc
	from sys.tables t1
		inner join sys.schemas t2
			ON t1.schema_id = t2.schema_id
	where t1.name LIKE @objNm + '%'
) 
select *
from cte_object t1
	cross apply sys.dm_sql_referencing_entities(t1.objectnm, 'OBJECT') t2 ;
Go

데이타베이스 공간 확인

http://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=KO-KR&k=k(SYS.DM_OS_VOLUME_STATS_TSQL);k(SQL11.SWB.TSQLRESULTS.F1);k(SQL11.SWB.TSQLQUERY.F1);k(DevLang-TSQL)&rd=true

   

원본 문서 : http://sqlblog.com/blogs/tamarick_hill/archive/2013/07/15/july-the-31-days-of-sql-server-dmo-s-day-15-sys-dm-os-volume-stats.aspx

   

기존에는 xp_fixeddrives 확장 저장프로시져를 이용해서 디스크 드라이브의 볼륨 정보를 반환할 수가 있었는데, 디스크 드라이브 문자에 대한 문제는 알파벳 문자의 수에 의해 제한된다는 것입니다. 데이터베이스 서버가 드라이브 문자 대신 마운트 포인트를 사용할 경우, 그 시점에서 당신에게 쓸모 없게 된다.

   

사용가능 버전 SQL2008R2+, SQL2012

SQL Server 2012에서 지정된 데이터베이스와 파일이 저장된 운영 체제 볼륨(디렉터리) 대한 정보를 반환합니다 동적 관리 함수를 사용하여 물리적 디스크 드라이브의 특성을 확인하거나 디렉터리에 대한 사용 가능한 공간 정보를 반환할 있습니다.

   

데이타베이스 파일이 저장된 운영체제 볼륨의 크기와 여유 공간 반환

SELECT
	volume_mount_point, MAX(total_bytes) total_bytes, MAX(available_bytes) available_bytes
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)
GROUP BY volume_mount_point

 

SQL Server 인스턴스의 모든 데이터베이스 파일에 대해 전체 공간과 사용 가능한 공간(바이트)을 반환합니다.

SELECT f.database_id, f.file_id, volume_mount_point, total_bytes, available_bytes
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)

sp_procedure_params_rowset 지정된 저장 프로시저의 매개변수 정보를 클라이언트 측 데이터베이스 API(ADO.NET등)로 전달해서 해당 프로시저의 Parameter 개체가자동 생성되도록 도와주는 용도 

   


추가적으로 SQL2014에 추가된 dmv로 반환되는 결과 정보도 확인할 수 있다. (쥑이네~~)
http://msdn.microsoft.com/ko-kr/library/Ff878258(v=sql.110).aspx

간단하게 XML으로 형 변환하여 콤마 구분하는 방법입니다. 르메님의 문자열 분리부분을 이용해서 만들었어요^^.

   

쿼리

-- 원본 링크 http://purumae.tistory.com/178
DECLARE
	  @vchString varchar(max),
	  @vchDelimiter varchar(10);

-- 콤마로 구분된 문자열
SELECT @vchString = '메롱1, 메롱2, 메롱3',	
		@vchDelimiter = ',';

DECLARE @xmlString xml,
			@xmlSQL VARCHAR(MAX);
 
SET @xmlSQL = N'';
SELECT @xmlString = CAST(@xmlSQL AS XML);

-- XML DATA
SELECT @xmlString;

-- 구분할 수 있는 문자열이 정해질 경우 아래의 방법은 어떠한쥐~?
SELECT 
	@xmlString.value('(Property/X/@n)[1]', 'varchar(8000)') AS n1	-- data type에 맞게 varchar(8000)을 수정 한다^^
	,@xmlString.value('(Property/X/@n)[2]', 'varchar(8000)') AS n2
	,@xmlString.value('(Property/X/@n)[3]', 'varchar(8000)') AS n3

 에러 발생하면 첨부 파일 다운로드^^

string split.sql

  

결과


 xml을 으용해서 로우와 컬럼구분(이게 더 좋아 보인다 ㅠㅠㅠ)
http://jingyangli.wordpress.com/2012/10/18/split-string-with-multiple-delimiters-t-sql-xml-method/


DB 리뉴얼 할 일이 있어서 SP 사용하지 않은 프로시져들이 먼가~ 찾는 DMV를 이용해서 쿼리를 만들어 보았습니다.

 

Script

WITH exec_procedure_stats
AS
(
	SELECT 
		object_id
		, database_id = MAX(database_id)
		, execution_count = SUM(execution_count) 
		, min_elapsed_time = SUM(min_elapsed_time) 
		, total_elapsed_time = SUM(total_elapsed_time) 
		, max_elapsed_time = SUM(max_elapsed_time) 
		, last_elapsed_time = SUM(last_elapsed_time) 
		, cached_time = MAX(cached_time) 
	FROM sys.dm_exec_procedure_stats
	GROUP BY object_id
)
SELECT 
	[DB Name] = DB_NAME(),
	[SP Name] = sc.name + '.'  + p.name, 
	[ISDELETE] = CASE WHEN qs.execution_count IS NULL THEN 1 ELSE 0 END,
	qs.execution_count, qs.min_elapsed_time,
	qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
	qs.max_elapsed_time, qs.last_elapsed_time,  qs.cached_time	
FROM sys.procedures AS p 
	LEFT OUTER JOIN exec_procedure_stats AS qs
		ON p.[object_id] = qs.[object_id]
			AND qs.database_id = DB_ID()
	INNER JOIN sys.schemas AS sc
		ON p.schema_id = sc.schema_id
WHERE p.is_ms_shipped = 0
ORDER BY sc.name ASC, p.name ASC
OPTION (RECOMPILE);
GO

결과

 

원본 링크

http://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/

http://www.sqlservercentral.com/blogs/james-sql-footprint/2012/04/06/clean-sql-server-cache/

http://blog.naver.com/PostView.nhn?blogId=hrk007&logNo=60124193909

http://hyoksong.tistory.com/entry/%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80-%EC%BA%90%EC%8B%9C%EC%9D%98-%ED%8A%B9%EC%A0%95-%ED%83%80%EC%9E%85%EB%A7%8CSQLCP-%EC%B4%88%EA%B8%B0%ED%99%94-%EC%8B%9C%ED%82%A4%EC%9E%90

http://www.sqlservercentral.com/blogs/glennberry/2009/12/28/fun-with-dbcc-freeproccache/

http://rschandrastechblog.blogspot.kr/2011/06/how-to-clear-sql-server-cache.html

 

 

ALL Cache 지우기       

 

DBCC FREEPROCCACHE

 

remove unused entries from all caches        

DBCC FREESYSTEMCACHE ('ALL','default');

 

SQL Plans 지우기

 

DBCC FREESYSTEMCACHE('SQL Plans')

 

select objtype,

count(*) as number_of_plans,

sum(cast(size_in_bytes as bigint))/1024/1024 as size_in_MBs,

avg(usecounts) as avg_use_count

from sys.dm_exec_cached_plans

group by objtype        

 

 

 

원하는 캐쉬 항목 찾아 지우기 (By 인옥형)

 

-- 문제되는 항목 찾기        

SELECT

TOP(20) [TYPE], [NAME], SUM(PAGES_KB) AS [SPA MEM, KB]

FROM SYS.DM_OS_MEMORY_CLERKS

GROUP BY [TYPE],name

ORDER BY SUM(PAGES_KB) DESC;

 

-- 지우고 싶은 타입

DBCC FREESYSTEMCACHE ('Lock Manager : Node 0');        

 

DBCC FREESYSTEMCACHE('Object Plans') -- stored procedure 계획

DBCC FREESYSTEMCACHE('SQL Plans') -- prepared, ad-hoc 계획

 

BufferPool 지우기        

checkpoint

DBCC DROPCLEANBUFFERS

 

Procedure cache        

DBCC FREEPROCCACHE                                                -- Removes all elements from the plan cache

 

-- Example 3 (Scalpel)

-- Remove one plan from the cache

-- Get the plan handle for a cached plan

SELECT cp.plan_handle, st.[text]

FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE [text] LIKE N'%/* GetOnlineSearchResultsMonday %';

 

-- Remove the specific plan from the cache using the plan handle

DBCC FREEPROCCACHE (0x05000800F7BA926C40C15055070000000000000000000000);

 

DBCC FLUSHPROCINDB (@intDBID);                -- Flush the procedure cache for one database only

 

예)  http://www.sqlservercentral.com/blogs/glennberry/2009/12/28/fun-with-dbcc-freeproccache/

DECLARE @intDBID INT;

SET @intDBID = (SELECT [dbid]

FROM master.dbo.sysdatabases

WHERE name = 'AdventureWorks');

 

-- Flush the procedure cache for one database only

DBCC FLUSHPROCINDB (@intDBID);

 

DBCC FREESESSIONCACHE                                        -- Flushes the distributed query connection cache



--------------------------------------------------------------------------------

--------------------------------------------------------------------------------


1. Normally, use following two when you do not want query compilation also to come into picture. 

CHECKPOINT

DBCC DROPCLEANBUFFERS 


2. When you want query compilation also to come into picture,  free the proccache also : 

CHECKPOINT

DBCC DROPCLEANBUFFERS 

DBCC FREEPROCCACHE


3. The most comprehensive cleaning strategy would be : 

DBCC FREESYSTEMCACHE(All)

DBCC FREESESSIONCACHE

DBCC FREEPROCCACHE

DBCC FLUSHPROCINDB( db_id )

CHECKPOINT

DBCC DROPCLEANBUFFERS


SQL json 쿼리

https://www.simple-talk.com/blogs/2013/03/26/sql-server-json-to-table-and-table-to-json/

정말 이분은 대단하신 분이야 @.@