SQL 2005는 DMV를 통해서 실시간으로 인덱스의 사용빈도, 변경 횟수, lock 현황, missing 된 인덱스 정보를 제공한다.

이 자료를 바탕으로 튜닝과 관리 모니터링이 가능하다.

DMV의 정보는 마지막으로 SQL 인스턴스가 시작된 후로 누적된 값이다.

 사용하고 있는 INDEX 무엇인가?

 DDL 문을 통해 인덱스를 생성하게 되면 인덱스가 카탈로그는 업데이트 된다.

 그렇다고 이 인덱스가 "USE" 사용되는 것은 아니다.  인덱스가 select, insert, update가 될 때 sys.dm_db_index_usage_stats 에서 정보를 찾을 수 있다.

 마지막으로 SQL SERVER를 방금 재 시작 했다면 sys.dm_db_index_usage_stats 정보를 찾을 수 없을 수도 있다.

 

 

사용하지 않는 테이블 & 인덱스

1. -- unused tables & indexes.

2. DECLARE @dbid INT

3. SET @dbid = DB_ID('AdventureWorks')

4.  

    

5. SELECT OBJECT_NAME(IDX.object_id) as object_name,

6.        IDX.name AS index_name,

7.        CASE WHEN IDX.type = 1 THEN 'Clustered'

8.           WHEN IDX.type = 2 THEN 'Non-Clustered'

9.           ELSE 'Unknown' END Index_Type

10. FROM sys.dm_db_index_usage_stats  AS DIS

11.       RIGHT OUTER JOIN sys.indexes AS IDX  ON DIS.object_id = IDX.object_id AND DIS.index_id = IDX.index_id

12.       JOIN sys.objects AS OBJ  ON IDX.object_id = OBJ.object_ID

13. WHERE  OBJ.type IN ('U', 'V') AND DIS.object_id IS NULL

14. ORDER BY OBJECT_NAME(IDX.object_id), IDX.name

     

 

드물게 사용하는 인덱스

 

1. DECLARE @dbid INT

2. SET @dbid = DB_ID('AdventureWorks')

3.  

4. --- rarely used indexes appear first

5. SELECT OBJECT_NAME(DIS.object_id) as object_name,

6.        IDX.name AS index_name, IDX.index_id,

7.        CASE WHEN IDX.type = 1 THEN 'Clustered'

8.           WHEN IDX.type = 2 THEN 'Non-Clustered'

9.           ELSE 'Unknown' END Index_Type,

10.         DIS.user_seeks, DIS.user_scans, DIS.user_lookups, DIS.user_updates

11. FROM sys.dm_db_index_usage_stats AS DIS

12.             JOIN sys.indexes AS IDX ON DIS.object_id = IDX.object_id AND DIS.index_id = IDX.index_id

13. WHERE DIS.database_id = @dbid AND objectproperty(DIS.object_id,'IsUserTable') = 1

14.             --AND DIS.user_updates > 0 AND DIS.user_seeks = 0

                 --AND DIS.user_scans = 0 AND DIS.user_lookups  = 0  --(업데이트는 일어나는 사용되지 않은것, 관리 부담만 있다.)

15. ORDER BY (DIS.user_updates + DIS.user_seeks + DIS.user_scans + DIS.user_lookups ) asc

 

인덱스에 lock 이 걸려있는 지 확인

1.  declare @dbid int
2. select @dbid = db_id()
3. Select dbid=database_id, objectname=object_name(s.object_id)
   , indexname=i.name, i.index_id       --, partition_number
   , row_lock_count, row_lock_wait_count
   , [block %]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2))
   , row_lock_wait_in_ms
   , [avg row lock waits in ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2))
   from sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s,   sys.indexes i
   where objectproperty(s.object_id,'IsUserTable') = 1
   and i.object_id = s.object_id
   and i.index_id = s.index_id
   order by row_lock_wait_count desc

 

실시간 LOCK 확인 -- 다른 LOOK 쿼리 확인. 구분 이해하기

위에 부분은 실시간으로 처리되는 것이 아니라 누적된 결과를 보는 쿼리 이다. 실시간으로 락이 걸려있는 상태를 확이낳려면 프로시저를 생성하고 처리 결과를 보는것도 좋은 방법이다.

1. create proc sp_block_info
   as
   set rowcount ON
   select t1.resource_type as [lock type]
               ,db_name(resource_database_id) as [database]
               ,t1.resource_associated_entity_id as [blk object]
               ,t1.request_mode as [lock req]                                          --- lock requested
               ,t1.request_session_id as [waiter sid]                                  --- spid of waiter
               ,t2.wait_duration_ms as [wait time]        
               ,(select text from sys.dm_exec_requests as r                           --- get sql for waiter
                           cross apply sys.dm_exec_sql_text(r.sql_handle)
                           where r.session_id = t1.request_session_id) as waiter_batch
               ,(select substring(qt.text,r.statement_start_offset/2,
                                       (case when r.statement_end_offset = -1
                                       then len(convert(nvarchar(max), qt.text)) * 2
                                       else r.statement_end_offset end - r.statement_start_offset)/2)
                           from sys.dm_exec_requests as r
                           cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
                           where r.session_id = t1.request_session_id) as waiter_stmt    --- statement blocked
                ,t2.blocking_session_id as [blocker sid]                                 -- spid of blocker
        ,(select text from sys.sysprocesses as p                                         --- get sql for blocker
                           cross apply sys.dm_exec_sql_text(p.sql_handle)
                           where p.spid = t2.blocking_session_id) as blocker_stmt
               from
               sys.dm_tran_locks as t1,
               sys.dm_os_waiting_tasks as t2
   where  t1.lock_owner_address = t2.resource_address
   set rowcount OFF
   go

 

---

 SEE ALSO : http://blogs.msdn.com/sqlcat/archive/2005/12/12/502735.aspx

Problem

In the course of any database development project, searching for database objects is something you will probably do.  It can be any kind of search which is done using some text as the search mechanism. Some of the examples of search that a developer / DBA may make within a database or database server are: searching for a database object, searching for occurance of particular text within database objects, searching within the schema of a database object, search within the results of query results or entire tables, etc..

In this tip, we look at different mechanisms that can be used to facilitate this type of searching.

Solution

Searching is generally required across database objects, across databases, and also across database servers. There are different mechanism that can be used for different kinds of search requirements.  For this tip I will be using SQL Server Management Studio (SSMS) 2008.

We will look at four different ways for our search that cover various search requirements.

1. SSMS Object Search
2. T-SQL Scripts
3. SQL Search Add-in
4. SSMS Tools Pack Add-in

1) SSMS Object Search:

As a part of the enhancements in SSMS 2008, object explorer details window has a nice object search toolbar which allows context sensitive searching of database objects. As show in the below screenshot, I attempted searching database object named "Address".  It fetched all the tables that it found with the same name. But there can be many database object having the text "Address" within it, or "Address" text can also appear in the definition of the database object. Also you can use '%' as a wild card character along with the text that you want to search in the same way as you use it with "like" for T-SQL.



2) T-SQL Scripts:

This one is the most flexible to search anything you would like to search within your database server. Using this mechanism, you have the flexibility to interrogate any schema or data level information that you would like, but the only down-side is that you need to create code and create a set of scripts to search across databases. You can create parameterized stored procedures to faciliate the search. But if a change is required, either you need to create your stored procedures with all the parameters based on how you would like to slice and dice your search or you will need to change the code.

In the below screen-shot, I attempted searching the text "Address" within "AdventureWorks" database. I found a stored-procedure containing "Address" text within it's name. Then I created a query to search text "Address" within all the stored-procedures in this database. There is no limit to the kind of scripts that can be created for searching using this technique as long as you know which catalog views to use.



3) SQL Search Add-in:

This is a free SSMS add-in from Red Gate Software and can be downloaded from here. Once installed, it becomes visible on the toolbar. This search requires no explanation and the benefit is that you just type the text and see the results. You can also limit the scope for a database or type of database objects as per your requirement. It also shows you if the text you are searching for appeared in the name of the database object and definition of the database object.

In the screenshot below, you can see that I attempted searching "Address" keyword within "AdventureWorks" database, and then tried locating the stored procedure that we looked at using the T-SQL Script. It shows this stored procedure twice in the search result, as "Address" is a part of the name of this stored procedure and also "Address" appears in the definition of the stored procedure. Not only does it list the object, but also highlights the places where the text appeared in the definition.

The only downside is that it won't can not copy the search results from the grid. I have requested this feature from Red Gate Software and I heard back that there is a good possibility that this feature will get added.



4) SSMS Tools Pack Add-in:

This is another free SSMS add-in and can be downloaded from here. After installation, it can be accessed from the object explorer by right-clicking as shown below.  Also at context specific areas, it is availalbe by right-clicking. If we extend our search to the next level, say we want to search the entire database including all tables and every column for a text that contains "Napa", and we need a report of the same. See the screen-shot below, where I did a right-click on database and did a search on the entire database data for any text that contains the word "Napa". This add-in has a lot of featuers, I leave it to the reader to browse the rest of these features.

Attach 스크립트 만들기

 

 

--==========================================
--DB 복원
--==========================================
SET NOCOUNT ON
DECLARE @sql            nvarchar(max)
DECLARE @sql_move       nvarchar(max)
DECLARE @move           nvarchar(200)
DECLARE @dbid           int


SET @sql_move = ''
SET @sql = ''
DECLARE cur_restore CURSOR FOR
      SELECT dbid
            , 'CREATE DATABASE ' + name + ' ON' + char(13)
      FROM sys.sysdatabases WHERE dbid > 4 and NAME != 'LiteSpeedLocal' 
      ORDER BY name

OPEN cur_restore
FETCH NEXT FROM cur_restore
INTO @dbid, @sql_move

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @move = ''

        DECLARE cur_move CURSOR FOR
            
            select '(NAME = ''' + name + ''', FILENAME = ''' + filename + '''),' 
            from sys.sysaltfiles
            where dbid = @dbid

        OPEN cur_move
        FETCH NEXT FROM cur_move
        INTO @move


        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @sql_move = @sql_move + @move + char(13)

            FETCH NEXT FROM cur_move
            INTO @move
        END
        CLOSE cur_move
        DEALLOCATE cur_move


    SET @sql = @sql + @sql_move + 'FOR ATTACH' + char(13)
  --  select @sql
    print @sql
    set @sql = ''
    FETCH NEXT FROM cur_restore 
    INTO @dbid, @sql_move

END
CLOSE cur_restore
DEALLOCATE cur_restore

테이블의 rowcount와 예약된 공간, 인덱스 공간 사이즈.

sp_spaceused 를 사용해도 되지만, 테이블 여러개를 한번에 조회시 사용하면  좋다.

 

--==================================
-- DB 테이블 목록, row수 ceusee
-- =================================
DECLARE @tablename sysname
SET @tablename = ''
SELECT sys.name, ind.row_count
    ,convert(int,(sum(convert(numeric(15,2),reserved) * m.low /1024))) as 'reserved(KB)'
    ,convert(int,(sum(convert(numeric(15,2),used) * m.low /1024))) as 'indexp(KB)'
FROM sys.sysobjects as sys 
    join sys.dm_db_partition_stats as ind on sys.id = ind.object_id
    join sysindexes as i on  i.id = ind.object_id
    ,master.dbo.spt_values  as m
WHERE sys.type = 'U' and ind.index_id < 2 
    and m.number = 1 and m.type = 'E'
    and i.indid in (0,1,255)
    and sys.name  = @tablename
)
GROUP BY sys.name, ind.row_count

현재 존재하는 Index 스크립트 생성.

T-SQL::List all indexes in database  에 관련 함수, view 생성해야함.

 

-- INDEX Script
SELECT  
    CASE WHEN T.TABLE_NAME IS NULL THEN 
        'CREATE ' 
        + CASE IS_UNIQUE WHEN 1 THEN ' UNIQUE' ELSE '' END 
        + CASE IS_CLUSTERED WHEN 1 THEN ' CLUSTERED' ELSE '' END 
        + ' INDEX [' + INDEX_NAME + '] ON [' + v.TABLE_NAME + ']' 
        + ' (' + COLUMN_LIST + ') ON ' + FILE_GROUP 
    ELSE 
        'ALTER TABLE ['+T.TABLE_NAME+']' 
        +' ADD CONSTRAINT ['+INDEX_NAME+']' 
        +' PRIMARY KEY ' 
        + CASE IS_CLUSTERED WHEN 1 THEN ' CLUSTERED' ELSE '' END 
        + ' (' + COLUMN_LIST + ')' 
    END 
FROM 
    dbo.vAllIndexes v 
LEFT OUTER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS T  
ON 
    T.CONSTRAINT_NAME = v.INDEX_NAME 
    AND T.TABLE_NAME = v.TABLE_NAME  
    AND T.CONSTRAINT_TYPE = 'PRIMARY KEY' 
ORDER BY 
    v.TABLE_NAME, 
    IS_CLUSTERED DESC

데이터 베이스에 있는 모든 인덱스 정보를 보기 위함.

sys.indexes, sys.index_columns, sys.columns  정보를 보면 복합 인덱스 일경우 row가 한개 이상으로 나타나서 한눈에 보기가 힘들다.

select  t.name as table_name ,ind.name as index_name, 
        ind.index_id, ic.index_column_id, col.name as column_name 
from    sys.indexes ind
inner join    sys.index_columns ic on ind.object_id = ic.object_id and ind.index_id = ic.index_id
inner join    sys.columns col on  ic.object_id = col.object_id and ic.column_id = col.column_id 
inner join    sys.tables t on  ind.object_id = t.object_id
where   ind.is_primary_key = 0   
    and ind.is_unique = 0     
    and ind.is_unique_constraint = 0    
    and t.is_ms_shipped = 0
order by    t.name, ind.name, ind.index_id, ic.index_column_id
 

그래서 다음과 같이 처리한다.

CREATE FUNCTION dbo.GetIndexColumnOrder 
( 
    @object_id INT, 
    @index_id TINYINT, 
    @column_id TINYINT 
) 
RETURNS NVARCHAR(5) 
AS 
BEGIN 
    DECLARE @r NVARCHAR(5) 
    SELECT @r = CASE INDEXKEY_PROPERTY 
    ( 
        @object_id, 
        @index_id, 
        @column_id, 
        'IsDescending' 
    ) 
        WHEN 1 THEN N' DESC' 
        ELSE N'' 
    END 
    RETURN @r 
END ;

CREATE FUNCTION dbo.GetIndexColumns 
( 
    @table_name SYSNAME, 
    @object_id INT, 
    @index_id TINYINT 
) 
RETURNS NVARCHAR(4000) 
AS 
BEGIN 
    DECLARE 
        @colnames NVARCHAR(4000),  
        @thisColID INT, 
        @thisColName SYSNAME 
         
    SET @colnames = '[' + INDEX_COL(@table_name, @index_id, 1) + ']'  
        + dbo.GetIndexColumnOrder(@object_id, @index_id, 1) 
 
    SET @thisColID = 2 
    SET @thisColName = '[' + INDEX_COL(@table_name, @index_id, @thisColID) + '] '
        + dbo.GetIndexColumnOrder(@object_id, @index_id, @thisColID)
 
    WHILE (@thisColName IS NOT NULL) 
    BEGIN 
        SET @thisColID = @thisColID + 1 
        SET @colnames = @colnames + ', ' + @thisColName 
 
        SET @thisColName = INDEX_COL(@table_name, @index_id, @thisColID) 
            + dbo.GetIndexColumnOrder(@object_id, @index_id, @thisColID) 
    END 
    RETURN @colNames 
END ;

 

CREATE VIEW dbo.V_ALLINDEXES 
AS
SELECT TABLE_NAME = OBJECT_NAME(i.id), 
      INDEX_NAME = i.name, 
      COLUMN_LIST = dbo.GetIndexColumns(OBJECT_NAME(i.id), i.id, i.indid), 
      IS_CLUSTERED = INDEXPROPERTY(i.id, i.name, 'IsClustered'),
      IS_UNIQUE = INDEXPROPERTY(i.id, i.name, 'IsUnique'), FILE_GROUP = g.GroupName 
FROM sysindexes i
INNER JOIN sysfilegroups g ON i.groupid = g.groupid
WHERE (i.indid BETWEEN 1 AND 254) -- leave out AUTO_STATISTICS: 
 AND (i.Status & 64)=0 -- leave out system tables: 
 AND OBJECTPROPERTY(i.id, 'IsMsShipped') = 0 ; 

디스크의 남은 용량은 xp_fixeddrives 를 이용해서 구할 수 있지만 디스크 전체 사이즈를 쉽게 얻기는 쉽지 않다.

여러 방법이 있었는데 그중 OLE 사용하는 방법이다.

 

이 방법을 사용하기 위해서는 기능 영역에 OLE 자동화 사용이 활성화 되어 있어야 한다.

서비스 되고 있는 DB 장비는 이 기능을 ON 해 놓지 않기에 평범하게 사용할 수는 없다.

 

(클릭해서 크게 보세요)