none
LOG IO spikes RRS feed

  • Question

  • We are seeing lot of spikes in LOG IO and it happens on a table with ntext column. COuld this be contributing to much of the log activity?

    CREATE TABLE [dbo].[RowProperties](
    [ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Properties_ID]  DEFAULT (newid()),
    [Key] [nvarchar](256) NOT NULL,
    [Value] [ntext] NOT NULL
    )

    If this is the case, other than redesigning the table are there any other options?

    grajee

    Tuesday, October 9, 2018 2:26 PM

All replies

  • Hi Grajee,

    First of all let me alert you that ntext is deprecated and can be removed in the future, so to avoid having to make changes in a short time I suggest to use nvarchar or varchar. More information here: https://docs.microsoft.com/en-us/sql/t-sql/data-types/ntext-text-and-image-transact-sql?view=sql-server-2017

    Log IO spikes will depend on the number of write operations being done on the database and the size of them. If the write operations on this table affect the "Value" field and the amount of data being handle is considerable then you can get those spikes but you will also get them when using nvarchar or varchar as it will depend on the number of write operations and the amount of data being handle.
    Tuesday, October 9, 2018 2:36 PM
  • Hello,

    NTEXT never stores the data in the form of a table, instead it stores it in the form of Large Objects structure, if you SET STATISTICS IO ON before running queries involving that table you will see high values on LOB logical reads and probably heavy clustered index scans. That means heavy IO consumption.

    If you convert the NTEXT column to NVARCHAR(MAX), you will see the amount of “LOB logical reads” will drop.


    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com



    Wednesday, October 10, 2018 4:31 AM