OLAP or OLATP - How to determine

Question

I have SQL express server with database that is used by one application. I am trying to optimize the DB but am not sure if the DB is OLTP or OLAP.

There is no analysis server installed, the DB is inside the SQL Server instance hence it should be OLTP, but when I run the read/write ration on the DB I have the following:

          Reads    Writes    Read%   Write %

DB file   400 000   75 000   85%     15%

DB log        250   30 000    1%     99%

Standard OLTP DB should have more reads than writes, right? This makes me suspect that maybe It is OLAP DB.

I am asking this regarding the use of HyperThreading technologies, MAXDOP setting and whether to configure cache as write-through or write-back among other things.

The query I am using is as follow:

SELECT DB_NAME(DB_ID()) AS [Database Name] ,
[file_id] , num_of_reads , num_of_writes , num_of_bytes_read , num_of_bytes_written ,
CAST(100. * num_of_reads / ( num_of_reads + num_of_writes ) AS DECIMAL(10, 1)) AS [# Reads Pct] ,
CAST(100. * num_of_writes / ( num_of_reads + num_of_writes ) AS DECIMAL(10,1)) AS [# Write Pct] ,
CAST(100. * num_of_bytes_read / ( num_of_bytes_read + num_of_bytes_written ) AS DECIMAL(10, 1)) AS [Read Bytes Pct] ,
CAST(100. * num_of_bytes_written / ( num_of_bytes_read + num_of_bytes_written ) AS DECIMAL(10,1)) AS [Written Bytes Pct]
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) ;

Any thoughts from senior DBAs?

Answer 1

From a DBA perspective, the key difference between OLAP and OLTP is the tuning method you apply to the queries. The read/write ratio doesn't really tell you anything useful.

I have a little "magic quadrant" that I use to illustrate the difference (in your case, consider BI/DW and ETL the same as OLAP):

Basically, if you must touch a lot of data to produce query results, you are OLAP. Converse if you can get away with touching very little data to produce return values, you are OLTP.

Obviously, if you have poor indexing strategies, then OLTP can end up looking like OLAP - simply because you end up doing table scans all the time. A good way to make that distinction is to look at how much data MUST be touched by the top 10 most important queries in the system - no matter how good your indexing strategy is.

Once you have settled on what the majority of your workload is, you can start applying workload specific tuning techniques. Here are some of the workload specific tricks you might consider (always tailor to your specific workload)

OLAP:

• Prefer hash joins
• Optimise table scan speed (typically by laying out data for sequential I/O)
• Aggregate view are often better than indexes
• Column store indexes are better than B-trees
• Sequential I/O
• Aggressively de-normalise
• Use high MAXDOP
• Optimise network for bandwidth

OLTP:

• Prefer loop joins
• Optimise for index seek (there should always be an indexed path to the data)
• Rarely aggregate
• B-tree indexes
• Random I/O
• Keep database in 3NF
• Use MAXDOP 1
• Optimise network for latency

Answer 2

Since the database is created in a normal SQL Server instance, so it's an OLTP database as OLAP databases are created in Analysis Server instances only. Moreover, you cannot insert, update or delete from OLAP databases, so as a software developer you don't have the option to use OLAP as the core database for your solution. Not only that, you cannot query OLAP databases with normal SELECT statements, you should use MDX statements.

The OLTP stands for Online Transaction Processing, meaning that it depends on transactions. However, on the other side, OLAP depends on what is called Processing. OLAP databases depend on a normal database with a certain schema, after the data is loaded (probably using SSIS), the Processing takes place to load the data in OLAP database.