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.