Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sproc fails to return data to app after a couple of weeks


Sproc fails to return data to app after a couple of weeks

Author
Message
n00bie
n00bie
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 20
Hi all,

Apologies if this is in the wrong place... or I'm missing something obvious (I have searched the interwebs for days)...

I've a sproc for searching, which I spent a week optimising so it didn't take hours... as a result it only returns a single requested page of data. I'm quite proud of it. Then I have a separate sproc which counts how many results there are, with a limit of "more than 5000", as otherwise the count takes too long.

I load tested the system 3 years ago with 7 years of data before I got bored and concluded that 7 was enough. Recently usage has dramatically increased (it was doing well so now more users have jumped onboard), and so it's beyond it's original testing. That's all possibly irrellevant.

So the COUNT sproc is now failing. I've created an isolated server with disabled windows update (I know, 32bit not ideal), and outside of our corporate domain:

Win2008r2-64bit.
SQL2005-32bit-SP3.

I can get a bit more life out of it by restarting the MSSQL service.

But what's weird is that it initially fails after 2 weeks, then a few days, then a few hours, then down to 10 minutes.

The failure is strange too... an asp.net app calling the sproc with textbook code, from a different server. If the failing application is pointed to a different server, then the application works. Also if a separate installation of the application is pointed to the faulting database, then the app fails. So it seems to be at the database end. More frustratingly... if the sproc is executed directly on the server when it's in a failing state, it works (approx 2-3 secs). When called from the application though, the database server maxes out the CPU for 30 seconds, and the app never recieves the reply. The db server is also using less than 1gb of it's 4gb.

Does anybody have any ideas?

The sproc is below, apologies if it's a horror... SQL is not really my bag. The C# code is also below (originally it used EntityFramework, but I've re-coded to ADO.net to eliminate that).

USE [TestRegister_Production]
GO
/****** Object: StoredProcedure [dbo].[TR_SearchTests_Counter_v2] Script Date: 06/12/2014 09:06:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[TR_SearchTests_Counter_v2]
@REGISTER_ID int = null,
@PART_NUMBER nvarchar(50) = null,
@PHASE_NUMBER nvarchar(10) = null,
@TEST_NUMBER nvarchar(20) = null,
@PROCESS_ORDER nvarchar(50) = null,
@IS_VOID bit = null,
@REGISTERED_FROM smalldatetime = null,
@REGISTERED_TO smalldatetime = null,
@TEST_STATUS_ID int = null,
@DEFECT_TYPE_ID int = null,
@DEFECT_POSITION_ID int = null,
@FILM_SIZE_ID int = null,
@OUT_RESULT int OUTPUT

AS
BEGIN
SET NOCOUNT ON;

-- >>> Reset empty values to null (fix for entity framework sending empty strings instead of nulls)
if @PART_NUMBER = ''
SET @PART_NUMBER = null
if @PHASE_NUMBER = ''
SET @PHASE_NUMBER = null
if @TEST_NUMBER = ''
SET @TEST_NUMBER = null
if @PROCESS_ORDER = ''
SET @PROCESS_ORDER = null

-- Returns distinct(/grouped by) TestId's matching search Criteria
select
@OUT_RESULT = count(a.TestId)
-- count(a.TestId) as ResultCount
from
(
SELECT top 5000
TR_Tests.TestId

FROM
TR_Tests
INNER JOIN TR_TestConfiguration ON TR_TestConfiguration.TestConfigurationId = TR_Tests.TestConfigurationId
INNER JOIN TR_Registers ON TR_TestConfiguration.RegisterId = TR_Registers.RegisterId
INNER JOIN TR_PartPhases ON TR_TestConfiguration.PartPhaseId = TR_PartPhases.PartPhaseId
INNER JOIN TR_Parts ON TR_PartPhases.PartId = TR_Parts.PartId
INNER JOIN TR_YearPrefixes ON DATEPART(year, DateRegistered) = TR_YearPrefixes.Year
WHERE
(
(@FILM_SIZE_ID IS NULL )
OR (
@FILM_SIZE_ID IS NOT NULL AND
TR_Tests.TestId IN
(
SELECT
TR_TestResults.TestId
FROM
TR_TestResults
WHERE
(
(@FILM_SIZE_ID IS NULL )
OR (
@FILM_SIZE_ID IS NOT NULL AND
EXISTS(
SELECT FilmSizeId FROM TR_FilmSizes_InTestResults
WHERE TR_TestResults.TestResultId = TR_FilmSizes_InTestResults.TestResultId
AND TR_FilmSizes_InTestResults.FilmSizeId = @FILM_SIZE_ID
)
)
)
)
)
) AND
(
(@DEFECT_TYPE_ID IS NULL )
OR (
@DEFECT_TYPE_ID IS NOT NULL AND
TR_Tests.TestId IN
(
SELECT
TR_TestResults.TestId
FROM
TR_TestResults
WHERE
(
(@DEFECT_TYPE_ID IS NULL )
OR (
@DEFECT_TYPE_ID IS NOT NULL AND
EXISTS(
SELECT DefectTypeId FROM TR_Defects_InTestResults
WHERE TR_TestResults.TestResultId = TR_Defects_InTestResults.TestResultId
AND TR_Defects_InTestResults.DefectTypeId = @DEFECT_TYPE_ID
)
)
)
)
)
) AND
(
(@DEFECT_POSITION_ID IS NULL )
OR (
@DEFECT_POSITION_ID IS NOT NULL AND
TR_Tests.TestId IN
(
SELECT
TR_TestResults.TestId
FROM
TR_TestResults
WHERE
(
(@DEFECT_POSITION_ID IS NULL )
OR (
@DEFECT_POSITION_ID IS NOT NULL AND
EXISTS(
SELECT DefectPositionId FROM TR_Defects_InTestResults
WHERE TR_TestResults.TestResultId = TR_Defects_InTestResults.TestResultId
AND TR_Defects_InTestResults.DefectPositionId = @DEFECT_POSITION_ID
)
)
)
)
)
) AND
(TR_TestConfiguration.RegisterId = @REGISTER_ID OR @REGISTER_ID IS NULL) AND
(TR_Parts.PartNumber = @PART_NUMBER OR @PART_NUMBER IS NULL) AND
(TR_PartPhases.PhaseNumber = @PHASE_NUMBER OR @PHASE_NUMBER IS NULL) AND
(TR_TestConfiguration.ProcessOrder = @PROCESS_ORDER OR @PROCESS_ORDER IS NULL) AND
(TR_TestConfiguration.IsVoided = @IS_VOID OR @IS_VOID IS NULL) AND
(TR_TestConfiguration.DateRegistered >= @REGISTERED_FROM OR @REGISTERED_FROM IS NULL) AND
(TR_TestConfiguration.DateRegistered <= @REGISTERED_TO OR @REGISTERED_TO IS NULL) AND
(TR_Tests.TestStatusId = @TEST_STATUS_ID OR @TEST_STATUS_ID IS NULL) AND
((TestNumberPrefix +
TR_YearPrefixes.Prefix +
CONVERT ( varchar(10), TestNumber) +
'/' +
right('000' + CONVERT ( varchar(10), BatchReference),3))
LIKE '%' + @TEST_NUMBER + '%' OR @TEST_NUMBER IS NULL)
) as a
END




try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();

SqlCommand command = new SqlCommand("TR_SearchTests_Counter_v2", conn);
command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add("@REGISTER_ID", SqlDbType.Int ).Value = param_registerId;
command.Parameters.Add("@PART_NUMBER", SqlDbType.NVarChar ).Value = param_partNumber;
command.Parameters.Add("@PHASE_NUMBER", SqlDbType.NVarChar ).Value = param_phaseNumber;
command.Parameters.Add("@TEST_NUMBER", SqlDbType.NVarChar ).Value = param_testNumber;
command.Parameters.Add("@PROCESS_ORDER", SqlDbType.NVarChar ).Value = param_PoNumber;
command.Parameters.Add("@IS_VOID", SqlDbType.Bit ).Value = param_IsVoided;
command.Parameters.Add("@REGISTERED_FROM", SqlDbType.SmallDateTime ).Value = param_RegisteredFrom;
command.Parameters.Add("@REGISTERED_TO", SqlDbType.SmallDateTime ).Value = param_RegisteredTo;
command.Parameters.Add("@TEST_STATUS_ID", SqlDbType.Int ).Value = param_StatusId;
command.Parameters.Add("@DEFECT_TYPE_ID", SqlDbType.Int ).Value = param_defectTypeId;
command.Parameters.Add("@DEFECT_POSITION_ID", SqlDbType.Int ).Value = param_defectPositionId;
command.Parameters.Add("@FILM_SIZE_ID", SqlDbType.Int ).Value = param_FilmSizeId;

SqlParameter out_result = command.Parameters.Add("@OUT_RESULT", SqlDbType.Int);
out_result.Direction = ParameterDirection.Output;

command.ExecuteNonQuery();
if(out_result != null)
_resultsCount = (int)out_result.Value;
}
}
catch (SqlException ex) { throw (ex); }
catch (Exception ex) { throw (ex); }


HanShi
HanShi
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3051 Visits: 3633
Do you get a specific error message or just a time-out of the query?
Can you look what is going on the SQL instance when the stored proc. fails? Is blocking involved? What are the wait-time, -type and -resource of the connection?

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47427 Visits: 44405
There's a bunch of problems with that procedure. Start with these two blog posts:

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
http://sqlinthewild.co.za/index.php/2008/05/22/parameter-sniffing-pt-3/

Fixing those two problems should help a lot.


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16673 Visits: 17033
That procedure has a lot of subqueries which is likely going to hurt. You are hitting the same table over and over. You also have some nonSARGable predicates in there. I am not sure you can avoid all of those because you appear to have some columns with multiple pieces of data. Last but not least you have a catch all query. Please take a look at this article which explains who to deal with this type of query.

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

--EDIT--

Seems Gail posted before I could. :-)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9028 Visits: 19041
There are a few hints in here which might help you on your way:
ALTER PROCEDURE [dbo].[TR_SearchTests_Counter_v2]
@REGISTER_ID int = null,
@PART_NUMBER nvarchar(50) = null,
@PHASE_NUMBER nvarchar(10) = null,
@TEST_NUMBER nvarchar(20) = null,
@PROCESS_ORDER nvarchar(50) = null,
@IS_VOID bit = null,
@REGISTERED_FROM smalldatetime = null,
@REGISTERED_TO smalldatetime = null,
@TEST_STATUS_ID int = null,
@DEFECT_TYPE_ID int = null,
@DEFECT_POSITION_ID int = null,
@FILM_SIZE_ID int = null,
@OUT_RESULT int OUTPUT

AS

BEGIN

SET NOCOUNT ON;

-- >>> Reset empty values to null (fix for entity framework sending empty strings instead of nulls)
if @PART_NUMBER = ''
SET @PART_NUMBER = null
if @PHASE_NUMBER = ''
SET @PHASE_NUMBER = null
if @TEST_NUMBER = ''
SET @TEST_NUMBER = null
if @PROCESS_ORDER = ''
SET @PROCESS_ORDER = null

-- Returns distinct(/grouped by) TestId's matching search Criteria
SELECT @OUT_RESULT = COUNT(a.TestId)
-- count(a.TestId) as ResultCount
FROM ( -- a
SELECT top 5000
t.TestId
FROM TR_Tests t
INNER JOIN TR_TestConfiguration tc
ON tc.TestConfigurationId = t.TestConfigurationId
INNER JOIN TR_Registers r
ON tc.RegisterId = r.RegisterId
INNER JOIN TR_PartPhases pp
ON tc.PartPhaseId = pp.PartPhaseId
INNER JOIN TR_Parts p
ON pp.PartId = p.PartId
INNER JOIN TR_YearPrefixes yp
ON DATEPART(year, tc.DateRegistered) = yp.Year -- non-SARGable predicate
CROSS APPLY ( -- x
SELECT TestNumberOut =
TestNumberPrefix +
yp.Prefix + -- can this be calculated? Then you could remove TR_YearPrefixes from the query
CONVERT(varchar(10), TestNumber) +
'/' +
RIGHT('000' + CONVERT(varchar(10), BatchReference),3)
) x
WHERE EXISTS ( -- d
SELECT 1
FROM TR_TestResults tr
INNER JOIN TR_FilmSizes_InTestResults fstr
ON tr.TestResultId = fstr.TestResultId
WHERE tr.TestId = t.TestId
AND (
(fstr.FilmSizeId = @FILM_SIZE_ID OR @FILM_SIZE_ID IS NULL)
OR (dtr.DefectTypeId = @DEFECT_TYPE_ID OR @DEFECT_TYPE_ID IS NULL)
OR (dtr.DefectPositionId = @DEFECT_POSITION_ID OR @DEFECT_POSITION_ID IS NULL)
)
) -- d
AND
(tc.RegisterId = @REGISTER_ID OR @REGISTER_ID IS NULL) AND
(p.PartNumber = @PART_NUMBER OR @PART_NUMBER IS NULL) AND
(pp.PhaseNumber = @PHASE_NUMBER OR @PHASE_NUMBER IS NULL) AND
(tc.ProcessOrder = @PROCESS_ORDER OR @PROCESS_ORDER IS NULL) AND
(tc.IsVoided = @IS_VOID OR @IS_VOID IS NULL) AND
(tc.DateRegistered >= @REGISTERED_FROM OR @REGISTERED_FROM IS NULL) AND
(tc.DateRegistered <= @REGISTERED_TO OR @REGISTERED_TO IS NULL) AND
(TR_Tests.TestStatusId = @TEST_STATUS_ID OR @TEST_STATUS_ID IS NULL) AND
(x.TestNumberOut LIKE '%' + @TEST_NUMBER + '%' OR @TEST_NUMBER IS NULL)
) a
OPTION (RECOMPILE) -- a new plan is generated each time the query is run

END

RETURN 0






“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
n00bie
n00bie
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 20
Thanks for the tips guys, I'll be plodding through them shortly with my fingers crossed!

I especially like the dynamic query one... that'd really make creation of such a queries easier in future! Probably make them much more readable too... sorry about such a nightmare of a sproc! The only defense I have is how rare it is I do anything other than the real basic CRUD stuff.

I'll see if re-working the catchall yeilds any joy, along with maybe throwing in the OPTION (RECOMPILE) pretty soonish as that seems quite a quick and easy avenue.

Thank you!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search