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