Início / dba / Perguntas / 143160
Accepted
Sabre
Asked: 2016-07-07 11:39:21 +0800 CST

Tabelas de bloqueio de conexões ODBC no SQL Server

  • 772

Temos um sistema ERP um pouco menos moderno, o back-end está atualmente em uma instalação do SQL Server 2008 R2. Temos por mandato de gerenciamento (não é sempre o caso) a prática completamente impraticável de permitir que muitos usuários extraiam dados deste sistema à vontade para fins de manipulação dos dados em seu tipo preferido de relatório (BI, Powerquery, Access , Excel, etc.)

Embora todos esses usuários geradores de relatórios tenham apenas direitos selecionados, existem vários outros aplicativos que têm a capacidade de realmente manipular dados para fins de fazer coisas como EDI, entrada automatizada de dados.

Logicamente, isso cria uma série de condições de corrida nas mesas. Primeiro a chegar, primeiro a servir, ou pior ainda, ainda servindo, por favor, aguarde, ou apenas NÃO... Acredito que isso está causando ainda mais anos de corrupção de dados inexplicável que requer assistência do fornecedor para corrigir. Porque nem todos os aplicativos são codificados adequadamente para antecipar a chance de que algo mais possa ter interrompido sua própria intenção privada.

Portanto, a tarefa é provar/corrigir/mitigar sempre que possível em um sistema que herdei recentemente.

Meu primeiro instinto me diz que isso não pode ser feito, sem algum tipo de controle de IPC/erro/transação entre produtos de diferentes fornecedores e sua vontade de corrigi-lo, controle sobre o código-fonte ou capacidade de restringi-lo, que isso NÃO PODE ser corrigido de um DBA perspectiva, são simplesmente más práticas que levam ao que deveria ser antecipado como efeitos colaterais potencialmente indesejados.

Portanto, tudo o que posso fazer neste momento é provar isso e ver que, com provas, posso solicitar algum tipo de mudança de comportamento no coração da administração que insiste que deve ser assim.

A primeira etapa é que estou executando uma saída contínua do criador de perfil no disco, embora com uso intensivo de recursos. Tenho que ser capaz de voltar e ver o que foi enviado, por quem e qual o impacto que teve. Também estou usando o log perfmon e o PAL para tentar fazer referência cruzada ao comportamento intensivo no servidor, "quando o processador e a memória estão atrelados, o que o servidor SQL estava fazendo naquela instância (provavelmente atendendo a uma das GUI escritas, com baixo desempenho, dez consultas de união de tabelas, não indexadas, que algum redator/ferramenta de relatório juntou"

Uma dessas situações acabou de ocorrer, tínhamos um usuário no sistema ERP que não conseguia realizar uma função, identificamos o usuário logado no banco de dados que não estava utilizando o software ERP (Estava utilizando ODBC, usuário SQL com acesso somente select, e MS Access), desconectou-os e a função foi concluída. A gerência se recusa a acreditar que eles estão relacionados porque "deveriam estar lidando com tabelas diferentes e o usuário só tinha acesso selecionado" e não tenho histórico de perfil do instante imediatamente anterior ao ocorrido.

Portanto, tudo isso se resume a uma pergunta para os colegas DBA, dada a tarefa de PROVAR que o aplicativo A está sendo afetado negativamente pelo aplicativo B, que sugestões você teria a oferecer?

sql-server sql-server-2008-r2

2 respostas

  1. Best Answer

    Infelizmente, também lidamos com muitos problemas com isso onde trabalho. Nos casos em que isso tem sido um problema, pessoas que não conhecem bancos de dados, abrem literalmente uma tabela SQL "ao vivo" no MS Access. A princípio, as coisas podem parecer transitórias porque podem abrir e fechar o Access rapidamente. Mas há aqueles momentos em que eles deixam o MS Access aberto na mesa SQL ao vivo enquanto saem para almoçar ou talvez durante o fim de semana prolongado - bloqueando aplicativos que tentam usar o banco de dados. Só para dizer o óbvio: o bloqueio pode afetar adversamente qualquer aplicativo que tente usar a linha, tabela ou banco de dados SQL bloqueado.

    Tudo o que realmente precisávamos fazer para provar qualquer coisa era capturar dados sp_who2para encontrar as cadeias de bloqueio.

    Dito isto, quando estou longe do console, ainda uso o seguinte código que escrevi há mais de uma década (quero dizer, ainda estou usando o DBCC InputBuffer) para encontrar esses problemas irritantes - se necessário (execute em um teste/desenvolvimento para ver se ele atende às suas necessidades). Embora certamente não seja o Rolls Royce das rotinas, ainda funciona para mim.

    Esse código basicamente cria uma tabela, BlockingProcesses, em um novo banco de dados DBADMIN para armazenar as informações de bloqueio. Dois procedimentos armazenados sp__Maint_BlockWatche sp__Maint_Blockingprocessestrabalham juntos para encontrar os bloqueadores. E um trabalho SQL é executado uma vez por minuto para verificar se há blocos. Você pode precisar alterar pequenas coisas no código abaixo, como o usuário que precisa executar o trabalho, possível agendamento, etc.

    CREATE DATABASE DBADMIN
GO

USE [dbadmin]
GO

SET ANSI_NULLS ON
GO   
SET QUOTED_IDENTIFIER ON
GO    
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[BlockingProcesses]
(
    [PK] [int] IDENTITY(1,1) NOT NULL,
    [last_batch] [datetime] NULL,
    [spid] [int] NULL,
    [BlockedTotal] [int] NULL,
    [LoginName] [varchar](128) NULL,
    [DBName] [varchar](128) NULL,
    [HostName] [varchar](128) NULL,
    [Program_Name] [varchar](255) NULL,
    [CPU] [bigint] NULL,
    [Physical_IO] [bigint] NULL,
    [Memusage] [bigint] NULL,
    [Open_tran] [int] NULL,
    [EventInfo] [varchar](max) NULL,
    [InsertTime] [smalldatetime] NULL,
    [Params] [varchar](500) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]    
GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[BlockingProcesses] 
   ADD DEFAULT (getdate()) FOR [InsertTime]
GO

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[sp__Maint_BlockWatch]
AS
    declare @blocker smallint

    DECLARE blocker_cursor CURSOR FOR 
         select distinct blocked 
         from sysprocesses 
         where blocked != 0

    OPEN blocker_cursor
    FETCH NEXT FROM blocker_cursor INTO @blocker

    WHILE (@@fetch_status <> -1)
    BEGIN
      IF (@@fetch_status = -2)
      BEGIN
  FETCH NEXT FROM blocker_cursor INTO @blocker
           CONTINUE
      END

  exec sp__Maint_Blockingprocesses @blocker

 FETCH NEXT FROM blocker_cursor INTO @blocker
    END
DEALLOCATE blocker_cursor


GO

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[sp__Maint_BlockingProcesses]    Script Date: 09/20/2016 13:31:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE procedure [dbo].[sp__Maint_BlockingProcesses]
@spid smallint
as
declare
@blockedTotal int, 
@loginame varchar(128), 
@dbid int,
@hostname varchar(128),
@program_name varchar(128),
@cpu bigint,
@physical_io bigint,
@memusage bigint,
@open_tran int,
@dbname varchar(128),
@EventInfo varchar(255),
@Last_batch datetime
create table #temp
(
 EventType varchar(50),
 Parameters int,
 EventInfo varchar(255)
)

insert into #temp
exec ('dbcc inputbuffer('+@spid+')')

select @EventInfo=eventinfo from #temp
select @blockedTotal= count(*) from sysprocesses where blocked = @spid
select @loginame=loginame, @dbid=dbid, @hostname=hostname, @program_name=program_name, @cpu=cpu,
@physical_io = physical_io, @memusage=[memusage], @open_tran=open_tran, @Last_batch=last_batch
from sysprocesses where spid=@spid
select @dbname = name from sysdatabases where dbid=@dbid
insert into dbadmin..blockingprocesses(last_batch,spid, BlockedTotal, LoginName, DBName, HostName, Program_Name, CPU, Physical_IO, Memusage, Open_tran, EventInfo)
values(@last_batch, @spid, @BlockedTotal, @LogiName, @DBName, @HostName, @Program_Name, @CPU, @Physical_IO, @Memusage, @Open_tran, @EventInfo)
drop table #temp



GO



USE [msdb]
GO

/****** Object:  Job [_Monitor Blocks]    Script Date: 09/20/2016 13:29:34 ******/
BEGIN TRANSACTION

DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 09/20/2016 13:29:34 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'_Monitor Blocks', 
        @enabled=1, 
        @notify_level_eventlog=2, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Look For Blocking]    Script Date: 09/20/2016 13:29:34 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Look For Blocking', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=1, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'exec sp__Maint_BlockWatch', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Run Once a Minute', 
        @enabled=1, 
        @freq_type=4, 
        @freq_interval=1, 
        @freq_subday_type=4, 
        @freq_subday_interval=1, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=0, 
        @active_start_date=20060918, 
        @active_end_date=99991231, 
        @active_start_time=0, 
        @active_end_time=235959
        --, @schedule_uid=N'bf518f11-c5ba-438a-8afd-e4e33e8dad1e'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO
    • 1

  2. Eu tenho a mesma vulnerabilidade ambiental, embora em menor escala: muitos usuários se conectam com seu front-end preferido, como MS Access via ODBC, bloqueando inadvertidamente tabelas SQL e exibições, mesmo quando o usuário tem SELECT ONLY.

    Felizmente, evitar muitos bloqueios parece viável seguindo uma ordem para que esses usuários também instalem o SQL Server 2012 SSMS. O pacote de instalação incluía "SQL Server Native Client 11.0". As conexões ODBC que usam esse driver têm uma nova opção de "intenção do aplicativo". Escolha 'READONLY' em vez do padrão 'READWRITE'. Voo doo?

    • -1

relate perguntas