Wednesday, February 25, 2009

SQL Server 2005 Performance Dashboard

SQL Server Performance Dashboard is a handy set of reports which I would highly recommend to anyone who wants to dig a little deeper into SQL Server. Requires SQL Server 2005 SP2 or above.

You can download the Performance Dashboard here:
http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

Here are a couple of screenshots showing the installation process:




By default, the dashboard report files install to
c:\program files\Microsoft SQL Server\90\Tools\PerformanceDashboard\



To complete the setup process, locate the setup.sql script in the PerformanceDashboard folder and run it against the server you wish to monitor.



Right click on the server, go to reports and then custom reports.
The best place to start is performance_dashboard_main.rdl. From this report you can navigate to all of the others.



So that's it for the installation - there are some really good reports to browse straight out of the box.
In my next post I will write about some of the more interesting metrics available through the performance dashboard.

Saturday, February 14, 2009

Using AMO to interface with SQL Server Analysis Services (SSAS)

In SQL Server 2000, working programmatically with SSAS was a real challenge. Performing a simple operation such as cube processing was a non trivial process. One of the systems I have been working on recently was using the following technique to process a cube:

1. Client application calls a web service

2. The web service calls stored procedure

3. The stored procecure uses sp_oacreate() to invoke a DTS package

4. The DTS package uses an Analysis Services Processing Task to process the desired cube

Needless to say this chain of execution is more complicated that we would like, and there are many problems that can be encountered using this technique. For example, sp_oacreate has been known to cause memory fragmentation problems in the mem-to-leave area of SQL Server. This issue was resolved in SQL 2000 SP4, however I am still not convinced that the process of building up and tearing down objects is rock solid.

Wouldn’t it be nice to have an API straight into SSAS?

In SQL 2005 and 2008, we can use AMO (Analysis Management Objects)!

http://msdn.microsoft.com/en-us/library/ms345089(SQL.90).aspx

AMO is a straightforward, simple to use object model which allows you to do almost anything with SSAS programmatically. In fact, SQL Server Management Studio (SSMS) is nothing more than a well presented SMO/AMO client.

The point is that it is possible to build extremely powerful management applications by hooking into the AMO API. How do you get started with AMO?

Here is a simple starting point:

· Create a new Visual Studio 2008 Console Application

· Add a reference to Microsoft.AnalysisServices.dll ( I am using version 9.0.242.0)

· Add a using directive to the top of your code

using Microsoft.AnalysisServices;

· Now you are ready to start using the AMO object model. Instantiate a server object and connect to SSAS by providing a connection string

Server s = new Server();

s.Connect(“Provider=msolap.3;Datasource=localhost;Initial Catalog=FINANCIALS'”);

You can check the boolean value of s.IsConnected to ensure that you are ready to do some work, although I expect the Connect() method would throw an exception if it doesn’t find the designated server.

Once you have a reference to the server object, you can access other objects by traversing the hierarchy. The following two lines get a reference to a database by name and then process that database

Database d = s.Databases.GetByName("MY_FINANCIALS");

d.Process();

So there you have it – programmatically processing an SSAS cube in four lines of code. We’ve come a long way since SQL 2000!