STU D IA IN F O R M A T IC A V olum e 28
_________ 2007 N um ber 2 (71)
M arcin W O C H
P o litechnika Śląska, Instytut Inform atyki
MICROSOFT SQL SERVER OPTIMIZATION FOR M ICROSOFT DYNAMICS NAY
S u m m a ry . T his article presents som e m ethods o f SQ L S erver 2000 o r SQL S erver 2005 for M icro so ft D ynam ics N A V optim ization. It focuses o n database design, p rogram m ing standards and a hardw are configuration.
K e y w o rd s: SQ L S erver 2000, SQ L S erver 2005, M icrosoft D ynam ics N A V , N avision A ttain, locking, optim ization, deadlock
OPTYMALIZACJA MICROSOFT SQL SERVER PRZY WSPÓŁPRACY Z MICROSOFT DYNAMICS NAV
S tre sz c z e n ie . A rtykuł prezentuje w ybrane m etody optym alizacji pracy serw era baz danych SQ L 2000 oraz 2005 przy w spółpracy z system em klasy ER P M icrosoft D ynam ics N A V . Z w raca on sz cz eg ó ln ą uw agę n a odpow iednie zaprojektow anie b az y danych, pop raw n y ko d źródłow y aplikacji, a także p odaje za le c a n ą konfigurację sprzętow ą.
S ło w a kluczow e: SQ L S erver 2000, SQL S erver 2005, M icrosoft D ynam ics N A V , N avision A ttain, blokow anie, optym alizacja, deadlock
1. Introduction
M icrosoft D ynam ics N A V - called N av isio n , form erly N avision A ttain and N avision F i
nancials is an E R P (E nterprise R esource P lanning) system w hich covers all areas o f co m p an y ’s activities: accountancy, sales, receivables, purchase, payables, C R M , m anufacturing, inventory, etc.
N avision system can run on tw o different servers: M icrosoft N av isio n D atabase Server and M icrosoft SQ L Server. To a user these tw o servers w o rk and look exactly th e sam e. Som e o f the differences betw een these servers are:
• scalability
• SQ L S erver has no database size lim it
• each database can have m ultiple filegroups on S Q L S erver
• large R A M support on SQ L S erver
• SQ L S erver is cluster aw are
• perform ance m onitoring
• SQ L S erver supports m ulti-processor, b oth 32 and 64 bit
• the w ay that SIFT w orks
• others.
In this article SQ L S erver option for N avision w ill be described.
2. Architecture Overview
M icrosoft D ynam ics N A V can run on M icrosoft SQ L Server, w hich is integrated w ith C /SID E (C lient/S erver Integrated D evelopm ent Engine). N ew versions o f N av isio n support SQ L 2000 and SQ L 2005 server. H ow ever, m uch b etter perform ances are achieved on 2005 version.
Database
Fig. 1. M S SQ L S erver O ption for M S D ynam ics N A V architecture R ys. 1. A rchitektura opcji M S SQ L S erver dla M S D ynam ics N A V
M icrosoft SQL Server Optimization for Microsoft Dynamics NAV 19
3. Hardware recommendations
M inim um h ardw are recom m endations dep en d on operational system and SQ L S erver version used. F or ex am p le SQL S erver 2005 requires m inim um 512 M B R A M [8], but the b etter configuration the b etter results.
H ardw are recom m endations p resented in that article are m uch h igher then m inim um one.
R eal co m p an y ’s expectations m ay be low ered due to available funds.
D ual C ore pro cesso r provides eq uivalent o r b etter com puting p o w er then tw o separate processors [2].
T able 1 M S D ynam ics N A V option for M S SQ L Server 2000 [21_________________
W indow s Edition
SQ L Server Edition
C oncurrent No. o f U sers
SQL Db.
Size TGB1
Proc. RAM
[GB]
N et. Card
MS W in 2003 Server St.
M S SQL Server 2000 St.
< 5 0 < 4 0 2 x 3 G H z 2 M B L3 Cache
3 1 GB
Eth/Fibre M S W in 2003
Server Ent.
M S SQL Server 2000 Ent.
OO
'■n > 4 0 2 x 3 GHz
2 M B L3 Cache
8 2 GB
Eth/Fibre B oth M S W indow s 2003 Standard and E nterprise editions require SQ L S erver 2000 S end ce P ack 3 or later [2].
T able 2 _____________ M S D ynam ics N A V option for M S SQ L S erver 2005 [21______ __________
W indow s Edition
SQL Server Edition
C oncurrent N o. o f U sers
SQL Db.
Size [GB]
Proc. RAM
[GB]
N et. Card
M S W in SBS 2003 R 2 St.
M S SQ L Server 2005 W orkgroup
< 5 < 2 0 1 x 3 G H z 2 M B Cache
2 1 GB Eth.
M S W in 2003 Server St.
M S SQ L Server 2000 St.
6 -2 5 2 1 -4 0 2 x 3 G H z 2 M B Cache
3 1 GB Eth.
M S W in 2003 Server St.
M S SQL Server 2000 St.
2 6 -5 0 2 1 -4 0 2 x 3 G H z 2 M B L3 Cache
3 1 GB
Eth/Fibre M S W in 2003
Server St.
M S SQ L Server 2000 St.
5 1 -1 0 0 4 1 -8 0 4 x 3 G H z 2 M B L3 Cache
4 1 GB
Eth/Fibre M S W in 2003
Server Ent.
M S SQL Server 2000 Ent.
Oo
4 1 -8 0 4 x 3 G Hz 2 M B L3 Cache
6 2 GB
Eth/Fibre M S W in 2003
Server Ent.
M S SQ L Server 2000 Ent.
151-200 > 8 0 6 x 3 GH z 2 M B L3 Cache
8 2 GB
Eth/Fibre M S W in 2003
Server Ent.
M S SQL Server 2000 Ent.
201 - 2 5 0 > 8 0 8 x 3 G H z 2 M B L3 Cache
8 2 GB
Eth/Fibre R ecom m endation fo r hard disk size depicts m inim um value only. W hen choosing a hard drive factors such as R P M , S eek T im e, L atency and n u m b er o f spindles should be taken into consideration.
T able 3 S torage for M icrosoft SQ L Server [2]_______ _________________
Storage Solution
Technology SQ L Db.
Size [GB1
Concurrent No. o fU sers
N o. o f D isks for D ata
No. o f Disks for T ransaction Log
DAS SATA < 2 0 < 5 2 x 36 GB
7,2K RPM RAID 1
2 x 72 GB 7 .2 K R P M RA ID 1
DAS SATA 2 1 -4 0 6 -2 5 4 x 36 GB
7,2K RPM RAID 0+1/RA ID 10
4 x 72 GB 1 5 K R P M , U 320
RAID 1
DAS SCSI 2 1 -4 0 2 6 -5 0 4 x 36 GB
1 5K R P M , U 320 RA ID 0+1/RA ID 10
4 x 72 GB 15K RPM , U 320
RAID 1
SAN Fiber/SCSI 4 1 -8 0 5 1 -1 5 0 6 x 36 GB
15K R P M RAID 0+1/RA ID 10
8 x 72 GB 1 5 K R P M , U 320
RA ID 1
SAN Fiber/SCSI > 8 0 151-250 14 x 36 GB
15K R P M RAID 0+1/R A ID 10
10 x 72 GB 15K RPM , U 320
RA ID 1 F iles distribution depends on num ber o f disks. A suggested approach is to p u t the N avision standard tables in the Prim ary D ata File and SIFT tables in the A dditional D ata File.
T able 4 _____________ R ecom m ended D ata D istribution [2] _____________ ___________
No. o f Disks D isk 1 Disk 2 D isk 3 D isk 4 D isk 5
3 OS Program Files Tcm pD B
N A V D ata File
SQL Trans. Log
— —
4 OS Program Files Tem pD B
N A V Prim ary Data File
N A V Add.
D ata File
SQL Trans. Log
—
5 OS Program Files Tem pDB N A V Prim ary Data File
N A V Add.
D ata File
SQL Trans. Log
4. SQL Server administering
D atabase server m aintenance is a day-to-day task for every adm inistrator. T here are m any server and database param eters and tools w hich can speed up a system . In th at article only that are described w h ich d irectly affect SQL w ith N avision cooperation:
• A uto sh rin k - in both SQ L S erver 2000 and SQ L S erver 2005 auto shrink p aram eter should b e set as false. I f necessary it is recom m ended to set up a database shrinking in a sch ed u ler to be run overnight.
• U pdate statistics — statistics affect an o ptim izer and speed up queries. Som e adm inistra
tors sw itch o f f auto update statistics p aram eter and set it up as an o v ernight routine.
M icrosoft SQL Server Optimization for Microsoft Dynamics NAV 21
• F iles distribution - detailed description in poin t 3.
• Index rebuild - indices should b e reb u ilt periodically. P aram eters like F IL L F A C T O R and P A D _IN D E X can b e used w hen an index is bein g rebuilt. F IL L F A C T O R determ ines the percentage o f space on each leaf-level page filled in data b y a server [10]. P A D _IN D E X specifies the space left on each page in the in term ediate levels on the index [10].
• Index hinting - index hinting can help to avoid situations w hen q uery optim izer chooses an index th a t requires m any pag e reads and generates tim e consum ing queries. It helps to hint a query o ptim izer to choose a “ b etter” index. W hen index h inting is used N avision adds com m ands to the SQ L queries that are sent to the server. T hese additional com m ands bypass th e norm al decision to the Q ueries O ptim izer and force th e serv er to choose a particu lar index [1], Index hint syntax is:
IndexHint=<Yes, No>; Company=<Company_name>; Table=<Table_name>;
Key=<Key_fieldl, KeyFieldn>; Search Method=<search_method_list>;
Index=<index_id>
Each p ara m ete r keyw ord can be found in stx file in N avision directory in “D rive C onfiguration P aram eters” section. Index hint w ill b e ignored w hen IndexH int= N o and/or a keyw ord in a q uery is m issin g o r incorrect. S earch m ethod contains a list o f m ethods used in C /A L F IN D /G E T statem ent: “+ ” , “= ” , “ !” . Index ID corresponds to a SQ L server index fo r a table: 0 is fo r a prim ary key. Index ID can be found by sp_helpindex stored procedure.
• D efaultL ockG ranulity - i f this param eter is false then SQ L Server p laces row locks, w hen is set as true SQ L S erver w ill choose its ow n g ranulity (page, table, row ) [11],
• C lustered and nonclustered index - clustered index sorts and stores the data row s in the table o r view b ased on th eir key values. O nly one clustered index can exist in a table. By default a prim ary key is set as clustered index.
5. Programming recommendations
C arefully p lanning the details o f an application w ill help ensure that a database has the best p ossible design. P roperly designed application is m u ch easier to build and m aintain. T his section does n o t focus on w ell-know n m ethodology o f analysis, design and im plem entation, but gives m any hints and guidelines regarding best practises in C /A L program m ing and N avision adm inistering.
P roper keys an d indices definition belongs to the features w ith high influence on the system behaviour. D evelopers should rem em b er not to define too m an y keys, b ecause it slow s
dow n records m anipulation. System has to up d ate a record its e lf and all related indices during m odification, insertion or deletion. A ll unused keys should be deleted.
S om e keys are unn ecessarily duplicated.
B Table 17 G/L Entry - Keys Jo J x J
SumlnctexFields
?
Entry No.
G.A. Account No.,Posting Date
G A. Account No.,Business Unit Code,Global Dimension 1 Code,Global Dimension 2 Code, Document No.,Posting Date
Transaction No.
Close Income Statement Dim. ID IC Partner Code
J i l
G/L Account No.,Document Date,Posting Date
Amount,Debit Amount,Credit Amount,Debit Amount, Credit
Am...
Am...
Fig. 2. G /L E ntry k ey definition
Rys. 2. D efm icja k luczy w tabeli G /L E ntry
In the exam ple show n in fig. 2 th e key m arked blue is un n ecessary and should be deleted.
T he key com posed o f the fields “ G /L A ccount N o .” and “D o cu m en t D ate” is included in the key “ G/L A ccount N o .” , “D ocum ent D ate” , “ P osting D ate” . S E T C U R R E N T K E Y statem ent w ill w ork correctly even i f the k ey is deleted.
SETCURRENTKEY("G/L Account No.", "Document Date");
F or queries using filters a correct key should be used. In b elo w exam ple the query w orks m uch faster w hen a p rogram m er uses S E T C U R R E N T K E Y () statem ent. F ilters (SET R A N G E) in that case w ill be applied on index due to the chosen key. C /A L queiy:
SETCURRENTKEY(Fieldl, Field2);
SETRANGE(Fieldl, Codel);
SETRANGE(Field2, Code2);
IF FIND('-') THEN
results w ith SQ L quety:
SELECT * FROM Table
WHERE Fieldl = Codel AND Field2 = Code2 ORDER BY FIELD1, FIELD2
w here:
SETRANGE(Fieldl, Codel);
SETRANGE(Field2, Code2);
IF FIND ( '- ') THEN
Microsoft SQL Server Optimization for M icrosoft Dynamics NAV 23
results w ith:
SELECT * FROM Table
WHERE Fieldl = Codel AND Field2 = Code2
O R D E R B Y clause forces query o ptim izer to use b etter index to process it.
W hen som e N av isio n functionalities are not u sed it is recom m ended to delete related fields from keys and indices. F or ex am ple i f a com pany does n o t u se item variants it is u n necessary to keep “V arian t C ode” field in keys.
In N avision k ey definition there are several p aram eters directly affecting locking and perform ance issues:
• M aintainS Q L Index - w h en that pro p erty is false a key in N avision w ill n o t force a S Q L to create corresponding index. W hen it is true SQ L S erver w ill create an index for that key.
S Q L S erver w h en perform ing a query often blocks a table and an index. W hen M aintainS Q L Index is false then SQ L S erver w ill hav e no index to b e blocked fo r a key.
T his setting can slow dow n read statem ents but speed up a record update.
• SQ L Index - w hen M aintainS Q L Index is true then alw ays a corresponding index is created. SQ L Server does n o t have to u se th e sam e fields o rd er as it is in the N av isio n key u nless SQ L Index property is defined. T his option is available for v ersion 4.0SP1 and above.
• M aintainS IF T Index - Sum Index F ield T echnology (SIFT) is an algorithm introduced by N av isio n for fast calculation o f virtual fields. SQ L S erver stores S IFT structure in a sp e
cial tables unless M aintainS IF T Index is false.
• S IF T L evelsT oM aintain - a developer can define w hich SIFT levels are stored and m aintained by a SIFT table.
• C lustered - th at p erm ission helps set an index u p as clustered or nonclustered. T his o ption is available for v ersion 4.0SP1 and above.
- Properties JsjxJ
Property Value
Enabled <Yes> ~ 1
Key Posting DatefG /l Account Mo.
SumlndsxFields Am om t,Additional-Curency Amount
KeyGroups <>
MaintainSQLIndex <Yes>
MaintainSIFTIndex <Yes>
SIFTLeve IsT oMa inta in <{Postlng Date:Year}, {Posting Date :Month}, {Posting DateiDay},...
Clustered <Mo>
SQLIndex
fcasE ij
Fig. 3. K ey properties definition Rys. 3. W łaściw ości kluczy
P rogram m ers have an access to keys definition, so they can activate and deactivate keys.
B ut end users usu ally do not h av e a licence to change the table structure. “ K ey groups” is a very u seful tool to m aintain rarely u sed keys b y a N avision user.
B y m aking the keys m em bers o f key groups a u ser can activate and deactivate various com binations o f keys in the tables b y enabling and disabling th e key groups respectively.
£5 Table 21 Cust. Ledger Entry - Keys " ' J ' T ‘
E ..|K e y IKeyGroups
n
► v' Entry No.
* Customer No.,Posting Date,Currency Code
Customer No..Currency Code.Postng Date C ust(C ur)
I * Document No.,Document Type,Customer No.
~ ja rs!
| Key Group | Last Change
^ C u s t i O r r ) ÎDisAted - DE
r~:Dim Enabled
GLAcc Disabled
—
Vend(CuT) Disabled
—
j
Epable | Qftahte | Etefc
Fig. 4. K ey G roups functionality
Rys. 4. D ziałanie funkcjonalności K ey G roups
V ery im portant clue is to keep locking o rd er the sam e. O therw ise it is likely the users w ill experience the deadlocks. It w ill not prevent the system from deadlocks at all b u t w ill reduce them . D eadlocks happen w hen tw o processes block each other w aiting for table release.
F irst process runs below com m ands locking at first T able 1 and then Table2.
Table1.LOCKTABLE;
Table2.LOCKTABLE;
Second process analogically runs below sequence locking tables in reverse order:
T able2.LOCKTABLE;
T able!.LOCKTABLE;
W hen both processes are run in the sam e tim e, process no. 1 has to w ait for T able2 to lock it and in the sam e tim e process no. 2 is w aiting for T able 1 w hich is blocked by process 1 and w ill n o t be released until T able2 is n o t free. T hat situation is called a deadlock.
L O C K T A B L E com m and on SQL S erver differs from N A V server. SQ L S erver applies table lock w hen reading a record. N A V server locks w hole table w hen explicit LO C K TA B LE w as u sed o r transaction starts. B y default transaction isolation level is readuncom m ited.
L O C K T A B L E m ay use tw o param eters both B oolean type. I f first p aram eter is true a server
M icrosoft SQL Server Optimization for M icrosoft Dynamics NAV 25
w aits until pend in g transaction is unlocked. Second p aram eter checks a version o f the record before it is updated.
T em porary tables h elp avoiding deadlocks as w ell. T em p o rary tables allow u pdating data in som e p rotected triggers, e.g. O nA fterG etC urrR ecord on form . T hey are p rocessed on a cli
ent com puter n o t on a server, so there is alw ays only one c o p y o f a tem porary variable p ointing the real table.
N avision in m o st cases w hen perfo m iin g a query sends all data from and to th e server.
C /SID E clien t filters them out, sorts them and process to return a result. C /A L com m ands like IN SE R T , M O D IF Y , D E L E T E are all processed b y a clien t and afterw ards results are se n t to the server. C ode like:
Table.SETRANGE(FieldX, ValueX);
IF T a b l e .F I N D ('-') THEN REPEAT
Table.FieldY := ValueY;
Table.MODIFY;
UNTIL Table.NEXT = 0;
can be replaced by:
Table.SETRANGE(FieldX, ValueX);
Table.MODIFYALL(FieldY, ValueY) ;
w hich is p rocessed b y a server. It helps to avoid sending all d ata by n etw ork to clients.
Sim ilar com m and D E L E T E A L L should replace D E L E T E w h erev er possible. C om m and R E P E A T ... U N T IL T able.N E X T (< S tep> ) = 0; blocks cu rrent record and one before and one after the current one. T h at situation increases a risk o f deadlocks w hich can be red u ced by M O D IFY A LL/D E L E T E A L L .
In N avision there are several o f com m ands to search for a record. O ne o f them is F IN D w hich scans the table basing on current filter (S E T R A N G E /S E T F IL T E R ) and sorting key (S E T C U R R E N T K E Y ). FIN D returns true w hen a record w as found and false in a contrary situation b asing on a param eter w hich defines a search m ethod. F IN D (‘- ‘) finds first record and F IN D (‘+ ’) tries to find last record m atching to the selection criteria. D isadvantage o f FIND com m and is the fact that it results w ith a full scan o f a table: S E L E C T * F R O M T able even i f it finds o nly one record. F or FIN D com m and a cursor is created.
In N av isio n version 4.0 SP1 and above th ere are n ew com m ands FIN D L A S T , FIN D FIR S T and F IN D S E T w hich do not scan all table but only selected row set. F or exam ple F IN D FIR S T and F IN D L A S T statem ents are equal to S E L E C T T O P 1. T hese new com m ands do not create cursors.
The com m and IS E M P T Y checks i f a table o r a record set is em pty. T h at com m and does not create any cursor and result w ith S EL E C T T O P 1.
F or version 4.0 SP1 and above som e new database param eters are introduced.
General | Database Files | Transaction Log Flies | Collation | Options | Integration Advanced | Locking---
15 Lock tim eout f Always rowlock
Timeout Deration (sec) .
Security ---
Model (Enhanced 3
C a ch h g ---
Record get 500
OK Cancel Help
Fig. 5. A lte r D atabase w indow
Rys. 5. Z m iana param etrów bazy danych
• L ock tim eout - this setting allow s a u se r specify w h eth e r a session w ill w ait to p lace a lock on a resource that has already been locked b y another session.
• T im eout D uration - this param eter allow s a u se r specify th e m ax im u m length o f tim e that a session w ill w ait to place a lock on a resource that has already been locked by another session. T he default value is 10 seconds. I f this option is left unchecked, the session w ill w ait indefinitely.
• A lw ays row lock - this setting allow s sp ecify that N av isio n alw ays p laces row -level locks instead o f page- and table-level locks.
• C /A L com m and L O C K T IM E O U T (F alse) - w hich allow s tem porarily change Lock tim eout property.
N avision allow s o p tim izing tables (m enu File, D atabase, Inform ation, T ables, O ptim ize).
Its internal optim ization process w orks in tw o w ays [1]:
• for each table, SQ L indices other then prim ary key are rebuilt to optim ize th eir layout and usage,
• for each SIFT structure, all entries containing zero values in all num eric fields are rem oved.
6. Summary
P ro p er database server adm inistering and m aintenance is a v ital key to a correct and a long life o f the server and its applications. O ptim ization and daily routines help avoid deadlocks, shorten database processes and transactions.
Microsoft SQL Server Optimization for M icrosoft Dynamics NAV 27
O ptim ization is com posed o f b o th hardw are and softw are issues. D evices can u su a lly be easily and p retty fast replaced. H ardw are upgrade is done every certain span o f tim e. A n im properly designed database and an application hav e m uch m ore negative consequences and their rebuild is m o re tim e consum ing, expensive and v e ry often im possible. A ccording to the statistics ap plication causes 8(H 90% o f perform ance problem s. P roblem s caused by infrastructure v ary betw een 1(H20% . In application design p h ase developers and designers should consider future data grow th, coding plans, optim ization issues and custom er expectations.
R ecent N av isio n versions w o rk w ith three types o f servers: M icrosoft D ynam ics N A V D atabase Server, M icrosoft SQ L S erver 2000 and M icrosoft SQ L S erver 2005. M icrosoft did som e perform ance tests w hich proved the fastest cooperation w ith SQL S erver 2005. SQ L S erver 2000 and N A V S erver had fairly sim ilar accom plishm ents. N avision cooperating w ith SQL S erver 2000 uses N D B C S d river w hich uses tem porary cursors to sim ulate n ative (NA V ) server.
B IB L IO G R A P H Y
1. M icrosoft B usiness Solutions N avision 4.0 Course: 8404B Installation and Configuration Training. M icrosoft C orporation, 2004.
2. D upont-R oc P., G atchalian R., Serrano D.: M icrosoft D ynam ics N A V 4.0 H ardw are G uide. 2006.
3. M uhlbaher H.: O ptim izing D ynam ics N A V on SQ L S erver - A pplication. 2007.
4. M uhlbaher H.: O ptim izing D ynam ics N A V on SQL S erver - Infrastructure. 2007.
5. R aheem M ., Sonkin D ., D ’H ers T., LeM onds K.: Inside SQL Server 2005 Tools. A ddison W esley Professional, B oston 2006.
6. W oody B.: A dm inistrator’s G uide to SQ L Server 2005. A ddison W esley Professional, B oston 2006.
7. R ankins R., Jensen P., B ertucci P.: M icrosoft SQ L S erver 2000. Ksiqga eksperta. Helion, G liw ice 2003.
8. B ieniek D., D yess R., H otek M ., L oria J., M achanic A., Soto A., W iem ik A.: M icrosoft SQ L S erver 2005 Im plem entation and M aintenance, S e lf P aced T raining Kit. M SPress, R edm ont 2006.
9. N ielsen P.: SQL S erver 2005 Bible. W iley Publishing, Inc., Indianapolis 2007.
10. Thom as O., M cLean I.: O ptim izing and M aintaining A D atabase A dm inistration Solution by U sing M icrosoft SQ L S erver 2005. M icrosoft Press, R edm ont 2006.
11. M icrosoft D ynam ics N A V 5.00 A pplication D esigner’s Guide. M icrosoft Corporation, R edm ont 2007.
R ecenzent: D r inż. P aw eł K asprow ski
W płynęło do R edakcji 3 kw ietnia 2007 r.
O m ó w ien ie
O becne w ersje M icrosoft D ynam ics N A V oprócz serw era natyw nego p o zw a lają na w spółpracę z M S SQ L S erver 2000 oraz M S SQ L S erver 2005. P raw idłow a adm inistracja serw erem oraz system em N avision um ożliw ia optym alizację zapytań oraz m anipulację danym i.
Płaszczyzny adm inistracji m ożna podzielić na 3 grupy:
• zarządzanie sprzętem ,
• adm inistracja SQ L Server,
• adm inistracja system em N avision.
Sam a decyzja zakupu sprzętu pow inna uw zględniać planow ane w drożenie, w szczegól
ności o b szar system u ERP, k tó ry będzie używ any, liczbę w szystkich u żytkow ników system u oraz p rzew idyw aną liczbę rów noległych, konkurencyjnych sesji. N ale ż y także brać pod uw agę m ożliw y przyrost bazy danych. Im m ocniejszy sprzęt, tym z je d n ej stro n y lepsze osiągi system u, ale też w iększe koszty.
P rz y w iększych b az ac h danych należy rozw ażyć zainstalow anie i w drożenie hurtow ni danych, po to aby ona odciążyła bazę O LTP przejm ując w iększość zapytań. W niektórych przypadkach instaluje się kolejny serw er O LTP, który przechow uje tylko zap isy archiw alne.
W ted y h urtow nia zbiera dane zarów no z bazy aktualnej, ja k i archiw alnej.
R utynow e prace adm inistratora serw era oraz system u N avision o b ejm u ją m iędzy innym i śledzenie logów , pilnow anie blokad, zm ianę p aram etrów w raz ze w zrostem bazy danych.
Istnieje szereg param etrów oraz narzędzi, które p o zw a lają m onitorow ać pracę system u.
O statnim , ale rów nie w ażnym elem entem w pływ ającym na w ydajność system u je s t odpow iednio zaprojektow ana i napisana baza danych. P raw idłow e naw yki program istyczne, takie ja k używ anie tabel tym czasow ych czy zachow anie odpow iedniej kolejności dostępu do tabel, p o zw a lają przyspieszyć pracę system u oraz uniknąć zbędnych blokad i zakleszczeń.
Istotnym elem entem je s t odpow iednie zaprojektow anie kluczy, indeksów , pam iętając, że zbyt
Microsoft SQL Server Optimization for M icrosoft Dynamics NAY 29
ich duża liczba spow alnia usuw anie, dodaw anie i m odyfikow anie rekordów . N ależy także używ ać opow iednich filtrów i k luczy sortujących p rz y zapytaniach.
T esty w ydajności przep ro w ad zan e p rzez M icro so ft z użyciem pakietu A pplication B enachm ark T oolkit pokazały, że najw yższa w ydajność została o siągnięta p rzy M icrosoft SQL S erver 2005. SQ L S erver 2000 oraz N A V S erver m a ją m niej w ięcej p o dobne osiągi.
A d re s
M arcin W O C H : P olitechnika Śląska, Instytut Inform atyki, ul. A kadem icka 16, 44-100 G liw ice, Polska, m a rcinw och@ w p.pl.
