October 29, 2007 at 4:14 pm
The table uploadedfiles has a datetime column that is null for 95% of the rows (> 2 million rows). The statistics (recently updated) do not appear to reflect this tho I'm not sure what range the count of null values would be included in. The issue is that query plans are being chosen that seem to reflect the assumption that just over 1,000 rows have a null value for this field.
The query below results in a hash join.
SELECT bf.builderfile_id, bf.fileid, uf.originalfilename as filename, uf.originallocation, uf.recycleddate
FROM builderfiles bf
INNER JOIN pa10303.dbo.uploadedfiles uf on uf.fileid=bf.fileid
where bf.EntityID = 764938 AND bf.FFEntityTypeID = 1 and uf.deleted is null and uf.recycleddate is null
order by filename
With "set showplan_text on", we see:
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Sort(ORDER BY:([uf].[OriginalFilename] ASC))
|--Hash Match(Inner Join, HASH:([bf].[FileID])=([uf].[FileID]), RESIDUAL:([Builder].[dbo].[BuilderFiles].[FileID] as [bf].[FileID]=[PA10303].[dbo].[UploadedFiles].[FileID] as [uf].[FileID]))
|--Index Seek(OBJECT:([Builder].[dbo].[BuilderFiles].[IX_BuilderFiles_EntityID_FFEntityTypeID] AS [bf]), SEEK:([bf].[EntityID]=(764938) AND [bf].[FFEntityTypeID]=(1)) ORDERED FORWARD)
|--Index Seek(OBJECT:([PA10303].[dbo].[UploadedFiles].[_dta_index_UploadedFiles_10_172631758__K11_K12_K1_K2_5_7_13_16] AS [uf]), SEEK:([uf].[Deleted]=NULL AND [uf].[RecycledDate]=NULL) ORDERED FORWARD)
With "set showplan_text off" and displaying the estimated execution plan, mousing over the index seek for table UploadedFiles, the estimated number of rows is 1152. However, the below query actual returns the number 2223408.
select count(*)
FROM pa10303.dbo.uploadedfiles uf
where uf.deleted is null and uf.recycleddate is null
-----------
2223446
A much faster query results in the top query if the query plan is coerced to first perform perform an index seek on the BuilderFiles table for EntityID=764938 and then do nested loops for lookups by FileID.
select count(*)
FROM builderfiles bf
where bf.EntityID = 764938 AND bf.FFEntityTypeID = 1
-----------
421
Am I correct in thinking that the statistics do not include the null value for deleted in table uploadedfiles? How I can force the query optimizer to produce better query plans for the many similar queries (which unfortunately are dynamically produced)?
Below is the results of the statistics for the index being used for table uploadedfiles:
DBCC SHOW_STATISTICS (uploadedfiles, [_dta_index_UploadedFiles_10_172631758__K11_K12_K1_K2_5_7_13_16])
Name Updated Rows Rows Sampled Steps Density Average key length String Index
-------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- ------ ------------- ------------------ ------------
_dta_index_UploadedFiles_10_172631758__K11_K12_K1_K2_5_7_13_16 Oct 29 2007 4:07PM 2357427 2357427 193 0.9476685 35.98126 NO
(1 row(s) affected)
All density Average Length Columns
------------- -------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2.095118E-05 0.1732431 Deleted
8.337572E-06 0.456345 Deleted, RecycledDate
4.241913E-07 4.456345 Deleted, RecycledDate, FileID
4.241913E-07 35.98126 Deleted, RecycledDate, FileID, OriginalFilename
(4 row(s) affected)
RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
----------------------- ------------- ------------- -------------------- --------------
2005-06-12 20:17:52.257 0 1 0 1
2005-06-14 12:18:27.517 62 6 60 1.033333
2005-06-15 20:38:15.360 41 4 38 1.078947
2005-06-20 12:19:33.603 1178 4 1005 1.172139
2005-06-20 12:19:39.370 2 4 1 2
2005-06-24 14:18:17.713 188 4 180 1.044444
2005-06-27 16:56:49.153 92 4 87 1.057471
2005-06-29 16:15:56.050 122 4 112 1.089286
2005-07-10 20:53:21.890 284 5 269 1.055762
2005-07-13 16:19:17.700 171 4 154 1.11039
2005-07-18 17:04:18.977 227 6 205 1.107317
2005-07-22 13:16:39.427 130 4 114 1.140351
2005-07-26 09:01:37.360 133 5 104 1.278846
2005-07-27 08:31:31.070 68 5 67 1.014925
2005-07-27 10:38:26.033 19 5 12 1.583333
2005-07-27 12:37:43.597 27 5 17 1.588235
2005-07-27 12:38:27.457 38 5 21 1.809524
2005-07-27 12:38:54.643 16 6 9 1.777778
2005-07-27 12:38:54.660 0 4 0 1
2005-08-04 08:17:40.010 241 4 225 1.071111
2005-08-08 00:38:03.963 120 5 95 1.263158
2005-08-11 15:43:58.017 240 4 233 1.030043
2005-08-12 14:20:47.003 105 4 101 1.039604
2005-08-23 15:58:51.490 305 5 290 1.051724
2005-08-23 15:59:11.613 8 4 7 1.142857
2005-08-30 08:23:04.847 317 5 303 1.046205
2005-08-30 08:51:04.067 3 4 3 1
2005-08-30 12:32:34.840 29 5 25 1.16
2005-09-02 07:50:33.697 90 4 86 1.046512
2005-09-10 17:43:47.167 301 5 277 1.086643
2005-09-10 19:20:03.377 3 4 3 1
2005-09-15 14:26:09.760 223 2 201 1.109453
2005-09-19 07:38:18.090 4095 1 4095 1
2005-09-20 13:08:17.823 2191 5 2182 1.004125
2005-09-22 15:35:43.010 219 4 204 1.073529
2005-09-29 12:39:40.577 360 6 352 1.022727
2005-09-29 13:04:43.873 20 6 15 1.333333
2005-10-04 15:54:13.727 301 4 286 1.052448
2005-10-04 15:54:25.697 6 6 3 2
2005-10-17 14:17:27.373 839 4 821 1.021924
2005-10-25 14:04:41.913 467 4 456 1.024123
2005-10-25 14:04:46.837 3 5 3 1
2005-10-27 11:22:14.947 115 4 114 1.008772
2005-10-31 14:32:13.270 142 4 135 1.051852
2005-10-31 15:06:12.700 12 4 8 1.5
2005-11-01 13:41:50.530 127 5 121 1.049587
2005-11-01 14:39:34.917 4 4 4 1
2005-11-09 11:59:36.913 923 7 861 1.072009
2005-11-09 11:59:47.303 16 4 7 2.285714
2005-11-10 16:29:53.110 199 4 190 1.047368
2005-11-11 00:17:59.523 27 4 20 1.35
2005-11-14 12:33:17.787 129 6 119 1.084034
2005-11-14 12:34:26.283 30 5 10 3
2005-11-14 12:35:28.767 27 6 22 1.227273
2005-12-05 19:15:07.460 1269 4 1223 1.037612
2005-12-05 19:15:07.477 0 4 0 1
2005-12-09 13:09:47.700 391 6 376 1.039894
2005-12-09 13:09:47.733 4 6 1 4
2005-12-14 14:32:03.487 285 5 266 1.071429
2006-01-03 11:43:45.160 887 5 859 1.032596
2006-01-12 10:49:34.013 667 4 648 1.029321
2006-01-12 17:44:25.317 60 5 53 1.132075
2006-01-13 16:47:42.293 106 4 104 1.019231
2006-01-16 10:46:04.900 115 4 108 1.064815
2006-01-19 11:06:20.637 387 5 373 1.037534
2006-01-19 11:16:06.757 42 6 29 1.448276
2006-01-26 15:09:21.263 500 4 471 1.061571
2006-01-30 11:59:31.407 176 4 175 1.005714
2006-01-30 15:36:59.610 34 4 34 1
2006-02-02 18:03:13.113 288 5 282 1.021277
2006-02-03 10:18:40.803 48 5 48 1
2006-02-10 14:57:51.643 609 5 589 1.033956
2006-02-10 15:45:40.493 19 4 17 1.117647
2006-02-13 09:26:20.993 80 4 76 1.052632
2006-02-13 16:48:19.287 68 4 62 1.096774
2006-02-15 12:16:16.600 107 4 92 1.163043
2006-02-15 12:16:32.570 0 4 0 1
2006-02-17 12:08:13.333 124 4 121 1.024793
2006-02-17 12:08:18.380 0 5 0 1
2006-02-21 22:35:42.307 163 4 159 1.025157
2006-02-24 17:37:35.187 232 4 225 1.031111
2006-02-28 10:40:02.410 79 4 75 1.053333
2006-02-28 10:54:25.050 23 4 19 1.210526
2006-03-02 16:45:59.040 91 4 87 1.045977
2006-03-06 07:20:05.947 55 8 50 1.1
2006-03-06 08:06:34.407 17 6 12 1.416667
2006-03-09 17:49:34.280 274 4 262 1.045802
2006-03-09 17:53:30.197 0 7 0 1
2006-03-11 22:47:37.517 89 5 79 1.126582
2006-03-17 16:41:13.370 250 5 245 1.020408
2006-03-17 16:49:47.923 6 4 4 1.5
2006-03-21 11:36:28.020 177 4 161 1.099379
2006-03-29 09:56:45.440 385 4 367 1.049046
2006-03-29 12:43:28.797 31 4 26 1.192308
2006-03-31 08:37:04.943 101 4 84 1.202381
2006-04-04 09:05:34.047 145 5 132 1.098485
2006-04-05 13:52:24.563 124 4 117 1.059829
2006-04-07 15:46:09.233 132 5 113 1.168142
2006-04-08 07:41:08.220 12 5 9 1.333333
2006-04-08 10:58:50.710 10 4 9 1.111111
2006-04-13 10:55:28.647 159 4 152 1.046053
2006-05-02 09:40:41.143 513 4 465 1.103226
2006-05-02 10:03:20.087 2 5 2 1
2006-05-02 10:12:39.047 56 7 36 1.555556
2006-05-02 12:29:03.093 54 4 46 1.173913
2006-05-02 13:07:22.783 11 4 10 1.1
2006-05-11 13:40:34.200 269 4 251 1.071713
2006-05-11 14:05:18.967 8 4 6 1.333333
2006-05-12 15:38:53.370 111 5 105 1.057143
2006-05-16 15:20:28.973 124 4 118 1.050847
2006-05-16 16:11:01.480 10 4 6 1.666667
2006-05-24 10:29:05.880 247 4 226 1.09292
2006-06-05 11:53:43.073 291 4 276 1.054348
2006-06-05 11:53:59.557 0 5 0 1
2006-06-05 12:58:43.967 10 4 10 1
2006-06-06 15:31:12.740 45 4 43 1.046512
2006-06-07 09:01:56.803 24 4 20 1.2
2006-06-08 16:59:18.803 74 5 71 1.042253
2006-06-08 17:07:15.747 5 6 5 1
2006-06-15 09:21:31.760 600 4 586 1.023891
2006-06-15 14:38:55.033 66 5 59 1.118644
2006-06-16 10:35:20.430 45 5 41 1.097561
2006-06-20 10:40:59.473 185 5 130 1.423077
2006-06-21 12:27:36.770 48 4 47 1.021277
2006-06-26 12:54:10.607 130 5 122 1.065574
2006-06-26 15:15:56.197 16 4 15 1.066667
2006-07-05 10:04:54.053 291 4 278 1.046763
2006-07-07 15:11:09.513 118 4 107 1.102804
2006-07-10 12:00:44.117 26 5 23 1.130435
2006-07-24 17:05:44.210 502 5 466 1.077253
2006-07-24 17:16:29.080 2 8 2 1
2006-07-27 17:31:48.263 112 6 105 1.066667
2006-08-10 14:53:12.163 464 6 444 1.045045
2006-08-18 09:34:36.950 288 4 271 1.062731
2006-08-25 09:13:51.007 191 7 185 1.032432
2006-09-12 11:40:52.250 459 7 456 1.006579
2006-09-12 17:28:22.700 68 7 60 1.133333
2006-09-22 10:29:53.940 381 5 347 1.097983
2006-09-25 07:18:42.770 70 6 55 1.272727
2006-10-25 10:35:28.890 1452 4 1365 1.063736
2006-10-25 12:15:28.260 38 6 34 1.117647
2006-11-02 14:05:48.997 380 5 361 1.052632
2006-11-08 11:14:24.203 410 4 392 1.045918
2006-11-08 15:30:47.807 61 5 61 1
2006-11-15 14:44:50.747 210 4 207 1.014493
2006-11-16 12:31:06.770 81 4 77 1.051948
2006-12-01 16:27:35.500 881 5 845 1.042603
2006-12-04 10:33:50.487 20 5 20 1
2006-12-04 11:38:18.850 102 6 75 1.36
2006-12-07 16:10:17.770 387 4 370 1.045946
2006-12-12 10:56:58.560 186 6 175 1.062857
2006-12-12 16:24:10.463 51 4 49 1.040816
2006-12-21 18:28:18.263 457 4 417 1.095923
2007-01-02 23:49:09.283 371 4 349 1.063037
2007-01-03 08:20:07.873 9 4 7 1.285714
2007-01-04 17:46:41.650 216 5 206 1.048544
2007-01-09 16:05:26.843 190 4 182 1.043956
2007-01-10 13:17:42.733 162 4 152 1.065789
2007-01-10 15:13:34.807 92 4 81 1.135803
2007-01-10 17:04:03.240 37 4 31 1.193548
2007-01-25 13:17:57.377 741 5 720 1.029167
2007-01-25 13:18:03.703 4 6 3 1.333333
2007-02-07 14:40:40.023 901 4 886 1.01693
2007-02-15 07:43:02.323 418 4 397 1.052897
2007-02-15 07:43:09.183 8 5 6 1.333333
2007-02-15 07:47:48.797 11 5 11 1
2007-02-23 08:13:45.620 302 5 276 1.094203
2007-03-08 15:45:34.657 883 5 852 1.036385
2007-03-08 18:07:42.853 5 5 5 1
2007-03-09 09:41:01.677 86 5 66 1.30303
2007-03-19 15:58:02.907 529 4 503 1.05169
2007-03-28 12:05:38.753 560 7 526 1.064639
2007-03-28 12:10:07.563 31 4 25 1.24
2007-04-02 12:41:13.813 223 4 203 1.098522
2007-04-02 12:41:42.577 70 5 46 1.521739
2007-04-06 07:13:55.807 203 5 201 1.00995
2007-04-10 08:56:34.463 262 4 260 1.007692
2007-04-12 17:36:08.537 134 4 130 1.030769
2007-04-13 08:16:08.617 16 5 15 1.066667
2007-04-17 10:55:33.053 211 4 204 1.034314
2007-04-29 15:01:51.550 515 4 492 1.046748
2007-05-09 10:57:51.660 718 4 685 1.048175
2007-05-09 11:14:16.780 47 5 43 1.093023
2007-05-09 15:04:17.650 36 5 34 1.058824
2007-05-12 09:57:00.717 302 5 235 1.285106
2007-05-29 14:26:50.467 539 5 515 1.046602
2007-06-01 16:25:19.427 188 6 166 1.13253
2007-06-04 12:49:04.793 55 5 49 1.122449
2007-06-04 12:49:09.793 18 5 11 1.636364
2007-07-05 12:34:15.177 1690 4 1599 1.056911
2007-07-18 11:49:36.780 566 4 547 1.034735
2007-10-29 15:45:09.547 4324 2 4148 1.04243
2007-10-29 15:57:59.527 0 1 0 1
(193 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
October 30, 2007 at 3:39 am
Hi,
Please bear in-mind the "Estimated Execution Plan" is exactly that, estimated, thus not accurate vs "Actual Execution Plan". Here is some further reading, you may find helpful.
http://blog.sqlauthority.com/2007/08/28/sql-server-actual-execution-plan-vs-estimated-execution-plan
Cheers,
Phillip Cox
October 30, 2007 at 6:42 am
I'm not entirely sure on this, but I believe that the statistics maintained don't include NULL values.
If I recall correctly, statistics are based on 200 rows from within the table or index and the data distribution of those values. These statistics are what are used to determine index worth in the optimizer. So, the optimizer can look at an index through its statistics and determine that it won't be useful, but you can force the optimizer to make the choice to use the index or change the join, using the index, whatever, and it may work. I wouldn't count on it working every time or over time for a given index.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 30, 2007 at 8:31 am
I was inclined to agree that the statistics do not include null values, but I checked a couple query plans.
1)
SELECT * FROM UploadedFiles
WHERE Deleted = '10/1/2007'
For the above query the estimated plan shows a plan based (accurately) on few rows meeting the condition.
|--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [PA10303].[dbo].[UploadedFiles].[FileID], [Expr1005]) OPTIMIZED WITH UNORDERED PREFETCH)
|--Index Seek(OBJECT:([PA10303].[dbo].[UploadedFiles].[_dta_index_UploadedFiles_10_172631758__K11_K12_K1_K2_5_7_13_16]), SEEK:([PA10303].[dbo].[UploadedFiles].[Deleted]='2007-10-01 00:00:00.000') ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([PA10303].[dbo].[UploadedFiles].[IXz_UploadedFiles_FileID]), SEEK:([PA10303].[dbo].[UploadedFiles].[FileID]=[PA10303].[dbo].[UploadedFiles].[FileID] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD)
2)
SELECT * FROM UploadedFiles
WHERE Deleted is null
For this query the estimated plan shows a plan based (accurately) on many rows meeting the condition, therefore it does a scan.
|--Clustered Index Scan(OBJECT:([PA10303].[dbo].[UploadedFiles].[IXz_UploadedFiles_FileID]), WHERE:([PA10303].[dbo].[UploadedFiles].[Deleted] IS NULL))
So I would have to conclude that nulls are included in the statistics although they do not show via "DBCC SHOW_STATISTICS".
October 30, 2007 at 9:57 am
I just noticed something. Although as I noted earlier...
SELECT * FROM UploadedFiles
WHERE Deleted is null
For this query the estimated plan shows a plan based (accurately) on many rows meeting the condition, therefore it does a scan.
|--Clustered Index Scan(OBJECT[PA10303].[dbo].[UploadedFiles].[IXz_UploadedFiles_FileID]), WHERE[PA10303].[dbo].[UploadedFiles].[Deleted] IS NULL))
It's the case where I also include the next column of the index that the query plan is greviously bad.
SELECT * FROM UploadedFiles
WHERE Deleted is null and recycleddate is null
|--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [PA10303].[dbo].[UploadedFiles].[FileID], [Expr1004]) OPTIMIZED WITH UNORDERED PREFETCH)
|--Index Seek(OBJECT:([PA10303].[dbo].[UploadedFiles].[IDXX_UploadedFiles_RecycledDate_Deleted_fileID_OrgFilename]), SEEK:([PA10303].[dbo].[UploadedFiles].[RecycledDate]=NULL AND [PA10303].[dbo].[UploadedFiles].[Deleted]=NULL) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([PA10303].[dbo].[UploadedFiles].[IXz_UploadedFiles_FileID]), SEEK:([PA10303].[dbo].[UploadedFiles].[FileID]=[PA10303].[dbo].[UploadedFiles].[FileID] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD)
SELECT count(*) FROM UploadedFiles
WHERE Deleted is null and recycleddate is null
shows:
-----------
2225625
So, the query plan is bad only when including the second column, which is also a datetime column where almost all the values are null. I don't see a great need to have that second column in the index key; I'll add it instead as a Include column and see if that solves the issue.
October 30, 2007 at 10:04 am
Adding the other column will only help if it turns the index into a covering index. If you're doing SELECT *... I probably won't do anything.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply