Database server
-
Hi all,
I need some advises about hardware and software configuration of a database server. I am new as DBA employer. I have noticed that there are a lot of BlkBy issues along one business day. There is an ERP system which uses inside its stored procedures a lot of temporary tables (#tTable). This means that tempdb is very often used.
The actual configuration of DB server is:
Quad-processor system with 4 GB RAM.
Logical Drives
Drive
Drive Type
File System
Volume Serial
Total Size
Free Space
% Free
C: (SO)
Local Disk
NTFS
6416-00C3
14998 MB
4134 MB
28 %
D:
Optical Drive
E: (PART2 OS & SEC DBs)
Local Disk
NTFS
DC90-FFC2
19994 MB
5786 MB
29 %
F: (OTHER DBs)
Local Disk
NTFS
644A-A411
69994 MB
21961 MB
31 %
G: (MOST USED DB)
Local Disk
NTFS
38E1-AFA4
139996 MB
30333 MB
22 %
H: (BACKUP)
Local Disk
NTFS
3816-3EE7
190779 MB
31404 MB
16 %
Physical Drives
[ Drive #1 (186.3 GB) ]
Partition
Partition Type
Drive
Start Offset
Partition Length
#1
NTFS
H: (BACKUP)
0 MB
190779 MB
[ Drive #2 (34.2 GB) ]
Partition
Partition Type
Drive
Start Offset
Partition Length
#1 (Active)
NTFS
C: (OS)
0 MB
14998 MB
#2
NTFS
E: (PART2 OS & SEC DBs)
14998 MB
19994 MB
[ Drive #3 (68.4 GB) ]
Partition
Partition Type
Drive
Start Offset
Partition Length
#1
NTFS
F: (OTHER DBs)
0 MB
69994 MB
[ Drive #4 (136.7 GB) ]
Partition
Partition Type
Drive
Start Offset
Partition Length
#1
NTFS
G: (MOST USED DB)
0 MB
139996 MB
I am thinking the new configuration should be the following:
Procesors: 4 x Xeon
RAM: 8GB
Storage system:
a.73GB x 2 HDD RAID1 (mirror) – OS and system databases (master, msdb, model)
b.73GB x 2 HDD RAID1 (mirror) – Log file of MOST USED DB
c.73GB x 6 HDD RAID10 (RAID 10 a stripe of mirrors) – MOST USED DB
d.73GB x 6 HDD RAID10 (RAID 10 a stripe of mirrors) – indexes of MOST USED DB
e.73GB x 6 HDD RAID10 (RAID 10 a stripe of mirrors) – audit tables of MOST USED DB
f.73GB x 6 HDD RAID10 (RAID 10 a stripe of mirrors) – database and log of tempdb
g.73GB x 6 HDD RAID10 (RAID 10 a stripe of mirrors) – distribution, other databases
h.300GB x 3 HDD RAID 5 - backups
HDD should be SCSI 320 and 15000 rpm.
Should be minimum 1-2 controllers RAID(preferrable with battery of BK), I don't know if the actual RAID controller could maintain so much HDDs.
In Theory, theory and practice are the same...In practice, they are not. -
Just another consideration...
Split your datafiles from your transaction log files.
-
Hi,
Some thoughts:
1. I wouldn't put the system databases (basically any dbs) to the OS partition. I'd put them next to the distribution and other small dbs.
2. Blocking in itself is not a problem, the question is the duration of blocking. So it's possible that you'll see blockings after this redesign as well. Try to declare countable goals for the redesign before you do anything - that is, what would be the state of the server when you didn't want to optimize it. If there's a particular table or group of tables which are the subject of blocking issues then try to make faster the operations on them.
3. Separating the indexes from the data is a very interesting idea. In Oracle, it's a best and quite common practice, but in SQL Server I'm not so convinced about its benefits. I asked some experts and no one suggested me doing this. They suggested that instead of this I should separate the tables into more filegroups. Reading an index and the corresponding records is an operation but joining tables by indexes is another, and if your indexes are in the same filegroup then what's your profit? So it's a swampy area - however, I'm very interested in any thoughts in it. For partitioning into filegroups of course you should know your database usage very well.
-- Erik http://blog.rollback.hu
-
Hi,
Thanks for your replies.
Erik, your first point is interesting. Anyway, if something is damaged to system databases, it should be restored from backups
Second point. Obviously, my concern is about duration of blocking. I show you some issues about the most longest duration of execution of stored procedures:
Ave. Duration Ave. CPU Time Ave. Reads Ave. Writes Command Raw Duration Raw CPU Raw Reads Raw Writes Seek Database 00:17:35.8 00:00:00.0 671 0 -- GEN18_VerificaSerieNrDocumentIF EXISTS(SELECT 1 FROM facturic c WHERE c.nrfact = @nrdoc AND ISNULL(c.seriefact,'') = ISNULL(@serie,'')-- AND (@config_serie =0 OR (@config_serie=1 AND ISNULL(c.seriefact,'') = ISNULL(@serie,'')))-- AND (@config_user =0 OR (@config_user =1 AND c.coduser = @coduser))-- AND (@config_soc =0 OR (@config_soc =1 AND c.codsoc = @codsoc))-- AND (@config_firma =0 OR (@config_firma =1 AND c.codclient = @codfirma))-- AND (@config_gestiune =0 OR (@config_gestiune =1 AND c.codgest = @codgestiune))-- AND (@config_data =0 OR (@config_data =1 AND YEAR(c.datafact) = YEAR(@datadoc)) ) AND c.nrintfact <> ISNULL(@nrintdoc,0) ) 1055.826 0.047 671 0 159241 rcs 00:13:36.4 00:03:09.7 19,780,667 155,241 update calendar_documente set data_sf='2007-08-31' --an luna zi (sfarsitul lunii viitoare)where data_sf = '2007-07-31'--an luna zi (sfarsitul lunii curente) 816.384 189.728 19780667 155241 926784 definiri 00:11:33.4 00:05:26.7 36,114,500 1,859 -- MFX18_MfVizualizareSELECT m.mf, m.codmf, m.denumire, m.seriemf, pctlucru = ISNULL(i.pctlucru_primire, m.pctlucru), denumirepctlucru= p.denumire, m.valuta, m.nrinventar, m.cartehnice, m.duratafol, m.durataam, m.normaam, tipam = CASE WHEN tipam='L' THEN 'LINIARA' WHEN tipam='D' THEN 'DEGRESIVA AD1' WHEN tipam='S' THEN 'DEGRESIVA AD2' WHEN tipam='A' THEN 'ACCELERATA' END, m.datareceptie, m.datapif, m.valintrarelei, m.valintrarevaluta, valamlei_0 = m.valamlei, valamvaluta_0 = m.valamvaluta, m.cursintrare, m.dataiesire, m.cursiesire, m.valiesirelei, m.valiesirevaluta, mfbaza=cast(m.mfbaza as int), obinventar = cast(m.obinventar as int), furnizor=cast(m.furnizor as int), prodproprie=cast(m.prodproprie as int), capsoc=cast(m.capsoc as int), donatie=cast(m.donatie as int), leasing=cast(m.leasing as int), 693.358857142857 326.739142857143 36114500.2857143 1859.35714285714 777915 rcs_ias 00:10:06.6 00:00:00.0 349 1 -- CMP18_FacturaF_ActualizareMUPDATE facturifSET total=@total, tva=@tva, totalvaluta=@totalvaluta, obstext=@obstext, tvavaluta=@tvavaluta, totalcutaxevaluta=@totalcutaxevaluta, platit = ISNULL(@platit,platit), scadenta =@scadentaWHERE nrintfact=@nrintfact 606.5582 0.0406 349.4 1.4 2368700 rcs 00:09:40.3 00:00:00.0 2 0 -- sp_trace_getdataselect * from OpenRowset(TrcData, @traceid, @records) 580.346771428571 3.74571428571429E-02 1.71428571428571 0 90004 master 00:09:33.7 00:03:55.1 11,059,221 703 -- MFX18_RaportInventariereRCSvalutaCursDinBazaSELECT m.nrinventar, denumireMFX= m.denumire, centrup = ISNULL(i.pctlucru_primire, m.pctlucru), denumirecentrup= p.denumire, centruptata=convert(varchar(250), ''), denumirecentruptata = convert(varchar(250), ''), m.datareceptie, m.datapif,m.dataiesire, m.dataamteoretica, am =CASE WHEN m.dataamteoretica is not null AND m.am = 1 AND m.dataamteoretica<=@data then 1 ELSE 0 END, casat = CASE WHEN m.dataiesire is not null AND m.casat = 1 AND m.dataiesire<= @data then 1 ELSE 0 END, lipsainv = CASE WHEN m.dataiesire is not null AND m.casat = 2 AND m.dataiesire<=@data then 1 ELSE 0 END, vandut = CASE WHEN m.dataiesire is not null AND m.casat = 0 AND m.dataiesire<=@data then 1 ELSE 0 END, valamlei = ISNULL(CASE W 573.744666666667 235.138666666667 11059221.3333333 703 2665498 rcs_ias 00:09:05.1 00:06:14.2 23,594,223 962 -- PLT18_FactFurncuSoldSELECT f.nrintfact, seriefact = f.seriefact, numarfact = f.nrfact, datafact = f.datafact, valfact = MIN(ISNULL(total,0) + ISNULL(tva,0)) + SUM(ISNULL(rd.diferenta,0)), codfirma = f.codfurnizor, denfirma = fp.denumire, s.societateINTO #tmp_facturif -- toate facturile posibile care ma intereseazaFROM facturif f JOIN firme_prim fp ON f.codfurnizor = fp.codfirma JOIN societati s ON f.codsoc = s.codsoc LEFT JOIN obsdoc o ON f.obsdoc = o.obsdoc LEFT JOIN modplata m ON f.modplata = m.codmodplata LEFT JOIN trz_reevaluariM rm ON rm.idoptip = 47 AND rm.datareevaluare <= @datasold LEFT JOIN trz_reevaluariD rd ON rd.idreevaluareM = rm.idreevaluareM AND f.nrintfact = ISNULL(rd.iddoc,0) AND rd.idoptip = 47WHERE f.codfurnizor like @codfirma AND f.datafact between @datafact1 a 545.05125 374.21625 23594223.25 962.25 446768 rcs 00:07:16.9 00:04:03.9 15,092,978 931 <TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:str="-- MFX18_RaportInventariereSELECT m.nrinventar, denumireMFX= m.denumire, centrup = ISNULL(i.pctlucru_primire, m.pctlucru), denumirecentrup= p.denumire, centruptata=convert(varchar(250), ''), denumirecentruptata = convert(varchar(250), ''), m.datareceptie, m.datapif,m.dataiesire, m.dataamteoretica, am =CASE WHEN m.dataamteoretica is not null AND m.am = 1 AND m.dataamteoretica<=@data then 1 ELSE 0 END, casat = CASE WHEN m.dataiesire is not null AND m.casat = 1 AND m.dataiesire<= @data then 1 ELSE 0 END, lipsainv = CASE WHEN m.dataiesire is not null AND m.casat = 2 AND m.dataiesire<=@data then 1 ELSE 0 END, vandut = CASE WHEN m.dataiesire is not null AND m.casat = 0 AND m.dataiesire-- MFX18_RaportInventariereSELECT m.nrinventar, denumireMFX= m.denumire, centrup = ISNULL(i.pctlucru_primire, m.pctlucru), denumirecentrup= p.denumire, centruptata=convert(varchar(250), ''), denumirecentruptata = convert(varchar(250), ''), m.datareceptie, m.datapif,m.dataiesire, m.dataamteoretica, am =CASE WHEN m.dataamteoretica is not null AND m.am = 1 AND m.dataamteoretica<=@data then 1 ELSE 0 END, casat = CASE WHEN m.dataiesire is not null AND m.casat = 1 AND m.dataiesire<= @data then 1 ELSE 0 END, lipsainv = CASE WHEN m.dataiesire is not null AND m.casat = 2 AND m.dataiesire<=@data then 1 ELSE 0 END, vandut = CASE WHEN m.dataiesire is not null AND m.casat = 0 AND m.dataiesire<=@data then 1 ELSE 0 END, valamlei = ISNULL(CASE WHEN m.valintrarelei
436.870142857143 243.881 15092978.1428571 931.285714285714 369901 rcs_ias 00:06:50.5 00:00:31.4 6,362,652 0 -- CNT_NoteContTiparireINSERT INTO #doccont SELECT DISTINCT dc.nrcontare, dc.datacontab, dc.codcent, ct.denumirecent, dc.pozitie, dc.coddoc, dm.document, dc.nrdoc, dc.datadoc, dc.sumadoc,dc.explicatiidoc, dc.coduserdoc, dc.validat,userdoc= usa.denumireuser, dc.datavalidare, dc.coduser, userin =usb.denumireuser, dc.valuta, dc.cursvaluta,dc.totalvaluta, dc.valuta_doc,dc.cursvaluta_doc, s.societate, dimensiune1= d1.denumire,dimensiune2 = d2.denumire, dc.codfirma, dc.seriedoc, dc.datainreg, dc.datamodif, dc.codusermodif,usermod = usm.denumireuser, d1.cod, d2.cod --INTO #tmp FROM doccontate dc JOIN documente dm ON dc.coddoc = dm.coddoc AND dm.document LIKE @document JOIN centralizatoare ct ON dc.codcent = ct.codcent AND ct.denu 410.464 31.392 6362652 0 3213325 rcs 00:05:58.3 00:03:31.1 16,124,269 288 <TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:str="-- MFX18_RaportInventariereinsert into RCS_tab_temp_final_MFX--amihai 7 aug 2007 ca sa nu mai foloseasca tempdbSELECT m.nrinventar, denumireMFX= m.denumire, centrup = ISNULL(i.pctlucru_primire, m.pctlucru), denumirecentrup= p.denumire, centruptata=convert(varchar(250), ''), denumirecentruptata = convert(varchar(250), ''), m.datareceptie, m.datapif,m.dataiesire, m.dataamteoretica, am =CASE WHEN m.dataamteoretica is not null AND m.am = 1 AND m.dataamteoretica<=@data then 1 ELSE 0 END, casat = CASE WHEN m.dataiesire is not null AND m.casat = 1 AND m.dataiesire<= @data then 1 ELSE 0 END, lipsainv = CASE WHEN m.dataiesire is not null AND m.casat = 2 AND m.dataiesire<=@data then 1 ELSE 0 END, vandut = CASE WHEN m.dataiesire is not null AND m.casat = 0 AND m.dataiesire-- MFX18_RaportInventariereinsert into RCS_tab_temp_final_MFX--amihai 7 aug 2007 ca sa nu mai foloseasca tempdbSELECT m.nrinventar, denumireMFX= m.denumire, centrup = ISNULL(i.pctlucru_primire, m.pctlucru), denumirecentrup= p.denumire, centruptata=convert(varchar(250), ''), denumirecentruptata = convert(varchar(250), ''), m.datareceptie, m.datapif,m.dataiesire, m.dataamteoretica, am =CASE WHEN m.dataamteoretica is not null AND m.am = 1 AND m.dataamteoretica<=@data then 1 ELSE 0 END, casat = CASE WHEN m.dataiesire is not null AND m.casat = 1 AND m.dataiesire<= @data then 1 ELSE 0 END, lipsainv = CASE WHEN m.dataiesire is not null AND m.casat = 2 AND m.dataiesire<=@data then 1 ELSE 0 END, vandut = CASE WHEN m.dataiesire is not null AND m.casat = 0 AND m.dataiesire<=@data then 1
358.261142857143 211.055571428571 16124268.5714286 288.142857142857 841977 rcs_ias 00:05:48.0 00:00:00.0 10 1 -- VNZ_ModificareAvizUPDATE stociesiri set codclient=@codclientnou WHERE nrintdoc = @nrintaviz AND tipdoc ='AC' 347.991 0 10 1 3192158 rcs 00:05:40.8 00:00:00.0 43 0 -- STC_StergeSeriiSELECT @bitavizf = a.bitavizf FROM stociesiri st JOIN artavizec ac ON st.nrpartidaaviz = ac.nrpartidaaviz JOIN avizec a ON ac.nrintaviz = a.nrintaviz WHERE st.nrpartidaaviz = @nrpartida AND st.codart = @codart AND st.tipdoc = @tipdoc 340.775 0 43 0 370633 rcs 00:05:26.5 00:00:00.0 3 0 -- CMP_ModificaNirUPDATE stociesiri SET valuta = @valuta WHERE nrintdoc = @nrintnir AND tipdoc = 'nr' 326.466 0 3 0 1395330 rcs 00:05:11.6 00:00:00.0 248 7 -- CMP_FacturiFInsertINSERT INTO facturif(coddoc, seriefact, nrfact, codfurnizor, datafact, scadenta, obsdoc, delegat, transport, total, tva, pfacturat, platit, transferat, coduser, modplata, valuta, obstext, codsoc, totalvaluta, curs, platavanzare, idproiect,idlocatie, modvaluta, tvavaluta)VALUES( @coddoc, @seriefact, @nrfact, @codfurnizor, @datafact, @scadenta, @obsdoc, @delegat, @transport, @total,@tva, @pfacturat, @platit, @transferat, @coduser, @modplata, @valuta, @obstext, @codsoc, @totalvaluta, @curs, @platavanzare, @proiect, @locatie, @modvaluta, @totaltvavaluta) 311.64175 0.0195 248 7 2336871 rcs 00:05:03.1 00:00:00.0 17 0 SELECT nrbon FROM bon WHERE tipbon = 'BT' AND nrbon =563579377 AND codgestsursa = 'D4632' 303.085 0 17 0 207802 rcs 00:04:32.0 00:01:26.4 12,182,576 20 -- CMP_StergeAvizeFDELETE partizispeciali FROM partizispeciali ps JOIN #loturi_aviz a ON a.nrpartidanou = ps.nrpartidanou 271.999 86.359 12182575.5 19.5 2043406 rcs 00:04:29.2 00:00:08.1 23,398 0 -- sp_MSget_repl_commandsselect rc.xact_seqno, rc.partial_command, rc.type, rc.command_id, rc.command from MSrepl_commands rc where rc.publisher_database_id = @publisher_database_id and rc.xact_seqno > @last_xact_seqno and rc.xact_seqno <= @max_xact_seqno and (rc.type & @snapshot_bit) <> @snapshot_bit and (rc.type & ~@snapshot_bit) not in ( 37, 38 ) order by rc.xact_seqno, rc.command_id asc 269.190428571429 8.09692857142857 23398.4285714286 0 2009819 distribution 00:04:19.9 00:01:23.0 11,970,915 47,584 delete cfrom calendar_documente cjoin societati s on c.codsoc = s.codsocWHERE s.soc_tata = 10 259.916 82.997 11970915 47584 2072205 definiri Third point. I asked also some experts and told me to separate the indexes from data.
In Theory, theory and practice are the same...In practice, they are not. -
Since you say the stored procedures use temp tables a lot, I'd also look at putting the tempdb data and tempdb log on seperate physical drives (that's two drives).
I'd also suggest that you run fn_virtualfilestats to determine where the majority of your I/O is occurring and if the read/write ratio.
Blocking quite often comes down to application and database design, very rarely does re-organising physical hardware help with blocking. Quite often you may find that the queries being run don't have effective indexes which causes them to read, and therefore place a lock on, more data than they should be reading. Simply placing an appropriate index can significantly reduce the amount of reading and as a consequence present fewer blocking problems.
--------------------
Colt 45 - the original point and click interface
-
Hi,
These were my thoughts, I do like separating SQL data from OS totally and I don't believe in separating indexes from table data. Many people think the opposite.
As I saw your queries, Phil definitely has a point. You should check the indexes on the tables. Anyway, I suggest you to check disk queue lengths with perfmon if you didn't do this yet.
These are just hints, your plan seems feasible in itself.
-- Erik http://blog.rollback.hu
-
Thank you for your replies. I will analyze these issues you show me.
In Theory, theory and practice are the same...In practice, they are not. -
Tend to agree with Erik's advice myself.
I will say that some of the research from MS on very large DBs has been splitting off entire objects (data + indexes) into their own filegroups. For a few reasons. Backup is one, keep stuff together. Performance is another.
-
I would recommmend 4 drives for datafiles (one for each CPU core), 1 drive for your log files, and 1 drive for tempdb database. The link below is also handy.
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx
-
how about RAID10 array? is justified to use 6 HDD?
In Theory, theory and practice are the same...In practice, they are not. -
I have the following IO on TempDB:
15 aug 2007:
DBName FileName DbId FileId TimeStamp NumberReads NumberWrites BytesRead BytesWritten IoStallMS tempdb tempdev 2 1 -850553795 4384580 6434299 2.76778E+11 3.33163E+11 307887 tempdb templog 2 2 -850553795 6866 418893 422344704 24401370624 2288 16 aug 2007:
tempdb tempdev 2 1 -700909717 4756386 6940393 3.00294E+11 3.59771E+11 318643 tempdb templog 2 2 -700909717 6903 443446 424257536 25851481088 2318 In Theory, theory and practice are the same...In practice, they are not. -
So how does this IO compare against your databases?
--------------------
Colt 45 - the original point and click interface -
So, I have the following difference for 1 day:
Unit FileName BytesRead BytesWritten
GBytes tempdev 21.9 24.7
MBytes templog 1.82 1382.93
I try to understand how this affect the BlkBy process during 1 day, 'cause I have noticed many locking spids locking other spids. I think this is because of heavy using of TempDB - stored procedures have a lot of creating of temporary tables (#tTbls) and populating them.
In Theory, theory and practice are the same...In practice, they are not. -
Check the queries that are at the head of the blocking chain. As I mentioned in my other post, without effective indexes the queries will read, and therefore place a lock on, more data than they should be reading. Make sure you've got adequate indexing and you're only returning exactly whats required.
--------------------
Colt 45 - the original point and click interface -
I didn't make the design and arhictecture of this ERP system. Anyway, the tables are plenty of indexes. I've tested the stored procedures, some of them because are a lot of, and noticed a good execution plan. I have problems with concurrent acceses of those stored procedures. There are many users who acceses in a short interval of time the same stored procedures.
In Theory, theory and practice are the same...In practice, they are not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply