Prev: How to export policies to excel
Next: SQL 7 and last time stored procedures was ran
From: prema on 26 May 2010 01:42
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

From: Dan Guzman on 26 May 2010 08:46
> 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(a)uwe> wrote in message news:a89106fb51dfb(a)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
>
From: prema via SQLMonster.com on 26 May 2010 09:43
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

From: prema via SQLMonster.com on 31 May 2010 05:16
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

From: Dan Guzman on 31 May 2010 12:28
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(a)uwe> wrote in message
news:a8d1c26d7b9a5(a)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
>
 |  Next  |  Last
Pages: 1 2
Prev: How to export policies to excel
Next: SQL 7 and last time stored procedures was ran