Trying to evaluate architectural changes to improve the performance of an app that calculates a lot of individual values and stores each of these results as a row. The storage size for one of the usages is 80 GB. I'm used to a artificial key driven warehouse, and this utilizes a natural key, so I'm considering various approaches.

The table structure is like this.

   create table dbo.ItemTable
    Column14 [BIGINT]        not null
   ,Column15 [BIGINT]        not null
   ,Column1  [INT]           not null
   ,Column16 [NVARCHAR](max) not null
   ,Column17 [INT]           not null references AnotherTable1 ([Id])
   ,Column18 [INT]           not null
   ,Column19 [INT]           not null references AnotherTable2 (Id) default 0
   ,Column20 [NVARCHAR](50)  null
   ,constraint [PK_AccountItems] primary key clustered
    Column14 asc,
    Column15 asc,
    Column1 asc
    on Column21 textimage_on Column21


create table dbo.ItemValues
   ,Column15 [BIGINT]   not null
   ,Column16 [INT]      not null
   ,Column17 [TINYINT]  not null
   ,Column18 [DATE]     not null
   ,Column19 [SMALLINT] not null
   ,Column20 [SMALLINT] not null
   ,Column21 [FLOAT]    not null
   ,Column22 [FLOAT]    not null
   ,constraint [PK_AccountItemValues] primary key clustered
    Column14 asc,
    Column15 asc,
    Column16 asc,
    Column17 asc,
    Column18 asc
    on Column23


The key issues I see:

  1. Bigint datatype. These columns are approx 10GB in storage cost alone. They could be reduced to INT and save a lot of space
  2. Float takes up additional storage when we might only need to use a 5-6 decimal precision level. I'm thinking to identify our max requirement here and then switch to decimal if possible to reduce size of the float columns.
  3. Column14 on ItemTableValues is purely for clustering the related Column15 together in close promoximity. This was done before I got here, since all Column15 rows are "grouped" when queried. Not sure that the cost of having this column purely for clustering records together offsets the cost of maintaining and storing.
  4. Refactoring the key and table to provide a narrower table with artificial key and a separate mapping to these keys.

Any other thoughts ideas? This has a huge performance impact when dealing with millions of rows being inserted, queried, and then deleted. I'd like to see if I could optimize to reduce the IO thrashing this causes.

Some of the indications of possible issues: (dmv's 25 days since last reboot)

  1. ItemValues: In Top 12 of indexes with most latch contention. Page latch wait in ms = 45,635, with wait count 247
  2. ItemValues: 2nd place of indexes with most lock promotion count - 953 instances
  3. ItemTable 2nd place for index under most row locking pressure, with 405,759 lock wait ms in last 3 weeks
  4. ItemValues table #1 for most page io latch contention, with 3188168 count and 810432461 ms in last 3 weeks.
  5. ItemValues #3 for most page splits with 68528 leaf allocation count and 42,594 nonleaf allocation count.

Usage patterns on this table

  1. Insert & Delete upon the core AccountCalculations being updated. At this time, it completely deletes and then reinserts the data with no difference comparison between the two. I have a goal to get this difference working, but it requires some big app changes.
  2. At one time the insert/delete could range from 8000 rows to 36,000 rows. This is a full insert/delete. (I will be examining a first step of using merge to try and reduce the activity on this)
  3. A significant number of reports query these details. All the accounts pulled are typically from the same Column14 grouping, and a child to Column15 (Account). All 8000-3600 rows may end up needing to be loaded by the application for calculations/display.
  • It would be helpful if you can show a few of the key queries that are hitting this table and describe more about the usage pattern; how frequently are rows inserted? How many rows are inserted at once? How long do rows remain in the table before deletion? Do all operations take place within one distinct value of Column14? Is there a large reporting query that operates on a large swath of the rows in this table? What does it look like? Etc. Jul 14, 2015 at 19:28
  • @gpatterson i updated with detail on how it is being used. This is a core table to the application and Entity Framework might be generating some of the selects. Otherwise, various other reports all pull every result for this Column15 to feed into the app. Jul 15, 2015 at 15:35


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.