Diverse

Re: sql server DTS(SSIS) memory

B_gd_n[ ]Sahlean — Tue, 30 Sep 2008 16:04:26 GMT

Hai sa punem problema altfel. Ce masuri ai luat pana in prezent ?

Ai incercat sa identifici cauza ?
> Something about SSIS Performance Counters & Set up OLE DB source to read from View efficiently
> Monitoring the Performance of the Data Flow Engine
> http://msdn.microsoft.com/en-us/library/ms141031.aspx

You can configure the following properties of the Data Flow task, all of which affect performance:

Specify the locations for temporary storage of buffer data (BufferTempStoragePath property) and of columns that contain binary large object (BLOB) data (BLOBTempStoragePath property). By default, these properties contain the values of the TEMP and TMP environment variables. You might want to specify other folders to put the temporary files on a different or faster hard disk drive, or to spread them across multiple drives. You can specify multiple directories by delimiting the directory names with semicolons.
Define the default size of the buffer that the task uses, by setting the DefaultBufferSize property, and define the maximum number of rows in each buffer, by setting the DefaultBufferMaxRows property. The default buffer size is 10 megabytes, with a maximum buffer size of 100 megabytes. The default maximum number of rows is 10,000.
Set the number of threads that the task can use during execution, by setting the EngineThreads property. This property provides a suggestion to the data flow engine about the number of threads to use. The default is 5, with a minimum value of 3. However, the engine will not use more threads than it needs, regardless of the value of this property. The engine may also use more threads than specified in this property, if necessary to avoid concurrency issues.

...

Adjust the Sizing of Buffers

The data flow engine begins the task of sizing its buffers by calculating the estimated size of a single row of data. Then it multiplies the estimated size of a row by the value of DefaultBufferMaxRows to obtain a preliminary working value for the buffer size.

If the result is more than the value of DefaultBufferSize, the engine reduces the number of rows.
If the result is less than the internally-calculated minimum buffer size, the engine increases the number of rows.
If the result falls between the minimum buffer size and the value of DefaultBufferSize, the engine sizes the buffer as close as possible to the estimated row size times the value of DefaultBufferMaxRows.

Re: sql server DTS(SSIS) memory

ignatandrei — Tue, 30 Sep 2008 09:18:57 GMT

ignatandrei:

Aveam probleme cu memoria server-ului (un 2005 pe 64 de biti, SP2). Am pus un procexp si iata rezultatele :

Process    PID    Description    Virtual Size    Private Bytes    Working Set    I/O Read Bytes    I/O Write Bytes    I/O Other Bytes
MsDtsSrvr.exe    1464         5,417,512 K    83,420 K    23,304 K    66,105    1,284    8,964
sqlservr.exe    1684    SQL Server Windows NT - 64 Bit    2,375,360 K    806,604 K    751,440 K    3,846,696,692    686,421,579    889,703,423
SQLAGENT90.EXE    2236    Microsoft SQL Server Agent    551,260 K    32,204 K    12,392 K    240,515    35,604    299,554

Ideea ar fi ca Virtual Size este enorm pentru DTS (ma rog, SSIS). Vreo idee de ce ?

Este SP2 - exista cumva un SP3 ?
Multumesc,

Dupa 3 ore de functionare:
Process    PID    Description    Virtual Size    Private Bytes    Working Set    I/O Read Bytes    I/O Write Bytes    I/O Other Bytes
MsDtsSrvr.exe    1464         5,413,416 K    79,312 K    1,024 K    69,241    1,732    8,964
sqlservr.exe    1684    SQL Server Windows NT - 64 Bit    2,457,356 K    1,341,028 K    1,232,488 K    14,611,292,831    3,046,151,926    2,009,768,874
SQLAGENT90.EXE    2236    Microsoft SQL Server Agent    553,308 K    33,592 K    10,352 K    301,348    91,171    966,020

Ideea este ca nu stiu cum sa ii spun sa nu isi mai aloce atita memorie de care nu are nevoie ... parerea mea...

Re: sql server DTS(SSIS) memory

Diana — Tue, 30 Sep 2008 08:56:36 GMT

Pentru SP3 poti vota aici:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=326575

Nu am auzit nimic precis despre data de "release"...

Re: sql server DTS(SSIS) memory

rsocol — Tue, 30 Sep 2008 08:28:49 GMT

Am făcut o confuzie, CU10 va fi disponibil în octombrie... ultimul Cummulative Update disponibil pentru SQL Server 2005 SP2 este CU9, la KB 953752.

Un Cummulative Update este un pachet care conține toate hotfix-urile produse până în acel moment. Este mai puțin testat decât un Service Pack, dar e testat ceva mai mult decât un hotfix și este suportat oficial de Microsoft. Totuși MS spune "A supported cumulative update package is now available from Microsoft. However, it is intended to correct only the problems that are described in this article. Apply it only to systems that are experiencing these specific problems.", însă în fiecare CU sunt corectate câteva zeci de bug-uri, deci discutăm în total de multe sute de "specific problems" corectate... În KB-ul aferent fiecărui CU sunt descrise hotfixurile incluse în acel CU (însă fiecare CU include și toate hotfix-urile din CU-urile anterioare). Poți găsi o listă completă a acestora în KB 937137.

Pe de altă parte, hotfix-ul din KB 920205 (menționat de Bogdan) ar trebui să fie inclus în Service Pack 2, fiind menționat în KB 921896.

Răzvan

Re: sql server DTS(SSIS) memory

ignatandrei — Tue, 30 Sep 2008 08:27:31 GMT

B_gd_n[ ]Sahlean:
ignatandrei:
B_gd_n[ ]Sahlean:
S-ar putea ca MsDtsSrvr.exe sa uite sa dealoce memoria alocata la un moment dat:
FIX: A memory leak may occur when you run SQL Server 2005 Integration Services packages that are stored in the SSIS Package store

Multumesc,dar asta e pentru sp1, si eu am SP2 - si nu as instala ceva valabil pentru SP1 peste SP2

@andrei:

[1] http://support.microsoft.com/kb/913089
Service packs are cumulative. Each new service pack contains all the fixes that are in previous service packs, together with any new fixes. You do not have to install a previous service pack before you install the latest one. For example, you do not have to install Microsoft SQL Server 2005 Service Pack 1 (SP1) before you install Microsoft SQL Server 2005 Service Pack 2 (SP2).

Mda ... banuiam asa ceva - deci oricum nu se aplica - deoarece am deja SP2...\

B_gd_n[ ]Sahlean:

[ 2]WORKAROUND To work around this problem, do not use the SSIS Package store as a storage location for your SSIS packages. Instead, you can store the packages in SQL Server or in the file system. ???

Chiar nu inteleg ....oricum package-urile sunt in SQL Server store ... exista si SSIS package store?

Re: sql server DTS(SSIS) memory

B_gd_n[ ]Sahlean — Tue, 30 Sep 2008 08:03:13 GMT

ignatandrei:
B_gd_n[ ]Sahlean:
S-ar putea ca MsDtsSrvr.exe sa uite sa dealoce memoria alocata la un moment dat:
FIX: A memory leak may occur when you run SQL Server 2005 Integration Services packages that are stored in the SSIS Package store

Multumesc,dar asta e pentru sp1, si eu am SP2 - si nu as instala ceva valabil pentru SP1 peste SP2

@andrei:

[1] http://support.microsoft.com/kb/913089
Service packs are cumulative. Each new service pack contains all the fixes that are in previous service packs, together with any new fixes. You do not have to install a previous service pack before you install the latest one. For example, you do not have to install Microsoft SQL Server 2005 Service Pack 1 (SP1) before you install Microsoft SQL Server 2005 Service Pack 2 (SP2).

[2]WORKAROUND To work around this problem, do not use the SSIS Package store as a storage location for your SSIS packages. Instead, you can store the packages in SQL Server or in the file system. ???

Re: sql server DTS(SSIS) memory

ignatandrei — Tue, 30 Sep 2008 07:55:11 GMT

rsocol:
Service Pack 3 pentru SQL Server 2005 va apare până la sfârşitul anului 2008. Până atunci, poţi încerca ultimul Cummulative Update (momentan CU 10, KB 956854).

Multumesc. Stii cumva ce contine CU ? E mediu de productie - si nu as vrea sa instalez ceva ce nu am nevoie...

Re: sql server DTS(SSIS) memory

ignatandrei — Tue, 30 Sep 2008 07:54:24 GMT

B_gd_n[ ]Sahlean:
S-ar putea ca MsDtsSrvr.exe sa uite sa dealoce memoria alocata la un moment dat:
FIX: A memory leak may occur when you run SQL Server 2005 Integration Services packages that are stored in the SSIS Package store

Multumesc,dar asta e pentru sp1, si eu am SP2 - si nu as instala ceva valabil pentru SP1 peste SP2

Re: sql server DTS(SSIS) memory

rsocol — Tue, 30 Sep 2008 07:46:22 GMT

Service Pack 3 pentru SQL Server 2005 va apare până la sfârşitul anului 2008. Până atunci, poţi încerca ultimul Cummulative Update (momentan CU 10, KB 956854).

Răzvan

Re: sql server DTS(SSIS) memory

B_gd_n[ ]Sahlean — Tue, 30 Sep 2008 07:46:14 GMT

S-ar putea ca MsDtsSrvr.exe sa uite sa dealoce memoria alocata la un moment dat:
FIX: A memory leak may occur when you run SQL Server 2005 Integration Services packages that are stored in the SSIS Package store

sql server DTS(SSIS) memory

ignatandrei — Tue, 30 Sep 2008 07:35:34 GMT

Aveam probleme cu memoria server-ului (un 2005 pe 64 de biti, SP2). Am pus un procexp si iata rezultatele :

Process    PID    Description    Virtual Size    Private Bytes    Working Set    I/O Read Bytes    I/O Write Bytes    I/O Other Bytes
MsDtsSrvr.exe    1464         5,417,512 K    83,420 K    23,304 K    66,105    1,284    8,964
sqlservr.exe    1684    SQL Server Windows NT - 64 Bit    2,375,360 K    806,604 K    751,440 K    3,846,696,692    686,421,579    889,703,423
SQLAGENT90.EXE    2236    Microsoft SQL Server Agent    551,260 K    32,204 K    12,392 K    240,515    35,604    299,554

Ideea ar fi ca Virtual Size este enorm pentru DTS (ma rog, SSIS). Vreo idee de ce ?

Este SP2 - exista cumva un SP3 ?
Multumesc,