SMO - Server Management Objects

SMO - Server Management Objects é uma blibioteca de objectos ou API escrita em cima do .NET Framework que permite gerir e automatizar algumas tarefas nos servidores de SQL.

Usando o PowerShell, podemos iniciar um script que permite com um conjunto reduzido de comandos obter informação que de outra forma seriam necessárias centenas de linhas (e.g. T-SQL).

$MyServerInstance='ServerInstance'#The SQL Server instance
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  | out-null
$Server = new-object ('Microsoft.SqlServer.Management.Smo.Server') $MyServerInstance
$Server

Link: http://www.sqlservercentral.com/articles/SMO/140966/

MSSQL Queries Productivity

Seguem em baixo um conjunto de 15 queries bastante uteis:

• Hostname, edition and version number SQL Server

SELECT SERVERPROPERTY('MachineName') as Host, SERVERPROPERTY('InstanceName') as Instance, @@VERSION as "Version Number", SERVERPROPERTY('Edition') as Edition, SERVERPROPERTY('ProductLevel') as ProductLevel, Case SERVERPROPERTY('IsClustered') when 1 then 'CLUSTERED' else 'STAND ALONE' end as "Server Type"

•  Número de tabelas numa base de dados

SELECT COUNT(OBJ.ID) as "Table Count" FROM SYSOBJECTS OBJ WHERE XTYPE='U'

•  Número de Stored Procedures numa base de dados

SELECT COUNT(OBJ.ID) as "SP Count" FROM SYSOBJECTS OBJ WHERE XTYPE='P'

• Lista de tabela com contador de registos

SELECT OBJ.NAME as "Table Name", MAX(SI.ROWS) as "Row Count" FROM SYSOBJECTS OBJ, SYSINDEXES SI WHERE OBJ.XTYPE = 'U' AND SI.ID = OBJECT_ID(OBJ.NAME) GROUP BY OBJ.NAME ORDER BY 2 DESC

• Mostra data de criação e modificação de tabelas

SELECT NAME as "Table Name", CREATE_DATE as "Created Date",
MODIFY_DATE as "Modified Date" FROM SYS.OBJECTS
WHERE TYPE = 'U' --AND NAME LIKE '%sys_%' ORDER BY 3 DESC 

• Mostra data de criação e mdificação de stored procedures

SELECT NAME as "SP Name", CREATE_DATE as "Created Date",
MODIFY_DATE as "Modified Date" FROM SYS.OBJECTS
WHERE TYPE = 'P' --AND NAME LIKE '%delete%' ORDER BY 3 DESC

• Procura lista de tabelas com determinado nome de coluna

SELECT s.[NAME] 'Schema', t.[NAME] 'Table', c.[NAME] 'Column', d.[NAME] 'Data Type', d.[MAX_LENGTH] 'Max Length', c.[IS_IDENTITY] 'Is Id',
c.[IS_NULLABLE] 'Is Nullable', t.[MODIFY_DATE] 'Date Modified', t.[CREATE_DATE] 'Date created' FROM SYS.SCHEMAS s INNER JOIN SYS.TABLES t ON s.SCHEMA_ID= t.SCHEMA_ID INNER JOIN SYS.COLUMNS c ON t.OBJECT_ID= c.OBJECT_ID INNER JOIN SYS.TYPES d ON c.USER_TYPE_ID= d.USER_TYPE_ID WHERE c.NAME like '%backup%

• Procura uma lista de objectos que contem um determinado texto

SELECT o.NAME FROM SYSCOMMENTS (nolock) AS c INNER JOIN SYSOBJECTS (nolock) AS o ON c.ID = o.id WHERE c.TEXT LIKE '%BACKUP%';

• Obtém localização fisica da base de dados

SELECT   DB_NAME(DATABASE_ID) as DatabaseName, NAME, TYPE_DESC,
PHYSICAL_NAME FROM SYS.MASTER_FILES

• Obtém o login com previlegios de administrador

SELECT l.NAME, l.DENYLOGIN, l.ISNTNAME, l.ISNTGROUP, l.ISNTUSER
FROM MASTER.DBO.SYSLOGINS l WHERE l.SYSADMIN = 1 OR l.SECURITYADMIN = 1

• Obtém o resultado do ultimo backup efetuado à base de dados

SELECT db.NAME, CASE WHEN MAX(b.backup_finish_date) IS NULL THEN 'No Backup' ELSE convert(varchar(100), MAX(b.BACKUP_FINISH_DATE)) END AS last_backup_finish_date FROM SYS.DATABASES db LEFT OUTER JOIN MSDB.DBO.BACKUPSET b ON db.NAME = b.DATABASE_NAME AND b.TYPE = 'D' WHERE db.DATABASE_ID NOT IN (2) GROUP BY db.NAME ORDER BY 2 DESC

• Obtém a lista de queries com leitura mais intensiva (READS)

SELECT TOP 50 SUBSTRING(QT.TEXT, (QS.STATEMENT_START_OFFSET/2) +1, ((CASE QS.STATEMENT_END_OFFSET WHEN -1 THEN (QT.TEXT) ELSE QS.STATEMENT_END_OFFSET
END - QS.STATEMENT_START_OFFSET)/2)+1), QS.EXECUTION_COUNT,
QS.TOTAL_LOGICAL_READS, QS.TOTAL_ELAPSED_TIME,
QS.LAST_ELAPSED_TIME, QS.MIN_ELAPSED_TIME, QS.MAX_ELAPSED_TIME, QS.LAST_EXECUTION_TIME, QP.QUERY_PLAN
FROM SYS.DM_EXEC_QUERY_STATS QS CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) QT CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(QS.PLAN_HANDLE) QP WHERE QT.ENCRYPTED=0 ORDER BY QS.TOTAL_LOGICAL_READS DESC, QS.TOTAL_ELAPSED_TIME DESC

• Obtém utilizador atual, processos bloqueados, memória, etc

sp_who2

• Lista os índices das tabelas

SELECT A.NAME TABLE_NAME, B.NAME INDEX_NAME, D.NAME COLUMN_NAME FROM SYS.TABLES A, SYS.INDEXES B, SYS.INDEX_COLUMNS C, SYS.COLUMNS D WHERE A.OBJECT_ID = B.OBJECT_ID AND B.OBJECT_ID = C.OBJECT_ID AND B.INDEX_ID = C.INDEX_ID AND C.OBJECT_ID = D.OBJECT_ID AND C.COLUMN_ID = D.COLUMN_ID --AND A.NAME LIKE '%BACKUP%';

• Obtém tamanho da base de dados e do log

WITH FS AS (SELECT DATABASE_ID, TYPE, SIZE * 8.0 / 1024 SIZE
FROM SYS.MASTER_FILES) SELECT NAME, (SELECT SUM(SIZE) FROM FS WHERE TYPE = 0 AND FS.DATABASE_ID = DB.DATABASE_ID) DATAFILESIZEMB, (SELECT SUM(SIZE) FROM FS WHERE TYPE = 1 AND FS.DATABASE_ID = DB.DATABASE_ID) LOGFILESIZEMB FROM SYS.DATABASES DB

Para saber mais consultar;

http://www.infobarrel.com/15_Sql_Server_Queries