October 16, 2012 at 11:20 am
I was recently asked to health-check a system. This term can cover a wide variety of topics and many specifics with regard to best practices. Here is my question: How much do the database fundamentals apply from your point of view?
From my point of view, if the data is not properly organized and stats are not up to date, it is difficult to carry any assessment much further. I realize that 'it depends' applies, but would you agree or disagree and why?
To state the question another way, assuming that everything seems to be in order at the hardware, operating system, and generally at the database level, how important do you consider rebuilds and update statistics.
October 16, 2012 at 11:58 am
Hi,
Rebuilds and update statistics need to be done frequently.
This takes care of fragmentation on indexes and in my environment did result in faster data access.
It did make the report generation 20 minutes faster from its usual time of 40 minutes 🙂
Its very important tool to execute.
Also please rebuild only those indexes that have scan density of more than 70%.
Although one thing to note is that post index rebuilding, the transaction log grows. It needs to be shrunk.
Thanks
Satyen
October 16, 2012 at 12:25 pm
menon.satyen (10/16/2012)
Although one thing to note is that post index rebuilding, the transaction log grows. It needs to be shrunk.
No, it absolutely does not. Please read through this - Managing Transaction Logs[/url]
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
October 16, 2012 at 12:28 pm
Index rebuilds... very much an 'it depends'
Depends on the fragmentation %, the size of the table and how the table is accessed. Low fragmentation, small table or mostly seeks and there's little point in rebuilding the indexes. Low avg page space used, then you probably do want to rebuild, as the index will be taking more space than it needs.
Stats maintenance, again, it depends. In many cases the triggered stats updates are adequate. When you find cases where they aren't, then you need to update manually. Finding those cases is a pain, so many people just blanket update all stats on a regular basis. It works, it may not be the most effective, but it works (depending on the size of the DB and the available maintenance period)
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
October 16, 2012 at 1:14 pm
Thank you for your replies. Most of the transactions are somewhat small, so large contiguous reads should not be an issue in terms of data organization or statistics.
Still, I think it is difficult to get a true baseline for how the database is operating, unless the playing field is level. For example, if one environment is updated regularly and another is not, it is difficult to expect to have a quality review of transactions. If test and production are equivalent, and the indexes are optimized, and the stats are optimized, I would expect that there would be a *better* comparison.
Another issue crops up with 24x7 operations. Customers do not want to interfere with database access, so maintenance goes undone. I am not sure of the best way to handle this, but a lot shops try to sneak them in edgewise, which hardly seems like a good idea.
I am aware that the stats may be updated as needed by the optimization process, but wonder if this is adequate, given a partial read of the table on the stats update.
Thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply