Strange stored procedure performance issue

I am experiencing performance issues with SQL 2005. I have the following stored procedure :
ALTER PROCEDURE [dbo].[qryTestPerformance]
( @IdMission uniqueidentifier,
@DateValeur datetime = NULL
)
AS
IF @DateValeur IS NULL
SET @DateValeur = GETDATE()

SELECT dbo.datEvenementType.IdEvenementType
FROM dbo.datEvenementType
LEFT OUTER JOIN dbo.datEvenement Objectif
ON dbo.datEvenementType.IdEvenementType = Objectif.IdEvtEvenementType
AND Objectif.IdEvtPersonne IS NULL
AND Objectif.EvtDate IS NULL
AND Objectif.EvtDateValeur <= @DateValeur
AND (Objectif.EvtDateArchive <= @DateValeur OR Objectif.EvtDateArchive IS NULL)
LEFT OUTER JOIN dbo.datEvenement
ON dbo.datEvenementType.IdEvenementType = dbo.datEvenement.IdEvtEvenementType
AND dbo.datEvenement.EvtDate IS NOT NULL
AND dbo.datEvenement.EvtDateValeur <= @DateValeur
AND (dbo.datEvenement.EvtDateArchive <= @DateValeur OR dbo.datEvenement.EvtDateArchive IS NULL)
AND dbo.datEvenement.EvtStatut < 320
WHERE dbo.datEvenementType.IdETMission = @IdMission
GROUP BY dbo.datEvenementType.IdEvenementType,
Objectif.EvtValeur
HAVING ISNULL(Objectif.EvtValeur, 0) - SUM(ISNULL(dbo.datEvenement.EvtValeur, 0)) <> 0
I’m running it on my development machine (T9500 proc with 4GB RAM, Windows Server 2008 Enterprise 32bits with SQL 2005 Standard (version 9.0.3073). Execution time is 46 seconds, it returns 40 rows.
If I use Inner joins instead of left outer joins, execution time drops to 2 seconds.
If I replace the @DateValeur parameter with GETDATE(), execution time drops to 0 seconds !
The table datEvenementType has 4922 rows. Its primary key is IdEvenementType (uniqueidentifier). There is an index on the column IdETMission (uniqueidentifier).
The table datEvenement has 86967 rows. Its primary key is IdEvenement (uniqueidentifier). The column IdEvtEventementType (uniqueidentifier) is a foreign key associated with column IdEvenementType of table datEvenementType. There are indexes on the following columns (each column has its own index) :
IdEvenementType (uniqueidentifier)
IdEvtPersonne (uniqueidentifier)
EvtDate (datetime)
EvtDateValeur (datetime)
EvtDateArchive (datetime)
EvtStatut (int)
Execution plan is something like this
SELECT <- Filter (Having clause, 48 rows) <- Stream aggregate, 73 rows <- Nested loops (datEvenement and datEvenementType, 649 rows) <- *1
<- *4
*1 is Sort (IdEvenementType, Objectif.EvtValeur, for the group by clause 89 rows) <- Nested loops (Objectif and datEvenementType, 89 rows) <- *2
<- *3
*2 is Index seek (IX_datEvenementType_IdETMission for the IdETMission = IdMission clause, 40 rows)
*3 is Filter (371360 rows !) <- Nested loops (inner join, Objectif table, 3478680 rows !) <- Index seek (IX_datEvenement_EvtDateValeur, 3478680 rows !)
<- Key lookup (Objectif, 3478680 rows)
*4 is Filter (5475903 rows !) <- Nested loops (inner join, datEvenement, 7740063 rows !) <- Index seek (IX_datEvenement_EvtDateValeur, 7740063 rows)
<- Key lookup (datEvenement, 7740063 rows)
3478680 rows is 40 (numbers of rows in *2) * 86967 (number of rows in datEvenement). 7740063 is 89 (number of rows in *2) * 86967. It seems SQL Server is crossing the two tables, and applying the join clause afterwards. The funny thing is the predicates used in the index seek (EvtDateValeur <= @DateValeur) actually returns all the row in the table ! Why is it doing something like this ?
This kind of issue has been plaguing my application for months, every time I manage to rewrite my procedure differently, and get "normal" execution times (one or two seconds max), usually by using temp tables or table variables, by removing left joins and using union instead, but every other week another stored procedure starts taking too long. I need to understand what is causing this, so I can write my stored procs correctly, and avoid this kind of issues completely

Welcome to the forum.
The only difference for the three cases is pages read for inner /outer left join , and may not available recent good statistics distribution.
Also you use filter on the join (and you shoud have compound index for all fields) .
Also you use OR operation (which loose the value of any index)
For inner join less read , and for outer left join more read
Start update statistics for all tables included in the query (to maximizer optimizer performance)
run DBCC DROPCLEANBUFFERS for every query (to clean the buffer) , and avoid caching effect.
Try using graphic format for the execution plan


create execution plan for the three cases
case#1 inner join
you find nested loops (inner join) cost =0 , so it is faster
register the value of estimated subtree cost for the select node (in extreme left)

case#2 left outer join
you find nested loops (outer join) cost >0 , for every outer join , so it is slower
register the value of estimated subtree cost for the select node (in left)

If the value estimated subtree cost increase , so execution time increase
also the cost of scan index , seek index may change due change in the returned row set
Also, use stastics io on , and find total scanned , logical pages , physical pages
as the phsical read increase , scanning increase , so the execution time increase
try to avoid OR operator.
create compund index for all join fields.

The performance degradation may be due
* Statistics are not up to date.
* For optimizer to take advantage of Statistics functions need to be expandable(foldable).. for example Getdate() are all not foldable.
Just a wild try, in your case create proc with option (hash join) and see if it works better.
Reason: Nested loops work at the speed of disk and since statistics may not be estimated right it ends with 1 and thus going to nested loop. On other hand Hash Join works at Memory / Proc speed and may complete faster (ofcourse in this case more CPU is utilised)

I tried updating statistics (UPDATE STATISTICS dbo.datEvenement WITH FULLSCAN, using DBCC DROPCLEANBUFFERS and using EXEC dbo.qryTestPerformance WITH RECOMPILE, and results are actually worst :
The first run after statistiscs update and DBCC DROPCLEANBUFFERS gives the following results : First query execution time is now 1:08, second one (with inner joins) is 0:26, third one (using GETDATE() instead of @DateValeur variable/parameter) is 0:06, fourth one (using LEFT OUTER HASH JOIN) is 0:22. After this first run, execution time gets down to query 1 (0:46), query 2 (0:02), query 3 (0:00) and query 4 (0:00).
For the execution plan, I don't know how to post the complete plans. For the first query, the expensive operations are Index seek and Key lookup on Objectif tables (3478680 rows, 18% each) and datEvenement (7740063 rows, 18% each) and the sort operation (89 rows, 22%, seems surprising...). Index seek on datEvenementType is 6%, all the rest (including nested loops) is 0%
For the second query (with inner joins), it starts with index seek and key lookup on column EvtDateValeur for Objectif table (like in *3 above), but does it on 86967 rows only, filters the result (down to 9284 rows) and sorts (for the group by clause, 19%) then joins with datEvenement using the index on IdEvtEvenementType column (index seek, 251582 rows, 6%), gets the rest of the columns for datEvenement (key lookup, 251582 rows, 58%), filters again, the joins with the datEvenementType table (index seek, 6%). All join operations use nested loops.
For the third query (@DateValeur parameters replaced with GETDATE()), it performs a filters table datEvenementType on IdETMission column (index seek, 40 rows returned, 0%), then joins with table datEvenement (first performs index seek on column IdEvtEvenementType 40 times, 373 rows returned, 3%, then a key lookup (373 rows, 32%), filters the result and performs the join with nested loops), then joins with Objectif (using a hash join and a clustered index scan on 9284 rows, with the filter clause as predicate and IdEvtEvenementType and EvtValeur as output list).
Execution plan for query 3 is what I would be normally expecting : it uses the index on the foreign keys to make the join, instead of doing a full cartesians product, a key lookup on 40 then 89 times the whole table, and a filter afterwards.
Funny thing is, if I look at the estimated subtree cost on the last select of each execution plan, I get 0.0537138 for query 1, 0.0587657 for query 2 and 3.85536 for query 3. Which, as far as I understand, seems to mean that query 3 is estimated to be 72 times more costly than query 1, but in fact runs in 0 seconds agains 46 for query 1.
Concerning your remarks, here are a few answers :

• using left or inner join : I need to use left join. The version with inner join is much faster, but I don't get the full result set (I'm missing two rows).
• using filter on the join : I cannot place the filter in a where clause because of the left join. I tried it anyway, and it's not always working : sometimes it gets better, sometimes worse. It depends on the query. An alternative would be to use subqueries LEFT JOIN (SELECT ... FROM ... WHERE ...) qry ON ... or views, temp tables or table variables, but it makes the code much more difficult to write and read.
• about using OR, I can use COALESCE or ISNULL instead, (ISNULL(Objectif.EvtDateArchive, @DateValeur + 1) > @DateValeur), but it doesn't make any difference
• using query hints (forcing hash joins) seems to work fine, but I heard it should be avoided whenever possible

But my problem is not so much trying to fix this procedure as understanding why it is using such a poor execution plan. Every week I get notifications from users saying they get query timeouts in the application, and every time it is a new query. I cannot keep on fixing them one at a time, and not knowing what makes them go wrong.

I found an interesting solution : if I use table hints to force SQL Server to perform the join by filter on IdEvtEvenementType first (using WITH (INDEX(IX_datEvenement_IdEvtEvenementType)) on the Objectif and datEvenement tables), I get an execution plan that is quite close to the one I get with query 3 and execution time is 3 seconds after statistics update and DBCC DROPCLEANBUFFERS, 0 seconds for subsequent runs.
The only question is why isn't SQL Server doing it automatically ? IdEvenementType is the foreign key column, it is the only clause in the join that uses both tables, it seems pretty obvious that it is the best way to do the join. Using table hints makes me uncomfortable, I would rather rely on the optimizer, but in this case its choices are clearly quite inefficient.

Can you please try this, do not use query hints but instead create statistics on that column and then retry the query and see if it picks up that index

I tried this :CREATE
STATISTICS datEvenement_IdEvtEvenementType ON dbo.datEvenement (IdEvtEvenementType) WITH FULLSCAN
but I'm back to 46 seconds execution time and an execution plan that doesn't use the index on the foreign key, and does key lookup on millions of rows. Am I doing something wrong ? I really like this statistics idea, I do not look forward to have to add WITH INDEX hints in all of my stored procedures...
Edit : Deleting the index on EvtDateValeur also seems to solve the problem, but I cannot be certain I won't have the same problem with another index later.

Can you please run DBCC Show_statistics('datEvenement','EvtDateValeur') and share the output. Also do we have index on dbo.datEvenementType.IdETMission column in our where clause. Sometime going to root of the issue could be time consuming.

There is an index on IdETMission.
The column EvtDateValeur is the creation date of the row, so there is each value is different (well, almost each...).
Here is the output :Name Updated Rows Rows Sampled Steps Density Average key length String Index
-------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- ------ ------------- ------------------ ------------
_WA_Sys_00000006_589C25F3 Oct 12 2008 4:54PM 86967 86967 199 0.5356076 8 NO
(1 row(s) affected)
All density Average Length Columns
------------- -------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2.744614E-05 8 EvtDateValeur
(1 row(s) affected)
RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
----------------------- ------------- ------------- -------------------- --------------
2008-03-19 19:21:25.900 0 716 0 1
2008-03-26 13:44:22.117 0 1224 0 1
2008-03-26 13:53:58.397 8 792 2 4
2008-04-03 11:08:52.017 0 203 0 1
2008-04-14 12:46:32.667 234 6 93 2.516129
2008-04-15 14:20:34.247 278 9 144 1.930556
2008-04-15 16:53:02.277 470 8 179 2.625698
2008-04-15 17:10:50.970 351 4 104 3.375
2008-04-15 17:34:09.513 228 6 96 2.375
2008-04-17 11:56:48.723 357 4 133 2.684211
2008-04-21 13:11:28.977 476 44 292 1.630137
2008-04-21 17:06:57.203 227 7 109 2.082569
2008-04-22 12:50:58.310 303 6 165 1.836364
2008-04-23 11:11:17.057 300 7 144 2.083333
2008-04-23 15:30:20.123 486 5 213 2.28169
2008-04-23 16:59:05.070 209 6 104 2.009615
2008-04-24 10:36:46.560 319 6 138 2.311594
2008-04-24 12:29:27.323 447 6 210 2.128572
2008-04-24 15:12:19.743 497 7 255 1.94902
2008-04-24 15:38:22.643 360 6 163 2.208589
2008-04-25 11:20:04.870 340 5 197 1.725888
2008-04-25 15:57:13.783 363 5 225 1.613333
2008-04-25 16:55:11.210 376 6 156 2.410256
2008-04-25 18:51:05.453 330 7 148 2.22973
2008-04-28 09:38:33.427 341 5 122 2.795082
2008-04-28 11:53:45.730 480 432 276 1.73913
2008-04-28 14:43:39.447 37 469 28 1.321429
2008-04-28 14:43:43.430 162 456 1 162
2008-04-29 12:10:52.720 254 4 149 1.704698
2008-04-29 18:35:44.010 371 4 168 2.208333
2008-04-30 16:13:41.777 279 1 136 2.051471
2008-04-30 17:53:17.253 361 34 109 3.311927
2008-05-05 14:57:19.897 502 4 297 1.690236
2008-05-05 17:14:06.777 282 3 176 1.602273
2008-05-06 16:25:17.297 363 16 231 1.571429
2008-05-07 15:19:24.600 253 3 135 1.874074
2008-05-12 14:58:13.420 510 4 283 1.80212
2008-05-13 16:09:08.623 511 3 314 1.627388
2008-05-13 19:10:44.687 511 3 244 2.094262
2008-05-14 09:00:36.133 255 1 108 2.361111
2008-05-14 11:31:28.750 382 3 215 1.776744
2008-05-15 12:21:41.810 383 2 245 1.563265
2008-05-15 15:29:39.543 255 1 164 1.554878
2008-05-15 17:01:43.473 383 3 202 1.89604
2008-05-16 12:53:15.760 255 1 168 1.517857
2008-05-18 15:02:11.267 510 3 296 1.722973
2008-05-18 16:29:22.940 382 3 193 1.979275
2008-05-19 18:50:18.827 254 3 182 1.395604
2008-05-20 11:06:15.130 321 3 195 1.646154
2008-05-20 15:12:24.123 255 3 173 1.473988
2008-05-20 16:55:46.200 254 2 194 1.309278
2008-05-20 18:41:54.567 383 3 244 1.569672
2008-05-21 11:38:44.023 256 1 157 1.630573
2008-05-21 13:08:04.287 255 2 154 1.655844
2008-05-21 17:56:15.290 255 1 184 1.38587
2008-05-22 11:34:52.233 382 2 237 1.611814
2008-05-22 12:48:40.973 255 2 173 1.473988
2008-05-23 09:28:55.820 383 2 303 1.264026
2008-05-23 11:10:13.337 254 2 171 1.48538
2008-05-23 19:06:39.323 373 129 251 1.486056
2008-05-25 16:36:30.163 415 11 162 2.561728
2008-05-26 10:41:56.790 444 14 116 3.827586
2008-05-26 11:41:06.370 210 13 73 2.876712
2008-05-26 15:46:59.233 511 15 123 4.154471
2008-05-26 18:19:46.383 369 18 157 2.350318
2008-05-26 19:41:44.967 256 10 52 4.923077
2008-05-27 11:32:51.707 256 13 95 2.694737
2008-05-27 11:48:54.057 127 10 46 2.76087
2008-05-27 15:48:02.927 383 4 222 1.725225
2008-05-27 17:39:40.640 263 1 128 2.054688
2008-05-28 12:06:20.050 255 1 123 2.073171
2008-05-28 18:01:02.647 383 20 152 2.519737
2008-05-29 11:28:24.960 251 15 122 2.057377
2008-05-29 15:49:32.943 254 12 111 2.288288
2008-05-30 16:44:19.387 379 6 190 1.994737
2008-06-01 19:54:23.323 380 10 178 2.134831
2008-06-02 10:47:56.893 257 5 60 4.283333
2008-06-02 12:44:15.780 252 14 107 2.35514
2008-06-02 15:58:25.180 383 4 182 2.104396
2008-06-02 19:21:50.117 382 9 99 3.858586
2008-06-03 12:33:37.720 257 1 109 2.357798
2008-06-03 15:13:26.800 254 6 114 2.22807
2008-06-03 17:32:54.313 507 6 227 2.23348
2008-06-03 18:21:02.287 248 13 112 2.214286
2008-06-03 18:52:50.770 265 16 54 4.907407
2008-06-04 12:13:32.813 506 6 195 2.594872
2008-06-04 18:48:13.233 374 4 219 1.707763
2008-06-05 12:51:23.397 258 14 115 2.243478
2008-06-05 15:57:35.073 511 12 194 2.634021
2008-06-05 20:07:30.377 272 6 149 1.825503
2008-06-06 12:55:15.980 280 18 139 2.014389
2008-06-06 12:55:58.967 112 16 28 4
2008-06-06 13:20:01.607 277 9 124 2.233871
2008-06-06 16:16:43.150 418 9 204 2.04902
2008-06-06 17:40:17.383 315 10 139 2.266187
2008-06-06 18:55:02.663 274 7 160 1.7125
2008-06-06 20:03:01.120 255 11 104 2.451923
2008-06-08 19:44:21.767 303 6 103 2.941748
2008-06-09 10:52:09.083 294 5 186 1.580645
2008-06-09 12:16:36.117 426 8 205 2.078049
2008-06-09 14:23:42.947 416 14 192 2.166667
2008-06-09 15:16:31.940 305 10 130 2.346154
2008-06-09 16:16:07.850 398 7 172 2.313953
2008-06-09 16:53:28.630 232 12 100 2.32
2008-06-09 17:41:38.273 262 7 131 2
2008-06-09 19:02:17.850 293 19 129 2.271318
2008-06-10 10:42:15.877 289 9 89 3.247191
2008-06-10 15:41:53.790 331 11 162 2.04321
2008-06-10 18:04:10.640 436 11 150 2.906667
2008-06-11 12:46:43.967 362 11 138 2.623188
2008-06-11 15:57:09.567 493 13 147 3.353741
2008-06-11 16:42:57.100 255 11 84 3.035714
2008-06-12 15:01:41.273 509 7 159 3.201258
2008-06-12 18:19:57.320 511 4 194 2.634021
2008-06-13 14:10:21.033 501 14 194 2.582474
2008-06-16 13:56:26.197 511 7 236 2.165254
2008-06-16 18:43:45.103 255 1 104 2.451923
2008-06-17 16:01:21.693 257 1 99 2.59596
2008-06-18 10:42:08.147 255 1 99 2.575758
2008-06-19 15:08:43.750 502 12 239 2.100418
2008-06-20 16:23:02.113 509 8 240 2.120833
2008-06-23 16:59:06.657 257 1 96 2.677083
2008-06-24 17:08:08.623 256 4 123 2.081301
2008-06-26 10:54:01.660 253 4 150 1.686667
2008-06-26 15:54:29.573 511 13 183 2.79235
2008-06-26 18:06:00.037 511 2 111 4.603604
2008-06-27 17:57:21.093 293 4 200 1.465
2008-06-30 17:54:42.413 383 1 332 1.153614
2008-07-01 15:14:03.550 366 6 248 1.475806
2008-07-02 11:43:59.450 281 4 196 1.433674
2008-07-02 17:56:53.503 304 7 130 2.338462
2008-07-03 11:32:56.920 268 4 169 1.585799
2008-07-03 16:42:22.653 274 5 180 1.522222
2008-07-04 14:46:50.523 260 11 138 1.884058
2008-07-07 12:26:57.183 381 3 236 1.614407
2008-07-07 19:15:24.737 253 4 226 1.119469
2008-07-09 16:42:32.447 383 1 329 1.164134
2008-07-10 18:21:37.460 383 1 366 1.046448
2008-07-11 17:23:49.317 251 6 193 1.300518
2008-07-15 09:32:05.270 267 6 139 1.920863
2008-07-15 11:32:13.757 281 5 217 1.294931
2008-07-15 16:33:29.483 266 7 149 1.785235
2008-07-16 17:02:16.103 327 5 282 1.159575
2008-07-17 11:46:24.050 276 7 175 1.577143
2008-07-17 17:04:12.410 326 6 236 1.381356
2008-07-18 11:40:38.427 253 8 128 1.976563
2008-07-18 17:40:16.210 254 9 108 2.351852
2008-07-22 11:43:24.083 294 6 142 2.070423
2008-07-22 17:04:12.493 271 10 174 1.557471
2008-07-23 15:32:05.110 511 15 148 3.452703
2008-07-24 10:51:03.847 347 7 142 2.443662
2008-07-24 23:17:50.617 255 6 145 1.758621
2008-07-25 14:02:47.000 288 12 105 2.742857
2008-07-28 18:16:29.310 309 9 144 2.145833
2008-07-29 14:41:03.307 282 7 201 1.402985
2008-07-30 08:25:51.080 333 8 190 1.752632
2008-07-30 15:14:53.433 306 10 190 1.610526
2008-08-01 10:25:32.237 382 2 276 1.384058
2008-08-05 09:56:17.250 383 1 250 1.532
2008-08-07 18:37:55.317 383 3 342 1.119883
2008-08-21 12:03:22.750 383 2 329 1.164134
2008-08-22 09:29:34.567 289 14 172 1.680233
2008-08-22 09:42:15.440 272 12 78 3.48718
2008-08-22 10:16:02.380 375 10 68 5.514706
2008-08-25 10:08:49.467 356 11 149 2.389262
2008-08-25 14:53:22.873 246 11 117 2.102564
2008-08-26 15:28:13.463 383 2 211 1.815166
2008-08-27 13:55:23.050 383 1 284 1.348592
2008-08-28 13:44:27.880 383 1 199 1.924623
2008-08-29 17:02:11.153 403 3 291 1.38488
2008-09-02 09:06:32.593 390 4 246 1.585366
2008-09-04 11:07:34.870 487 1347 379 1.28496
2008-09-04 11:07:36.993 0 3383 0 1
2008-09-04 18:07:45.050 431 4139 329 1.31003
2008-09-05 09:11:09.533 383 1 139 2.755396
2008-09-05 12:31:56.907 381 6 279 1.365591
2008-09-05 17:59:01.237 385 5 246 1.565041
2008-09-07 17:44:56.667 409 5 264 1.549242
2008-09-09 09:08:29.497 919 4 821 1.119367
2008-09-09 15:39:48.167 511 1 488 1.047131
2008-09-10 11:38:20.120 511 1 461 1.10846
2008-09-10 17:12:12.920 402 5 320 1.25625
2008-09-12 10:02:05.957 511 1 337 1.51632
2008-09-15 11:29:25.380 412 8 291 1.415808
2008-09-17 15:42:35.367 511 1 302 1.692053
2008-09-18 14:19:39.130 511 1 332 1.539157
2008-09-19 13:24:48.650 203 1482 149 1.362416
2008-09-22 18:15:08.210 449 1616 415 1.081928
2008-09-23 15:41:44.197 511 2 309 1.653722
2008-09-25 10:58:28.063 510 2 313 1.629393
2008-09-26 10:17:26.060 251 1662 172 1.459302
2008-09-26 14:34:04.797 511 2 253 2.019763
2008-09-26 16:54:43.873 416 12 186 2.236559
2008-09-29 17:15:52.210 511 1 255 2.003922
2008-09-29 23:47:56.593 372 15 157 2.369427
2008-09-30 11:24:03.110 390 11 202 1.930693
2008-09-30 15:14:28.693 428 11 207 2.067633
2008-10-03 18:43:08.523 454 1 283 1.60424
2008-10-11 12:40:44.127 0 1 0 1
(199 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Just again a wild thing..
in the stored procedure after this code insert below
IF @DateValeur IS NULL
SET @DateValeur = GETDATE()
declare @datetemp datetime
set @datetemp = @DateValuer
and where ever you have @DateValuer replace it with @datetemp and then execute the query.
And these are system created auto statistics, right??
Also execute the query with different parameters below are the parameters. Is the performance comparable for all the three cases.
2008-04-03
2008-05-30
2008-08-29

These are system created auto statistics.
If I use your @datetemp code, execution time is 0 seconds, with an execution plan equivalent to the one I get using GETDATE() (query 3 above)
If I specify the @dateValeur parameter with the value you provided, it is the same (I get a different execution plan with 2008-04-03, but then it returns 0 rows, so that's not surprising...)
I tried with @DateValeur = '2008-10-12' or even this :
DECLARE
@Date datetimeSET
@Date = '20080529'EXEC
qryTestPerformance 'BCC2B661-C76B-47B7-84AA-7D8053BF1ED9', @Date WITH RECOMPILE
and I still get 0 seconds. So what is the problem ? Is the problem coming from the Null valued parameter ?

I am not completely sure that my analysis is right but my line of thinking was this.. may be we can discuss this along with others in the forum.
When you said with getdate() the query works fastest but not when dates are provided. The condition in query is <= @date value. And if getdate() is given even then we are fastet (typically should return maximum rows). The reason why I felt get date() was giving results quick is because during compile time sql server optimizer would not know the values of getdate(). So execution plan would be to generalized one. But when you provided definitive values that fell in those statistics hash buckets it estimated number of rows and generated execution plan which in this case is not optimal . So when we declared a different paramter and equated it that set statement would not execute till run time so again behaviour is same as get date.. so in essence we are fooling around with optimizer and generating a plan that is good for this execution.

can you post all the indexes that are created for all tables used in the query , and mark the index that you used for hint.
can you use DTA to check the optimum index for the different cases .

Can you please post what options worked till now and what did not work (performance)?

Back online. I believe if found the problem. So far all these solution are working :
specifying a date parameter, even GETDATE(). Using a local variable @DateTemp in the procedure instead of the parameter @DateValeur in the JOIN clauses. Using GETDATE() instead of parameter @DateValeur in the JOIN clause. Forcing the use of tje index on column IdEvtEvenementType through query hints, disabling the index on EvtDateValeur.
And here is the best part :
If I remove the IF @DateValeur IS NULL THEN SET @DateValeur = GETDATE(), the query returns 0 rows in 0 seconds, using the "absurd' execution plan (using indexes on EvtDateValeur) which makes perfect sense in this case, because there is no rows that match EvtDateValeur <= @DateValeur when @DateValeur IS NULL.
Next, if I set default value for @DateValeur to '2008-10-12' and run the procedure, without specifying @DateValeur, it returns 48 rows in 0 seconds, using a good execution plan (using index on IdEvtEvenementType). And If I add SET @DateValeur = NULL in the stored procedure, it still uses that same execution plan (returning 0 rows this time).
My guess is the optimizer doesn't understand that the @DateValeur parameter changes value during the procedure, optimizes the execution plan for the initial value of the parameter (NULL in the original stored proc), and when the query runs, the value used is in fact GETDATE().

Not sure if this has been covered already in this thread, but note that the default value for a parameter only comes into play if you do not mention the parameter when calling the procedure. If you supply a NULL value for the parameter, it is not overruled by the default.

The default value for the parameter is Null. And the result was the same wether you call the procedure specifying Null as the default value for the parameter, or no value was specified.
But the problem definitely seems to be coming from changing the value of the parameter in the stored procedure. Which leads me to the following rule : never change a parameter value, unless it is an output parameter. And then, don't use it in any query. If you have to change the parameter value, use a variable inside the stored procedure.
I'll check to see if this behavior is documented somewhere, I have been pulling my hair over similar problems for a few months, and was even starting to question the quality and reliability of SQL Server...

This is the dreaded "parameter sniffing" phenomenon - plenty of discussion of that on the forums here. As far as I know, it doesn't matter if you change the parameter inside the procedure or not.
Parameter sniffing was supposed to be a performance enhancement, but it often deteriorates performance. The easiest bypass is indeed to use local variables inside the procedure.
I didn't think of setting NULL as the default for a parameter value, but I think it's not necessary: if you don't mention the parameter when calling the procedure, the parameter won't be initialized so it will be NULL anyway.

First:
You set the parameter @DateValeur at start of procedure to be GETDATE
[quote user="Stephane GUNET"]ALTER PROCEDURE [dbo].[qryTestPerformance]
( @IdMission uniqueidentifier,
@DateValeur datetime = NULL
)
AS
IF @DateValeur IS NULL
SET @DateValeur = GETDATE()[/quote]
modify it to be
ALTER PROCEDURE [dbo].[qryTestPerformance]
( @IdMission uniqueidentifier,
@DateValeur datetime = GETDATE
)
AS
-- remove the next line
IF @DateValeur IS NULL
SET @DateValeur = GETDATE()
it has same logic
in this way you keep the parameter with one value all the execution time, which is passed from the parameter , and is best for optimizer
second:
IF hints enhance query performance , use it, as you test it carefully.

why you make outer join with the same table twice
really, i don't understand the logic of using the twice outer join for same table, to help you more.
if it can help, that some ideas:

• create view (or indexed view) for the outer join of the two tables
• or create table function with parameters for the outer join tables and use CROSS APPLY.
• use CTE (common table expression)
• Consult DTA for optimum performance

and modify your query