January 8, 2013 at 11:31 am
I have a query that will run for a time period of 01/07/13 - 01/13/13 but will not run for 01/14/13 - 01/20/13. The only differences are the dates. When I get the estimated execution plan for the non running date range it says I have a missing Index. When I add the index it runs fine. However I dont understand why this has happened out of the blue as this query runs in the back end of our app weekly. I will attach the 2 estimated Execution Plans.
Here is the query:
select COUNT(*)
from
PlannedShift
where
ShiftDate >= '1/14/13'
and ShiftDate <= '1/20/13'
and (ShiftType = 'F' or ShiftType = 'G')
and JobID in
(
select ID from Assignment where ID in
(
3893,3894,3897,3896,3898,3899,3900,3905,3907,3908,4514,4502,4512,4544,4501,4551,4519,4554,4523,4509,4504,4534,3975,3751,3755,3756,4025,3732,3913,27277,3921,4027,4016,3889,3735,3742,3736,3768,3803,3758,3752,3769,4532,4507,4505,4529,4517,4521,3748,3773,4518,3971,4015,3912,3966,3888,3919,3938,3865,3961,4010,3999,21368,4496,4524,4537,4513,3733,3793,3795,3882,3791,3890,3976,3954,3867,3832,4530,3857,3891,4555,3833,3892,4553,4540,3868,3858,3774,3782,4951,4998,3977,5033,5005,3834,3821,3869,3934,27308,3994,27337,27336,4516,4028,3926,4500,3805,4545,27315,27324,27325,3822,3933,3835,3806,3823,3849,3870,3814,3978,4497,3979,27339,3980,3981,4001,4002,3982,3995,3970,3729,3777,3743,3807,3850,3837,3825,3808,3872,3836,3895,3824,3871,4515,3941,3927,3967,4510,4538,3737,3996,3809,3844,4044,5016,8050,8052,8051,3887,3918,3937,3771,3960,3965,4024,3847,3820,3831,4009,3998,3962,3910,4013,27326,27311,27310,27281,27312,27282,27302,27305,3950,3810,4003,3757,3778,4029,3928,27299,27300,4550,4526,3779,3873,4030,27313,4528,3990,3922,3988,3731,3747,3932,3948,4004,4031,3929,4546,4032,4033,5002,4034,3987,3838,3826,3851,3989,4863,3759,3761,4527,4548,3914,3942,3983,3991,3968,4019,4035,4536,4543,27284,27276,3801,4522,3799,3874,3815,3754,3760,4005,4036,4525,4541,27309,27307,27278,27279,27280,27301,27327,3949,4037,4038,4039,4017,4511,4542,4508,3974,3885,3886,3856,3786,4533,3741,3972,3939,3734,3772,3750,3767,3963,4549,4011,4520,4026,4499,3881,3846,3843,3819,3866,4000,3848,3940,3740,3964,4018,3816,3813,3992,3875,3780,4040,3924,3973,5019,4041,3753,3763,3800,3749,3762,3945,3901,3951,3796,4023,4020,5031,8054,5027,4996,27283,3953,3802,3946,27314,3943,4006,3902,3903,3863,3859,3839,4535,3827,3852,3876,3860,3877,3765,3952,3904,27298,3920,3911,4014,4012,27306,27303,27304,3955,3853,3915,3985,3984,3883,3788,3744,4007,3861,3787,3797,5000,4552,4556,7959,3789,3745,3738,3764,3854,3840,3828,4021,4042,3956,3916,3906,4498,4506,3935,3841,3811,3829,3878,4927,4925,4547,4503,4531,3930,3879,3776,3783,3957,4898,4539,4907,3775,4854
)
and ID not in
(
select distinct JobID from EmployeeShift where JobID in
(
3893,3894,3897,3896,3898,3899,3900,3905,3907,3908,4514,4502,4512,4544,4501,4551,4519,4554,4523,4509,4504,4534,3975,3751,3755,3756,4025,3732,3913,27277,3921,4027,4016,3889,3735,3742,3736,3768,3803,3758,3752,3769,4532,4507,4505,4529,4517,4521,3748,3773,4518,3971,4015,3912,3966,3888,3919,3938,3865,3961,4010,3999,21368,4496,4524,4537,4513,3733,3793,3795,3882,3791,3890,3976,3954,3867,3832,4530,3857,3891,4555,3833,3892,4553,4540,3868,3858,3774,3782,4951,4998,3977,5033,5005,3834,3821,3869,3934,27308,3994,27337,27336,4516,4028,3926,4500,3805,4545,27315,27324,27325,3822,3933,3835,3806,3823,3849,3870,3814,3978,4497,3979,27339,3980,3981,4001,4002,3982,3995,3970,3729,3777,3743,3807,3850,3837,3825,3808,3872,3836,3895,3824,3871,4515,3941,3927,3967,4510,4538,3737,3996,3809,3844,4044,5016,8050,8052,8051,3887,3918,3937,3771,3960,3965,4024,3847,3820,3831,4009,3998,3962,3910,4013,27326,27311,27310,27281,27312,27282,27302,27305,3950,3810,4003,3757,3778,4029,3928,27299,27300,4550,4526,3779,3873,4030,27313,4528,3990,3922,3988,3731,3747,3932,3948,4004,4031,3929,4546,4032,4033,5002,4034,3987,3838,3826,3851,3989,4863,3759,3761,4527,4548,3914,3942,3983,3991,3968,4019,4035,4536,4543,27284,27276,3801,4522,3799,3874,3815,3754,3760,4005,4036,4525,4541,27309,27307,27278,27279,27280,27301,27327,3949,4037,4038,4039,4017,4511,4542,4508,3974,3885,3886,3856,3786,4533,3741,3972,3939,3734,3772,3750,3767,3963,4549,4011,4520,4026,4499,3881,3846,3843,3819,3866,4000,3848,3940,3740,3964,4018,3816,3813,3992,3875,3780,4040,3924,3973,5019,4041,3753,3763,3800,3749,3762,3945,3901,3951,3796,4023,4020,5031,8054,5027,4996,27283,3953,3802,3946,27314,3943,4006,3902,3903,3863,3859,3839,4535,3827,3852,3876,3860,3877,3765,3952,3904,27298,3920,3911,4014,4012,27306,27303,27304,3955,3853,3915,3985,3984,3883,3788,3744,4007,3861,3787,3797,5000,4552,4556,7959,3789,3745,3738,3764,3854,3840,3828,4021,4042,3956,3916,3906,4498,4506,3935,3841,3811,3829,3878,4927,4925,4547,4503,4531,3930,3879,3776,3783,3957,4898,4539,4907,3775,4854
)
and ShiftTypeCode = 'S' and ShiftDate >= '1/14/13' and ShiftDate <= '1/20/13'
)
and TypeCode <> 'E'
)
January 8, 2013 at 12:19 pm
Estimated execution plans don't really help much. Performance problems can be difficult to determine the root cause. Are you running this query just like this or is it a stored proc? Take a look at this article that explains the types of information required to help with performance problems. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]
_______________________________________________________________
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/
January 8, 2013 at 2:00 pm
I found the issue. Thank you for your help. Was the Ascending Date issue. http://sqlinthewild.co.za/index.php/2011/03/22/statistics-row-estimations-and-the-ascending-date-column/
January 8, 2013 at 2:32 pm
Cole.Mietzner (1/8/2013)
Glad you figured it out. I had a feeling it was stale statistics.
_______________________________________________________________
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/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply