| Author |
Topic |
|
dewacorp.alliances
452 Posts |
 Posted - 2009-05-27 : 03:23:36
|
| Hi thereThe Database Tuning Advisor is recomending that I need to create a bunch of statistic for the same table. Can someone explain why it recommends more than one for creating statitic on the same table?!?!Here's the recomendations:CREATE STATISTICS [_dta_stat_260912001_14_1_4_7] ON [dbo].[brp_Services]([IsActive], [ServiceID], [ServiceNo], [BatchID])CREATE STATISTICS [_dta_stat_260912001_4_3_7_14_1] ON [dbo].[brp_Services]([ServiceNo], [TenantID], [BatchID], [IsActive], [ServiceID])CREATE STATISTICS [_dta_stat_260912001_7_1] ON [dbo].[brp_Services]([BatchID], [ServiceID])CREATE STATISTICS [_dta_stat_260912001_1_3_7] ON [dbo].[brp_Services]([ServiceID], [TenantID], [BatchID])CREATE STATISTICS [_dta_stat_260912001_1_4_7_3] ON [dbo].[brp_Services]([ServiceID], [ServiceNo], [BatchID], [TenantID]) |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-27 : 03:33:16
|
As you can see, the statistics are for different set of column pairs.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
dewacorp.alliances
452 Posts |
Posted - 2009-05-27 : 03:54:02
|
quote:
As you can see, the statistics are for different set of column pairs.
Hi Peso .. I don't quite understand though. Could you explain a bit further? Does this mean I have to apply this recomendation one by one?Thanks |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-27 : 03:57:38
|
Tuning advisor generated 5 sets of statistics create statements, because it found the need for it.Probably you are using 5 different queries against the table, and thus sql engine needs a statistics set for each and one of them.Statistics are normally updated every time you SELECT from that table (it auto statistics is set).
E 12°55'05.63"N 56°04'39.26" |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2009-05-27 : 04:06:25
|
quote:
Originally posted by Peso
Tuning advisor generated 5 sets of statistics create statements, because it found the need for it.Probably you are using 5 different queries against the table, and thus sql engine needs a statistics set for each and one of them.Statistics are normally updated every time you SELECT from that table (it auto statistics is set).
E 12°55'05.63"N 56°04'39.26"
Hi PesoIn this DB, it sets to Auto Create Statistic and Auto Update Statistic both are true. Does this mean I have to ignore this then?Thanks |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-27 : 04:53:19
|
No. The statistics are problably not even created to start with.Give the Tuning Advisor a chance. Create the statistics.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2009-05-27 : 09:41:48
|
| You can use sp_helpstats to see what the automatically created statistics are on. I think the auto-stats feature can only do single columns. |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2009-05-27 : 17:12:54
|
quote:
Originally posted by Peso
No. The statistics are problably not even created to start with.Give the Tuning Advisor a chance. Create the statistics.
E 12°55'05.63"N 56°04'39.26"
Do I need to apply this 5 sets of statistic? Or I can just combine this into 1 sets containing all possibilities columns that are mentioned in this 5 sets ei: ([IsActive], [ServiceID], [ServiceNo], [BatchID], [TenantID]) |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2009-05-28 : 09:33:48
|
| Sounds like you have something to test on your own. First, measure the performance of a few queries without any new statistics at all. Then apply your one monster statistic, and retest the queries you tested before to see if there is any performance improvement. Then remove the monster statistic, apply the 5 separate statistics from the DTA, and measure the performance yet again. The only way to really know if a thing will help you is if you test it. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-05-28 : 23:30:49
|
| You can't blindly add statistics as DTA or DMV suggested. Like Mcrowley said, It is best to test it. |
|
|
|
|
"Create Statistic" on SQL Server 2005