Перейти к содержимому

Mssql

Все по MSsql

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

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 
Читать далее

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

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'

 

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

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

)

select --top(10)

'use ['+db_name(igd.database_id)+'];

create index ['+'ix_'+replace(convert(varchar(10),getdate(),120),'-','')+'_'+convert(varchar,igs.group_handle)+'] on '+

igd.[statement]+'('+

case

when left(ix_col,1)=',' then stuff(ix_col,1,1,'')

when right(ix_col,1)=',' then reverse(stuff(reverse(ix_col),1,1,''))

else ix_col

end

+') '+isnull('include('+igd.included_columns+')','')+' with(online=on, maxdop=0)

go

' command

,igs.user_seeks

,igs.user_scans

,igs.avg_total_user_cost

from igs

join sys.dm_db_missing_index_groups link on link.index_group_handle = igs.group_handle

join igd on link.index_handle = igd.index_handle

where igd.database_id = db_id()

order by igs.avg_total_user_cost * igs.user_seeks desc

Но их легко можно найти были по маске

select * from sys.indexes where name like 'ix[_]2017%'

 

Первый запрос показывает текущую фрагментацию … Читать далее

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

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

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

select s.job_id,s.name,s.enabled,c.name as categoryname into #JobList 
from msdb.dbo.sysjobs s inner join msdb.dbo.syscategories c on s.category_id = c.category_id
where c.name in ('REPL-Merge','REPL-Distribution','REPL-LogReader')

create TABLE #xp_results  
   (job_id                UNIQUEIDENTIFIER NOT NULL,
    last_run_date         INT              NOT NULL,
    last_run_time         INT              NOT NULL,
    next_run_date         INT              NOT NULL,
    next_run_time         INT              NOT NULL,
    next_run_schedule_id  INT              NOT NULL,
    requested_to_run      INT              NOT NULL, 
    
Читать далее

Как исправить ошибку The OLE DB provider “SQLNCLI” for linked server “s0069” reported a change in schema version between compile time (“185675752805467”) and run time (“185684342736281”) for table

Как исправить ошибку

The OLE DB provider "SQLNCLI" for linked server "s0069" reported a change in schema version between compile time ("185675752805467") and run time ("185684342736281") for table

возникает при не правильном кеш плане у вьюхи. делаем команду

EXECUTE sp_refreshview N'имя вьюхи'

 

Как сделать хранимую процедуру для мониторинга заданий в MSSQL через prtg.

Хранимая процедура

USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[alertjob_test]    Script Date: 04/25/2018 08:59:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[alertjob_test]
zakupki NVARCHAR(max)
AS
BEGIN
set nocount ON
DECLARE @job_id VARCHAR(50)
SET @job_id = (SELECT job_id FROM msdb.dbo.sysjobs WHERE [name] = zakupki)
IF (not object_id('tempdb..#x') is null) drop table #x 
SELECT  TOP 2 s.instance_id INTO #x FROM msdb.dbo.sysjobhistory s WHERE s.job_id = @job_id AND s.step_name LIKE '(Job outcome)'  ORDER BY s.instance_id DESC
IF EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE [name] = zakupki)
BEGIN
	IF (not object_id('tempdb..#z') is null) drop table #z 
	SELECT ja.job_id, j.name as job_name, 
Читать далее

Как узнать какие запросы грузят диск

SELECT

SUM(qs.total_physical_reads) as physical_reads,

SUM(qs.total_logical_reads) as logical_reads

into T1 FROM (

select top 100000 * from

sys.dm_exec_query_stats qs

where qs.last_execution_time > (CURRENT_TIMESTAMP - '01:00:00.000')

order by qs.total_physical_reads desc

) as qs;

select top 100

(qs.total_physical_reads) as physical_reads,

(qs.total_logical_reads) as logical_reads,

qp.query_plan,

st.text,

dtb.name,

qs.*,

st.dbid

INTO T2

FROM

sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

left outer join sys.databases as dtb on st.dbid = dtb.database_id

where qs.last_execution_time > (CURRENT_TIMESTAMP - '01:00:00.000')

order by qs.total_physical_reads desc;

select

(T2.physical_reads*100/T1.physical_reads) as percent_physical_reads,

(T2.logical_reads*100/T1.logical_reads) as percent_logical_reads,

T2.*

from

T2 as T2

INNER JOIN T1 as T1

ON 1=1

order by T2.total_physical_reads 
Читать далее

Как отправлять оповещения в telegram из mssql

Задача отправлять оповещение из mssql в телеграмм для быстрого реагирования.

https://www.rootusers.com/install-powershell-5-windows-server-2008-r2/

Решил сделать  через powershell. но так как windows 2008 то версия powershell 2.0. Установил 5 версию powersheell. Так как из-за старой версии слались вопросы в телеграмм когда по русски писал.

Скрипт который будет на диске С хранится tel.ps1

param(
[string]$chat_id = $(Throw "'-chat_id' argument is mandatory"),
[string]$text = $(Throw "'-text' argument is mandatory"),
[switch]$markdown,
[switch]$nopreview
)
$token = "Ваш токен"
if($nopreview) { $preview_mode = "True" }
if($markdown) { $markdown_mode = "Markdown" } else {$markdown_mode = ""}

$payload = @{
    "chat_id" = $chat_id;
    "text" = $text
    "parse_mode" = $markdown_mode;
    "disable_web_page_preview" 
Читать далее