SQL SERVER – Reducing CXPACKET Wait Stats for High Transactional Database

While engaging in a performance tuning consultation for a client, a situation occurred where they were facing a lot of CXPACKET Waits Stats. The client asked me if I could help them reduce this huge number of wait stats. I usually receive this kind of request from other client as well, but the important thing to understand is whether this question has any merits or benefits, or not.

Before we continue the resolution, let us understand what CXPACKET Wait Stats are.

The official definition suggests that CXPACKET Wait Stats occurs when trying to synchronize the query processor exchange iterator. You may consider lowering the degree of parallelism if a conflict concerning this wait type develops into a problem. (from BOL)

In simpler words, when a parallel operation is created for SQL Query, there are multiple threads for a single query. Each query deals with a different set of the data (or rows). Due to some reasons, one or more of the threads lag behind, creating the CXPACKET Wait Stat. Threads which came first have to wait for the slower thread to finish. The Wait by a specific completed thread is called CXPACKET Wait Stat. Note that CXPACKET Wait is done by completed thread and not the one which are unfinished.

“Note that not all the CXPACKET wait types are bad. You might experience a case when it totally makes sense. There might also be cases when this is also unavoidable. If you remove this particular wait type for any query, then that query may run slower because the parallel operations are disabled for the query.”

Now let us see what the best practices to reduce the CXPACKET Wait Stats are.

The suggestions, with which you will find that if you search online through the browser, would play a major role as and might be asked about their jobs In addition, might tell you that you should set ‘maximum degree of parallelism’ to 1. I do agree with these suggestions, too; however, I think this is not the final resolutions. As soon as you set your entire query to run on single CPU, you will get a very bad performance from the queries which are actually performing okay when using parallelism.

The best suggestion to this is that you set ‘the maximum degree of parallelism’ to a lower number or 1 (be very careful with this – it can create more problems) but tune the queries which can be benefited from multiple CPU’s.

You can use query hint OPTION (MAXDOP 0) to run the server to use parallelism.

Here is the two-quick script which helps to resolve these issues:

Change MAXDOP at Server Level

EXEC sys.sp_configure N'max degree of parallelism', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

Run Query with all the CPU (using parallelism)

USE AdventureWorks
GO
SELECT *
FROM Sales.SalesOrderDetail
ORDER BY ProductID
OPTION (MAXDOP 0)
GO

Below is the blog post which will help you to find all the parallel query in your server.

SQL SERVER – Find Queries using Parallelism from Cached Plan

Please note running Queries in single CPU may worsen your performance and it is not recommended at all. Infect this can be very bad advise.

I strongly suggest that you identify the queries which are offending and tune them instead of following any other suggestions.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – Microsoft WhitePaper on PowerPivot Data Refresh

I was recently working at customer location on PowerPivot project. It was quite complected as this is relatively new technology and we all are exploring what this technology can do and what it can bring to us on table in real life experience. During this implementation the project design document needed specification regarding Data Refresh rates. It was a bit complected as there were various components and modules to the project and selecting the refresh rates means understand all of the requirement as well understanding our implementation in and out.

I referred following white paper from Microsoft before I move further in the project and found it very helpful and it helped me understand the implication of various data refresh methods and its capacities.

Everything You Always Wanted to Know About PowerPivot Data Refresh but Were Afraid to Ask

Following is the summary of the whitepaper.

The purpose of PowerPivot for SharePoint is to allow users to share their PowerPivot workbooks in a secure and scalable environment. To ensure that you are sharing and collaborating on the most recent data available to you, PowerPivot for SharePoint provides a data refresh feature that lets you automatically update PowerPivot data in an Excel workbook. This white paper describes in detail the data refresh feature in PowerPivot for SharePoint. It starts by explaining the steps for setting up a data refresh schedule in SharePoint, and then it continues with an in-depth view on how data refresh works on a SharePoint farm. Both administrators and the business analysts who author and manage PowerPivot workbook data can benefit from learning more about setting up and using data refresh in a SharePoint environment.

I suggest if you are facing situation like me download this whitepaper and read it before moving ahead in project.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

 

SQLAuthority News – SQL Server Quickstart Downloads from Microsoft

Here are few recent published by Microsoft.

Application Platform Optimization SQL Server Migration QuickStart

The SQL Server Migration QuickStart includes a comprehensive set of technical content including presentations, whitepapers and demos that are designed to help you get details about how to approach your customers who want to improve the return on investment from their data platforms by migrating to SQL Server from their existing Oracle or Sybase platforms.

Application Platform Optimization SQL Server Consolidation QuickStart

The SQL Server Consolidation QuickStart includes a comprehensive set of technical content including presentations, whitepapers and demos that are designed to present to customers who want to improve the manageability and security of their SQL Server environment while reducing the associated hardware and software cost

Getting Started with SQL Azure

SQL Azure Database is a cloud based relational database service from Microsoft. SQL Azure provides relational database functionality as a utility service. Cloud-based database solutions such as SQL Azure can provide many benefits, including rapid provisioning, cost-effective scalability, high availability, and reduced management overhead.

Note: Description courtesy Microsoft.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Application Platform Optimization SQL Server Migration QuickStart

SQLAuthority News – Whitepaper – SQL Azure vs. SQL Server

SQL Server and SQL Azure are two Microsoft Products which goes almost together. There are plenty of misconceptions about SQL Azure. I have seen enough developers not planning for SQL Azure because they are not sure what exactly they are getting into. Some are confused thinking Azure is not powerful enough. I disagree and strongly urge all of you to read following white paper written and published by Microsoft.

SQL Azure vs. SQL Server by Dinakar Nethi, Niraj Nagrani

SQL Azure Database is a cloud-based relational database service from Microsoft. SQL Azure provides relational database functionality as a utility service. Cloud-based database solutions such as SQL Azure can provide many benefits, including rapid provisioning, cost-effective scalability, high availability, and reduced management overhead. This paper compares SQL Azure Database with SQL Server in terms of logical administration vs. physical administration, provisioning, Transact-SQL support, data storage, SSIS, along with other features and capabilities.

The content of this white paper is as following:

  • Similarities and Differences
    • Logical Administration vs. Physical Administration
    • Provisioning
    • Transact-SQL Support
    • Features and Types
  • Key Benefits of the Service
    • Self-Managing
    • High Availability
    • Scalability
    • Familiar Development Model
    • Relational Data Model

The above summary text is taken from white paper itself.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – Storage and SQL Server Capacity Planning and configuration – SharePoint Server 2010

Just a day ago, I was asked how do you plan SQL Server Storage Capacity. Here is the excellent article published by Microsoft regarding SQL Server capacity planning for SharePoint 2010. This article touches all the vital areas of this subject. Here are the bullet points for the same.

  • Gather storage and SQL Server space and I/O requirements
  • Choose SQL Server version and edition
  • Design storage architecture based on capacity and IO requirements
  • Determine memory requirements
  • Understand network topology requirements
  • Configure SQL Server
  • Validate storage performance and reliability

Read the original article published by Microsoft here: Storage and SQL Server Capacity Planning and configuration – SharePoint Server 2010.

The question to all the SharePoint developers and administrator that if they use the whitepapers and articles to decide the capacity or they just start with application and as they progress they plan the storage? Please let me know your opinion.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – List of Master Data Services White Paper

Since my TechEd India 2010 presentation I am very excited with SQL Server 2010 MDS. I just come across very interesting white paper on Microsoft site related to this subject. Here is the list of the same and location where you can download them. They are all written by Top Experts at Microsoft.

Master Data Management from a Business Perspective

– Download a PDF version or an XPS version

Master Data Management from a Technical Perspective

– Download a PDF version or an XPS version

Bringing Master Data Management to the Stakeholders

– Download a PDF version or an XPS version

Implementing a Phased Approach to Master Data Management

– Download a PDF version or an XPS version

SharePoint Workflow Integration with Master Data Services

– Read it here.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Interesting Whitepaper – We Loaded 1TB in 30 Minutes with SSIS, and So Can You

We Loaded 1TB in 30 Minutes with SSIS, and So Can You
SQL Server Technical Article
Writers: Len Wyatt, Tim Shea, David Powell
Published: March 2009

In February 2008, Microsoft announced a record-breaking data load using Microsoft SQL Server Integration Services (SSIS): 1 TB of data in less than 30 minutes. That data load, using SQL Server Integration Services, was 30% faster than the previous best time using a commercial ETL tool. This paper outlines what it took: the software, hardware, and configuration used. We will describe what we did to achieve that result, and offer suggestions for how to relate these techniques to typical scenarios. Even for customers who don’t have needs quite like this benchmark, such efforts can teach a lot about getting optimal performance.

Read the white paper here.

Abstract courtesy Microsoft

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – SQL Server Technical Article – The Data Loading Performance Guide

Note: SQL Server Technical Article – The Data Loading Performance Guide by Microsoft

The white paper describes load strategies for achieving high-speed data modifications of a Microsoft SQL Server database.

“Bulk Load Methods” and “Other Minimally Logged and Metadata Operations” provide an overview of two key and interrelated concepts for high-speed data loading: bulk loading and metadata operations.

After this background knowledge, white paper describe how these methods can be used to solve customer scenarios. Script examples illustrating common design pattern are found in “Solving Typical Scenarios with Bulk Loading” Special consideration must be taken when you need to load and read data concurrently in the same table. The section “Bulk Load, NOLOCK Queries, and Read Committed Snapshot Isolation” describes methods you can use to achieve concurrent loading and reading.

This white paper concludes with troubleshooting hints in “Optimizing Bulk Load”.

SQL Server Technical Article – The Data Loading Performance Guide

Abstract courtesy : Microsoft

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Download White Paper – Troubleshooting Performance Problems in SQL Server 2008

Troubleshooting Performance Problems in SQL Server 2008
SQL Server Technical Article
Writers: Sunil Agarwal, Boris Baryshnikov, Keith Elmore, Juergen Thomas, Kun Cheng, Burzin Patel
Technical Reviewers: Jerome Halmans, Fabricio Voznika, George Reynya
Published: March 2009

It’s not uncommon to experience the occasional slowdown of a database running the Microsoft SQL Server database software. The reasons can range from a poorly designed database to a system that is improperly configured for the workload. As an administrator, you want to proactively prevent or minimize problems; if they occur, you want to diagnose the cause and take corrective actions to fix the problem whenever possible. This white paper provides step-by-step guidelines for diagnosing and troubleshooting common performance problems by using publicly available tools such as:

  • SQL Server Profiler
  • System Monitor (in the Windows Server 2003 operating system) or Performance Monitor (in the Windows Vista operating system and Windows Server 2008), also known as Perfmon
  • Dynamic management views (sometimes referred to as DMVs)
  • SQL Server Extended Events (Extended Events) and the data collector, which are new in SQL Server 2008.

Download “Troubleshooting Performance Problems in SQL Server 2008” from the Microsoft Download Center.

Abstract courtesy : Microsoft

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Remote BLOB Store Provider Library Implementation Specification

Remote BLOB Store Provider Library Implementation Specification
logo-sql08.gif
SQL Server Technical Article
Writers: Kevin Farlee, Pradeep Madhavarapu
Technical Reviewer: Pradeep Madhavarapu, Michael Warmington
Published: August 2008

Remote BLOB Store (RBS) is designed to move the storage of large binary data (BLOBs) from database servers to commodity storage solutions.

With RBS, BLOB data is stored in storage solutions such as Content Addressable Stores (CAS), commodity hardware with data integrity and fault-tolerance systems, or mega service storage solutions like MSN Blue. A reference to the BLOB is stored in the database. An application stores and accesses BLOB data by calling into the RBS client library. RBS manages the life cycle of the BLOB, such as doing garbage collection as and when needed.

RBS is an add-on that can be applied to Microsoft SQL Server 2008 and later. It uses auxiliary tables, stored procedures, and an executable to provide its services. A reference to the BLOB (provided by the BLOB Store) is stored in RBS auxiliary tables and an RBS BLOB ID is generated. Applications store this RBS BLOB ID in a column in application tables. These columns in application tables are called RBS Columns in this specification. The RBS Column is not a new data type; it is just a simple binary(20).

Above words are quoted from original Microsoft White Paper Please read complete white paper for excellent notes on what are exact specification for Remote BLOB Store Provider Library.

Abstract courtesy : Microsoft

Reference: Pinal Dave (http://blog.SQLAuthority.com)