Online transaction processing (OLTP), workloads are characterized by high volumes of similar small transactions. You need to keep this in mind when you are trying to troubleshoot resource utilization and system performance. The following resources provide performance information for OLTP applications:

• Transactional
  Online transaction processing (OLTP) systems, often simply referred to as transactional systems, are designed to process small, quick, interactive workloads for which users expect a fast response time. The following resources provide some best practices and some pitfalls to avoid when using transactional systems:
  
  • Top SQL Server 2005 Performance Issues for OLTP Applications. Examines the significance of database design, resource utilization, and system performance.
  • Troubleshooting Performance Problems in SQL Server 2008. Provides step-by-step guidelines for diagnosing and troubleshooting common performance problems by using publicly available tools.
  • Monitor SQL Server Performance and Activity with Built-In Functions. Lists the key built-in functions that return system information and their usages.
  • Performance Optimizations for the XML Data Type in SQL Server 2005. Explores several ideas to improve the query and data modification performance of the XML data type in SQL Server 2005.
  • Diagnosing Transaction Log Performance Issues and Limits of the Log Manager. Discusses how to determine if I/O to the transaction log file is a performance bottleneck and how to determine if this is storage related; a limitation is due to log manager itself or a combination of the two.
  • & SQL Server Performance Articles. Provides a number of articles that are useful in learning about SQL Server performance tuning.
  • Analyzing Deadlocks with SQL Server Profiler. Explains how to use SQL Server Profiler to identify the cause of a deadlock, which is a circular blocking chain, where two or more threads are each blocked by the other so that no one can proceed.
  • Deadlock Troubleshooting, Part 1. Provides information about how to troubleshoot deadlocks.
  • Scripts and Tools for Performance Tuning and Troubleshooting SQL Server 2005. Provides a toolbox of information to make performance tuning and troubleshooting easier.
  • & Procedures to Performance Tune SQL Server. Describes enhancement options available to DBAs that provide the best server performance to users and clients without interruptions or outages.
  • & SQL Server Performance Tuning Basics. Explains how to optimize server performance by integrating testing procedures in the earliest stages of the development process and database design.

• Relational
  The importance of physical database design, application code interaction, and operational best practices for pure relational systems is extremely important, but at times, it is skipped because of time pressure. The following links provide guidance for designing your databases and information about performance, availability, and manageability.
  
  • General Index Design Guidelines. Provides information about the characteristics of your database, queries, and data columns to help you design optimal indexes.
  • Using Statistics to Improve Query Performance. Provides information about statistics, which greatly impacts Microsoft SQL Server performance.
  • Using Sparse Columns. Describes sparse columns, which are ordinary columns that have an optimized storage for null values. Also provides estimated space savings by data type.
  • Best Practices for Semantic Data Modeling for Performance and Scalability. Provides best practices, in addition to some theoretical information and some practical hints for semantic data modeling.
  • & SQL Server Index Maintenance Performance Tuning for Large Tables.
  • Improving Performance with SQL Server 2008 Indexed Views. Explains indexed views and specific scenarios in which they may provide performance improvements.
  • Partitioned Table and Index Strategies Using SQL Server 2008. Explains indexed views and specific scenarios in which they may provide performance improvements.
    

    Note:
    In SQL Server 2005, you cannot switch any partition in or out of the partitioned table while a partitioned index is defined on it.

  • Troubleshooting DBCC Errors on Indexed Views. Describes how to resolve DBCC errors on indexed views.
  • Statistics Used by the Query Optimizer in Microsoft SQL Server 2008. Describes how statistics are used by the SQL Server query optimizer, and provides guidance when writing queries so you can obtain good query performance.
  • SQL Server Optimization. Describes different ways of improving the performance of SQL Server queries, with a focus on index optimization.
  • & Improving 2D Range Query Performance in SQL Server. Explains how you can rewrite BETWEEN queries for increased performance (more than 100 times faster).
  • Tuning the Performance of Change Data Capture in SQL Server 2008. Provides guidance on how to configure change data capture parameters to maximize data capture performance while minimizing the performance impact on the production workload.

• Scale-Up
  More and more scale-out architectures are being adopted for large-scale deployments in which the expected number of concurrent users is huge and the total cost of ownership (TCO) must be kept low. Good design for scale-up is of utmost importance for both scale-up and scale-out. The following links contain examples of customer scenarios for scale-up solutions, in addition to general scale-up reference material.
  
  • Diagnosing and Resolving Spinlock Contention on SQL Server. Describes how to identify and resolve spinlock contention issues observed when running SQL Server 2008 applications on high concurrency systems with certain workloads.
  • Diagnosing and Resolving Latch Contention on SQL Server. Provides in-depth information about the methodology the Microsoft SQLCAT team uses to identify and resolve issues related to page latch contention observed when running SQL Server 2008 and SQL Server 2008 R2 applications on high-concurrency systems.
  • Analyzing I/O Characteristics and Sizing Storage Systems for SQL Server Database Applications. Discusses input/output (I/O) in relation to Microsoft SQL Server database applications.
  • Data Compression: Strategy, Capacity Planning and Best Practices. Explains how data compression can help reduce the size of the database and improve the I/O intensive workloads performance.
  • The Data Performance Guide 2008. Describes techniques for bulk loading large data sets into SQL Server.
  • & SQL Q & A: Bottlenecks and Transaction Logs. Questions and answers about how to determine the nature of a performance bottleneck and how to configure transaction logs.
  • The Data Loading Performance Guide. Describes techniques for bulk loading large data sets into SQL Server and covers both available techniques as well as methodologies to performance tune and optimize the bulk loading process.
  • Tuning the Performance of Backup Compression in SQL Server 2008 and Tuning Backup Compression Part 2. Provides guidance related to tuning options for backup performance, and discusses how backup compression interacts with other SQL Server 2008 features.
  • Resolving PAGELATCH Contention on Highly Concurrent INSERT Workloads. Describes the process of diagnosing a problem and then using table partitioning to work around it.
  • Managing SQL Server Workloads using Resource Governor. Explains how to use Resource Governor to manage SQL Server workload and system resource consumption.
  • Service Broker: Performance and Scalability Techniques. Describes a large scale customer scenario and the techniques employed in scaling Service Broker to process tens of thousands of messages per second on one server.
  • SQL Server 2005 Waits and Queues. Explains how to use waits and queues to identify the best opportunities for improving performance.
  • Plan Caching in SQL Server 2008. Explains how the SQL Server plan cache operates and how to avoid excessive recompilation.
  • Online Indexing Operations in SQL Server 2005. provides a detailed discussion of the index process and provides guidelines and best practices for implementing this feature in a production environment.
  • & Boost Performance with New Features in SQL Server 2005. Describes how to develop performance enhancing strategies, and introduces Service Broker.
  • Data Access Tracing in SQL Server 2005. Discusses how you can use data tracing to track down bugs and bottlenecks in your applications.
  • Minimize Blocking in SQL Server. Explains why lock escalation occurs and how to avoid unnecessary blocking. Explains how to optimize your queries and how to monitor the impact of locking on performance.
  • Using the Resource Governor. Shows ways in which you can use the Resource Governor to analyze and streamline the way resources are utilized within your SQL Server instances.
  • Troubleshooting Resource Governor. Provides troubleshooting guidance for conditions that may occur when using Resource Governor.
  • & Resource Governor in SQL Server 2008. Provides concepts and information fundamental to the understanding and usage of Resource Governor.
  • & Working with Dynamic Management Objects. Explains how Dynamic Management Objects can help you manage and monitor workload details essential for performance tuning.

Data warehouses are characterized by queries that scan larger numbers of rows, large ranges of data and may return relatively large results for the purposes of analysis and reporting. Most relational technologies are not tuned to deal with the large-query and data-loading workloads of data warehousing. The following links provide some general information and best practices about how to make the data base engine work more efficiently.

• Data Warehouse Query Performance. Provides a deep-dive look into some of the most important performance-related data warehousing features in SQL Server 2008.
• SQL Server Columnstore Index FAQ. Posts questions and answers about how the columnstore index speeds up data warehouse query processing in many cases by a factor of 10 to 100.
• SQL Server Columnstore Performance Tuning. Describes query performance tuning for SQL Server columnstores and how they are designed to improve query performance for data warehouses and data marts.
• Top 10 SQL Server 2005 Performance Issues for Data Warehouse and Reporting Applications. Outlines the top performance bottlenecks or gotchas to avoid for data warehouse or reporting application.
• Troubleshooting Bitmap Filter Selectivity Problems. Explains how to diagnose and resolving nonselective bitmap filters.
• Troubleshooting Poor Query Performance: Cardinality Estimation. Describes how to improve cardinality for better estimated costs and faster execution plans.
• Troubleshooting Poor Query Performance: Constant Folding and Expression Evaluation During Cardinality Estimation. Provides best practices for generating optimal query plans.
• Troubleshooting Queries. Provides troubleshooting information for query problems in the SQL Server Database Engine.
• Troubleshooting Query Notifications. Provides troubleshooting information for common problems with query notifications.
• & Optimizing SQL Server Query Performance. Analyzes execution plans and explains how to optimize queries and identify queries to tune.
• & New Tools to Diagnose Index Health. Describes index fragmentation, and how to determine which indexes are useful for your SQL queries.
• Merge Replication Performance Improvements in SQL Server 2005. Describes performance optimizations and new features that provide improved performance for merge replication in SQL Server 2005.
• Using Star Join and Few-Outer-Row Optimizations to Improve Data Warehousing Queries. Describes how to use Star Join and Few-Outer-Row optimizations to improve query execution speed and therefore reducing query response time.
• Optimizing Data Warehouse Query Performance through Bitmap Filtering. Explains how optimized bitmap filtering can significantly improve the performance of data warehouse queries that use star schemas by removing non-qualifying rows from the fact table early in the query plan.

• & Optimizing SQL Server CPU Performance. Explains how to troubleshoot database performance issues, reviews hardware causes, explains how to use Performance Monitor to track database bottlenecks, and describes how to evaluate query performance.
• Troubleshooting Insufficient Data Disk Space. Provides information about how to resolve disk space problems.
• Troubleshooting Insufficient Disk Space in tempdb. Provides procedures and recommendations about how to diagnose and troubleshoot insufficient disk space in the tempdb database.
• Performance Evaluation of Hosting TempDB on FusionIO. Compares the TempDB performance between direct-attached physical disks and the FusionIO MLC DIMMS.
• Monitoring Disk Usage. Explains how to monitor your disk usage, which is critical in troubleshooting performance problems.
• SQL Server Distributed Replay. Explains how to assess the impact of your future SQL Server upgrades and how to use it to help assess the impact of hardware and operating system upgrades, and SQL Server tuning.
• SQL Server 2008 Performance Tuning for Hardware Forum. A forum where you can ask questions and find answers to your SQL Server hardware-related performance tuning questions.

There are a number of blogs that contain a log of great information about troubleshooting SQL Server performance. Following are some blogs you might want to check out.

Note that the opinions and views expressed in non-Microsoft blogs are those of the author and do not necessarily state or reflect those of Microsoft.

Microsoft Corporation Blogs

• SQL Server Performance Team Blog. Best Practices, Tips, Benchmarks, Troubleshooting and Monitoring - SQL Server, ADO.NET, Analysis Services, and SSIS.
• SQL Query Optimizer Team BlogSQL Query Optimizer Team Blog

MVP Blogs

• Aaron Bertrand. Aaron is a senior consultant for SQL Sentry, Inc., makers of performance monitoring and event management software for SQL Server, Analysis Services, and Windows. He has been blogging at sqlblog.com since 2006, focusing on manageability, performance, and new features. He has been a Microsoft MVP since 1997, and speaks frequently at user group meetings and SQ: Saturday events.
• Ashish Kumar Mehta.
• Glenn Berry’s SQL Server Performance. Glenn is a database architect at Avalara in Denver, Colorado, and has been a SQL Server MVP since 2007.
• Greg Larson. Greg has been working with SQL Server since 1999, and has been working as a DBA on one platform or another since 1985. He currently holds a MCTS: SQL Server 2005 certification and is one test away from getting his MCITP: Database Administrator certificate. Greg is a PASS chapter leader for the Olympia Area SQL Server User Group.
• Kimberly Tripp. Kimberly is a SQL Server MVP, and a Microsoft Regional Director. Currently, Kimberly is focused on SQL Server 2008 content for conferences and workshops around the world. She has worked as a co-author/editor helping to put together the Index Internals chapter for SQL Server 2008 Internals and SQL Server MVP Deep Dives, which is a collection of best practices from MVPs around the world.
• Maciej PileckiMaciej Pilecki. Maciej is an Associate Mentor with Solid Quality Mentors, a global organization that specializes in training, mentoring, and consulting. He is a Microsoft Certified Trainer (MCT) and Microsoft MVP and frequently delivers courses and conference speeches on many aspects of SQL Server and application development.
• Paul Randal. Paul is the managing director of SQLskills.com, a Microsoft regional director and a SQL Server MVP. He worked on the SQL Server Storage Engine team at Microsoft from 1999 to 2007. He was responsible for the Core Storage Engine during SQL Server 2008 development and is an expert on disaster recovery, high availability and database maintenance, and is a regular presenter at conferences worldwide.
• Zach Nichter. Zach is a SQL Server professional with more than 10 years of experience. He has held a number of SQL Server support roles including DBA, team lead, manager, and consultant. Currently, Zach is employed by Levi Strauss & Co. as the DBA Architect, focusing on SQL Server performance, monitoring, and architecture as well as other strategic initiatives.
  In German
  
  • Frank Kalis. Den Teilnehmern der Microsoft SQL Server Foren ist Falk bestens bekannt durch seine fachkundigen und fundierten Antworten auf viele Fragen.

Community Blogs

• AmruthaVarshiniJ.
• THE SQL Server Blog Spot on the Web. Blogs about SQL Server, T-SQL, CLR, Service Broker, Integration Services, Reporting, Analysis Services, Business Intelligence, XML, SQL Scripts, best practices, database development, database administration, and programming.

MSDN Blogs

• Bart Duncan. Bart is a senior software design engineer and focuses on blogging about what he finds most interesting -- SQL Server performance.
• Buck Woody. Buck Woody is a Senior Technical Specialist for Microsoft, working with enterprise-level clients to develop computing platform architecture solutions within their organizations. With over twenty-five years professional and practical experience in computer technology, he is also a popular speaker at TechEd, PASS and many other conferences; the author of over 500 articles and five books on databases; and teaches a Database Design course at the University of Washington.
• Conor Cunningham. Conor blogs on SQL Server, data-driven applications, and pretty much whatever other random stuff he decides to post.
• Mike Wachal. Mike is a program manager in the SQL Server group. He has spent a lot of time working on desktop database systems including Access and SQL Express. He’s working on diagnostic systems now, primarily SQL Server Extended Events.
• Tony Voellm. Tony is the Principle Development Lead in the Windows Virtual Machine Team.

Do you have a SQL Server question that needs answering? Forums are an excellent place to ask questions and find answers to your SQL Server performance issues.

SQL Server 2008 Forums

• SQL Server 2008 Performance Tuning for DBAs
• SQL Server 2008 Performance Tuning for Hardware.
• SQL Server 2008 T-SQL Performance Tuning

SQL Server 2005 Forums

• SQL Server 2005 Performance Tuning for DBAs
• SQL Server 2005 T-SQL Performance Tuning

The SQL Server Resource Centers connect users to the help content, how-to information, downloads, installation information, community, social media, and other online resources that help you deploy, maintain, and support SQL Server.

• SQL Server 2012 Resource Center
• SQL Server 2008 R2 and SQL Server 2008 Resource Center
• SQL Server 2005 Resource Center

To learn useful, practical techniques to help you improve your SQL server performance skills, you can register for the following performance workshops, or download copies of the workshop videos.

• Level 400 SQL Server Performance Monitoring and Tuning Hands-on Workshops and Webcasts
• TechNet Webcast: SQL Server 2008 R2: Performance Troubleshooting (Level 300)
• TechNet Webcast: SQL Server Performance Counter Guidance (Level 300)

• SQL Server 2008 Query Performance Tuning Distilled – By Brent Ozar
• Performance Tuning with SQL Server Dynamic Management Views – By Matt Velic
• Professional SQL Server 2005 Performance Tuning