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?