SQL Server 2005 works great right out of the box. For most SQL Server users, performance is never an issue.... more

SQL Server 2005 works great right out of the box. For most SQL Server users, performance is never an issue. They install SQL Server, load up their data, and the system works just great. Over the past few years, however, there has been an explosion in database size and in the hardware performance that is available to small and medium-sized businesses. This has allowed many more users to build larger and larger systems. Many of today’s systems are now larger than yesterday’s enterprise class systems that required mainframe class hardware. This explosion in size and performance is pushing more users against the limits of the out-of-the-box performance of SQL Server. This means that more and more SQL Server users are having to start learning about a side of SQL Server that they never needed to know about—mdperformance tuning.

The good news is that as with the out-of-the-box experience, the SQL Server team has spent a lot of time working on making SQL Server an easy database system to performance tune. Unlike many of SQL Server’s competitors, where you need many highly trained and very expensive consultants to diagnose and finetune performance problems, SQL Server has the tools and information available so that just about anyone can jump in and start tuning their databases.

... less

Understanding how your server is performing can be invaluable when troubleshooting problems and is useful in effective... more

Understanding how your server is performing can be invaluable when troubleshooting problems and is useful in effective management of your systems such as, for example, capacity planning. This chapter covers when to use System Monitor and how to get the most from monitoring to determine how your server is performing. Deciding what data to gather is the first step; interpreting the data is the next step in resolving an issue before putting a fix in place. At the end of this chapter, you should expect to better understand how to:

• Analyze a performance problem

• Proactively monitor server resource usage

• Apply best practices when using system monitor

Familiarizing yourself with the tools and your system’s regular workload means that you’re able to differentiate between the usual creaks and groans of your server and an actual performance problem. Performance tuning is a science. Getting to the root cause is a journey that can be long and arduous, but if you’re comfortable with the tools and familiar with your environment, it should be an enjoyable and interesting challenge.

... less

When you’ve established SQL Server as the likely cause of your performance problem, the next step is to drill down... more

When you’ve established SQL Server as the likely cause of your performance problem, the next step is to drill down to identify the specific SQL Server resource bottleneck causing the issue. When you’ve located the cause, the next step (and the purpose of this whole activity) is to resolve the problem. System Monitor is able to help with preliminary steps in identifying and resolving SQL Server bottlenecks. Additionally there are a number of tools to allow excellent visibility of SQL Server internals.

Understanding how and when to use System Monitor are both important as this determines the quality of the data collected. You may have had poor experiences with System Monitor in the past. Perhaps this was because you were collecting too much, or not the right kind of data—meaning that you don’t see the value this tool can provide. The challenge is multi-layered: collecting the right data, at the right time and knowing what to do with it. At the end of this chapter, you should be familiar with:

• Identifying SQL Server resource issues with System Monitor

• Understanding configuration versus schema problems

• Knowing best practices for monitoring SQL Server with system monitor

One of the numerous significant improvements in SQL Server 2005 is improved supportability; this means better out-of-the box tools for administration and troubleshooting. SQL Server Profiler and the Database Tuning Advisor (DTA) have both been improved to enable easier monitoring and shorter time to identify and resolve issues. Positioning these tools, and familiarizing yourself with each of them will enable you to use the right tool the first time when working on a production performance problem. You should feel encouraged and interested in identifying a performance problem—these are typically challenging and may require computer architecture and software implementation experience and as a result are usually very satisfying once resolved. Almost every performance problem will offer you an opportunity to learn more about how Windows, SQL Server, and your specific applications communicate and respond to each other.

... less

Now that you have a good idea of what’s going on at the server level using System Monitor, you can start to look... more

Now that you have a good idea of what’s going on at the server level using System Monitor, you can start to look at what’s going on within SQL Server. Think about what happens when you issue a query to update a row, for example:

1. The optimizer uses statistics to help decide on the best way to execute the query.

2. The query is executed.

3. The row will be read from disk into memory.

4. The update will be written to the transaction log.

5. The row will be changed in memory.

6. Confirmation will be sent back to the client.

Imagine if you could measure the amount of time that was spent waiting for things within each of these stages. It probably won’t surprise you to read that this chapter doesn’t end here and you can. Not only are monitoring waits possible, but they can actually form a very effective part of a performance troubleshooting strategy.

Wait time is effectively dead time, and if you can reduce the amount of time you spend waiting, you’ll be able to achieve better overall performance. This chapter covers how SQL Server waits work, what types there are, and how to view and track them. It also covers common scenarios and their associated wait types. Because SQL Server waits represent just a single view of performance, you will also see when to correlate the results with the system monitor counters discussed in the previous chapter.

The chapter concludes with a closer look at a very common waiting scenario: locking and blocking.

... less

The previous chapters provide the knowledge, tools, and resources for finding Windows and SQL server bottlenecks.... more

The previous chapters provide the knowledge, tools, and resources for finding Windows and SQL server bottlenecks. Tracing database activities and collecting information are critical steps in troubleshooting, tuning, and optimizing a database. SQL Profiler is a valuable investigative tool for measuring query performance and troubleshooting database applications.

In this chapter, you will see how and when to use SQL Profiler to collect and analyze query performance data. SQL Trace is a server-side component for tracing. SQL Profiler is a graphical user interface for SQL Trace. Profiler is used for monitoring an instance of the Microsoft Database Engine or Analysis Services. This chapter focuses mainly on using Profiler to solve performance issues. SQL Trace provides a set of system-stored procedures that can be used from within your own applications to create traces manually. This allows you to write custom applications specific to the needs of your enterprise.

Performance issues can be very specific or vague. Capturing the right amount of reliable event data at the right time with minimum costs presents a challenge for most database administrators. There are many SQL Profiler features and details, which need to be considered before starting a trace. You will see some important options and concerns. You will use appropriate scenarios to examine the processes involved in setting up a Profiler trace and a server-side trace. Toward the end of this chapter, you will see how to correlate SQL Profiler trace data and Windows performance counter data.

Upon finishing the chapter, with some practice, a database administrator should be able to set up an optimal Profiler trace based on a given scenario.

... less

The previous chapters have focused on how to monitor and troubleshoot a SQL Server system. From this chapter forward you’ll... more

The previous chapters have focused on how to monitor and troubleshoot a SQL Server system. From this chapter forward you’ll look at what you can do to resolve the bottlenecks and tune any performance problems. Your SQL Server system may consist of multiple servers hosting multiple applications or just a single server running everything. Either way, there are lots of moving parts, each of which must work together efficiently to provide the highest level of performance. If you think of each resource as a link in a chain, the weakest link really can weaken or even break the chain.

This chapter covers selecting and configuring the environment that SQL Server runs in; namely Windows and the server hardware itself. You will also learn how you should proactively design your environment using best practices to avoid performance problems later on.

First you’ll look at which resources can become a bottleneck and what can be the cause. It is important to know where your system’s weaknesses are likely to occur so you can proactively monitor the resources using what you’ve learned in Chapters 2 and 3. Read the first section of this chapter to understand the scope of what you’re going to tune.

You will then look at what you need to consider to make the correct server configuration choices and how your choice can impact SQL Server performance. Taking into account the requirements of all the applications running on the infrastructure is absolutely necessary to avoid service outages later on. You can read this section to guide your decisions when designing a new system or to help you proactively review an existing environment to ensure you’re getting the most from your setup.

... less

Unlike other major RDBMS products on the market such as Oracle 9i and 10g, which have hundreds of parameters to tweak,... more

Unlike other major RDBMS products on the market such as Oracle 9i and 10g, which have hundreds of parameters to tweak, SQL Server, for the most part, is self-configuring. That means that the default setting will work best for most scenarios.

Having said that, there are some configuration settings you can tweak, after careful analysis of the application, performance counters, SQL traces/profiling, and DMVs data, that warrant such action. If you need help with performance monitoring, SQL Server trace and profiling, and DMVs, please refer to the relevant chapters in this book for detailed discussion on those subjects.

In this chapter, we will discuss ways on how to tweak some advanced SQL Server settings to maximize performance. We will start from where and how to find existing SQL Server configuration settings. Next we will examine some important server settings, what they mean, and implications of changing them to different values.

As mentioned earlier, SQL Server is self-tuning for the most part. Only change server configuration settings when you have solid evidence that it really helps performance.

... less

A key component in creating a high-performance database or improving one that is performing inadequately lies in the schema.... more

A key component in creating a high-performance database or improving one that is performing inadequately lies in the schema. The schema refers to both the general and specific design of a database. General aspects include items such as normalization and database style (OLTP, OLAP). Specific aspects include items such as data types, foreign key relationships, table definitions, and so on. This chapter serves as a guide, describing the techniques and methodologies addressing both areas.

... less

Tuning T-SQL statements for performance is a primary task for most DBAs. The task is essentially a contest between a human... more

Tuning T-SQL statements for performance is a primary task for most DBAs. The task is essentially a contest between a human being and SQL Server’s query optimizer. An analogy can be seen in the world of chess. Numerous chess-playing computer programs have been developed over the years. Early chess programs were weak. However, the programs got better and began to surpass most people’s ability to play. Yet there are human players who can still beat the best chess programs in the world.

The world of T-SQL tuning is no different. In the early days of database engines, the query optimizers were weak. In fact, an early tuning technique was a simple re-arrangement of table order in a query. By simply reordering the tables, vast improvements could be made. However, over the years optimizers have gotten better and can now handle situations automatically, which previously had to be handled by a person. Don’t make the mistake of expending large amounts of effort to create a report that torpedoes thousands of write operations. The days of tweaking an existing query to get large gains in performance are rapidly diminishing so that this type of tuning really isn’t an option anymore.

In the first section, like the two different approaches that machines and humans take to playing chess, you’ll look at the algorithmic approach to playing chess—how to analyze the cost components of each step in a query execution process. You’ll examine in detail the pieces of the game as the optimizer sees them. In the second section, you’ll apply human intelligence that is agile and can apply abstractive reasoning. You’ll discover the things that you can do to avoid expensive operations, techniques for reducing specific cost components, and how to go about examining the performance data you’ll need to tune your T-SQL for performance.

... less

A system’s performance can be measured at any time with any workload. A workload contains rich data for a specific... more

A system’s performance can be measured at any time with any workload. A workload contains rich data for a specific scenario and time period. In this chapter, a workload refers to SQL trace output data that can be replayed by SQL 2005 Profiler. Analyzing trace events in a workload helps with troubleshooting, performance tuning, and measuring database usage data for capacity planning. Using SQL Profiler to capture, measure and replay a workload provides an inexpensive approach to establish and compare SQL Server performance baselines.

This chapter covers the necessary building blocks for using SQL Profiler to replay a workload. After learning how to capture, measure, and replay a workload, you will walk through three usage scenarios. The first scenario demonstrates a process using the Profiler’s replay feature to measure the overall performance gain for a workload. The second scenario compares the result of using workload replays on different SQL server configurations. The last scenario demonstrates using Profiler to send multiple workloads to a single server.

... less

Indexes are the solution to many performance problems but as in most parts of life, too much of a good thing can be bad... more

Indexes are the solution to many performance problems but as in most parts of life, too much of a good thing can be bad for you. A thorough understanding of indexes is essential for a DBA to make the right choices and SQL Server 2005 brings new features and options to help even the most hardened indexing expert. We’ve broken this chapter into four sections to make it easy to dip into:

• “Section l: Indexing Review” discusses indexing terminology and introduces some new features and tips. This should be regarded as a refresher rather than an introduction.

• “Section 2: Tuning” focuses on using Database Tuning Advisor and the built-in tuning tool. It also covers tuning indexes with no server impact using the built-in Dynamic Management Views.

• “Section 3: Maintenance” is predominantly the domain of the operational DBA and will show you how and why you need to maintain your indexes.

• “Section 4: Table and Index Partitioning” is an advanced topic but is made much more accessible with the built-in features of SQL Server 2005. This section covers how, why, and where you should implement it.

... less

The storage subsystem is the foundation for all the data that lives in SQL Server. The performance,... more

The storage subsystem is the foundation for all the data that lives in SQL Server. The performance, robustness, and reliability of the storage subsystem affect everything you build on it in SQL. For this reason it is essential that you have a solid understanding of what your storage subsystem can deliver and have confidence in its robustness and reliability.

The best way to build that solid understanding of the storage subsystem’s performance potential is to measure it using a performance test. There are several ways to do this, and this chapter discusses several options before diving into using one in more detail. The tool of choice for this chapter is SQLIO.

The best way to build confidence in the robustness and reliability of the storage subsystem is to run a stress test on it. There are multiple ways you can do this, and this chapter discusses several ways you might do this before digging into the details of using the latest tool from Microsoft: SQLIOSIM.

Obviously, everyone’s system is different. You will have your system configured according to your various needs. Depending on your system, you will have a certain amount of disk space. You will have assorted programs running on different schedules. As you follow along with this chapter, it is likely that you will have different measurements and readings than some of the ones I will provide. That is fine. Don’t get too caught up comparing those specific numbers. The important thing to take away from this chapter is an understanding of the core principles involved here.

... less

There are millions of deployments of SQL Server across the world ranging from the free SKU of SQL Server Express Edition... more

There are millions of deployments of SQL Server across the world ranging from the free SKU of SQL Server Express Edition to SQL Server Enterprise Edition. Each SQL Server environment is configured differently, has different business requirements, and serves different purposes. If you’re a good DBA in a perfect world, you’ll have a handle on many of these. You know your required up-time, integrated systems, dependencies, and so on. If something goes down, you are probably aware of it and can resolve it quickly. If you are an IT Professional who manages multiple server types or if you work in an enterprise that runs dozens of applications on top of hundreds of SQL Servers, it’s impossible to know all of your systems inside and out. You rely on monitoring tools to alert you when there’s a problem.

Now imagine that you work for Microsoft in SQL Server customer support. Each year you engage with hundreds of different businesses with thousands of SQL Servers. You have no prior knowledge of the environment or the business requirements. It’s your job to get up to speed immediately. You need to sift through symptoms, identify the underlying problems, and minimize the impact of the resolution to bring the business back online as soon as possible. Oh, and one more thing: You have to do this remotely and your only connection with the business is through the telephone.

The challenges you face as a DBA diagnosing problems in your environment are amplified when you need to bring in a third party to help you remotely. Thus, it is critical that you can quickly discover at a high level what could potentially be wrong with your environment and subsequently dive deeper into that area to identify the underlying problem.

From a support perspective, performance troubleshooting and tuning is an area that frequently starts with a lack of appropriate high-level information. A customer will call support and say something like, “my SQL Server is slow” or “my web app is slow and we think it is because of SQL.” This is not actionable high-level information. This requires the support specialist to spend a significant amount of time to understand the environment and collect sets of metrics that will eventually narrow it down to a few specific items.

... less

Not many architects start software projects to create applications to sit on shelves unused, but that can be the reality... more

Not many architects start software projects to create applications to sit on shelves unused, but that can be the reality if the product doesn’t meet user expectations. If performance is required in an application, you should approach the project with performance in mind from the start. Too often, the DBA resources are brought in at the end of the design, or worse, at the end of the development process to figure out how to speed things up. To prepare for database-level performance issues, you absolutely have to understand how the application works and how the database resources are going to be used. In new agile development processes, we see database designers and developers being added to development teams. This is a good thing. If you are a developer, add SQL Server performance skills to your toolset. It takes these two skill sets together to implement successful, high-performance projects.

Performance problems are usually caused by contention for common resources. Disk heads can only move to so many places at one time. Only so many pages and procedures can be cached in one moment. The more information you have about the system, the better performance tuning decisions you’ll be able to make. This chapter is designed for the DBA who is pulled into a development project before or perhaps after the physical model has already been formalized. Yet it’s still early enough for the DBA to have a hand in troubleshooting the design and capturing some important predicative metrics prior to going to production. You’ll be provided guidance on how to capture performance requirements and then use this information to evaluate existing design decisions. You’ll also be provided detailed TSQL queries and a benchmark data model that you can use to tie performance requirements to actual metrics. As you break down the main performance concerns you’ll enter this information into the benchmark data model to use in future communication. Creating high performance databases starts with understanding these performance requirements and applying common sense, but it is even more important to communicate these issues to developers, project managers, and users in a language that everyone can understand.

... less

The application life cycle goes through a series of very important steps before the actual deployment.... more

The application life cycle goes through a series of very important steps before the actual deployment. Just like any other application, the database platform is also expected to be reliable and agile. A lot of deployments fail due to a lack of sufficient planning and performance testing during the earlier stages. This chapter aims to provide some guidelines and best practices to follow for a successful deployment of your application databases.

Your production system can be compared to a supply chain process. The world of supply chains consists of incoming orders that are fulfilled by servers (machines, humans and so on). The servers source various components that are then assembled together to make the final product. These products are then stored in inventory to be shipped out to customers. The capacity of a supply chain environment is determined by the capacity of the slowest process within the supply chain. A process that restricts a supply chain from achieving maximum throughput is known as a bottleneck. A bottleneck may arise due to multiple factors such as machine breakdowns, lack of resources, or simply poor planning.

Similarly, a typical production scenario consists of users that request information from the application or database through the use of queries or transactions. These transactions are then processed by the various servers (computers) after having assimilated the information from various sources such as cache, memory, disk and so on. Once the information has been gathered, the replies are then sent back to the users.

As it is in a supply chain environment, a production scenario consists of bottlenecks which could cause slowdowns at various stages of the process. Bottlenecks could arise due to slower memory, slow processing power, I/O queues due to a slow storage subsystem and so on. The important point to remember is that no matter what the bottleneck may be, the actual capacity of the production system is constrained by the bottleneck areas. No matter how fast your processors are, if the disk I/O is causing the slowness, the processors will remain idle as they wait for replies from the storage subsystem.

Our goal is to identify these bottlenecks and hopefully mitigate them during the pre deployment stages. We talk about various aspects of the application life cycle starting from sizing your architecture to partitioning large tables without delving into the actual details as they have been covered elsewhere in this book. However, as luck would have it, even though a lot can be done during the pre-deployment stages, every DBA faces issues post production. The latter part of the chapter talks about certain limitations faced when tuning production systems. Enterprise applications such as Siebel and SAP often limit changes to the underlying database and as such impose

Best Practice

Find the bottlenecks before your production deployment.

... less