Conditional timeout issue when executing SP

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.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 split on date ?

Hi,

I have a DT_DATE column. I'd like to achieve a conditional split to ignore all records for which the date is below a specific hardcoded date (eg: 2007-03-01).

I'm having a hard time trying to express this using the conditional split transform.

What is the correct syntax to express a DT_DATE literal ?

eg:
[date] < (DT_DATE) "2007-03-01"

regards

Thibaut

What you have should work fine. I built a little test package to verify, and each of these worked as expected:

Code Snippet

HireDate < (DT_DATE)"1998-01-30"

Code Snippet

[HireDate] < (DT_DATE)"1998-01-30"

Code Snippet

HireDate < (DT_DATE)"01/30/1998"

Code Snippet

[HireDate] < (DT_DATE)"01/30/1998"

What behavior are you experiencing that prompts you to ask the question?

|||Are you sure [date] is a DT_DATE column and not a DT_DBTIMESTAMP column? That is, does it contain a time component?

Just double checking.

Conditional Running of DTS Jobs

We have several DTS packages run as SQL Agent jobs using DTSRUN methods.
However, there are certain specific holidays we don't want to run these jobs
that are stored in a holiday table. Since parameters can't be passed between
job steps, what is the best way for aborting the job on a specific day
without running the DTS job? Is there a way to set a success or failure flag
in jobs and use this flag to abort before running the next job step?
--
Larry Menzin
American Techsystems Corp.Couldn't you check your holiday table in each job step, using sql...for
example...
/*get today*/
select @.today = (select getdate())
/*if today isn't in the holiday table then kick off dts job*/
if not exists (select * from holiday_table where holiday = @.today)
xp_cmdshell 'dtsrun .....blah, blah'
Not sure if my syntax is quite right, and not sure how your dates are
formatted, but something like this should work.|||You could also create a job step as the first step, that checks the date and
fails the job.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:624FC617-C35C-4FDF-95F9-D31C5349C85D@.microsoft.com...
> We have several DTS packages run as SQL Agent jobs using DTSRUN methods.
> However, there are certain specific holidays we don't want to run these
> jobs
> that are stored in a holiday table. Since parameters can't be passed
> between
> job steps, what is the best way for aborting the job on a specific day
> without running the DTS job? Is there a way to set a success or failure
> flag
> in jobs and use this flag to abort before running the next job step?
> --
> Larry Menzin
> American Techsystems Corp.

Conditional Running of DTS Jobs

We have several DTS packages run as SQL Agent jobs using DTSRUN methods.
However, there are certain specific holidays we don't want to run these jobs
that are stored in a holiday table. Since parameters can't be passed between
job steps, what is the best way for aborting the job on a specific day
without running the DTS job? Is there a way to set a success or failure flag
in jobs and use this flag to abort before running the next job step?
Larry Menzin
American Techsystems Corp.
Couldn't you check your holiday table in each job step, using sql...for
example...
/*get today*/
select @.today = (select getdate())
/*if today isn't in the holiday table then kick off dts job*/
if not exists (select * from holiday_table where holiday = @.today)
xp_cmdshell 'dtsrun .....blah, blah'
Not sure if my syntax is quite right, and not sure how your dates are
formatted, but something like this should work.
|||You could also create a job step as the first step, that checks the date and
fails the job.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:624FC617-C35C-4FDF-95F9-D31C5349C85D@.microsoft.com...
> We have several DTS packages run as SQL Agent jobs using DTSRUN methods.
> However, there are certain specific holidays we don't want to run these
> jobs
> that are stored in a holiday table. Since parameters can't be passed
> between
> job steps, what is the best way for aborting the job on a specific day
> without running the DTS job? Is there a way to set a success or failure
> flag
> in jobs and use this flag to abort before running the next job step?
> --
> Larry Menzin
> American Techsystems Corp.

Conditional Running of DTS Jobs

We have several DTS packages run as SQL Agent jobs using DTSRUN methods.
However, there are certain specific holidays we don't want to run these jobs
that are stored in a holiday table. Since parameters can't be passed between
job steps, what is the best way for aborting the job on a specific day
without running the DTS job? Is there a way to set a success or failure flag
in jobs and use this flag to abort before running the next job step?
--
Larry Menzin
American Techsystems Corp.Couldn't you check your holiday table in each job step, using sql...for
example...
/*get today*/
select @.today = (select getdate())
/*if today isn't in the holiday table then kick off dts job*/
if not exists (select * from holiday_table where holiday = @.today)
xp_cmdshell 'dtsrun .....blah, blah'
Not sure if my syntax is quite right, and not sure how your dates are
formatted, but something like this should work.|||You could also create a job step as the first step, that checks the date and
fails the job.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:624FC617-C35C-4FDF-95F9-D31C5349C85D@.microsoft.com...
> We have several DTS packages run as SQL Agent jobs using DTSRUN methods.
> However, there are certain specific holidays we don't want to run these
> jobs
> that are stored in a holiday table. Since parameters can't be passed
> between
> job steps, what is the best way for aborting the job on a specific day
> without running the DTS job? Is there a way to set a success or failure
> flag
> in jobs and use this flag to abort before running the next job step?
> --
> Larry Menzin
> American Techsystems Corp.

Conditional format within matrix, depending on subtotal?

Hi there,

I'm having trouble inserting a conditional format to a specific column.

e.g.: Matrix within the rows the "weeks" (1, 2, 3, ... , 52), and in the column a "lastyear revenu", "thisyear revenue" and a difference between them, "delta %", in percent grouped by the stores.
I added a subtotal to it so I get in the latest column the "total lastyear revenue", the "total thisyear revenue" and a difference between them in percent for all stores, "total delta %", for a specific week.

Problem: I want to colour the "delta %" column green when it is greater then the "total delta %" value.

I thougt this would be quite easy, but it really is a pain in the *** because, in the background expression dialog box, I can't refer to the subtotal cells ...

I tried to create a simple report from a cube with Month,Store,Turnover, Previous Year turnover and Delta %. Then I placed on the rows the months and the stores and the values in the colums (that should be the way that you did on the report, am I correct?) and I added the subtotal. Then in the background expression I wrote this:

"=iif(sum(Delta.values) > sum(Delta.values,"Dataset1"),"Green","White")"

Doing this I had the monthly delta background in green when it was higher than the total one.

I hope that I was clear enough!

|||So, am I getting this right:

You simply created another dataset in which you calculate the "total delta %". You then refer in the background expression dialog box to the "total delta %" field of the new dataset?
|||

The dataset is the same, I just refer to the whole dataset in the formula.

So, I have only one dataset (dataset1) and in the % delta for the background I use a formula like:

iif((sum(Fields!CYRevenue.value)-sum(Fields!PYRevenue.value))/sum(fields!PYRevenue.value) > (sum(Fields!CYRevenue.value,"Dataset1")-sum(Fields!PYRevenue.value,"Dataset1"))/sum(fields!PYRevenue.value,"Dataset1"),"Green","White")

Hope it helps!

Conditional format within matrix, depending on subtotal?

Hi there,

I'm having trouble inserting a conditional format to a specific column.

e.g.: Matrix within the rows the "weeks" (1, 2, 3, ... , 52), and in the column a "lastyear revenu", "thisyear revenue" and a difference between them, "delta %", in percent grouped by the stores.
I added a subtotal to it so I get in the latest column the "total lastyear revenue", the "total thisyear revenue" and a difference between them in percent for all stores, "total delta %", for a specific week.

Problem: I want to colour the "delta %" column green when it is greater then the "total delta %" value.

I thougt this would be quite easy, but it really is a pain in the *** because, in the background expression dialog box, I can't refer to the subtotal cells ...

I tried to create a simple report from a cube with Month,Store,Turnover, Previous Year turnover and Delta %. Then I placed on the rows the months and the stores and the values in the colums (that should be the way that you did on the report, am I correct?) and I added the subtotal. Then in the background expression I wrote this:

"=iif(sum(Delta.values) > sum(Delta.values,"Dataset1"),"Green","White")"

Doing this I had the monthly delta background in green when it was higher than the total one.

I hope that I was clear enough!

|||So, am I getting this right:

You simply created another dataset in which you calculate the "total delta %". You then refer in the background expression dialog box to the "total delta %" field of the new dataset?
|||

The dataset is the same, I just refer to the whole dataset in the formula.

So, I have only one dataset (dataset1) and in the % delta for the background I use a formula like:

iif((sum(Fields!CYRevenue.value)-sum(Fields!PYRevenue.value))/sum(fields!PYRevenue.value) > (sum(Fields!CYRevenue.value,"Dataset1")-sum(Fields!PYRevenue.value,"Dataset1"))/sum(fields!PYRevenue.value,"Dataset1"),"Green","White")

Hope it helps!