Different Execution Plan With Similar Query

Question

Post reply

Different Execution Plan With Similar Query

BrianAtSQLCentral

SSC Veteran

Points: 204
More actions
January 13, 2010 at 11:04 am

#214505

I have a query that is giving me two completely different execution plans when I change the following line:
SELECT dbo.fnDEVICE_UNIT_CONVERT(T_EQUIPMENT_LOG_DATA.READ_DATE, T_EQUIPMENT_LOG_DATA.READ_VALUE, T_EQUIPMENT_VARIABLE.UNIT_ID, 72, T_EQUIPMENT.EQUIPMENT_ID) AS READ_VALUE,
T_EQUIPMENT_LOG_DATA.READ_DATE,
T_EQUIPMENT.EQUIPMENT_NM,
T_EQUIPMENT.UWI,
T_EQUIPMENT.SERIAL_NUMBER,
T_EQUIPMENT.IS_ASSET,
dbo.fnGET_JOURNAL_ENTRY(2644, T_EQUIPMENT_VARIABLE.EQUIP_TYPE_VARIABLE_ID, T_EQUIPMENT_LOG_DATA.READ_DATE) AS JOURNAL_ENTRY
FROM T_EQUIPMENT_VARIABLE INNER JOIN
T_EQUIPMENT_LOG_DATA ON T_EQUIPMENT_VARIABLE.EQUIP_VARIABLE_ID = T_EQUIPMENT_LOG_DATA.EQUIP_VARIABLE_ID INNER JOIN
T_EQUIPMENT ON T_EQUIPMENT_VARIABLE.EQUIPMENT_ID = T_EQUIPMENT.EQUIPMENT_ID INNER JOIN
T_VARIABLE_GROUP ON T_EQUIPMENT.EQUIP_TYPE_ID = T_VARIABLE_GROUP.EQUIP_TYPE_ID AND
T_EQUIPMENT_VARIABLE.EQUIP_TYPE_VARIABLE_ID = T_VARIABLE_GROUP.FLOW_VARIABLE_ID
WHERE (T_EQUIPMENT_VARIABLE.EQUIPMENT_ID = 2644)
AND (T_EQUIPMENT_LOG_DATA.READ_DATE BETWEEN '1/6/2010' AND '1/14/2010') //This is the line I changed
AND (T_EQUIPMENT_LOG_DATA.READ_VALUE IS NOT NULL)
ORDER BY T_EQUIPMENT_LOG_DATA.READ_DATE
By changing the date by one day, the new query has a completely different execution plan. I know that if I rebuild all indexes in the database, the execution plan for both query's will be identical. But I can't rebuild often because it takes the DB offline. So instead of INDEX REBUILD's I've been running UPDATE STATISITCS and REORGANIZE INDEX.
Can can I isolate which index is causing the different in execution plan, so that I can rebuild only the necessary ones? Or can I just get away with UPDATE STATISITCS and REORGANIZE INDEX?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Lynn Pettis

SSC Guru

Points: 442467

January 13, 2010 at 11:46 am

#1103226

Could you post the two actual execution plans?

BrianAtSQLCentral SSC Veteran Points: 204 More actions · Answer 2

Thanks:

//Execution plan that runs as expected using '1/6/2010'

|--Compute Scalar(DEFINE:([Expr1012]=[AFTI_PROD].[dbo].[fnDEVICE_UNIT_CONVERT]([AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[READ_DATE],[Expr1014],[AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[UNIT_ID],(72.),[Expr1015]), [Expr1013]=[AFTI_PROD].[dbo].[fnGET_JOURNAL_ENTRY]((2644),[Expr1016],[AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[READ_DATE])))

|--Sort(ORDER BY:([AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[READ_DATE] ASC))

|--Compute Scalar(DEFINE:([Expr1014]=CONVERT_IMPLICIT(decimal(19,5),[AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[READ_VALUE],0)))

|--Nested Loops(Inner Join, OUTER REFERENCES:([AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[EQUIP_VARIABLE_ID]))

|--Hash Match(Inner Join, HASH:([AFTI_PROD].[dbo].[T_VARIABLE_GROUP].[FLOW_VARIABLE_ID])=([AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[EQUIP_TYPE_VARIABLE_ID]), RESIDUAL:([AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[EQUIP_TYPE_VARIABLE_ID]=[AFTI_PROD].[dbo].[T_VARIABLE_GROUP].[FLOW_VARIABLE_ID]))

| |--Compute Scalar(DEFINE:([Expr1015]=CONVERT_IMPLICIT(decimal(18,0),[AFTI_PROD].[dbo].[T_EQUIPMENT].[EQUIPMENT_ID],0)))

| | |--Nested Loops(Inner Join, OUTER REFERENCES:([AFTI_PROD].[dbo].[T_EQUIPMENT].[EQUIPMENT_ID]))

| | |--Nested Loops(Inner Join, OUTER REFERENCES:([AFTI_PROD].[dbo].[T_VARIABLE_GROUP].[EQUIP_TYPE_ID]))

| | | |--Clustered Index Scan(OBJECT:([AFTI_PROD].[dbo].[T_VARIABLE_GROUP].[PK_T_VARIABLE_GROUP]))

| | | |--Index Seek(OBJECT:([AFTI_PROD].[dbo].[T_EQUIPMENT].[IDX_T_EQUIPMENT_EQUIP_TYPE_ID]), SEEK:([AFTI_PROD].[dbo].[T_EQUIPMENT].[EQUIP_TYPE_ID]=[AFTI_PROD].[dbo].[T_VARIABLE_GROUP].[EQUIP_TYPE_ID] AND [AFTI_PROD].[dbo].[T_EQUIPMENT].[EQUIPMENT_ID]=(2644.)) ORDERED FORWARD)

| | |--Clustered Index Seek(OBJECT:([AFTI_PROD].[dbo].[T_EQUIPMENT].[PK_T_EQUIPMENT]), SEEK:([AFTI_PROD].[dbo].[T_EQUIPMENT].[EQUIPMENT_ID]=[AFTI_PROD].[dbo].[T_EQUIPMENT].[EQUIPMENT_ID]) LOOKUP ORDERED FORWARD)

| |--Compute Scalar(DEFINE:([Expr1016]=CONVERT_IMPLICIT(int,[AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[EQUIP_TYPE_VARIABLE_ID],0)))

| |--Nested Loops(Inner Join, OUTER REFERENCES:([AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[EQUIP_VARIABLE_ID], [Expr1017]) WITH UNORDERED PREFETCH)

| |--Index Seek(OBJECT:([AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[IDX_T_EQUIPMENT_VARIABLE_4]), SEEK:([AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[EQUIPMENT_ID]=(2644.)) ORDERED FORWARD)

| |--Clustered Index Seek(OBJECT:([AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[PK_T_EQUIPMENT_VARIABLE]), SEEK:([AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[EQUIP_VARIABLE_ID]=[AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[EQUIP_VARIABLE_ID]) LOOKUP ORDERED FORWARD)

|--Index Seek(OBJECT:([AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[IDX_T_EQUIPMENT_LOG_DATA_6]), SEEK:([AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[EQUIP_VARIABLE_ID]=[AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[EQUIP_VARIABLE_ID] AND [AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[READ_DATE] >= '2010-01-06 00:00:00.000' AND [AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[READ_DATE] <= '2010-01-14 00:00:00.000'), WHERE:([AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[READ_VALUE] IS NOT NULL) ORDERED FORWARD)

//Different execution plan, date is set to '1/7/2010'

|--Compute Scalar(DEFINE:([Expr1012]=[AFTI_PROD].[dbo].[fnDEVICE_UNIT_CONVERT]([AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[READ_DATE],[Expr1014],[AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[UNIT_ID],(72.),[Expr1015]), [Expr1013]=[AFTI_PROD].[dbo].[fnGET_JOURNAL_ENTRY]((2644),[Expr1016],[AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[READ_DATE])))

|--Compute Scalar(DEFINE:([Expr1015]=CONVERT_IMPLICIT(decimal(18,0),[AFTI_PROD].[dbo].[T_EQUIPMENT].[EQUIPMENT_ID],0)))

|--Nested Loops(Inner Join, OUTER REFERENCES:([AFTI_PROD].[dbo].[T_EQUIPMENT].[EQUIPMENT_ID]))

|--Nested Loops(Inner Join, OUTER REFERENCES:([AFTI_PROD].[dbo].[T_VARIABLE_GROUP].[EQUIP_TYPE_ID]))

| |--Nested Loops(Inner Join, WHERE:([AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[EQUIP_TYPE_VARIABLE_ID]=[AFTI_PROD].[dbo].[T_VARIABLE_GROUP].[FLOW_VARIABLE_ID]))

| | |--Compute Scalar(DEFINE:([Expr1016]=CONVERT_IMPLICIT(int,[AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[EQUIP_TYPE_VARIABLE_ID],0)))

| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[EQUIP_VARIABLE_ID]))

| | | |--Compute Scalar(DEFINE:([Expr1014]=CONVERT_IMPLICIT(decimal(19,5),[AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[READ_VALUE],0)))

| | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[EQUIPMENT_LOG_DATA_ID]))

| | | | |--Index Seek(OBJECT:([AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[IDX_T_EQUIPMENT_LOG_DATA_2]), SEEK:([AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[READ_DATE] >= '2010-01-07 00:00:00.000' AND [AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[READ_DATE] <= '2010-01-14 00:00:00.000'), WHERE:([AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[READ_VALUE] IS NOT NULL) ORDERED FORWARD)

| | | | |--Clustered Index Seek(OBJECT:([AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[PK_T_EQUIPMENT_LOG_DATA]), SEEK:([AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[EQUIPMENT_LOG_DATA_ID]=[AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[EQUIPMENT_LOG_DATA_ID]) LOOKUP ORDERED FORWARD)

| | | |--Clustered Index Seek(OBJECT:([AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[PK_T_EQUIPMENT_VARIABLE]), SEEK:([AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[EQUIP_VARIABLE_ID]=[AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[EQUIP_VARIABLE_ID]), WHERE:([AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[EQUIPMENT_ID]=(2644.)) ORDERED FORWARD)

| | |--Clustered Index Scan(OBJECT:([AFTI_PROD].[dbo].[T_VARIABLE_GROUP].[PK_T_VARIABLE_GROUP]))

| |--Index Seek(OBJECT:([AFTI_PROD].[dbo].[T_EQUIPMENT].[IDX_T_EQUIPMENT_EQUIP_TYPE_ID]), SEEK:([AFTI_PROD].[dbo].[T_EQUIPMENT].[EQUIP_TYPE_ID]=[AFTI_PROD].[dbo].[T_VARIABLE_GROUP].[EQUIP_TYPE_ID] AND [AFTI_PROD].[dbo].[T_EQUIPMENT].[EQUIPMENT_ID]=(2644.)) ORDERED FORWARD)

|--Clustered Index Seek(OBJECT:([AFTI_PROD].[dbo].[T_EQUIPMENT].[PK_T_EQUIPMENT]), SEEK:([AFTI_PROD].[dbo].[T_EQUIPMENT].[EQUIPMENT_ID]=[AFTI_PROD].[dbo].[T_EQUIPMENT].[EQUIPMENT_ID]) LOOKUP ORDERED FORWARD)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

You can save the plans graphiclly as a .sqlplan files and upload those. It would be easier to read.

BrianAtSQLCentral SSC Veteran Points: 204 More actions · Answer 4

I did a fullscan update statistics and both execution plans were identical.

Is there a way to figure out which statistic was out of date?

WHug Ten Centuries Points: 1248 More actions · Answer 5

This is just a stab in the dark, but if it helps . . .

AND (T_EQUIPMENT_LOG_DATA.READ_DATE BETWEEN '1/6/2010' AND '1/14/2010')

becomes (if the datatype on the table is a datetime)

AND (T_EQUIPMENT_LOG_DATA.READ_DATE BETWEEN cast( '1/6/2010' as datetime) AND cast('1/14/2010' as datetime)