Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Code Block
languagesql
titleTablo isimleri, satır sayıları ve kapladığı alanları listeleyen prosedür. Parametre almaz. :exec GetAllTableSizes
linenumberstrue
CREATE PROCEDURE GetAllTableSizes
AS
/*
    Obtains spaced used data for ALL user tables in the database
*/
DECLARE @TableName VARCHAR(100)    --For storing values in the cursor

--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR 
select [name]
from dbo.sysobjects 
where  OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY

--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
    tableName varchar(100),
    numberofRows int,
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)

--Open the cursor
OPEN tableCursor

--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName

--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
    --Dump the results of the sp_spaceused query to the temp table
    INSERT  #TempTable
        EXEC sp_spaceused @TableName

    --Get the next table name
    FETCH NEXT FROM tableCursor INTO @TableName
END

--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor

--Select all records so we can use the reults
SELECT * 
FROM #TempTable order by 2 desc

--Final cleanup!
DROP TABLE #TempTable

GO
Code Block
languagesql
titleVeri tabanı üzerinde obje bulma If-Else
linenumberstrue
--SP
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[SP_YIL_AY_GUN_INSERT]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.[SP_YIL_AY_GUN_INSERT]
END
 
--index
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'IDX_EBOrderNO')
	drop index [IDX_EBOrderNO] ON [dbo].[schBayer_OrderHeaders] 
go
 
-- IF exists table
IF EXISTS (SELECT * FROM sysobjects WHERE xtype = 'U' AND name = 'schBayer_Products')
BEGIN
	DROP TABLE dbo.[schBayer_Products]
END
Code Block
languagesql
titleEğer veri tabanına sık sık erişiliyorsa AUTO_CLOSE OFF yapılmalı
exec sp_MsForEachDB ' ALTER DATABASE [?] SET AUTO_CLOSE OFF  '		
Code Block
languagesql
titleProsedürlerin Okuma-Yazma Süreleri
  SELECT TOP 10
ObjectName          = OBJECT_NAME(qt.objectid)
,DiskReads          = qs.total_physical_reads -- The worst reads, disk reads
,MemoryReads        = qs.total_logical_reads  --Logical Reads are memory reads
,Executions         = qs.execution_count
,AvgDuration        = qs.total_elapsed_time / qs.execution_count
,CPUTime            = qs.total_worker_time
,DiskWaitAndCPUTime = qs.total_elapsed_time
,MemoryWrites       = qs.max_logical_writes
,DateCached         = qs.creation_time
,DatabaseName       = DB_Name(qt.dbid)
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = db_id() -- Filter by current database
ORDER BY qs.total_elapsed_time DESC
Code Block
languagesql
titleEn uzun süreyi alan sorgular listesini kontrol eder.
linenumberstrue
SELECT TOP 100
    qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
    qs.total_elapsed_time / 1000000.0 AS total_seconds,
    qs.execution_count,
    SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,
    o.name AS object_name,
    DB_NAME(qt.dbid) AS database_name
  FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qt.dbid = DB_ID()
  ORDER BY average_seconds DESC;
-- Queries doing most I/O:
SELECT TOP 100
    (total_logical_reads + total_logical_writes) / qs.execution_count AS average_IO,
    (total_logical_reads + total_logical_writes) AS total_IO,
    qs.execution_count AS execution_count,
    SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS indivudual_query,
    o.name AS object_name,
    DB_NAME(qt.dbid) AS database_name
  FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qt.dbid = DB_ID()
  ORDER BY average_IO DESC;
Code Block
languagesql
titleSQL - index fragmentation script - 30 dan yüksek parçalanmaya sahip indexleri listeler, bu indexlerin yeniden rebuild edilmesi gerekir.
linenumberstrue
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
indexstats.avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind  
ON ind.object_id = indexstats.object_id 
AND ind.index_id = indexstats.index_id 
WHERE indexstats.avg_fragmentation_in_percent > 30 
ORDER BY indexstats.avg_fragmentation_in_percent DESC