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

SELECT statement hangs on certain where conditions Expand / Collapse
Author
Message
Posted Thursday, April 16, 2009 3:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 25, 2010 3:41 AM
Points: 5, Visits: 15
I've encountered a bizarre issue today while investigating a job that suddenly stopped working.

To explain - I was recently working on and implemented an automated database maintenance script (result of going to the awesome SqlBits conference in Manchester). The script did it's job well - up until now there was the Sql Server 2005 inbuilt maintenance plain in use - the one that performs reorganize on selected tables but no rescan (no statistics update). The script would reorganize/rescan tables with average fragmentation between 10 and 30% and rebuild any with above 30% fragmentation. It also rescans anything below reorganize threshold.

I ran the script on our development database and it certainly sped some things up. On the down hand it also seems to have caused a particular job to hang all of a sudden. I've inspected the stored procedure and identified the problem being in a where statement condition:

tableName.createdDate < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

This seemed odd since executing the select from table alone with given condition worked fine. The column createdDate (not actual column name) was indexed with a non-clustered index that depended on a primary key (clustered) of type BigInt. The table had another non-clustered index but all of the indexes were up to date (I checked fragmentation levels and statistics last update).

Since this started happening after the maintenance script I first tried reorganizing/rebuilding the index in question and when that improved nothing the primary key (since that rebuilds any dependant non-clustered indexes).

Anyway when all of that produced no viable results someone suggested hard coding the problematic where condition - supprizingly it worked! I put he condition DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) into a DateTime varible and used the variable in the condition instead.

What I suspect might have caused the problem lies in the estimated query execution plan. Please mind that the actual select had several inner/left joins. The condition was one of the first things to happen according to query plan and it performed a non-clustered index scan. The table on which the index operates has around 1 million records which means the index to scan would be rather big. But even so it shouldn't hang because it worked fine isolated.

When I spoke with some other people who have been around when migration from Sql Server 2000 to 2005 took place they said same kind of problems stared occurring - selects stared hanging for no apparent reason. Could this be a bug in 2005? Has anyone else encountered this?

Please mind I'm not a fully qualified DBA and please do correct me if I'm wrong on any points.

I hope this helps anyone with similar issues. We didn't manage to fully explain what the problem is but a viable solution that works is to replace conditions with problems with variables.
Post #698237
Posted Thursday, April 16, 2009 3:30 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 9, 2011 2:49 AM
Points: 536, Visits: 412
Please provide your select query.........

"Don't limit your challenges, challenge your limits"
Post #698248
Posted Thursday, April 16, 2009 4:06 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 @ 9:11 AM
Points: 40,390, Visits: 36,826
Try updating the statistics on that table. Fullscan may be necessary.


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 #698267
Posted Thursday, April 16, 2009 4:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 25, 2010 3:41 AM
Points: 5, Visits: 15
I did not post the query as I can't post it as is and what I can post might not be useful at all. Find below a query with renamed tables/columns. I only posted this in case someone else comes across this problem as I've already found a workaround.

SELECT
A.a_id,
C.c_id,
B.b_id,
D.d_id,
E.e_id,
F.g_id
FROM table_G G
INNER JOIN
table_A A
ON A.a_id = G.a_id
INNER JOIN
table_C C
ON C.c_id = G.c_id
LEFT JOIN
table_B B
ON B.a_id = A.a_id
LEFT JOIN
table_D D
ON D.b_id = B.b_id AND D.c_id = G.c_id
LEFT JOIN
table_E E
ON E.e_id = COALESCE(D.e_id, C.e_id)
LEFT JOIN
table_F F
ON F.g_id = G.g_id
WHERE
F.g_id IS NULL
AND A.created < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

The problem line I spoke of was "AND A.created ..." - table Table_A contains around a million records. Simply removing that line worked and putting the "DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)" into variable and using variable instead also worked.
Post #698271
Posted Thursday, April 16, 2009 4:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 25, 2010 3:41 AM
Points: 5, Visits: 15
Thanks for suggestion but as I mentioned I already tried all of it 0 reorganize/full scan, rebuild on non-clustered index and then on clustered again - all of which made no difference. This only started happening after the script has already performed proper maintenance - in this case it only rescaned the table as it's initial fragmentation was 0.3 %.
Post #698272
Posted Thursday, April 16, 2009 4:19 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 @ 9:11 AM
Points: 40,390, Visits: 36,826
Any chance of seeing the estimated execution plan?


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 #698274
Posted Thursday, April 16, 2009 4:28 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 9, 2011 2:49 AM
Points: 536, Visits: 412
 DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

what is 'dd' in above statement? Any column name or a variable?


"Don't limit your challenges, challenge your limits"
Post #698280
Posted Thursday, April 16, 2009 4:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 25, 2010 3:41 AM
Points: 5, Visits: 15
The "dd" is a keyword argument to date functions (DATEADD, DATEDIFF) and it signifies the days to add for example. You could also add minutes, seconds, ...

What the whole thing you pointed out does it "zeroes out" the date. So if GetDate() for example returns "2009-04-16 12:35:59" the resulting date would be "2009-04-16 00:00:00". In the example that I posted we want to capture any records created up to today, excluding today - thus smaller than "zeroed out" current date.
Post #698282
Posted Friday, November 16, 2012 5:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 31, 2014 6:31 AM
Points: 107, Visits: 514
Try this...
declare @date datetime
set @date = round(cast(getdate() as float),0,1)
SELECT
A.a_id,
C.c_id,
B.b_id,
D.d_id,
E.e_id,
F.g_id
FROM table_G G
INNER JOIN
table_A A
ON A.a_id = G.a_id and a.Created < @date
INNER JOIN
table_C C
ON C.c_id = G.c_id
LEFT JOIN
table_B B
ON B.a_id = A.a_id
LEFT JOIN
table_D D
ON D.b_id = B.b_id AND D.c_id = G.c_id
LEFT JOIN
table_E E
ON E.e_id = COALESCE(D.e_id, C.e_id)
LEFT JOIN
table_F F
ON F.g_id = G.g_id
WHERE
F.g_id IS NULL


In general, it is better to put conditions (where possible) into inline views instead of the where clause. That way, the number of rows is determined before the potentially billions of other rows introduced by the joins are considered. Also, if you're computing a value, do it in a variable before you put it in a join - it's a kazillion times faster. Another good way would be
declare @date datetime
set @date = round(cast(getdate() as float),0,1)
SELECT
A.a_id,
C.c_id,
B.b_id,
D.d_id,
E.e_id,
F.g_id
FROM table_G G
INNER JOIN
(select * from table_A where created < @date) A
ON A.a_id = G.a_id
INNER JOIN
table_C C
ON C.c_id = G.c_id
LEFT JOIN
table_B B
ON B.a_id = A.a_id
LEFT JOIN
table_D D
ON D.b_id = B.b_id AND D.c_id = G.c_id
LEFT JOIN
table_E E
ON E.e_id = COALESCE(D.e_id, C.e_id)
LEFT JOIN
table_F F
ON F.g_id = G.g_id
WHERE
F.g_id IS NULL


if you don't like inline views.
Post #1385637
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse