Stored Procedure Running Slow via SSIS vs Management Studio
-
I've been having some pesky tuning problems with Stored Procedures running via DTEXEC and through BIDS.
When I run the stored proc (right click run task), it is slow, very slow. When I run the exec sproc_Name in Management Studio it runs in seconds. I've tried so many things
1. ending all the statements within the sproc with semicolons
2. dropping and recreating the sproc
3. adjusting different BIDS options like IsStoredProc (off and on)
I tuned each and every statement within the sproc, and when I run it in Management Studio, it is good. But in BIDS not so much.
Finally I just dump the t-sql code into the task and that seemed to work, but it eventually slowed down again.
But then when I ran the code by itself, it was ok.
The BIDS is on a different server than SQL Server, but I ran the Management Studio from the same server as BIDS.
When the sproc is running, I look at the Activity Monitor and I can see that the sproc/code is running. I don't see any blocks or wait statistics, so i'm at a loss of what can be causing it to run so slow.
I would think , once SQL Server got the command from SSIS, then it would run the same as if it had gotten the command from Management Studio.
Please help/advise.
thanks
-
Have you compared the query plans? I think that has to be the first step.
You can do this with Profiler.
Also, are you running it just once from SSMS, but multiple times from SSIS? It is possible for the query to slow down after multiple executions if the data distribution/volume changes dramatically between first and last executions.
-
How big is the result set?
How does the SSIS package operate on the result set as it is received (as a set or row by row)?
-
nawillia
We once had performance issues and was on a call with Microsoft on this.
I was told that SSMS is designed to execute queries faster and also it uses a single thread only.
We had queries completed in seconds using single threads when ran from SSMS but same queries when called from an application used multiple threads and got timed-out.
If multiple threads are being used when executed from BIDS, try testing MAXDOP.
The issue could also be with your connection manager/ drivers installed.
-
Thanks
I will try this out.
-
Richard Fryar (12/9/2013)
Have you compared the query plans? I think that has to be the first step.You can do this with Profiler.
Also, are you running it just once from SSMS, but multiple times from SSIS? It is possible for the query to slow down after multiple executions if the data distribution/volume changes dramatically between first and last executions.
I didn't have time to run Profiler and compare the query plans, but that is something I can try now that things seem to be running ok.
Also, the result set isn't extremely big, just 15-25k records depending on the run.
Generally the SSIS would loop and do multiple resultsets, loading a file, processing it, etc...
I would do a breakpoint and run the sproc multiple times in BIDS , via DTEXEC, and via SSMS.
I was thinking that most of the time, subsequent runs of the same data would run faster, but not the case.
The sproc does a truncate and then reloads the same data that is static in the database (until the entire package runs, the data remains static). So the fact that it would run fast in SSMS and then slow in BIDS is very baffling, thinking the data would at least be cached.
-
Abdul Rahman (12/11/2013)
nawilliaWe once had performance issues and was on a call with Microsoft on this.
I was told that SSMS is designed to execute queries faster and also it uses a single thread only.
We had queries completed in seconds using single threads when ran from SSMS but same queries when called from an application used multiple threads and got timed-out.
If multiple threads are being used when executed from BIDS, try testing MAXDOP.
The issue could also be with your connection manager/ drivers installed.
This doesn't make any sense. The database engine determines whether a query will be executed with a parallel plan, regardless of what application submits the query. With a stored procedure, an execution plan is cached the first time the proc is called and all subsequent calls of that proc reuse that same query plan. Adjusting the MAXDOP setting is only a solution in rare cases where a parallel plan is determined to be less efficient than a single-thread plan.
OP, first of all, are you sure that the queries are being executed against the same database instance (e.g., both SSMS and BIDS are hitting the development server)? BIDS and SSMS could be using different ANSI settings, which could result in different execution plans (a bad one for BIDS and a good one for SSMS). When you say the query is "slow", what exactly do you mean? Do you mean that your SSIS package takes a long time to execute? As somebody hinted above, this could happen because your SSIS package may not be able to consume the data as fast as SQL Server can send it. Is the proc experiencing any waits or blocking when it's called by BIDS? Are you running BIDS and SSMS on the same machine? There are a lot of things that could be going on here - we'll need a lot more details to help any more than that.
Jason Wolfkill
-
are you sure that the queries are being executed against the same database instance (e.g., both SSMS and BIDS are hitting the development server)?
>>yes, i'm watching the Activity Monitor for a specific Server
BIDS and SSMS could be using different ANSI settings, which could result in different execution plans (a bad one for BIDS and a good one for SSMS).
>>how could I check that?
When you say the query is "slow", what exactly do you mean? Do you mean that your SSIS package takes a long time to execute?
>>yes, all the package task does is 'exec sproc'.
When it is run in BIDS, I go to activity monitor and the spid is there with the command for minutes (10+) with no blocks or waits that is showing up. When run in SSMS it takes maybe 45 seconds at most.
As somebody hinted above, this could happen because your SSIS package may not be able to consume the data as fast as SQL Server can send it. Is the proc experiencing any waits or blocking when it's called by BIDS? Are you running BIDS and SSMS on the same machine?
>>they are not on the same server, but the sproc only truncates and reloads a table in SQL Server, so I don't know why it would take so long to run that sproc once the command is sent to SQL Server.
There are a lot of things that could be going on here - we'll need a lot more details to help any more than that.
>>I'm running lots of troubleshooting, and the last thing I did was just take the commands out the sproc to run the t-sql inline.
thanks for the reponse
-
nawillia (12/13/2013)
BIDS and SSMS could be using different ANSI settings, which could result in different execution plans (a bad one for BIDS and a good one for SSMS).
>>how could I check that?
If you know the SPID of a session, you can query sys.dm_exec_sessions - there are columns in that view that show you the ANSI settings for the session. You could run the package in BIDS and check the SPID for that session, then execute the proc in SSMS and check the SPID for that session.
The DBCC USEROPTIONS command will tell you what the ANSI settings are for the current connection.
In SSMS (I'm looking at 2012, since that's what I use), click Query > Query Options > Execution > ANSI to see and change the defaults used for sessions opened by SSMS.
In VS2010 w/BIDS (again, that's what I use), click Tools > Options > SQL Server Tools > Transact-SQL Editor > Query Execution > ANSI to see and change the defaults (for queries executed from a query window, and, I think, packages running in BIDS).
I don't know off the top of my head whether an SSIS package uses the default ANSI settings of the DB server when it runs on the SSIS instance or whether you can configure those in the SSIS instance.
Jason Wolfkill
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply