Hi All,

     First of all i am beginner to SSIS in MS SQL.
     I have an issue with while running SSIS packages in  SQL Serer 2008. 
While excecuting the  SSIS packages, all the other applications accessing
this SQL Serer 2008 database  will become slow in process even if both SSIS
packages and applications are accessing  different databases.
I am curious know the reason for this.
So awaiting for your Reply.
Let me know if you need more information to answer this.
Thanks in advance and Regards,
SQL USER

> While excecuting the  SSIS packages, all the other applications accessing
> this SQL Serer 2008 database  will become slow in process even if both 
> SSIS
> packages and applications are accessing  different databases.
> I am curious know the reason for this.

The likely cause is that SQL Server and SSIS are competing for the same CPU 
and memory resources.  If the package uses a lot of memory (e.g. large 
in-memory lookups) or CPU (e.g. intensive transformations in parallel), then 
SQL Server response time will be impacted.

When you run applications (including SSIS) on the same box as the database 
server, consider setting max SQL Server memory to prevent thrashing.  Also, 
consider limiting the number of parallel tasks in the package and/or run 
during off-peak hours.

-- 
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"prema" <u60348@uwe> wrote in message news:a89106fb51dfb@uwe...
> Hi All,
>
>     First of all i am beginner to SSIS in MS SQL.
>     I have an issue with while running SSIS packages in  SQL Serer 2008.
> While excecuting the  SSIS packages, all the other applications accessing
> this SQL Serer 2008 database  will become slow in process even if both 
> SSIS
> packages and applications are accessing  different databases.
> I am curious know the reason for this.
> So awaiting for your Reply.
> Let me know if you need more information to answer this.
> Thanks in advance and Regards,
> SQL USER
> 

Thanks a lot .

Dan Guzman wrote:
>> While excecuting the  SSIS packages, all the other applications accessing
>> this SQL Serer 2008 database  will become slow in process even if both 
>> SSIS
>> packages and applications are accessing  different databases.
>> I am curious know the reason for this.
>
>The likely cause is that SQL Server and SSIS are competing for the same CPU 
>and memory resources.  If the package uses a lot of memory (e.g. large 
>in-memory lookups) or CPU (e.g. intensive transformations in parallel), then 
>SQL Server response time will be impacted.
>
>When you run applications (including SSIS) on the same box as the database 
>server, consider setting max SQL Server memory to prevent thrashing.  Also, 
>consider limiting the number of parallel tasks in the package and/or run 
>during off-peak hours.
>
>> Hi All,
>>
>[quoted text clipped - 9 lines]
>> Thanks in advance and Regards,
>> SQL USER

-- 
Message posted via http://www.sqlmonster.com

Hi again,

 I need some more information for my below query if you can provide.
 My SQL DB (SQL SERVER 2008 complete Package)  is installed in Windows Server
2008 (Service  Pack 1) of type 64-bit OS and I am using 32GB of RAM. 
But the application is Installed in other Box and  not the one where this SQL
is installed. And this application is accessing  SQL as a backend only. 
But when you run SSIS packages in SQL SERVER 2008 (local machine) the
application(s) that is/are installed in other box(es)  accessing this SQL as
a backend, will become slow in process.
I have set the SQL memory to maximum and still facing the same problem.
Any help for this issue is highly appreciated.

Thanks for your usual help.
SQL USER


   We are using Data
Dan Guzman wrote:
>> While excecuting the  SSIS packages, all the other applications accessing
>> this SQL Serer 2008 database  will become slow in process even if both 
>> SSIS
>> packages and applications are accessing  different databases.
>> I am curious know the reason for this.
>
>The likely cause is that SQL Server and SSIS are competing for the same CPU 
>and memory resources.  If the package uses a lot of memory (e.g. large 
>in-memory lookups) or CPU (e.g. intensive transformations in parallel), then 
>SQL Server response time will be impacted.
>
>When you run applications (including SSIS) on the same box as the database 
>server, consider setting max SQL Server memory to prevent thrashing.  Also, 
>consider limiting the number of parallel tasks in the package and/or run 
>during off-peak hours.
>
>> Hi All,
>>
>[quoted text clipped - 9 lines]
>> Thanks in advance and Regards,
>> SQL USER

-- 
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201005/1

Run Performance Monitor against the server while the package is running. 
The key metrics to monitor are:

Memory:Pages/sec:  This should average at or near zero, with occasional 
spikes.  If you see a lot of paging, reduce SQL Server Max memory so that 
there is enough memory for SSIS and SQL Server to run concurrently without 
paging.

Processor:% Processor Time:  Verify all CPUs are not at capacity.  If CPU is 
high due to SSIS queries, perform query and index tuning.  If you have 
parallel plans, consider adding the OPTION (MAXDOP 1) to reduce processor 
utilization in deference to OLTP performance.

Physical Disk:Avg. Disk Queue Length:  This should average under 2 times the 
number of physical spindles.  Query and index tuning of SSIS queries can 
mitigate I/O bottlenecks.

If your SSIS package performs a lot of inserts or updates, ensure log files 
are on different physical disks than the data files.


-- 
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"prema via SQLMonster.com" <u60348@uwe> wrote in message 
news:a8d1c26d7b9a5@uwe...
> Hi again,
>
> I need some more information for my below query if you can provide.
> My SQL DB (SQL SERVER 2008 complete Package)  is installed in Windows 
> Server
> 2008 (Service  Pack 1) of type 64-bit OS and I am using 32GB of RAM.
> But the application is Installed in other Box and  not the one where this 
> SQL
> is installed. And this application is accessing  SQL as a backend only.
> But when you run SSIS packages in SQL SERVER 2008 (local machine) the
> application(s) that is/are installed in other box(es)  accessing this SQL 
> as
> a backend, will become slow in process.
> I have set the SQL memory to maximum and still facing the same problem.
> Any help for this issue is highly appreciated.
>
> Thanks for your usual help.
> SQL USER
>
>
>   We are using Data
> Dan Guzman wrote:
>>> While excecuting the  SSIS packages, all the other applications 
>>> accessing
>>> this SQL Serer 2008 database  will become slow in process even if both
>>> SSIS
>>> packages and applications are accessing  different databases.
>>> I am curious know the reason for this.
>>
>>The likely cause is that SQL Server and SSIS are competing for the same 
>>CPU
>>and memory resources.  If the package uses a lot of memory (e.g. large
>>in-memory lookups) or CPU (e.g. intensive transformations in parallel), 
>>then
>>SQL Server response time will be impacted.
>>
>>When you run applications (including SSIS) on the same box as the database
>>server, consider setting max SQL Server memory to prevent thrashing. 
>>Also,
>>consider limiting the number of parallel tasks in the package and/or run
>>during off-peak hours.
>>
>>> Hi All,
>>>
>>[quoted text clipped - 9 lines]
>>> Thanks in advance and Regards,
>>> SQL USER
>
> -- 
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201005/1
> 

Thank you Dan for your detailed  explanation. Its very useful.

Dan Guzman wrote:
>Run Performance Monitor against the server while the package is running. 
>The key metrics to monitor are:
>
>Memory:Pages/sec:  This should average at or near zero, with occasional 
>spikes.  If you see a lot of paging, reduce SQL Server Max memory so that 
>there is enough memory for SSIS and SQL Server to run concurrently without 
>paging.
>
>Processor:% Processor Time:  Verify all CPUs are not at capacity.  If CPU is 
>high due to SSIS queries, perform query and index tuning.  If you have 
>parallel plans, consider adding the OPTION (MAXDOP 1) to reduce processor 
>utilization in deference to OLTP performance.
>
>Physical Disk:Avg. Disk Queue Length:  This should average under 2 times the 
>number of physical spindles.  Query and index tuning of SSIS queries can 
>mitigate I/O bottlenecks.
>
>If your SSIS package performs a lot of inserts or updates, ensure log files 
>are on different physical disks than the data files.
>
>> Hi again,
>>
>[quoted text clipped - 41 lines]
>>>> Thanks in advance and Regards,
>>>> SQL USER

-- 
Message posted via http://www.sqlmonster.com