Performance issues with a large table in SQL Server 2005

5 pts.
Tags:
SQL Server 2005 performance
SQL server 2005 performance tuning
SQL Server query optimization
I have an application that is managing inventory for a company that has multiple locations. Items are being inserted into an inventory table roughly every 5-6 seconds from three locations. At the same time, this company is packing inventory in boxes in order to fulfill orders. I have a table setup for this in which inserts are made for an inventory id and a box id (tied to a purchase order table) to show when an item has been packed and is no longer in inventory. Roughly 6000 items are being added per day into inventory as well as being shipped out of inventory. Before an item is being shipped - I have to first check the inventory table to make sure the item exists in inventory and also need to check the shipping table to make sure the item hasn't been shipped. I'm also checking these tables on the sales side to verify when an order has been completed. These two tables are being hit every few seconds both with reads and writes. I'm running into some performance issues as the size of my table is growing. I've been researching the problem and have a couple of ideas on how to fix this - but wanted to check here first to see if I'm on the right track. 1. Indexing this table in order to get better performance during the lookups. From what I've read, this will improve the select queries - but will slow down the inserts since the table will be re-indexed after each insert. Since I'm inserting into these tables every couple of seconds - I'm wondering if having indexes on these tables might degrade performance instead of improving it. 2. The queries are being performed via my application. Ie, a query checks to make sure the item is active in inventory - results are returned to the application and either an error is thrown or an insert statement is re-submitted to the SQL server. By moving this into a stored procedure - I'll remove the network traffic associated with going back and forth between the application server and the sql server. 3. I've read a little about partioning and it sounds like this might help (ie, partioning on each of the location id's) - but I have not gone into it enough to really know enough about it. Hopefully my question was clear. Any advice or suggestions are appreciated.
ASKED: May 17, 2009  12:11 AM
UPDATED: October 23, 2013  2:27 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Adding 6000 records per day isn’t all that much. You are only looking at ~2.2M records per year.

  1. When you add data the index isn’t rebuild. The new data is simply inserted into the correct data page of the index, and if needed SQL does a page split. When building your indexes pay attention to the fill factor that you use. This tells the SQL Server how much free space to allow for in each data page so that you can minimize page splits.
  2. Yes, this will also allow the SQL Server to make better use of cached execution plans which will help reduce your CPU load.
  3. Partitioning requires SQL 2005 Enterprise Edition or SQL 2008 Enterprise Edition. Based on the amount of data that you have in the table (2.2M records per year even after 10 years of data is only 22M records) isn’t enough data to really get the benefit from table partitioning. You’ll really see the benefit when you get into the hundreds of millions and billions of rows per table range.

Based on what you have said you need to add some indexes to the tables. Look at the execution plans and see where the SQL Server is using table and index scans. This can be reduced or eliminated by adding proper indexes.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following