Category Banco de dados

Relatório de tamanho de schema

Para obtermos o tamanho de um determinado schema utilizamos a seguinte query:

 
 

SELECT tablespace_name,

Sum(bytes)/1024/1024 AS total_size_mb

FROM dba_segments

WHERE owner = Upper(&User_Name)

GROUP BY owner, rollup(tablespace_name)

 
 

Esta query irá retornar o spaço ocupado em cada tablespace.

 
 

 
 

Para obtermos o espaço total utilizado por cada schema utilizamos a seguinte query:

 
 

SELECT OWNER,

SUM(BYTES)/1024/1024 AS TOTAL_SIZE_MB

FROM DBA_SEGMENTS

GROUP BY OWNER

ORDER BY OWNER

 
 

Read More

Finding primary keys and missing primary keys in SQL Server

Query 1 – Tables with primary keys

 
 

SQL 2005

SELECT c.name, b.name, a.name

FROM sys.key_constraints a

INNER JOIN sys.tables b ON a.parent_object_id = b.OBJECT_ID

INNER JOIN sys.schemas c ON a.schema_id = c.schema_id

WHERE a.type = ‘PK’

 
 

SQL 2000

SELECT c.name, a.name, b.name

FROM sysobjects a

INNER JOIN sysindexes b ON a.id = b.id

INNER JOIN sysusers c ON a.uid = c.uid

WHERE (b.status & 2048)<>0

 
 

Query 2 – Tables without primary keys

 
 

SQL 2005

SELECT c.name, b.name

FROM sys.tables b

INNER JOIN sys.schemas c ON b.schema_id = c.schema_id

WHERE b.type = ‘U’

AND NOT EXISTS

(SELECT a.name

FROM sys.key_constraints a

WHERE a.parent_object_id = b.OBJECT_ID

AND a.schema_id = c.schema_id

AND a.type = ‘PK’ )

 
 

SQL 2000

SELECT c.name, a.name

FROM syso...

Read More

Enabling Memory Support for Over 4 GB of Physical Memory

Enabling Memory Support for Over 4 GB of Physical Memory

 
 

To enable Address Windowing Extensions (AWE) for Microsoft SQL Server, you must run the SQL Server 2005 Database Engine under a Microsoft Windows account that has been assigned the Lock Pages in Memory option and use sp_configure to set the awe enabled option to 1. The Maximize data throughput for network application option of Network Connection in Control Panel must be selected.

 
 

To enable AWE for an instance of SQL Server 2005, use sp_configure to set the awe enabled option to 1, and then restart SQL Server...

Read More

Verificar fragmentação de tabelas e índices

DBCC SHOWCONTIG
[ (
    { table_name | table_id | view_name | view_id }
    [ , index_name | index_id ]
) ]
    [ WITH
        {
         [ , [ ALL_INDEXES ] ]
         [ , [ TABLERESULTS ] ]
         [ , [ FAST ] ]
         [ , [ ALL_LEVELS ] ]
         [ NO_INFOMSGS ]
         }
    ]

  
 

 
 

Deve-se olhar para o campo Scan Density que deve estar próximo a 100% caso contrario devemos eliminar a fragmentação com um reindex ou recriar o objeto

 
 

Ex.

 
 

DBCC SHOWCONTIG (‘tablename’) — mosta informações sobre uma tabela

 
 

DBCC SHOWCONTIG WITH TABLERESULTS –mostra informações sobre todas as tabelas de forma tabular

Read More