Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sproc fails to return data to app after a couple of weeks Expand / Collapse
Author
Message
Posted Thursday, June 12, 2014 4:00 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 13, 2014 4:01 AM
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); }

Post #1579941
Posted Thursday, June 12, 2014 7:16 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:37 AM
Points: 2,257, Visits: 2,719
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’! **
Post #1580048
Posted Thursday, June 12, 2014 7:20 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 42,812, Visits: 35,931
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 2008, MVP
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

Post #1580053
Posted Thursday, June 12, 2014 7:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 13,296, Visits: 12,147
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)
Post #1580058
Posted Thursday, June 12, 2014 9:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 7,208, Visits: 13,669
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
Post #1580139
Posted Friday, June 13, 2014 4:14 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 13, 2014 4:01 AM
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!
Post #1580433
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse