A/Synchronous execution of SSIS ETL packages

Hi

I'd like to know if there's a way to control the execution of ETL packages, such that:
Different packages, or at least packages that don't access the same table or database run asynchronously with respect to each other; e.g., two different packages run at the same time
and
If a package is called for execution more than once by different requests, force them to run synchronously, or one after the other.If this is possible, what resources would it require? Is this possible under, say, a dual or quad processor machine?

Thanks.

Well, there is nothing to stop you running 2 packages concurrently (i.e. at the same time). They run as seperate processes so cannot interfere with each other in the process space. Of course, if they are competing for the same external connections then you may run into problems.

Not so sure about ensuring that executions of a package are queued up tho as you require. You may have to implement a process on top of your packages to check to see if a package is already executing before attempting to execute it.

I don't think the spec of the machine is the issue here but it may help to know what resources a package utilises: http://blogs.conchango.com/jamiethomson/archive/2005/05/29/1486.aspx

-Jamie

|||

I'm not sure about the big picture, and how these package are run, but here are two ideas to consider:

1) using master package with execute package tasks and appropriate precedence constraints

2) creating Agent Job for each package - the Agent runs maximum one instance of a job at a time, thus if you always go via Agent, you'll have at most one instance of same package running.

|||

You would need to maintain some kind of shared state in order for packages in seperate processes to 'know' if a singleton object was being consumed by eachother and prevent contention. You can typically use a semaphore to stagger execution of any Windows dependant processes; in the SSIS context you could use a semaphore from within a script task. Have a look in the .Net framework for synchronization primitives support.

|||MSMQ task does this smashingly. :)

A question about execution plans

Hello, I'm using SQL 2000 with the latest updates.
I have a large table Call_Record (5 million rows) that has three indexes:
1. A clustered index on account_no ASC, date_start DESC
2. A non-clustered index on date_end DESC
3. A non-clustered index on call_record_id ASC
I'm querying it for failed calls in the last hour:
SELECT COUNT(*) AS failure_count, C.master_id_carrier, C.location_name,
D.description AS disconnect_reason
FROM dbo.Call_Record c
INNER JOIN dbo.Disconnect_Code D ON C.disconnect_code = D.code
WHERE (c.date_end >= (GetUtcDate() - (1.0/24.0)) )
AND D.is_failure = 1
GROUP BY C.master_id_carrier, C.location_name, D.description
The problem is that the execution plan shows that it is using index 1 to
perform this query, whereas index 2 is clearly the best choice. If I
replace the call to GetUtcDate() with a literal date constant like so:
SELECT COUNT(*) AS failure_count, C.master_id_carrier, C.location_name,
D.description AS disconnect_reason
FROM dbo.Call_Record c
INNER JOIN dbo.Disconnect_Code D ON C.disconnect_code = D.code
WHERE (c.date_end >= '2005/11/23')
AND D.is_failure = 1
GROUP BY C.master_id_carrier, C.location_name, D.description
then it does use index (2) as expected, and executes in a fraction of
the time. My question is, why does it pick the "incorrect" index for
the first query, and is there any way to force it to pick index (2)?
MikeMike
I tried to rewrite a little bit your SELECT
DBCC FREEPROCCACHE
GO
SELECT COUNT(*) AS failure_count, C.master_id_carrier, C.location_name,
D.description AS disconnect_reason
FROM dbo.Call_Record c
INNER JOIN dbo.Disconnect_Code D ON C.disconnect_code = D.code
WHERE c.date_end >=dateadd(hour,-1,GetUtcDate()) and c.date_end <
dateadd(day,+1,GetUtcDate()) --replace with the date that is relevant for
the searching
(GetUtcDate() - (1.0/24.0)) )
AND D.is_failure = 1
GROUP BY C.master_id_carrier, C.location_name, D.description
Do you see now any changes in the execution plan , I'd put the CI on
date_end column since your criteria is based on range date seraching and CI
is probably a good choice for it, but you'll have to test it.
"Mike Chamberlain" <none@.hotmail.com> wrote in message
news:%23ibexQI8FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Hello, I'm using SQL 2000 with the latest updates.
> I have a large table Call_Record (5 million rows) that has three indexes:
> 1. A clustered index on account_no ASC, date_start DESC
> 2. A non-clustered index on date_end DESC
> 3. A non-clustered index on call_record_id ASC
> I'm querying it for failed calls in the last hour:
> SELECT COUNT(*) AS failure_count, C.master_id_carrier, C.location_name,
> D.description AS disconnect_reason
> FROM dbo.Call_Record c
> INNER JOIN dbo.Disconnect_Code D ON C.disconnect_code = D.code
> WHERE (c.date_end >= (GetUtcDate() - (1.0/24.0)) )
> AND D.is_failure = 1
> GROUP BY C.master_id_carrier, C.location_name, D.description
> The problem is that the execution plan shows that it is using index 1 to
> perform this query, whereas index 2 is clearly the best choice. If I
> replace the call to GetUtcDate() with a literal date constant like so:
> SELECT COUNT(*) AS failure_count, C.master_id_carrier, C.location_name,
> D.description AS disconnect_reason
> FROM dbo.Call_Record c
> INNER JOIN dbo.Disconnect_Code D ON C.disconnect_code = D.code
> WHERE (c.date_end >= '2005/11/23')
> AND D.is_failure = 1
> GROUP BY C.master_id_carrier, C.location_name, D.description
> then it does use index (2) as expected, and executes in a fraction of the
> time. My question is, why does it pick the "incorrect" index for the
> first query, and is there any way to force it to pick index (2)?
> Mike|||Mike Chamberlain (none@.hotmail.com) writes:
> I have a large table Call_Record (5 million rows) that has three indexes:
> 1. A clustered index on account_no ASC, date_start DESC
> 2. A non-clustered index on date_end DESC
> 3. A non-clustered index on call_record_id ASC
> I'm querying it for failed calls in the last hour:
> SELECT COUNT(*) AS failure_count, C.master_id_carrier, C.location_name,
> D.description AS disconnect_reason
> FROM dbo.Call_Record c
> INNER JOIN dbo.Disconnect_Code D ON C.disconnect_code = D.code
> WHERE (c.date_end >= (GetUtcDate() - (1.0/24.0)) )
> AND D.is_failure = 1
> GROUP BY C.master_id_carrier, C.location_name, D.description
> The problem is that the execution plan shows that it is using index 1 to
> perform this query, whereas index 2 is clearly the best choice. If I
> replace the call to GetUtcDate() with a literal date constant like so:
> SELECT COUNT(*) AS failure_count, C.master_id_carrier, C.location_name,
> D.description AS disconnect_reason
> FROM dbo.Call_Record c
> INNER JOIN dbo.Disconnect_Code D ON C.disconnect_code = D.code
> WHERE (c.date_end >= '2005/11/23')
> AND D.is_failure = 1
> GROUP BY C.master_id_carrier, C.location_name, D.description
> then it does use index (2) as expected, and executes in a fraction of
> the time. My question is, why does it pick the "incorrect" index for
> the first query, and is there any way to force it to pick index (2)?
When making the choice between scanning a clustered index, or using a
non-clustered index + bookmark lookup, the optimizer always have a
delicate choice. If the condition on the column in the NC-index hits
few rows is small, the NC index is good. But if the condition hits many
rows, the NC index is a lot worse than the table scan, as SQL Server
would have to access many data pages more than once.
To determine which to use, SQL Server makes estimates from statistics
saved for the table. When you put in a date literal, SQL Server can see
that the query will only hit a small number of rows, and thus the index
is good.
But for the first query, the problem is that getutcdate() is a non-
deterministic function, and thus will return different values each
time. I guess, therefore, the optimizer does not care about the
expression, but uses the clustered index instead. Since you have a
condition with >= there could potentially be many rows that are
hit in the condition.
In a situation like this an index hint may be a good idea:
FROM dbo.Call_Record c WITH (INDEX = DateEnd_ix)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

A QUERY THAT RUN ON DB2 THAT HAVE MORE PERFORMANCE THAN SQL SERVER 2000

The execution time for this query on DB2 v8.0 DBMS one second but I execute it on SQL SERVER 2000 is around 55 second

so how i can incease the performance for SQL server

SELECT ACC_KEY1,ACC_STATUS_LAST FROM PSSIG.CLNT_ACCOUNTS INNER JOIN PSSIG.CLNT_CUSTOMERS ON
PSSIG.CLNT_ACCOUNTS.CSTMR_OID = PSSIG.CLNT_CUSTOMERS.CSTMR_OID
WHERE (PSSIG.CLNT_CUSTOMERS.CSTMR_START_DT >= '1900-1-1 12:00:00') AND
(PSSIG.CLNT_CUSTOMERS.CSTMR_END_DT <= '2106-12-31 12:00:00') AND
(PSSIG.CLNT_ACCOUNTS.ACC_KEY1 >= '0000000000000') AND
(PSSIG.CLNT_ACCOUNTS.ACC_KEY1 <= '9999999999999') AND
(PSSIG.CLNT_ACCOUNTS.ACC_STATUS_LAST = 5 ) AND
ACC_KEY1 > '0' ORDER BY ACC_KEY1

Note 1: value 5 exist in most of rows about ( 999999/1000000 ) from the table rows count
Note 2: the number of rows in each table around 15000000
Note 3: I used the same index structure for both DB2 and SQL server 2000
Note 4: I used some other feature in DB2 that increase the performance but I did not
found the alternative for it in SQL server 2000 :
a- cardinality varies at run time feature
b- include column in index instead of use compound index for
( ACC_KEY1 ,ACC_STATUS_LAST ) columns
Note 5 : Enable reverse scan for index

Um, why are you using strings to store the ACC_KEY1? Numeric fields are much faster.

I would suggest that you drop all your indexes that relate to that query. Then run the Database Engine Tuning Advisor (or whatever its called in SQL 2000) to determine what the right indexes are. Unless you know SQL Server intimately, it can generate better indexes than you can by hand.

Jonathan

|||

thank you for you advice , i use the tuuning wizard but it did not improve the performance

- and acc_key1 could contain a letter so it must be a string

|||

You use the same indexes, but what does those indexes look like?

What is the volume to be returned? Is the expected output close to a million rows? (all the '5's)

How do you measure the time? Do you look at the server for the time it takes to resolve the query, or do you measure at the 'end-point'? (ie if you select... and wait until a million rows has been drawn on the screen, or similar)

/Kenneth