Performance
Hidden Tricks To SQL Server Table Cleanup
Problem Your SQL Server environment has millions of obsolete rows that need to be removed but you can’t impact the business and there is no maintenance window. How do you remove those rows with no business impact and avoid getting your boss on a tirade? What little known SQL tricks can you leverage to ensure a quick and non disruptive cleanup? Solution Building upon my earlier article published in SQLServerCentral, Large Table Clearnup with Minimal Locks , I have added to the small batch concept discussed there one key improvement: A coding trick to allow very efficient deletions by ordered primary key.
In earlier installments of this series we looked at T-SQL Performance optimizations along with different T-SQL practices, we can now turn our attention to the second part of this series which is index Tuning In looking into Views we will break the article into two sections : Basic Index Tuning using DTA (Database Tuning Advisor) to indentify indexes that need to be reviewed and validated against the current workload of your T-SQL queries. Advanced Index Tuning using a Query Execution plan to find missing indexes and determine the expected percent of enhancement for expensive queries. Basic Index Tuning using DTA This method offers the benefit of simplicity as well as detailed reporting and a broad scope of tuning options which can include indexes, filtered indexes, indexed views and schema partitioning that it can’t be covered easily by the advanced method.
SQL Server Performance Tuning - Index Tuning
If you are relying on using 'best-practice' percentage-based thresholds when you are creating an index maintenance plan for a SQL Server that checks the fragmentation in your pages, you may miss occasional 'edge' conditions on larger tables that will cause severe degradation in performance. It is worth being aware of patterns of data access in particular tables when judging the best threshold figure to use. In this article I’ll be describing an edge case related to logical and internal fragmentation within a specific index branch that may cause performance issues, and also I’d like to contribute to the debate about the use of “global” thresholds for your maintenance plans. Let’s suppose you have a table with a structure that holds 5 rows per page and leaves almost no space to accommodate changes. After a complete index rebuild with a fillfactor of 100%, the pages would be almost full and you should see a minimal logical fragmentation in your index.
No Significant Fragmentation? Look Closer…
Disk Partition Alignment Best Practices for SQL Server
Writers: Jimmy May, Denny Lee Contributors: Mike Ruthruff, Robert Smith, Bruce Worthington, Jeff Goldner, Mark Licata, Deborah Jones, Michael Thomassy, Michael Epprecht, Frank McBath, Joseph Sack, Matt Landers, Jason McKittrick, Linchi Shea, Juergen Thomas, Emily Wilson, John Otto, Brent Dowling Technical Reviewers: Mike Ruthruff, Robert Smith, Bruce Worthington, Emily Wilson, Lindsey Allen, Stuart Ozer, Thomas Kejser, Kun Cheng, Nicholas Dritsas, Paul Mestemaker, Alexei Khalyako, Mike Anderson, Bong Kang Published: May 2009 Applies to: SQL Server 2008
SAN Storage Best Practices for SQL Server
I bet you’re here because you don’t trust your SAN administrator. The SAN admin’s been telling you everything’s fine, and that it must be a SQL Server problem, right? Odds are, it’s not. Time to learn what SQL Server needs from its storage, and what SAN admins need from us database administrators. SQL Server Storage Performance Tuning
Published: November 1, 2006 OLTP work loads are characterized by high volumes of similar small transactions. It is important to keep these characteristics in mind as we examine the significance of database design, resource utilization and system performance.
Top SQL Server 2005 Performance Issues for OLTP Applications
Storage Top 10 Best Practices
Published: October 17, 2006 Proper configuration of IO subsystems is critical to the optimal performance and operation of SQL Server systems. Below are some of the most common best practices that the SQL Server team recommends with respect to storage configuration for SQL Server. Understand the IO characteristics of SQL Server and the specific IO requirements / characteristics of your application. In order to be successful in designing and deploying storage for your SQL Server application, you need to have an understanding of your application’s IO characteristics and a basic understanding of SQL Server IO patterns. Performance monitor is the best place to capture this information for an existing application.
Published: October 1, 2005 Writers: Sunil Agarwal, Boris Baryshnikov, Tom Davidson, Keith Elmore, Denzil Ribeiro, Juergen Thomas Applies To: SQL Server 2005
Troubleshooting Performance Problems in SQL Server 2005