quote:

---

Originally posted by russell

are there a lot of inserts and/or updates?

try updating stats with fullscan

by the way, do stored procedures in question normally perform index seeks?

---

quote:

---

Originally posted by yosiasz

how are you calling the stored procedures from your web page? adodb or what? are you closing any connections to the stored procedure properly, cleaning up after use of stored procedure? show us your web code please.

---

quote:

---

Originally posted by yosiasz

you never know :O) also any underlying views that might be problematic. this sounds indeed like a very strange problem. I would do a trace from web page onward. Is this a new problem that just suddenly appeare. Has something changed in your environment on web server and sql backend? new updates anything...are you calling the tables directly in your sproc or are you using views. if not try using views and see what happens. has underlying table changed but views haev not been refreshed?

---

quote:

---

Originally posted by russell

there's lots of replies here...please go back and read Tara's reply. it is the correct one.

by the way, rebuilding indexes does in fact update stats but not with a fullscan.

---

/****** Object:  StoredProcedure [dbo].[AD_Branches_3]    Script Date: 12/23/2009 23:10:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[AD_Branches_3]
/*************************************************************************************************
                                          
Purpose:	Return recordset of Centers and Branches for a specific Client

exec dbo.AD_Branches_3 @CallerAdminID=17109,@CourseID=4532, @CenterID=0,@BranchID=0,@Visibility=0

*************************************************************************************************/
	@CallerAdminID	INT ,
	@CourseID	INT ,
	@CenterID	INT = NULL ,
	@BranchID	INT = NULL ,
	@Visibility	BIT = 0 , -- Set to 1 for User Update
	@Wildcard	INT = NULL 

AS
DECLARE @Adminlevel	INT, 
	@Admincenterid	INT, 
	@Adminbranchid	INT, 
	@ClientID	INT


-- Get the controlling parameters for this Administrator
SELECT	@ClientID = ClientID, @AdminLevel = AdminLevel, @AdminCenterID = CenterID, @AdminBranchID = BranchID
	FROM	dbo.Admins A
	WHERE	AdminID = @CallerAdminID

-- SET THE SELECTED CENTERID TO NULL IF IT IS GLOBAL
SET	@CenterID = 	CASE	WHEN @CenterID = 0
				THEN NULL 
				ELSE @CenterID 
			END
SET	@BranchID =	CASE	WHEN @BranchID = 0
				THEN NULL
				ELSE @BranchID
			END

-- RESTRICT VISIBILITY ACCORDING TO ADMINLEVEL
SET	@AdminCenterID = CASE	WHEN @AdminLevel <= 1 OR @Visibility = 1
				THEN NULL
				ELSE @AdminCenterID
			 END
SET	@AdminBranchID = CASE	WHEN @AdminLevel <= 2 OR @Visibility = 1
				THEN NULL 
				ELSE @AdminBranchID 
			 END

-- RETURN THE SCOPED RECORDSET
SELECT	B.BranchID, 
	B.Branchname + 
	'  (' + CAST(IsNull(A.TotalBranchID, 0) AS VARCHAR) + ')' As Branchname, 
	B.CenterID, 
	C.Centername

	FROM	dbo.Branches B
	LEFT OUTER JOIN (
		SELECT	BranchID, COUNT(*) As TotalBranchID		-- Count of assigned users for each BranchID
			FROM	dbo.Users U
			LEFT OUTER JOIN dbo.CourseAssignments CA
				ON	CA.CourseID = @CourseID
				AND	CA.UserID = U.UserID
				AND	CA.Assigned = 1
			WHERE	U.ClientID = @ClientID
			AND	U.Deleted = 0

			GROUP BY BranchID
		) A	ON	A.BranchID = B.BranchID


	INNER JOIN dbo.Centers C 
		ON	C.CenterID = B.CenterID 
		AND	C.ClientID = B.ClientID

	WHERE	B.ClientID = @ClientID
	AND	(B.Inactive = 0 OR B.BranchID = @BranchID) -- Include SELECTED BranchID even if Inactive
	AND	(@CenterID IS NULL OR B.CenterID = @CenterID)
	AND	(@AdminBranchID IS NULL 
	  OR	B.Branchname LIKE ( -- All branchnames wildcarded to match Admin's Branchname
				SELECT	Branchname
					FROM	dbo.Branches 
					WHERE	B.ClientID =@ClientID
					AND	(@AdminCenterID IS NULL
						OR B.CenterID = @AdminCenterID)
					AND	BranchID = @AdminBranchID)+'%')
	ORDER BY Centername, Branchname

quote:

---

Originally posted by SamC

...I suspect parameter sniffing could be related...

---

quote:

---

Originally posted by ehorn
http://elegantcode.com/2008/05/17/sql-parameter-sniffing-and-what-to-do-about-it/

---

• Why does the stored procedure timeout (for specific clients) when invoked from the web but run correctly for all clients within SSMS? This unanswered question appears both in my experience and in the link quoted above. What is it about web access passing EXACTLY identical parameters that causes the proc to do table scans? Wouldn't a skewed query plan also fail in SSMS?

quote:

---

Originally posted by SamC

quote:

---

Originally posted by ehorn
http://elegantcode.com/2008/05/17/sql-parameter-sniffing-and-what-to-do-about-it/

---

...The solution proposed above (indirect parameter references) may be a way out, but there are a lot of stored procedures making this quite a job. It also doesn't answer all the questions.

• Why does the stored procedure timeout (for specific clients) when invoked from the web but run correctly for all clients within SSMS? This unanswered question appears both in my experience and in the link quoted above. What is it about web access passing EXACTLY identical parameters that causes the proc to do table scans? Wouldn't a skewed query plan also fail in SSMS?

---