SQL Server Performance

Strange stored procedure performance issue

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Stephane GUNET, Oct 11, 2008.

  1. Stephane GUNET New Member

    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
  2. moh_hassan20 New Member

    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.







  3. gurucb New Member

    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)
  4. Stephane GUNET New Member

    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.
  5. Stephane GUNET New Member

    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.
  6. gurucb New Member

    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
  7. Stephane GUNET New Member

    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.
  8. gurucb New Member

    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. ;)
  9. Stephane GUNET New Member

    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.
  10. gurucb New Member

    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
  11. Stephane GUNET New Member

    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 ?
  12. gurucb New Member

    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.
  13. moh_hassan20 New Member

    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 .
  14. gurucb New Member

    Can you please post what options worked till now and what did not work (performance)?
  15. Stephane GUNET New Member

    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().
  16. Adriaan New Member

    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.
  17. Stephane GUNET New Member

    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...
  18. Adriaan New Member

    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.
  19. moh_hassan20 New Member

    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

Share This Page