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

SQL Server – verificando emails enviados

— ocorrencias de email reprovados

select * from msdb.dbo.sysmail_faileditems as items  inner join msdb.dbo.sysmail_event_log as l on items.mailitem_id = l.mailitem_id order by 1 desc

SQL Server – criação indices

A. Recriando um índice

O exemplo a seguir recria um índice único.

Transact-SQL

USE AdventureWorks2008R2;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO

B. Recriando todos os índices em uma tabela e especificando opções

O exemplo a seguir especifica a palavra-chave ALL. Isso recria todos os índices associados à tabela. Três opções são especificadas.

Transact-SQL

USE AdventureWorks2008R2;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
             STATISTICS_NORECOMPUTE = ON);
GO

SQL Server – listando todos os datafiles

—– List datafiles

SELECT db.name AS DBName, type_desc AS FileType, Physical_Name AS Location FROM sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id

 

—– Listando os datafiles de um banco de dados apenas

use tempdb         —  esse é o nome da base que desejo saber os datafiles

go

sp_helpfile

go

Verificando versões SQLServer

Bom dia Pessoal

Para verificar a versão do sql server que está em execução, podemos utilizar os scripts abaixo :

select @@versions

ou

SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)

 

Quanto à execução do primeiro script, no site abaixo é possível verificar o que refere-se a numeração de cada versão

http://sqlserverbuilds.blogspot.com.br/

 

att

vlw Pessoal

 

Status restore e backup

—- Status restore script

SELECT session_id as SPID, command, aa.text AS Query, start_time, percent_complete,
dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) aa
WHERE r.command in(‘BACKUP DATABASE’,’RESTORE DATABASE’,’BACKUP LOG’,’DBCCFILESCOMPACT’,’DBCC’,’CREATE INDEX’,’DbccFilesCompact’)