Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
Could it be that threads compete on updating the indexes and cause the index to lock, whereas in the case of a single thread that does not happen of course? –  Emmad Kareem Jun 29 '12 at 21:42
    
If so, would I see it as waiting on on locks? I am not seeing any blocking or waiting on locks. –  Paul Williams Jun 29 '12 at 21:45
    
Can you capture and make available the XML version of the actual (not estimated) Query Plan? It has a lot more information than the text version. –  RBarryYoung Jun 30 '12 at 14:59
3  
Also, FYI, "(NoLock)" is a No-Op on any DML. –  RBarryYoung Jun 30 '12 at 14:59
    
Also, avg write waits of 350ms are pretty bad. Avg write waits of 1,110ms are truly awful, unless your bytes per read/write are very large (see here for how to get these: sqlskills.com/BLOGS/PAUL/post/…) –  RBarryYoung Jun 30 '12 at 15:23
show 11 more comments

2 Answers

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.

share|improve this answer
add comment

With the multi-threaded approach, I'm wary of the insert into a table from which you must first check prior existence of a key. That sort of says to me that there is a concurrency issue on that PK index to that table no matter how many threads there are. For same reason, I don't like the NOLOCK hint on the inventory table because it seems like an error would occur if different threads are able to write the same key (does the partitioning scheme remove that possibility?). I'm curious how big the speedup was on the initial introduction of multiple threads because it must have worked well at some point.

Something to try is to make the query read more like a bulk operation and convert the "where not exists" into an "anti-join." (ultimately the optimizer may choose to ignore this effort). As mentioned above I'd remove the NOLOCK hint on the destination table unless perhaps the partitioning has guaranteed no key collisions between threads.

 INSERT INTO i (...)
 SELECT DISTINCT ...             
   FROM tempdb..Update_Item_Work t (NOLOCK) -- nolock okay on read table
   left join Inventory i -- use without NOLOCK because PK is written inter-thread
     on i.Inv_Site_Key = t.UpdItemWrk_Site_Key
    and i.Inv_Item_Key = t.UpdItemWrk_Item_Key
    and i.Inv_Date = t.UpdItemWrk_Date
  where i.Inv_Site_Key is null   -- where not exist in inventory
    and UpdItemWrk_GUID = @GUID  -- for this thread

Timing that run as a base, you might re-run with the merge hint ("left join" --> "left merge join") as another possibility. You probably should have an index on the temp table(UpdItemWrk_Site_Key,UpdItemWrk_Item_Key,UpdItemWrk_Date) for the merge hint.

I don't know whether or not the newer non-express versions of SQL Server 2008/2012 would be able to automatically parallel'ise larger merges of this form allowing you to remove the GUID-based partitioning.

To encourage the join to only occur on the distinct items rather than all items, the "select distinct ... from ..." clauses can be converted into "select * from (select distinct ... from ...)" before continuing with the join. This might only make a noticeable difference if the distinct is filtering many rows. Again the optimizer may ignore this effort.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.