June 30, 2009 at 3:10 am
Hi,
I understand users can configure partitioning by date for example but one of my biggest problems is INSERTS and READS at the same time. For example:
---------------------------------------------------------------
TableA = 150M records
50 Inserts a second
Typical user query:
@DateFrom='Jun 1 2009 12:00:00:000AM',
@DateTo='Jun 24 2009 12:00:00:000AM',
88,861,413 reads
1,003,906 ms
Takes 16 mins to run , during which my inserts grind to a hault as the tableA locks up for reads.
---------------------------------------------------------------
We are tackling this by:
1. scheduling reports out of hours
2. limiting date ranges
3. splitting the tableA to a second disk array using ARCHIVE TBLE or partitioning (to be decided)
(assume 2 arrays for the following)
A. Using partitioning Im guessing I could split the table by date each YEAR (Array1 current year, array 2 archive years). Under this method however the above query would be quicker but crucially it would still lock the table preventing inserts.
B. Using partitioning Im guessing I could split the table by date each MONTH (array 1 current month, array 2 archive months). Under this method however the above query would be quicker but I would be left we creating a hell of a lot of partitions.
C. Using a DAILY table (array 1) and a host of yearly archive tables (array 2) using SSIS to populate archive tables overnight the daily inserts would remain fast to the daily table and read quires on the daily table would be tiny and very quick (i.e archive tables would in practice be read only , only SSIS package would insert data). The downside is our report data access layer need re-written to copy with multiple tables.
I understand without knowing our app these calls are hard to make. INSERT times are very important to us. The flow of incoming data greatly exceeds the amount of users querying that data.
Thank you for any comments.
Scott
July 1, 2009 at 1:42 pm
You may want to look at SNAP SHOT ISOLATION on your database. Enabling this feature in SQL Server 2005 should help you keep Readers from blocking Writers.
July 1, 2009 at 2:30 pm
July 1, 2009 at 3:53 pm
If you are looking for a partition scheme you could use a hybrid of what you were proposing. Previous years either in a single partition, or in a yearly partition. Current month in its own partition and all months before for the year in another partition, it is some work to make this go but it would provide a finer granularity..
CEWII
July 1, 2009 at 4:30 pm
Richard M (7/1/2009)
... or, besides what Lynn mentions, you can go granular adding the NOLOCK hint to your queries, if that is possible that is.
I'd be wary of using the NOLOCK. This can result in dirty reads; missing or duplicate records. This could result in bad reports for your users.
July 1, 2009 at 4:45 pm
I agree with Lynn on the NOLOCK hint. Too many places have mandated the NOLOCK on all Select statements. This is one that should have a proceed with caution label.
SnapShot Isolation is a good possible solution.
With the partitioning, have you considered the readonly filegroup option for your older partitions (in conjunction with what Elliott suggested)?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 2, 2009 at 6:05 am
Thanks for the reply. Been chatting to a SQL contact at an ISP and he also mentioend NOLOCK (see below). taking into account what everyone has said in this forum i think i may give NOLOCKs a miss. Sounds like it would cause more problems that it would solve.
"In addition if you check the size in terms of MB/GB of the table , you could add a query hint to lock the whole table in ram making the select statements not have to touch the hard drives.... However if you are doing that many updates the sql engine may put an exclusive lock on that table. A way around this is to use a locking hint such as NOLOCK in the select statement. THis will cause the select query to ignore the exclusive lock and return the data without waiting for the update transaction to complete. Please be aware here that this may present dirty data in the result set if the data being viewed is the sames data that is being updated. An alternative and less performant way is to use rowlock in the update statement - this will prevent a table lock from being issued."
1. Regardaing the SNAPSHOT comment. I understand how SNAPSHOTS can be used with replication , i dont understand how SNAPSHOT ISOLATION mode works. Can you please explain ?
2. Back to an original point, enterprise table partiioning verus using standard edition, creating a new table on a new filegroup (new disk array), and making our app aware it must query DAILY table and ARCHIVE table , and using SSIS overnight to pull daily data to archive. if SSIS fails the query would need to understand the end of date point in the daily table to know where to look.
Has anyone attempted anything simaler to help speed up INSERT attempt into DAILY table ? does it sould like a plausable solution.
Thanks for the posts
Scott
July 2, 2009 at 7:25 am
scott_lotus (7/2/2009)
Thanks for the reply. Been chatting to a SQL contact at an ISP and he also mentioend NOLOCK (see below). taking into account what everyone has said in this forum i think i may give NOLOCKs a miss. Sounds like it would cause more problems that it would solve."In addition if you check the size in terms of MB/GB of the table , you could add a query hint to lock the whole table in ram making the select statements not have to touch the hard drives.... However if you are doing that many updates the sql engine may put an exclusive lock on that table. A way around this is to use a locking hint such as NOLOCK in the select statement. THis will cause the select query to ignore the exclusive lock and return the data without waiting for the update transaction to complete. Please be aware here that this may present dirty data in the result set if the data being viewed is the sames data that is being updated. An alternative and less performant way is to use rowlock in the update statement - this will prevent a table lock from being issued."
1. Regardaing the SNAPSHOT comment. I understand how SNAPSHOTS can be used with replication , i dont understand how SNAPSHOT ISOLATION mode works. Can you please explain ?
2. Back to an original point, enterprise table partiioning verus using standard edition, creating a new table on a new filegroup (new disk array), and making our app aware it must query DAILY table and ARCHIVE table , and using SSIS overnight to pull daily data to archive. if SSIS fails the query would need to understand the end of date point in the daily table to know where to look.
Has anyone attempted anything simaler to help speed up INSERT attempt into DAILY table ? does it sould like a plausable solution.
Thanks for the posts
Scott
Trying to explain SNAPSHOT ISOLATION here would take up too much space and time for a forum. I suggest that you start by reading about it in BOL (Books Online). There is plenty of information there to get you started and if you have specific questions or need clarification that we can provide easier here on line.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply