SQL Server 2005 SP2 Performance Reports

Just stumbled across a whole set of reports that can be deployed to SQL 2005 if you have SP2 installed.  Here is the link.  From what I have read, these are a pretty slick addition to the existing reports (you did know that there are existing reports, right?)

Here is the description from the download site:

The SQL Server 2005 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature introduced in the SP2 release of SQL Server Management Studio. The reports allow a database administrator to quickly identify whether there is a current bottleneck on their system, and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem. For example, if the system is experiencing waits for disk IO the dashboard allows the user to quickly see which sessions are performing the most IO, what query is running on each session and the query plan for each statement.

Common performance problems that the dashboard reports may help to resolve include:

- CPU bottlenecks (and what queries are consuming the most CPU)
- IO bottlenecks (and what queries are performing the most IO).
- Index recommendations generated by the query optimizer (missing indexes)
- Blocking
- Latch contention

The information captured in the reports is retrieved from SQL Server's dynamic management views. There is no additional tracing or data capture required, which means the information is always available and this is a very inexpensive means of monitoring your server.

Reporting Services is not required to be installed to use the Performance Dashboard Reports.

Microsoft BI Demo VPC 5.1

You better have a monster machine on which to run this: Microsoft has released a new Business Intelligence sample VPC.  The .vmc is set to use 1GB when you first install it, I would recommend bumping to 1.5GB - I tried 2GB and it was a still sluggish on my laptop.  Might want to throw it on a spare server if you have one.

It is a big download, but definitely worth it if you want to have a VPC available for "playing around" with any of the Microsoft BI tools without having to go through the trouble of deploying MOSS, etc. on a VPC.

Download Part 1 (3.5GB)
Download Part 2 (1GB)

From the readme, here is the list of software that is installed and fully configured:

Microsoft Windows Server 2003 R2 Service Pack 2
Internet Explorer 7
Microsoft Office Enterprise 2007
Microsoft Office Visio 2007
Microsoft Office Project 2007
Microsoft Office SharePoint Designer 2007
Microsoft SharePoint Portal Server 2007
Microsoft Visual Studio 2005 w/ Service Pack 1
Microsoft SQL Server 2005 w/ Service Pack 2 (w/ SSIS, SSAS, SSRS)
Microsoft SQL Server 2005 Data Mining Add-ins
Microsoft SQL Server 2005 SharePoint Integration Add-in
ProClarity Analytics Server 6.3
ProClarity Dashboard Server 6.3
ProClarity Desktop Professional 6.3
ProClarity Web Professional 6.3
Microsoft PerformancePoint Planning Server 2007
Microsoft PerformancePoint Monitoring Server 2007
Microsoft PerformancePoint Dashboard Designer
Microsoft PerformancePoint Planning Add-In for Excel
Microsoft PerformancePoint Planning Business Modeler
Project REAL Reference Implementation
SoftArtisans OfficeWriter 3.8.1 and Designer
BI Demo Files
Microsoft SQL Server 2005 Samples
Microsoft SQL Server BI Metadata Samples
Performance Point Planning Samples/Labs
Visual Studio 2005 Tools for Office Second Edition

New set of SSIS Tasks

These may have been around for a while, but I just discovered that there is a nice set of (affordable) SSIS tasks at CozyRoc.  There is a task for zipping files, managing partitions, SFTP and even using another dataflow as a source.  Here is the current info from their site:

 

Component
Key Features

Beta! Package SSIS Connection Manager

• Gain access to the current or different package at runtime.
• Support for all current types of package storage: SQL, File, SSIS Package Store.
• Support for package variables initialization before execution.
• Easy to use interface.

Beta! Data Flow Source SSIS Data Flow Component

• Based on Package Connection Manager.
• Very fast, non-blocking, in-memory data read from another data flow.
• Scalable alternative of the solution Microsoft is recommending with raw data file source.
• User interface is based on the standard data flow source component dialog.

Beta! Data Flow Destination SSIS Data Flow Component

• Exposes data from a data flow for in-memory read in another data flow.
• Scalable alternative of the solution Microsoft is recommending with raw data file destination.
• User interface is based on the standard data flow destination component dialog.

Database Partitions SSIS Control Flow Task

• Saves you time by handling the intricacies of the partitioning and letting you concentrate on your workflow instead.
• Create, insert and remove database partitions in Microsoft SQL Server 2005.
• Combine smaller partitions into a larger one, for easy backup later.
• Support for ADO.NET, OLEDB, ADO connection managers.
• Easy to use interface.

Script Task Plus SSIS Control Flow Task

• Extension of Microsoft SSIS Script Task, meaning you already have the skills to use it.
• Greater reusability and easier maintenance of script functionality.
• Customizable script user interface and setup.
• SSIS Script export, import or linking to.
• Keep your script code separate from your package and have easier time tracking changes in your source control system.
• Requires SQL Server 2005 SP2.
• New! Share your exported SSIS scripts with the community.

Zip SSIS Control Flow Task

• Compression and decompression of Zip, GZip, BZip2, Unix, Tar archives.
• Automatically switches to Zip64 format, if it is required.
• Streamlined user interface.

SSH SSIS Connection Manager

• Secure connections with SSH-enabled server.
• Authenticate with password or private key file.
• Support for Tunnel (HTTP), SOCKS4, SOCKS5 proxy.
• Streamlined user interface.

SFTP SSIS Control Flow Task

• Based on SSH Connection Manager.
• Secure-FTP (SFTP) communications and management.
• Support for SFTP commands send, receive and delete files, create and remove directory, rename file.
• The user interface covers most of the functionality of the standard Microsoft SSIS FTP task and it is very similar.

SSH Execute SSIS Control Flow Task

• Based on SSH Connection Manager.
• Secure execution of shell commands on a remote SSH server.
• Streamlined user interface.

More Interesting SSIS Components

I knew about the Konesans Trash Destination, but it turns out that they have a whole series of free, publicly available SSIS components.  The main product page is here, and below I have pasted in content for each of the transforms, with the link to the download page:

Checksum Transformation

 The Checksum Transformation computes a hash value, the checksum, across one or more columns, returning the result in the Checksum output column. The transformation provides functionality similar to the T-SQL CHECKSUM function, but is encapsulated within SQL Server Integration Services, for use within the pipeline without code or a SQL Server connection. Checksums can reduce network contention and increase process performance by allowing you to verify data through a single value rather than transferring all data values for comparison.

Data Generator Source

This source component literally generates data. Specify how many columns you want, and how many rows, then watch the data flow out. Build demonstration and research scenarios faster with this simple source.

File Watcher Task

The File Watcher Task does what it says really, it watches a folder waiting for files. When an available file is found the task completes, returning the name of the file for onward use within the package.

Regular Expression Transformation

The Regular Expression Transformation exposes the power of regular expression matching within the pipeline. One or more columns can be selected, and for each column an individual expression can be applied. If all columns selected pass their tests then rows are passed down the successful match output. Rows that fail to pass all tests are directed down the alternate output.

Row Count Plus Transformation

The Row Count Plus Transformation can replace the stock transformation. We have recreated and extended with more functionality and a user friendly interface for faster and easier package design.

Row Number Transformation

The Row Number Transformation calculates a row number for each row. It offers ROW_NUMBER or IDENTITY like behaviour within the Data Flow. Uses include surrogate key generation or data partitioning within the pipeline.

Trash Destination

The Trash Destination Adapter is a development aid. It allows you to quickly terminate a data flow path, and does not require any configuration. It will consume the rows without any side effects, and prevents warnings or errors you may otherwise receive when executing the data flow.

Additional SSIS Transforms that didn't ship with the product

There are a whole series of SSIS transforms that Microsoft has made available for download.  They have differing degrees of usefulness, and have been available for a while but I am putting them out here for future reference.  Off the top of my head, it looks like Regex would be useful, UnPackDecimal if you are deal with mainframe files, calendar transform (as I recall it has a dependency on a DLL from SSAS though).

 

Regex Transform
Regex is an SSIS dataflow component that applies a configured regular expression against an incoming column, matching, extracting, or splitting, as configured by the user.

ConfigureUnDouble Transform
ConfigureUnDouble takes a text column and removes bracketing quotes if present, plus replaces double quotes inside the text with sinqle quotes. Configure the quote character by, on the first tab of the advanced editor, setting the "Qualifier" custom property to the desired value.

RTrimPlus Transform
RTrimPlus takes a string or unicode column and removes trailing spaces, whether ASCII or Japanese.

UnpackDecimal Transform
UnpackDecimal takes an input column formatted in packed decimal (comp-3) and generates the corresponding Decimal value.

UnDoubleOut Transform
UnDoubleOut is an SSIS dataflow component that removes qualifiers from quoted text, either in place or via the creation of a new output column.

CodePageConvert Transform
CodePageConvert is an SSIS dataflow component that translates from and to any code page or unicode character representations.

SeeBuffer Transform
SeeBuffer is an SSIS dataflow component that sits in a data flow and is provided a look at each buffer that is presented to it.

UnDouble Transform
UnDouble takes a text column and removes bracketing quotes if present, plus replaces double quotes inside the text with sinqle quotes.

NullDetector Transform
NullDetector is an SSIS dataflow component that sits astride a data flow, and, depending on whether the value of a user-indicated column is null or not, routes rows to one or the other of its outputs.

Calendar Transform
CalendarTransform is an SSIS dataflow transform component that generates standard calendar attributes

Microsoft "Bulldog" (MDM)

Details are just coming to light regarding Microsoft's entry into the world of Master Data Management through their acquisition of Atlanta-based Stratature several months ago.  The product will be released to coincide with the next release of Office (Office 13), and will install as part of SharePoint.  According to the Bulldog product roadmap, the dev team is working with many Microsoft products such as PPS, Dynamics, SharePoint, Excel and more to integrate the master data hub with those products.  The stated goal is to have Bulldog remain almost identical to the Stratature +EDM product, with several enhancements aimed at improving the experience.

There is now a full product page at Microsoft, and Kirk Haseldon has updated his blog with some of the new details.  From his blog, it sounds like the planning has turned to development, and we should start to see something tangible in February 2008 for TAP program members and other early adopters.

One exciting aspect of the product is hierarchy management within an enterprise.  Taken directly from the product roadmap, here are some of the hierarchy scenario's that Bulldog will handle:

Master data contains a wide variety of hierarchies. The roll‐up hierarchies for accounts in a chart of accounts, the reporting hierarchy for employees, the organizational structure of a customer’s business, and categories of items in an item master are typical examples of hierarchies. These hierarchies are often hard coded into business systems or defined in a number of Excel spreadsheets. Hierarchies can be defined by attribute relationships: for example, city belongs to state and state belongs to region. These derived hierarchy relationships change whenever an attribute value changes. Hierarchies can also be defined as parent‐child relationships that may have an arbitrary depth along any path (ragged). The Stratature solution allows both types of hierarchies to be leveraged into a new derived hierarchy. The advantage of reuse is that it ensures that there is only one definition of the relationship at any point in time.

There is also extensive support for Business rules, workflow, flexible data models, etc.

Microsoft Performance Point Server 2007 finally RTM's

After years of hard work and a ProClarity acquisition later, Performance Point Server 2007 has finally been released.  More details here on the product home page.  Here are links to trial downloads and SDK.

PerformancePoint Server 2007 Evaluation Version (x86)

PerformancePoint Server 2007 Evaluation Version (x64)

Deployment Guide, Operations Guide, Whitepapers

Planning Server, Business rules development guide and Monitoring SDK