Using SQL Profiler for Analysis server to monitor and trap slow running MDX queries

From SQL 2005 onwards, SQL profiler can be used to run a trace against Analysis server which was not available with earlier versions.

Running a trace to monitor SSAS is similar to using profiler against Database engine.
Start SQL profiler from start menu under SQL server program group.

Start a new trace and connect to Analysis server instace.

In Events Selection tab, capture Queries Events and deselect all other events.

Beneath Query Events, There will be two main event classes as following:

1>Query Begin: This event class provides information about the query that was submitted to Analysis Services. The Event Subclass column provides information about the type of query.

2>Query End: This event class verifies the status of the query execution.
Duration data column shows the amount of time in milliseconds taken by the event and can be monitored to get the long running mdx queries.

Othe data columns which could be tracked are: TextData, DatabaseName, EventSubClass ....

Also note the following before running a traceMonitor only those events which are required as monitoring too many events adds extra overhead.To create traces you must be a member of the Analysis Services server role.

SQL Server 2008: in-line declaration and assigning values to variable

It might sound strange, but with SQL 2008, I would say much awaited feature of assigning default value to a variable at the time of declarion has been introduced.
It means you can assign a default value to the declared variable in-line without adding extra SET statement in the new line.

Syntax: DECLARE @local_variable [AS] data_type [ = value ]
Here [= value] is optional and is used to assign a value to the variable in-line. The value can be a constant or an expression, but it should match the variable declaration type or be implicitly convertible to that type.

Example:
DECLARE @v1 int;
/* Also allowed:
DECLARE @v1 int = 10;
*/
SET @v1 = 10;
SELECT @v1;

Merging Analysis Server Mesure Group Partition using AMO

Merging partitions refers to combining two or more partitions into one partition.
It merges the data of source partitions into a target partition and deletes the source partitions. Partitions can be merged if they are in the same measure group and have same storage mode. Also the source and target partitions has to be fully processed.

Following code sample merges partitions of a specified measure group into the first partition.
Dim mg As MeasureGroup = cube.MeasureGroups.GetByName(MGname)
Dim i As integer
Dim partArray(mg.Partitions.Count - 1) As Partition
For i = 1 To i < mg.Partitions.Count
partArray(i - 1) = mg.Partitions(i)
i = i + 1
Next
mg.Partitions(0).Merge(partArray)
mg.Update()

SQL Server 2008 Query Editor IntelliSense(SSMS Enhancements)

The Database Engine Editor in SQL server 2008 now comes with IntelliSense functionality such as word completion and error underlining.
IntelliSense provides an array of options that make language references easily accessible.
While coding and without leaving the editor, you can find the needed information, insert language elements directly into your code and even have IntelliSense complete your typing.

Following are few useful Intellisense options. . . . . . . .
1. Parameter Info: This option opens a parameters list that provides information about the number, names and parameter type that are required by a function or procedure.

2. Complete Word: This option types the rest of a variable, command, or function name once you have entered enough characters to uniquely identify the term.

Many IntelliSense features work automatically by default, but you can disable any feature.

SQL server 2008 Storage optimization with SPARSE columns in a table

Sparse columns are designed to have an optimized storage format for null values. Consider using sparse columns when at least 20 percent to 40 percent of the values in a column will be NULL.
This SQL server 2008 feature can be very useful in terms of table storage.
Consider this example when table is created with following structure.
CREATE TABLE Books
(BookID int PRIMARY KEY,
BookTitle varchar(200) NOT NULL,
BookSpecs varchar(20) SPARSE NULL,
BookServey smallint SPARSE NULL) ;
So the code in this example is creating a table with two columns as SPARSE columns.
The SQL Server Database Engine uses the SPARSE keyword in a column definition to optimize the storage of values in that column. Therefore, when the column value is NULL for any row in the table, the values require no storage.

But Sparse columns are restricted to many things like:
A sparse column must be nullable and cannot have a default value
Sparse columns require more storage space for nonnull values than the space required for identical data that is not marked SPARSE.

Overall, making a column as sparse column is beneficial only when more than 40 percent of column values will be NULL.

Table-valued parameters in SQL Server 2008

With SQL Server 2008, Using Table-Valued Parameters in Transact-SQL is a new feature.Follow these steps to achieve the same:
A. Create a table type and define the table structure
eg: /* Create user-defined table type. */
CREATE TYPE LocationTableType AS TABLE ( LocName VARCHAR(50), LocSpeed INT );
GO

B. Create a procedure with an input parameter of the table type. eg:
CREATE PROCEDURE Select_LocTabletype
@TabValParam LocationTableType READONLY
AS
SET NOCOUNT ON
SELECT *, GETDATE() FROM @TabValParam;
GO

C. Now Prepare for calling the above procedure by declaring a variable of the table type with due reference.
Insert rows into this table variable.
After the table variable is created and filled, you can pass the variable to the procedure. eg:

DECLARE @LocationTVP AS LocationTableType;
INSERT INTO @LocationTVP (LocName , LocSpeed)
VALUES('Mumbai',500), ('Pune',250)
EXEC Select_LocTabletype @LocationTVP;
GO
After the procedure is out of scope, the table-valued parameter is no longer available. The type definition remains until it is dropped explicitly.

New in SQL 2008:Inserting Multiple rows in One Insert..Value statement

With SQL Server 2008, Microsoft has introduced new feature from developer's perspective whereby executing One insert statement, you can insert multiple rows.

Consider below mentioned example with SQL 2005:

Declare @t1 TABLE(column1 INT)
INSERT @t1 VALUES(10)
INSERT @t1 VALUES(20)

Same example can be re-written with SQL 2008 as
Declare @t1 TABLE(column1 INT)
INSERT @t1 VALUES(10),(20)

Database Unit Testing with Visual Studio Team Edition

This technical article covers the important toolset for performing database unit tests.
http://msdn2.microsoft.com/en-us/library/bb381703(VS.80).aspx

HierarchyID datatype in SQL Server 2008

SQL Server 2008 has come up with a NEW datatype, hierarchyID which is system-provided. Use hierarchyid as a data type to create tables with a hierarchical structure, or to reference the hierarchical structure of data in another location.
One needs to deal with Hierarchical data in terms of hierarchical relationships where one data-item is the parent of another data-item.
The most common example where such data exists:

• An organizational structure related application.

SQL server 2008 gives many related hierarchyid functions to query and working with hierarchical data by using Transact-SQL.

If interested in more technical reading, Please refer to below link.
http://www.sqlservercentral.com/articles/SQL+Server+2008/62204/