sql perfomance on new server
- by Rapunzo
My database is running on a pc (AMD Phenom x6, intel ssd disk, 8GB DDR3 RAM and windows 7 OS + sql server 2008 R2 sp3 ) and it started working hard, timeout problems and up to 30 seconds long queries after 200 mb of database
And I also have an old server pc (IBM x-series 266: 72*3 15k rpm scsi discs with raid5, 4 gb ram and windows server 2003 + sql server 2008 R2 sp3 ) and same query start to give results in 100 seconds..
I tried query analyser tool for tuning my indexed. but not so much improvements.
its a big dissapointment for me. because I thought even its an old server pc it should be more powerfull with 15k rpm discs with raid5. what should I do. do I need $10.000 new server to get a good performance for my sql server? cant I use that IBM server?
Extra information: there is 50 sql users and its an ERP program.
There is my query
ALTER FUNCTION [dbo].[fnDispoTerbiye]
(
)
RETURNS TABLE
AS
RETURN
(
SELECT MD.dispoNo,
SV.sevkNo,
M1.musteriAdi AS musteri,
SD.tipTurId,
TT.tipTur,
SD.tipNo,
SD.desenNo,
SD.varyantNo,
SUM(T.topMetre) AS toplamSevkMetre,
MD.dispoMetresi,
DT.gelisMetresi,
ISNULL(DT.fire, 0) AS fire,
SV.sevkTarihi,
DT.gelisTarihi,
SP.mamulTermin,
SD.miktar AS siparisMiktari,
M.musteriAdi AS boyahane,
MD.akisNotu AS islemler, --dbo.fnAkisIslemleri(MD.dispoNo)
DT.partiNo,
DT.iplikBoyaId,
B.tanimAd AS BoyaTuru,
MAX(HD.hamEn) AS hamEn,
MAX(HD.hamGramaj) AS hamGramaj,
TS.mamulEn,
TS.mamulGramaj,
DT.atkiCekmesi,
DT.cozguCekmesi,
DT.fiyat,
DV.dovizCins,
DT.dovizId,
(SELECT CASE WHEN DT.dovizId = 2 THEN CAST(round(SUM(T .topMetre) * DT.fiyat *
(SELECT TOP 1 satis
FROM tblKur
WHERE dovizId = 2
ORDER BY tarih DESC), 2) AS numeric(18, 2))
WHEN DT.dovizId = 3 THEN CAST(round(SUM(T .topMetre) * DT.fiyat *
(SELECT TOP 1 satis
FROM tblKur
WHERE dovizId = 3
ORDER BY tarih DESC), 2) AS numeric(18, 2))
WHEN DT.dovizId = 1 THEN CAST(round(SUM(T .topMetre) * DT.fiyat *
(SELECT TOP 1 satis
FROM tblKur
WHERE dovizId = 1
ORDER BY tarih DESC), 2) AS numeric(18, 2)) END AS Expr1)
AS ToplamTLfiyat,
DT.aciklama,
MD.dispoNotu,
SD.siparisId,
SD.siparisDetayId,
DT.sqlUserName,
DT.kayitTarihi,
O.orguAd,
'Çözgü=(' + (SELECT dbo.fnTipIplikler(SD.tipTurId, SD.tipNo, SD.desenNo, SD.varyantNo, 1) AS Expr1) + ')' +
' Atki=(' + (SELECT dbo.fnTipIplikler(SD.tipTurId, SD.tipNo, SD.desenNo, SD.varyantNo, 2) AS Expr1) + ')' AS iplikAciklama,
DT.prosesOk,
dbo.[fnYikamaTalimat](SP.siparisId) yikamaTalimati
FROM tblDoviz AS DV WITH(NOLOCK)
INNER JOIN tblDispoTerbiye AS DT WITH(NOLOCK)
INNER JOIN tblTanimlar AS B WITH(NOLOCK) ON DT.iplikBoyaId = B.tanimId AND B.tanimTurId = 2 ON DV.id = DT.dovizId
RIGHT OUTER JOIN tblMusteri AS M1 WITH(NOLOCK)
INNER JOIN tblSiparisDetay AS SD WITH(NOLOCK)
INNER JOIN tblDispo AS MD WITH(NOLOCK) ON SD.siparisDetayId = MD.siparisDetayId
INNER JOIN tblTipTur AS TT WITH(NOLOCK) ON SD.tipTurId = TT.tipTurId
INNER JOIN tblSiparis AS SP WITH(NOLOCK) ON SD.siparisId = SP.siparisId ON M1.musteriNo = SP.musteriNo
INNER JOIN tblTip AS TP WITH(NOLOCK) ON SD.tipTurId = TP.tipTurId AND SD.tipNo = TP.tipNo AND SD.desenNo = TP.desen AND SD.varyantNo = TP.varyant
INNER JOIN tblOrgu AS O WITH(NOLOCK) ON TP.orguId = O.orguId
INNER JOIN tblMusteri AS M WITH(NOLOCK)
INNER JOIN tblSevkiyat AS SV WITH(NOLOCK) ON M.musteriNo = SV.musteriNo
INNER JOIN tblSevkDetay AS SVD WITH(NOLOCK) ON SV.sevkNo = SVD.sevkNo ON MD.mamulDispoHamSevkno = SV.sevkNo
LEFT OUTER JOIN tblTop AS T WITH(NOLOCK)
INNER JOIN tblDispo AS HD WITH(NOLOCK) ON T.dispoNo = HD.dispoNo AND T.dispoTuruId = HD.dispoTuruId ON SVD.dispoTuruId = T.dispoTuruId AND SVD.dispoNo = T.dispoNo
AND SVD.topNo = T.topNo AND MD.siparisDetayId = HD.siparisDetayId ON DT.dispoTuruId = MD.dispoTuruId AND DT.dispoNo = MD.dispoNo
LEFT OUTER JOIN tblDispoTerbiyeTest AS TS WITH(NOLOCK) ON DT.dispoTuruId = TS.dispoTuruId AND DT.dispoNo = TS.dispoNo
--WHERE DT.gelisTarihi IS NULL
-- OR DT.gelisTarihi > GETDATE()-30
GROUP BY MD.dispoNo, DT.partiNo, DT.iplikBoyaId, TS.mamulEn, TS.mamulGramaj, DT.gelisMetresi, DT.gelisTarihi, DT.atkiCekmesi, DT.cozguCekmesi, DT.fire, DT.fiyat,
DT.aciklama, DT.sqlUserName, DT.kayitTarihi, SD.tipTurId, TT.tipTur, SD.tipNo, SD.desenNo, SD.varyantNo, SD.siparisId, SD.siparisDetayId, B.tanimAd, M.musteriAdi,
M.musteriAdi, M1.musteriAdi, O.orguAd, TP.iplikAciklama, SD.miktar, MD.dispoNotu, SP.mamulTermin, DT.dovizId, DV.dovizCins, MD.dispoMetresi,
MD.akisNotu, SV.sevkNo, SV.sevkTarihi, DT.prosesOk,SP.siparisId
)
