Monday, March 19, 2012
Conditional timeout issue when executing SP
with SQL 2005 SP2 and the execution of a single, specific stored
procedure. The stored procedure executes in under 1 second and uses
all appropriate indexes when it's run from the SQL Enterprise Manager,
but when the query is executed as part of the loading process of the
website it's used in, the data doesn't load and a SQL timeout error is
encountered. In both circumstances, the query originates from the same
machine.
In the website, the procedure that is timing out isn't the first query
in the loading process to access that database or set of tables. It
seems that the problem is just something with that query itself. There
really isn't anything too out of the ordinary about the query either;
it's just a single SELECT statement with a few joins, two subqueries
(one of which contains a subquery of its own) in the SELECT and one
subquery in the WHERE, and
I tried restarting IIS on the machine just for kicks, but it doesn't
solve the problem. I also tried copying the website application to
another machine, but I still receive the same timeout error on the
same query even when the query continues to execute just fine from the
Enterprise Manager environment on the first machine. To make matters
even more puzzling, the query / stored procedure _usually_ loads
without fuss when I pass in a different value for the single parameter
it takes; sometimes though, the procedure never loads within the
website application even when I try all of the different reasonable
values for that parameter.
The only way to fix the problem that I've found is to either restart
the SQL service or change the number of threads that the service is
using (which seems to issue a "soft restart" of sorts to SQL itself).
Once restarted, the website application loads fine and runs fine for a
few days. However, once a few days have passed, the SQL server again
starts giving timeouts for that query. The timeout errors that _do_
occur at this point are usually given when the single parameter is
different that the last parameter that gave the timeout errors before
the last time the server was restarted. In all reality, everything
just seems so random, and so it's hard to pin down any more details
for sure.
Like I said, this issue has only popped up in the past week or so and
had been running fine for the 6+ months previous. Has anyone else
encountered this issue before or otherwise have any suggestions for
how I can fix the problem? I'd really appreciate anything at this
point because I'm running out of ideas.My guess is that you have parameter sniffing issues. I suggest you spend an hour or two with below.
It is worth your time.
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"amattie" <amattie@.gmail.com> wrote in message
news:57616074-99f3-4c49-9b49-babbddfe11d9@.e23g2000prf.googlegroups.com...
> In the past week, I've been running into a very unusual timeout issue
> with SQL 2005 SP2 and the execution of a single, specific stored
> procedure. The stored procedure executes in under 1 second and uses
> all appropriate indexes when it's run from the SQL Enterprise Manager,
> but when the query is executed as part of the loading process of the
> website it's used in, the data doesn't load and a SQL timeout error is
> encountered. In both circumstances, the query originates from the same
> machine.
> In the website, the procedure that is timing out isn't the first query
> in the loading process to access that database or set of tables. It
> seems that the problem is just something with that query itself. There
> really isn't anything too out of the ordinary about the query either;
> it's just a single SELECT statement with a few joins, two subqueries
> (one of which contains a subquery of its own) in the SELECT and one
> subquery in the WHERE, and
> I tried restarting IIS on the machine just for kicks, but it doesn't
> solve the problem. I also tried copying the website application to
> another machine, but I still receive the same timeout error on the
> same query even when the query continues to execute just fine from the
> Enterprise Manager environment on the first machine. To make matters
> even more puzzling, the query / stored procedure _usually_ loads
> without fuss when I pass in a different value for the single parameter
> it takes; sometimes though, the procedure never loads within the
> website application even when I try all of the different reasonable
> values for that parameter.
> The only way to fix the problem that I've found is to either restart
> the SQL service or change the number of threads that the service is
> using (which seems to issue a "soft restart" of sorts to SQL itself).
> Once restarted, the website application loads fine and runs fine for a
> few days. However, once a few days have passed, the SQL server again
> starts giving timeouts for that query. The timeout errors that _do_
> occur at this point are usually given when the single parameter is
> different that the last parameter that gave the timeout errors before
> the last time the server was restarted. In all reality, everything
> just seems so random, and so it's hard to pin down any more details
> for sure.
> Like I said, this issue has only popped up in the past week or so and
> had been running fine for the 6+ months previous. Has anyone else
> encountered this issue before or otherwise have any suggestions for
> how I can fix the problem? I'd really appreciate anything at this
> point because I'm running out of ideas.
Conditional SQL Ajent Job execution
job should be running at any given time. If execution time exceeds 3
minutes - job should not start. Currently I have a flag in a
user-defined table that is set to 1 by the SP that is called by the
job when it begins, and reset back to 0 when it ends. The SP checks
the value of the flag, if it is currently = 1 then the SP exits.
The problem is that if the SP fails half-way through the flag is never
reset back to 0, thus preventing subsequent executions.
Is there a way to set the job to execute conditionally based on
whether or not another instance of same job is already running?I will probably add check the sysjobhistory to verify that
the job didn't fail:
select top 2 *
from sysjobhistory
where job_id = (select job_id from sysjobs
where name = <'Job Name'>)
order by run_date, convert(smalldatetime, (case len
(run_time)
when 1 then '00'+':'+'00'+':'+'0'+ right
(run_time,2)
when 2 then '00'+':'+'00'+':'+right
(run_time,2)
when 3 then '00'+':'+'0'+left(convert(char
(3), run_time), 1)+':'+right(run_time,2)
when 4 then '00'+':'+left(convert(char(4),
run_time), 2)+':'+right(run_time,2)
when 5 then '0'+left(convert(char(5),
run_time), 1)+':'+substring(convert(char(5), run_time), 2,
2)+':'+right(run_time,2)
when 6 then left(convert(char(6),
run_time), 2)+':'+substring(convert(char(6), run_time), 3,
2)+':'+right(run_time,2)
END)) desc
If the job failed, it should continue with the schedule:
Edgardo Valdez
MCSD, MCDBA, MCSE, MCP+I
http://www.edgardovaldez.us/
>--Original Message--
>I have a job set to execute every 3 minutes. Only one
instance of the
>job should be running at any given time. If execution
time exceeds 3
>minutes - job should not start. Currently I have a flag
in a
>user-defined table that is set to 1 by the SP that is
called by the
>job when it begins, and reset back to 0 when it ends.
The SP checks
>the value of the flag, if it is currently = 1 then the SP
exits.
>The problem is that if the SP fails half-way through the
flag is never
>reset back to 0, thus preventing subsequent executions.
>Is there a way to set the job to execute conditionally
based on
>whether or not another instance of same job is already
running?
>.
>|||You shouldn't need such flags etc. Agent will not start a job if it is still executing (from last
time).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alexey Aksyonenko" <Alexey.Aksyonenko@.coanetwork.com> wrote in message
news:1449e414.0309300914.2af4241@.posting.google.com...
> I have a job set to execute every 3 minutes. Only one instance of the
> job should be running at any given time. If execution time exceeds 3
> minutes - job should not start. Currently I have a flag in a
> user-defined table that is set to 1 by the SP that is called by the
> job when it begins, and reset back to 0 when it ends. The SP checks
> the value of the flag, if it is currently = 1 then the SP exits.
> The problem is that if the SP fails half-way through the flag is never
> reset back to 0, thus preventing subsequent executions.
> Is there a way to set the job to execute conditionally based on
> whether or not another instance of same job is already running?
Saturday, February 25, 2012
conditional execution of the next job in DTS
Any ideas?
Thanks
RaguYou can turn steps on or off. See the topic titled "Using ActiveX Scripts in a DTS Workflow" in BOL.
Also the topic "Using ActiveX Scripts in DTS" may be helpful.
Phil|||activex in dts is single threaded in dts and slows all kinds of stuff down.
try using any built in task first if you can.
if you want to try something trick, try the mesage queue task.
or
you can provide if logic for rows returned in an execute sql task and if successfull have the on success run an xpcmdshell from the sql task/
or try a data driven query task ++++ place if logic in the query
if rows returned then transfer to text
if no rows returned then raiserror
Books Online {Data Driven Query Task}
Books Online {Execute SQL Task}
Books Online {Message Queue Task}
Books Online {DTS tasks, overview}|||Phil/Ruprect: Thanks for your replies. I was using logic in sql (Execute SQL Task) to initiate the next step but the table has grown to 15 M rows. I plan to use activex script to just check the size of the output file (Thanks to sqldts.com site for the script) and accordingly initiate the next job.
Thanks again for the replies.
Ragu
Conditional execution of first task
I have a situation where I'd like to conditionally execute the first task in a package based on the contents of a user variable.
If user variable "Var1" is false I want to begin execution with the first task.
If "Var1" is true I want to begin execution at the second task.
My first thought of course was SequenceContainer, but the same issue would exist for the first task in a SequenceContainer.
Is there a way to do this?
Thanks!
To conditionally execute a task you would use a constraint with an expression on it, but you need two tasks (containers) to do this. The ideal answer is to use something that does nothing, but offers a start point for the constraint. A sequence container works rather well, just drop it on and collpase it (the arrow on the right-hand side of the header). You can then link that to the real task, nothing should go inside that sequence container.|||I have created a sample solution inline with Darren's suggeston to use 'Sequence Container' for placing the precedence condition @. http://mystutter.blogspot.com/2006/03/sql-server-integration-services.html
Please let me know your comments.
Thanks,
Loonysan
Thank you. Your example was perfect.
I found another solution as well. I created a Sequence Container, inside that I created another Sequence Container and my first task. I put an expression constraint between those. Then I linked the outer Sequence Container to the second task.
I appreciate the help.
Conditional execution of DTS package task
I want to run a DTS package that simply executes a SQL statement to get
a count of rows in a table, if the resulting number is greater than
zero I want to execute another step in the same package, else just
quit.
I don't to pass global vars from one package to another
How can I do this?
Any help would be greatHi Scouser,
I've got a suggestion: Create a stored proc that does the count of the
records and then execute the DTS from the procedure, if the condition
is true.
Look at this message if you want to see how to execute the DTS-Package
from a stored procedure:
http://groups.google.de/group/borla...62580101f3 8fe
Michael
www.zankl-it.de
Conditional Execution in the Control Flow via Script Task
I'm trying to conditionally execute a dataflow based on the presence of a data file. If the data file isn't present, I'd like to execute gracefully without error.
Logic is as follows:
If FileExists Then
execute dataflow
Else
exit w/o error
End If
I've got the code ready to go, but I'm not sure how to do this conditional branch logic. Right now, the code calls the Dts.Results.Success / Failure. The problem, however, is Failure is exactly that... which doesn't result in the graceful exit I'm looking for.
Anyone have any ideas?
Thanks in advance.
Here is how I would do that:
Create a script task in your control flow to check if the file exists and write that result into a SSIS variable, let's say FileExists=1 -->exists; FileExtis=0 -->Does not exist. Then create a precedence constraint from the script task to the data flow. Then Edit the precedence constraint to use evaluation operation 'Expression and constraint'; Value 'success' and write the expression like @.[User::FileExists]==1.
This way the dataflow will be executed only if the sript task succed and the value of the variable FileExists is equal to one.
Rafael Salas
|||This sounds like a fantastic suggestion for the File System Task. I would suggest you submit it at the Microsoft Connect site.|||Phil Brammer wrote:
This sounds like a fantastic suggestion for the File System Task. I would suggest you submit it at the Microsoft Connect site.
You mean to have a 'Check if file exists' operation in the file system task?
Rafael Salas
|||Rafael Salas wrote:
Phil Brammer wrote: This sounds like a fantastic suggestion for the File System Task. I would suggest you submit it at the Microsoft Connect site.
You mean to have a 'Check if file exists' operation in the file system task?
Rafael Salas
Indeed.|||
Good Idea. I followed your suggestion; for those interested in voting on that suggestion:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=231838
Rafael Salas
|||For those curious.. I documented the steps for future peoples that landed on this thread.dichotic.wordpress.com
Conditional Execution based on Multi-Valued Parameter Selection
If a user selects all (266) parameter values from a mult-valued parameter list, I would like to display a table based on a version of a stored-proc that does not select records using parameter driven criteria.
The total selected count can be acquired by =Parameters!ParmName.Count syntax. Total available parameter values can be extracted by =CountDistinct(Fields!ColName.Value,"DatasetName").
Setting a filter on a table and inspecting the total selected count can limit the number of values executed for the query.
That is great, but I would also like to run an efficient query if all values were selected. Thought I could set a filter on a table and compare these two values, but I can't use an aggregate.
Any ideas?
Decided to create a one row dataset (query) containing a count of the number of values to select from. Used this value in a hidden parameter "PrimaryListCount" and used the new dataset to populate the default report parameter value. Then I determined if all values were selected using the following table filter:
=CInt(Parameters!PrimaryNames.Count) = =CInt(Parameters!PrimaryListCount.Value)
If you have a better solution, I'd like to know about it. If not, hope this can help someone else.
Sunday, February 19, 2012
Concurrent Procedure Execution
Think of that there is a db which is used by many users. An there is a proc
which is called frequently by all users. For concurrent callings of this
proc by many differents users, Does there occur several execution time
intervals as parallel or are the concurrent callers wait each other? AND Can
we or Do we have to use BEGIN and COMMIT TRAN pairs for an atomic process in
such situations?
Thanks in Advance.
Emre GuldoganOn Thu, 10 Mar 2005 15:42:22 +0200, "Emre Guldogan" <ask me please...>
wrote:
>Think of that there is a db which is used by many users. An there is a proc
>which is called frequently by all users. For concurrent callings of this
>proc by many differents users, Does there occur several execution time
>intervals as parallel or are the concurrent callers wait each other?
Hi Emre,
Different users can execute the procedure simultaneously; they will
execute in parallel. Of course, if the procedure locks data, this will
block other users trying to use the same data, whether through the same
procedure or not. For most data retrieval procedures, this should not be
an issue, so execution would be simultaneous; for data modification
procedures, locking and blocking might lead to actual execution being
serial, but that depends on the code of the procedure, the transaction
isolation level and whether transactions are used or not.
>AND Can
>we or Do we have to use BEGIN and COMMIT TRAN pairs for an atomic process i
n
>such situations?
If you need atomic processing, you should indeed use BEGIN TRAN and
either COMMIT or ROLLBACK TRAN. If different users execute the proc
simultaneously, the transactions won't interfere (but they might block
each other, as indicated above).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Tuesday, February 14, 2012
Concurrency
Single commands are 'implied' transactions, that is the command executes completely, or nothing is done.
For multiple commands, where there is a need to make sure the several statements execute in a 'all or nothing' scenario, use TRANSACTIONS.
Refer to Books Online, Topic: Transactions
|||Other good topics in the BOL are "Isolation mode" and locking hints.
Jens K. Suessmeyer.
http://www.sqlserver2005.de