June 3, 2011 at 2:16 pm
It could be due to parameter sniffing. Check if the plans generated are different when running from your package and SSMS.
When running from your package, are you using different datatype for parameters than what is specified on the table?
-Roy
June 3, 2011 at 2:33 pm
The queries don't have parameters.
One of the queries did have two CTEs. When we rewrote the query using derived tables instead of the CTEs we didn't get the CXPACKET waits. The execution plan for both versions of the query though were identical.
While not exactly the same query, we do use the CTE "version" of the problem query in several other data flows in the package and they all work run the same in SSIS and SSMS.
June 3, 2011 at 2:43 pm
If you haven't already, double check that your connections in the SSIS package, and your configurations, are pointing where you think they are. May just be a typo.
Next step to determining the issue would be to trace the server in question and pull the full XML plan when the SSIS package runs that process and see where the difference lies.
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
June 3, 2011 at 2:46 pm
I am not sure why when running on SSMS it does not show any trace of parallelism. Do you have hyper threading enabled?
You could try to band-aid of using a MAXDOP hint of 1 to get rid of parallelism.
Just out of curiosity, what is the size of the DB and how much RAM do you have? And your disks, are the RAID config or SAN? If RAID, what is the RAID config?
-Roy
June 8, 2011 at 6:20 am
Are these SQL tasks or dataflows?
if the latter, are the queries in the DF source or have You said "use table / view" option? If that, then your problem might lie there.
When using a table or view as a source (without saying Select col1, col2 etc) SSIS issues a SET ROWCOUNT 1 to get the definition back.
On occasion, SQL will cache this query plan and try use it when you run the real query.
it's bad practice to use a table or a view as a source.
I've had it bite badly in the past.
if you not using DFs then.... :hehe:
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 8, 2011 at 8:18 am
We are using data flows where the source is a SQL command. The queries have been gone over with several times to make sure that they using the right indexes and are as tight as possible. Despite all of this when SSIS gets a hold of the queries all that effort goes out the window.
A new wrinkle is that when we run the package through the package execution utility the CPU goes to 100% and the server locks up. Running the same package as a job and everything works fine.
June 8, 2011 at 8:30 am
is there any "dynamic" sql in the query? Can you change any parameters to hardcoded values as a test
Assuming you've confirmed the queries are the same through profiler...
Run DBCC FREEPROCCACHE (understand it if you going to do it on a prod server!!!)
How many rows are you expecting the query to return? if SQL is rendering the data but SSIS is struggling to consume it into buffers, could this be a problem? is there a memory contention? Are we talking 100s of rows, 100k's or millions?
if on SQL you run your query into a temp table, how long does it take to complete?
Are you able to run the package on a different machine which would divorce SSIS / SQL and stop any memory issues.
what's the difference in spec between the test server and prod? it's not unheard of for a lower spec machine to be quicker than a higher spec one as SQL can come up with a better plan on the lower spec and decide to brute-force it on the higher spec.
You say the plans are identical? Does that mean they both bad or both good? Any large hash joins thrashing the disks?
thinking out loud... :w00t:
final thought - what's your data flow like? Lots of ASYNC components?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
Viewing 7 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply