SSAS STORAGE MODES

In SQL Server Analysis Services 2008, we have three storage mode options available to us: Relational Online Analytical Processing (ROLAP), Multidimensional Online Analytical Processing (MOLAP) and Hybrid Online Analytical Processing (HOLAP). There are advantages and disadvantages to each, so I figured I’d take a few minutes to give a quick overview describing the storage modes and laying out some of the pros and cons of each.

Relational Online Analytical Processing (ROLAP)

The ROLAP storage mode allows the detail data and aggregations to be stored in the relational database. If you plan on using ROLAP, you need to make sure that your database is carefully designed or you’ll run into some bad performance issues.

Pros:

  • Since the data is kept in the relational database instead of on the OLAP server, you can view the data in almost real time.
  • Also, since the data is kept in the relational database, it allows for much larger amounts of data, which can mean better scalability.
  • Low latency.

Cons:

  • With all the data being stored in the relational database, query performance is going to be much slower than MOLAP.
  • You must maintain a permanent connection with the relational database to use ROLAP.

Multidimensional Online Analytical Processing (MOLAP)

MOLAP is the default and thus most frequently used storage mode. With MOLAP storage, the data and aggregations are stored in a multidimensional format, compressed and optimized for performance. This is both good and bad. When a cube with MOLAP storage is processed, the data is pulled from the relational database, the aggregations are performed, and the data is stored in the AS database.

Pros:

  • Since the data is stored on the OLAP server in optimized format, queries (even complex calculations) are faster than ROLAP.
  • The data is compressed so it takes up less space.
  • And because the data is stored on the OLAP server, you don’t need to keep the connection to the relational database.
  • Cube browsing is fastest using MOLAP.

Cons:

  • Because you don’t have a real time connection to the relational database, you need to frequently process the cube to update your data.
  • If there’s a large amount of data, processing is going to take longer.
  • There’s also an additional amount of storage since a copy of the relational database is kept on the OLAP server.
  • High latency.

Hybrid Online Analytical Processing (HOLAP)

HOLAP is a combination of MOLAP and ROLAP. HOLAP stores the detail data in the relational database but stores the aggregations in multidimensional format. Because of this, the aggregations will need to be processed when changes are occur. With HOLAP you kind of have medium query performance: not as slow as ROLAP, but not as fast as MOLAP. If, however, you were only querying aggregated data or using a cached query, query performance would be similar to MOLAP. But when you need to get that detail data, performance is closer to ROLAP.

Pros:

  • HOLAP is best used when large amounts of aggregations are queried often with little detail data, offering high performance and lower storage requirements.
  • Cubes are smaller than MOLAP since the detail data is kept in the relational database.
  • Processing time is less than MOLAP since only aggregations are stored in multidimensional format.
  • Low latency since processing takes place when changes occur and detail data is kept in the relational database.

Cons:

  • Query performance can head downhill fast when more detail data is queried from the relational database.
Advertisements

Best Practices while creating Stored Procedures

1. SET NOCOUNT ON: Always use ‘SET NOCOUNT ON’ statement at the begening of your code inside the SP to reduce unnecessary network round trips.

2. PARAMETER SNIFFING: Do not use SP parameters directly within the WHERE clause of SQL statements. This may cause the case of Prameter Sniffing. To avod this assign the parameter values to local variables and then use them with SQL queries.

3. Use table variables: Try to use table variables instead of temporary tables inside the SP to cache small record sets whenever possible.

4. Use of Temp Tables: If you think that the temporary record set may exceed upto millions of rows, then try using temporary table instead of table variables. Also try creating indexes on temporary tables if required.

5. Avoid using CURSORS: This will make your SP slow and degrade the performance. Try using more effecient SET based approaches instead of using CURSORS.

6. Use TRY-CATCH block: for Better error handling.

7. Using Transactions: Use Transactions by using BEGIN TRANSACTION and COMMIT/ROLLBACK TRANSACTION blocks to make sure that your actions follow ACID properties.
But keep the transctions as short as possible to create less blocking in database and thus avoiding deadlocks.

8. Aliasing objects: Do not use SQL Server reserve keywords while naming SQL Server objects, like for: tables, columns, views, etc. Although it allows most of the time, but its better to use other and better naming conventions.

9. Avoid GOTO stmt: Do not use GOTO statements in your code as it is considered a bad programming practice (and in every other programming language). Try comming up with a better approach/logic, use conditional IF-ELSE logic, WHILE loops and other programming constructs.

10. Avoid ‘sp_’ prefix : Do not prefix the SP name with ‘sp_’. If it begins with ‘sp_’ then the compiler will first search it in master database and then in the current one, thus delaying the execution.

11. Use fully qualified objects name: in your SQL queries, this helps in quickly finding the cached/compiled plan. Also execute the SP by using its fully qualified name, like: EXEC dbo.SPname

12. Use WITH ENCRYPTION: You can also use ‘WITH ENCRYPTION’ option while creating SP to hide the code.

13. Add Comments: Try to put comments wherever possible to give details and idea to other developers what actually your code is doing.

14. Use BEGIN-END code block: Try to bind your whole SP code within BEGIN-END block. Although it is optional but it looks good and gives a better picture.

16. Beautify your code: Well indent your code by uisng TABs, Spaces and New line (ENTER). Try going vertically downwards with your code rather expanding horizontally. This will make your code easy to read and more understandable to other developers.

SQL SERVER – Get Database Backup History


GO
-- Get Backup History for required database
SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB'AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id =m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO

Setting Up Alerts for All SQL Server Agent Jobs

Some times clients ask to enable alerts for the sql agent jobs, it will be difficult when we have more jobs, because we need to goto each and every job and enable the notifications.

Here is the script for enabling for the jobs,

Steps :

1. Create Mail Profile

2. Enable the mail profile for agent (SQL Server Agent Properties –> Alert System –>select Enable Mail profile & Select profile)

3. Create Operator with Mail ID. (Ex : DBA)

4. Run below script for enabling the alerts for all the jobs.

We need to decide when we want email to be trigger (i.e  after Complete, Failed, Success) below table will give the details.

Value Description
0 Never
1 On Success
2 On Failure
3 Always

In the below script i assume that , Alert is sent on 2 (On Failure) and alert will be sent to DBA (Operator).

use msdb
GO

DECLARE @operator varchar(50)
SET @operator = ‘DBA’

SELECT ‘EXEC msdb.dbo.sp_update_job @job_ID = ”’ + convert(varchar(50),job_id) + ”’ ,@notify_level_email = 2, @notify_email_operator_name = ”’ + @operator + ”’ ;’ FROM sysjobs

 

Execute the script which is generated by above script.
 

 

 

 

 

Windows Disk Space Alert From SQL SERVER JOB

In SQL server when the auto growth option is set to true and when we have defined the autogorwth in MB, some times we may face the issue like windows disk space is full, during that time SQL Server cannot increase the auto growth size of the mdf or ldf files.

As Best practice to have the alert for the drives free space. We can create a job for the below script. schedule it for every 15 or 30 minutes based on your application usage / load.

declare @svrName varchar(255)
declare @sql varchar(400)
set @svrName = @@SERVERNAME
set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'
--creating a temporary table
CREATE TABLE #output
(line varchar(255))
--inserting disk name, total space and free space value in to temporary table
insert #output
EXEC xp_cmdshell @sql
--script to retrieve the values in MB from PS Script output
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
(CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'capacity(GB)'
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
(CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace(GB)'
into #SpaceGB
from #output
where line like '[A-Z][:]%'
order by drivename
--script to drop the temporary table
--</code>

DECLARE @lowdrives VARCHAR(100)
set @lowdrives= NULL

SELECT @lowdrives = COALESCE(@lowdrives + ' ', '') + drivename + '  freespace(GB):  ' + convert(varchar(50),[freespace(GB)])  FROM #SpaceGB where  [freespace(GB)] <= 50  <span style="color: #ff0000;">--replace your Limit GB</span>
select @lowdrives
if @lowdrives is not NULL
begin

EXEC msdb.dbo.sp_send_dbmail
@profile_name =<span style="color: #ff0000;"> '    ', --- replace with profile name</span>
@recipients = '<span style="color: #ff0000;">  ', --- replace with receiver Mail id</span>
@subject = 'Emergency ** SQL SERVER ** DRIVE SPACE ISSUE' ,
@body =  @lowdrives
end

drop table #output
drop table #SpaceGB

Types of SQL Server Backup -1 : FULL Backup

The most common types of SQL Server backups are complete or full backups, also known as database backups.  These backups create a complete backup of your database as well as part of the transaction log, so the database can be recovered. This allows for the simplest form of database restoration, since all of the contents are contained in one backup.

A full backup can be completed either using T-SQL or by using SSMS

Create a full backup of the AdventureWorks database to one disk file

T-SQL

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK'
GO

SQL Server Management Studio

  • Right click on the database name
  • Select Tasks > Backup
  • Select “Full” as the backup type
  • Select “Disk” as the destination
  • Click on “Add…” to add a backup file and type “C:\AdventureWorks.BAK” an click “OK”
  • Click “OK” again to create the backup