Table Design for XML Message logging

• hariharan_marudan

  SSC Rookie

  Points: 42

  More actions

  September 10, 2009 at 10:07 am

  #215028

  Hi,

  I am desiging a table for logging xml messages, size of the row may grow more than 8kb (page size).

  I am little worried about the Query, which will eat up the processing time, if the record size goes above the page size (8kb) and store it in ROW_OVERFLOW_DATA.

  (Row-Overflow Data Exceeding 8 KB)

  http://msdn.microsoft.com/en-us/library/ms186981.aspx

  Table structure :

  Message_Id Int

  Message_Value varchar(max)

  Can someone please help me on the Best practices for storing xml message into a SQL Table, what will be best data type can be used ? size of the xml message gets stored is not limited to the size (can be of size 2kb, 8kb, 10kb, 200kb etc...). Expecting your valuable response.

  Note : I cannot use the xml datatype as we may get unstructured xml sometime, leads to parsing error while inserting the data.

  Thanks in advance.

  regards

  Hariharan

• sturner

  SSC-Insane

  Points: 22411

  More actions

  September 10, 2009 at 12:12 pm

  #1051861

  Only advise I could give is to avoid XML and use straight text. XML is costly in terms of resources and if all you are doing is just logging the messages I would strip the XML out first.

  The probability of survival is inversely proportional to the angle of arrival.

• Elliott Whitlow

  SSC Guru

  Points: 102296

  More actions

  September 10, 2009 at 12:24 pm

  #1051877

  Since you are simply storing the data I don't see any issue with varchar(max). If you can parse the data out into normal columns that would be even better, but if your data doesn't support then then the varchar(max) should be just fine.

  I'm not sure what your concerns are about your query, it does have to reach out to other pages to get overflow data but if you have generally smaller data (<8000 bytes) then the overflow is the exception.. Even if it happens a lot I don't think you will have any problems.

  size of the row may grow more than 8kb (page size).

  Is this the norm or the exception?

  CEWII

• LutzM

  SSC Guru

  Points: 107049

  More actions

  September 10, 2009 at 1:23 pm

  #1051915

  irrelevant post. Sorry.

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• Paul White

  SSC Guru

  Points: 150467

  More actions

  September 10, 2009 at 10:57 pm

  #1052066

  This is a complex subject. A comprehensive discussion of the issues surrounding when to use XML, [n]varchar(max), and varbinary(max) storage can be found here

  I would also add the ROW_OVERFLOW_DATA is only used with varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns. If the combined lengths of these columns exceeds the 8060 byte row limit, some data may be moved off-row to ROW_OVERFLOW_DATA allocation unit(s). See Row-Overflow Data Exceeding 8K.

  XML and the MAX data types use the same on-disk structures as the other LOB data types (text, ntext, image), but only once the data needs to move off-row. ROW_OVERFLOW_DATA allocation units are not used.

  Paul

  edit: fixed link

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi