Re: SQL queries getting stuck
From: Michael C# (xyz_at_yomomma.com)
Date: 02/08/05
- Next message: Evelyn Schwartz via SQLMonster.com: "How do I call a stored procedure using perl DBI and evaluate the output"
- Previous message: Evelyn Schwartz via SQLMonster.com: "How to determine programatically what datafiles are associated with a database"
- In reply to: X: "Re: SQL queries getting stuck"
- Next in thread: JohnnyAppleseed: "Re: SQL queries getting stuck"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 8 Feb 2005 13:37:39 -0500
Sounds like one or more of your other processes running on the server might
be causing the issue. Do any of your other processes that are running on
the server at the same time lock the tables that your SP is updating? The
apparent "randomness" in this situation would be a function of the other
processes which your SP has no control over. I'd start by reviewing those
other processes and their interaction with the tables the SP is updating.
Thanks,
Michael C#, MCDBA
"X" <sa@nomail.com> wrote in message
news:%23JmuFhgDFHA.2180@TK2MSFTNGP10.phx.gbl...
> Ok I will try and explain the whole situation.
>
> The server I am talking about is a DWH server. The OLTP system is DB2.
> Datamirror is running continously getting getting data from DB2 to the SQL
> (Staging).
>
> At 12:00 am we start the nightly process. Initial steps include getting
> data
> from the Staging DB to prod DB via Informatica. Depending on what tables
> are
> loaded, other SP are started that load data in to the fact tables, which
> in
> turn start cube processing.
>
> The SP I am talking about is part of this process. But this is one of SPs,
> that gets fired up during the end, since it depends on all the other data
> that gets updated in the transaction table.
>
> Steps in SP are:
> 1. Truncates the daily load table (T1)
> 2. Loads data from various tables based on certain join criteria.
> 3. Performs update on T1.
>
> ***After each step (SQL DML) in the process, I write to a log table, which
> tells me at what point I am in the process.
>
> Further:
>
> 1. This SP is not the only process running on the Server
> 2. Most days it runs fine, but some days it just sit at one place for
> hours.
> The place it gets stuck can be anywhere in the SP.
> 3. I have examined the state of Server on such occassions. Sometimes the
> CPU
> has been at 80-90% and other times only 10-20%. At times there have been
> other processes running, but not always.
> 4. I have changed the indexes, moved the filegroups, etc, but this pops up
> time and again.
>
> Also our DBA run backups of some DBs (Full) daily between 8pm to 12 (Don't
> ask me why)
>
> Questions are:
>
> 1. Why would the process be fine one day and not the other
> 2. When I kill the process and run the steps manually (from the point
> where
> it was stuck), it runs just fine. Why?
>
> What else can I do?
>
> Please let me know if there is more info that I can provide...
>
>
>
> "Mal .mullerjannie@hotmail.com>" <<removethis> wrote in message
> news:BC69F41C-8929-4E53-B826-5BB7CE3DF820@microsoft.com...
>> Your not making it easy, your saying your car doens't want to start but
> you
>> don't tell us what car it is ? for all we know it doesn't even have
> engine :)
>>
>> If the procedure does not fail at the same place you can "assume" that
> it's
>> not the procedure but you can't be sure, maybe it's just a condition in
> your
>> procedure that only occur once every now and then cause everything to
>> slow
>> down, maybe locking or something . Do you have some sort of logging for
> your
>> procedure that's running ?
>>
>> Second thing, maybe the server is just too busy every now and then, maybe
>> someone is running a backup or re-indexing ? Do you know no one is
>> working
> on
>> the server.
>>
>> Latency , what's your layout network etc.. do you use distributed
> processing
>> do you use clustering ... are you executing the procedure as a job local
> on
>> the server or remote ?
>>
>> Maybe it's getting stuck in the same place in the procedure but because
>> if
>> different records to process each time it takes longer to get to the
>> point
> of
>> failing and give the illusion it's "random" , there's no such thing as
> random
>> it's a formula with a different seed :P
>>
>> If everything else fails, run profiler to try and catch the last
> transaction
>> processed before everything went haywire.
>>
>> Being more of a developer than DB, I'll start by looking at the code,
>> Profiler, some sort of logging for the procedure.
>> DBA would maybe look more into the config, locking and even as deep in
>> detail as fillfactor on tables .
>>
>> Hope I had a lucky guess on your problem ! :P
>>
>> Can you tell a bit more about your situation ?
>>
>> "X" wrote:
>>
>> > We have a SP that runs every night. It basically inserts, update data
> from
>> > various tables, has a bunch of SQL statements. The total records it is
>> > working with are between 6-7 million. But it randomly gets stuck at
>> > different places in the procedure. This is a random happening. Some
>> > days
> are
>> > good, others are not!
>> >
>> > I don't believe anything is wrong with the SP. What should I be looking
> at?
>> >
>> >
>> >
>> >
>
>
- Next message: Evelyn Schwartz via SQLMonster.com: "How do I call a stored procedure using perl DBI and evaluate the output"
- Previous message: Evelyn Schwartz via SQLMonster.com: "How to determine programatically what datafiles are associated with a database"
- In reply to: X: "Re: SQL queries getting stuck"
- Next in thread: JohnnyAppleseed: "Re: SQL queries getting stuck"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
