6

We have an ERP system that is a little less than modern, the backend is currently sitting on a SQL Server 2008 R2 installation. We have through mandate of management (isn't that always the case) the completely impractical practice of allowing many users to extract data from this system at will for the purposes of manipulating the data into their preferred flavor of reporting (BI, Powerquery, Access, Excel, etc)

Though all of these report generating users only have select rights, there are several other applications that have the ability to actually manipulate data for the purposes of doing things like EDI, automated data entry.

Logically this creates a number of race conditions on the tables. First come first serve, or worse still, still serving, please wait, or just NO... I believe it is further causing what has been years of previously unexplained data corruption requiring vendor assistance to correct. Because not all of the applications are properly coded to anticipate the chance that something else may have interrupted its own private intent.

So the task is to prove/correct/mitigate it where possible on a system I only recently inherited.

My first instinct tells me it cannot be done, without some sort of IPC/error/transaction control between different vendor's products and their will to fix it, control over source code, or ability to restrict it, that this CANNOT be fixed from a DBA perspective, it is simply bad practices leading to what should be anticipated as potentially unwanted side effects.

So all I can do at this point is prove it, and see that if with proof, I can solicit some sort of behavioral change in the hearts of the management that insists it has to be this way.

First step, is that I am running a continuous profiler output to disk, albeit resource intensive, I have to be able to go back and look at what has been submitted, by whom, and what impact it had. I am also using perfmon logging and PAL to try and cross reference intensive behavior on the server, "when the processor and memory are pegged, what was the SQL server doing at that instance (most likely servicing one of the GUI written, poorly performing, ten table joining, non indexed queries, that some report writer/tool tossed together"

One of those situations just occurred, we had a user in the ERP system that could not perform a function, we identified the user logged into the database that was not using the ERP software (Was using ODBC, SQL user with select only access, and MS Access), had them disconnect and the function went through. Management refuses to believe they are related because they "Should have been dealing with different tables, and the user only had select access" and I have no profile history of the instant just before it happened.

So all of that boils down to a question to the DBA fellows out there, given the task of PROVING that application A is being adversely affected by application B, what suggestions would you have to offer?

  • 4
    Can you not create a secondary for people to do these large reads off of (Either a snapshot, or another actual instance with replication or log shipping)? Or try using Read Committed Snapshot Isolation and see if that help alleviate some of the contention. – Chad Mattox Jul 6 '16 at 19:52
  • To get the proof, I would suggest using SQLDIAG (diagmanager.codeplex.com) to capture the data including blocking chains and then using SQL Nexus (sqlnexus.codeplex.com) to visualize this data which can then be easily-ish presented to your management. If it's blocking that's causing most of this, depending on the type, I agree with @ChadMattow that RCSI would be very useful for you. – Sean Gallardy Jul 6 '16 at 20:11
  • The two database model is what I have pretty much told them they will have to go with to do this the way they would like. And that for that there will be a delay and not 100% live data. I am looking into the optimistic locking, however if there is any chance it will stop the ERP system, I will have to shy in favor of not introducing new issues. Thank you for the input, sorry for just getting back, it has been a long few days. – Sabre Jul 11 '16 at 17:46
1

Unfortunately, we've dealt with too may issues with this where I work. In cases where this has been a problem, people who don't know databases, literally open a SQL table "live" in MS Access. At first things can appear transient because they may open and close Access quickly. But then there are those moments where they leave MS Access open on the live SQL table while they go away for lunch or perhaps through the long weekend--blocking applications that try to use the database. Just to state the obvious--blocking can adversely affect any application trying to use the blocked SQL row, table, or database.

All we really had to do to prove anything was capture data from sp_who2 to find the blocking chains.

That being said, when I'm away from the console, I still use the following code I wrote over a decade ago (I mean I'm still using DBCC InputBuffer) to find these pesky issues -- if needed (please run in a test/dev environment to see if it meets your needs). Though certainly not the Rolls Royce of routines it still works for me.

This code essentially creates a table, BlockingProcesses, in a new database DBADMIN to store the blocking information. Two stored procedures sp__Maint_BlockWatch and sp__Maint_Blockingprocesses work together to find the blockers. And a SQL job runs once a minute to check for blocks. You may need to change minor things in the code below like the user who needs to execute the job, possible scheduling, 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
  • That is why I love this place, there is always more hope, perspective, and experience out there somewhere, I will not be able to test until week after next, however I will certainly do so and get back to you! Thank you. – Sabre Sep 23 '16 at 22:56
-1

I have same environmental vulnerability, albeit at smaller scale: Many users connect with their preferred front-end such as MS Access via ODBC, inadvertently locking SQL tables and views even when user has SELECT ONLY.

Happily much avoidance of blocks seems feasible following a mandate that these users also install SQL Server 2012 SSMS. The install package included "SQL Server Native Client 11.0". ODBC Connections that use this driver have a new "Application Intent" option. Choose 'READONLY' instead of default 'READWRITE'. Voo doo?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.