December 1, 2010 at 8:52 am
this morning's statistics for the column
Statistics for INDEX 'idx_timegen'.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Updated Rows Rows Sampled Steps Density Average Key Length
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
idx_timegen Dec 1 2010 7:03AM 181606437 531762 198 0.09233826 8 NO
All Density Average Length Columns
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2.060709E-05 8 TimeGenerated
Histogram Steps
RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
12/24/2009 12:40:07 AM 0 1 0 1
1/13/2010 1:05:28 PM 1277040 5443.009 2048 623.5607
2/4/2010 10:41:47 AM 638007.8 5443.009 1108 575.6527
3/8/2010 6:52:15 PM 937543.5 2381.317 991 946.1887
4/8/2010 8:57:19 AM 1113440 3401.881 1348 825.8375
5/3/2010 1:04:16 PM 968624.2 4762.633 1114 869.2326
6/13/2010 2:00:09 PM 1005511 5443.009 1100 913.8607
8/1/2010 10:05:21 PM 451865.2 5443.009 763 592.2408
11/1/2010 1:10:07 AM 961110.2 21772.04 1226 784.0817
11/1/2010 4:46:53 AM 839519.8 19390.72 184 4569.575
11/1/2010 7:49:22 AM 600779.3 21091.66 271 2216.41
11/1/2010 10:34:05 AM 692655.1 10205.64 353 1960.084
11/1/2010 4:32:59 PM 867185.1 19050.53 375 2309.622
11/1/2010 6:30:30 PM 628444.4 9525.267 248 2534.36
11/1/2010 10:11:19 PM 1012001 19730.91 291 3476.019
11/2/2010 7:52:53 AM 1285579 26534.67 224 5742.374
11/2/2010 1:30:41 PM 577212.6 20411.29 199 2903.804
11/2/2010 5:15:22 PM 913635.3 19050.53 318 2870.87
11/2/2010 7:14:48 PM 979212.1 19730.91 177 5541.913
11/2/2010 10:13:17 PM 966233.4 20411.29 187 5174.462
11/2/2010 11:16:24 PM 509586.5 24493.54 181 2819.949
11/3/2010 5:13:57 AM 971356.6 30616.93 475 2045.593
11/3/2010 9:01:06 AM 873674.4 18370.16 201 4351.273
11/3/2010 11:48:55 AM 851132.4 20071.1 178 4789.823
11/3/2010 4:04:59 PM 874015.9 20411.29 195 4487.6
11/3/2010 7:41:10 PM 1014050 19050.53 228 4449.705
11/4/2010 1:26:43 AM 984676.8 23472.98 242 4069.817
11/4/2010 4:45:37 AM 705292.3 20411.29 97 7242.444
11/4/2010 9:11:55 AM 730908.3 30616.93 99 7353.867
11/4/2010 1:15:16 PM 1308121 27895.42 311 4203.256
11/4/2010 5:14:04 PM 1261671 42183.32 324 3890.84
11/4/2010 7:30:54 PM 677968.6 25854.29 70 9647.007
11/4/2010 11:45:58 PM 910561.4 23472.98 168 5431.1
11/5/2010 3:48:30 AM 694704.4 30616.93 92 7521.406
11/5/2010 6:14:56 AM 698119.8 22112.23 459 1521.652
11/5/2010 10:48:45 AM 1083725 20751.47 386 2811.18
11/5/2010 2:21:25 PM 940617.4 31977.68 189 4983.694
11/5/2010 4:00:01 PM 817319.3 20411.29 251 3256.493
11/5/2010 5:55:11 PM 604536.3 25514.11 125 4817.303
11/5/2010 6:59:19 PM 461770.1 32998.25 84 5475.598
11/6/2010 1:08:54 AM 969990.4 23813.17 134 7264.542
11/6/2010 9:38:16 AM 775309.2 20411.29 236 3286.284
11/6/2010 5:03:36 PM 602828.5 20411.29 275 2191.504
11/6/2010 6:55:15 PM 777700 21091.66 159 4902.86
11/6/2010 11:12:20 PM 680359.4 19390.72 154 4429.361
11/7/2010 2:07:37 AM 1005170 20411.29 230 4372.217
11/7/2010 5:12:10 AM 891093.3 20411.29 390 2287.664
11/7/2010 10:51:15 AM 1103535 19050.53 343 3214.106
11/7/2010 3:37:43 PM 995264.8 21772.04 255 3903.044
11/7/2010 5:58:22 PM 649620.3 28915.99 162 4019.096
11/7/2010 10:36:05 PM 1006877 28915.99 247 4076.991
11/8/2010 1:57:40 AM 615465.7 21772.04 142 4347.884
11/8/2010 5:23:04 AM 554670.6 21772.04 147 3784.216
11/8/2010 11:42:57 AM 685824.2 18370.16 278 2466.222
11/8/2010 1:34:36 PM 732957.5 21772.04 380 1926.373
11/8/2010 4:06:48 PM 862744.9 10886.02 140 6182.451
11/8/2010 5:11:17 PM 692996.6 20411.29 199 3486.283
11/8/2010 7:52:52 PM 1215220 21772.04 284 4277.281
11/8/2010 10:14:20 PM 698461.4 20751.47 85 8184.813
11/9/2010 4:16:13 AM 1054011 10205.64 138 7663.326
11/9/2010 9:46:29 AM 1040349 25173.92 169 6168.291
11/9/2010 2:37:05 PM 1053328 10205.64 259 4066.702
11/9/2010 9:40:08 PM 1037958 22792.6 318 3261.522
11/9/2010 10:51:26 PM 340862.8 20411.29 82 4140.477
11/10/2010 5:22:30 AM 1041032 10545.83 190 5486.539
11/10/2010 8:23:30 AM 693679.8 10205.64 144 4831.882
11/10/2010 9:34:05 AM 703584.6 10205.64 87 8055.32
11/10/2010 1:17:20 PM 1047180 21431.85 194 5404.56
11/10/2010 3:50:30 PM 701193.8 10886.02 160 4392.74
11/10/2010 5:35:08 PM 706316.9 12927.15 111 6338.209
11/10/2010 7:19:18 PM 694362.8 21772.04 164 4243.202
11/10/2010 8:29:59 PM 712806.3 10886.02 88 8068.166
11/10/2010 10:47:01 PM 729883.6 1339.47 114 6377.333
11/11/2010 4:02:06 AM 1036933 15648.65 253 4098.725
11/11/2010 8:17:31 AM 700510.6 16329.03 212 3306.964
11/11/2010 10:55:03 AM 698461.4 7824.326 290 2407.376
11/11/2010 1:47:19 PM 707000 542.0406 94 7491.672
11/11/2010 5:16:52 PM 708366.2 17009.4 144 4934.182
11/11/2010 6:39:37 PM 727492.8 3401.881 85 8525.013
11/12/2010 12:42:54 AM 1047521 20411.29 234 4478.214
11/12/2010 3:09:01 AM 735348.3 11906.58 241 3051.968
11/12/2010 6:08:57 AM 708707.8 11566.4 106 6659.637
11/12/2010 10:37:56 AM 1082701 30616.93 214 5063.21
11/12/2010 3:26:16 PM 719295.7 14628.09 110 6513.353
11/12/2010 7:08:04 PM 1147253 21772.04 206 5574.452
11/12/2010 9:28:22 PM 840202.9 14287.9 147 5732.248
11/13/2010 3:31:13 AM 1382919 20411.29 182 7610.517
11/13/2010 5:39:36 AM 584726.6 22112.23 76 7663.412
11/13/2010 9:55:45 AM 1305047 30616.93 190 6877.975
11/13/2010 2:09:02 PM 976138.2 20751.47 221 4419.632
11/13/2010 5:03:04 PM 929004.9 19390.72 151 6169.083
11/13/2010 6:36:07 PM 881529.9 20751.47 179 4933.019
11/13/2010 8:32:33 PM 611025.6 20071.1 100 6086.221
11/14/2010 12:33:08 AM 1105243 30616.93 194 5704.226
11/14/2010 2:49:31 AM 635616.9 30616.93 91 6957.299
11/14/2010 5:54:18 AM 689239.6 10205.64 147 4702.307
11/14/2010 9:13:31 AM 1045472 20411.29 204 5129.951
11/14/2010 12:05:09 PM 640398.6 20411.29 149 4310.051
11/14/2010 4:41:25 PM 902705.8 21091.66 406 2225.696
11/14/2010 7:17:52 PM 826541.1 30616.93 116 7097.364
11/15/2010 12:11:05 AM 1075187 23472.98 312 3443.683
11/15/2010 5:59:59 AM 696753.6 10205.64 309 2253.348
11/15/2010 11:18:46 AM 1038300 20411.29 246 4221.384
11/15/2010 3:53:19 PM 697778.3 1700.94 184 3798.064
11/15/2010 6:27:58 PM 1090898 24153.35 252 4329.211
11/15/2010 7:48:22 PM 696412.1 6803.762 85 8160.799
11/16/2010 1:05:06 AM 698461.4 1339.47 125 5565.754
11/16/2010 4:00:27 AM 857280.2 30616.93 195 4401.671
11/16/2010 6:50:41 AM 692996.6 21772.04 91 7585.362
11/16/2010 1:06:32 PM 1086458 30616.93 313 3468.63
11/16/2010 2:51:28 PM 682408.7 20411.29 105 6473.578
11/16/2010 6:54:28 PM 1395557 7484.138 304 4587.799
11/17/2010 2:02:05 AM 1391800 21772.04 496 2806.413
11/17/2010 7:33:53 AM 1393849 11906.58 213 6549.031
11/17/2010 12:49:25 PM 1389750 20411.29 378 3671.945
11/17/2010 4:55:54 PM 702218.4 6123.386 160 4399.159
11/17/2010 11:37:25 PM 1398289 10205.64 312 4478.538
11/18/2010 3:03:46 AM 700852.2 1339.47 83 8410.72
11/18/2010 6:42:39 AM 697436.8 10205.64 183 3817.063
11/18/2010 11:28:40 AM 1384969 20411.29 378 3659.311
11/18/2010 2:22:28 PM 747302.4 12927.15 170 4404.584
11/18/2010 4:44:30 PM 729883.6 10205.64 261 2796.269
11/18/2010 6:04:36 PM 691630.4 12927.15 83 8300.053
11/18/2010 9:11:31 PM 698119.8 11906.58 110 6321.602
11/19/2010 2:12:39 AM 1393507 18370.16 308 4521.375
11/19/2010 8:53:07 AM 1405461 10205.64 270 5204.338
11/19/2010 12:08:01 PM 758573.4 12927.15 177 4293.194
11/19/2010 2:46:24 PM 743545.4 9185.078 184 4047.179
11/19/2010 5:12:17 PM 1389750 12246.77 311 4465.548
11/19/2010 6:24:57 PM 716563.3 30616.93 128 5620.083
11/19/2010 7:28:03 PM 689239.6 11566.4 159 4345.179
11/20/2010 3:43:41 AM 701193.8 12927.15 95 7351.938
11/20/2010 6:18:35 AM 704950.8 1339.47 91 7716.209
11/20/2010 10:32:11 AM 1395898 11566.4 299 4665.917
11/20/2010 12:55:23 PM 685824.2 30616.93 148 4647.176
11/20/2010 4:10:44 PM 688215 20411.29 84 8160.747
11/20/2010 5:33:08 PM 755841.1 32658.06 124 6071.561
11/20/2010 10:07:03 PM 977162.8 21091.66 312 3129.726
11/21/2010 1:01:13 AM 680359.4 19050.53 108 6274.862
11/21/2010 5:53:24 AM 752767.2 20411.29 99 7573.796
11/21/2010 8:12:36 AM 720320.3 20411.29 164 4401.826
11/21/2010 9:36:20 AM 782823.2 20411.29 97 8038.587
11/21/2010 3:10:41 PM 720320.3 21772.04 110 6522.631
11/21/2010 6:00:51 PM 768819.8 20751.47 148 5209.559
11/21/2010 7:38:22 PM 703243 21091.66 164 4297.468
11/21/2010 11:42:43 PM 826882.6 21772.04 113 7288.793
11/22/2010 3:38:57 AM 1047521 2381.317 182 5764.746
11/22/2010 6:58:45 AM 698802.9 23472.98 159 4405.469
11/22/2010 1:05:45 PM 802974.4 24833.73 113 7078.048
11/22/2010 3:33:18 PM 818002.4 22452.41 196 4178.462
11/22/2010 5:34:52 PM 1044447 10205.64 161 6502.213
11/22/2010 9:20:00 PM 1005170 21772.04 208 4836.888
11/23/2010 1:33:50 AM 928663.3 20411.29 342 2712.715
11/23/2010 8:52:25 AM 1888066 28235.61 247 7645.05
11/23/2010 1:13:33 PM 777358.4 31977.68 203 3833.247
11/23/2010 4:10:38 PM 1048204 4422.445 186 5643.783
11/23/2010 6:40:40 PM 915001.4 22452.41 211 4340.099
11/23/2010 9:36:56 PM 718954.1 23813.17 185 3892.059
11/24/2010 2:50:16 AM 1042740 6803.762 226 4616.268
11/24/2010 4:48:05 AM 665331.4 40822.57 85 7796.584
11/24/2010 9:00:51 AM 1026004 21091.66 358 2862.736
11/24/2010 12:21:08 PM 755158 30957.12 218 3466.375
11/24/2010 5:14:39 PM 1040007 10205.64 202 5153.913
11/24/2010 6:20:20 PM 1048888 5443.009 155 6784.258
11/25/2010 3:22:24 AM 1048546 1700.94 216 4857.875
11/25/2010 8:07:35 AM 1042740 11566.4 150 6970.813
11/25/2010 1:09:06 PM 1040007 15648.65 338 3074.02
11/25/2010 4:24:19 PM 1045814 4762.633 165 6351.924
11/25/2010 6:48:27 PM 876406.8 40822.57 195 4499.875
11/25/2010 10:09:36 PM 1038983 20411.29 111 9323.42
11/26/2010 2:09:42 AM 1040690 21091.66 184 5664.563
11/26/2010 6:53:12 AM 1044447 10205.64 237 4408.314
11/26/2010 12:21:58 PM 1045814 11906.58 130 8075.262
11/26/2010 3:57:22 PM 1042057 10205.64 166 6290.748
11/26/2010 5:54:48 PM 1041373 11226.21 170 6137.832
11/26/2010 11:55:57 PM 1069039 14628.09 408 2622.82
11/27/2010 5:41:58 AM 1039324 11566.4 175 5949.73
11/27/2010 8:43:44 AM 1042740 10205.64 288 3619.039
11/27/2010 12:02:32 PM 1049229 8844.891 274 3828.306
11/27/2010 3:13:53 PM 1042057 10205.64 367 2836.03
11/27/2010 5:16:42 PM 1043423 16669.22 366 2847.528
11/27/2010 6:52:00 PM 1072796 13947.71 177 6071.555
11/27/2010 11:50:49 PM 1123003 30616.93 221 5084.587
11/28/2010 3:26:11 AM 1062208 11566.4 236 4502.354
11/28/2010 11:36:41 AM 1059817 11566.4 273 3881.155
11/28/2010 5:14:00 PM 1050937 10886.02 372 2821.651
11/28/2010 11:23:46 PM 1031469 20411.29 213 4846.379
11/29/2010 4:01:23 AM 1250058 22112.23 163 7686.168
11/29/2010 8:34:31 AM 1119246 20411.29 205 5465.026
11/29/2010 2:31:35 PM 1174235 17009.4 212 5543.316
11/29/2010 6:33:52 PM 1115489 27895.42 242 4610.483
11/29/2010 11:18:44 PM 1107633 17689.78 282 3926.349
11/30/2010 2:50:22 AM 1088848 20751.47 155 7042.728
11/30/2010 7:49:49 AM 1088507 20411.29 254 4285.575
11/30/2010 11:42:12 AM 1037275 30616.93 265 3913.711
11/30/2010 5:38:34 PM 1237421 22112.23 262 4722.54
11/30/2010 7:33:08 PM 846692.3 8844.891 102 8268.259
11/30/2010 7:33:10 PM 14.63288 26874.86 0 3742.379
December 1, 2010 at 8:57 am
alen teplitsky (12/1/2010)
Ninja's_RGR'us (12/1/2010)
Is this script scheduled?Are you getting any errors in the logs?
Are you logging the @@rowcount after each exec?
We don't have much to go on without that info...
i also have another step for the application log. did it this way to keep the amount of DML to a minimum for each step
So do you have any errors for any of the executions that failed?
December 1, 2010 at 9:07 am
no, always runs. sometimes it runs for hours. and once a month or so it gets stuck to where i have to stop it and delete data manually. i'll start at more than 40 days and go to 29.
one time when i was doing this is when i noticed that it had old data that shouldn't have been there. in the last week or so i had to do it and it shouldn't have had more than 32 days of data. in the last week it has been running OK but there seems to be old data in the table
might have to report this to Connect. the SQL Build is one of the latest if not the latest hotfix. but i've noticed this over the last year.
December 1, 2010 at 9:19 am
it is possible is wrong insert is generate oldeste data if you leave data next run data delete or leave for ever whitout intervention
December 1, 2010 at 9:31 am
alen teplitsky (12/1/2010)
no, always runs. sometimes it runs for hours. and once a month or so it gets stuck to where i have to stop it and delete data manually. i'll start at more than 40 days and go to 29.one time when i was doing this is when i noticed that it had old data that shouldn't have been there. in the last week or so i had to do it and it shouldn't have had more than 32 days of data. in the last week it has been running OK but there seems to be old data in the table
might have to report this to Connect. the SQL Build is one of the latest if not the latest hotfix. but i've noticed this over the last year.
Let's start with the very most obvious option :
The delete takes too long.
Ok fine, delete in small batches :
SET ROWCOUNT 50000
SELECT 'this starts the loop'
WHILE @@rowcount > 0
BEGIN
DELETE...
END
SET ROWCOUNT 0
Obivously make sure the where is correctly indexed.
You might also want to make sure that this is not running at the same time you have a reindex job or backups.
December 1, 2010 at 9:37 am
could be the backups
i'll try the loop and reschedule to avoid the backup window.
December 1, 2010 at 9:44 am
That's one of the reasons I wanted to see when it failed... my next questions would have been what's the error and what else is taking a lot of ressources at that time?
Another option could be that the log is growing too much because of the delete and the server is waiting on file growth.
To make sure it doesn't happen you can take a log backup in the loop every x executions to control the log size.
You can also consider switching to simple mode but I'm guessing you have too much valuable info to do that one... and even in that case the server needs to log the transaction untill it completes... so again you need to use batches if the delete fails becauses it's too big.
Plan Z might be to use partitioning (1 per day) and simply move that old partition out daily. I've heard it's faster but I've never seen that in action so I can't be sure of this info.
December 1, 2010 at 10:44 am
Are you using DBCC ShowStatistics to see what's in the table, or are you querying the table?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2010 at 2:37 pm
alen teplitsky (12/1/2010)
delete sql_server_logswhere timegenerated < getdate() - 30
and eventlog = 'Security'
Hey Alen, I know it's just an doublecheck, but the histogram above shows me something different then I think you see.
Which of the following code bits shows older records immediately after this is completed?
EDIT: (I have no idea why the following won't format properly)
select distinct
timegenerated
from
sql_server_logs
select distinct
timegenerated
from
sql_server_logs
WHERE
eventlog = 'Security'
The reason I ask is because nowhere so far (that I saw) has it been mentioned that this table stores only 30 days for Security data. You mention everything... but your delete query is specific. Can you confirm that the 'older records' aren't security?
The next step would be to see if there's a pattern to the older data. Is it always a little over a month old? I'd check which server(s) that it's coming from and make sure the local data on the computer isn't inaccurate.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply