June 12, 2014 at 4:00 am
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_IDint= null,
@PART_NUMBERnvarchar(50)= null,
@PHASE_NUMBERnvarchar(10)= null,
@TEST_NUMBERnvarchar(20) = null,
@PROCESS_ORDERnvarchar(50) = null,
@IS_VOIDbit= null,
@REGISTERED_FROMsmalldatetime= null,
@REGISTERED_TOsmalldatetime= null,
@TEST_STATUS_IDint= null,
@DEFECT_TYPE_IDint= null,
@DEFECT_POSITION_IDint= null,
@FILM_SIZE_IDint= null,
@OUT_RESULTintOUTPUT
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_IDOR @REGISTER_IDIS NULL) AND
(TR_Parts.PartNumber= @PART_NUMBEROR @PART_NUMBERIS NULL) AND
(TR_PartPhases.PhaseNumber= @PHASE_NUMBEROR @PHASE_NUMBERIS NULL) AND
(TR_TestConfiguration.ProcessOrder= @PROCESS_ORDEROR @PROCESS_ORDERIS NULL) AND
(TR_TestConfiguration.IsVoided= @IS_VOIDOR @IS_VOIDIS NULL) AND
(TR_TestConfiguration.DateRegistered>= @REGISTERED_FROMOR @REGISTERED_FROMIS NULL) AND
(TR_TestConfiguration.DateRegistered<= @REGISTERED_TOOR @REGISTERED_TOIS NULL) AND
(TR_Tests.TestStatusId= @TEST_STATUS_IDOR @TEST_STATUS_IDIS NULL) AND
((TestNumberPrefix +
TR_YearPrefixes.Prefix +
CONVERT ( varchar(10), TestNumber) +
'/' +
right('000' + CONVERT ( varchar(10), BatchReference),3))
LIKE '%' + @TEST_NUMBER + '%'OR @TEST_NUMBERIS 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); }
June 12, 2014 at 7:16 am
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?
June 12, 2014 at 7:20 am
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
June 12, 2014 at 7:26 am
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/[/url]
--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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 12, 2014 at 9:51 am
There are a few hints in here which might help you on your way:
ALTER PROCEDURE [dbo].[TR_SearchTests_Counter_v2]
@REGISTER_IDint= null,
@PART_NUMBERnvarchar(50)= null,
@PHASE_NUMBERnvarchar(10)= null,
@TEST_NUMBERnvarchar(20) = null,
@PROCESS_ORDERnvarchar(50) = null,
@IS_VOIDbit= null,
@REGISTERED_FROMsmalldatetime= null,
@REGISTERED_TOsmalldatetime= null,
@TEST_STATUS_IDint= null,
@DEFECT_TYPE_IDint= null,
@DEFECT_POSITION_IDint= null,
@FILM_SIZE_IDint= null,
@OUT_RESULTintOUTPUT
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_IDOR @REGISTER_IDIS NULL) AND
(p.PartNumber= @PART_NUMBEROR @PART_NUMBERIS NULL) AND
(pp.PhaseNumber= @PHASE_NUMBEROR @PHASE_NUMBERIS NULL) AND
(tc.ProcessOrder= @PROCESS_ORDEROR @PROCESS_ORDERIS NULL) AND
(tc.IsVoided= @IS_VOIDOR @IS_VOIDIS NULL) AND
(tc.DateRegistered>= @REGISTERED_FROMOR @REGISTERED_FROMIS NULL) AND
(tc.DateRegistered<= @REGISTERED_TOOR @REGISTERED_TOIS NULL) AND
(TR_Tests.TestStatusId = @TEST_STATUS_ID OR @TEST_STATUS_IDIS 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
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
June 13, 2014 at 4:14 am
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!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply