May 20, 2011 at 1:42 pm
I have a situation where a user runs a long query, then a job that calls a stored proc which truncates and inserts to a table that is used by the long query gets blocked by the long query.
The big problem is when several new queries are then blocked by the truncate/insert stored proc.
So a chain of events causes many queries to be blocked.
I started to read about concurrency and RLV. Didn't find anything I could use.
I don't want to make server/database-wide changes, but just want to reduce or eliminate the issue with this particular table that is often truncated/inserted.
Sql Server 2005.
Steve
May 20, 2011 at 3:23 pm
First of all a critique, the method you are using has a number of pitfalls including that periodically the table will be empty when the query is run..
Why are you using trunc/load for this? If it is a live table then the blocking would be desired since you don't want the data to disappear while the query is running.
So before we go too deep, why don't you tell us a little bit more about this process and then we can help you out.
CEWII
May 20, 2011 at 7:06 pm
The table is coming from another application on Oracle 10.2.
The rows can change and new rows can also be created. There's about 5,000 rows in this table.
We have a more elaborate interface to this database with other tables and data involved where we send records to them and they also send to us, but we do it through intermediate tables so as not to affect updates local to my database.
But for this particular data, the need to me to receive updates from this Oracle db was not identified until the end of the Development/test cycle.
So that's the issue.
I may try to duplicate the problem so I can capture the locks to help me solidify what's going on/what I suspect.
May 21, 2011 at 3:11 am
Try to eliminate the root cause (= the duration of the "long running query"). It might be an option to use the Divide'n'Conquer concept if this is a really complicated query.
Another question would be how the upload process from Oracle is designed. To load 5k of rows shouldn't even be noticed (in terms of duration). How often that reload process is started (frequency)? Is there a staging table concept to eliminate the data transfer time from the update process?
May 23, 2011 at 11:33 am
I continually inform the users (there is one main user who runs these long queries) to break down the queries, but assume that some queries are just going to take several minutes. This one needed 24m rows in tempdb and it eventually filss up the tempdb data and fails.
What I found is the truncate/insert is an issue. truncate does a sch-m (schema) level lock, so requires sole use of the table.
I changed it to delete/insert, which uses row-level locking.
I ran the long query, then ran the refresh before and after the change. Before, I captured the locks and could see sch-M lock mode waiting on the truncate.
After the change to delete, the delete/insert completes so quickly, there is no issue.
This is one case where delete is better than truncate.
May 23, 2011 at 12:55 pm
sgambale (5/23/2011)
I continually inform the users (there is one main user who runs these long queries) to break down the queries, but assume that some queries are just going to take several minutes. This one needed 24m rows in tempdb and it eventually filss up the tempdb data and fails.What I found is the truncate/insert is an issue. truncate does a sch-m (schema) level lock, so requires sole use of the table.
I changed it to delete/insert, which uses row-level locking.
I ran the long query, then ran the refresh before and after the change. Before, I captured the locks and could see sch-M lock mode waiting on the truncate.
After the change to delete, the delete/insert completes so quickly, there is no issue.
This is one case where delete is better than truncate.
There are a few scenarios where a query actually needs 24m rows in tempdb (as usual: it depends). But I would guess in the given scenario this is due to a missing index or poorly written query leading to SQL Server creating a worktable. If possible, post the actual execution plan together with the tables involved (including indexes of the tables). Never, ever, just "assume that some queries are just going to take several minutes"! Analyze it and either know it needs to take that long or try to tune it.
Regarding delete vs. truncate: if delete works almost instantly vs. truncate takes a while, then I would guess this table is rather frequently queried, so the truncate statement needs to wait until there is no other connection holding a lock on a single row.
May 23, 2011 at 1:48 pm
There are 5 or 6 tables in the query plus aggregation.
Most queries need this refresh table, so that's the main problem and why a delete/insert is needed vs. truncate/insert.
There are no full table scans on the tables except for this refresh table (about 4000 rows).
I'll see if I can put out the plan.
45 lines for the plan If I SET SHOWPLAN_ALL ON
Steve
May 23, 2011 at 2:04 pm
sgambale (5/23/2011)
There are 5 or 6 tables in the query plus aggregation.Most queries need this refresh table, so that's the main problem and why a delete/insert is needed vs. truncate/insert.
There are no full table scans on the tables except for this refresh table (about 4000 rows).
I'll see if I can put out the plan.
45 lines for the plan If I SET SHOWPLAN_ALL ON
Steve
Use the "Include Actual Execution Plan" button in Management Studio. Save the plan as .sqlplan file and attach it.
May 23, 2011 at 2:12 pm
Couple of ideas.
Instead of a permanent table, teach them to use a #tmp and pull the data over into that. 5k rows shouldn't be that painful.
Use schema for this table. It's kind of an odd use for a 'permanent' table to be constantly resetting itself anyway, so using user-level schemas for the table may help here. This of course relies on not using an AD group login and manipulating it at the user level.
Do table swaps. Rebuild the table as a second name (table_notCurrent) or something and swap the tables with an sp_rename (will require a Sch-M lock, but MUCH faster on the swap time).
It's an odd use for a permanent table. You're going to have to come up with an odd solution, or use larger table methodologies on this tiny one, which is approaching it as a Delete/Update/Insert scenario off a staging structure.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 24, 2011 at 6:44 am
The query does not run in prod as I only have 10 GB of tempdb space.
On dev/test we were able to get about 80 GB of tempdb on a temporary basis.
It did complete. The table data is almost exactly the same. Here is the plan.
May 24, 2011 at 6:47 am
first attachment does not appear to work.
I zipped sqlplan and attached it here.
May 24, 2011 at 8:26 am
Craig,
Thanks for some good ideas.
The #tmp table seems good. Create it from the table that is refreshed often and use that.
I know the idea of refreshing such a table that is used so often is not a great design, but it's based on a new app that uses Oracle that has information that is maintained by it and not by my app.
So I have to get the latest info from Oracle. You should see what we had to do for some other data that needs to be shared by the two databases/apps in almost real-time. We have two-way sharing for certain data and we use triggers to insert rows into staging tables on the sql server side, then send the new rows to Oracle with jobs. The app on the Oracle side does something similar to keep our stuff updated. All this is supposed to actually be an improvement for the Company because it retired an old app that worked seamlessly with my SQL Server db, but this other app using Oracle is deployed elsewhere in the company and overall, it seems to be a benefit. For me, it's more prod support and more chance for errors and problems, but this interface has behaved nicely since deployment.
The table swap with sp_rename would be good if it took a long time to delete/insert, but it doesn't .
The sch-M lock mode is unacceptable and is the reason I changed from truncate/insert to delete/insert. Good idea to throw out, but does not work for me here.
May 24, 2011 at 11:42 am
The major issue is this rather large join is not supported at all by non-clustered indexes leading to a monster of 26m rows before any filter is applied (in between there are as many as 46m rows).
Here's what I think might help to get rid of all the clustered index scans, with the exception that I don't know the current index definition per table:
1)
Replace
OR ( (( SUBSTRING([prj_id_allocations].[BUDGET_ENTITY], 1, 2) ) = 'oi') )
with
OR ( [prj_id_allocations].[BUDGET_ENTITY] LIKE 'oi%')
Otherwise you'll always end up with a table scan since the query can't benefit from an index
2)
Add the following non-clustered indexes (warning: this may take a moment on that 20m table):
prj_id_information: PRJ_ID,PRJ_CODE INCLUDE()
prj_id_allocations: BUDGET_ENTITY,EXPCD_ID,PRJ_ID,YEAR, ALLOC_VER_ID INCLUDE()
CEC_BUDGET_ENTITIES: PROJ_ACCTG_ID, BUSINESS_UNIT_DETAIL_NAME INCLUDE()
prj_id_information_v and PRJ_ID_PRJ_STATUS_V seem to be views where the definition itself is unknown.
Side note: Expand the INCLUDE() part to hold the additional columns of the table used in the query.
Those would be my steps to start tuning the query.
I'm confident the query can be tuned to perform much better not using tempdb so heavily.
May 24, 2011 at 11:52 am
sgambale (5/24/2011)
The query does not run in prod as I only have 10 GB of tempdb space.On dev/test we were able to get about 80 GB of tempdb on a temporary basis.
It did complete. The table data is almost exactly the same. Here is the plan.
PRJ_ID_Allocations pulls back 22 million rows.
It turns itself into ~45 million rows when it hits the last hash join.
It then tries to aggregate on the group by. This is going to inhale your TempDB. Finally, after doing all of that, it filters due to the HAVING.
If you don't restrict your data in some way prior to the aggregation, it's going to need that kind of space. There's a lot of columns in the final.
An idea might be to two-stage this query if you can. I haven't fully broken out the logic, but usually when you run into something like this, tighten up the aggregation portion, drop to a #tmp, then add on the rest of the 'lookup' data after.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 24, 2011 at 2:57 pm
Thanks for the suggestions.
The first one using like 'xx%' instead of substring is something I understand and have known.
However, it did not help because there is no index that starts with budget_entity column, so it performed the same either way. But point is well taken.
I ram the query through the Tuning Advisor in dev.test db with no recommendations. I have found that it can offer useful recommendations, although it likes to do alot of include indexes and sometimes the space they need is more than I'm willing to accept because the query is not run often or it's ad hoc and there is little repeatability value in creating a big index.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply