Production experience to share

I wanted to post this in case anyone else runs into it - save you loads of
time.
Yesterday we deployed our latest release of reports - someone forgot to move
the pin on the report in source control and yet the updated stored proc for
the report went out. Net result was the updated stored proc was out there
but the report wasn't. The new stored proc was requesting a new parameter
the report couldn't supply. Normally on our dev box we get the error message
back from reporting services rather quickly (~3 seconds). On our test box it
was taking 3 minutes and production was just hanging.
We are using C# to proxy all report requests (so nobody can hit the box
directly) and the error codes being returned to C# were basically saying that
reporting services was abruptly cutting the connection. We received Denial
of Service Attach messages, Tls errors, SSL/Tls trust errors and timeout
errors. Nothing returned about missing parameters - the exception object
doesn't receive detailed info as to why the connection was killed - only that
it was killed. In order to get the details we will have to modify the code
to go into the HTML returned by the WebRequest object.
I don't know why the report was hanging for as long as it was- I went into
the IIS logs to see what was being requested (fortunately we have to use URL
requests in our case) and when picking through the requests I noticed that it
wasn't passing in a required parameter. (The report didn't request it, so it
didn't pass it). When I recreated the request through report manager I
noticed that it wasn't using all of the parameters expected.
In our attempts we upped the MaxReqPerUser property in RSReportServer.config
file from default (20) to 140 as a guess - it helped for about 5 minutes,
then everybody crashed again.
Microsoft - hopefully the new RS release will interrogate the stored proc at
runtime and return results quicker? Hopefully error integration will be
better with C# next year? I doubt it b/c there may not be a good way to pass
errors to the exception object.
I am sharing this b/c I was on the phone with MS premier support ($$)
yesterday for 7 hours and they offered no creative ideas or really anything
of signifigance. Please add this to your knowledge base so the next guy
might avoid 12 hours of headache.
Any comments are welcomed!
John Hennesey
JohnHennesey82:hotmail.comThanks for sharing...I'll have to remember this one...
"JohnHennesey" <JohnHennesey@.discussions.microsoft.com> wrote in message
news:C285281B-4230-4114-8B3A-E303CB2FADD9@.microsoft.com...
>I wanted to post this in case anyone else runs into it - save you loads of
> time.
> Yesterday we deployed our latest release of reports - someone forgot to
> move
> the pin on the report in source control and yet the updated stored proc
> for
> the report went out. Net result was the updated stored proc was out there
> but the report wasn't. The new stored proc was requesting a new
> parameter
> the report couldn't supply. Normally on our dev box we get the error
> message
> back from reporting services rather quickly (~3 seconds). On our test box
> it
> was taking 3 minutes and production was just hanging.
> We are using C# to proxy all report requests (so nobody can hit the box
> directly) and the error codes being returned to C# were basically saying
> that
> reporting services was abruptly cutting the connection. We received
> Denial
> of Service Attach messages, Tls errors, SSL/Tls trust errors and timeout
> errors. Nothing returned about missing parameters - the exception object
> doesn't receive detailed info as to why the connection was killed - only
> that
> it was killed. In order to get the details we will have to modify the
> code
> to go into the HTML returned by the WebRequest object.
> I don't know why the report was hanging for as long as it was- I went into
> the IIS logs to see what was being requested (fortunately we have to use
> URL
> requests in our case) and when picking through the requests I noticed that
> it
> wasn't passing in a required parameter. (The report didn't request it, so
> it
> didn't pass it). When I recreated the request through report manager I
> noticed that it wasn't using all of the parameters expected.
> In our attempts we upped the MaxReqPerUser property in
> RSReportServer.config
> file from default (20) to 140 as a guess - it helped for about 5 minutes,
> then everybody crashed again.
> Microsoft - hopefully the new RS release will interrogate the stored proc
> at
> runtime and return results quicker? Hopefully error integration will be
> better with C# next year? I doubt it b/c there may not be a good way to
> pass
> errors to the exception object.
> I am sharing this b/c I was on the phone with MS premier support ($$)
> yesterday for 7 hours and they offered no creative ideas or really
> anything
> of signifigance. Please add this to your knowledge base so the next guy
> might avoid 12 hours of headache.
> Any comments are welcomed!
> John Hennesey
> JohnHennesey82:hotmail.com

Product Sales Forecasting.

Hi,

We are trying to forcast product sales for next three months based on their sales for previous 12 months. In this case, Microsoft Time Series algorithm requires the sales data to be present for each product for past 12 months (?). However, our products have typical life span of 6 months and obviously the new products will not have sales before they were added. Any help will be very much appreciated.

Thanks

Riju

Riju -

A few pointers that have been very successful for us in forecasting sales and inventory:

1. Can you build store clustering models to increase the data set size?
2. For new products you can also try correlating sales from similar existing products. We have utilized attributes from existing products and mapped their sales as a predictor for new product sales.

Here is a whitepaper that provides a detailed inventory forecasting example from Project REAL http://apollodatatech.com/company/shell.html?projreal

Hope this helps,

Jeff

Please visit Apollo Data Technologies http://apollodatatech.com/|||

Hi Jeff,

Thanks for the response.

It seems that the Out-of-stock predictive model in REAL project is based in the Decision Tree algorithm which does not require the data to be present for each period. I am having problem to use Time Series algorithm becuase my products typically have sales for about six month and i am trying to use the last 12 months data to predict the sales of next 3 months.

Thanks

Riju

|||

This must be a common problem in forcasting. Isn't there any way to handle this?

Thanks

Riju

|||You should read the paper referenced by apollo. In these circumstances you likely need to build a tree model to predict based on similar products, not use time series as there's not enough historical data.|||

Hi Jamie,

Thanks for the response.

I read the paper referenced by Apollow which gave the detail information on how to predict the product sales using the Decision Tree algorithm. However, i was trying to get some help on how to use the Time Series algorithm in my situation. Thanks to your response which stated that the Time Series algorithm is not applicable in my situation.

Riju

Process Info (SQL Server Enterprise - Management - Current Activity)

Dear All
I have problem with my database server which running SQL server 2000.
The server running very slow. The worst case, to save a record required
more than 20-30 seconds.
Since this problem, I usually monitoring Process Info from Enterprise
Manager (Management - Current Activity), and I found a misterious
process as follow :
1. User: System
AccessTo: Master
Status: Background
Common: Task Manager
Waiting: >438 Million
2. User: System
AccessTo: Master
Status: Background
Common: Task Manager
Physical IO: > 51000
3. User: Administrator (Join domain)
Database: MSDB
Status: Sleeping
Common: Awaiting Command
App: SQL Agent Alert Engine
CPU Usage: > 16 Million
Anybody know about these condition? Does it normal?
Thanks
Michael
Mike
What is amount of memory?
Run SQL Server Profiler to see what is going on. (Blocking ,locks)
EXEC sp_who2 , see blkby column (If I remember well).
"Michael" <yapmichael2000@.gmail.com> wrote in message
news:1115718206.985119.318980@.o13g2000cwo.googlegr oups.com...
> Dear All
> I have problem with my database server which running SQL server 2000.
> The server running very slow. The worst case, to save a record required
> more than 20-30 seconds.
> Since this problem, I usually monitoring Process Info from Enterprise
> Manager (Management - Current Activity), and I found a misterious
> process as follow :
> 1. User : System
> AccessTo : Master
> Status : Background
> Common : Task Manager
> Waiting : >438 Million
> 2. User : System
> AccessTo : Master
> Status : Background
> Common : Task Manager
> Physical IO : > 51000
> 3. User : Administrator (Join domain)
> Database : MSDB
> Status : Sleeping
> Common : Awaiting Command
> App : SQL Agent Alert Engine
> CPU Usage : > 16 Million
> Anybody know about these condition? Does it normal?
> Thanks
> Michael
>
|||Thanks for your answer,
Amount of memory that be used by SQL Server is 1 GB.
I've check using sp_who, and on nothing on column blkby.
I think that not caused by Locks.
Note : After I restart the server, everything run well for a while. In
the worst case, the problem will be occured in 1 day, but usually
system running well within 3,4 days.

Process Info (SQL Server Enterprise - Management - Current Activity)

Dear All

I have problem with my database server which running SQL server 2000.
The server running very slow. The worst case, to save a record required
more than 20-30 seconds.
Since this problem, I usually monitoring Process Info from Enterprise
Manager (Management - Current Activity), and I found a misterious
process as follow :

1. User: System
AccessTo: Master
Status: Background
Common: Task Manager
Waiting: >438 Million

2. User: System
AccessTo: Master
Status: Background
Common: Task Manager
Physical IO: > 51000

3. User: Administrator (Join domain)
Database: MSDB
Status: Sleeping
Common: Awaiting Command
App: SQL Agent Alert Engine
CPU Usage: > 16 Million

Anybody know about these condition? Does it normal?

Thanks

MichaelMichael (yapmichael2000@.gmail.com) writes:
> I have problem with my database server which running SQL server 2000.
> The server running very slow. The worst case, to save a record required
> more than 20-30 seconds.
> Since this problem, I usually monitoring Process Info from Enterprise
> Manager (Management - Current Activity), and I found a misterious
> process as follow :
> 1. User : System
> AccessTo : Master
> Status : Background
> Common : Task Manager
> Waiting : >438 Million
> 2. User : System
> AccessTo : Master
> Status : Background
> Common : Task Manager
> Physical IO : > 51000
> 3. User : Administrator (Join domain)
> Database : MSDB
> Status : Sleeping
> Common : Awaiting Command
> App : SQL Agent Alert Engine
> CPU Usage : > 16 Million
> Anybody know about these condition? Does it normal?

These are system processes, and they be normal, particularly if SQL Server
has been up for a long time. I checked a production box, and while it
had lower numbers than yours, they were still big.

The most likely reason when a server appears to be slow is poor indexing,
poorly written code and fragmentation. For instance, when saving a row and
there is a poorly written trigger, this could make the INSERT statement
to take a long time. Blocking could also be an issue, and blocking can
also easily occur, if there are slow queries.

You don't say whether this is an application, you have control over
or a third-party app. But in any, case you need to analyse exactly
which queries that are slow. One way to do this is use the SQL Profiler,
and filter for operations with a long duration. Note though that from
duration alone, you cannot tell whether it was due to blocking or bad
performance. The CPU, Reads and Writes columns can give some hints about
this. (If they are low and duration is high, there was blocking.) You
can also use sp_who to see if you have any blocking, by looking for
non-zero values in the Blk column.

Once you have found the queries that are long-running, you can look
into improving indexes, and if possible also rewrite them.

You can also try running DBCC DBREINDEX on tables where you experience
problem. If you have fragmentation, you can get improvements.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for your answer.

I've problem, as I wrote down, sometime to save a record required much
time.
When this happen, usually I restart the server, and then the problem
solved for a while. The problem will happen again within 10 day.
When the problem occured, on the Task Manager, Process Tab, SQLServ.exe
using more than 1 Gigabyte memory. And after restart the server,
SQLServ.exe only use about 450 to 500 Megabyte.

While the problem occured, there were difficulty to make connection to
the server (using Enterprise manager, Query Analyser, Application).
Usually an error message : Timeout expired.

About the application, I use visual basic to develop application.
And there were a few trigger, some of there were use cursor.
At this momenth, I have disable many trigger that used cursor, but
there a bit trigger which used cursor still active.

Would you like to give any suggestion?

Thanks very much

Michael|||Michael (yapmichael2000@.gmail.com) writes:
> I've problem, as I wrote down, sometime to save a record required much
> time.
> When this happen, usually I restart the server, and then the problem
> solved for a while. The problem will happen again within 10 day.
> When the problem occured, on the Task Manager, Process Tab, SQLServ.exe
> using more than 1 Gigabyte memory. And after restart the server,
> SQLServ.exe only use about 450 to 500 Megabyte.

That's perfectly normal. SQL Server grabs as much memory it needs and
can get. This memory is used for cache. So if SQL Server are kept running,
and there is no other activity on the machine, SQL Server should by
time have grown to use about all memory on the machine that the OS
does not need. (If there are other apps asking for memory, SQL Server
will yield memory.) Thus, a high memory consumption is no sign of
problem.

> While the problem occured, there were difficulty to make connection to
> the server (using Enterprise manager, Query Analyser, Application).
> Usually an error message : Timeout expired.

This one on the other hand obviously is a token of that something is wacko.
Do you get these problems also when you try to connect from the machine on
which SQL Server is running? If this works fine, one could suspect network
problems.

If not, it sounds like something is bogging down SQL Server very heavily.
This could be a poorly written query, but it also be an anomaly in the
server. Check what is in the SQL Server log at these occassions; there
might be some interesting messages. Particularly, I have one about UMS
Scheduler in mind. (A message that was added in SP3, but you are running
SP3 aren't you? By the way, SP4 is out.)

It could also be an idea to keep a Profiler trace running so you can see
what commands that are submitted and then try to correlate these commands
with the conditions where there server is not very reposnive.

Another check to make, just to rule out the more silly stuff, is that
you don't have any compressed database files.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Process Info (SQL Server Enterprise - Management - Current Activity)

Dear All
I have problem with my database server which running SQL server 2000.
The server running very slow. The worst case, to save a record required
more than 20-30 seconds.
Since this problem, I usually monitoring Process Info from Enterprise
Manager (Management - Current Activity), and I found a misterious
process as follow :
1. User : System
AccessTo : Master
Status : Background
Common : Task Manager
Waiting : >438 Million
2. User : System
AccessTo : Master
Status : Background
Common : Task Manager
Physical IO : > 51000
3. User : Administrator (Join domain)
Database : MSDB
Status : Sleeping
Common : Awaiting Command
App : SQL Agent Alert Engine
CPU Usage : > 16 Million
Anybody know about these condition? Does it normal?
Thanks
MichaelMike
What is amount of memory?
Run SQL Server Profiler to see what is going on. (Blocking ,locks)
EXEC sp_who2 , see blkby column (If I remember well).
"Michael" <yapmichael2000@.gmail.com> wrote in message
news:1115718206.985119.318980@.o13g2000cwo.googlegroups.com...
> Dear All
> I have problem with my database server which running SQL server 2000.
> The server running very slow. The worst case, to save a record required
> more than 20-30 seconds.
> Since this problem, I usually monitoring Process Info from Enterprise
> Manager (Management - Current Activity), and I found a misterious
> process as follow :
> 1. User : System
> AccessTo : Master
> Status : Background
> Common : Task Manager
> Waiting : >438 Million
> 2. User : System
> AccessTo : Master
> Status : Background
> Common : Task Manager
> Physical IO : > 51000
> 3. User : Administrator (Join domain)
> Database : MSDB
> Status : Sleeping
> Common : Awaiting Command
> App : SQL Agent Alert Engine
> CPU Usage : > 16 Million
> Anybody know about these condition? Does it normal?
> Thanks
> Michael
>|||Hi Michael,
I agree with Uri, something is a bit iffy with your server and you should
run the profiler.
If you don't have any jobs running I would sugest you stop then restart the
service that way when it re-starts it will start with a 'clean plate' then
your can put on your monitoring stuff. NB if you to have jobs running then
you run the risk of losing data.
However when it does re-start it should be a lot faster.
Peter
"Michael" wrote:
> Dear All
> I have problem with my database server which running SQL server 2000.
> The server running very slow. The worst case, to save a record required
> more than 20-30 seconds.
> Since this problem, I usually monitoring Process Info from Enterprise
> Manager (Management - Current Activity), and I found a misterious
> process as follow :
> 1. User : System
> AccessTo : Master
> Status : Background
> Common : Task Manager
> Waiting : >438 Million
> 2. User : System
> AccessTo : Master
> Status : Background
> Common : Task Manager
> Physical IO : > 51000
> 3. User : Administrator (Join domain)
> Database : MSDB
> Status : Sleeping
> Common : Awaiting Command
> App : SQL Agent Alert Engine
> CPU Usage : > 16 Million
> Anybody know about these condition? Does it normal?
> Thanks
> Michael
>|||Thanks for your answer,
Amount of memory that be used by SQL Server is 1 GB.
I've check using sp_who, and on nothing on column blkby.
I think that not caused by Locks.
Note : After I restart the server, everything run well for a while. In
the worst case, the problem will be occured in 1 day, but usually
system running well within 3,4 days.

Process Info (SQL Server Enterprise - Management - Current Activity)

Dear All
I have problem with my database server which running SQL server 2000.
The server running very slow. The worst case, to save a record required
more than 20-30 seconds.
Since this problem, I usually monitoring Process Info from Enterprise
Manager (Management - Current Activity), and I found a misterious
process as follow :
1. User : System
AccessTo : Master
Status : Background
Common : Task Manager
Waiting : >438 Million
2. User : System
AccessTo : Master
Status : Background
Common : Task Manager
Physical IO : > 51000
3. User : Administrator (Join domain)
Database : MSDB
Status : Sleeping
Common : Awaiting Command
App : SQL Agent Alert Engine
CPU Usage : > 16 Million
Anybody know about these condition? Does it normal?
Thanks
MichaelMike
What is amount of memory?
Run SQL Server Profiler to see what is going on. (Blocking ,locks)
EXEC sp_who2 , see blkby column (If I remember well).
"Michael" <yapmichael2000@.gmail.com> wrote in message
news:1115718206.985119.318980@.o13g2000cwo.googlegroups.com...
> Dear All
> I have problem with my database server which running SQL server 2000.
> The server running very slow. The worst case, to save a record required
> more than 20-30 seconds.
> Since this problem, I usually monitoring Process Info from Enterprise
> Manager (Management - Current Activity), and I found a misterious
> process as follow :
> 1. User : System
> AccessTo : Master
> Status : Background
> Common : Task Manager
> Waiting : >438 Million
> 2. User : System
> AccessTo : Master
> Status : Background
> Common : Task Manager
> Physical IO : > 51000
> 3. User : Administrator (Join domain)
> Database : MSDB
> Status : Sleeping
> Common : Awaiting Command
> App : SQL Agent Alert Engine
> CPU Usage : > 16 Million
> Anybody know about these condition? Does it normal?
> Thanks
> Michael
>|||Thanks for your answer,
Amount of memory that be used by SQL Server is 1 GB.
I've check using sp_who, and on nothing on column blkby.
I think that not caused by Locks.
Note : After I restart the server, everything run well for a while. In
the worst case, the problem will be occured in 1 day, but usually
system running well within 3,4 days.