Murat HOŞVER

Aşağıdaki kod ile vertabanı üzerinde CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_TABLE komutları ile yapılan işlemleri kayıt altına alabilirsiniz.

Bu işlem için öncelikle ilgili veritabanında ChangeLog isminde bir tablo oluşturulur ve ardından yukarıda saydığımız komutları içerek SQL komutları bu tabloda birikmeye başlar.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ChangeLog](
	[LogId] [int] IDENTITY(1,1) NOT NULL,
	[DatabaseName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[EventType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[ObjectName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[ObjectType] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[SqlCommand] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[EventDate] [datetime] NOT NULL,
	[LoginName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[AllData] 1 NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING ON
GO

ALTER TABLE [dbo].[ChangeLog] ADD  CONSTRAINT [DF_EventsLog_EventDate]  DEFAULT (getdate()) FOR [EventDate]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [backup_objects] ON DATABASE
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_TABLE,
        ALTER_TABLE, DROP_TABLE, CREATE_FUNCTION, ALTER_FUNCTION,
        DROP_FUNCTION
AS
    SET nocount ON

    DECLARE @data XML
    SET @data = EVENTDATA()

    INSERT  INTO dbo.changelog
            (
              databasename,
              eventtype,
              objectname,
              objecttype,
              sqlcommand,
              loginname,
              AllData
            )
    VALUES  (
              @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
              @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
              @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
              @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
              @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
              @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)'),
              @data
            )

GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

DISABLE TRIGGER [backup_objects] ON DATABASE
GO

ENABLE TRIGGER [backup_objects] ON DATABASE
GO

Aşağıdaki kod ile C:\Dev klasöründeki test.xls dosyasında yer alan Sayfa1 isimli sheet deki verileri sorgulayabilirsiniz:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Dev\test.xls', 'SELECT * FROM [Sayfa1$]');

Aşağıdaki örnek kod ile STE_EMAILS tablosundaki emails kolonunu PRINT ediyoruz.

DECLARE @email NVARCHAR(255)
DECLARE MyCursor CURSOR
    FOR SELECT  email
        FROM    STE_EMAILS
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @email
WHILE @@FETCH_STATUS = 0
    BEGIN

        PRINT '%' + @email + '%'
        FETCH NEXT FROM MyCursor INTO @email
    END
CLOSE MyCursor
DEALLOCATE MyCursor

Aşağıdaki kod ile mevcut veritabanınızdaki Foreign Key’lerin ALTER scriptini oluşturabilirsiniz.

SELECT  ('ALTER TABLE [' + OBJECT_NAME(f.parent_object_id) + ']'
        + ' ADD CONSTRAINT ' + '[' + f.name + ']' + ' FOREIGN KEY ' + '('
        + COL_NAME(fc.parent_object_id, fc.parent_column_id) + ') '
        + 'REFERENCES [' + OBJECT_NAME(f.referenced_object_id) + '] ('
        + COL_NAME(fc.referenced_object_id, fc.referenced_column_id) + ');'
        + CASE WHEN is_disabled = 1 THEN ' ALTER TABLE [' + OBJECT_NAME(f.parent_object_id) + '] NOCHECK CONSTRAINT [' + f.name + ']' ELSE '' END) AS Scripts
        -- AS SET_DISABLE
FROM    .sys.foreign_keys AS f
        INNER JOIN .sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

SQL Server da yaptığınız işlemleri şayet transaction içerisinde yapıyorsanız belirli bir süre sonra log dosyasının şiştiğini ve bir zaman sonra diskte yer sıkıntısı yaşadığınızı fark edeceksinizdir.

Aşağıdaki komut ile SQL Server da bulunan bütün veritabalarınızın log dosyalarının boyutlarını minimum a indirebilirsiniz.

declare @ssql nvarchar(4000)
set @ssql= '
if ''?'' not in (''tempdb'',''master'',''model'',''msdb'') begin
use [?]
declare @tsql nvarchar(4000) set @tsql = ''''
declare @iLogFile int
declare @sLogFileName varchar(55)
declare @RecoveryModel nvarchar(10)
declare LogFiles cursor for
select fileid from sysfiles where status & 0x40 = 0x40
open LogFiles
fetch next from LogFiles into @iLogFile
while @@fetch_status = 0
begin
set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 10) ''
fetch next from LogFiles into @iLogFile
end
select @RecoveryModel = CONVERT(nvarchar(10),Databasepropertyex(name, ''Recovery'')) from master..sysdatabases where name = ''?''
set @tsql = ''USE [?]; '' + @tsql + '' ALTER DATABASE [?] SET RECOVERY SIMPLE '' + @tsql
set @tsql = @tsql + ''; ALTER DATABASE [?] SET RECOVERY '' + @RecoveryModel + '';''
--print @tsql --for debugging
exec(@tsql)
close LogFiles
DEALLOCATE LogFiles
end'

exec sp_msforeachdb @ssql

Not: Yukarıdaki log dosyası küçültme işlemi sadece recovery modeli Simple olan veritabanları için geçerlidir. Veritabanınızın recovery modelini veritabanı özelliklerindeki Options alanından görebilirsiniz.

MS SQL Server da tablolar üzerine koyduğumuz indexler, zaman içerisinde tablolara eklenen veya silinen kayıtları yüzünden parçalara ayrılırlar (fragmented olma durumu).

Bundan dolayı bu indexleri kullanan sorgularımız da zaman içerisinde performanslarında ciddi düşüşler yaşanır.

Eğer siz de son zamanlarda sorgularınızda performans kaybı olduğunu düşünüyorsanız, aşağıdaki sql kodu ile indexlerinizi defrag etmenizde fayda olacaktır.

SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr   VARCHAR (255)
DECLARE @objectid  INT
DECLARE @indexid   INT
DECLARE @frag      DECIMAL
DECLARE @maxfrag   DECIMAL

-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 30.0

-- Declare cursor
DECLARE tables CURSOR FOR
   SELECT TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE'

-- Create the table
CREATE TABLE #fraglist (
   ObjectName CHAR (255),
   ObjectId INT,
   IndexName CHAR (255),
   IndexId INT,
   Lvl INT,
   CountPages INT,
   CountRows INT,
   MinRecSize INT,
   MaxRecSize INT,
   AvgRecSize INT,
   ForRecCount INT,
   Extents INT,
   ExtentSwitches INT,
   AvgFreeBytes INT,
   AvgPageDensity INT,
   ScanDensity DECIMAL,
   BestCount INT,
   ActualCount INT,
   LogicalFrag DECIMAL,
   ExtentFrag DECIMAL)

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
   FROM tables
   INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
   FETCH NEXT
      FROM tables
      INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor
OPEN indexes

-- loop through the indexes
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%'
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')'
   EXEC (@execstr)

   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag
END

-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes

-- Delete the temporary table
DROP TABLE #fraglist
GO

Not: Tablolarda kullandığınız indexlerin boyutlarına bağlı olarak bu işlem biraz uzun sürebilir. Bundan dolayı bu SQL i veritabanı kullanımınızın minimum olduğu bir zamanda çalıştırmanızı tavsiye ederim. Ayrıca bunu bir job olarak tanımlayıp belirli periyodlarda bu işlemi yapmanızda da ciddi fayda olacaktır.

Aşağıda yazdığım bir komut ile mevcut çalıştırdığınız veritabanı üzerindeki tabloların kayıt sayısı ve o tabloların kapladığı alanları görebilirsiniz.

SET nocount ON
IF (object_id('tempdb..#spaceused') IS NOT NULL) DROP TABLE #spaceused ;
CREATE TABLE #spaceused
    (
      name NVARCHAR(120),
      rows FLOAT,
      reserved VARCHAR(18),
      data VARCHAR(18),
      index_size VARCHAR(18),
      unused VARCHAR(18)
    )

DECLARE Tables CURSOR
    FOR SELECT  name
        FROM    sysobjects
        WHERE   type = 'U'
        ORDER BY name ASC

OPEN Tables
DECLARE @table VARCHAR(128)

FETCH NEXT FROM Tables INTO @table

WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT  INTO #spaceused
                EXEC sp_spaceused @table
        FETCH NEXT FROM Tables INTO @table
    END

CLOSE Tables
DEALLOCATE Tables 

SELECT  *
FROM    #spaceused
ORDER BY [rows] DESC

DROP TABLE #spaceused

Bu sorgu sonucunda gelen tablo listesine göre tanımlayacağınız indexlerin hangi tablolarda daha çok verim vereceğini görebilmiş olacaksınız.

Indexlerin önemi ve performansı arttırmadaki gücüne bir başka konuda değineceğiz..

SQL Management Studio nun bana göre en önemli eksiklerinden birisi Stored Procedure lerin son güncelleme zamanını önyüz kısmında göstermemesidir. Bu eksiklik sayesinde biz developerlar genellikle SP lerin son güncelleme tarihini görebilmek için çeşitli taklalar atarız. Bunlardan bir tanesi de aşağıdaki örnekte yer alır.

SELECT name, *
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 7

Bu SQL komutu ile son güncelleme tarihi 1 hafta öncesine kadar olan Stored Procedure lerin listesini verir

Aşağıdaki kod içerisinde xxx yazan yere istediğiniz kelimeyi yazarak, o kelimenin içerisinde geçtiği Store Procedure leri listeleyebilirsiniz.

SELECT  obj.Name as SPName,
		--modu.definition as SPDefinition,
        obj.create_date as CreationDate,
        obj.modify_date as LastUpdateDate
FROM    sys.sql_modules modu
        INNER JOIN sys.objects obj ON modu.object_id = obj.object_id
WHERE   obj.type = 'P'
        AND modu.definition LIKE '%xxx%'
Order By obj.Name

Örneğin MEMBERS adında bir tablonuz olsun. Bu tabloyla ilgili işlem yapan Stored Procedure leri bulmak için aşağıdaki SQL i çalıştırabilirsiniz:

SELECT  obj.Name as SPName,
		--modu.definition as SPDefinition,
        obj.create_date as CreationDate,
        obj.modify_date as LastUpdateDate
FROM    sys.sql_modules modu
        INNER JOIN sys.objects obj ON modu.object_id = obj.object_id
WHERE   obj.type = 'P'
        AND modu.definition LIKE '%MEMBERS%'
Order By obj.Name