SQL 2000 Performance Tuning | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL 2000 Performance Tuning

Hi All,<br /><br />I’m working right now to optimize a SQL server 2000 database environment.<br /><br />The current situation is:<br /><br />Hardware:<br />CPU: 2×2 hyper threading Xeon 2.4 GHz<br />RAM memory: 1536 Mb<br />Hard disk: RAID5<br />Smart array 5300 controller<br />With two arrays:<br /><br />Array A: 2 hardisks 18.2 GB each<br />Array B:10 hardisk 72.8 GB each one disk for spare. The 10 disks are configured as one array.<br /><br /><br />Operating System:<br /><br />Windows 2000 server<br /><br />Database Server:<br /><br />Windows SQL Server 2000 standard edition<br /><br />The Array A is reserved for Windows 2000 Server operating system en the SQL server database system files.<br />The Array B is for the data (All databases are stored in this Array).<br />5 databases are implemented on the SQL Server.<br />Both the data files and the transaction log files of all the 5 databases are saved in one partition F<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />array B).<br /><br />On the server there are 3 applications written in VB5.0 (RDO, ODBC) active and continuously writing to the database. These applications are continually writing to the database. In this case there is a lot of write activities to the databases.<br />Other applications (client/server) also written in VB5.0 (RDO, ODBC connections) are used to generate reports on the client machines. The reports are defined to write to a word document (bookmarks in Word).This report are activated once a month. <br />The time taken to generate such a report is too long.<br /><br />What#%92s your advice about RAID5 and the fact that all data files and Transaction files are stored in the same Array?<br /><br /><br /><br />Thank u for your help.<br />
If you will have more write than read, I suggest to considerate RAID 1 or 10.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
quote:Originally posted by LuisMartin If you will have more write than read, I suggest to considerate RAID 1 or 10.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
Hi Luis, Thank u for your replay.
What are the differences between the two types, RAID5 and RAID10?
I know that RAID10 is more exspensive than RAID5.
I’m considering of moving Transaction files of the 5 databases to the first array (RAID1: 2 disks, one disk is for OS files the other for mirroring). Is this action goed for performance ?
It’s hard to give good advice if we don’t know what your counters look like. Are you having issues with this server? What do you read and write queue length max and avg values look like for each of these disks (performance monitor)? What does it look like during times where your having issues? What does your SQL Server Cache Hit Ratio and SQL Server Buffer Cache Hit Ratio look like? How about the % Processor? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by derrickleggett</i><br /><br />It’s hard to give good advice if we don’t know what your counters look like. Are you having issues with this server? What do you read and write queue length max and avg values look like for each of these disks (performance monitor)? What does it look like during times where your having issues? What does your SQL Server Cache Hit Ratio and SQL Server Buffer Cache Hit Ratio look like? How about the % Processor?<br /><br />MeanOldDBA<br />[email protected]<br /><br />When life gives you a lemon, fire the DBA.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Hi derrick,<br /><br />The problem lays with some queries that takes a lot of time to finish.<br />I have posted this question also in SQLTEAM.com forum but unfortunately I haven’t yet got any answers.<br /><br />I Will collect this values for each disk today.<br /><br />On The server I have 1536 MB RAM memory<br /><br />Object Memory counter:<br /><br />Committed Bytes<br />Avg:1886275133 Byte<br />Min:1882927104 Byte<br />MAx:2306556768 Byte<br /><br /><b>Can I conclude from the above values (max committed Bytes) that extra RAM memory is needed on the server?</b><br /><br />Available Byte counter<br />Avg:4986921 Byte<br />Min:4603904 Byte<br />Max:5742592 Byte<br /><br />I don’t know if you can take a conclusion from the values measured for the totals. <br /><br />Anyway these are the Values I have collected:<br /><br />For Disk(0:c)<br /><br />item:Current Disk Queue Length<br />Avg:1<br />Max:4<br /><br />item:avg Disk Write Queue Length<br />Avg:0,023<br />Max:0,199<br /><br />item:Avg. Disk Read Queue Length<br />avg:0,840<br />Max:2,359<br /><br /><br />For Disk(1:F)<br /><br />item:Current Disk Queue Length<br />Avg:7<br />Max:70<br /><br />item:avg Disk Write Queue Length<br />Avg:0,011<br />Max:0,201<br /><br />item:Avg. Disk Read Queue Length<br />avg:0,840<br />Max:10,802<br /><br />% Processor time<br />Avg:20,660<br />Max:53,125<br /><br />SQL Server Cach Hit Ratio<br />Avg:50,195<br />MAx:50,195<br /><br />SQL Buffer Cach Hit Ratio<br />Avg:99,884<br />MAx:99,885<br /><br /><br /><br /><br />PhysicalDisk(_Total)Avg. Disk Read Queue Length<br />Minimum: VALUE=0,7576<br />Maximum: VALUE=1,9163<br />Average: VALUE=0,957986868686869<br /><br /><br /><br />PhysicalDisk(_Total)Avg. Disk sec/Read<br />Minimum" VALUE=1,30796178343949E-03<br />Maximum" VALUE=3,79465346534653E-03<br />Average" VALUE=1,85630933041044E-03<br /><br /><br />PhysicalDisk(_Total)Avg. Disk sec/Write"&gt;<br />Minimum" VALUE="1,29268292682927E-04<br />Maximum" VALUE="5,46376811594203E-04<br />Average" VALUE="1,62386390118853E-04<br /><br /><br />PhysicalDisk(_Total)Current Disk Queue Length"&gt;<br />Minimum" VALUE="0"<br />Maximum" VALUE="10"<br />Average" VALUE="1,05"<br /><br />PhysicalDisk(_Total)Disk Read Bytes/sec"&gt;<br />Minimum" VALUE="2488712,68116288"<br />Maximum" VALUE="5286103,21551249"<br />Average" VALUE="3610427,8215927"<br /><br />PhysicalDisk(_Total)Disk Reads/sec"&gt;<br /><br />Minimum" VALUE="413,40917865696"<br />Maximum" VALUE="683,783150194319"<br />Average" VALUE="516,102545921543"<br /><br /><br />PhysicalDisk(_Total)Disk Write Bytes/sec"&gt;<br /><br />Minimum" VALUE="20441,8143060289"<br />Maximum" VALUE="1034605,53077646"<br />Average" VALUE="156298,935157415"<br /><br /><br />SQLServer:Buffer ManagerBuffer cache hit ratio"&gt;<br /><br />Minimum" VALUE="99,33799190879<br />Maximum" VALUE="99,8655913978495<br />Average" VALUE="99,6501164789699<br /><br /><br /><br /><br /><br />An example of queries that take long time(30 min) to finish is as follows:<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT str (100-(cast (count (a.acc_mpdu_id) as real)/<br />(SELECT (case count (a.acc_mpdu_id) when 0 then 1 else count(a.acc_mpdu_id) end)<br />FROM X400tblAccounting7 a, X400tblAccounting7 b<br />WHERE a.acc_local_mta in (‘OBSWITCH1’, ‘OBSWITCH3’)<br />AND a.acc_remote_mta in (‘APGTOU23-MTA’, ‘APGTOU65-MTA’, ‘APMOSU6IN-MTA’, ‘APMOSU6OUT-MTA’, ‘APMOSU7IN-MTA’, <br /><br />’APMOSU7OUT-MTA’, ‘APMOSU8IN-MTA’, ‘APMOSU8OUT-MTA’, ‘APMOSU9IN-MTA’, ‘APMOSU9OUT-MTA’)<br />AND a.acc_mpdu_type = 0<br />AND a.acc_record_type = ‘I’ <br />AND b.acc_local_mta = ‘OBEBD’ <br />AND b.acc_record_type = ‘D'<br />AND a.acc_mpdu_priority = 0<br />AND a.acc_mpdu_id = b.acc_mpdu_id<br />AND a.acc_report = 1<br />AND b.acc_report = 1<br />)* 100) , 6,2) + ‘%'<br /><br />FROM X400tblAccounting7 a, X400tblAccounting7 b<br />WHERE a.acc_local_mta in (‘OBSWITCH1’, ‘OBSWITCH3’) <br />AND a.acc_remote_mta in (‘APGTOU23-MTA’, ‘APGTOU65-MTA’, ‘APMOSU6IN-MTA’, ‘APMOSU6OUT-MTA’, ‘APMOSU7IN-MTA’, <br /><br />’APMOSU7OUT-MTA’, ‘APMOSU8IN-MTA’, ‘APMOSU8OUT-MTA’, ‘APMOSU9IN-MTA’, ‘APMOSU9OUT-MTA’)<br />AND a.acc_mpdu_type = 0<br />AND a.acc_record_type = ‘I’ <br />AND b.acc_local_mta = ‘OBEBD’ <br />AND b.acc_record_type = ‘D'<br />AND a.acc_mpdu_priority = 0<br />AND a.acc_mpdu_id = b.acc_mpdu_id<br />AND a.acc_report = 1<br />AND b.acc_report = 1<br />AND DATEDIFF(second, a.acc_datetime, b.acc_datetime)&gt; 30<br /></font id="code"></pre id="code"><br /><br />The Database Table X400tblAccounting7 has a total row of <b>22937038</b>. In The query <br />We use Count(*) Or Count(columnname).<br />Is this the problem (count)why this query takes a long time to finish?<br />If I use the following query to count the total row number of the X400tblAccounting7 Table.<br /><br /><br />SELECT rows FROM sysindexes WHERE id = OBJECT_ID(‘tbTest’) AND indid &lt; 2<br />GO <br /><br />the query takes an acceptable time to finish.<br />But I’m wondering if it is risk free to use this query because of the sysindexes table.<br /><br />On the table there is a clusterd, unique, primar index (Index keys:acf_id, acc_line_number, acc_datetime, acc_recipient_number)<br /><br />and all column used in the Where close are nonclusterd indexes.<br /><br /><br /><br /><br /><br /><br />The explain plan using SET STATISTICS PROFILE ON<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />the explain pl an using SET STATISTICS PROFILE ON<br /><br />I don’t know if it is "read freindely". This statistics took approximately 30 min.<br /><br /><br /><br />Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions <br />———– ———– —————————————————————————————————————————————————————————————————————————————————————- ———– ———– ———– —————————— —————————— —————————————————————————————————————————————————————————————————————————————————————- ———————————————————————————————————————————————————————— ———————— ———————— ———————— ———– ———————— ———————————————————————————————————————————————————————— ——– —————————— ——– ———————— <br />1 1 SELECT str (100-(cast (count (a.acc_mpdu_id) as real)/<br />(SELECT (case count (a.acc_mpdu_id) when 0 then 1 else count(a.acc_mpdu_id) end)<br />FROM X400tblAccounting7 a, X400tblAccounting7 b<br />WHERE a.acc_local_mta in (‘OBSWITCH1’, ‘OBSWITCH3′)<br />AND a.acc_re 3 1 0 NULL NULL NULL NULL 1.0 NULL NULL NULL 47726.383 NULL NULL SELECT 0 NULL<br />1 1 |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Expr1007]=str(Convert(100-Convert([Expr1002])/Convert(If ([Expr1005]=0) then 1 else [Expr1005])*100), 6, 2)+’%’)) 3 2 1 Compute Scalar Compute Scalar DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Expr1007]=str(Convert(100-Convert([Expr1002])/Convert(If ([Expr1005]=0) then 1 else [Expr1005])*100), 6, 2)+’%’) [Expr1007]=str(Convert(100-Convert([Expr1002])/Convert(If ([Expr1005]=0) then 1 else [Expr1005])*100), 6, 2)+’%’ 1.0 0.0 0.0000001 14 47726.383 [Expr1007] NULL PLAN_ROW 0 1.0<br />1 1 |–Nested Loops(Inner Join) 3 3 2 Nested Loops Inner Join NULL NULL 1.0 0.0 4.1799999E-6 15 47726.383 [Expr1002], [Expr1005] NULL PLAN_ROW 0 1.0<br />1 1 |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Expr1002]=Convert([globalagg1017]))) 3 4 3 Compute Scalar Compute Scalar DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Expr1002]=Convert([globalagg1017])) [Expr1002]=Convert([globalagg1017]) 1.0 0.0 0.0000001 11 14334.903 [Expr1002] NULL PLAN_ROW 0 1.0<br />1 1 | |–Stream Aggregate(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[globalagg1017]=SUM([partialagg1016]))) 3 5 4 Stream Aggregate Aggregate NULL [globalagg1017]=SUM([partialagg1016]) 1.0 0.0 0.0000004 15 14334.903 [globalagg1017] NULL PLAN_ROW 0 1.0<br />4 1 | |–Parallelism(Gather Streams) 3 6 5 Parallelism Gather Streams NULL NULL 4.0 0.0 2.8508598E-2 15 14334.903 [partialagg1016] NULL PLAN_ROW -1 1.0<br />4 4 | |–Stream Aggregate(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[partialagg1016]=Count(*))) 3 7 6 Stream Aggregate Aggregate NULL [partialagg1016]=Count(*) 4.0 0.0 321.77271 15 14334.875 [partialagg1016] NULL PLAN_ROW -1 1.0<br />4222 4 | |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[a].[acc_mpdu_id])=(<b>.[acc_mpdu_id]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ /><b>.[acc_mpdu_id]=[a].[acc_mpdu_id] AND datediff(second, [a].[acc_datetime], <b>.[acc_datetime])&gt;30)) 3 8 7 Hash Match Inner Join HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[a].[acc_mpdu_id])=(<b>.[acc_mpdu_id]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ /><b>.[acc_mpdu_id]=[a].[acc_mpdu_id] AND datediff(second, [a].[acc_datetime], <b>.[acc_datetime])&gt;30) NULL 6.4354545E+9 0.0 13226.939 104 14013.103 NULL NULL PLAN_ROW -1 1.0<br />1977289 4 | |–Parallelism(Repartition Streams, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[a].[acc_mpdu_id])) 3 10 8 Parallelism Repartition Streams PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[a].[acc_mpdu_id]) NULL 516221.19 0.0 4.127038 80 403.81772 [a].[acc_datetime], [a].[acc_mpdu_id] NULL PLAN_ROW -1 1.0<br />1977289 4 | | |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Expr1014])=([a].[acc_remote_mta]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[a].[acc_remote_mta]=[Expr1014])) 3 11 10 Hash Match Inner Join HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Expr1014])=([a].[acc_remote_mta]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[a].[acc_remote_mta]=[Expr1014]) NULL 516221.19 0.0 11.383599 80 399.69067 [a].[a cc_datetime], [a].[acc_mpdu_id] NULL PLAN_ROW -1 1.0<br />40 4 | | |–Parallelism(Broadcast) 3 12 11 Parallelism Broadcast NULL NULL 10.0 0.0 2.8523564E-2 18 3.9946824E-2 [Expr1014] NULL PLAN_ROW -1 1.0<br />10 1 | | | |–Sort(DISTINCT ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Expr1014] ASC)) 3 13 12 Sort Distinct Sort DISTINCT ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Expr1014] ASC) NULL 10.0 1.1261261E-2 1.5184008E-4 18 1.1423258E-2 [Expr1014] NULL PLAN_ROW 0 1.0<br />10 1 | | | |–Constant Scan 3 14 13 Constant Scan Constant Scan NULL NULL 10.0 0.0 1.0157E-5 18 1.0157E-5 [Expr1014] NULL PLAN_ROW 0 1.0<br />4249976 4 | | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[ISO_DATA].[dbo].[X400tblaccounting7].[pri_7] AS [a]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />((([a].[acc_mpdu_type]=0 AND [a].[acc_mpdu_priority]=0) AND [a].[acc_record_type]=’I’) AND ([a].[acc_local_mta]=’OB 3 25 11 Clustered Index Scan Clustered Index Scan OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[ISO_DATA].[dbo].[X400tblaccounting7].[pri_7] AS [a]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />((([a].[acc_mpdu_type]=0 AND [a].[acc_mpdu_priority]=0) AND [a].[acc_record_type]=’I’) AND ([a].[acc_local_mta]=’OBSWITCH3′ OR [a].[acc_local_mta]=’OBSWITCH1′)) AND Convert([a].[acc_repo [a].[acc_datetime], [a].[acc_report], [a].[acc_mpdu_priority], [a].[acc_record_type], [a].[acc_mpdu_type], [a].[acc_remote_mta], [a].[acc_local_mta], [a].[acc_mpdu_id] 3305185.0 349.50351 12.61541 163 362.11893 [a].[acc_datetime], [a].[acc_report], [a].[acc_mpdu_priority], [a].[acc_record_type], [a].[acc_mpdu_type], [a].[acc_remote_mta], [a].[acc_local_mta], [a].[acc_mpdu_id] NULL PLAN_ROW -1 1.0<br />1283618 4 | |–Parallelism(Repartition Streams, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ /><b>.[acc_mpdu_id])) 3 28 8 Parallelism Repartition Streams PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ /><b>.[acc_mpdu_id]) NULL 685798.63 0.0 5.5207186 180 382.31934 <b>.[acc_datetime], <b>.[acc_mpdu_id] NULL PLAN_ROW -1 1.0<br />1283618 4 | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[ISO_DATA].[dbo].[X400tblaccounting7].[pri_7] AS <b>), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />(<b>.[acc_local_mta]=’OBEBD’ AND <b>.[acc_record_type]=’D’) AND Convert(<b>.[acc_report])=1)) 3 29 28 Clustered Index Scan Clustered Index Scan OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[ISO_DATA].[dbo].[X400tblaccounting7].[pri_7] AS <b>), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />(<b>.[acc_local_mta]=’OBEBD’ AND <b>.[acc_record_type]=’D’) AND Convert(<b>.[acc_report])=1) <b>.[acc_datetime], <b>.[acc_report], <b>.[acc_mpdu_id], <b>.[acc_record_type], <b>.[acc_local_mta] 685798.63 349.50351 12.61541 180 362.11893 <b>.[acc_datetime], <b>.[acc_report], <b>.[acc_mpdu_id], <b>.[acc_record_type], <b>.[acc_local_mta] NULL PLAN_ROW -1 1.0<br />1 1 |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Expr1005]=Convert([globalagg1021]))) 3 40 3 Compute Scalar Compute Scalar DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Expr1005]=Convert([globalagg1021])) [Expr1005]=Convert([globalagg1021]) 1.0 0.0 0.0000001 11 33391.48 [Expr1005] NULL PLAN_ROW 0 1.0<br />1 1 |–Stream Aggregate(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[globalagg1021]=SUM([partialagg1020]))) 3 41 40 Stream Aggregate Aggregate NULL [globalagg1021]=SUM([partialagg1020]) 1.0 0.0 0.0000004 15 33391.48 [globalagg1021] NULL PLAN_ROW 0 1.0<br />4 1 |–Parallelism(Gather Streams) 3 42 41 Parallelism Gather Streams NULL NULL 4.0 0.0 2.8508598E-2 15 33391.48 [partialagg1020] NULL PLAN_ROW -1 1.0<br />4 4 |–Stream Aggregate(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[partialagg1020]=Count(*))) 3 43 42 Stream Aggregate Aggregate NULL [partialagg1020]=Count(*) 4.0 0.0 1072.5757 15 33391.449 [partialagg1020] NULL PLAN_ROW -1 1.0<br />1032892 4 |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[a].[acc_mpdu_id])=(<b>.[acc_mpdu_id]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ /><b>.[acc_mpdu_id]=[a].[acc_mpdu_id])) 3 44 43 Hash Match Inner Join HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[a].[acc_mpdu_id])=(<b>.[acc_mpdu_id]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ /><b>.[acc_mpdu_id]=[a].[acc_mpdu_id]) NULL 2.1451514E+10 0.0 31533.92 89 32318.875 NULL NULL PLAN_ROW -1 1.0<br />1977289 4 |–Parallelism(Repartition Streams, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[a].[acc_mpdu_id])) 3 46 44 Parallelism Repartition Streams PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[a].[acc_mpdu_id]) NULL 516221.19 0.0 3.6283686 80 403.31906 [a].[acc_mpdu_id] NULL PLAN_ROW -1 1.0<br />1977289 4 | |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Expr1018])=([a].[acc_remote_mta]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[a].[acc_remote_mta]=[Expr1018])) 3 47 46 Hash Match Inner Join HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Expr1018])=([a].[acc_remote_mta]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[a].[acc_remote_mta]=[Expr1018]) NULL 516221.19 0.0 11.383599 80 399.69067 [a].[acc_mpdu_id] NULL PLAN_ROW -1 1.0<br />40 4 | |–Parallelism(Broadcast) 3 48 47 Parallelism Broadcast NULL NULL 10.0 0.0 2.8523564E-2 18 3.9946824E-2 [Expr1018] NULL PLAN_ROW -1 1.0<br />10 1 | | |–Sort(DISTINCT ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Expr1018] ASC)) 3 49 48 Sort Distinct Sort DISTINCT ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Expr1018] ASC) NULL 10.0 1.1261261E-2 1.5184008E-4 18 1.1423258E-2 [Expr1018] NULL PLAN_ROW 0 1.0<br />10 1 | | |–Constant Scan 3 50 49 Constant Scan Constant Scan NULL NULL 10.0 0.0 1.0157E-5 18 1.0157E-5 [Expr1018] NULL PLAN_ROW 0 1.0<br />4249976 4 | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[ISO_DATA].[dbo].[X400tblaccounting7].[pri_7] AS [a]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />((([a].[acc_mpdu_type]=0 AND [a].[acc_mpdu_priority]=0) AND [a].[acc_record_type]=’I’) AND ([a].[acc_local_mta]=’OB 3 61 47 Clustered Index Scan Clustered Index Scan OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[ISO_DATA].[dbo].[X400tblaccounting7].[pri_7] AS [a]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />((([a].[acc_mpdu_type]=0 AND [a].[acc_mpdu_priority]=0) AND [a].[acc_record_type]=’I’) AND ([a].[acc_local_mta]=’OBSWITCH3′ OR [a].[acc_local_mta]=’OBSWITCH1′)) AND Convert([a].[acc_repo [a].[acc_report], [a].[acc_mpdu_priority], [a].[acc_record_type], [a].[acc_mpdu_type], [a].[acc_remote_mta], [a].[acc_local_mta], [a].[acc_mpdu_id] 3305185.0 349.50351 12.61541 180 362.11893 [a].[acc_report], [a].[acc_mpdu_priority], [a].[acc_record_type], [a].[acc_mpdu_type], [a].[acc_remote_mta], [a].[acc_local_mta], [a].[acc_mpdu_id] NULL PLAN_ROW -1 1.0<br />1283618 4 |–Parallelism(Repartition Streams, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ /><b>.[acc_mpdu_id])) 3 64 44 Parallelism Repartition Streams PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ /><b>.[acc_mpdu_id]) NULL 685798.63 0.0 4.8109169 180 381.60953 <b>.[acc_mpdu_id] NULL PLAN_ROW -1 1.0<br />1283618 4 <br /><br /></font id="code"></pre id="code"><br /><br />
Your cache hit ratio is really odd compared to your buffer cache hit ratio. I would add detail to that and post it. I’m guessing it’s your procedure cache hit ratio and adhoc cache hit ratio that are suffering the most. It does definitely look like you need more RAM. You can push it up to 4GB on this server. Since you have Standard Edition, you’re not really going to benefit from more than that. Out of the disk counters, this one: For Disk(1:F)
item:Avg. Disk Read Queue Length
avg:0,840
Max:10,802 is one of the worst. Still, it’s not that bad when you consider how many disks you have (10 on that array). You might benefit from going with RAID 10 on this system; however, your current issues can probably be mostly solved by some good performance tuning. Here are some things to consider: 1) Consider setting your max degree of parallelism to the number of physical processors.
—-It looks like you are having some parallelism issues with the query you posted. 2) Post the detail cache hit ratio counters I posted below so we can further analyze memory usage.
—-I would definitely consider upgrading the server to a full 4GB of RAM. It’s an inexpensive upgrade. 3) Tune individual queries.
—-Taking the example you posted:
——Make sure you have proper indexes on the columns used in the WHERE clause.
——Change the query to use the INNER JOIN syntax and make sure the columns involved specifically in the join are indexed.
——Try using an INNER LOOP JOIN when you change from the col1, col2 syntax and see how that speeds things up.
——If changing your max degree of parallelism doesn’t get rid of all the weird parallelism (including the stream aggregation counts), set the MAXDOP on the query itself and see if that helps. Question: Are you using stored procedures or inline code in your application? 4) Run Profiler. Find out what your top running queries are for reads, writes, and CPU. Run the Profiler on another server or PC and collect the information to a table. 5) Search for locking and blocking. Have you looked at that yet? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
on the matter of parallelism,
run the query one each with
OPTION (MAXDOP 1)
and
OPTION (MAXDOP 2)
noting time
I guess it would help if I actually told him HOW to use MAXDOP, wouldn’t it Joe? [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />MeanOldDBA<br />[email protected]<br /><br />When life gives you a lemon, fire the DBA.
quote:Originally posted by joechang on the matter of parallelism,
run the query one each with
OPTION (MAXDOP 1)
and
OPTION (MAXDOP 2)
noting time

Thank u Joe for this tip. The syntax:
Set STATISTICS TIME ON <QUERY above> OPTION (MAXDOP 1) Set STATISTICS TIME OFF The query took approximately 30 min using 4 processors(2×2 hyper threading, meaning 2 physical CPU) The Results for the 2 options: The query above with OPTION (MAXDOP 1)
SQL Server Execution Times:
CPU time = 151016 ms, elapsed time = 1137296 ms. 1137296Ms = 19 min
151016Ms =2,5 min The same Query with OPTION (MAXDOP 2)
SQL Server Execution Times:
CPU time = 150718 ms, elapsed time = 1348005 ms. 1348005ms = 22,5 min
150718ms =2,5 min What can u conclude from the above results?
quote:Originally posted by derrickleggett Your cache hit ratio is really odd compared to your buffer cache hit ratio. I would add detail to that and post it. I’m guessing it’s your procedure cache hit ratio and adhoc cache hit ratio that are suffering the most. It does definitely look like you need more RAM. You can push it up to 4GB on this server. Since you have Standard Edition, you’re not really going to benefit from more than that. Out of the disk counters, this one: For Disk(1:F)
item:Avg. Disk Read Queue Length
avg:0,840
Max:10,802 is one of the worst. Still, it’s not that bad when you consider how many disks you have (10 on that array). You might benefit from going with RAID 10 on this system; however, your current issues can probably be mostly solved by some good performance tuning. Here are some things to consider: 1) Consider setting your max degree of parallelism to the number of physical processors.
—-It looks like you are having some parallelism issues with the query you posted. 2) Post the detail cache hit ratio counters I posted below so we can further analyze memory usage.
—-I would definitely consider upgrading the server to a full 4GB of RAM. It’s an inexpensive upgrade. 3) Tune individual queries.
—-Taking the example you posted:
——Make sure you have proper indexes on the columns used in the WHERE clause.
——Change the query to use the INNER JOIN syntax and make sure the columns involved specifically in the join are indexed.
——Try using an INNER LOOP JOIN when you change from the col1, col2 syntax and see how that speeds things up.
——If changing your max degree of parallelism doesn’t get rid of all the weird parallelism (including the stream aggregation counts), set the MAXDOP on the query itself and see if that helps. Question: Are you using stored procedures or inline code in your application? 4) Run Profiler. Find out what your top running queries are for reads, writes, and CPU. Run the Profiler on another server or PC and collect the information to a table. 5) Search for locking and blocking. Have you looked at that yet? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Hi derrickleggett,
Thank u very much for your help and for these valuable tips. I will check lockings and mybe u can help to understand the result that I will get.
if there were no blocking issues,
then the fact that the CPU times were reasonably consistent between MAXDOP 1 & 2,
then i would say you have a moderate disk bottleneck. if all data were in memory & there is no blocking, then CPU & Duration would be about the same at MAXDOP 1,
CPU should be approx 2X Dur at MAXDOP 2. if you did have to go disk, then Dur should be longer than CPU, but 8X (at MAXDOP 1) is higher than i would like.
for now, i would run the query with MAXDOP 1 because 20min is better than 30min.
you could look at the plan, guess which ops take the most disk time, and force a different plan to better use the disks.
you could also buy a higher performance disk system. do you have the DB clone sp from MS?, if not, you could look at my article on transfering statistics.
With both the cloned DB with statistics (but no data) and the Perfmon logs, i can do a better analysis.
you could also run Perfmon in log mode, collect %Processor and all physical disk counters at 5 sec intervals, send me the log, i can better analysis more precisely
quote:Originally posted by joechang if there were no blocking issues,
then the fact that the CPU times were reasonably consistent between MAXDOP 1 & 2,
then i would say you have a moderate disk bottleneck. if all data were in memory & there is no blocking, then CPU & Duration would be about the same at MAXDOP 1,
CPU should be approx 2X Dur at MAXDOP 2. if you did have to go disk, then Dur should be longer than CPU, but 8X (at MAXDOP 1) is higher than i would like.
for now, i would run the query with MAXDOP 1 because 20min is better than 30min.
you could look at the plan, guess which ops take the most disk time, and force a different plan to better use the disks.
you could also buy a higher performance disk system. do you have the DB clone sp from MS?, if not, you could look at my article on transfering statistics.
With both the cloned DB with statistics (but no data) and the Perfmon logs, i can do a better analysis.
you could also run Perfmon in log mode, collect %Processor and all physical disk counters at 5 sec intervals, send me the log, i can better analysis more precisely

This is some data about blocking and locking issues. spidloginnamehostnamedbiddbnameObjOwner objIdObjName IndIdIndName TypeResource ModeStatus 51EmpIMSLPT0240912OTP_DATA00DBSGRANT
52NT AUTHORITYSYSTEMASTRA174msdb00DBSGRANT
53EmpIMSLPT024098ISO_DATA00DBSGRANT
55IPIDEmpASTRA177IP_DATA_100DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATA00DBSGRANT
56report_readwriterIMSLPT024098ISO_DATAdbo 309576141X400tblAddresses 0TABISGRANT
56report_readwriterIMSLPT024098ISO_DATAdbo 309576141X400tblAddresses 0TABISGRANT
56report_readwriterIMSLPT024098ISO_DATAdbo 309576141X400tblAddresses 0TABISGRANT
56report_readwriterIMSLPT024098ISO_DATAdbo 309576141X400tblAddresses 0TABISGRANT
56report_readwriterIMSLPT024098ISO_DATAdbo 309576141X400tblAddresses 0TABISGRANT
56report_readwriterIMSLPT024098ISO_DATAdbo 309576141X400tblAddresses 0TABISGRANT
56report_readwriterIMSLPT024098ISO_DATAdbo 309576141X400tblAddresses 0TABISGRANT
56report_readwriterIMSLPT024098ISO_DATAdbo 309576141X400tblAddresses 0TABISGRANT
56report_readwriterIMSLPT024098ISO_DATAdbo 309576141X400tblAddresses 0TABISGRANT
56report_readwriterIMSLPT024098ISO_DATAdbo 565577053tblLengthClass 0TABISGRANT
56report_readwriterIMSLPT024098ISO_DATAdbo 565577053tblLengthClass 0TABISGRANT
56report_readwriterIMSLPT024098ISO_DATAdbo 565577053tblLengthClass 0TABISGRANT
56report_readwriterIMSLPT024098ISO_DATAdbo 565577053tblLengthClass 0TABISGRANT
56report_readwriterIMSLPT024098ISO_DATAdbo 565577053tblLengthClass 0TABISGRANT
56report_readwriterIMSLPT024098ISO_DATAdbo 565577053tblLengthClass 0TABISGRANT
56report_readwriterIMSLPT024098ISO_DATAdbo 565577053tblLengthClass 0TABISGRANT
56report_readwriterIMSLPT024098ISO_DATAdbo 565577053tblLengthClass 0TABISGRANT
56report_readwriterIMSLPT024098ISO_DATAdbo 565577053tblLengthClass 0TABISGRANT
56report_readwriterIMSLPT024098ISO_DATAdbo 501576825X400tblaccounting9 0TABSGRANT
56report_readwriterIMSLPT024098ISO_DATAdbo 501576825X400tblaccounting9 0TABSGRANT
56report_readwriterIMSLPT024098ISO_DATAdbo 501576825X400tblaccounting9 0TABSGRANT
56report_readwriterIMSLPT024098ISO_DATAdbo 501576825X400tblaccounting9 0TABSGRANT
56report_readwriterIMSLPT024098ISO_DATAdbo 501576825X400tblaccounting9 0TABSGRANT
56report_readwriterIMSLPT024098ISO_DATAdbo 501576825X400tblaccounting9 0TABSGRANT
56report_readwriterIMSLPT024098ISO_DATAdbo 501576825X400tblaccounting9 0TABSGRANT
56report_readwriterIMSLPT024098ISO_DATAdbo 501576825X400tblaccounting9 0TABSGRANT
56report_readwriterIMSLPT024098ISO_DATAdbo 501576825X400tblaccounting9 0TABSGRANT
64EmpIMSLPT024098ISO_DATA00DBSGRANT
65NT AUTHORITYSYSTEMASTRA174msdb00DBSGRANT
68EmpIMSLPT024091masterdbo 85575343spt_values 0TABISGRANT
68EmpIMSLPT024092tempdbdbo 151566345#locktable__________________________________________________________________________________________________________000100C6475B 0#locktable TABIXGRANT
Can you please check if your query returns the same result as the query bellow:
select str (100-cast(
(SELECT count (a.acc_mpdu_id))
FROM X400tblAccounting7 a
join X400tblAccounting7 b on a.acc_mpdu_id = b.acc_mpdu_id and a.acc_dateTime <= dateAdd(second, 30, b.acc_datetime)
WHERE a.acc_local_mta in (‘OBSWITCH1’, ‘OBSWITCH3’)
AND a.acc_remote_mta in (‘APGTOU23-MTA’, ‘APGTOU65-MTA’, ‘APMOSU6IN-MTA’, ‘APMOSU6OUT-MTA’, ‘APMOSU7IN-MTA’, ‘APMOSU7OUT-MTA’, ‘APMOSU8IN-MTA’, ‘APMOSU8OUT-MTA’, ‘APMOSU9IN-MTA’, ‘APMOSU9OUT-MTA’)
AND a.acc_mpdu_type = 0
AND a.acc_record_type = ‘I’
AND a.acc_mpdu_priority = 0
AND a.acc_report = 1
AND b.acc_local_mta = ‘OBEBD’
AND b.acc_record_type = ‘D’
AND b.acc_report = 1
) as real)
/
(SELECT (case count (a.acc_mpdu_id)
when 0 then 1
else count(a.acc_mpdu_id)
end)
FROM X400tblAccounting7 a
join X400tblAccounting7 b on a.acc_mpdu_id = b.acc_mpdu_id
WHERE a.acc_local_mta in (‘OBSWITCH1’, ‘OBSWITCH3’)
AND a.acc_remote_mta in (‘APGTOU23-MTA’, ‘APGTOU65-MTA’, ‘APMOSU6IN-MTA’, ‘APMOSU6OUT-MTA’, ‘APMOSU7IN-MTA’, ‘APMOSU7OUT-MTA’, ‘APMOSU8IN-MTA’, ‘APMOSU8OUT-MTA’, ‘APMOSU9IN-MTA’, ‘APMOSU9OUT-MTA’)
AND a.acc_mpdu_type = 0
AND a.acc_record_type = ‘I’
AND a.acc_mpdu_priority = 0
AND a.acc_report = 1
AND b.acc_report = 1
AND b.acc_local_mta = ‘OBEBD’
AND b.acc_record_type = ‘D’
)* 100), 6,2) + ‘%’
Is acc_mpdu_id nullable? If not see if the code bellow returns the same result as yours:
select str (100-cast(sum(case
when a.acc_dateTime <= dateAdd(second, 30, b.acc_datetime) then 1
else 0
end
) as real
)
/
case count (a.acc_mpdu_id)
when 0 then 1
else count(a.acc_mpdu_id)
end
FROM X400tblAccounting7 a
join X400tblAccounting7 b on a.acc_mpdu_id = b.acc_mpdu_id
WHERE a.acc_local_mta in (‘OBSWITCH1’, ‘OBSWITCH3’)
AND a.acc_remote_mta in (‘APGTOU23-MTA’, ‘APGTOU65-MTA’, ‘APMOSU6IN-MTA’, ‘APMOSU6OUT-MTA’, ‘APMOSU7IN-MTA’, ‘APMOSU7OUT-MTA’, ‘APMOSU8IN-MTA’, ‘APMOSU8OUT-MTA’, ‘APMOSU9IN-MTA’, ‘APMOSU9OUT-MTA’)
AND a.acc_mpdu_type = 0
AND a.acc_record_type = ‘I’
AND a.acc_mpdu_priority = 0
AND a.acc_report = 1
AND b.acc_local_mta = ‘OBEBD’
AND b.acc_record_type = ‘D’
AND b.acc_report = 1
]]>