Extremely slow SQL server
-
HI all
I am not much of a DBA but find myself in a situation where I need to learn really fast.We have a SQL Server running SQL Express (I know, problem number 1)
This server is also running a Domain controller and generally everything in the business at this stage.
I have got the company to purchase new servers to separate all of this and to just get everything configured properly. ( and SQL Server standard! :D)
The scenario is that we have SQL execution timeouts occurring, interrupting the operation of the web portal we are running (on this same box)
The database file size is 9.64GB (close to the limit) and the transaction log file is 25GB (bulk-logged recovery model).We are experiencing Buffer I/O waits and logging waits.
The disk that the database is stored on often hits disk queue lengths higher than 10 and can even go over 30 and 40.
The database is currently stored on a 3 disk RAID 5 array.
I'm not sure what more I can provide, but the big issue is that a specific part of the application is slow and keeps timing out. Would it help to post the query? -
What other applications run on the server? How much memory is in the server and what is max server memory set to in sys.configurations?
John
-
Hi John
The server runs Pastel, some backup software, the IIS software, DNS, DHCP, Domain controller. They typical everything on one server of a small company, but we have grown so much that this original configuration is not going to struggle along much longer.
The server has 48GB of memory and the max in sys.configuration is 16000MB
As per task manager:- In use: 12.2GB
- Committed: 13.8/54.9GB
- Cached: 35.7GB
- Paged Pool: 1.2GB
- Non-Paged pool: 827MB
-
Sounds as if you may have a disk bottleneck. But I think you need a slightly more detailed analysis of your wait stats. Paul Randal's and Brent Ozar's blogs have some good information on that. Meanwhile, yes, please post the query and its execution plan (actual if possible). How often does it run and how long does it take?
John
- John Mitchell-245523 - Monday, March 12, 2018 9:30 AM
So I ran this:
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time
It gave me this result:
This is the top Query(execution plan is attached as insert.sqlplan)(I see in the execution plan that clustered index insert took most of the time.):INSERT INTO ManifestDetail (ManifestID,WaybillID,SupplierID,WaybillNo,ClientID,Consignee,ServiceID,OriginID,DestID,NoParcels,Mass,UsersID,ComputerName,DateCreated,Sender,SenderRef,VolMass)
SELECT ManifestHeader.ManifestID AS [ManifestID],
WaybillHeader.WaybillID,
WaybillHeader.SupplierID,
WaybillHeader.WaybillNo,
WaybillHeader.ClientID,WaybillHeader.Rname AS Consignee,
WaybillHeader.ServiceID,
WaybillHeader.OriginID,
WaybillHeader.DestID,ISNULL(WaybillHeader.NoParcels,0) AS NoParcels,
ISNULL(WaybillHeader.ActMass,0) AS Mass,
WaybillHeader.UsersID,
WaybillHeader.ComputerName,
WaybillHeader.DateCreated,
WaybillHeader.Sname AS Sender,
WaybillHeader.SenderRef,
WaybillHeader.VolMass
FROM WaybillHeader (NOLOCK) INNER JOIN ManifestHeader (NOLOCK) ON ManifestHeader.ManifestNo=@ManifestNo WHERE WaybillHeader.WaybillID = @WBIDThis is the second query (attached as update.sqlplan):
UPDATE WaybillStatus SET StageNo=5,CurrentStatus='Linehaul Outbound',MainAgentID=i.SupplierID,Manifest1ID=i.ManifestID,Manifest1No=MH.ManifestNo,Manifest1Date=MH.ManifestDate,Manifest1AgentID=MH.SupplierID,Manifest1Driver=MH.DriverName,Manifest1RegNo=MH.FlightRegNo,Manifest1TrailerNo=MH.TrailerRegNo1,Manifest1Status='Linehaul Outbound',Manifest1Comment=ISNULL(i.Comment,MH.Comment) FROM Inserted i INNER JOIN ManifestHeader MH ON i.ManifestID=MH.ManifestID WHERE WaybillStatus.WaybillID=i.WaybillID -
Do you have the option to change code? The SelectWaybillID stored procedure uses a cursor - probably dreadfully inefficient. Please will you post the full definition of the stored procedure, and also of the trigger trig_manifest on the ManifestDetail table?
John
-
I cannot change it myself, but I can give my recommendations to the developer and they would change it for me.
The first query is the biggest problem, it takes forever to try that action within the software and is usually times out and never completes. -
You've got some pretty nasty problems. First, your transaction log file is 2 and 1/2 times bigger than the data fill. That probably means that the log file isn't be truncated. Make sure your backup software is actually working correctly.
The graphic with the number of reads may be leading you in the wrong direction because the data is cumulative from the last reboot, the last time the proc cache was cleared, or if there's something that causes a recompile on the given proc. To get a better feel for what's going on, you should include that date and time when the proc was last compiled.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Monday, March 12, 2018 11:14 AM
The server was last rebooted 3 days ago and I got those stats just before posting.
How can I go about making sure that the log file gets truncated properly? is there a good resource for Database backups here?
My biggest concern here is the performance hit and in a couple weeks everything will be moved to a new, dedicated SQL server with SQL standard which will give me some breathing room to get all these issues properly rooted out.
If you see the screenshot below of the first query's execution plan it says the Fetch Status is holding up the whole batch and the clustered index insert is 96% of that cost. That seems pretty bad to me. Should I resolve that and how can I? - troys - Monday, March 12, 2018 1:04 PMJeff Moden - Monday, March 12, 2018 11:14 AM
The server was last rebooted 3 days ago and I got those stats just before posting.
How can I go about making sure that the log file gets truncated properly? is there a good resource for Database backups here?
My biggest concern here is the performance hit and in a couple weeks everything will be moved to a new, dedicated SQL server with SQL standard which will give me some breathing room to get all these issues properly rooted out.
If you see the screenshot below of the first query's execution plan it says the Fetch Status is holding up the whole batch and the clustered index insert is 96% of that cost. That seems pretty bad to me. Should I resolve that and how can I?We need to see the definition of the stored procedure to help you with that, as I requested yesterday.
John
- John Mitchell-245523 - Monday, March 12, 2018 10:05 AMJohn, sorry about that, I missed that part of your message.
This is the stored procedure( a few hours ago I showed the developer the execution plan and how long the one part took, we removed the @@FETCH that was in there):
SET ANSI_NULLS ONEND
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SelectWaybillID]
(
@ManifestNo VARCHAR(20),
@TableWaybillID AS dbo.WaybillIDTableType READONLY
)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO ManifestDetail (ManifestID,WaybillID,SupplierID,WaybillNo,ClientID,Consignee,ServiceID,OriginID,DestID,NoParcels,Mass,UsersID,ComputerName,DateCreated,Sender,SenderRef,VolMass)
SELECT ManifestHeader.ManifestID AS [ManifestID],
WaybillHeader.WaybillID,
WaybillHeader.SupplierID,
WaybillHeader.WaybillNo,
WaybillHeader.ClientID,WaybillHeader.Rname AS Consignee,
WaybillHeader.ServiceID,
WaybillHeader.OriginID,
WaybillHeader.DestID,ISNULL(WaybillHeader.NoParcels,0) AS NoParcels,
ISNULL(WaybillHeader.ActMass,0) AS Mass,
WaybillHeader.UsersID,
WaybillHeader.ComputerName,
WaybillHeader.DateCreated,
WaybillHeader.Sname AS Sender,
WaybillHeader.SenderRef,
WaybillHeader.VolMass
FROM WaybillHeader (NOLOCK) INNER JOIN ManifestHeader (NOLOCK) ON ManifestHeader.ManifestNo = @ManifestNo WHERE WaybillHeader.WaybillID IN (SELECT ID FROM @TableWaybillID)This is the manifest add trigger. I just pasted it as is, so unfortunately not as nicely formatted as the Stored Procedure.
GO
/****** Object: Trigger [dbo].[trig_Manifest_Add] Script Date: 2018/03/13 12:15:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trig_Manifest_Add] ON [dbo].[ManifestDetail] FOR INSERT AS Begin DECLARE @ManType INT; SELECT @ManType=ISNULL(MH.ManifestType,0) FROM Inserted i INNER JOIN ManifestHeader MH ON i.ManifestID=MH.ManifestID IF (@ManType IN (0,3)) Begin UPDATE WaybillStatus SET StageNo=5,CurrentStatus='Linehaul Outbound',MainAgentID=i.SupplierID,Manifest1ID=i.ManifestID,Manifest1No=MH.ManifestNo,Manifest1Date=MH.ManifestDate,Manifest1AgentID=MH.SupplierID,Manifest1Driver=MH.DriverName,Manifest1RegNo=MH.FlightRegNo,Manifest1TrailerNo=MH.TrailerRegNo1,Manifest1Status='Linehaul Outbound',Manifest1Comment=ISNULL(i.Comment,MH.Comment) FROM Inserted i INNER JOIN ManifestHeader MH ON i.ManifestID=MH.ManifestID WHERE WaybillStatus.WaybillID=i.WaybillID End ELSE IF (@ManType=1) Begin UPDATE WaybillStatus SET StageNo=8,CurrentStatus='Out On Delivery',DelAgentID=i.SupplierID,TripID=i.ManifestID,TripNo=MH.ManifestNo,TripDate=MH.ManifestDate,TripAgentID=MH.SupplierID,TripDriver=MH.DriverName,TripRegNo=MH.FlightRegNo,TripStatus='Out On Delivery',Manifest1Comment=ISNULL(i.Comment,MH.Comment) FROM Inserted i INNER JOIN ManifestHeader MH ON i.ManifestID=MH.ManifestID WHERE WaybillStatus.WaybillID=i.WaybillID End ELSE Begin UPDATE WaybillStatus SET StageNo=4,CurrentStatus='Collection Received',ColAgentID=i.SupplierID,ColManifestID=i.ManifestID,ColManifestNo=MH.ManifestNo,ColRecDate=MH.ManifestDate,ColDriver=ISNULL(ColDriver,MH.DriverName),ColRegNo=ISNULL(ColRegNo,MH.FlightRegNo),ColStatus='Collection Received at Warehouse',ColComment=ISNULL(i.Comment,MH.Comment) FROM Inserted i INNER JOIN ManifestHeader MH ON i.ManifestID=MH.ManifestID WHERE WaybillStatus.WaybillID=i.WaybillID End EndThis is the Manifest Delete Trigger
GO
/****** Object: Trigger [dbo].[trig_Manifest_Delete] Script Date: 2018/03/13 12:20:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trig_Manifest_Delete] ON [dbo].[ManifestDetail] FOR DELETE AS Begin DECLARE @ManType INT; SELECT @ManType=ISNULL(MH.ManifestType,0) FROM Inserted i INNER JOIN ManifestHeader MH ON i.ManifestID=MH.ManifestID IF (@ManType IN (0,3)) Begin UPDATE WaybillStatus SET StageNo=5,CurrentStatus='Removed from Linehaul Manifest',Manifest1Status='Removed from Linehaul Manifest '+convert(varchar(10),GETDATE(),103)+' '+convert(varchar(10),GETDATE(),108) WHERE WaybillStatus.WaybillID IN (SELECT deleted.WaybillID FROM deleted) End ELSE IF (@ManType=1) Begin UPDATE WaybillStatus SET StageNo=8,CurrentStatus='Removed from Tripsheet',TripStatus='Removed from Tripsheet '++convert(varchar(10),GETDATE(),103)+' '+convert(varchar(10),GETDATE(),108) WHERE WaybillStatus.WaybillID IN (SELECT deleted.WaybillID FROM deleted) End ELSE Begin UPDATE WaybillStatus SET StageNo=4,CurrentStatus='Removed from Collection Manifest',TripStatus='Removed from Collection Manifest '++convert(varchar(10),GETDATE(),103)+' '+convert(varchar(10),GETDATE(),108) WHERE WaybillStatus.WaybillID IN (SELECT deleted.WaybillID FROM deleted) End End -
That's not the whole stored procedure definition, is it? The execution plan shows that there's a cursor involved. Also, do you have an example of the table parameter (@TableWaybillID) that is passed to the procedure, please?
John
-
Hi John
The @@Fetch was the cursor from the query was removed, this is the original query as per the execution plan:
/******Object: StoredProcedure [dbo].[SelectWaybillID] ScriptDate: 2018/03/13 12:57:31 ******/
SET ANSI_NULLSON
GO
SET QUOTED_IDENTIFIERON
GO
ALTER PROCEDURE[dbo].[SelectWaybillID]
(
@ManifestNo VARCHAR(20),
@TableWaybillID AS dbo.WaybillIDTableTypeREADONLY
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @WBID VARCHAR(MAX)
DECLARE ManifestCursor CURSOR FAST_FORWARD FORSELECT ID FROM @TableWaybillID
OPEN ManifestCursor
FETCH NEXT FROM ManifestCursor INTO @WBID
WHILE @@FETCH_STATUS= 0
BEGIN
INSERT INTO ManifestDetail(ManifestID,WaybillID,SupplierID,WaybillNo,ClientID,Consignee,ServiceID,OriginID,DestID,NoParcels,Mass,UsersID,ComputerName,DateCreated,Sender,SenderRef,VolMass)
SELECT ManifestHeader.ManifestID AS[ManifestID],
WaybillHeader.WaybillID,
WaybillHeader.SupplierID,
WaybillHeader.WaybillNo,
WaybillHeader.ClientID,WaybillHeader.Rname AS Consignee,
WaybillHeader.ServiceID,
WaybillHeader.OriginID,
WaybillHeader.DestID,ISNULL(WaybillHeader.NoParcels,0) AS NoParcels,
ISNULL(WaybillHeader.ActMass,0) AS Mass,
WaybillHeader.UsersID,
WaybillHeader.ComputerName,
WaybillHeader.DateCreated,
WaybillHeader.Sname AS Sender,
WaybillHeader.SenderRef,
WaybillHeader.VolMass
FROM WaybillHeader (NOLOCK) INNER JOINManifestHeader (NOLOCK) ON ManifestHeader.ManifestNo=@ManifestNoWHERE WaybillHeader.WaybillID = @WBID
END
CLOSE ManifestCursor
DEALLOCATE ManifestCursor
END
--CREATETYPE dbo.WaybillIDTableType AS TABLE
--(
-- ID INT
--)
-
You can't just remove the cursor, because now the stored procedure isn't doing the same thing. It's inserting just one row instead of all the rows. You need to rewrite the INSERT statement so that it inserts all the rows as a single set. Before you do that, though, you need to rewrite your triggers, since you'll get an error as soon as you attempt to insert more than one row in a single operation. I think we're at the stage where it would be best for you to get someone in to help you with this.
John
- John Mitchell-245523 - Tuesday, March 13, 2018 6:10 AM
Thanks John, I appreciate the input.
The developer removed the cursor and changed it to keep the same functionality.
My point was that the cursor was slowing everything down and doing the function without the cursor has made a great performance difference.Thank you for your assistance.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply