http://www.sqlservercentral.com/Content tagged SQL Server 2005, Performance Tuning posted on SQLServerCentral.comen-us360sjones@sqlservercentral.com (Steve Jones)The SQL Server 2005 Performance Dashboard is a new add-on to SQL Server 2005 that became available shortly after the release of Service Pack 2 for SQL Server 2005.http://www.sqlservercentral.com/redirect/articles/3018/2008/05/20http://www.sqlservercentral.com/redirect/articles/3018/SQL 2008 has some key improvements over SQL 2005. This article deals with backup compression and faster backups in SQL 2008http://www.sqlservercentral.com/articles/Compression/62746/2008/05/05http://www.sqlservercentral.com/articles/Compression/62746/This article illustrates how to query dynamic management views to find the TOP queries based on average CPU and average I/O.http://www.sqlservercentral.com/redirect/articles/62765/2008/04/09http://www.sqlservercentral.com/redirect/articles/62765/The I/O system is important to the performance of SQL Server. When configuring a new server for SQL Server or when adding or modifying the disk configuration of an existing system, it is good practice to determine the capacity of the I/O subsystem prior to deploying SQL Server. This white paper discusses validating and determining the capacity of an I/O subsystem. A number of tools are available for performing this type of testing. This white paper focuses on the SQLIO.exe tool, but also compares all available tools. It also covers basic I/O configuration best practices for SQL Server 2005.http://www.sqlservercentral.com/redirect/articles/61747/2008/01/04http://www.sqlservercentral.com/redirect/articles/61747/How should you approach indexing your database? Do you have a good strategy for deciding which indicies to create and on which columns? Aaron Ingold brings us a great article discussing these very topics that is likely to become a must read for many DBAs. http://www.sqlservercentral.com/articles/Performance+Tuning/2770/2007/12/24http://www.sqlservercentral.com/articles/Performance+Tuning/2770/Using hints in a query is something that most DBAs don't ever seem to bother with, but when they do, NOLOCK seems to be their hint of choice. Wayne Fillis brings us a detailed examination of how this particular hint actually affects the performance of your system. http://www.sqlservercentral.com/articles/Performance+Tuning/2764/2007/12/17http://www.sqlservercentral.com/articles/Performance+Tuning/2764/SQL Server performance can be tracked and monitored by using performance counters. For SQL Server 2005 performance counters can be displayed by using the “sys.os_exec_performance_counters” Dynamic Management View (DMV).http://www.sqlservercentral.com/redirect/articles/61637/2007/12/05http://www.sqlservercentral.com/redirect/articles/61637/Indexes directly affect the performance of database applications. This article uses analogies to describe how indexes work. The estimated execution plan feature of the Query Window is utilized to compare the performance of two queries in a batch.http://www.sqlservercentral.com/redirect/articles/61605/2007/12/04http://www.sqlservercentral.com/redirect/articles/61605/It is not uncommon to experience the occasional slow down of a SQL Server database. A poorly designed database or a system that is improperly configured for the workload are but several of many possible causes of this type of performance problem.http://www.sqlservercentral.com/redirect/articles/61478/2007/11/22http://www.sqlservercentral.com/redirect/articles/61478/As part of your performance audit, you will need to examine each database located on each of your SQL Server instances and examine some basic database settings.http://www.sqlservercentral.com/redirect/articles/61422/2007/11/16http://www.sqlservercentral.com/redirect/articles/61422/In a previous article I showed how to look at what query plans are in SQL Server's memory. In this article I'll look at how they got there and how and when they get reused. This is commonly called compiling a query plan. More specifically I'll look at how and when SQL Server can parameterize dynamic SQL http://www.sqlservercentral.com/redirect/articles/3155/2007/08/14http://www.sqlservercentral.com/redirect/articles/3155/SQL Server memory is primarily used to store data (buffer) and query plans (procedure cache). In this article I'll show how much memory is allocated to the procedure cache (RAM). I'll explain how to determine what plans are in the cache and how often they're used.http://www.sqlservercentral.com/redirect/articles/3126/2007/08/10http://www.sqlservercentral.com/redirect/articles/3126/If you are using SSMS and SQL Server 2005 SP2, You've probably tried out the database reports that are available in the Performance Dashboard, and decided how useful they are, but did you know that you can create your own dashboard reports? http://www.sqlservercentral.com/redirect/articles/3133/2007/08/07http://www.sqlservercentral.com/redirect/articles/3133/SQL Server memory is primarily used to store data (buffer) and query plans (cache). In this article I'll show how much memory is allocated to the data buffer (or RAM). I'll explain how to determine what tables and indexes are in the buffer memory of your server.http://www.sqlservercentral.com/redirect/articles/3125/2007/08/03http://www.sqlservercentral.com/redirect/articles/3125/The SQL Server 2005 Performance Dashboard is a new add-on to SQL Server 2005 that became available shortly after the release of Service Pack 2 for SQL Server 2005.http://www.sqlservercentral.com/redirect/articles/3018/2008/05/20http://www.sqlservercentral.com/redirect/articles/3018/Full scan statistics update can be slow and expensive on a large system, and a single update statistics job for a whole database can easily exceed an overnight maintenance window. This article looks at jobs that could update statistics on the system in parallel.http://www.sqlservercentral.com/redirect/articles/3017/2007/05/31http://www.sqlservercentral.com/redirect/articles/3017/This article introduces some techniques that will allow you to pinpoint exactly where the performance issues are in your system, so you'll know exactly where to spend your time (and money) in solving them. http://www.sqlservercentral.com/redirect/articles/2941/2007/04/03http://www.sqlservercentral.com/redirect/articles/2941/Using hints in a query is something that most DBAs don't ever seem to bother with, but when they do, NOLOCK seems to be their hint of choice. Wayne Fillis brings us a detailed examination of how this particular hint actually affects the performance of your system. http://www.sqlservercentral.com/articles/Performance+Tuning/2764/2007/12/17http://www.sqlservercentral.com/articles/Performance+Tuning/2764/In SQL Server 2005, there is a new feature called Plan Guides that can help out in some cases where you discover poorly performing queries that you don't have direct control over. Essentially, a Plan Guide allows you to add or modify query hints to queries on the fly, just before they are executed.http://www.sqlservercentral.com/redirect/articles/2885/2007/02/26http://www.sqlservercentral.com/redirect/articles/2885/A series of tips on optimizing XML in your SQL Server databases. http://www.sqlservercentral.com/redirect/articles/2869/2007/02/21http://www.sqlservercentral.com/redirect/articles/2869/Using hints in a query is something that most DBAs don't ever seem to bother with, but when they do, NOLOCK seems to be their hint of choice. Wayne Fillis brings us a detailed examination of how this particular hint actually affects the performance of your system. http://www.sqlservercentral.com/articles/Performance+Tuning/2764/2007/12/17http://www.sqlservercentral.com/articles/Performance+Tuning/2764/How should you approach indexing your database? Do you have a good strategy for deciding which indicies to create and on which columns? Aaron Ingold brings us a great article discussing these very topics that is likely to become a must read for many DBAs. http://www.sqlservercentral.com/articles/Performance+Tuning/2770/2007/12/24http://www.sqlservercentral.com/articles/Performance+Tuning/2770/The benefits of SQL Server 2005's new snapshot isolation feature are well known, but SQL Server developers also need to understand the downstream ramifications of snapshot isolation.http://www.sqlservercentral.com/redirect/articles/2716/2006/12/11http://www.sqlservercentral.com/redirect/articles/2716/This document describes the problem and the workaround in SQL Server 2000, and the solution and recommendation in SQL Server 2005.http://www.sqlservercentral.com/redirect/articles/2705/2006/12/08http://www.sqlservercentral.com/redirect/articles/2705/In this presentation, Brian introduces SQL Server 2005 Profiler. He shows you to integrate System Monitor logs (also called perfmon) to find what queries were running against your machine during key performance failures. He also shows you how to output the query plan into an XML file. http://www.sqlservercentral.com/redirect/articles/2746/2006/12/04http://www.sqlservercentral.com/redirect/articles/2746/Some of the key factors to consider while you devise an index maintenance plan are performance, concurrency, and required resources. SQL Server 2005 provides you with several options for index create and rebuild operations that you can use to effectively meet the requirements of performance, concurrency, and resources.http://www.sqlservercentral.com/redirect/articles/2704/2006/12/01http://www.sqlservercentral.com/redirect/articles/2704/If you're having a problem with particular queries, there are a few things you can do with the information that the server collects about the distribution of data in columns and indexes.http://www.sqlservercentral.com/redirect/articles/2713/2006/11/30http://www.sqlservercentral.com/redirect/articles/2713/The methodology helps identify the areas of slow performance by looking at the problem from two directions called Waits and Queues. An analysis of Waits indicates where SQL Server is spending lots of time waiting. In addition, the biggest waits point out the most important or relevant Queues (that is, Performance Monitor counters and other data) for this workload.http://www.sqlservercentral.com/redirect/articles/2703/2006/11/24http://www.sqlservercentral.com/redirect/articles/2703/Keeping track of the performance of your SQL Servers requires metrics. There are many methods for doing this, but some type of automated process is essential these days with DBAs managing many servers. New author Allen White brings us a technique for doing this using SMO, the replacement for DMO in SQL Server 2005. http://www.sqlservercentral.com/articles/Performance+Tuning/2692/2006/11/22http://www.sqlservercentral.com/articles/Performance+Tuning/2692/Gathering performance data on your servers can be a cumbersome task without paying for an expensive tool. New author Jambu Krishnamurthy brings us a short tutorial on how you can gather this data from Perfmon and import it into SQL Server for reporting. http://www.sqlservercentral.com/articles/Performance+Tuning/2677/2006/10/30http://www.sqlservercentral.com/articles/Performance+Tuning/2677/