... SQL Server can use the memory notification mechanisms that are provided by the QueryMemoryResourceNotification Windows API. On a Microsoft Windows 2000 Server-based system, SQL Server periodically calculates the free physical memory on the system by using the native Windows API. Based on this information from the QueryMemoryResourceNotification Windows API or from the memory calculation, SQL Server responds to the current memory situation on a specific system...

Automating build of Analysis Services Projects
As you may be aware, an Analysis Services project consists of the following files:

A project file (*.dwProj)
One or more Data sources (*.ds)
One or more Data source views (*.dsv)
A database file (*.database)
One or more cube files (*.cube) with their partition (*.partitions)
One or more dimension files (*.dim)
You can right click the project in Visual Studio and choose "build". What happens now seems to be a lot of called to DLL files executed by the integration between Visual Studio and the Analysis Services. The output of this build is the bin folder in your project directory. This folder contains a database file (*.asdatabase). The .asdatabase file is the one you want - you will see why later.

Unlike other projects - which you can build using their proper compiler (Example: CSC.EXE, VBC.EXE) - Analysis Services does not seem to have a compiler executable.

You could be tempted to run msbuild. Unlike most temptation this will result in disappointment - msbuild does not understand Analysis Services files.

The only way I have found to automate the building of Analysis Services projects is to invoke Visual Studio from the command line. Say you have a project called: MyAsProject.dwProj. To build it and obtain the asdatabase file you execute the following:

devenv MyAsProject.dwProj /rebuild Relase /project MyAsProject

So far so good - now you have automated the createion of the .asdatabase file.

You are not done yet. The asdatabase file provides the input to the Analysis Services Deployment Wizard. The spell cast by this wizard allows you to transform an asdatabase filen into an XML/A file. This XML/A file can be run on your deployment target - which will create the cube database.

From the command line the Deployment Wizard can be run in two modes.

The answer mode:
Microsoft.AnalysisServices.Deployment.exe /a
In this mode, you get to choose all the server specific settings and write them back to your bin directory. Check out the BOL documentation to find out exactly how this is implemented (You will find no defense for the naming of the executable in the documentation)

Once you have your answers stored (which you will only do once) you want to automate the creation of the XML/A file. Execute:

Microsoft.AnalysisServices.Deployment.exe MyAsProject.asdatabase /d /o:MyProject.xmla

Voila! You have your xmla file, which you can copy to your deployment target and run. By the way. xmla files can be run from the command line using ascmd.exe

OLAP Heap isn't an stable as Windows, especially if you have a large OLAP database (we're 500GB and I guess that's considered large)...

SSAS performance counters


* Direct hit ratio - Ratio of cache direct hits to cache lookups, for the period between obtaining counter values. Higher is better.
* Evictions / sec - Rate of evictions from the cache. This is per partition per cube per database. Typically due to background cleaner. Could indicate memory issue is the cache is being excessively cleaned.
* Misses / sec - Rate of cache misses. High ratio indicates that queries are having to go to disk
* Current Connections - Current number of client connections established.
* Current user sessions - Current number of user sessions established.
* Total calculation covers - Total number of of calculation covering subcubes in MDX query plan. High value means that queries are being resolved cell by cell instead of block operations.
* Total cells calculated - Total number of cell properties calculated. High value means queries being resolved cell by cell.
* Total recomputes - Total number of cells recomputed due to error
* Memory Usage KB - Memory usage of the server process. Same as perfmon counter for Process\PrivateBytes.
* Dimension queries / sec - high number means heavy trips to the storage engine
* Measure group queries / sec - high number means heavy trips to the storage engine
* Available Mbytes - Available MBytes is the amount of physical memory available to processes running on the computer
* Page Faults / sec - high page faults / sec could indicate inadequate memory. Counter has both hard and soft page faults. Soft page faults are okay. Hard page faults require disk access and can hinder performance.
* % Processor Time - > 80 % constant value indicates inadequate processor power
* Avg. Disk Queue Length - disk queues occur when the disk is busy reading or writing information. High disk queue lengths indicate inadequate disk performance. Rule of thumb is Avg. Disk Queue Length < 3 for an extended period.

Security checklists for different products

Automating build of Analysis Services Projects
As you may be aware, an Analysis Services project consists of the following files:

A project file (*.dwProj)
One or more Data sources (*.ds)
One or more Data source views (*.dsv)
A database file (*.database)
One or more cube files (*.cube) with their partition (*.partitions)
One or more dimension files (*.dim)
You can right click the project in Visual Studio and choose "build". What happens now seems to be a lot of called to DLL files executed by the integration between Visual Studio and the Analysis Services. The output of this build is the bin folder in your project directory. This folder contains a database file (*.asdatabase). The .asdatabase file is the one you want - you will see why later.

Unlike other projects - which you can build using their proper compiler (Example: CSC.EXE, VBC.EXE) - Analysis Services does not seem to have a compiler executable.

You could be tempted to run msbuild. Unlike most temptation this will result in disappointment - msbuild does not understand Analysis Services files.

The only way I have found to automate the building of Analysis Services projects is to invoke Visual Studio from the command line. Say you have a project called: MyAsProject.dwProj. To build it and obtain the asdatabase file you execute the following:

devenv MyAsProject.dwProj /rebuild Relase /project MyAsProject

So far so good - now you have automated the createion of the .asdatabase file.

You are not done yet. The asdatabase file provides the input to the Analysis Services Deployment Wizard. The spell cast by this wizard allows you to transform an asdatabase filen into an XML/A file. This XML/A file can be run on your deployment target - which will create the cube database.

From the command line the Deployment Wizard can be run in two modes.

The answer mode:
Microsoft.AnalysisServices.Deployment.exe /a
In this mode, you get to choose all the server specific settings and write them back to your bin directory. Check out the BOL documentation to find out exactly how this is implemented (You will find no defense for the naming of the executable in the documentation)

Once you have your answers stored (which you will only do once) you want to automate the creation of the XML/A file. Execute:

Microsoft.AnalysisServices.Deployment.exe MyAsProject.asdatabase /d /o:MyProject.xmla

Voila! You have your xmla file, which you can copy to your deployment target and run. By the way. xmla files can be run from the command line using ascmd.exe