AdminBD — Записи по sql, windows,oracle и linux.

27Дек 2018

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

Автор: kmosk ⋅ Оставить комментарий ⋅ Aside

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 > 50

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 > 50

14Дек 2018

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

Автор: kmosk ⋅ Оставить комментарий ⋅ Aside

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  ))
	end as query_text,
	db_name(st.dbid)as database_name,
	object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as object_name,
	qp.query_plan
	
from sys.dm_exec_query_stats  qs

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 ))

end as query_text,

db_name(st.dbid)as database_name,

object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as object_name,

qp.query_plan

from sys.dm_exec_query_stats qs

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

03Окт 2018

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

Автор: kmosk ⋅ Оставить комментарий ⋅ Aside

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;

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;

17Авг 2018

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

Автор: kmosk ⋅ Оставить комментарий ⋅ Aside

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

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

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

USE [ax_molniya]

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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

07Авг 2018

Как понизить редакцию сервера Windows Server 2008 R2 с enterprise до Standard.

Автор: kmosk ⋅ Оставить комментарий ⋅ Aside

Возникла такая необходимость понизить редакцию сервера Windows Server 2008 R2 с enterprise до Standard.

Для этого необходимо поменять два ключика в реестре. Запускаем редактор реестра regedit. Проходим в ветку реестра HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\
Читать далее →

26Июн 2018

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

Автор: kmosk ⋅ Оставить комментарий ⋅ Aside

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'
      END [ScheduleType]
    , CASE [freq_type]
        WHEN 1 THEN 'One Time'
        WHEN 4 THEN 'Daily'
        WHEN 8 THEN 'Weekly'
        WHEN 16 THEN 'Monthly'
        WHEN 32 THEN 'Monthly - Relative to Frequency Interval'
        WHEN 64 THEN 'Start automatically when SQL Server Agent starts'
        WHEN 128 THEN 'Start whenever the CPUs become idle'
      END [Occurrence]
    , CASE [freq_type]
        WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)'
        WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) 
                    + ' week(s) on '
                    + CASE WHEN [freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END
                    + CASE WHEN [freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END
                    + CASE WHEN [freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END
                    + CASE WHEN [freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END
                    + CASE WHEN [freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END
                    + CASE WHEN [freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END
                    + CASE WHEN [freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END
        WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3)) 
                     + ' of every '
                     + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'
        WHEN 32 THEN 'Occurs on '
                     + CASE [freq_relative_interval]
                        WHEN 1 THEN 'First'
                        WHEN 2 THEN 'Second'
                        WHEN 4 THEN 'Third'
                        WHEN 8 THEN 'Fourth'
                        WHEN 16 THEN 'Last'
                       END
                     + ' ' 
                     + CASE [freq_interval]
                        WHEN 1 THEN 'Sunday'
                        WHEN 2 THEN 'Monday'
                        WHEN 3 THEN 'Tuesday'
                        WHEN 4 THEN 'Wednesday'
                        WHEN 5 THEN 'Thursday'
                        WHEN 6 THEN 'Friday'
                        WHEN 7 THEN 'Saturday'
                        WHEN 8 THEN 'Day'
                        WHEN 9 THEN 'Weekday'
                        WHEN 10 THEN 'Weekend day'
                       END
                     + ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) 
                     + ' month(s)'
      END AS [Recurrence]
    , CASE [freq_subday_type]
        WHEN 1 THEN 'Occurs once at ' 
                    + STUFF(
                 STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 2 THEN 'Occurs every ' 
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between ' 
                    + STUFF(
                   STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 4 THEN 'Occurs every ' 
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between ' 
                    + STUFF(
                   STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 8 THEN 'Occurs every ' 
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
      END [Frequency]

100

101

102

103

104

105

106

107

108

109

110

111

112

use msdb

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'

END [ScheduleType]

, CASE [freq_type]

WHEN 1 THEN 'One Time'

WHEN 4 THEN 'Daily'

WHEN 8 THEN 'Weekly'

WHEN 16 THEN 'Monthly'

WHEN 32 THEN 'Monthly - Relative to Frequency Interval'

WHEN 64 THEN 'Start automatically when SQL Server Agent starts'

WHEN 128 THEN 'Start whenever the CPUs become idle'

END [Occurrence]

, CASE [freq_type]

WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)'

WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3))

+ ' week(s) on '

+ CASE WHEN [freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END

+ CASE WHEN [freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END

+ CASE WHEN [freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END

+ CASE WHEN [freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END

+ CASE WHEN [freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END

+ CASE WHEN [freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END

+ CASE WHEN [freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END

WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3))

+ ' of every '

+ CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'

WHEN 32 THEN 'Occurs on '

+ CASE [freq_relative_interval]

WHEN 1 THEN 'First'

WHEN 2 THEN 'Second'

WHEN 4 THEN 'Third'

WHEN 8 THEN 'Fourth'

WHEN 16 THEN 'Last'

END

+ ' '

+ CASE [freq_interval]

WHEN 1 THEN 'Sunday'

WHEN 2 THEN 'Monday'

WHEN 3 THEN 'Tuesday'

WHEN 4 THEN 'Wednesday'

WHEN 5 THEN 'Thursday'

WHEN 6 THEN 'Friday'

WHEN 7 THEN 'Saturday'

WHEN 8 THEN 'Day'

WHEN 9 THEN 'Weekday'

WHEN 10 THEN 'Weekend day'

END

+ ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3))

+ ' month(s)'

END AS [Recurrence]

, CASE [freq_subday_type]

WHEN 1 THEN 'Occurs once at '

+ STUFF(

STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)

, 3, 0, ':')

, 6, 0, ':')

WHEN 2 THEN 'Occurs every '

+ CAST([freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between '

+ STUFF(

STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)

, 3, 0, ':')

, 6, 0, ':')

+ ' & '

+ STUFF(

STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)

, 3, 0, ':')

, 6, 0, ':')

WHEN 4 THEN 'Occurs every '

+ CAST([freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between '

+ STUFF(

STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)

, 3, 0, ':')

, 6, 0, ':')

+ ' & '

+ STUFF(

STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)

, 3, 0, ':')

, 6, 0, ':')

WHEN 8 THEN 'Occurs every '

+ CAST([freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between '

+ STUFF(

STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)

, 3, 0, ':')

, 6, 0, ':')

+ ' & '

+ STUFF(

STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)

, 3, 0, ':')

, 6, 0, ':')

END [Frequency]

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

22Июн 2018

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

Автор: kmosk ⋅ Оставить комментарий ⋅ Aside

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)

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)

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

22Июн 2018

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

Автор: kmosk ⋅ Оставить комментарий ⋅ Aside

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' */

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' */

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

22Июн 2018

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

Автор: kmosk ⋅ Оставить комментарий ⋅ Aside

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]

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]

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

21Июн 2018

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

Автор: kmosk ⋅ Оставить комментарий ⋅ Aside

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 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

21Июн 2018

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

Автор: kmosk ⋅ Оставить комментарий ⋅ Aside

-- текущая ситуация на сервере (выполняемые запросы)
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 &gt;= 50 and session_id &lt;&gt; @@spid
  order by 1

-- текущая ситуация на сервере (выполняемые запросы)

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

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

21Июн 2018

Как посмотреть нагрузку на tempdb.

Автор: kmosk ⋅ Оставить комментарий ⋅ Aside

USE MASTER
SELECT cast ((1.0 * stats.io_stall_write_ms / stats.num_of_writes) as int)
FROM sys.dm_io_virtual_file_stats(2,null) as stats
INNER JOIN master.sys.master_files AS files 
ON stats.database_id = files.database_id
AND stats.file_id = files.file_id
WHERE files.type_desc = 'ROWS'

USE MASTER

SELECT cast ((1.0 * stats.io_stall_write_ms / stats.num_of_writes) as int)

FROM sys.dm_io_virtual_file_stats(2,null) as stats

INNER JOIN master.sys.master_files AS files

ON stats.database_id = files.database_id

AND stats.file_id = files.file_id

WHERE files.type_desc = 'ROWS'

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

19Июн 2018

Как создать индексы которые рекомендует sql

Автор: kmosk ⋅ Оставить комментарий ⋅ Aside

with igs as

(

select *

from sys.dm_db_missing_index_group_stats

)

, igd as

(

select *,

isnull(equality_columns,'')+','+isnull(inequality_columns,'') as ix_col

from sys.dm_db_missing_index_details

with igs as

(

select *

from sys.dm_db_missing_index_group_stats

)

, igd as

(

select *,

isnull(equality_columns,'')+','+isnull(inequality_columns,'') as ix_col

from sys.dm_db_missing_index_details

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

31Май 2018

Как мониторить репликации на mssql. Создать оповещение (alert) если не работает репликация

Автор: kmosk ⋅ Оставить комментарий ⋅ Aside

Хранимая процедура что бы посмотреть в каком состоянии репликации.

CREATE PROCEDURE GetReplicationAgentStatus
AS

BEGIN
set nocount on
set transaction isolation level read uncommitted 

/*
Make sure your agents are in the correct category 
i.e Merge agents under REPL-Merge, 
Distribution agents under REPL-Distribution 
and LogReader agent under REPL-LogReader
*/

CREATE PROCEDURE GetReplicationAgentStatus

BEGIN

set nocount on

set transaction isolation level read uncommitted

Make sure your agents are in the correct category

i.e Merge agents under REPL-Merge,

Distribution agents under REPL-Distribution

and LogReader agent under REPL-LogReader

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

17Май 2018

Как залить настройки на точку доступа cisco через python

Автор: kmosk ⋅ Оставить комментарий ⋅ Aside

Подключаем точку к сети и консольным кабелем. Включаем tftp сервер. Внутр папки ложим подготовленный файл конфигурации.

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

Навигация по записям

1 2 3 … 9 Далее →

Февраль 2019
Пн	Вт	Ср	Чт	Пт	Сб	Вс
« Дек
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28