August 17, 2009 at 2:29 pm
I have a query that when it is executed it comes up using sP_who 5 times with some connections being suspended and others being runnable and one being blocked by another. See below
SPIDStatusLoginHostNameBlkByDBNameCommandCPUTimeDiskIOLastBatchProgramNameSPIDREQUESTIDREQUEST
61 SUSPENDED AdvanceStoreUserKOPWWW08 .AdvanceStoreSELECT 01355008/17 16:27:12.Net SqlClient Data Provider 61 0 AdvanceStore.dbo.mpi_RecentlyViewedItems_s;1
61 RUNNABLE KOPWWW08 .AdvanceStoreSELECT 63008/17 16:27:12.Net SqlClient Data Provider 61 0 AdvanceStore.dbo.mpi_RecentlyViewedItems_s;1
61 RUNNABLE KOPWWW08 .AdvanceStoreSELECT 94008/17 16:27:12.Net SqlClient Data Provider 61 0 AdvanceStore.dbo.mpi_RecentlyViewedItems_s;1
61 RUNNABLE KOPWWW08 .AdvanceStoreSELECT 0008/17 16:27:12.Net SqlClient Data Provider 61 0 AdvanceStore.dbo.mpi_RecentlyViewedItems_s;1
61 RUNNABLE KOPWWW08 .AdvanceStoreSELECT 47008/17 16:27:12.Net SqlClient Data Provider 61 0 AdvanceStore.dbo.mpi_RecentlyViewedItems_s;1
The query that is running is as follows
CREATE PROCEDURE [dbo].[mpi_RecentlyViewedItems_s]
@user-id int = NULL,
@CookieID int = NULL,
@SiteVersionID smallint = 3
AS
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
/*This is the same as the NOlock hint on each table. Please use this when you do not need to lock the tables you are using*/
BEGIN
if not (@UserID is null and @CookieID is null)
begin
declare @max-2 tinyint
select @max-2 = CAST(SettingValue as tinyint)
from mpi_webappsettingValue SV
JOIN mpi_WebAppSetting S ON SV.WebAppSettingID = S.ID
where settingname = 'RecentlyViewedItemsLength'
AND SV.SiteVersionID = @SiteVersionID
set rowcount @max-2
SELECT
ProductID
FROM
mpi_RecentlyViewedItem RVI
JOIN mpi_SiteVersion SV ON RVI.SiteID = SV.[SiteID]
WHERE
UserID = ISNULL(@UserID, UserID) AND
CookieID = ISNULL(@CookieID, CookieID)
AND SV.ID = @siteVersionID
ORDER BY
LastViewed desc
set rowcount 0
end
END
SET NOCOUNT OFF;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
Does anyone know what would possibly be causing this issue?
August 17, 2009 at 3:18 pm
There's nothing wrong there.
The query is running in parallel, some of the threads finish faster thatn others and have to wait for those slower threads to catch up. This shows up as the thread blocking itself. The status is suspended because any thread that's waiting for something is suspended.
If you're seeing this occasionally, there's nothing to worry about. If you're seeing it often or if the app is an OLTP app (rather than decision support/data warehouse) then you may want to consider changing the server's maximum degree of parallelism and reducing the number of procs that threads can use.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply