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?
Best Answer
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:
OLTP: