Olá Mundo, bom dia, comunidade….
Tudo bem? E ai como passaram os últimos meses?
Pergunto isso, devido ao post anterior desta sessão ter sido publicado no mês de fevereiro, posso imaginar que neste intervalo de tempo nossas vidas apresentaram inúmeras situações, espero que todas possam ter sido dentro do possível boas, assim como a minha, e se caso venha a existir algo mais espinhoso, que você possa ter superado.
Seguindo em frente, que alegria poder te encontrar em mais um post da sessão Short Scripts, uma das sessões mais recentes do meu blog que esta alçando a marca de 33 posts publicados trimestralmente.
Mantendo a tradição estou retornando com mais um conjunto de “pequenos” scripts catalogados e armazenados em minha biblioteca pessoal de códigos relacionados ao Microsoft SQL Server e sua fantástica linguagem de desenvolvimento Transact-SQL.
Como promessa é dívida e deve ser cumprida “ou melhor” compartilhada, estou compartilhando a minha feita a exatos três meses no final do último post da sessão Short Scripts, hoje publicando mais um conjunto de scripts adicionados atualmente na minha biblioteca particular de códigos e exemplos.
O post de hoje
Como de costume selecionei os principais scripts armazenados recentemente na minha biblioteca de códigos, que apresentam os seguintes assuntos:
- Acesso de usuário a tabelas;
- Acesso e Leitura a Banco de dados;
- Contas de Execução;
- Descrição de Colunas;
- DMV – Sys.dm_os_memory_clerks;
- DMV – Sys.dm_server_services;
- Ident_Current;
- Leitura de Log;
- Memória;
- Memory Cache Entries;
- Memory Clerks;
- Multipage Allocators Statistic
- OS Threads;
- Processos em execução;
- Scope_Ident;
- Serviços do SQL Server;
- SessioID;
- System Identity Variable; e
- XP_ReadErrorLog.
Chegou a hora, mãos nos teclados, a seguir apresento os códigos e exemplos selecionados para o Short Script – Maio 2018 . Vale ressaltar que todos os scripts publicados nesta sessão foram devidamente testados, mas isso não significa que você pode fazer uso dos mesmo em seu ambiente de produção, vale sim todo cuidado possível para evitar maiores problemas.
Fique a vontade para compartilhar, comentar e melhorar cada um destes códigos.
Short Scripts
— Short Script 1 – Encerramento processos que apresentam várias horas ou longo tempo de execução —
SET NOCOUNT ON
DECLARE @spid SMALLINT, @spidString VARCHAR(12)
DECLARE spidCursor CURSOR
FOR SELECT spid
FROM master.sys.sysprocesses
WHERE last_batch < DATEADD(hh, -8, GETDATE()) AND spid > 50 — Change 8 to any other value for hours AND spid > 50 — for user spid
FOR READ ONLY
OPEN spidCursor
FETCH NEXT FROM spidCursor INTO @spid
SELECT ‘Killed spid(s) – ‘
WHILE (@@fetch_status = 0)
AND (@@error = 0)
BEGIN
SELECT @spidString = CONVERT(VARCHAR(12), @spid)
EXEC (‘kill ‘ + @spidString)
SELECT @spid
FETCH NEXT FROM spidCursor INTO @spid
END
CLOSE spidCursor
DEALLOCATE spidCursor
SET NOCOUNT OFF
— Short Script 2 – Identificando – Memory Cache Entries —
WITH memory_cache_entries
AS
(
SELECT
name AS entry_name,
[type],
in_use_count,
pages_allocated_count,
CAST(entry_data AS XML) AS entry_data
FROM sys.dm_os_memory_cache_entries(nolock)
WHERE type = ‘USERSTORE_TOKENPERM’
),
memory_cache_entries_details
AS
(
SELECT
entry_data.value(‘(/entry/@class)[1]’, ‘bigint’) AS class,
entry_data.value(‘(/entry/@subclass)[1]’, ‘int’) AS subclass,
entry_data.value(‘(/entry/@name)[1]’, ‘varchar(100)’) AS token_name,
pages_allocated_count,
in_use_count
FROM memory_cache_entries
)
SELECT
class,
subclass,
token_name,
COUNT(*) AS nb_entries
FROM memory_cache_entries_details
GROUP BY token_name, class, subclass
ORDER BY nb_entries DESC;
— Short Script 3 – Obtendo a relação de últimos acessos de leitura e escrita por banco de dados —
;WITH myCTE AS
(
SELECT
DB_NAME(database_id) AS TheDatabase,
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update
FROM sys.dm_db_index_usage_stats
)
SELECT
ServerRestartedDate = (SELECT CREATE_DATE FROM sys.databases where name=’tempdb’),
x.TheDatabase,
MAX(x.last_read) AS last_read,
MAX(x.last_write) AS last_write
FROM
(
SELECT TheDatabase,last_user_seek AS last_read, NULL AS last_write FROM myCTE
UNION ALL
SELECT TheDatabase,last_user_scan, NULL FROM myCTE
UNION ALL
SELECT TheDatabase,last_user_lookup, NULL FROM myCTE
UNION ALL
SELECT TheDatabase,NULL, last_user_update FROM myCTE
) AS x
GROUP BY TheDatabase
ORDER BY TheDatabase
— Short Script 4 – Identificando e analisando as diferenças entre Ident_Current, Scope_Identity e Variável de Sistema Identity —
USE AdventureWorks2016;
Go
CREATE TABLE t6(id int IDENTITY);
CREATE TABLE t7(id int IDENTITY(100,1));
Go
CREATE TRIGGER t6ins ON t6 FOR INSERT
AS
BEGIN
INSERT t7 DEFAULT VALUES
END;
Go
SELECT id FROM t6;
SELECT id FROM t7;
— Do the following in Session 1.
INSERT t6 DEFAULT VALUES;
SELECT @@IDENTITY;
/*Returns the value 100.*/
SELECT SCOPE_IDENTITY();
/* Returns the value 1. This was inserted by the
INSERT statement two statements before this query.*/
SELECT IDENT_CURRENT(‘t7’);
/* Returns value inserted into t7, that is in the trigger.*/
SELECT IDENT_CURRENT(‘t6’);
/* Returns value inserted into t6. This was the INSERT statement four statements before this query.*/
— Do the following in Session 2.
SELECT @@IDENTITY;
/* Returns NULL because there has been no INSERT action
up to this point in this session.*/
SELECT SCOPE_IDENTITY();
/* Returns NULL because there has been no INSERT action
up to this point in this scope in this session.*/
SELECT IDENT_CURRENT(‘t7’);
/* Returns the last value inserted into t7.*/
— Short Script 5 – Identificando a SessionID e suas respectivas OS Threads —
SELECT osTask.session_id,
osThreads.os_thread_id,
osTask.scheduler_id,
osTask.task_state
FROM sys.dm_os_tasks AS osTask INNER JOIN sys.dm_os_threads AS osThreads
ON osTask.worker_address = osThreads.worker_address
WHERE osTask.session_id IS NOT NULL
ORDER BY osTask.session_id;
Go
— Short Script 6 – Identificando o último usuário que acesso a tabela —
SELECT DB_NAME(ius.[database_id]) AS [Database],
OBJECT_NAME(ius.[object_id]) AS [TableName],
MAX(ius.[last_user_lookup]) AS [last_user_lookup],
MAX(ius.[last_user_scan]) AS [last_user_scan],
MAX(ius.[last_user_seek]) AS [last_user_seek] FROM sys.dm_db_index_usage_stats AS ius
WHERE ius.[database_id] = DB_ID()
AND ius.[object_id] = OBJECT_ID(‘[PlayerAllData]’)
GROUP BY ius.[database_id], ius.[object_id]
Go
— Short Script 7 – Obtendo informações sobre a descrição de uma coluna —
declare @ColumnName nvarchar(300),
@TableName nvarchar(300);
Set @ColumnName= N’…’;
Set @TableName= N’…’;
Select st.name [Table],
sc.name [Column],
sep.value [Description]
from sys.tables st inner join sys.columns sc
on st.object_id = sc.object_id
left join sys.extended_properties sep
on st.object_id = sep.major_id
and sc.column_id = sep.minor_id
and sep.name = ‘MS_Description’
Where st.name = @TableName
And sc.name = @ColumnName
Go
— Short Script 8 – XP_ReadErrorLog – Identificando a porta padrão do SQL Server —
USE MASTER
Go
XP_readerrorlog 0, 1, N’Server is listening on’
Go
— Short Script 9 – Utilizando – sys.dm_os_memory_clerks – Multipage Allocators Statistic —
Select name AS clerk_name, memory_node_id,
sum(single_pages_kb) / 1024 as single_page_total_size_mb,
sum(multi_pages_kb) / 1024 as multi_page_total_size_mb,
sum(awe_allocated_kb) / 1024 as awe_allocaed_size_MB
From sys.dm_os_memory_clerks(nolock)
Where memory_node_id = 64
Group by memory_node_id, name
Having SUM(multi_pages_kb) > 0
Order BY sum(single_pages_kb) + sum(multi_pages_kb) +
sum(awe_allocated_kb) DESC
Go
— Short Script 10 – Utilizando – sys.dm_server_services – Identificando a conta que esta executando os serviços do SQL Server —
Select servicename,
startup_type_desc,
status_desc,
last_startup_time,
service_account,
is_clustered,
cluster_nodename,
filename,
startup_type,
status,
process_id
From sys.dm_server_services
Go
Missão mais que cumprida! Uma nova relação de short scripts acaba de ser compartilhada, mesmo sendo denominados short entre aspas “pequenos”, posso garantir que todos estes exemplos são de grande importância, apresentam um valor e conhecimento do mais alto nível.
Chegamos ao final de mais um Short Scripts, espero que este material possa lhe ajudar, ilustrando o uso de alguns recursos e funcionalidades do Microsoft SQL Server.
Acredito que você tenha observado que estes códigos são conhecidos em meu blog, todos estão relacionados aos posts dedicados ao Microsoft SQL Server publicados no decorrer dos últimos anos.
Boa parte deste material é fruto de um trabalho dedicado exclusivamente a colaboração com a comunidade, visando sempre encontrar algo que possa ser a solução de um determinado problema, bem como, a demonstração de como se pode fazer uso de um determinado recurso.
Links
Caso você queira acessar os últimos posts desta sessão, não perca tempo acesse os links listados abaixo:
https://pedrogalvaojunior.wordpress.com/2018/02/19/short-scripts-fevereiro-2018-transaction-log/
https://pedrogalvaojunior.wordpress.com/2017/12/09/short-scripts-dezembro-2017/
https://pedrogalvaojunior.wordpress.com/2017/09/16/short-scripts-setembro-2017/
https://pedrogalvaojunior.wordpress.com/2017/06/08/short-scripts-junho-2017/
Agradecimento
Obrigado mais uma vez por sua visita, fico honrado com sua ilustre presença ao meu blog, desejo e espero que você possa ter encontrado algo que lhe ajudou.
Volte sempre, nos encontraremos mais uma vez na sessão Short Scripts no post do mês de agosto de 2018.
Abraços…
Junior Galvão - MVP - Data Platform 