发现有慢SQL,'   -- 关键此处填写需要刷新视图

2019-07-16 作者:数据库   |   浏览(68)

1 建立备份数据表

图片 1图片 2

CREATE TABLE [dbo].[ProcBackup](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [sysname] NOT NULL,
    [db] [nvarchar](50) NULL,
    [obj_id] [int] NULL,
    [create_date] [datetime] NOT NULL,
    [modify_date] [datetime] NOT NULL,
    [text] [nvarchar](4000) NULL,
    [type] [nvarchar](5) NULL,
    [remark] [nvarchar](500) NULL,
    [backup_date] [datetime] NULL
) ON [PRIMARY]

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'自增ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'name'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'所在数据库' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'db'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'系统对象id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'obj_id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'create_date'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'修改时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'modify_date'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'内容' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'text'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'类型' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'type'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'remark'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'记录时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'backup_date'
GO

ProcBackup

DECLARE @tableNames varchar(500)
SET @tableNames='xxx,DB2, '   -- 关键此处填写需要刷新视图的数据库名称

1、查看监控,发现整点时间有写IO过高情况

Sqlserver  UrlEncode

2 创建存储过程

图片 3图片 4

create proc proc_backup
as
--插入新增的存储过程
insert into ProcBackup 
select A.name,'db_' as db,A.[object_id] as obj_id,A.create_date,A.modify_date,C.[text],'P' as type,'' as remark,getdate() as backup_date from sys.procedures A 
left join sys.syscomments C on A.[object_id] = C.id 
where A.name not in (select name from ProcBackup) 

--插入修改过的存储过程
insert into ProcBackup 
select A.name,'db_' as db,A.[object_id] as obj_id,A.create_date,A.modify_date,C.[text],'P' as type,'' as remark,getdate() as backup_date from sys.procedures A 
left join ProcBackup B on A.[object_id] = B.obj_id 
left join sys.syscomments C on A.[object_id] = C.id 
where A.modify_date > B.modify_date

--插入新增的视图
insert into ProcBackup 
select A.name,'db_' as db,A.[object_id] as obj_id,A.create_date,A.modify_date,C.[text],'V' as type,'' as remark,getdate() as backup_date from sys.views A 
left join sys.syscomments C on A.[object_id] = C.id 
where A.name not in (select name from ProcBackup) 

--插入修改过的视图
insert into ProcBackup 
select A.name,'db_' as db,A.[object_id] as obj_id,A.create_date,A.modify_date,C.[text],'V' as type,'' as remark,getdate() as backup_date from sys.views A 
left join ProcBackup B on A.[object_id] = B.obj_id 
left join sys.syscomments C on A.[object_id] = C.id 
where A.modify_date > B.modify_date

proc_backup

DECLARE @i_start int
SET @i_start=1;

图片 5

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UrlEncode]') and xtype in (N'FN', N'IF', N'TF'))
-- 删除函数
drop function [dbo].[UrlEncode]
GO
CREATE FUNCTION dbo.UrlEncode(@URL varchar(3072))
RETURNS varchar(3072)
AS
BEGIN 
 DECLARE @count int,
@char varchar(2),
@i int,
@bytes binary(2),
@low8 int,
@high8 int

DECLARE @URLEncode varchar(3000) ;
SET @count = LEN(@URL)
SET @i = 1
SET @URLEncode = ''
WHILE (@i <= @count)
BEGIN
 SET @char = SUBSTRING(@URL,@i,1)
 IF @char LIKE '[-A-Za-z0-9()''*._!]' AND DATALENGTH(@char) = 1
  SET @URLEncode = @URLEncode   @char
 ELSE
 BEGIN
  IF DATALENGTH(@char) = 1
  BEGIN
   SET @URLEncode = @URLEncode   '%'   SUBSTRING('0123456789ABCDEF',(ASCII(@char) / 16   1),1)
   SET @URLEncode = @URLEncode   SUBSTRING('0123456789ABCDEF',(ASCII(@char) % 16   1),1)
  END
  ELSE
  BEGIN
   SET @bytes = CONVERT(binary,@char)
   SET @high8 = (0xFF00 & CAST(@bytes AS int))/256
   SET @low8 = 0x00FF & CAST(@bytes AS int)
   SET @URLEncode = @URLEncode   '%'   SUBSTRING('0123456789ABCDEF',(@high8 / 16   1),1)
   SET @URLEncode = @URLEncode   SUBSTRING('0123456789ABCDEF',(@high8 % 16   1),1)
   SET @URLEncode = @URLEncode   '%'   SUBSTRING('0123456789ABCDEF',(@low8 / 16   1),1)
   SET @URLEncode = @URLEncode   SUBSTRING('0123456789ABCDEF',(@low8 % 16   1),1)
  END
 END
 SET @i = @i   1

END
    RETURN @URLEncode;
END

3 创建SQL Server 代理 作业

在SQL Server代理中创建作业,设置为定时执行存储过程proc_backup即可。

 

DECLARE @i_end int
SET @i_end = CHARINDEX(',', @tableNames, @i_start);

 

 

DECLARE @tableName varchar(30)

2、iotop 分析确认io高峰是由mysql导致的

declare @s nvarchar(1000) -- 注意此处改为nvarchar(1000)

图片 6

WHILE @i_end>0
BEGIN
  SET @tableName= LTrim(RTrim(SUBSTRING(@tableNames, @i_start, @i_end-@i_start)))
  --select @tableName

 

  if exists(select * from master..sysdatabases where name=@tableName)
  begin
    print '更新 数据库[' @tableName ']所有视图'

3、开启general log,分析SQL

    if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#tempTable') and type='U')
    begin
      drop table #tempTable
    end
    create table #tempTable (_sql_ nvarchar(1000))

set global general_log = on ;

    SET @s = 'USE ' @tableName ' select ''USE ' @tableName ' EXECUTE sp_refreshview '' name from sysobjects where [xtype]=''V'''
    insert into #tempTable(_sql_)
    exec sp_executesql @s

 

    ---游标循环遍历--
    declare @temp nvarchar(1000)

4、观察binlog 与 general log 发现 文件增长量不大,怀疑不是有Insert 与 update 与 delete 导致的 写IO过高

    --声明游标
    declare order_cursor cursor

 

    for(select * from #tempTable)
    --打开游标--
    open order_cursor
    --开始循环游标变量--
    fetch next from order_cursor into @temp
    while @@FETCH_STATUS = 0 --返回被 FETCH语句执行的最后游标的状态--
    begin
      --print @temp
      exec (@temp) --OK
      --exec sp_executesql @temp --OK

5、show full processlist ;发现有慢SQL

      fetch next from order_cursor into @temp --转到下一个游标,没有会死循环
    end

*************************** 6. row ***************************

    close order_cursor --关闭游标
    deallocate order_cursor --释放游标

     Id: 337153

    --用完之后要把临时表清空
    drop table #tempTable--需要注意的是,这种方法不能嵌套。

   User: user_car_bill

  end

   Host: 192.168.3.10:63018

本文由小鱼儿玄机30码发布于数据库,转载请注明出处:发现有慢SQL,'   -- 关键此处填写需要刷新视图

关键词: 小鱼儿玄机30码