adminbd

Записи по mssql,oracle,windows и linux

Have a Question?

If you have any question you can ask below or enter what you are looking for!

All posts in Mssql

Как узнать кто подключился к mssql. С какова ip

SELECT
dc.session_id          AS [SPID]
,dc.client_net_address  AS [IP клиента]
,sp.hostname            AS [Имя PC клиента]
,dc.local_net_address   AS [IP подключения к серверу]
,dc.net_transport       AS [Протокол подключения]
,dc.local_tcp_port      AS [Порт]
,sp.[program_name]      AS [Имя программы]
,sp.loginame
FROM
sys.dm_exec_connections dc
INNER JOIN
MASTER.sys.sysprocesses sp
ON  dc.session_id = sp.spid
WHERE
sp.spid

 

Как просмотреть историю запросов mssql

/************************************************************
 *  * k.moskvichev ©
 * Time: 27.06.2019 13:13:52
 ************************************************************/

SELECT 		
      creation_time
           ,last_execution_time
           ,execution_count
           ,total_worker_time / 1000      AS CPU
           ,CONVERT(MONEY ,(total_worker_time)) / (execution_count * 1000) AS [AvgCPUTime]
           ,qs.total_elapsed_time / 1000  AS TotDuration
           ,CONVERT(MONEY ,(qs.total_elapsed_time)) / (execution_count * 1000) AS [AvgDur]
           ,total_logical_reads           AS [Reads]
           ,total_logical_writes          AS [Writes]
           ,total_logical_reads + total_logical_writes AS [AggIO]
           ,CONVERT(
                MONEY
               ,(total_logical_reads + total_logical_writes) / (execution_count + 0.0)
            )                             AS [AvgIO]
           ,CASE 
                 WHEN sql_handle IS NULL THEN ' '
                 ELSE (
                          SUBSTRING(
                              st.text
                             ,(qs.statement_start_offset + 2) / 2
                             ,(
                                  CASE 
                                       WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX) ,st.text)) * 2
                                       ELSE qs.statement_end_offset
                                  END - qs.statement_start_offset
                              ) / 2
                          )
                      
Читать далее

Как узнать нагрузку на диски и файловые группы. Ввод-Вывод. Mssql. Где больше всего IO

SELECT 		
			DB_NAME(a.database_id)          AS [Database Name]
           --,a.FILE_ID
           ,i.name
           ,a.io_stall_read_ms
           ,a.num_of_reads
           ,CAST(a.io_stall_read_ms / (1.0 + a.num_of_reads) AS NUMERIC(10 ,1)) AS [avg_read_stall_ms]
           ,a.io_stall_write_ms
           ,a.num_of_writes
           ,CAST(
                a.io_stall_write_ms / (1.0 + a.num_of_writes) AS NUMERIC(10 ,1)
            )                             AS [a.avg_write_stall_ms]
           ,a.io_stall_read_ms + a.io_stall_write_ms AS [io_stalls]
           ,a.num_of_reads + a.num_of_writes  AS [total_io]
           ,CAST(
                (a.io_stall_read_ms + a.io_stall_write_ms) / (1.0 + a.num_of_reads + a.num_of_writes) AS NUMERIC(10 ,1)
            )                             AS [avg_io_stall_ms],
            i.physical_name          
FROM   
			sys.dm_io_virtual_file_stats(NULL ,NULL)a
			INNER JOIN 
	   		sys.master_files i
            ON  a.file_id = i.file_id 
            AND            a.database_id = i.database_id
--WHERE i.database_id in  ('7')
			
ORDER BY
       		avg_io_stall_ms                  DESC;

 

Как создать хранимую процедуру для реиндекса таблиц mssql (reindex)

Задача делать реиндекс только определенных таблиц.

Создаём таблицу

USE [ax_molniya]
GO

/****** Object:  Table [dbo].[www_reindex_tablename]    Script Date: 08/17/2018 08:45:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[www_reindex_tablename](
	[objectname] [nvarchar](128) NULL,
	[group] [nvarchar](1) NULL,
	[id] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

GO


Наполняем её таблицами которые нужно реиндексировать

Как создать хранимую процедуру для реиндекса таблиц mssql (reindex)

Делаем хранимую процедуру

USE [ax_molniya]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[www_reindex_ALTER]
-- Add the parameters for the stored procedure here

-- если readonly = 0 (значение по- умолчанию), тогда выполняется вся работа скрипта)
-- если readonly = 1, тогда предоставится информация о 
Читать далее

Как вывести список заданий (job) в mssql

use msdb
go
SELECT 
    [sJOB].[name] AS [JobName]
    , [sDBP].[name] AS [JobOwner]
    , [sCAT].[name] AS [JobCategory]
  , [sJOB].[description] AS [JobDescription]
    , [sJSTP].[step_id] AS [JobStartStepNo]
    , [sJSTP].[step_name] AS [JobStartStepName]
    , [sJOB].[date_created] AS [JobCreatedOn]
    , [sJOB].[date_modified] AS [JobLastModifiedOn]
  , CASE [sJOB].[enabled]
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
      END AS [IsEnabled]
       , CASE
          WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
          ELSE 'Yes'
          END AS [IsScheduled]
    , CASE 
        WHEN [freq_type] = 64 THEN 'Start automatically when SQL Server Agent starts'
        WHEN [freq_type] = 128 THEN 'Start whenever the CPUs become idle'
        WHEN [freq_type] IN (4,8,16,32) THEN 'Recurring'
        WHEN [freq_type] = 1 THEN 'One Time'
      
Читать далее

Как сделать письмо с ошибками выполнения заданий (job)

IF (SELECT COUNT(*)
FROM dbo.sysjobs AS s 
INNER JOIN sysjobsteps AS s2 (nolock) ON s2.job_id = s.job_id
INNER JOIN dbo.sysjobhistory sh (nolock) ON sh.instance_id=(SELECT MAX(sjh.instance_id) FROM dbo.sysjobhistory sjh (nolock) WHERE sjh.job_id=s.job_id AND sjh.step_id=s2.step_id) 
			  AND sh.run_date>=(SELECT MAX(sjh.run_date) FROM dbo.sysjobhistory sjh (nolock) WHERE sjh.job_id=s.job_id AND sjh.step_id=0)
WHERE s.[enabled]=1 AND sh.run_status=0)>0
BEGIN
DECLARE @tableHTML AS NVARCHAR(max)

SET @tableHTML =
    N'<H1>Ошибки в джобах на sql сервере '+@@SERVERNAME+'</H1>' +
    N'<table border="1">' +
    N'<tr><th>Джоб</th><th>ID шага</th>' +
    N'<th>Имя шага</th><th>команда</th><th>база</th>' +
    N'<th>последний запуск</th><th>ошибка</th></tr>' +
    CAST ( ( SELECT td = s.name,'', td = s2.step_id,'', td = s2.step_name, '',td = cast(s2.command AS VARCHAR(100)),'', td = s2.database_name,'',
       td = s2.last_run_date,'', 
Читать далее

Как посмотреть кэш план

sELECT TOP 20
	qs.last_execution_time AS Last_execution_time,
	SUBSTRING(qt.text, 
				(qs.statement_start_offset/2) + 1, 
				((CASE qs.statement_end_offset 
						WHEN -1 THEN DATALENGTH(qt.text) 
						ELSE qs.statement_end_offset 
					END - qs.statement_start_offset)/2) + 1) AS Query_text, 
	qp.query_plan AS Query_plan,
	qs.execution_count AS Execution_count
FROM sys.dm_exec_query_stats AS qs
	CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
	CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
--WHERE 
WHERE
/* datepart(YEAR,qs.last_execution_time) BETWEEN 2018 AND 2018 and
		datepart(month,qs.last_execution_time) BETWEEN 06 AND 06 and
		datepart(day,qs.last_execution_time) BETWEEN 20 AND 22
	
		 --AND		CONVERT(CHAR(8),last_execution_time,8) BETWEEN '09:00:00' and '23:50:00' */


qs.last_execution_time > '2018-05-20 11:30:00.000' /* 1. Date & Time filter */
	--and qt.text like '%ChequeHead%'	/* 2. SQL query text filter */
	and qt.text not like '%Query Finder%' 
Читать далее

Как обновить статистику и очистить кеш планов.

declare @dt as datetime
set @dt= getdate()-01 -- дата, меньше которой будем обновлять


begin try

      select o.name as [objname],s.name as [shname],o.object_id,st.name as     [stname]

      into #t_stat

    from sys.stats st

        inner join  sys.objects o  on st.object_id=o.object_id

        inner join   sys.schemas  s on o.schema_id=s.schema_id

      where o.type='U'

      and STATS_DATE(st.object_id, st.stats_id)  <@dt    --фильтр по дате

      --and o.name='dbservers'                             --фильтра по обеъкту

end try

begin catch
      print (cast(error_number() as nchar(5))+'_'+ ERROR_MESSAGE())
      drop table #t_stat
end catch

--2 открываем курсом и обновляем статистику
declare cur1 cursor
             for select objname,shname,stname from #t_stat

open cur1
declare @stname sysname
declare @obj sysname
declare @sh sysname

fetch next from cur1 into @obj,@sh,@stname

WHILE 
Читать далее

Как отключить не использующиеся индексы

SELECT DatabaseName = DB_NAME(),
       TableName = OBJECT_NAME(s.[object_id]),
       IndexName = i.name,
       user_updates,
       system_updates,
      'alter index ['+i.name+'] ON [' +OBJECT_SCHEMA_NAME(i.object_id, DB_ID())+ '].['+OBJECT_NAME(s.[object_id])+'] DISABLE' as [Disable],
      'exec sp_rename ''['+OBJECT_SCHEMA_NAME(i.object_id, DB_ID())+'].['+OBJECT_NAME(s.[object_id])+'].['+i.name+']'',''disable_'+i.name+''',''INDEX''' as [Rename]
  FROM sys.dm_db_index_usage_stats s 
  INNER JOIN sys.indexes i ON s.object_id = i.object_id and
                              s.index_id  = i.index_id
  WHERE s.database_id = DB_ID() and
        OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0 and
        s.user_seeks   = 0 and
        s.user_scans   = 0 and
        s.user_lookups = 0 and
        i.is_disabled  = 0 and
        i.is_unique = 0 and
        i.is_primary_key = 0 and
        i.type_desc <> 'HEAP'
  order by user_updates + system_updates desc

 

Полезные скрипты

-- текущая ситуация на сервере (выполняемые запросы)
select session_id, status, wait_type, command, last_wait_type, percent_complete, qt.text, total_elapsed_time/1000 as [total_elapsed_time, сек],
       wait_time/1000 as [wait_time, сек], (total_elapsed_time - wait_time)/1000 as [work_time, сек]
  from sys.dm_exec_requests as qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
  where session_id >= 50 and session_id <> @@spid
  order by 1

-- текущая ситуация на сервере (выполняемые запросы)
select *
  from sys.sysprocesses where spid > 50 and spid <> @@spid and status <> 'sleeping'
  order by spid, ecid


-- проверка базы
DBCC CHECKDB WITH NO_INFOMSGS;


-- размер файлов логов
DBCC SQLPERF (Logspace) 


-- исправляет неточности в подсчете страниц для всех баз
exec 
Читать далее