Sql-server – Slow Performance Inserting Few Rows Into Huge Table

performancequery-performancesql server

We have a process that takes data from stores and updates a company-wide inventory table. This table has rows for every store by date and by item. At customers with many stores, this table can get very large– on the order of 500 million rows.

This inventory update process typically gets run many times a day as the stores enter data. These runs update data from only a few stores. However, the customers can also run this to update, say, all stores in the past 30 days. In this case, the process spins up 10 threads and updates each store's inventory in a separate thread.

The customer is complaining that the process is taking a long time. I have profiled the process and found that one query that INSERTs into this table is consuming much more time than I expected. This INSERT sometimes completes in 30 seconds.

When I run an ad-hoc SQL INSERT command against this table (bounded by BEGIN TRAN and ROLLBACK), the ad-hoc SQL completes on the order of milliseconds.

The slow performing query is below. The idea is to INSERT records that aren't there and later to UPDATE them as we calculate various bits of data. A prior step in the process has identified the items that need to be updated, done some calculations, and stuffed the results into the tempdb table Update_Item_Work. This process is running in 10 separate threads, and each thread has its own GUID in Update_Item_Work.

INSERT INTO Inventory
(
    Inv_Site_Key,
    Inv_Item_Key,
    Inv_Date,
    Inv_BusEnt_ID,
    Inv_End_WtAvg_Cost
)
SELECT DISTINCT
    UpdItemWrk_Site_Key,
    UpdItemWrk_Item_Key,
    UpdItemWrk_Date,
    UpdItemWrk_BusEnt_ID,
    (CASE UpdItemWrk_Set_WtAvg_Cost WHEN 1 THEN UpdItemWrk_WtAvg_Cost ELSE 0 END)
FROM tempdb..Update_Item_Work (NOLOCK)
WHERE UpdItemWrk_GUID = @GUID
AND NOT EXISTS
    -- Only insert for site/item/date combinations that don't exist
    (SELECT *
    FROM Inventory (NOLOCK)
    WHERE Inv_Site_Key = UpdItemWrk_Site_Key
    AND Inv_Item_Key = UpdItemWrk_Item_Key
    AND Inv_Date = UpdItemWrk_Date)

The Inventory table has 42 columns, most of which track quantities and counts for various inventory adjustments. sys.dm_db_index_physical_stats says each row is about 242 bytes, so I expect about 33 rows will fit on a single 8 KB page.

The table is clustered on the unique constraint (Inv_Site_Key, Inv_Item_Key, Inv_Date). All keys are DECIMAL(15,0), and the date is SMALLDATETIME. There is an IDENTITY primary key (nonclustered) and 4 other indexes. All the indexes and the clustered constraint are defined with an explicit (FILLFACTOR = 90, PAD_INDEX = ON).

I looked in the log file to count page splits. I measured about 1,027 splits on the clustered index and 1,724 splits on another index, but I didn't record over what interval those occurred. One and a half hours later, I measured 7,035 page splits on the clustered index.

The query plan I captured in the profiler looks like this:

Rows         Executes     StmtText                                                                                                                                             
----         --------     --------                                                                                                                                             
490          1            Sequence                                                                                                                                             
0            1              |--Index Update
0            1              |    |--Collapse
0            1              |         |--Sort
0            1              |              |--Filter
996          1              |                   |--Table Spool                                                                                                                 
996          1              |                        |--Split                                                                                                                  
498          1              |                             |--Assert
0            0              |                                  |--Compute Scalar
498          1              |                                       |--Clustered Index Update(UK_Inventory)
498          1              |                                            |--Compute Scalar
0            0              |                                                 |--Compute Scalar
0            0              |                                                      |--Compute Scalar
498          1              |                                                           |--Compute Scalar
498          1              |                                                                |--Top
498          1              |                                                                     |--Nested Loops
498          1              |                                                                          |--Stream Aggregate
0            0              |                                                                          |    |--Compute Scalar
498          1              |                                                                          |         |--Clustered Index Seek(tempdb..Update_Item_Work)
498          498            |                                                                          |--Clustered Index Seek(Inventory)
0            1              |--Index Update(UX_Inv_Exceptions_Date_Site_Item)
0            1              |    |--Collapse
0            1              |         |--Sort
0            1              |              |--Filter
996          1              |                   |--Table Spool
490          1              |--Index Update(UX_Inv_Date_Site_Item)
490          1                   |--Collapse
980          1                        |--Sort
980          1                             |--Filter
996          1                                  |--Table Spool                                                                                       

Looking at queries vs. various dmv's, I see the query is waiting on PAGEIOLATCH_EX for a duration of 0 on a page in this Inventory table. I do not see any waits or blocking on locks.

This machine has about 32 GB of memory. It is running SQL Server 2005 Standard Edition, though they are upgrading soon to 2008 R2 Enterprise Edition. I don't have numbers on how large the inventory table is in terms of disk usage, but I can get that, if necessary. It is one of the largest tables in this system.

I ran a query against sys.dm_io_virtual_file_stats and saw average write waits against tempdb to be upwards of 1.1 seconds. The database in which this table is stored has average write waits of ~350 ms. But they only restart their server every 6 months or so, so I have no idea if this information is relevant. tempdb is spread over 4 different files They have 3 different files for the database that holds the Inventory table.

Why would this query take so long to INSERT a few rows when run with many different threads when a single INSERT is very fast?

— UPDATE —

Here are the latency numbers per drive including bytes read. As you can see, tempdb performance is questionable. The Inventory table is in in either PDICompany_252_01.mdf, PDICompany_252_01_Second.ndf, or PDICompany_252_01_Third.ndf.

ReadLatencyWriteLatencyLatencyAvgBPerRead AvgBPerWriteAvgBPerTransferDriveDB                     physical_name
         42        1112    623       62171       67654          65147R:   tempdb                 R:\Microsoft SQL Server\Tempdb\tempdev1.mdf
         38        1101    615       62122       67626          65109S:   tempdb                 S:\Microsoft SQL Server\Tempdb\tempdev2.ndf
         38        1101    615       62136       67639          65123T:   tempdb                 T:\Microsoft SQL Server\Tempdb\tempdev3.ndf
         38        1101    615       62140       67629          65119U:   tempdb                 U:\Microsoft SQL Server\Tempdb\tempdev4.ndf
         25         341     71       92767       53288          87009X:   PDICompany             X:\Program Files\PDI\Enterprise\Databases\PDICompany_Third.ndf
         26         339     71       90902       52507          85345X:   PDICompany             X:\Program Files\PDI\Enterprise\Databases\PDICompany_Second.ndf
         10         231     90       98544       60191          84618W:   PDICompany_FRx         W:\Program Files\PDI\Enterprise\Databases\PDICompany_FRx.mdf
         61         137     68        9120        9181           9125W:   model                  W:\Microsoft SQL Server\MSSQL.3\MSSQL\Data\modeldev.mdf
         36         113     97        9376        5663           6419V:   model                  V:\Microsoft SQL Server\Logs\modellog.ldf
         22          99     34       92233       52112          86304W:   PDICompany             W:\Program Files\PDI\Enterprise\Databases\PDICompany.mdf
          9          20     10       25188        9120          23538W:   master                 W:\Microsoft SQL Server\MSSQL.3\MSSQL\Data\master.mdf
         20          18     19       53419       10759          40850W:   msdb                   W:\Microsoft SQL Server\MSSQL.3\MSSQL\Data\MSDBData.mdf
         23          18     19      947956       58304         110123V:   PDICompany_FRx         V:\Program Files\PDI\Enterprise\Databases\PDICompany_FRx_1.ldf
         20          17     17      828123       55295         104730V:   PDICompany             V:\Program Files\PDI\Enterprise\Databases\PDICompany.ldf
          5          13     13       12308        4868           5129V:   master                 V:\Microsoft SQL Server\Logs\mastlog.ldf
         11          13     13       22233        7598           8513V:   PDIMaster              V:\Program Files\PDI\Enterprise\Databases\PDIMaster.ldf
         14          11     13       13846        9540          12598W:   PDIMaster              W:\Program Files\PDI\Enterprise\Databases\PDIMaster.mdf
         13          11     11       22350        1107           1110V:   msdb                   V:\Microsoft SQL Server\Logs\MSDBLog.ldf
         17           9      9      745437       11821          23249V:   PDIFoundation          V:\Program Files\PDI\Enterprise\Databases\PDIFoundation.ldf
         34           8     31       29490       33725          30031W:   PDIFoundation          W:\Program Files\PDI\Enterprise\Databases\PDIFoundation.mdf
          5           8      8       61560       61236          61237V:   tempdb                 V:\Microsoft SQL Server\Logs\templog.ldf
         13           6     11        8370       35087          16785W:   SAHost_Company01       W:\Program Files\PDI\Enterprise\Databases\SAHostCompany.mdf
          2           6      5       56235       33667          38911W:   SAHost_Company01       W:\Program Files\PDI\Enterprise\Databases\SAHost_Company_01_log.LDF

Best Answer

Looks like your clustered index page splits are going to be painful because the clustered index holds the actual data and this will need new pages to be allocated and the data moved to these. This is likely to cause page locking and thus blocking.

Remember also that your clustered index key is 21 bytes and this will need to be stored in all of your secondary indexes as a bookmark.

Have you considered making your primary key identity column your clustered index, not only will this reduce the size of your other indexes, it will also mean that you will reduce the number of page splits in your clustered index. It's worth a try if you can stomach rebuilding your indexes.