------------------>TAMANHO TABLESPACES set lines 132 set pages 105 set pause off set echo off set feedb on column "TOTAL ALLOC (MB)" format 9,999,990.00 column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00 column "USED (MB)" format 9,999,990.00 column "FREE (MB)" format 9,999,990.00 column "% USED" format 990.00 select a.tablespace_name, a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)", a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)", nvl(b.tot_used,0)/(1024*1024) "USED (MB)", (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED" from (select tablespace_name, sum(bytes) physical_bytes, sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc from dba_data_files group by tablespace_name ) a, (select tablespace_name, sum(bytes) tot_used from dba_segments group by tablespace_name ) b where a.tablespace_name = b.tablespace_name (+) and a.tablespace_name not in (select distinct tablespace_name from dba_temp_files) and a.tablespace_name not like 'UNDO%' order by 1;
Month: January 2017
SQL Indexes
————————–
—– Partial List of Index Statistics for the AdventureWorks Database
SELECT object_id AS ObjectID,
index_id AS IndexID,
avg_fragmentation_in_percent AS PercentFragment,
fragment_count AS TotalFrags,
avg_fragment_size_in_pages AS PagesPerFrag,
page_count AS NumPages
FROM sys.dm_db_index_physical_stats(DB_ID(‘AdventureWorks’),
NULL, NULL, NULL , ‘DETAILED’)
WHERE avg_fragmentation_in_percent > 0
ORDER BY ObjectID, IndexID
—————————
—- List of Index Statistics for the Sales.StoreContact Table in the AdventureWorks Database
SELECT b.name AS IndexName,
a.avg_fragmentation_in_percent AS PercentFragment,
a.fragment_count AS TotalFrags,
a.avg_fragment_size_in_pages AS PagesPerFrag,
a.page_count AS NumPages
FROM sys.dm_db_index_physical_stats(DB_ID(‘AdventureWorks’),
OBJECT_ID(‘Sales.StoreContact’), NULL, NULL , ‘DETAILED’) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id
AND a.index_id = b.index_id
WHERE a.avg_fragmentation_in_percent > 0
ORDER BY IndexName
——–
—– In the following SELECT statement, I return statistics on a specific index in the StoreContact table:
DECLARE @IndexName VARCHAR(100)
SET @IndexName = ‘PK_StoreContact_CustomerID_ContactID’
DECLARE @IndexID SMALLINT
SET @IndexID =
(SELECT index_id FROM sys.indexes
WHERE name = @IndexName)
SELECT @IndexName AS IndexName,
avg_fragmentation_in_percent AS PercentFragment,
fragment_count AS TotalFrags,
avg_fragment_size_in_pages AS PagesPerFrag,
page_count AS NumPages
FROM sys.dm_db_index_physical_stats(DB_ID(‘AdventureWorks’),
OBJECT_ID(‘Sales.StoreContact’),
@IndexID, NULL , ‘DETAILED’)
WHERE avg_fragmentation_in_percent > 0
—————
—- To reorganize an index, run an ALTER INDEX statement and include the keyword REORGANIZE, as shown in the following example:
ALTER INDEX PK_StoreContact_CustomerID_ContactID
ON AdventureWorks.Sales.StoreContact
REORGANIZE
——- Rebuilding Indexes
— rebuild a index
ALTER INDEX IX_StoreContact_ContactID
ON AdventureWorks.Sales.StoreContact
REBUILD
— rebuild all index of a table
ALTER INDEX ALL
ON [Sales].[DefragSalesOrderDetail] Rebuild
go
—-
ALTER INDEX IX_StoreContact_ContactTypeID
ON AdventureWorks.Sales.StoreContact
REBUILD WITH (
FILLFACTOR = 70,
ONLINE = ON
)
—-
CREATE UNIQUE NONCLUSTERED INDEX AK_StoreContact_rowguid
ON AdventureWorks.Sales.StoreContact (rowguid ASC)
WITH (
DROP_EXISTING = ON,
FILLFACTOR = 70,
ONLINE = ON
) ON [PRIMARY]
———————–
———————–
EXECUTE master.dbo.IndexOptimize @Databases = ‘MyDatabase’
go
———————–
———————–
/* Originally created by Microsoft */
/* Error corrected by Pinal Dave (http://www.SQLAuthority.com) */
— Specify your Database Name
USE AdventureWorks;
GO
— Declare variables
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 for.
SELECT @maxfrag = 30.0;
— Declare a cursor.
DECLARE tables CURSOR FOR
SELECT CAST(TABLE_SCHEMA AS VARCHAR(100))+’.’+CAST(TABLE_NAME AS VARCHAR(100)) AS 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 the cursor for the 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
SQL Partições
===========================================
===> Partitions
—- To determine if a table is partitioned
SELECT *
FROM sys.tables AS t
JOIN sys.indexes AS i
ON t.[object_id] = i.[object_id]
AND i.[type] IN (0,1)
JOIN sys.partition_schemes ps
ON i.data_space_id = ps.data_space_id
WHERE t.name = ‘PartitionTable’;
GO
—- To determine the boundary values for a partitioned table
SELECT t.name AS TableName, i.name AS IndexName, p.partition_number, p.partition_id, i.data_space_id, f.function_id, f.type_desc, r.boundary_id, r.value AS BoundaryValue
FROM sys.tables AS t
JOIN sys.indexes AS i
ON t.object_id = i.object_id
JOIN sys.partitions AS p
ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.partition_schemes AS s
ON i.data_space_id = s.data_space_id
JOIN sys.partition_functions AS f
ON s.function_id = f.function_id
LEFT JOIN sys.partition_range_values AS r
ON f.function_id = r.function_id and r.boundary_id = p.partition_number
WHERE t.name = ‘PartitionTable’ AND i.type <= 1
ORDER BY p.partition_number;
—- To determine the partition column for a partitioned table
SELECT
t.[object_id] AS ObjectID
, t.name AS TableName
, ic.column_id AS PartitioningColumnID
, c.name AS PartitioningColumnName
FROM sys.tables AS t
JOIN sys.indexes AS i
ON t.[object_id] = i.[object_id]
AND i.[type] <= 1 — clustered index or a heap
JOIN sys.partition_schemes AS ps
ON ps.data_space_id = i.data_space_id
JOIN sys.index_columns AS ic
ON ic.[object_id] = i.[object_id]
AND ic.index_id = i.index_id
AND ic.partition_ordinal >= 1 — because 0 = non-partitioning column
JOIN sys.columns AS c
ON t.[object_id] = c.[object_id]
AND ic.column_id = c.column_id
WHERE t.name = ‘PartitionTable’ ;
GO
— Ex:
ALTER PARTITION SCHEME ps_quinzenal NEXT USED FGDATA1;
ALTER PARTITION FUNCTION pf_quinzenal() SPLIT RANGE (‘2017-03-15 00:00:00’);
go
ALTER PARTITION SCHEME ps_quinzenal NEXT USED FGDATA2;
ALTER PARTITION FUNCTION pf_quinzenal() SPLIT RANGE (‘2017-04-01 00:00:00’);
go
https://technet.microsoft.com/pt-br/library/ms188730(v=sql.110).aspx
========================================= FIM PARTITIONS
Roberto Magalhães - Dicas 