August 10, 2011 at 10:25 am
Sorry of I am posting in the wrong place, but I cannot find where the metadata I am loking for is found. I need it for for an install/update script that will run rebuild index (with online = on option) code only where the online = off option is currently in place.
Thanks in advance!
August 10, 2011 at 10:47 am
Metadata? That's just a setting on an index rebuild, not based on metadata
If you want to change the script, why not a find/replace?
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
August 10, 2011 at 11:00 am
I am not explaining this correctly.
What I want is to add code that will only rebuild the index to change the online = option to 'on' where it is currently set to 'off'.
For that, I need to know where the metadata is located so I can add something to my current
if not exists (select 1 from sys.indexes where name = 'XXX' and fill_factor = 80)....
August 10, 2011 at 11:17 am
But it's not set anywhere. That's not something that's stored anywhere in the metadata. It is purely and solely an option specified for an index rebuild operation and it only affects that index rebuild, not future ones.
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
August 10, 2011 at 11:20 am
Thanks.
I had misunderstood and thought the setting effected future rebuilds.
August 10, 2011 at 11:27 am
duncfair (8/10/2011)
Thanks.I had misunderstood and thought the setting effected future rebuilds.
Maybe just understood for the wrong object.
Statistics have this behavior. If you use sample 50% this time, then the next time it'll be 50% if you don't specify a new sampling rate.
August 10, 2011 at 11:38 am
Ninja's_RGR'us (8/10/2011)
Statistics have this behavior. If you use sample 50% this time, then the next time it'll be 50% if you don't specify a new sampling rate.
Depends how you update them
Edit: The only statistics update that keeps the old sampling rate is EXEC sp_updatestats @resample = 'resample'
The valid options for the @resample parameter are 'resample' and 'no'. The default is 'no' which does not keep the previous update's sampling rate.
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
August 10, 2011 at 11:44 am
Am I clearer by saying this instead?
sp_updatestats AND updatestats X keep the current sampling rate
UDPATE STATS WITH <fullscan or smapling rate> overwrites the old setting and saves the new one.
August 10, 2011 at 11:56 am
Ninja's_RGR'us (8/10/2011)
sp_updatestats AND updatestats X keep the current sampling rate
UPDATE STATISTICS TestingRangeQueries idx_TestingStats WITH SAMPLE 50 PERCENT
DBCC SHOW_STATISTICS(TestingRangeQueries, idx_TestingStats) WITH STAT_HEADER
-- Rows = 1000000, Rows Sampled = 498191. Close enough to 50 percent
-- update 1 row so that sp_updatestats will actually do something
UPDATE TOP (1) dbo.TestingRangeQueries SET SomeValue = SomeValue + 1;
EXEC sp_updatestats
DBCC SHOW_STATISTICS(TestingRangeQueries, idx_TestingStats) WITH STAT_HEADER
-- Rows = 1000000, Rows Sampled = 40425. That's more like 4%
--------------------------------
UPDATE STATISTICS TestingRangeQueries idx_TestingStats WITH SAMPLE 50 PERCENT
DBCC SHOW_STATISTICS(TestingRangeQueries, idx_TestingStats) WITH STAT_HEADER
-- Rows = 1000000, Rows Sampled = 498191. Close enough to 50 percent
UPDATE STATISTICS TestingRangeQueries idx_TestingStats
DBCC SHOW_STATISTICS(TestingRangeQueries, idx_TestingStats) WITH STAT_HEADER
-- Rows = 1000000, Rows Sampled = 40425. That's more like 4%
The only one that keep the previous sampling rate is this one:
EXEC sp_updatestats @resample = 'resample'
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
August 10, 2011 at 12:02 pm
Ninja's_RGR'us (8/10/2011)
Am I clearer by saying this instead?sp_updatestats AND updatestats X keep the current sampling rate
UDPATE STATS WITH <fullscan or smapling rate> overwrites the old setting and saves the new one.
It's clearer alright... just completely wrong.
Tx again Gail.
August 10, 2011 at 1:30 pm
...and which one does auto_update stats run?
August 10, 2011 at 1:32 pm
Ninja's_RGR'us (8/10/2011)
...and which one does auto_update stats run?
None of them. All of those are manual stats updates
As for 'which behaviour does auto update have', well, is the SQL Server there not working? 😉
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
August 10, 2011 at 1:36 pm
GilaMonster (8/10/2011)
Ninja's_RGR'us (8/10/2011)
...and which one does auto_update stats run?None of them. All of those are manual stats updates
As for 'which behaviour does auto update have', well, is the SQL Server there not working? 😉
Got corrupted... ;-).
Ok, building a table... I just thought this would make a darn good point in the conversation for people reading this thread :Whistling:
August 10, 2011 at 1:42 pm
Ninja's_RGR'us (8/10/2011)
Ok, building a table... I just thought this would make a darn good point in the conversation for people reading this thread :Whistling:
It's 10pm here. Not doing any more SQL tonight.
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
August 10, 2011 at 1:46 pm
GilaMonster (8/10/2011)
Ninja's_RGR'us (8/10/2011)
Ok, building a table... I just thought this would make a darn good point in the conversation for people reading this thread :Whistling:It's 10pm here. Not doing any more SQL tonight.
It's alright, I was hoping to save 10 minutes. I'll keep you posted of what I find
like you need this info :-D.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply