DETA recommendations..whats my next step?

Question

DETA recommendations..whats my next step?

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Pyman SSChampion Points: 13830 More actions · Answer 1

Sure. I just verified again if any of our databases are shrinking through any maintenance plans or jobs and I don't see it happening.

Wondering why it happens so quickly.

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Jared SSC Guru Points: 61793 More actions · Answer 2

What are the fill factor settings for each of your indexes?

Thanks,

Jared

Jared
CE - Microsoft

Pyman SSChampion Points: 13830 More actions · Answer 3

80

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Jared SSC Guru Points: 61793 More actions · Answer 4

Take a look at this article as well: http://blog.sqlauthority.com/2009/12/16/sql-server-fillfactor-index-and-in-depth-look-at-effect-on-performance/

Thanks,

Jared

Jared
CE - Microsoft

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

What are the data types of your key values? Int? Or something else?

CEWII

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

99% fragmentation is meaningless on 25 pages.

Run the same query with 1000 pages minimum and see what transpires.

This is what I use to auto-fix fragmentation.

http://sqlfool.com/2011/06/index-defrag-script-v4-1/

Here's how I tuned the fill factor to extend the time between defrags.

http://www.sqlservercentral.com/Forums/Topic1126147-146-1.aspx

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

To me DTA is more dangerous than anything else. You must REALLY understand tuning to use it without danger. And once you're that good you just don't need it.

Here's a better way to tune your server & to get max impact for the lowest time investment.

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2

Pyman SSChampion Points: 13830 More actions · Answer 8

Also I forgot to mention that the DETA recommended to create a lot of statistics along with the indexes. Right now I am about to rebuild the indexes. Is it a good practice to do an exec sp_updatestats once the indexes are rebuild?

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Sapen (10/26/2011)
Also I forgot to mention that the DETA recommended to create a lot of statistics along with the indexes. Right now I am about to rebuild the indexes. Is it a good practice to do an exec sp_updatestats once the indexes are rebuild?

Rebuilding the indexes will recreate the statitics associated with the indexes, no need to update them after the rebuild.

Pyman SSChampion Points: 13830 More actions · Answer 10

ok..thank you

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Pyman SSChampion Points: 13830 More actions · Answer 11

I increased the size of the log for index rebuild and now want to release the unused space in the log. I did a transaction log backup with Truncate the transaction log option and I see no change.

when I query on sys.databases the log_reuse_wait_desc column says NOTHING for the database.

How do I release the unused space?

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

You'll just need it again next time you rebuil.

Unless you're short on hd space leave it as is.

Shrink + growth + shrink + growth = physical file fragmentation.

If you know you'll only need to shrink this 1 time, then you can use shrinkfile on the log. Make sure to leave plenty of free room for the normal "big" day to day operations.