3

We have an in-house-developed web application with a very small database (~20MB) and small number of users (~20 max) it is unfortunately a very high-visibility application used by executives. They have found that late in the afternoon every week, when multiple users are making changes simultaneously, performance is EXTREMELY poor - sometimes taking 5 minutes to "save an update" which sometimes fails completely.

The table design is not ideal and I've recommended the developer make some changes which would take some time to implement. The main table has a number of varchar(max) columns in which they store delimited lists of users. All of the tables in the database have a single clustered index on an ID column - another thing I've recommended improving to the developer.

Some "projects" in this application are significantly slower than others, there is one in particular that is almost always slow to save, but outside the heavy-usage period, we can typically see queries perform well.

I am not a developer, but this query looks poor/inefficient to me as a DBA - this is typical of the application's "save project" queries. It looks auto-generated by some tool - the query is generated by the application and it passes parameters for each column in the table, it does not use stored procedures at all.

UPDATE Actions SET Name = @Name,
Start_Date = @Start_Date,
End_Date = @End_Date,
Status = @Status,
Comments = @Comments,
Comp_Date = @Comp_Date,
Owner = @Owner,
Owner_EmplID = @Owner_EmplId,
Status_Name = @Status_Name
WHERE ID = @ID;

The server is SQL 2012 Standard and it has 4 CPUs, 18GB RAM (pretty typical for our environment) - it does run other application DBs but the overall server load is "normal" during peak usage times for this application, and no jobs/loads are running in other DBs at peak usage time. I have not found any deadlocks or locking at all while we monitor peak usage performance.

We do have a Tableau report connected to this same database that's auto-generating some other poor reporting queries, the entire database has about 25,000 rows among all tables, and the query Tableau runs generates millions of rows; I suspect cross-joins in it somewhere but none of my peers want to focus on that aspect of this issue.

Do these update queries actually follow good development practice? Can you give any advice that would help me convince the developer to redesign some of this, or is there something I can do to improve performance without involving them?

Definition of the Actions table:

TABLE [dbo].[Actions](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Project_ID] [int] NULL,
    [Name] [varchar](1000) NULL,
    [Owner] [varchar](500) NULL,
    [Owner_Emplid] [varchar](500) NULL,
    [Comments] [varchar](max) NULL,
    [Start_Date] [date] NULL,
    [End_Date] [date] NULL,
    [Comp_Date] [date] NULL,
    [Status] [int] NOT NULL,
    [Status_Name] [varchar](50) NULL
  • I'm guessing when you say there is no locking you're talking about blocking? What about wait stats during this time? – MrTCS Aug 11 '17 at 14:34
  • That is a simple update. Maybe explicitly try taking a rowlock. – paparazzo Aug 11 '17 at 15:11
  • I don't see anything inherently problematic with that update statement, other than the likelihood that many/most of those fields aren't actually being changed on every edit. But as others have said, this is likely a locking issue, not a performance issue, per se. – BradC Aug 11 '17 at 15:22
  • You might get better performance with varchar(8000) if it fits – paparazzo Aug 11 '17 at 15:50
  • Comment formatting sucks, I put the Actions table definition in the body of the question. All of the ID columns are INT. There are several tables in the database but all are similar to this one, the "main table" has more varchar(max) fields. The developer has now agreed to change these to varchar(500) which is greater than the longest string stored in them. – Fred Shope Aug 11 '17 at 16:39
4

varchar(max) columns with delimited lists is an anti-pattern, particularly if you split those lists out. Properly designed relational tables provide much better performance. However, that is likely secondary to your performance problem.

I'd bet money on the problem being blocking. From Microsoft:

Blocking is an unavoidable characteristic of any relational database management system (RDBMS) with lock-based concurrency. On SQL Server, blocking occurs when one SPID holds a lock on a specific resource and a second SPID attempts to acquire a conflicting lock type on the same resource.

Blocking is probably occurring when the Tableau queries take a long time to complete, blocking the updates from occurring, and creating the impression of slowness.

Brent Ozar has an excellent set of tools that can help understand performance problems. Specifically around blocking, this page contains a load of great resources.

By the way, it looks to me like that update statement in your question looks perfectly well-formed to me. I see nothing wrong with it as shown. If you provide query plans for the queries and update statements that are problematic, I'll provide more definitive advice.

  • 2
    If it turns out to be blocking, consider switching READ_COMMITTED_SNAPSHOT on, on the database. This will eliminate blocking between an INSERT or UPDATE and a reporting query . See technet.microsoft.com/en-us/library/ms175095(v=sql.105).aspx – David Browne - Microsoft Aug 11 '17 at 17:42
  • True. I've used READ_COMMITTED_SNAPSHOT with great success. I'm a little reluctant to just flat-out recommend it without understanding the precise impact on the OPs workload. – Max Vernon Aug 11 '17 at 17:46
  • Yes it's something that the developer needs to approve (and test). Performance is rarely the issue, rather it's queries that the developer expects to block, suddenly don't. Eg if the application selects a row and immediately tries to update that row, the select now not block if another session is in the middle of an update. – David Browne - Microsoft Aug 11 '17 at 17:51
  • Thanks @DavidBrowne-Microsoft, I have recommended that and we're investigating. We did find some connections from an old Tableau development server that appeared to be silently blocking (wasn't showing up when I ran a blocking query but the BI team noticed the hostname of the connection) so we're re-testing now that we've disabled that. – Fred Shope Aug 14 '17 at 13:48
2

That update is probably not the problem.

There are probably some selects that are holding a read lock and it takes a while for it to get an update lock.

Look at the selects that are used the most. Does the application hold on for example process DataReader that leaves the select active?

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.