ToolBox

ASLB Toolkit

2010-02-09T03:00:00Z

Author: Carl Rabeler

Contributor: Josh Caplan

Reviewers: Prem Mehra, Denny Lee, John Desch, Wayne Robertson, Steve Pontello, Thomas Kejser

The ASLB toolkit is a custom Analysis Services load balancing solution that consists of a load balancing Web service backed by a SQL Server load balancing metadata database to load balance MDX queries across multiple query servers. The metadata database contains information about each Analysis Services query server to which queries can be redirected by the Web service.

Load Balancing Web Services	Location of the Analysis Services load balancing (ASLB) Web application. Internet Information Services (IIS) is required on these servers. These servers can use Network Load Balancing (NLB) for failover. For more information about using Network Load Balancing, see Network Load Balancing Deployment Guide.
Load Balancing metadata db	Location of ASLB database. The ASLB database can be mirrored for failover (optional, but recommended for availability). For more information about mirroring, see Database Mirroring Best Practices and Performance Considerations.
AS servers with IIS and MSMDPump	Location of Microsoft® SQL Server® Analysis Services databases. IIS and MSMDPump.DLL must be installed on each of these servers.

Important: For more information, including a discussion of the problems that are solved by ASLB that are not solved by other software and hardware load balancing solutions, see the Microsoft SQL Server 2008 Analysis Services Consolidation Best Practices paper on the SQLCAT web site.

To Get Started: Download the setup instructions and solution files at: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=c7737e80-8dfd-4cb6-a27e-69bbe03b2f9e

Response Time Analysis using Extended Events

2009-04-24T23:20:00Z

Contributors: Raoul Illyes, Sanjay Mishra, Jerome Hallmans, Thomas Kejser, Mario Broodbakker

This tool demonstrates response time analysis at the session or statement level including waitstats using the new Extended Events infrastructure in SQL Server 2008. This tool is based on the simple principle:

Response time = service time + wait time

This tool allows you to drill down on the time spent in serving the user requests and the time spent in waiting for resources.

Download the application and documentation from http://sqlcat.codeplex.com/Wiki/View.aspx?title=ExtendedEventsWaitstats. Follow the User Guide to install and use the tool. The download also contains the source code for the project.

Restart SQL Audit Policy and Job

2009-04-23T03:19:00Z

Authors: Ayad Shammout, Denny Lee, Jack Richins

As noted within the Reaching Compliance: SQL Server 2008 Compliance Guide (you can also check the sqlauditcentral codeplex project), an easier way to view and manage all of the audit logs within your SQL Server environment is to place all of the audit logs in one central location. As per the guide, you can then use a SSIS package to import in all of these logs files into a separate SQL database where you can then generate reports to view all of the audits within your entire SQl Server environment.

The problem that we recently discovered is that if SQLAudit loses connectivity to the folder it places the audit files, provided that you did not tell SQL Server to shutdown if it cannot write an audit:

The audit’s is_state_enabled column in sys.server_audits will remain 1, meaning true, but the audit status in sys.dm_server_audit_status will be “RUNTIME_FAILED” and no events will be written to the audit log.
Even when connectivity to the folder has returned, the audit will remain in the “RUNTIME_FAILED” state - meaning it still tries to write to the log but will always fail as it is using an old and now invalid handle, or reference, to the audit log from before the connectivity loss. Currently the only way to get the audit to create a new valid handle for the audit log is to stop and restart the audit – which will create a new audit file.

There is a bug assigned to this issue and will be resolved in the future. But for us whom are working with SQL Audit right now, to work around this problem, please go to the sqlauditNetworkConnectivity Codeplex project where you can download the full Centralized Audit Framework project. Within this project is the Restart SQL Audit Policy and Job folder. This folder contains three pieces of source code:

Server Audit Status (Started).xml - Import this on-schedule policy into your server's Policy-Based Management as it will determine if the audit is enabled and able to write to the file system.
Create Audit Job.sql - This is a SQL Server Audit job that will execute the noted policy; you will need to schedule this yourself
Create Audit Alert.sql - This is a SQL Server Audit job that will create an audit alert.

Together these three source components will (whenever manually executed or scheduled) determine if all of the audits on your server are able to write to the folder. If they are not, they will send out an alert as well as stop and restart the audit job re-initializing it so that way the audits will start writing again.

SSIS Package For Collecting SSAS DMV Data

2009-03-02T21:42:00Z

SSIS Package For Collecting SSAS DMV Data at http://www.codeplex.com/SQLSrvAnalysisSrvcs

This project on Codeplex includes an SSIS package along with its configuration file to collect Analysis Services Dynamic Management View data by means of a SQL Agent job and store the collected data in tables in a database called DMV. These scripts were extracted from the A Solution for Collecting Analysis Services Performance Data for Performance Analysis project in the same codeplex release and are intended to demonstrate, in a stand-alone fashion, how to periodically query your Analysis Services instance using DMVs to determine the current state of your server - collecting connection, session and command information.

SSAS Monitoring Scripts For Management Data Warehouse

2009-02-27T21:27:00Z

SSAS Monitoring Scripts For Management Data Warehouse at http://www.codeplex.com/SQLSrvAnalysisSrvcs

This project on Codeplex includes scripts for creating a custom collection set in Management Data Warehouse for collecting performance counter data specific to Analysis Services operations. These scripts were extracted from the A Solution for Collecting Analysis Services Performance Data for Performance Analysis project in the same codeplex release.

These scripts were updated on 3/6/2009 to fix bug related to named instances - namely, Performance Monitor counters for Analysis Services named instances must be entirely in capital letters in order to work in the Management Data Warehouse performance counter data collector.

SQL Server Partition Management Tool

2009-02-24T03:48:00Z

SQL Server Partition Management Tool is available at http://www.codeplex.com/SQLPartitionMgmt with source code.

This tool provides a set of commands (at the Command Line or via Powershell) to create a staging table on-demand (including all appropriate indexes and constraints) based on a specific partitioned table and particular partition of interest. By calling this executable, with parameters, from maintenance scripts or SSIS packages, DBAs can avoid having to ‘hard code’ table and index definition scripts for staging tables. The tool eliminates the challenge of keeping such scripts in synch with changes to partition tables’ columns or indexes. It also provides a fast, single-command shortcut for the operation of quickly deleting all data from a partition.

This tool supports SQL Server 2008, but is also fully compatible with SQL Server 2005. An earlier verison of the tool (for SQL Server 2005) is also available on codeplex under the same project.

The latest version supports new features in SQL Server 2008 such as filtered indexes, new data types, and partition-aligned indexed views.

-- Stuart Ozer / SQLCAT

Scripts for Stress Testing Analysis Services using ASCMD

2009-02-06T00:37:00Z

Scripts for Using ASCMD for Stress Testing at http://www.codeplex.com/SQLSrvAnalysisSrvcs

This project on Codeplex includes two scripts for using ASCMD for stress testing. One script is optimized for serially executing a stream of queries and the second script is for executing multiple streams of queries in parallel. You can control the number of queries, the number of streams, and the think time between queries.

A Solution for Collecting Analysis Services Performance Data From Many Sources For Performance Analysis

2009-02-06T00:17:00Z

A Solution for Collecting Analysis Services Performance Data for Performance Analysis at http://www.codeplex.com/SQLSrvAnalysisSrvcs

This codeplex solution automates the collection of SQL Server 2008 performance data from any or all of the following data sources ans stores the collected data into a single SQL Server 2008 relational database:

SQL Server Profiler Analysis Services trace data
Performance Monitor counters
Analysis Services Dynamic Memory Views (DMVs)

This data is collected using an Analysis Services server-side trace, several Integration Services packages, a custom performance monitor collector in Management Data Warehouse, and Transact-SQL stored procedures. This codeplex project also include sample Reporting Services reports utilizing SQL Server stored procedures that correlate and analyze the collected data.These reports enable you to:

Determine your slowest MDX queries for a specified time period
Compare the performance of a specified query during different time periods
Analyze a specific query to determine if it is storage engine or formula engine bound, to view the aggregations utilized, the number of subcubes and the number of partitions
Analyze processing performance
Correlate performance monitor counters with the execution of a specific query or processing operation
Correlate DMVs with the execution of a specific query or processing operation

This solution was updated on 3/6/2009 and again on 3/31/2009 to:

Consolidate all environment variables into a single script file - this dramatically simplifies installation and operation
Fix bugs related to named instances - including, but not limited to the following - Performance Monitor counters for Analysis Services named instances must be entirely in capital letters in order to work in the Management Data Warehouse performance counter data collector.

Compress Many-to-Many C# Utility

2008-03-24T20:38:00Z

http://sqlsrvanalysissrvcs.codeplex.com/

This project creates a utility that enables you to easily implement the many-to-many compression technique discussed in the Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques best practices white paper. You can download this white paper from the SQL Server Best Practices web site at: http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx

Create a Processing Log Script Project

2008-03-24T20:35:00Z

http://sqlsrvanalysissrvcs.codeplex.com/

This project contains a script that enables you to create a system-wide processing log file for monitoring / auditing purposes. It also contains a script that enables you to delete a system-wide processing file.

Powershell Scripts for SQL Server 2008 Analysis Services DMVs

2008-03-24T20:24:00Z

http://sqlsrvanalysissrvcs.codeplex.com/

These sample scripts demonstrate how to use Powershell to query SQL Server 2008 Analysis Services DMV's. The four sample scripts enable you to cancel all queries running longer than a specified length of time, cancel a particular session/SPID/connection, retrieve the Analysis Services version, or return the results of any DMV query.

SQL DMVStats Toolkit

2008-02-21T16:48:00Z

http://www.codeplex.com/sqldmvstats

A SQL Server 2005 Dynamic Management View Performance Data Warehouse

Introduction

Microsoft SQL Server 2005 provides Dynamic Management Views (DMVs) to expose valuable information that you can use for performance analysis. DMVstats 1.0 is an application that can collect, analyze and report on SQL Server 2005 DMV performance data. DMVstats does not support Microsoft SQL Server 2000 and earlier versions.

Main Components

The three main components of DMVstats are:
• DMV data collection
• DMV data warehouse repository
• Analysis and reporting.
Data collection is managed by SQL Agent jobs. The DMVstats data warehouse is called DMVstatsDB. Analysis and reporting is provided by means of Reporting Services reports.

For more details, refer to the file attachment DMVStats.doc.

Tom Davidson & Sanjay Mishra
Microsoft SQL Server Customer Advisory Best Practices Team

SQL Server 2005 Partition Management Tool

2008-02-21T16:47:00Z

http://www.codeplex.com/sql2005partitionmgmt

Sliding Window technique is commonly used with partitioned tables to manage large data volumes. It allows efficient loading of new data and archival or removal of the old data. The challenges in a sliding window scenario is to create the staging table(s) correctly for efficient partition SWITCH operations. This utility provides a command line interface to:

1. Remove all the data from one partition by switching it out to a staging table. It creates the required staging table.
2. Create a staging table for loading data into a partition. The staging table can be created with or without indexes -- if created without indexes this utility provides a separate command to create appropriate indexes on the staging table, before SWITCHing it into the partitioned table.

The commands can be invoked from other scripts for end-to-end sliding window scenarios.

Scripts and Tools for Performance Tuning and Troubleshooting SQL Server 2005

2008-02-21T16:44:00Z

http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/default.mspx

Sample scripts and stored procedures for managing and working with SQL Server 2005.

Buffer Cache
Sample scripts and stored procedures for managing and monitoring the SQL Server buffer cache.

CPU and Optimization
Sample scripts and stored procedures for optimizing CPU performance in SQL Server.

Indexes and Indexing
Sample scripts and stored procedures for working with SQL Server indexes.

Input/Output
Sample scripts and stored procedures for managing and monitoring input and output in SQL Server.

Performance (General)
Sample scripts and stored procedures for managing and monitoring general performance issue in SQL Server.

Procedure Cache
Sample scripts and stored procedures for managing the SQL Server procedure cache.

SQL Text
Sample scripts and stored procedures for working with SQL text.

SQLOS
Sample scripts and stored procedures for working with the new SQL Server Operating System Layer.

Tempdb
Sample scripts and stored procedures for working with the tempdb system database.

Transactions and Logging
Sample scripts for working with SQL Server transactions and locks.

Waitstats
Sample scripts and stored procedures for working with SQL Server waitstats.

SQL Server 2005 Best Practices Analyzer

2008-02-21T16:42:00Z

http://www.microsoft.com/downloads/details.aspx?FamilyId=DA0531E4-E94C-4991-82FA-F0E3FBD05E63&displaylang=en

The SQL Server 2005 Best Practices Analyzer (BPA) gathers data from Microsoft Windows and SQL Server configuration settings. BPA uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential issues in the database environment.