Navigacija
Lista poslednjih: 16, 32, 64, 128 poruka.

Za mene jako cudan SQL problem

[es] :: MS SQL :: Za mene jako cudan SQL problem

[ Pregleda: 5506 | Odgovora: 15 ] > FB > Twit

Postavi temu Odgovori

Autor

Pretraga teme: Traži
Markiranje Štampanje RSS

pota0311

Član broj: 63065
Poruke: 4
*.eds.net.



Profil

icon Za mene jako cudan SQL problem06.07.2005. u 14:42 - pre 228 meseci
Zdravo svima,

Radim jednu aplikaciju koja obradjuje dosta podataka u MS SQL 2000. Baza je ogromna, pored moje aplikacije postoji niz drugih koje istovremeno rade insert, update i delete nad tabelama.

Svakih pola sata, iz aplikacije se poziva mnostvo stored procedura, u jednom trenutku oko 400 scriptova poziva istu stored proceduru ali SEKVENCIJALNO jedan po jedan. Totalno vreme da se zavrsi ovaj posao za 400 scriptova je oko 20 minuta. Oko 2-2.5 sekunde po jednom pozivu proseku sa malim pauzama izmedju.

PROBLEM je da posle izvesnog vremena izvrsenje same stored procedure se usporava sto predstavlja problem. Duplira se vreme izvrsenja. Sve sam probao, lock-ove, no locko-ove, razne trace-ove, shrink-ove ali ne vidim gde je problem.

NAJCUDNIJE za mene u celoj prici je da postoji zaobilazno resenje za problem:
Ako samo uradim ALTER PROCEDURE procedure koju pozivam, sve se odmah vraca na normalnu brzinu! Ili samo snimim ponovo dizajn neke od tabela sa kojom sp radi!

Neka mi molim neko objasni o cemu se ovde radi, tj. sta se tacno desava kada pokusam sa zaobilaznim resenjem. I kako da ljudski resim problem.

Hvala.

Predrag
 
Odgovor na temu

sasas
Saša Slavnić
radim za neke švabe

Član broj: 35478
Poruke: 617
*.zaslon-telecom.si.



Profil

icon Re: Za mene jako cudan SQL problem06.07.2005. u 14:46 - pre 228 meseci
Mogao bi možda da pokušaš sa sp_recompile (pogledaj detaljnije u books online).

ss.
When something is hard to do, then it's not worth doing.
 
Odgovor na temu

MilovanB
Sydney

Član broj: 61367
Poruke: 21
*.flexirent.com.



Profil

icon Re: Za mene jako cudan SQL problem07.07.2005. u 02:29 - pre 227 meseci
Ne verujem da bi rekompajliranje procedure resilo tvoj problem. Sto vise izvrsavas SP to bi trebala da bude brza. Execution plan za SP se nalazi stalno u memoriji. Ti imas drugu vrstu problema.
Sta ti znaci da je baza ogromna, po kojoj meri? Da li ima 500-600 GB ili 20-50 GB. Koliko redova imaju tvoje najvece tabele koje se pozivaju iz te SP. Da li imas pravilne clustered i non-clustered indexe na tabelama. Da li redovno odrzavas bazu (na primer reindex). kolika ti je fragmentacije (logicka) na pages i na extentima (DBCC SHOWCONTIG). Da li je tvoj database server dedicated ili imas neke druge aplikacije koje se izvrsavaju na serveru? Kako si konfigurisao memoriju na serveru (staticki ili dinamicki)? Koliko procesora ima server i koliko je vidljivo za SQL Server? Koliko si procesora omogucio za paralelno procesiranje i koliki ti je treshold koji za pocetak paralelnog procesiranja? Da li si smestio mdf na RAID? Svi ti odgovori bi pomogli da se tacno odredi dijagnoza.

1. Ali kao prva pomoc mogu da ti kazem da proveris kako si konfigurisao tvoju bazu. Izgleda da imas problema sa 'statistikom'. Ti si 'creation' i 'update' statistike u bazi setovao na automatski mod - vrlo verovatno. To mozes da proveris ako otvoris 'Enterprise Manager', kliknes desno dugme na imenu baze i izaberes opciju 'Properties'. Sada klikni na 'tab' sa imenom 'Options'. Tu mozes da vidis da li ti je 'statistika' automatski ('auto update statistics' i 'auto create statistics'). Skini statistiku sa automatskog moda i zatvori 'window 'properties'. Jos uvek nije gotovo jer treba da napravis 'SQL Server job' koji ce preko noci da updejtuje i kreira statistiku (kada nema mnogo korisnika base ciji ce pristup bazi biti usporen). SQL skript za creiranje nove statistike i update mozes naci na internetu na mnogo sajtova (ako nemas ja cu ti ga priloziti). Kada kreiras job napravi schedule za svaku noc. Ako imas vec neke batch SQL jobove preko noci koji brisu, azuriraju ili ubacuju nove redove onda je dobro da jobove za statistiku izvrsis dva puta. Prvi put pre nego sto pocne obrada podataka i ujutru pre nego sto se vecina korisnika loguje na bazu.
U sustini sta se desava u tvom SQL Serveru. Ti sada izvrsavas SP i druge skriptove koji masovno menjaju podatke u tvojim tabelama. Posto je SQL Server najautomatizovanija baza podataka on u jednom trenutku odluci da azurira statistiku (statistika je veoma vazna jer na osnovu nje i drugih parametara koje nemam vremena da opisujem ovde SQL Server kreira 'EXECUTION PLAN'). Kada pocne to da radi on uspori sve usere i to ti je kao lancana reakcija.

Po tvom opisu izgleda da je problem u statistici, ali zapamti da ako ne odrzavas bazu redovno kao na primer defragmentaca svake noci, reindex nedeljno ... itd) desavace ti se cudne stvari na tvom SQL Serveru. Ali ni odrzavanje ne moze da ti pomogne ako ti dizajn baze nije dobar. Sta to znaci? Ako nemas clustered unique index na tabeli, mozes da reindeksiras svakih 2 sata i to ti nece pomoci jer samo clustered index kreira fizicki 'countinious' data na disku.. itd itd.

Pogledaj da li si setovao rast tvojih .ldf i .mdf na automatski (na primer 10%). Ako jesi to nije dobro takodje. Moras da kreiras 'pages' rucno - dodaj sto je moguce vise memorije na disku za .mdf file. i redovno prati sta se desava (ili postavi e-mail alert)da nse ne bi desilo da ostanes bez prostora u bazi. Tako izbegavas 'Split pages' i veliku fragnmentaciju za 'extends'/'pages' a i usporavace ti usere kada god se memorija koja je dostupna smanji ispod 5% od upotrebljenje.

Pozdrav,
Milovan
 
Odgovor na temu

Mrav
Aleksandar Mraović
.net programer u Wireless Media
Beograd

Član broj: 6532
Poruke: 279
*.smin.sezampro.yu.

ICQ: 197419540


Profil

icon Re: Za mene jako cudan SQL problem10.07.2005. u 20:04 - pre 227 meseci
Kada napraviš stored proceduru ona se na neki način kompajlira (prevodi se u komande nižeg nivoa za bazu) kako bi se što brže izvršavala sa trenutnim stanjem baze. Sam si rekao da se dok izvršavaš tu proceduru izvodi mnogo update, delete i insert operacija, što logično menja stanje u bazi (pomeraju se indeksi, fizički raspored podataka i sl.). Kada uradiš alter proc ona se u stvari pri tome i rekompajlira što dovodi do ubrzanja (jer se procedura ponovo optimizuje za trenutno stanje baze).

sp_recompile je dobar hint. Možda bi mogao da napraviš proceduru koja će se pozivati u nekim vremenskim razmacima a koja bi ti rekompajlirala spornu proceduru.

Ne verujem da je najveći udar na performanse ovde zbog statistika ili nečeg sličnog, a potvrda za to je da kada uradiš alter proc vraćaš performanse na početne (koliko sam ja razumeo iz posta)
Lepota je u jednostavnosti.

Cis.
 
Odgovor na temu

MilovanB
Sydney

Član broj: 61367
Poruke: 21
*.flexirent.com.



Profil

icon Re: Za mene jako cudan SQL problem11.07.2005. u 03:34 - pre 227 meseci
Mrav , kada nisi siguran u ono sto predlazes, bolje da ne dajes misljenje jer mozes da zbunis one koji citaju. Rekompilacija 'Stored Procedures' je sub-set od kreiranja nove statistike. SQL Server !!! AUTOMATSKI !!! izvrsava rekompilaciju STORED PROCEDURES u sledecim okolnostima:

1) KADA KREIRAS NOVU STATISTIKU

2) KADA SQL SERVER 'AGE OUT' EXECUTION PLAN ZA TU 'STORED PROCEDURE' IZ CASH MEMORIJE

3) KADA DROPUJES BILO KOJI INDEX BILO KOJE TABELE, KOJI SE UPOTREBLJAVA U 'EXECUTION PLAN'-u TE 'STORED PROCEDURE'

4) KADA SE IZVRSI VELIKI BROJ INSERTA, DELETE i UPDATE NA BILO KOJOJ TABELI KOJA SE KORISTI U OKVIRU TE 'STORED PROCEDURE'

5) KADA RESTORUJES DATABASE GDE SE NALAZI BILO KOJI OBJEKAT KOJI SE POZIVA IZ TE 'STORED PROCEDURE'

6) KADA U OKVIRU 'STORED PROCEDURE' SU UKLJUCENE DDL (Data Definition Language) I DML (Data Manipulation Language) NAREDBE, I IZVRSAVAJU SE JEDNA POSLE DRUGE.

7) KADA SE PROMENI SKEMA BILO KOG OBJEKTA KOJI SE POZIVA IZ TE STORED PROCEDURE, KAO NA PROMER: DODAVANJE NOVE KOLONE ILI DROPOVANJE 'RULES', 'DEFAULTS' I 'CONSTRAINTS'

8) KADA 'STORED PROCEDURE' KREIRA, DROPUJE ILI VRSI PROMENE NA SEMI OD TEMPORARNIH TABELA.

9) I NA KRAJU AKO KREIRAS 'STORED PROCEDURE' SA OPCIJOM 'WITH RECOMPILE' ILI UKLJUCIS OPCIJU 'WITH RECOMPILE' KADA POZIVAS STORED PROCEDURE SA 'EXEC' NAREDBOM.

Normalno mozes i da rekompajliras SP direktno pozivajuci sistemsku stored proceduru sp_recompile Ta operacija moze da ti totalno degradira performancu tvoga sistema. Pogledaj tacku 9 (opcija WITH RECOMPILE).

Kao sto vidis kreiranje statistike je direktno u cvezi sa rekompajliranjem stored procedure. Statistika je veoma vazna!

Ja mislim da je u slucaju Predragove stored procedure sasvim suprotan slucaj. Proceduru uopste ne treba rekompajlirati. Treba koristiti EXECUTION PLAN koji se nalazi u memoriji. KADA POZIVAS CESTO SP I ONA SE REKOMPAJLIRA SVAKI PUT (na primer jer je tvoja statistika na AUTO) onda performanca tvog database servera je mizerna. REKOMPAJLIRANJE UZIMA MNOGO RESOURCES I NE PREPORUCUJE SE CESTO. KOMPAJLIRANJE SP BLOKIRA DRUGE KORISNIKE. U SLUCAJU DA SE SP ZOVE CESTO U OKVIRU BATCH JOBA !!!REKOMPAJLIRANJE !!! je totalna degradacija u performanci database. SQL Server koristi EXECUTION PLAN KOJI SE VEC NALAZI U MEMORIJI. EXECUTION PLAN MOZAS DA PROVERIS IZ Query Analyser-a, i vidis da li pristup odredjenim tabelama koristi 'SCAN' (lose) ili 'SEEK' (dobro jer koristi indexe). STORED PROCEDURE re-use isti EXECUTION PLAN i tako cuva resources za vaznije poslove nego sto je re-kompilacija.

Ako bu rekompajlirali SP kaosto predlazu SASAS i MRAV nista ne bismo uradili, jer Predrag treba da uradi totalno suprotno: DA IZBEGNE REKOMPILACIJU kod svakog izvrsavanja te stored procedure!

Evo ti nekoliko izvornih tekstove na Engleskom, nadam se da ti nece biti veliki problem da razumes:

Pozdrav,
Milovan
--------------------------------------------------------------------------------

One hidden performance problem of using stored procedures is when a stored procedure recompiles too often. Normally, you want a stored procedure to compile once and to be stored in SQL Server's cache so that it can be re-used without it having to recompile each time it is used. This is one of the major benefits of using stored procedures.

But in some cases, a stored procedure is recompiled much more often than it needs to be recompiled, hurting your server's performance. In fact, it is possible for a stored procedure to have to be recompiled while it is executing!

Here are three potential problems you want to look out for when writing stored procedures.

Unnecessary Stored Procedure Recompilations Due to Row Modifications and Automated Statistics Update

If your database has the "Auto Update Statistics" database option turned on, SQL Server will periodically automatically update the index statistics. On a busy database, this could happen many times each hour. Normally, this is a good thing because the Query Optimizer needs current index statistics if it is to make good query plan decisions. One side effect of this is that this also causes any stored procedures that reference these tables to be recompiled. Again, this is normal, as you don't want a stored procedure to be running an outdated query plan. But again, sometimes stored procedures recompile more than they have to. Here are some suggestions on how to reduce some of the unnecessary recompilations:

Use sp_executesql instead of EXECUTE to run Transact-SQL strings in your stored procedures.



Instead of writing one very large stored procedure, instead break down the stored procedure into two or more sub-procedures, and call then from a controlling stored procedure.



If your stored procedure is using temporary tables, use the KEEP PLAN query hint, which is used to stop stored procedure recompilations caused by more than six changes in a temporary table, which is the normal behavior. This hint should only be used for stored procedures than access temporary tables a lot, but don't make many changes to them. If many changes are made, then don't use this hint.

Unnecessary Stored Procedure Recompilations Due to Mixing DDL and DML Statements in the Same Stored Procedure

If you have a DDL (Data Definition Language) statement in your stored procedure, the stored procedure will automatically recompile when it runs across a DML (Data Manipulation Language) statement for the first time. And if you intermix both DDL and DML many times in your stored procedure, this will force a recompilation every time it happens, hurting performance.

To prevent unnecessary stored procedure recompilations, you should include all of your DDL statements at the first of the stored procedure so they are not intermingled with DML statements.

Unnecessary Stored Procedure Recompilations Due to Specific Temporary Table Operations

Improper use of temporary tables in a stored procedure can force them to be recompiled every time the stored procedure is run. Here's how to prevent this from happening:

Any references to temporary tables in your stored procedure should only refer to tables created by that stored procedure, not to temporary tables created outside your stored procedure, or in a string executed using either the sp_executesql or the EXECUTE statement.



All of the statements in your stored procedure that include the name of a temporary table should appear syntactically after the temporary table.



The stored procedure should not declare any cursors that refer to a temporary table.



Any statements in a stored procedure that refer to a temporary table should precede any DROP TABLE statement found in the stored procedure.



The stored procedure should not create temporary tables inside a control-of-flow statement.

[7.0, 2000] Updated 10-18-2003 More from Microsoft

*****

To find out if your SQL Server is experiencing excessive recompilations of stored procedures, a common cause of poor performance, create a trace using Profiler and track the SP:Recompile event. A large number of recompilations should be an indicator if you potentially have a problem. Identify which stored procedures are causing the problem, and then take correction action (if possible) to reduce or eliminate these excessive recompilations. [7.0, 2000] Updated 10-18-2003

*****

Stored procedures can better boost performance if they are called via Microsoft Transaction Server (MTS) instead of being called directly from your application. A stored procedure can be reused from the procedure cache only if the connection settings calling the stored procedure are the same. If different connections call a stored procedure, SQL Server must load a separate copy of the stored procedure for each connection, which somewhat defeats the purpose of stored procedures.

But if the same connection calls a stored procedure, it can be used over and over from the procedure cache. The advantage of Transaction Server is that it reuses connections, which means that stored procedures can be reused more often. If you write an application where every user opens their own connection, then stored procedures cannot be reused as often, reducing performance. [7.0, 2000] Updated 6-21-2004
-------------------------------------------------------------------------------












 
Odgovor na temu

ivan jeremic
Bgd

Član broj: 51138
Poruke: 48
80.93.234.*



Profil

icon Re: Za mene jako cudan SQL problem11.07.2005. u 16:03 - pre 227 meseci
with recompile
 
Odgovor na temu

MilovanB
Sydney

Član broj: 61367
Poruke: 21
*.flexirent.com.



Profil

icon Re: Za mene jako cudan SQL problem12.07.2005. u 01:12 - pre 227 meseci
Ivane Jeremija,

Veoma intelegentan zakljucak. Mozes li nama ostalima da objasnis kako si uspeo da dodjes do njega pa da se i mi prosvetlimo!

 
Odgovor na temu

jablan

Član broj: 8286
Poruke: 4541



+710 Profil

icon Re: Za mene jako cudan SQL problem12.07.2005. u 08:33 - pre 227 meseci
Milovane, hvala na dobrom postu, sve što si rekao zvuči prilično logično i nemoj obraćati pažnju na druge savete, neko kome to treba će probati jednu i drugu varijantu i videće koja mu radi posao, a koja ne...

Jedno pitanje za tebe:
Ako meni baza tokom vremena ne menja previše "oblik", to jest neke tabele koje se konstantno menjaju i dalje se konstantno menjaju, a neke koje ne - ne, ima li logike potpuno isključiti automatski update statistike (tj. da se ne koristi ni noćni job koji si pomenuo)?
 
Odgovor na temu

ivan jeremic
Bgd

Član broj: 51138
Poruke: 48
80.93.234.*



Profil

icon Re: Za mene jako cudan SQL problem12.07.2005. u 09:04 - pre 227 meseci
Pazi, execution plan stoji u memoriji nakon prvog izvrsavanja stored procedure. Ukoliko nema strukturnih promena nad objektima na koje gleda taj execution plan stoji jako dugo tamo.
"With recompile" treba uvek koristiti iz prostog razloga sto je jedan execution plan u najvecem broju slucajeva dobar samo za par opsega za koje se traze podaci. Recimo izmedju 01.01.2005. i 01.05.2005 mozda ima 5 slogova i on ce koristiti index koji je dignut nad tim datumskim poljem dok od 01.05.2005. do 31.12.2005. moze imati 5.000.000 slogova i on nece koristiti index jer mu se jednostavno ne isplati. Sve u svemu execution plan se menja u zavisnosti od strukture podataka u nekom opsegu a bez with recompile on koristi prvi koji je napravio za opseg za koji je inicijalno zadat.

Posto ti se struktura podataka menja server sve vise i vise gresi u procenama (tj. sto je najgore ne gresi vec ima prastare procene) i procedure se sve duze i duze rade koceci jedna drugu zbog pogresno izabranog zakljucavanja.

Na tvom mestu ja bih proverio i statistiku tj. postavio bih job koji jednom nedeljno (ili cesce) azurira statistiku nad tom tabelom i radi reindexaciju. Znam ja da statistiku server update-uje sam ali ko je radio sa tabelama nad kojima se radi mnogo pojedinacnih izmena zna da se on zaglupi tj. posto ne moze stalno da azurura statistiku on to radi kada proceni da treba da se uradi ... a to mu ne ide bas najbolje :)

 
Odgovor na temu

Mrav
Aleksandar Mraović
.net programer u Wireless Media
Beograd

Član broj: 6532
Poruke: 279
*.smin.sezampro.yu.

ICQ: 197419540


Profil

icon Re: Za mene jako cudan SQL problem12.07.2005. u 19:16 - pre 227 meseci
Mislim, šta reći?
Lepota je u jednostavnosti.

Cis.
 
Odgovor na temu

MilovanB
Sydney

Član broj: 61367
Poruke: 21
*.flexirent.com.



Profil

icon Re: Za mene jako cudan SQL problem14.07.2005. u 00:28 - pre 227 meseci
Jablane,

Hvala na komentaru. Nemoj da nikako izbacujes statistiku is upotrebe, jer je statistika jedan od cinilaca koji ce da trigeruje rekompajliranje za SP. Ja imam preko 600 konkurentnih usera na samo jednoj financijskoj aplikaciji - financijska intranet aplikacija koji non stop izvrsavaju stored procedures na bazi cija je velicina nesto vise od 500GB, a vece tabele oko 20-25GB. Baza mi raste nedeljno u proseku od 2GB. Ako jednu noc ne izvrsim statistiku, onda sam sutradan u velikom problemu. Voleo bih da dodam da nikako ne teras SQL Server da re-compajlira SP sa "with recompile". On ce to uraditi sam kada budes radio statistiku. Kada su ti tabele sa malim brojem redova SQL Server nece uopsete koristiti indekse (seek) vec ce skanirati tabele.

To je suprotno od onoga sto predlaze Ivan ali se slazemo bar, ali u zadnjem postu on sam sebi kontradiktuje. Prvo kaze da rekompilaciju treba raditi uvek sa uslovom "with recompile" a posle toga da treba redovno raditi statistiku?
Trebalo bi da zna da promena u statistici automatski trigeruje rekompilaciju SP.

Veoma je vazno da vidis kako su ti indexi definisani i kolika je logicka fragmentacija. Svaka tabela bi trebala da ima clustered index.

Pozdrav,
Milovan
 
Odgovor na temu

ivan jeremic
Bgd

Član broj: 51138
Poruke: 48
80.93.234.*



Profil

icon Re: Za mene jako cudan SQL problem14.07.2005. u 09:41 - pre 227 meseci
Milovan je verovatno u pravu po pitanju toga da update statistika rekompajlira stored procedure.. nisam se raspitivao oko toga .. ali ljudi ...mislim... recompile stored procedure sa with recompile mora da se radi. U toku dana ces ti 100 puta da je okines sa razlicitim parametrima a ona ce da radi po execution plan-u od prvog puta koji je verovatno pogresan. To sa performansama verovatno ima smisla sto se tice nekih malih bazica inace za x00GB baze upit traje mnogo duze od kreiranja execution plan-a a ako izabere pogresan ... pa mislim ... trajace! Ima stored procedura gde ne vredi stavljati with recompile ali to je vec od procedure do procedure ... nadam se da ova tvoja nije jedna od tih jerbo smo se onda dzaba ovoliko dopisivali ... i javi bre sta si uradio!
 
Odgovor na temu

pota0311

Član broj: 63065
Poruke: 4
212.200.27.*



Profil

icon Re: Za mene jako cudan SQL problem15.07.2005. u 12:35 - pre 227 meseci
Evo, sedam dana posle objavljivanja posta da se i ja ponovo javim.

Prvo da se zahvalim svima vama na vrlo detaljnim odgovorima.

Da pojasnim malo moju bazu.

1. Baza je za mene ogromna, tj. oko 50GB u data file-u. Raste oko 2-3GB mesecno. Neke tabele imaju i do 30M recorda.

2. Baza je non-stop aktivna. U principu to je od nedelje 22:00 do subote u 14:00, ali nekad se radi i nedeljom.

3. Tacno je da imam automatic update statisics.

4. Imam i automatic (10%) grow za oba file-a: data i transaction log.

5. Sve tabele imaju uredne clustered indexe.

6. Server je 8 procesorski XEON. SQL korisiti svih 8.

7. Memorije ima dovoljno i dinamicka je.

8. Imam samo SQL na toj masini.

Problem je sto zbog non-stop proizvodnje poslednjih 10-ak dana nemam priliku da testiram vase predloge. Ja sam deo tima koji je radio razvoj DB aplikacije, ali treba i da ponudimo kupcu na koji nacin da odrzava bazu. Naravno prvo treba da zadovoljimo i funkcionalnost, koju nemamo 100% kada baza posle izvesnog vremena (nekad 4-5 sati, nekad 1-2 dana) uspori.

Elem, ja za sada samo "studiram" vase odgovore.

Pretpostavljam da bi "with sp_recompile" pomoglo oko execution plana, ali koliko bi to usporilo svaki poziv ove stored procedure? Mislim da bi na kraju imao isti mozda i losiji vremenski rezultat.

Job za "rucni" update statistike bi mozda bilo pravo resenje, ali kada ga izvrsavati? U mojoj aplikacije nema dan-noc, non stop je isto opterecenje baze, jer je proizvodnja u tri smene, a novi podaci stizu iz procesa. Pitanje je koliko traje update statistike. Mogao bih da ga postavim u nekom periodu kada ocekujem da nema nekih drugih akcija nad bazom (pored inserta, i trigera on insert kojih prakticno ima non-stop, svakih 100-200ms). Da li je baza prilikom update "lockovana", kao prilikom "srink-a". To ne bih smeo da dozvolim u toku proizvodnje. I neko zacajnije usporenje ne bih smeo da imam u periodu od vise od 10-ak minuta.

Pitanje je i da li moram da restartujem DB server ako zelim iskljuciti pomenute "auto" parametre.

Nadam se da cu ovaj vikend imati prilike da istestiram vase predloge.

Hvala,

Predrag
 
Odgovor na temu

Milovan_B
DBA
Sydney

Član broj: 61018
Poruke: 11
*.nsw.bigpond.net.au.



Profil

icon Re: Za mene jako cudan SQL problem16.07.2005. u 12:14 - pre 227 meseci
Predraze,

Pretpostavljam da tvoj server ima 4 fizicka (8 logickih - multi-treading tehnologija) procesora. 50GB baza spada u manje baze i ne bi trebao da imas mnogo problema. Srednje baze su do 200GB, a vece iddu i do nekoliko terabajta.
Prvo treba da setujes ispravno SQL Server. Verujem da nije setovan kako treba.


1. Kao prvo idi u properties lokalnog sql servera (klikni desno dugme na serveru - Enterprise Manager). Proveri kako ti je konfigurisana memorija (izaberi tag na topu windowsa - Memory'). Posto kazes da ti je server dedicated samo za SQL Server i ako ti je memorija konfigurisana dinamicki onda 'NIJE DOBRO' (nisi dao info koliko memorije imas na serveru i da li koristis AWE). Promeni konfiguraciju u 'Fixed memory' (to znaci da NE koristis max. i min. slides od memorije za SQL Server vec samo treci slajd odozgo na dole). Daj SQL serveru sto vise memorije. Pomeri slaid skoro do granice koja je u zutoj boji na desnoj strani slajda. Sto znaci da za operativni system i ostale programe ostavljas samo onoliko koliko je potrebno da normalno rade. Ne zaboravi da tiknes 'Reserve phisical memory for SQL Server' - to je vazno takodje. Moraces da restartujes SQL services (control panel/administrative tools/Services - "MS SQL Server" i "SQL Server Agent"), ali bih ti preporucio da restartujes operativni system.

2. Kao drugo izaberi opciju (tag) 'Processor'. Iskljuci zadnji procesor (u tvom slucaju procesor No. 7). 'U tom slucaju ce postati 'nevidljiv' za SQL Server. NEMOJ NIKAKO da koristis opcije 'BOOST SQL SERVER PRIORITY ON WINDOWS' i 'USE WINDOWS NT FIBRES'.
Verujem da je upotreba tvojih procesora setovana na 'Use all avaliable procesors' i to 'NIJE DOBRO'. Setuj ga da koristi samo sedam (7) procesora. Pogledaj na koliko sekundi je setovan 'Minimum query plan treshold for paralel execution'. Setuj ga na pet (5) sekundi - za pocetak. Kada bi ovde objasnavao kako sve radi onda bi morao da pisem knjigu ali dacu ti kratko objasnjenje - zasto.
SQL Server je 'NEZAJAZLJIV' i ako mu das sve procesore borice se sa operativnim systemom. Ako mu onemogucis jedan procesor onda ce operativni system biti oslobodjen. Ako SQL Server ne moze da vidi zadji procesor, OS ce automatski da ga koristi za svoje operacije i potrebne service na serveru. Nisi naveo da li imas applikativne servere. Ako oslobodis prvi procesor (obelezen kao nulti (0)) onda ce on biti koriscen od strane NIC kartica za komunikaciju sa applikativnim i 'batch' serverima. Kada sql server dobije zadatak da odradi stord proceduru ili queri, on se pridrzava execution plana. Ako je veci queri ili SP on ga podeli u delove i izvrsava paralelno (samo kod masina sa vise od jednog procesora). Na svakom procesoru se izvrsava drugi 'TREAD' koji je deo istog querija ili SP. Uvek se desava da jedan ili vise tread-ova cekaju na druge da bi mogli da nastave sa obradom. Tu nastaje interno blokiranje sto veoma cesto prouzrokuje da paralelno procesiranje uzme mnogo vise vremena nego na jednom procesoru. U mom systemu gde imam tabele preko 20GB nijedna SP ne ide preko 8 sekundi. Nemoj da dajes mogucnost SQL Serveru da ide u paralelno procesiranje kao 'grlom u jagode'. Drzi ga na dizginama jer ti upravljas sa njim. Sta to zanaci = dozvoli mu da koristi paralelno procesiranje samo ako execution plan proceni da ce izvrsenje biti duze od 5 sekundi - za pocetak.

3. Statistika ne lokuje usere (za razliku od REINDEX-iranje) i ne uzima mnogo vremena. Radi je rucno u vreme kada ima najmanje usera na systemu.

4. Kazes da svaka tabela ima unique clustered index (pazi - nemoj da to mesas sa constraints - PK). Pretpostavljam da bar jednom nedeljno vrsis REINDEX. Na dnevnoj bazi treba da vrsis de-fragmentaciju (za razliku od reindexa ona ne blokira usere ali radi samo na nivou 'PAGE' - za EXTENT fizicki order trebas da redovno reindexiras).

5. Proveravaj tbele sa DBCC SHOWCONTIG - i rast tabela tako da mozes pravilno odredis 'FILL FACTOR'. Stavi 100% fill faktor za sve 'lookup tabele, a za one gde imas mnogo inserta nemoj da ides nize od 75% jer je bolje da cesce reindexiras nego da ti fizicki pristup podacima traje duze.

6 Ako ti se mdf nalazi na RAID 5, promeni na RAID 10 i dobices duplo brze pisanje na disku.

7 Koji recovery metod koristis? Ako koristis 'SIMPLE' promeni backups u full/differential/log tako da ces imati vise vremena za batch jobove.

8 Promeni automatski grow i napravi job koji ce da te alertuje kada stignes na 15% jer automatsko prosiravanje baze usporava korisnike jer SQL Server prosiruje mdf i ldf obicno u najkriticnijim momentima

9. Proceuj da li ti tempdb ima dovoljno prostora. Ako koristis mnogo temporary tabele ili sub-Querije onda ti treba dosta prostora u tempdb, Nemoj nikako da je srinkujes!

Ima jos mnogo stvari koje mozes da setujes ali sve to zavisi od tvojih querija i indexa. Pokusavaj da koristis sto je moguce vise indexovane views. Podizanje performanci trazi iskustvo ne samo u SQL Serveru nego i u aplikativnom programiranju.

Pozdrav
Milovan
 
Odgovor na temu

pota0311

Član broj: 63065
Poruke: 4
212.200.27.*



Profil

icon Re: Za mene jako cudan SQL problem15.09.2005. u 20:53 - pre 225 meseci
Evo mene ponovo posle duzeg vremena.

Moram da priznam da se nisam javljao sve cekajuci da nadjem odgovarajuce resenje za problem koji sam opisao u prvom post-u.

Na moju zalost stanje je isto kao i pre, ak ne i gore.

Nista od predlozenih resenja ne pomaze. Jednostavno samo kada uradim alter procedure sve se vrati na normalnu brzinu i to tak radi par sati kada opet naglo uspori. Ako neko ima jos nekih predloga, neka se molim javi.

Drugi problem koji mi se u medjuvremenu pojavio je sledeci:

Imam triger instead of insert na tabeli ALARM_LOG.



ALTER TRIGGER dbo.ALARM_LOG_TRIGG ON dbo.ALARM_LOG
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
DECLARE @timestamp datetime,
@sequence_number integer,
@alarm_id varchar(32),
@alarm_class varchar(5),
@resource varchar(16),
@logged_by varchar(32),
@reference varchar(32),
@prev_state varchar(1),
@log_action varchar(1),
@final_state varchar(1),
@alarm_message varchar(80),
@generation_time datetime,
@project varchar(21),
@openRecords integer;


DECLARE alarm_log_cursor CURSOR FAST_FORWARD FOR
SELECT [timestamp], alarm_id, alarm_class, resource,
logged_by, reference, prev_state, log_action, final_state,
alarm_message, generation_time, project
FROM inserted

OPEN alarm_log_cursor

FETCH NEXT FROM alarm_log_cursor INTO
@timestamp, @alarm_id, @alarm_class, @resource,
@logged_by, @reference, @prev_state, @log_action, @final_state,
@alarm_message, @generation_time, @project


declare @date datetime
declare @workday datetime
declare @h int
declare @min int
declare @shift int
declare @seq_nr int

declare @exact_time datetime --extra logging when record is inserted into alarm_log
SET @exact_time = getdate()

--select @date = max(com_time)from actual_shift_schedule
--select @shift = max(shift_nr) from actual_shift_schedule
--check also area id
select @date = max(com_time)from actual_shift_schedule where area_id IN (select area_id from AREA_ASSIGNMENT where resource_id = @resource)
select @shift = max(shift_nr) from actual_shift_schedule where area_id IN (select area_id from AREA_ASSIGNMENT where resource_id = @resource)

select @h = datepart(hh,@date)
select @min = datepart(mi,@date)

select @workday = DATEADD (hh, -@h,@date)
select @workday = DATEADD (mi, -@min,@workday)


WHILE @@FETCH_STATUS = 0
BEGIN
-- Check for special treatment of system alarms
IF (@alarm_class = '$SYS')
BEGIN
IF (@log_action = 'G')
BEGIN
IF (@alarm_id = '$ALARM.ACTIVE')
BEGIN
-- Close all open alarms of the same project
UPDATE ALARM_LOG
SET log_action = 'R', final_state = 'R'
WHERE (project = @project) and (final_state in ('A', 'G'))




-- Insert the alarm itself for monitoring purpose
INSERT INTO ALARM_LOG
([timestamp], alarm_id, alarm_class, resource,
logged_by, reference, prev_state, log_action, final_state,
alarm_message, generation_time, project)
VALUES
(@timestamp, @alarm_id, @alarm_class, '',
@logged_by, @reference, 'R', 'G', 'R',
@alarm_message, @timestamp, @project)

-- Sven de Bleyser: insert workday and shiftnumber into ALARM_LOG_TABLE

set @seq_nr = @@identity

INSERT INTO ALARM_LOG_DATA
(sequence_number, gen_workday, gen_shift, exact_gen_time)
VALUES
(@seq_nr, @workday, @shift, @exact_time)

-- select * from ALARM_LOG_DATA

-- END
END
IF (@alarm_id = 'ALE_ALIVE')
BEGIN
-- Elongate all open alarms of the same project
-- to the current timestamp
UPDATE ALARM_LOG
SET [timestamp] = @generation_time
WHERE ([timestamp] < @generation_time)
and (project = @project) and (final_state in ('A', 'G'))
END
END
END
-- Ignore comment records, they do not provide any valuable information
ELSE IF (@log_action <> 'C')
BEGIN
-- Simplify the action / state information of the new record
IF (@prev_state in ('A', 'G')) SET @prev_state = 'G'
ELSE SET @prev_state = 'R';

IF (@final_state in ('A', 'G')) SET @final_state = 'G'
ELSE SET @final_state = 'R';

-- Check, whether there is a previous "open" record with the same alarm_id
SELECT @openRecords = COUNT (*)
FROM ALARM_LOG
WHERE (alarm_id = @alarm_id) and (final_state in ('A', 'G'))

IF (@openRecords = 0)
BEGIN
-- There is no previous "open" record with the same alarm_id
IF (@prev_state = 'R')
BEGIN
IF (@final_state = 'R')
BEGIN
-- Ignore R -> R transitions
SET @log_action = '';
END
ELSE -- @final_state = 'G'
BEGIN
-- Start of an alarm occurrence
SET @log_action = 'G';
SET @timestamp = @generation_time;
END
END
ELSE -- @prev_state = 'G'
BEGIN
IF (@final_state = 'R')
BEGIN
-- End of an alarm occurrence
SET @log_action = 'R';
END
ELSE -- @final_state = 'G'
BEGIN
-- Continuation, but without a previous start record
SET @log_action = 'G';
END
END
END
ELSE
BEGIN
-- There is already a previous "open" record with the same alarm_id
IF (@prev_state = 'R')
BEGIN
-- We must have missed, that the "open" alarm has been closed in the meanwhile
-- Update and close all "open" records now with the generation_time of the new record

-- SDB get sequence number before update
-- select @seq_nr = sequence_number from alarm_log WITH (NOLOCK)
-- where alarm_id = @alarm_id and final_state in ('A','G')
-- end

UPDATE ALARM_LOG
SET [timestamp] = @generation_time, log_action = 'R', final_state = 'R', @seq_nr = sequence_number
WHERE (alarm_id = @alarm_id) and (final_state in ('A', 'G'))


-- Sven de Bleyser: insert closed workday and closed shiftnumber into ALARM_LOG_TABLE


UPDATE ALARM_LOG_DATA
SET closed_workday = @workday, closed_shift = @shift
WHERE (sequence_number = @seq_nr)


-- END

IF (@final_state = 'R')
BEGIN
-- Ignore R -> R transitions
SET @log_action = '';
END
ELSE -- @final_state = 'G'
BEGIN
-- Start of an alarm occurrence
SET @log_action = 'G';
SET @timestamp = @generation_time;
END
END
ELSE -- @prev_state = 'G'
BEGIN
IF (@final_state = 'R')
BEGIN
-- End of an alarm occurrence
-- Update and close all "open" records with the timestamp of the new record




UPDATE ALARM_LOG
SET [timestamp] = @timestamp, log_action = 'R', final_state = 'R', @seq_nr = sequence_number
WHERE (alarm_id = @alarm_id) and (final_state in ('A', 'G'))





UPDATE ALARM_LOG_DATA
SET closed_workday = @workday, closed_shift = @shift
WHERE (sequence_number = @seq_nr)

-- END

-- Do not insert a new record
SET @log_action = '';
END
ELSE -- @final_state = 'G'
BEGIN
-- Continuation of a previous start record
-- Update all "open" records with the timestamp of the new record
UPDATE ALARM_LOG
SET [timestamp] = @timestamp, log_action = 'G', final_state = 'G'
WHERE (alarm_id = @alarm_id) and (final_state in ('A', 'G'))
-- Do not insert a new record
SET @log_action = '';
END
END
END

IF (@log_action <> '')
BEGIN
-- Insert the new record
INSERT INTO ALARM_LOG
([timestamp], alarm_id, alarm_class, resource,
logged_by, reference, prev_state, log_action, final_state,
alarm_message, generation_time, project)
VALUES
(@timestamp, @alarm_id, @alarm_class, @resource,
@logged_by, @reference, @prev_state, @log_action, @final_state,
@alarm_message, @generation_time, @project)



set @seq_nr = @@identity

INSERT INTO ALARM_LOG_DATA
(sequence_number, gen_workday, gen_shift, exact_gen_time)
VALUES
(@seq_nr, @workday, @shift, @exact_time)

-- END

END

exec ALM_Conveyors_Update @resource, @alarm_class

END

FETCH NEXT FROM alarm_log_cursor INTO
@timestamp, @alarm_id, @alarm_class, @resource,
@logged_by, @reference, @prev_state, @log_action, @final_state,
@alarm_message, @generation_time, @project

END

CLOSE alarm_log_cursor
DEALLOCATE alarm_log_cursor

END



4 razlicita procesa non stop loguju u tu tabelu. Vrlo cesto imam deadlock na UPDATE ALARM_LOG_DATA delu (update druge tabele iz trigera).

Da li to znaci da se trigeri izvrsavju paralelno na istoj tabeli ?!

Pozdrav,
Predrag
 
Odgovor na temu

majstor01
Majstor 01
Nis

Član broj: 68717
Poruke: 12
*.my-its.net.



Profil

icon Re: Za mene jako cudan SQL problem23.09.2005. u 00:20 - pre 225 meseci
Negde imas mrtvu petlju pozivanja u aplikaciji!
 
Odgovor na temu

[es] :: MS SQL :: Za mene jako cudan SQL problem

[ Pregleda: 5506 | Odgovora: 15 ] > FB > Twit

Postavi temu Odgovori

Navigacija
Lista poslednjih: 16, 32, 64, 128 poruka.