sql login permissions: duration

Showing posts with label duration. Show all posts

Friday, March 30, 2012

Long running SQLTransaction

Hi all,
we have a strange problem in SQL2000 SP3a with SQLTransaction's.
When running SQL Profiler the duration for some SQLTransactions (Commit) are
over six seconds, but the individual SQL statements (sp's) are executed at no
time at all.
I've used sp_lock but there are no locks.
Here's an extract from SQLProfiler:
SQLTransaction Commit 6796 5 2005-03-04 15:30:27.887 2005-03-04
15:30:34.683 DTCXact 16335154 sa
Any Ideas ?
Regards Tommy S.Check out master..sysprocesses and find out what the waittypes are
to determine what is causing the extended commit operation.
is the Tlog file on the same disk as the data file . .how busy is the server?
is this constantly reproduceable? or is it intermittent?
HTH
"Tommy SÃ¶derkvist" wrote:
> Hi all,
> we have a strange problem in SQL2000 SP3a with SQLTransaction's.
> When running SQL Profiler the duration for some SQLTransactions (Commit) are
> over six seconds, but the individual SQL statements (sp's) are executed at no
> time at all.
> I've used sp_lock but there are no locks.
> Here's an extract from SQLProfiler:
> SQLTransaction Commit 6796 5 2005-03-04 15:30:27.887 2005-03-04
> 15:30:34.683 DTCXact 16335154 sa
> Any Ideas ?
> Regards Tommy S.
>|||Thanks,
I will try to check out the waittypes the next time I see a long running
transaction.
Yes, the log and the datafile are located on the same disk.
The CPU's (4 of them) are almost idle.
The problem is intermittent and it doesn't seem to be dependent on the
number of users, it can occur at early evening when the number of users
decrease significant.
/Tommy
"Olu Adedeji" wrote:
> Check out master..sysprocesses and find out what the waittypes are
> to determine what is causing the extended commit operation.
> is the Tlog file on the same disk as the data file . .how busy is the server?
> is this constantly reproduceable? or is it intermittent?
> HTH
> "Tommy SÃ¶derkvist" wrote:
> > Hi all,
> > we have a strange problem in SQL2000 SP3a with SQLTransaction's.
> > When running SQL Profiler the duration for some SQLTransactions (Commit) are
> > over six seconds, but the individual SQL statements (sp's) are executed at no
> > time at all.
> > I've used sp_lock but there are no locks.
> > Here's an extract from SQLProfiler:
> > SQLTransaction Commit 6796 5 2005-03-04 15:30:27.887 2005-03-04
> > 15:30:34.683 DTCXact 16335154 sa
> > Any Ideas ?
> > Regards Tommy S.
> >|||"Tommy SÃ¶derkvist" <TommySderkvist@.discussions.microsoft.com> schrieb im
Newsbeitrag news:72BF587D-E3CF-41FE-9EC8-5681C62E472B@.microsoft.com...
> Thanks,
> I will try to check out the waittypes the next time I see a long running
> transaction.
> Yes, the log and the datafile are located on the same disk.
> The CPU's (4 of them) are almost idle.
> The problem is intermittent and it doesn't seem to be dependent on the
> number of users, it can occur at early evening when the number of users
> decrease significant.
My guess would be that you have an IO bottleneck which makes tx log
writing slow. You could use perfmon to verify this theory.
robert
> /Tommy
> "Olu Adedeji" wrote:
> > Check out master..sysprocesses and find out what the waittypes are
> > to determine what is causing the extended commit operation.
> >
> > is the Tlog file on the same disk as the data file . .how busy is the
server?
> > is this constantly reproduceable? or is it intermittent?
> >
> > HTH
> >
> > "Tommy SÃ¶derkvist" wrote:
> >
> > > Hi all,
> > > we have a strange problem in SQL2000 SP3a with SQLTransaction's.
> > > When running SQL Profiler the duration for some SQLTransactions
(Commit) are
> > > over six seconds, but the individual SQL statements (sp's) are
executed at no
> > > time at all.
> > > I've used sp_lock but there are no locks.
> > > Here's an extract from SQLProfiler:
> > > SQLTransaction Commit 6796 5 2005-03-04 15:30:27.887 2005-03-04
> > > 15:30:34.683 DTCXact 16335154 sa
> > > Any Ideas ?
> > > Regards Tommy S.
> > >

Long running SQLTransaction

Wednesday, March 28, 2012

long query

My SQL server blocking for a while
After monitoring with SQL Profiler I found a lot of interesting situation:

query duration is 2045607 msec (=34 min)
CPU usage is 0 msec, number of reads is 3 and number of writes is 0

Where could be a bottleneck ?

Query is tooooooooo long but resorces usage seems to be OK.

Help... SOS ...What about locking?|||Originally posted by snail
What about locking?

It looks with no locks|||Post the query, please.

blindman|||Originally posted by blindman
Post the query, please.

blindman

This is one of many querys with long response

SELECT PPKZIP ,POPZIP ,Zupanija FROM AVEZIP ORDER BY PPKZIP ASC|||Originally posted by valentini
This is one of many querys with long response

SELECT PPKZIP ,POPZIP ,Zupanija FROM AVEZIP ORDER BY PPKZIP ASC

Nr of rows ?
DDL of the table ?
Any index ?|||Originally posted by fadace
Nr of rows ?
DDL of the table ?
Any index ?

Rows about 300
Index on first field - PPKZIP
Table with 3 fields|||Are you seriously saying that it is taking 34 minutes for SQL server to sort 300 rows on an indexed column?

Are there any other processes running? Check the database activity and check the status of any scheduled jobs.

How many users are on the system while this is happening?|||Originally posted by blindman
Are you seriously saying that it is taking 34 minutes for SQL server to sort 300 rows on an indexed column?

Are there any other processes running? Check the database activity and check the status of any scheduled jobs.

How many users are on the system while this is happening?
No scheduling jobs and no special database activity.
As you can see CPU is not used (0 msec)
But after 30 min SQL response with packet of data
Possible from cached memory.

But after 30 min ..............
Is there maybe problem in conlfict with some other transaction
problem with TCP/IP or with physical RAM

...|||What happens if you run the same statement directly from Query Analyzer on the database server?

Monday, March 26, 2012

Long duration Pre-Execute phase

Does anyone know why SSIS sometimes just sits in the Pre-Execute phase of a data flow and does nothing? It doesn't matter how elaborate the data flow is or the volume of data. It can sometimes take 80% of the task's run time.

Are you able to monitor activity on the source system? Perhaps the problem is there.

A shot in the dark - perhaps the problem is the one documented here: http://blogs.conchango.com/jamiethomson/archive/2006/02/21/2930.aspx

Just a thought!!!

-Jamie

|||

Another possibility is that the package has lookup with a large reference table and the lookup is charging its cache for all this time (charging the cache is done in PreExecute).

Thanks,

Matt

|||

I'm having the same trouble, but no lookups. First task in my package is a data flow, which consists of an OLE DB Source, Row Count transformation and a Flat File Destination. Source is a SELECT <fields> FROM <tables and views>, and should return about 2.1m rows. When I debug in BIDS, the task turns yellow on the "Control Flow" tab, but none of the components on the "Data Flow" tab turn yellow. Is it even executing? It must be doing something, because sp_lock and sp_who2 reveal locks being granted to that SPID. This query runs in about 35 mins in SQL Server Management Studio, but runs interminably (I killed it after 3 hours) in SSIS. Why? Help!

thanks,

Matt

|||the same problem here... packages that repitively get stuck in the pre-execution phase.

right after the status level doesn't rise any more, the server seems to take a deep nap: no activity shown in the system's performance monitor any longer!

and these are packages that have run until last week!

i haven't seen anything like that during my last 10 months of ssis-development. despair takes place, i just don't know what to do... any suggestions?

cheers,

frank

postscript. yes, there are lookups involved -- yet, kind of modest ones: six lookups à 16 rows...

|||got it. it's an issue of performance:

i've split up my puzzling task into two smaller tasks... and now, the package decently works again.

unfortunately, i've got the same problem in another package as well. there, it might be harder to find some solution.

i guess, the server's not properly configured.

|||

Read the thread in this forum titled "SSIS Performance 32 Bit V 64 Bit". This solved the problem for me.

To summarize, I'm developing on a 32-bit machine, hence using the 32-bit OLE DB data source driver. When that driver runs in the context of the 64-bit DTExec, I have these inexplicable performance problems with large and complex queries. When I shell out to call the 32-bit version of DTExec, it works like a champ.

Prefered fix of course is to develop on a 64-bit machine.

HTH,
Matt

|||

I had the same issue and it turned out to be parallelism. I set the maxdop 1 and havent had a problem since.

BobP

|||I have the same issue. I have a lookup component that is bound to a table in sql server database. The table has about 20*10^6 rows so it's pretty big, but I am not sure it is even executing the query since it doesn't display caching progress, just sits there in pre-execute. In my ohter packages I have other lookup components on the same table that work fine which confuses me even more. This package has a couple of more OLE DB sources connected to Oracle. Anyone have any idea what SSIS is doing while sitting in pre-execute?|||

dimaj79 wrote:
I have the same issue. I have a lookup component that is bound to a table in sql server database. The table has about 20*10^6 rows so it's pretty big, but I am not sure it is even executing the query since it doesn't display caching progress, just sits there in pre-execute. In my ohter packages I have other lookup components on the same table that work fine which confuses me even more. This package has a couple of more OLE DB sources connected to Oracle. Anyone have any idea what SSIS is doing while sitting in pre-execute?

You can check the source system to see if the query is being executed. If the source is SQL Server then use SQL Server Profiler. If its Oracle then I dare say they have something similar.

If I remember correctly you get information in your log provider when the cache is getting charged. So make sure you are logging OnInformation events.

-Jamie

|||

Ok, I have turned off caching in all of my lookups in that data flow task, and it still gets stuck in Pre-Execute phase. I am sure it's not executing data source queries since the debugger doesn't show them in yellow. When I said I wasn't seeing any caching progress I meant in the output window. I've turned off caching now so it shouldn't make a difference anyway.

I noticed someone mentioned performance issues and that breaking the task into multiple tasks solved it. Could you please elaborate?

If someone from Microsoft knows about issues with SSIS data flow tasks locking up due to some root problem deep inside SSIS or SQL Server please respond. Obviously people are finding different ways to get around it so there must be a root problem causing all of this. At least in future we'll know how to avoid it if not completely solve it. I would at least be happy to find out what it's doing in Pre-Execute phase that is taking it so long... That way we might think of a way to avoid it.

Like I said, the reference table is big, about 20mil rows, but lookup tasks have no caching set and there's no reason for the long pre-execute phase, unless it's doing something in the background we (developers) don't know about. Someone should respond explaining exactly what's taking place in the Pre-Execute phase.

|||

The task does not turn yellow when the query is running, just when rows start returning. I was burned by this also... The pre execute actually executes the query.

Look at your data source and see what queries are being run (Use profiler).

Then you can go from there.

BobP

|||

Ok, here's some more info on my long pre-execute phase:

I'have verified that it does in fact run the sql query against Oracle during the pre-execute phase. However, the SSIS task hangs in pre-execute at 80% even though the sql query has finished. I have verified that in Oracle session. When running the same SQL query manually it takes about 2 minutes, but the pre-execute phase in SSIS hangs and I've let it run for a lot longer than 2 minutes. Oracle session status for the session used to execute the SQL query from SSIS pre-execute phase states INACTIVE. My interpretation of this is that the query finished (as it should have, in about 2 minutes) but for some reason SSIS task never closes the connection.

My SSIS package consists of a foreach loop on a previously filled recordset variable, that contains a script task that prepares the sql query strings into variables and a data flow task that executes those sql queries against Oracle, transforms the data and loads it into a Sql Server database. I have two data source tasks in that data flow task. Both run in parallel executing a sql query against Oracle. I have verified that Oracle sessions remain open for both tasks, and their status is INACTIVE, even though the queries executed finish in about 2 minutes. Package hangs in pre-execute at 80%. The data source I use is Native OLEDB Oracle Provider from Oracle.

If anyone at Microsoft knows of a problem with using this package setup, or data extraction problems with Oracle, please respond.

I will try switching to Microsoft OLEDB Provider for Oracle to see if it makes any difference, and post the result here.

|||No change when using Microsoft OLEDB Provider for Oracle|||

Anyone?

Where are all the MVPs gone? Any clues? Or should I just write it off as another one of Microsoft's "bug/feature" things...

Long duration Pre-Execute phase

Are you able to monitor activity on the source system? Perhaps the problem is there.

A shot in the dark - perhaps the problem is the one documented here: http://blogs.conchango.com/jamiethomson/archive/2006/02/21/2930.aspx

Just a thought!!!

-Jamie

|||

Another possibility is that the package has lookup with a large reference table and the lookup is charging its cache for all this time (charging the cache is done in PreExecute).

Thanks,

Matt

|||

thanks,

Matt

|||the same problem here... packages that repitively get stuck in the pre-execution phase.

right after the status level doesn't rise any more, the server seems to take a deep nap: no activity shown in the system's performance monitor any longer!

and these are packages that have run until last week!

i haven't seen anything like that during my last 10 months of ssis-development. despair takes place, i just don't know what to do... any suggestions?

cheers,

frank

postscript. yes, there are lookups involved -- yet, kind of modest ones: six lookups à 16 rows...

|||got it. it's an issue of performance:

i've split up my puzzling task into two smaller tasks... and now, the package decently works again.

unfortunately, i've got the same problem in another package as well. there, it might be harder to find some solution.

i guess, the server's not properly configured.

|||

Read the thread in this forum titled "SSIS Performance 32 Bit V 64 Bit". This solved the problem for me.

Prefered fix of course is to develop on a 64-bit machine.

HTH,
Matt

|||

I had the same issue and it turned out to be parallelism. I set the maxdop 1 and havent had a problem since.

BobP

dimaj79 wrote:
I have the same issue. I have a lookup component that is bound to a table in sql server database. The table has about 20*10^6 rows so it's pretty big, but I am not sure it is even executing the query since it doesn't display caching progress, just sits there in pre-execute. In my ohter packages I have other lookup components on the same table that work fine which confuses me even more. This package has a couple of more OLE DB sources connected to Oracle. Anyone have any idea what SSIS is doing while sitting in pre-execute?

You can check the source system to see if the query is being executed. If the source is SQL Server then use SQL Server Profiler. If its Oracle then I dare say they have something similar.

If I remember correctly you get information in your log provider when the cache is getting charged. So make sure you are logging OnInformation events.

-Jamie

|||

I noticed someone mentioned performance issues and that breaking the task into multiple tasks solved it. Could you please elaborate?

|||

The task does not turn yellow when the query is running, just when rows start returning. I was burned by this also... The pre execute actually executes the query.

Look at your data source and see what queries are being run (Use profiler).

Then you can go from there.

BobP

|||

Ok, here's some more info on my long pre-execute phase:

If anyone at Microsoft knows of a problem with using this package setup, or data extraction problems with Oracle, please respond.

I will try switching to Microsoft OLEDB Provider for Oracle to see if it makes any difference, and post the result here.

|||No change when using Microsoft OLEDB Provider for Oracle|||

Anyone?

Where are all the MVPs gone? Any clues? Or should I just write it off as another one of Microsoft's "bug/feature" things...

sql

Long duration Pre-Execute phase

Are you able to monitor activity on the source system? Perhaps the problem is there.

A shot in the dark - perhaps the problem is the one documented here: http://blogs.conchango.com/jamiethomson/archive/2006/02/21/2930.aspx

Just a thought!!!

-Jamie

|||

Another possibility is that the package has lookup with a large reference table and the lookup is charging its cache for all this time (charging the cache is done in PreExecute).

Thanks,

Matt

|||

thanks,

Matt

|||the same problem here... packages that repitively get stuck in the pre-execution phase.

right after the status level doesn't rise any more, the server seems to take a deep nap: no activity shown in the system's performance monitor any longer!

and these are packages that have run until last week!

i haven't seen anything like that during my last 10 months of ssis-development. despair takes place, i just don't know what to do... any suggestions?

cheers,

frank

postscript. yes, there are lookups involved -- yet, kind of modest ones: six lookups à 16 rows...

|||got it. it's an issue of performance:

i've split up my puzzling task into two smaller tasks... and now, the package decently works again.

unfortunately, i've got the same problem in another package as well. there, it might be harder to find some solution.

i guess, the server's not properly configured.

|||

Read the thread in this forum titled "SSIS Performance 32 Bit V 64 Bit". This solved the problem for me.

Prefered fix of course is to develop on a 64-bit machine.

HTH,
Matt

|||

I had the same issue and it turned out to be parallelism. I set the maxdop 1 and havent had a problem since.

BobP

dimaj79 wrote:
I have the same issue. I have a lookup component that is bound to a table in sql server database. The table has about 20*10^6 rows so it's pretty big, but I am not sure it is even executing the query since it doesn't display caching progress, just sits there in pre-execute. In my ohter packages I have other lookup components on the same table that work fine which confuses me even more. This package has a couple of more OLE DB sources connected to Oracle. Anyone have any idea what SSIS is doing while sitting in pre-execute?

You can check the source system to see if the query is being executed. If the source is SQL Server then use SQL Server Profiler. If its Oracle then I dare say they have something similar.

If I remember correctly you get information in your log provider when the cache is getting charged. So make sure you are logging OnInformation events.

-Jamie

|||

I noticed someone mentioned performance issues and that breaking the task into multiple tasks solved it. Could you please elaborate?

|||

The task does not turn yellow when the query is running, just when rows start returning. I was burned by this also... The pre execute actually executes the query.

Look at your data source and see what queries are being run (Use profiler).

Then you can go from there.

BobP

|||

Ok, here's some more info on my long pre-execute phase:

If anyone at Microsoft knows of a problem with using this package setup, or data extraction problems with Oracle, please respond.

I will try switching to Microsoft OLEDB Provider for Oracle to see if it makes any difference, and post the result here.

|||No change when using Microsoft OLEDB Provider for Oracle|||

Anyone?

Where are all the MVPs gone? Any clues? Or should I just write it off as another one of Microsoft's "bug/feature" things...

Long duration Pre-Execute phase

Are you able to monitor activity on the source system? Perhaps the problem is there.

A shot in the dark - perhaps the problem is the one documented here: http://blogs.conchango.com/jamiethomson/archive/2006/02/21/2930.aspx

Just a thought!!!

-Jamie

|||

Another possibility is that the package has lookup with a large reference table and the lookup is charging its cache for all this time (charging the cache is done in PreExecute).

Thanks,

Matt

|||

thanks,

Matt

|||the same problem here... packages that repitively get stuck in the pre-execution phase.

right after the status level doesn't rise any more, the server seems to take a deep nap: no activity shown in the system's performance monitor any longer!

and these are packages that have run until last week!

i haven't seen anything like that during my last 10 months of ssis-development. despair takes place, i just don't know what to do... any suggestions?

cheers,

frank

postscript. yes, there are lookups involved -- yet, kind of modest ones: six lookups à 16 rows...

|||got it. it's an issue of performance:

i've split up my puzzling task into two smaller tasks... and now, the package decently works again.

unfortunately, i've got the same problem in another package as well. there, it might be harder to find some solution.

i guess, the server's not properly configured.

|||

Read the thread in this forum titled "SSIS Performance 32 Bit V 64 Bit". This solved the problem for me.

Prefered fix of course is to develop on a 64-bit machine.

HTH,
Matt

|||

I had the same issue and it turned out to be parallelism. I set the maxdop 1 and havent had a problem since.

BobP

dimaj79 wrote:
I have the same issue. I have a lookup component that is bound to a table in sql server database. The table has about 20*10^6 rows so it's pretty big, but I am not sure it is even executing the query since it doesn't display caching progress, just sits there in pre-execute. In my ohter packages I have other lookup components on the same table that work fine which confuses me even more. This package has a couple of more OLE DB sources connected to Oracle. Anyone have any idea what SSIS is doing while sitting in pre-execute?

You can check the source system to see if the query is being executed. If the source is SQL Server then use SQL Server Profiler. If its Oracle then I dare say they have something similar.

If I remember correctly you get information in your log provider when the cache is getting charged. So make sure you are logging OnInformation events.

-Jamie

|||

I noticed someone mentioned performance issues and that breaking the task into multiple tasks solved it. Could you please elaborate?

|||

The task does not turn yellow when the query is running, just when rows start returning. I was burned by this also... The pre execute actually executes the query.

Look at your data source and see what queries are being run (Use profiler).

Then you can go from there.

BobP

|||

Ok, here's some more info on my long pre-execute phase:

If anyone at Microsoft knows of a problem with using this package setup, or data extraction problems with Oracle, please respond.

I will try switching to Microsoft OLEDB Provider for Oracle to see if it makes any difference, and post the result here.

|||No change when using Microsoft OLEDB Provider for Oracle|||

Anyone?

Where are all the MVPs gone? Any clues? Or should I just write it off as another one of Microsoft's "bug/feature" things...

Long duration Pre-Execute phase

Are you able to monitor activity on the source system? Perhaps the problem is there.

A shot in the dark - perhaps the problem is the one documented here: http://blogs.conchango.com/jamiethomson/archive/2006/02/21/2930.aspx

Just a thought!!!

-Jamie

|||

Another possibility is that the package has lookup with a large reference table and the lookup is charging its cache for all this time (charging the cache is done in PreExecute).

Thanks,

Matt

|||

thanks,

Matt

|||the same problem here... packages that repitively get stuck in the pre-execution phase.

right after the status level doesn't rise any more, the server seems to take a deep nap: no activity shown in the system's performance monitor any longer!

and these are packages that have run until last week!

i haven't seen anything like that during my last 10 months of ssis-development. despair takes place, i just don't know what to do... any suggestions?

cheers,

frank

postscript. yes, there are lookups involved -- yet, kind of modest ones: six lookups à 16 rows...

|||got it. it's an issue of performance:

i've split up my puzzling task into two smaller tasks... and now, the package decently works again.

unfortunately, i've got the same problem in another package as well. there, it might be harder to find some solution.

i guess, the server's not properly configured.

|||

Read the thread in this forum titled "SSIS Performance 32 Bit V 64 Bit". This solved the problem for me.

Prefered fix of course is to develop on a 64-bit machine.

HTH,
Matt

|||

I had the same issue and it turned out to be parallelism. I set the maxdop 1 and havent had a problem since.

BobP

dimaj79 wrote:
I have the same issue. I have a lookup component that is bound to a table in sql server database. The table has about 20*10^6 rows so it's pretty big, but I am not sure it is even executing the query since it doesn't display caching progress, just sits there in pre-execute. In my ohter packages I have other lookup components on the same table that work fine which confuses me even more. This package has a couple of more OLE DB sources connected to Oracle. Anyone have any idea what SSIS is doing while sitting in pre-execute?

You can check the source system to see if the query is being executed. If the source is SQL Server then use SQL Server Profiler. If its Oracle then I dare say they have something similar.

If I remember correctly you get information in your log provider when the cache is getting charged. So make sure you are logging OnInformation events.

-Jamie

|||

I noticed someone mentioned performance issues and that breaking the task into multiple tasks solved it. Could you please elaborate?

|||

The task does not turn yellow when the query is running, just when rows start returning. I was burned by this also... The pre execute actually executes the query.

Look at your data source and see what queries are being run (Use profiler).

Then you can go from there.

BobP

|||

Ok, here's some more info on my long pre-execute phase:

If anyone at Microsoft knows of a problem with using this package setup, or data extraction problems with Oracle, please respond.

I will try switching to Microsoft OLEDB Provider for Oracle to see if it makes any difference, and post the result here.

|||No change when using Microsoft OLEDB Provider for Oracle|||

Anyone?

Where are all the MVPs gone? Any clues? Or should I just write it off as another one of Microsoft's "bug/feature" things...

Long duration Pre-Execute phase

Are you able to monitor activity on the source system? Perhaps the problem is there.

A shot in the dark - perhaps the problem is the one documented here: http://blogs.conchango.com/jamiethomson/archive/2006/02/21/2930.aspx

Just a thought!!!

-Jamie

|||

Another possibility is that the package has lookup with a large reference table and the lookup is charging its cache for all this time (charging the cache is done in PreExecute).

Thanks,

Matt

|||

thanks,

Matt

|||the same problem here... packages that repitively get stuck in the pre-execution phase.

right after the status level doesn't rise any more, the server seems to take a deep nap: no activity shown in the system's performance monitor any longer!

and these are packages that have run until last week!

i haven't seen anything like that during my last 10 months of ssis-development. despair takes place, i just don't know what to do... any suggestions?

cheers,

frank

postscript. yes, there are lookups involved -- yet, kind of modest ones: six lookups à 16 rows...

|||got it. it's an issue of performance:

i've split up my puzzling task into two smaller tasks... and now, the package decently works again.

unfortunately, i've got the same problem in another package as well. there, it might be harder to find some solution.

i guess, the server's not properly configured.

|||

Read the thread in this forum titled "SSIS Performance 32 Bit V 64 Bit". This solved the problem for me.

Prefered fix of course is to develop on a 64-bit machine.

HTH,
Matt

|||

I had the same issue and it turned out to be parallelism. I set the maxdop 1 and havent had a problem since.

BobP

dimaj79 wrote:
I have the same issue. I have a lookup component that is bound to a table in sql server database. The table has about 20*10^6 rows so it's pretty big, but I am not sure it is even executing the query since it doesn't display caching progress, just sits there in pre-execute. In my ohter packages I have other lookup components on the same table that work fine which confuses me even more. This package has a couple of more OLE DB sources connected to Oracle. Anyone have any idea what SSIS is doing while sitting in pre-execute?

You can check the source system to see if the query is being executed. If the source is SQL Server then use SQL Server Profiler. If its Oracle then I dare say they have something similar.

If I remember correctly you get information in your log provider when the cache is getting charged. So make sure you are logging OnInformation events.

-Jamie

|||

I noticed someone mentioned performance issues and that breaking the task into multiple tasks solved it. Could you please elaborate?

|||

The task does not turn yellow when the query is running, just when rows start returning. I was burned by this also... The pre execute actually executes the query.

Look at your data source and see what queries are being run (Use profiler).

Then you can go from there.

BobP

|||

Ok, here's some more info on my long pre-execute phase:

If anyone at Microsoft knows of a problem with using this package setup, or data extraction problems with Oracle, please respond.

I will try switching to Microsoft OLEDB Provider for Oracle to see if it makes any difference, and post the result here.

|||No change when using Microsoft OLEDB Provider for Oracle|||

Anyone?

Where are all the MVPs gone? Any clues? Or should I just write it off as another one of Microsoft's "bug/feature" things...

Long duration Pre-Execute phase

Are you able to monitor activity on the source system? Perhaps the problem is there.

A shot in the dark - perhaps the problem is the one documented here: http://blogs.conchango.com/jamiethomson/archive/2006/02/21/2930.aspx

Just a thought!!!

-Jamie

|||

Another possibility is that the package has lookup with a large reference table and the lookup is charging its cache for all this time (charging the cache is done in PreExecute).

Thanks,

Matt

|||

thanks,

Matt

|||the same problem here... packages that repitively get stuck in the pre-execution phase.

right after the status level doesn't rise any more, the server seems to take a deep nap: no activity shown in the system's performance monitor any longer!

and these are packages that have run until last week!

i haven't seen anything like that during my last 10 months of ssis-development. despair takes place, i just don't know what to do... any suggestions?

cheers,

frank

postscript. yes, there are lookups involved -- yet, kind of modest ones: six lookups à 16 rows...

|||got it. it's an issue of performance:

i've split up my puzzling task into two smaller tasks... and now, the package decently works again.

unfortunately, i've got the same problem in another package as well. there, it might be harder to find some solution.

i guess, the server's not properly configured.

|||

Read the thread in this forum titled "SSIS Performance 32 Bit V 64 Bit". This solved the problem for me.

Prefered fix of course is to develop on a 64-bit machine.

HTH,
Matt

|||

I had the same issue and it turned out to be parallelism. I set the maxdop 1 and havent had a problem since.

BobP

dimaj79 wrote:
I have the same issue. I have a lookup component that is bound to a table in sql server database. The table has about 20*10^6 rows so it's pretty big, but I am not sure it is even executing the query since it doesn't display caching progress, just sits there in pre-execute. In my ohter packages I have other lookup components on the same table that work fine which confuses me even more. This package has a couple of more OLE DB sources connected to Oracle. Anyone have any idea what SSIS is doing while sitting in pre-execute?

You can check the source system to see if the query is being executed. If the source is SQL Server then use SQL Server Profiler. If its Oracle then I dare say they have something similar.

If I remember correctly you get information in your log provider when the cache is getting charged. So make sure you are logging OnInformation events.

-Jamie

|||

I noticed someone mentioned performance issues and that breaking the task into multiple tasks solved it. Could you please elaborate?

|||

The task does not turn yellow when the query is running, just when rows start returning. I was burned by this also... The pre execute actually executes the query.

Look at your data source and see what queries are being run (Use profiler).

Then you can go from there.

BobP

|||

Ok, here's some more info on my long pre-execute phase:

If anyone at Microsoft knows of a problem with using this package setup, or data extraction problems with Oracle, please respond.

I will try switching to Microsoft OLEDB Provider for Oracle to see if it makes any difference, and post the result here.

|||No change when using Microsoft OLEDB Provider for Oracle|||

Anyone?

Where are all the MVPs gone? Any clues? Or should I just write it off as another one of Microsoft's "bug/feature" things...

sql

long backup duration

I recently moved my backups from the wizard generated maintenance plans to a
simple backup script. My 180GB database is now taking 7 hours to backup with
instead of the normal 4 hours. The step details show 7.359 MB/sec when the
backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
hours.
Below is my script. This is actually step 2. Step 1 deletes the previous
backup.
BACKUP DATABASE MyDB
TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
WITH INIT
During the backup, pinging the NAS device from the SQL Server takes 0ms.
I have used Perfmon and the counters seem usual from what I have researched
(e.g., http://www.sql-server-performance.co...ore_tuning.asp).
Average Device Throughput bytes/sec: 5570346
Average % Disk Time: 3.854
Average Disk Queue Length: .0193
Average IO Write Bytes/sec: 5897638
Average Split IO/sec: 0
As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
Alert Engine.
No compression is taking place on either machine.
Does anyone have any suggestions as what is causing the backup time to
almost double?
Thanks,
ray
SS2K
Hi,
Was your Wizard generated backup maintenance plan overwriting the same
backup file each time or writing to a new file each time? The only
thing I can think of is that if the backup file already exists and you
are re-initializing it takes less time. From an OS/Network standpoint
that should not hinder performance. Perhaps you can look at the wizard
generated backup plan to see if there are some option enabled by SQL
Server which may increase performance.
Shahryar
raybouk wrote:

>I recently moved my backups from the wizard generated maintenance plans to a
>simple backup script. My 180GB database is now taking 7 hours to backup with
>instead of the normal 4 hours. The step details show 7.359 MB/sec when the
>backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
>hours.
>Below is my script. This is actually step 2. Step 1 deletes the previous
>backup.
>BACKUP DATABASE MyDB
>TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
>WITH INIT
>During the backup, pinging the NAS device from the SQL Server takes 0ms.
>I have used Perfmon and the counters seem usual from what I have researched
>(e.g., http://www.sql-server-performance.co...ore_tuning.asp).
>Average Device Throughput bytes/sec: 5570346
>Average % Disk Time: 3.854
>Average Disk Queue Length: .0193
>Average IO Write Bytes/sec: 5897638
>Average Split IO/sec: 0
>As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
>Alert Engine.
>No compression is taking place on either machine.
>Does anyone have any suggestions as what is causing the backup time to
>almost double?
>Thanks,
>ray
>SS2K
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohi
bited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.
|||Delete the old backup file prior to performing the backup. Dont init the new
backup device. I found this to work the best.
On a sidenote, ping rates do not necessarily reflect thoughput performance.
"raybouk" wrote:

> I recently moved my backups from the wizard generated maintenance plans to a
> simple backup script. My 180GB database is now taking 7 hours to backup with
> instead of the normal 4 hours. The step details show 7.359 MB/sec when the
> backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
> hours.
> Below is my script. This is actually step 2. Step 1 deletes the previous
> backup.
> BACKUP DATABASE MyDB
> TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
> WITH INIT
> During the backup, pinging the NAS device from the SQL Server takes 0ms.
> I have used Perfmon and the counters seem usual from what I have researched
> (e.g., http://www.sql-server-performance.co...ore_tuning.asp).
> Average Device Throughput bytes/sec: 5570346
> Average % Disk Time: 3.854
> Average Disk Queue Length: .0193
> Average IO Write Bytes/sec: 5897638
> Average Split IO/sec: 0
> As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
> Alert Engine.
> No compression is taking place on either machine.
> Does anyone have any suggestions as what is causing the backup time to
> almost double?
> Thanks,
> ray
> SS2K
|||What was the destination of the Wizard generated backups?
There is going to be a world of difference between writing to a local disk
vs going to a NAS device.
You should be able to go into the Management Studio and look at the script
that it generates.
See what the differences are in the backup statement.
"Raoul Laoyan" <RaoulLaoyan@.discussions.microsoft.com> wrote in message
news:0C4A91B3-D06B-40DA-87EE-1D94A94A707F@.microsoft.com...[vbcol=seagreen]
> Delete the old backup file prior to performing the backup. Dont init the
> new
> backup device. I found this to work the best.
> On a sidenote, ping rates do not necessarily reflect thoughput
> performance.
> "raybouk" wrote:

long backup duration

I recently moved my backups from the wizard generated maintenance plans to a
simple backup script. My 180GB database is now taking 7 hours to backup with
instead of the normal 4 hours. The step details show 7.359 MB/sec when the
backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
hours.
Below is my script. This is actually step 2. Step 1 deletes the previous
backup.
BACKUP DATABASE MyDB
TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
WITH INIT
During the backup, pinging the NAS device from the SQL Server takes 0ms.
I have used Perfmon and the counters seem usual from what I have researched
(e.g., http://www.sql-server-performance.c...tore_tuning.asp).
Average Device Throughput bytes/sec: 5570346
Average % Disk Time: 3.854
Average Disk Queue Length: .0193
Average IO Write Bytes/sec: 5897638
Average Split IO/sec: 0
As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
Alert Engine.
No compression is taking place on either machine.
Does anyone have any suggestions as what is causing the backup time to
almost double?
Thanks,
ray
SS2KHi,
Was your Wizard generated backup maintenance plan overwriting the same
backup file each time or writing to a new file each time? The only
thing I can think of is that if the backup file already exists and you
are re-initializing it takes less time. From an OS/Network standpoint
that should not hinder performance. Perhaps you can look at the wizard
generated backup plan to see if there are some option enabled by SQL
Server which may increase performance.
Shahryar
raybouk wrote:

>I recently moved my backups from the wizard generated maintenance plans to
a
>simple backup script. My 180GB database is now taking 7 hours to backup wit
h
>instead of the normal 4 hours. The step details show 7.359 MB/sec when the
>backup completes in 7 hours and 12.453 MB/sec when the backup completes in
4
>hours.
>Below is my script. This is actually step 2. Step 1 deletes the previous
>backup.
>BACKUP DATABASE MyDB
>TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
>WITH INIT
>During the backup, pinging the NAS device from the SQL Server takes 0ms.
>I have used Perfmon and the counters seem usual from what I have researched
>(e.g., http://www.sql-server-performance.c...tore_tuning.asp).
>Average Device Throughput bytes/sec: 5570346
>Average % Disk Time: 3.854
>Average Disk Queue Length: .0193
>Average IO Write Bytes/sec: 5897638
>Average Split IO/sec: 0
>As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent
-
>Alert Engine.
>No compression is taking place on either machine.
>Does anyone have any suggestions as what is causing the backup time to
>almost double?
>Thanks,
>ray
>SS2K
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is
legally privileged. The information is solely for the use of the intended
recipient(s); any disclosure, copying, distribution, or other use of this in
formation is strictly prohi
bited. If you have received this e-mail in error, please notify the sender
by return e-mail and delete this message. Thank you.|||Delete the old backup file prior to performing the backup. Dont init the ne
w
backup device. I found this to work the best.
On a sidenote, ping rates do not necessarily reflect thoughput performance.
"raybouk" wrote:

> I recently moved my backups from the wizard generated maintenance plans to
a
> simple backup script. My 180GB database is now taking 7 hours to backup wi
th
> instead of the normal 4 hours. The step details show 7.359 MB/sec when the
> backup completes in 7 hours and 12.453 MB/sec when the backup completes in
4
> hours.
> Below is my script. This is actually step 2. Step 1 deletes the previous
> backup.
> BACKUP DATABASE MyDB
> TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
> WITH INIT
> During the backup, pinging the NAS device from the SQL Server takes 0ms.
> I have used Perfmon and the counters seem usual from what I have researche
d
> (e.g., http://www.sql-server-performance.c...tore_tuning.asp).
> Average Device Throughput bytes/sec: 5570346
> Average % Disk Time: 3.854
> Average Disk Queue Length: .0193
> Average IO Write Bytes/sec: 5897638
> Average Split IO/sec: 0
> As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent
-
> Alert Engine.
> No compression is taking place on either machine.
> Does anyone have any suggestions as what is causing the backup time to
> almost double?
> Thanks,
> ray
> SS2K|||What was the destination of the Wizard generated backups?
There is going to be a world of difference between writing to a local disk
vs going to a NAS device.
You should be able to go into the Management Studio and look at the script
that it generates.
See what the differences are in the backup statement.
"Raoul Laoyan" <RaoulLaoyan@.discussions.microsoft.com> wrote in message
news:0C4A91B3-D06B-40DA-87EE-1D94A94A707F@.microsoft.com...[vbcol=seagreen]
> Delete the old backup file prior to performing the backup. Dont init the
> new
> backup device. I found this to work the best.
> On a sidenote, ping rates do not necessarily reflect thoughput
> performance.
> "raybouk" wrote:
>

long backup duration

I recently moved my backups from the wizard generated maintenance plans to a
simple backup script. My 180GB database is now taking 7 hours to backup with
instead of the normal 4 hours. The step details show 7.359 MB/sec when the
backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
hours.
Below is my script. This is actually step 2. Step 1 deletes the previous
backup.
BACKUP DATABASE MyDB
TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
WITH INIT
During the backup, pinging the NAS device from the SQL Server takes 0ms.
I have used Perfmon and the counters seem usual from what I have researched
(e.g., http://www.sql-server-performance.com/backup_restore_tuning.asp).
Average Device Throughput bytes/sec: 5570346
Average % Disk Time: 3.854
Average Disk Queue Length: .0193
Average IO Write Bytes/sec: 5897638
Average Split IO/sec: 0
As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
Alert Engine.
No compression is taking place on either machine.
Does anyone have any suggestions as what is causing the backup time to
almost double?
Thanks,
ray
SS2KHi,
Was your Wizard generated backup maintenance plan overwriting the same
backup file each time or writing to a new file each time? The only
thing I can think of is that if the backup file already exists and you
are re-initializing it takes less time. From an OS/Network standpoint
that should not hinder performance. Perhaps you can look at the wizard
generated backup plan to see if there are some option enabled by SQL
Server which may increase performance.
Shahryar
raybouk wrote:
>I recently moved my backups from the wizard generated maintenance plans to a
>simple backup script. My 180GB database is now taking 7 hours to backup with
>instead of the normal 4 hours. The step details show 7.359 MB/sec when the
>backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
>hours.
>Below is my script. This is actually step 2. Step 1 deletes the previous
>backup.
>BACKUP DATABASE MyDB
>TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
>WITH INIT
>During the backup, pinging the NAS device from the SQL Server takes 0ms.
>I have used Perfmon and the counters seem usual from what I have researched
>(e.g., http://www.sql-server-performance.com/backup_restore_tuning.asp).
>Average Device Throughput bytes/sec: 5570346
>Average % Disk Time: 3.854
>Average Disk Queue Length: .0193
>Average IO Write Bytes/sec: 5897638
>Average Split IO/sec: 0
>As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
>Alert Engine.
>No compression is taking place on either machine.
>Does anyone have any suggestions as what is causing the backup time to
>almost double?
>Thanks,
>ray
>SS2K
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.|||Delete the old backup file prior to performing the backup. Dont init the new
backup device. I found this to work the best.
On a sidenote, ping rates do not necessarily reflect thoughput performance.
"raybouk" wrote:
> I recently moved my backups from the wizard generated maintenance plans to a
> simple backup script. My 180GB database is now taking 7 hours to backup with
> instead of the normal 4 hours. The step details show 7.359 MB/sec when the
> backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
> hours.
> Below is my script. This is actually step 2. Step 1 deletes the previous
> backup.
> BACKUP DATABASE MyDB
> TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
> WITH INIT
> During the backup, pinging the NAS device from the SQL Server takes 0ms.
> I have used Perfmon and the counters seem usual from what I have researched
> (e.g., http://www.sql-server-performance.com/backup_restore_tuning.asp).
> Average Device Throughput bytes/sec: 5570346
> Average % Disk Time: 3.854
> Average Disk Queue Length: .0193
> Average IO Write Bytes/sec: 5897638
> Average Split IO/sec: 0
> As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
> Alert Engine.
> No compression is taking place on either machine.
> Does anyone have any suggestions as what is causing the backup time to
> almost double?
> Thanks,
> ray
> SS2K|||What was the destination of the Wizard generated backups?
There is going to be a world of difference between writing to a local disk
vs going to a NAS device.
You should be able to go into the Management Studio and look at the script
that it generates.
See what the differences are in the backup statement.
"Raoul Laoyan" <RaoulLaoyan@.discussions.microsoft.com> wrote in message
news:0C4A91B3-D06B-40DA-87EE-1D94A94A707F@.microsoft.com...
> Delete the old backup file prior to performing the backup. Dont init the
> new
> backup device. I found this to work the best.
> On a sidenote, ping rates do not necessarily reflect thoughput
> performance.
> "raybouk" wrote:
>> I recently moved my backups from the wizard generated maintenance plans
>> to a
>> simple backup script. My 180GB database is now taking 7 hours to backup
>> with
>> instead of the normal 4 hours. The step details show 7.359 MB/sec when
>> the
>> backup completes in 7 hours and 12.453 MB/sec when the backup completes
>> in 4
>> hours.
>> Below is my script. This is actually step 2. Step 1 deletes the previous
>> backup.
>> BACKUP DATABASE MyDB
>> TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
>> WITH INIT
>> During the backup, pinging the NAS device from the SQL Server takes 0ms.
>> I have used Perfmon and the counters seem usual from what I have
>> researched
>> (e.g., http://www.sql-server-performance.com/backup_restore_tuning.asp).
>> Average Device Throughput bytes/sec: 5570346
>> Average % Disk Time: 3.854
>> Average Disk Queue Length: .0193
>> Average IO Write Bytes/sec: 5897638
>> Average Split IO/sec: 0
>> As for sp_who2, the only thing peculiar is a DISKIO of 875659 for
>> SQLAgent -
>> Alert Engine.
>> No compression is taking place on either machine.
>> Does anyone have any suggestions as what is causing the backup time to
>> almost double?
>> Thanks,
>> ray
>> SS2K

Friday, March 30, 2012

Long running SQLTransaction

Long running SQLTransaction

Long running SQLTransaction

Wednesday, March 28, 2012

long query

Monday, March 26, 2012

Long duration Pre-Execute phase

Long duration Pre-Execute phase

Long duration Pre-Execute phase

Long duration Pre-Execute phase

Long duration Pre-Execute phase

Long duration Pre-Execute phase

Long duration Pre-Execute phase

long backup duration

long backup duration

long backup duration

sql login permissions

Blog Archive

About Me