3

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?

migrated from stackoverflow.com Jan 17 '15 at 8:19

This question came from our site for professional and enthusiast programmers.

  • The percent of read/write is only part of the picture. If your goal is to ascertain the type of workload using DMVs, I suggest sys.dm_exec_query_stats to calculate the average logical I/O per query. – Dan Guzman Jan 17 '15 at 18:03
14

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):

WorkLoad Categories

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
-5

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.

  • 3
    OLAP is a workload, not a specific technology. It's perfectly viable to use standard SQL Server as an OLAP engine. – Thomas Kejser Jan 17 '15 at 12:15
  • I know it is not related to a specific technology but the question mentioned that SQL Express is used which is an edition of SQL Server. And Analysis Server is a part of SQL Server which I mentioned that it supports OLAP. My answer depends on the question asked; moreover, I said SQL Server does support OLAP but it seems you don't know that Analysis Server is part of SQL Server. Unfortunately, you made a negative vote while you don't know the details of the answer. Have a nice day. – HGF Jan 17 '15 at 17:29
  • 2
    HGF, I think you missed the point of @ThomasKejser's comment. He is probbaly referring to your very first sentence (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.) I think me means that it's perfectly fine (although not very common I'd guess) to use SQL-Server - and not the Analysis Server - for an OLAP workload. – ypercubeᵀᴹ Jan 17 '15 at 18:10
  • 2
    Please do not assume you know who voted - just because someone commented does not mean they voted down. Votes are private for a reason and I've never seen any good come out of stressing about who voted which way and when. – Jack Douglas Jan 17 '15 at 18:14
  • 3
    @HGF: I wrote the Analysis Services Performance guide 2008R2, so I do have a bit of knowledge about that engine too. But even SQL Server Express has its place in OLAP workloads. Outside of the Microsoft bubble and DAX/MDX confusion, SQL is still the most widespread query language and relational engines are great OLAP engines too. – Thomas Kejser Jan 17 '15 at 22:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.