Featured Article(s)
DBA Learning Strategies (Part 1 of 4)
DBA learning strategies: how we are going to take what you need to know and find out how you are going to learn it and then put that into practice. And this is just like anything else. It is a matter of discipline.
Webcast: Using Log Shipping for High Availability
Log Shipping has been around in one form or another since SQL Server 2000 and many “homegrown†and third-party solutions have recently become available. This session will cover all you need to know about log shipping to use it effectively for high availability. We will look at set up, maintenance, monitoring, and failover. In addition, we will look at other SQL Server objects that you need to account for when using Log Shipping for HA. This session is for beginning to advanced SQL Server DBAs with little or no Log Shipping experience.
Presented by: Eric Johnson
> Register Now
> Live date: 7/14/2010 at 12:00 Pacific
Last Thoughts on Indexing
Sergei wrote in to say, quite simply, "We run Ola Hallngren’s stored procedure to maintain our indexes on a weekly basis. http://ola.hallengren.com/"
…And, from our very own Ben Taylor – "Unlike others, I have found Index Fragmentation to be a large cause of system degradation on certain systems. Even with high performing SAN storage, fragmentation slows one of the systems I support dramatically.
Two elements I found that cause issues are:
* Lots of movement of data-Inserts/Deletes/Updates
* Large Volumes of Data that are consistently queried
One system I maintain has nearly 100% modification of a large volume of data almost daily. As a result, indexes and fragmentation is a huge problem. I found that to keep system performance on this OLTP/OLAP hybrid running at peak performance throughout the day I had to take three approaches:
Reorganize the indexes throughout the day when updates are occurring. The reorganize only updates the leaf nodes and does not lock the table allowing queries to continue without blocking.
Reindex fragmented tables daily during off hours. Originally we simply ran a maintenance plan that reindexed all tables. However, it got to the point it could not complete in the maintenance windows. I modified a process I posted to SSWUG years ago that reviews each table and it’s fragmentation. It performs an ALTER INDEX command on any table with a fragmentation percentage above my allowed level.
Separate certain tables onto dedicated drives. Even pre-allocating space in the database was not adequate. The need was for the database to allocate contiguous pages on a database file. So, placing certain tables on specific database files allowed them to expand and contract without interleaving with other tables of the same database.
Clearly this is a unique situation. I have never had to do anything so dramatic as assigning specific devices to a table previously. However, using an automated method to maintain your fragmentation is a good practice in almost any situation. Even on a small database, you will get a better cache hit ratio by implementing a simple maintenance job."
Guest Editor
Ben Taylor will be joining us for a bit and working with you, doing editorials. He has excellent experiences to share and I really look forward to reading more of his suggestions, tips and tricks. Welcome Ben!