Performance issue and keeping old data
-
Hello,
We have a database for one of our website (10 years old), for which we still keep orders.
Our orders table has grown to 12+ million records, growing at 5000 commands per day.
in the back office, we are facing performance issues, when searching for orders, this also impacts the front office.
the question is :
How performant can a 3 parameter query can get on a 12 million records table (max we get is 8 seconds)?
We have a between for dates and two status fields (int). All is indexed.
What process is used to keep historical database and start from a fresh two years max ?
Or do we purge old orders, periodically every 2 years or so ?
Any suggestion would be very helpful.
thank you.
Kind Regards,
O.D.
Oussama Dinia
.NET Solutions Architect
http://devmeetsbi.ghost.io/
http://www.devmeetsbi.io/catalog/3079 -
Have you checked the execution plans of the 8sec queries? Does that index get used in scan or seek operation (if at all used)? What are the fragmentation levels of the index and are the statistics regularly updated?
As this is a 2008 forum im assuming you're running a SQL 2008 installation. That being the case; have you done any experiments/tests with table partitioning?
The "between for dates" could be causing parameter sniffing (if the ranges are changes dramatically). You may want to check that out.
-
Thank you for your reply,
>> Have you checked the execution plans of the 8sec queries?
Not in great detail (we have 3 joins in the query in question).
>>Does that index get used in scan or seek operation (if at all used)?
we will check that.
>>What are the fragmentation levels of the index and are the statistics?
Do not know how to check that. will look into it, and get back to you.
>> have you done any experiments/tests with table partitioning?
No
>> The "between for dates" could be causing parameter sniffing (if the ranges are changes dramatically)
The dates are usually the current date and the date-1.
A lot of different points. Being so focused on web development of the platfrom, we actually do not realize how many SQL Server techniques we have to pay attention too
Oussama Dinia
.NET Solutions Architect
http://devmeetsbi.ghost.io/
http://www.devmeetsbi.io/catalog/3079 -
oussama.dinia (7/12/2011)
We have a database for one of our website (10 years old), for which we still keep orders.Our orders table has grown to 12+ million records, growing at 5000 commands per day.
in the back office, we are facing performance issues, when searching for orders, this also impacts the front office.
the question is :
How performant can a 3 parameter query can get on a 12 million records table (max we get is 8 seconds)?
We have a between for dates and two status fields (int). All is indexed.
What process is used to keep historical database and start from a fresh two years max ?
Or do we purge old orders, periodically every 2 years or so ?
Any suggestion would be very helpful.
This appears to be an OLTP system that is also being used as Reporting - otherwise I can see no reason to keep ten years history online. Having said that, never a good idea to force an OLTP system to serve as a reporting resource - that's why we have data warehouses.
In regards to how long of a history has to be kept online, just check business requirements - business know for how long those records have to be kept online.
12M rows is not a large table, having the right indexind strategy in place retrieving a particular row should be a subsecond query - please follow previous post suggestion and: a) check execution plan, b) double-check indexing strategy.
No doubt an archive and purge process is needed. I would say the standard approach is to archive and purge in a monthly basis meaning, if business requirement is to keep 24 month worth of data online each month you archive and purge the oldest month,, in this case month #25.
Last but not least, if you do not have yet a DSS system in place like a data warehouse I would suggest to archive everything you purge from Orders table into an ODS table, call it ODS_ORDERS - believe me, over time you will need that data to populate your data warehouse.
_____________________________________
Pablo (Paul) BerzukovAuthor of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice. -
Thank you very much, for your reply.
This gives us more to think about, in matter of solutions.
For the execution plan, when we check, this is what it looks like
Here's a screenshot of the execution plan, if you have anything is easy to notice.
some translation 🙂 of the words : Trier (37%) = Sort, boucles imbriqués = nested loops
Recherche de clé = search for key
This is the procedure concerned.
ALTER PROCEDURE [dbo].[Order_GetOrdersByDate]@dateFirst smalldatetime,
@dateLast smalldatetime,
@parentretailshopid tinyint
AS
SET NOCOUNT ON
SELECT Order_Id, Customer_Id, OrderDate, Orders.Amount as 'Amount', Orders.IsEmailSent, PaymentStatus, DeliveryStatus, ISNULL(SUM(DISTINCT PaymentMode), 0) as PaymentMode, Login, FirstName, LastName
FROM Orders
INNER JOIN Customers ON Customer_Id = FK_Customer
LEFT OUTER JOIN dbo.OrdersTransactions ON FK_Order = Order_Id
LEFT OUTER JOIN dbo.Transactions ON Transaction_Id = FK_Transaction
inner join RetailShops on Orders.FK_RetailShop = RetailShops.RetailShop_Id and (FK_ParentRetailShop = @parentretailshopid)
WHERE OrderDate BETWEEN @dateFirst AND @dateLast AND (PaymentStatus = 2 OR PaymentStatus = 4)
GROUP BY Order_Id, Customer_Id, OrderDate, Orders.Amount, Orders.IsEmailSent, PaymentStatus, DeliveryStatus, Login, FirstName, LastName
ORDER By Order_Id DESC
SET NOCOUNT OFF
Please do not feel obligated to look into it in detail, just at a glance, if you can notice anything alarming.
We are following your guidelines, and checking which solution will work best.
thanks
Oussama Dinia
.NET Solutions Architect
http://devmeetsbi.ghost.io/
http://www.devmeetsbi.io/catalog/3079 -
I can't see anything too worrying in that exec plan, apart from maybe your Key Lookup and Ordering. Is this definately the plan for that SP? I was perhaps expecting to see a [stream aggregate] operator?
KeyLookup - You can probably remove this by adding the missing fields to your index (depending on what those fields are - look at included columns). Altering / Creating an index on a 12m+ table will need to be done during your maintenance window.
Ordering - This is always expensive. Can it be done outside of the procedure? We get our devs to try and do this as much as possible in the .net data grid on the aplication side. Ordering happens inside of tempdb. Is your tempdb on a different physical disk to your data files (which should be on a different disk to your log files)?
Do you have maintenance jobs for reorganising / rebuilding index and updating statistics? If not, i'd set one up. There are some great example scripts on SSC for doing this kind of thing.
If your only interested in the previous days data, archive into another table / database (as suggested). As said earlier, 12m+ in SQL terms is nothing spectacular, but you'd certainly see an increase in performance if 95% of that was archived out of that table.
-
Thank you.
This assures us the problem is not as major as we though it could be.
we will create an action plan, and wait for our manager to get started.
Thanks again, this was very informative. Cleared out a few concepts and techniques.
Kind Regards
Oussama Dinia
.NET Solutions Architect
http://devmeetsbi.ghost.io/
http://www.devmeetsbi.io/catalog/3079 -
get rid of unnecessary data. index btree depth could be reduced 1 or even 2 levels, saving 8-16KB page IO for EVERY INDEX SEEK. Believe you me that will add up to some signficant savings!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply