Spool operators in query plan…

I came across a question in the relationalserver.performance newsgroup where a customer was wondering about the spools seen in a recursive query execution plan. The query is shown below: USE Northwind;Go WITH EmpChart AS(SELECT EmployeeId, ReportsTo, 1 AS treelevelFROM EmployeesWHERE (Employees.ReportsTo = 2)UNION ALLSELECT e.EmployeeId, e.ReportsTo, treelevel +1FROM Employees eJOIN EmpChart ecON e.ReportsTo=ec.EmployeeID)SELECT * FROM…

1

What’s swimming in your bufferpool?

When doing a performance investigation a useful thing to do is look at what data is present in the buffer pool.  This can be used to analyze impact of running a query on the state of data pages in buffer pool. By collecting the pre and post picture of buffer pool, you can see the…

3

Adjust buffer size in SSIS data flow task

The data flow task in SSIS (SQL Server Integration Services) sends data in series of buffers. How much data does one buffer hold? This is bounded by DefaultBufferMaxRows and DefaultBufferMaxSize, two Data Flow properties. They have default values of 10,000 and 10,485,760 (10 MB), respectively. That means, one buffer will contain either 10,000 rows or…

2

Implement Parallel Execution in SSIS

SQL Server Integration Services (SSIS) allows parallel execution in two different ways. These are controlled by two properties as outlined below.   The first one is MaxConcurrentExecutables, a property of the package. It defines how many tasks (executables) can run simultaneously. It defaults to -1 which is translated to the number of processors plus 2….

10

Something about SSIS Performance Counters

SQL Server Integration Services provide a set of performance counters. Among them the following few are helpful when you tune or debug your package: Buffers in use Flat buffers in use Private buffers in use Buffers spooled Rows read Rows written   “Buffers in use”, “Flat buffers in use” and “Private buffers in use” are…

5

Set BLOBTempStoragePath and BufferTempStoragePath to Fast Drives

BLOBTempStoragePath and BufferTempStoragePath are two properties on Data Flow Task. They define where on the hard disk(s) to page buffer data to. BLOBs (text, next, image) are written to BLOBTempStoragePath. All other buffer data will be swapped to BufferTempStoragePath. See the figure below for where these two properties are exposed in designer.   Now what…

3

Set up OLE DB source to read from View efficiently

  Introduction OLE DB source adapter is one of the most commonly used components in SSIS data flow task. In this article, we will discuss a very important performance observation about this adapter. Use “SQL Command” to pull data from a view OLE DB source adapter can be set up to work in either “SQL…

7

Getting Optimal Performance with Integration Services Lookups

Introduction Most users see good performance from SSIS packages using out-of-the-box configurations and with little tuning. Sometimes, though, it is necessary to do tuning to get optimal performance. One of the most commonly used transformations is the Lookup transformation. There are several techniques for getting optimal performance from a Lookup transform in SSIS. A few…

8

What’s so temporary about tempdb?

Other than #temptables, @localvariables and many other objects being stored in tempdb what’s so temporary about it?  TempDB is always present on each SQL Server instance and can be a determining factor of your overall performance.  In many ways tempdb could be named scratchdb or pagefiledb.  The following papers will help you understand what goes…

1

SQL Server 2005 SP2 Re-release and post fixes

Bob Ward from PSS has a wonderful blog article that explains the details about the re-release of SQL Server 2005 SP2 and fixes posted later. This is a must read for anyone deploying SQL Server 2005 SP2 to understand the various hotfixes, GDRs and procedures. Please visit his link for more details. SQL Server 2005…

0