Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vandelft
Starting Member
14 Posts |
 Posted - 2005-10-01 : 16:18:28
|
| Hi All,I'm working right now to optimize a SQL server 2000 database environment.The current situation is:Hardware:RAM memory: 1.5 GbHard disk:RAID5Smart array 5300 controllerWith two arrays:Array A: 2 hardisks 18.2 GB eachArray B:10 hardisk 72.8 GB each one disk for spare.Operating System:Windows 2000 serverDatabase Server:Windows SQL Server 2000 standard editionThe Array A is reserved for the operating system.The Array B is for the data.5 databases are implemented on the SQL Server.Both the data files and the transaction log files of all the 5 databases are saved in one partition F:(array B).On the server there are 3 applications written in VB5.0 (RDO, ODBC) active and continuously writing to the database.Other applications (client/server) also written in VB5.0 (RDO, ODBC connections) are used to generate report on the client machine. The reports are defined to write to a word document (bookmarks in Word).The time taken to generate such a report is to long.What’s your advice?Thank u for your help. |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-10-01 : 17:05:38
|
| How are the 10 disks configured? Is that a big RAID 5 configuration? What do the disk read and write queue lengths look like when you're having slowtime? Is the reporting application using stored procedure or inline code to query the database? Have you looked at the query plans to determine if indexes are being used? What performance tuning steps have you taken so far?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
vandelft
Starting Member
14 Posts |
Posted - 2005-10-01 : 17:36:11
|
[quote]Originally posted by derrickleggett
How are the 10 disks configured? Is that a big RAID 5 configuration? The 10 disks are indeed configured as one array.>>What do the disk read and write queue lengths look like when you're having slowtime? I have used System Monitor to capture some performance counters:Avg Disk Queue Length:Avg:11,771Min:0,687Max:39,691On The server I have 1.5 GB RAM memoryObject Memory:Committed BytesAvg:1886275133 ByteMin:1882927104 ByteMAx:2306556768 ByteCan I conclude from the above values (committed Bytes) that that extra RAM memory is needed on the server?Available Byte counterAvg:4986921 ByteMin:4603904 ByteMax:5742592 Byte>>Is the reporting application using stored procedure or inline code to query the database? The reporting application connects to the database using an ODBC connection. The text of the queries is stored in the database. So there are a lot of roundtrips.The most queries are inline queries, very few stored procedures.>>Have you looked at the query plans to determine if indexes are being used?
yes I have used the DBCC SHOWCONTIG command and I have not yet analyzed the results.>>What performance tuning steps have you taken so far?I have collected all the information about the environment.First about the used hardware.Database server configuration settings.The configuration of the different databases.The applications active on the server.Watt kind of technologies are used (in this case VB 5.0 RDO, ODBC)I have analyzed the queries.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-10-01 : 18:28:58
|
| please tell us what the current disk queue looks like for all physical disks. When the system is busy, watch the current disk queue numbers and tell us what the range is. It is important to know if the queue fluctuates up and down or if it stays at some kind of constant.check a couple of memory counters under SQLServer:memory manager:target server memory and total server memory. What are the values?Please respond with the disk queue ranges for all physical disks (watch it for a few minutes when the system is busy) and the target and total server memory values.-ec |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-10-01 : 18:48:35
|
quote:
Avg Disk Queue Length:Avg:11,771Min:0,687Max:39,691
What are the read and write queue lengths for each disk. The overall isn't near as valuable. MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
vandelft
Starting Member
14 Posts |
Posted - 2005-10-01 : 19:06:42
|
quote:
Originally posted by derrickleggett
quote:
Avg Disk Queue Length:Avg:11,771Min:0,687Max:39,691
What are the read and write queue lengths for each disk. The overall isn't near as valuable. This are the Values I have collected:PhysicalDisk(_Total)\Avg. Disk Read Queue LengthMinimum: VALUE=0,7576Maximum: VALUE=1,9163Average: VALUE=0,957986868686869PhysicalDisk(_Total)\Avg. Disk sec/ReadMinimum" VALUE=1,30796178343949E-03Maximum" VALUE=3,79465346534653E-03Average" VALUE=1,85630933041044E-03PhysicalDisk(_Total)\Avg. Disk sec/Write">Minimum" VALUE="1,29268292682927E-04Maximum" VALUE="5,46376811594203E-04Average" VALUE="1,62386390118853E-04PhysicalDisk(_Total)\Current Disk Queue Length">Minimum" VALUE="0"Maximum" VALUE="10"Average" VALUE="1,05"PhysicalDisk(_Total)\Disk Read Bytes/sec">Minimum" VALUE="2488712,68116288"Maximum" VALUE="5286103,21551249"Average" VALUE="3610427,8215927"PhysicalDisk(_Total)\Disk Reads/sec">Minimum" VALUE="413,40917865696"Maximum" VALUE="683,783150194319"Average" VALUE="516,102545921543"PhysicalDisk(_Total)\Disk Write Bytes/sec"> Minimum" VALUE="20441,8143060289"Maximum" VALUE="1034605,53077646"Average" VALUE="156298,935157415"SQLServer:Buffer Manager\Buffer cache hit ratio">Minimum" VALUE="99,33799190879Maximum" VALUE="99,8655913978495Average" VALUE="99,6501164789699MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA.
|
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-10-01 : 19:21:42
|
| We need the counters for each individual disk, not the total.Please respond with the total and target memory counters also. |
|
|
|
vandelft
Starting Member
14 Posts |
Posted - 2005-10-01 : 19:26:17
|
quote:
Originally posted by eyechart
We need the counters for each individual disk, not the total.Please respond with the total and target memory counters also.
Hi eyechart,The 10 disks(one for spare) are considerd as one disk(they are configured as one RAID5).How can I mesure this counters for each individual disk?I'm using only the System Monitor tool. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-10-01 : 19:48:04
|
| I mean the logical drives. the Total includes the combined statistics for all drives. We need the statistics broken down by drive letter.-ec |
|
|
|
vandelft
Starting Member
14 Posts |
Posted - 2005-10-01 : 20:12:31
|
quote:
Originally posted by eyechart
I mean the logical drives. the Total includes the combined statistics for all drives. We need the statistics broken down by drive letter.-ec
On this server I have two logical drives:The first (c:) containing two disks is for OSThe second (F:) containing 10 disks are in one Array and this array of disks are used for data. In F: are all the Data files and all the Transaction files of the 5 databases saved.My question here is is it good to put the data files and the Transaction files on the same logical drive(F: in this case).The SQL server are configured to use 4 processors. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-10-01 : 20:13:30
|
| And, please give us the counters for when you're actually having issues. Showing us random counter numbers won't do us any good. We need to see what they look like when you're having the issue. MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
vandelft
Starting Member
14 Posts |
Posted - 2005-10-01 : 20:31:56
|
quote:
Originally posted by derrickleggett
And, please give us the counters for when you're actually having issues. Showing us random counter numbers won't do us any good. We need to see what they look like when you're having the issue. MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA.
thank u derrickleggett.I will check this counters on monday.I have the following query with statistics results. can you give some advize in tuning this query.SET STATISTICS TIME ONUSE <DATABASE>SELECT str (100-(cast (count (a.acc_mpdu_id) as real)/ (SELECT (case count (a.acc_mpdu_id) when 0 then 1 else count(a.acc_mpdu_id) end) FROM X400TBL a, X400TBL b WHERE a.acc_local_mta in ('OBSW1', 'OBSW3') 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 b.acc_local_mta = 'OBE' AND b.acc_record_type = 'D' AND a.acc_mpdu_priority = 0 AND a.acc_mpdu_id = b.acc_mpdu_id AND a.acc_report = 1 AND b.acc_report = 1 )* 100) , 6,2) + '%'FROM X400TBL a, X400TBL BWHERE a.acc_local_mta in ('OBSW1', 'OBSW3') 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 = 0AND a.acc_record_type = 'I' AND b.acc_local_mta = 'OBE' AND b.acc_record_type = 'D'AND a.acc_mpdu_priority = 0AND a.acc_mpdu_id = b.acc_mpdu_idAND a.acc_report = 1AND b.acc_report = 1AND DATEDIFF(second, a.acc_datetime, b.acc_datetime)> 30---------------SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.--SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.--(1 row(s) affected)----SQL Server Execution Times: CPU time = 180324 ms, elapsed time = 1036206 ms. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-10-01 : 21:37:54
|
| SET STATISTICS IO on too. we need to see an execution plan as wellsome table DDL, including indexes will also be necessary.-ec |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-10-01 : 21:59:38
|
| 1. You should probably change that to an INNER JOIN.2. Do you have indexes on acc_local_mta, acc_remote_mta, mpdu_type, acc_record_type, acc_local_mta, acc_mpdu_priority, acc_mpdu_id, acc_datetime, and the acc_report fields?--Which do you have indexes on?3. Run the query with "SET STATISTICS PROFILE ON" at the top of the query. Set the results in Query Analyzer to text. Post the results here, along with the recommended data from ec.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
vandelft
Starting Member
14 Posts |
Posted - 2005-10-03 : 09:53:32
|
quote:
Originally posted by eyechart
>>SET STATISTICS IO on too. we need to see an execution plan as wellAfter having set The Statistics IO On:The result IS:Table 'X400tbl'. Scan count 16, logical reads 1887132, physical reads 1964, read-ahead reads 1898351.some table DDL, including indexes will also be necessary.The Database Table X400TBL has a total row of 22937038. In The query We use Count(*) Or Count(columnname).Is this the problem (count)why this query takes a long time to finish?If I use the following query to count the total row number of the X400TBL Table.SELECT rows FROM sysindexes WHERE id = OBJECT_ID('tbTest') AND indid < 2GO the query takes an acceptable time to finish.But I'm wondering if it is risk free to use this query because of the sysindexes table.On the table there is a clusterd, unique, primar index (Index keys:acf_id, acc_line_number, acc_datetime, acc_recipient_number)and all column used in the Where close are nonclusterd indexes.-ec
|
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-10-03 : 10:05:00
|
quote:
Originally posted by vandelft
quote:
Originally posted by eyechart
>>SET STATISTICS IO on too. we need to see an execution plan as wellAfter having set The Statistics IO On:The result IS:Table 'X400tbl'. Scan count 16, logical reads 1887132, physical reads 1964, read-ahead reads 1898351.some table DDL, including indexes will also be necessary.The Database Table X400TBL has a total row of 22937038. In The query We use Count(*) Or Count(columnname).Is this the problem (count)why this query takes a long time to finish?If I use the following query to count the total row number of the X400TBL Table.SELECT rows FROM sysindexes WHERE id = OBJECT_ID('tbTest') AND indid < 2GO the query takes an acceptable time to finish.But I'm wondering if it is risk free to use this query because of the sysindexes table.On the table there is a clusterd, unique, primar index (Index keys:acf_id, acc_line_number, acc_datetime, acc_recipient_number)and all column used in the Where close are nonclusterd indexes.-ec
ok, you've given us a bit of data here and there. Now it is time to give us all the data we requested. Otherwise, we cannot help you.1. please post table DDL including indexes. 2. post the query that is slow3. Post the explain plan using SET STATISTICS PROFILE ON-ec |
|
|
|
vandelft
Starting Member
14 Posts |
Posted - 2005-10-03 : 10:26:47
|
quote:
Originally posted by eyechart
quote:
Originally posted by vandelft
quote:
Originally posted by eyechart
>>SET STATISTICS IO on too. we need to see an execution plan as wellAfter having set The Statistics IO On:The result IS:Table 'X400accountingtbl'. Scan count 16, logical reads 1887132, physical reads 1964, read-ahead reads 1898351.some table DDL, including indexes will also be necessary.The Database Table X400blaccounting has a total row of 22937038. In The query We use Count(*) Or Count(columnname).Is this the problem (count)why this query takes a long time to finish?If I use the following query to count the total row number of the X400TBL Table.SELECT rows FROM sysindexes WHERE id = OBJECT_ID('tbTest') AND indid < 2GO the query takes an acceptable time to finish.But I'm wondering if it is risk free to use this query because of the sysindexes table.On the table there is a clusterd, unique, primar index (Index keys:acf_id, acc_line_number, acc_datetime, acc_recipient_number)and all column used in the Where close are nonclusterd indexes.-ec
ok, you've given us a bit of data here and there. Now it is time to give us all the data we requested. Otherwise, we cannot help you.1. please post table DDL including indexes. Thank u eyechart,I will try to post all the information.Information about the table:-----------------------------------------Name Owner Type Created_datetimeX400tblaccounting7 dbo user table 2003-07-17 10:07:02.357 Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collationacf_id int no 4 10 0 no (n/a) (n/a) acc_line_number int no 4 10 0 no (n/a) (n/a) acc_record_type char no 1 yes no yes SQL_Latin1_General_CP1_CI_ASacc_datetime datetime no 8 no (n/a) (n/a) acc_mpdu_id varchar no 76 yes no no SQL_Latin1_General_CP1_CI_ASacc_mpdu_type tinyint no 1 3 0 yes (n/a) (n/a) acc_mpdu_priority tinyint no 1 3 0 yes (n/a) (n/a) adr_id_originator int no 4 10 0 yes (n/a) (n/a) adr_id_recipient int no 4 10 0 yes (n/a) (n/a) acc_recipient_number smallint no 2 5 0 no (n/a) (n/a) acc_recipient_count smallint no 2 5 0 yes (n/a) (n/a) acc_local_mta varchar no 32 yes no no SQL_Latin1_General_CP1_CI_ASacc_remote_mta varchar no 41 yes no no SQL_Latin1_General_CP1_CI_ASacc_size int no 4 10 0 yes (n/a) (n/a) acc_content_length int no 4 10 0 yes (n/a) (n/a) acc_assoc_event tinyint no 1 3 0 yes (n/a) (n/a) acc_assoc_nettype char no 1 yes no yes SQL_Latin1_General_CP1_CI_ASacc_assoc_direction char no 2 yes no yes SQL_Latin1_General_CP1_CI_ASacc_assoc_duration int no 4 10 0 yes (n/a) (n/a) acc_assoc_confirm_duration int no 4 10 0 yes (n/a) (n/a) acc_channel_id int no 4 10 0 yes (n/a) (n/a) acc_assoc_type tinyint no 1 3 0 yes (n/a) (n/a) acc_bytes_transmitted int no 4 10 0 yes (n/a) (n/a) acc_bytes_received int no 4 10 0 yes (n/a) (n/a) acc_local_ipm_id varchar no 64 yes no no SQL_Latin1_General_CP1_CI_ASacc_original_mpdu_id varchar no 76 yes no no SQL_Latin1_General_CP1_CI_ASacc_recipient_delivery_status tinyint no 1 3 0 yes (n/a) (n/a) acc_diagnostic_codes tinyint no 1 3 0 yes (n/a) (n/a) acc_reason_code tinyint no 1 3 0 yes (n/a) (n/a) len_code tinyint no 1 3 0 yes (n/a) (n/a) acc_country_code tinyint no 1 3 0 yes (n/a) (n/a) acc_report bit no 1 no (n/a) (n/a) Identity Seed Increment Not For ReplicationNo identity column defined. RowGuidColNo rowguidcol column defined. Data_located_on_filegroupPRIMARY index_name index_description index_keysidx_acc_country_code nonclustered located on PRIMARY acc_country_codeidx_acc_datetime nonclustered located on PRIMARY acc_datetimeidx_acc_local_mta nonclustered located on PRIMARY acc_local_mtaidx_acc_mpdu_id nonclustered located on PRIMARY acc_mpdu_ididx_acc_mpdu_priority nonclustered located on PRIMARY acc_mpdu_priorityidx_acc_mpdu_type nonclustered located on PRIMARY acc_mpdu_typeidx_acc_recordtype nonclustered located on PRIMARY acc_record_typeidx_acc_remote_mta nonclustered located on PRIMARY acc_remote_mtaidx_adr_id_originator nonclustered located on PRIMARY adr_id_originatoridx_adr_id_recipient nonclustered located on PRIMARY adr_id_recipientidx_len_code nonclustered located on PRIMARY len_codepri_7 clustered, unique, primary key located on PRIMARY acf_id, acc_line_number, acc_datetime, acc_recipient_number constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keysDEFAULT on column acc_report DF_X400tblaccounting7_acc_report (n/a) (n/a) (n/a) (n/a) (1)FOREIGN KEY FK_X400tblaccounting7_X400tblaccountingFiles No Action No Action Enabled Is_For_Replication acf_id REFERENCES ISO_DATA.dbo.X400tblaccountingFiles (acf_id)FOREIGN KEY for_acf_id_7 No Action No Action Enabled Is_For_Replication acf_id REFERENCES ISO_DATA.dbo.X400tblaccountingFiles (acf_id)PRIMARY KEY (clustered) pri_7 (n/a) (n/a) (n/a) (n/a) acf_id, acc_line_number, acc_datetime, acc_recipient_number No foreign keys reference this table.No views with schema binding reference this table. ---------------------------------------------------------------------2. post the query that is slowThis is The Query that takes a lot of time to finish.-------------------------------------------------------------------SELECT str (100-(cast (count (a.acc_mpdu_id) as real)/ (SELECT (case count (a.acc_mpdu_id) when 0 then 1 else count(a.acc_mpdu_id) end) FROM X400tblAccounting7 a, X400tblAccounting7 b 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 b.acc_local_mta = 'OBEBD' AND b.acc_record_type = 'D' AND a.acc_mpdu_priority = 0 AND a.acc_mpdu_id = b.acc_mpdu_id AND a.acc_report = 1 AND b.acc_report = 1 )* 100) , 6,2) + '%'FROM X400tblAccounting7 a, X400tblAccounting7 bWHERE 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 = 0AND a.acc_record_type = 'I' AND b.acc_local_mta = 'OBEBD' AND b.acc_record_type = 'D'AND a.acc_mpdu_priority = 0AND a.acc_mpdu_id = b.acc_mpdu_idAND a.acc_report = 1AND b.acc_report = 1AND DATEDIFF(second, a.acc_datetime, b.acc_datetime)> 30---------------------------------------------------------------------3. Post the explain plan using SET STATISTICS PROFILE ONIs There a beter way to present this results. I don't know if it is "read freindely". This statistics took approximately 30 min.Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------ ------------------------ ------------------------ ----------- ------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------- ------------------------------ -------- ------------------------ 1 1 SELECT str (100-(cast (count (a.acc_mpdu_id) as real)/ (SELECT (case count (a.acc_mpdu_id) when 0 then 1 else count(a.acc_mpdu_id) end) FROM X400tblAccounting7 a, X400tblAccounting7 b WHERE a.acc_local_mta in ('OBSWITCH1', 'OBSWITCH3') AND a.acc_re 3 1 0 NULL NULL NULL NULL 1.0 NULL NULL NULL 47726.383 NULL NULL SELECT 0 NULL1 1 |--Compute Scalar(DEFINE:([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:([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.01 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.01 1 |--Compute Scalar(DEFINE:([Expr1002]=Convert([globalagg1017]))) 3 4 3 Compute Scalar Compute Scalar DEFINE:([Expr1002]=Convert([globalagg1017])) [Expr1002]=Convert([globalagg1017]) 1.0 0.0 0.0000001 11 14334.903 [Expr1002] NULL PLAN_ROW 0 1.01 1 | |--Stream Aggregate(DEFINE:([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.04 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.04 4 | |--Stream Aggregate(DEFINE:([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.04222 4 | |--Hash Match(Inner Join, HASH:([a].[acc_mpdu_id])=([b].[acc_mpdu_id]), RESIDUAL:([b].[acc_mpdu_id]=[a].[acc_mpdu_id] AND datediff(second, [a].[acc_datetime], [b].[acc_datetime])>30)) 3 8 7 Hash Match Inner Join HASH:([a].[acc_mpdu_id])=([b].[acc_mpdu_id]), RESIDUAL:([b].[acc_mpdu_id]=[a].[acc_mpdu_id] AND datediff(second, [a].[acc_datetime], [b].[acc_datetime])>30) NULL 6.4354545E+9 0.0 13226.939 104 14013.103 NULL NULL PLAN_ROW -1 1.01977289 4 | |--Parallelism(Repartition Streams, PARTITION COLUMNS:([a].[acc_mpdu_id])) 3 10 8 Parallelism Repartition Streams PARTITION COLUMNS:([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.01977289 4 | | |--Hash Match(Inner Join, HASH:([Expr1014])=([a].[acc_remote_mta]), RESIDUAL:([a].[acc_remote_mta]=[Expr1014])) 3 11 10 Hash Match Inner Join HASH:([Expr1014])=([a].[acc_remote_mta]), RESIDUAL:([a].[acc_remote_mta]=[Expr1014]) NULL 516221.19 0.0 11.383599 80 399.69067 [a].[acc_datetime], [a].[acc_mpdu_id] NULL PLAN_ROW -1 1.040 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.010 1 | | | |--Sort(DISTINCT ORDER BY:([Expr1014] ASC)) 3 13 12 Sort Distinct Sort DISTINCT ORDER BY:([Expr1014] ASC) NULL 10.0 1.1261261E-2 1.5184008E-4 18 1.1423258E-2 [Expr1014] NULL PLAN_ROW 0 1.010 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.04249976 4 | | |--Clustered Index Scan(OBJECT:([ISO_DATA].[dbo].[X400tblaccounting7].[pri_7] AS [a]), WHERE:(((([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:([ISO_DATA].[dbo].[X400tblaccounting7].[pri_7] AS [a]), WHERE:(((([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.01283618 4 | |--Parallelism(Repartition Streams, PARTITION COLUMNS:([b].[acc_mpdu_id])) 3 28 8 Parallelism Repartition Streams PARTITION COLUMNS:([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.01283618 4 | |--Clustered Index Scan(OBJECT:([ISO_DATA].[dbo].[X400tblaccounting7].[pri_7] AS [b]), WHERE:(([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:([ISO_DATA].[dbo].[X400tblaccounting7].[pri_7] AS [b]), WHERE:(([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.01 1 |--Compute Scalar(DEFINE:([Expr1005]=Convert([globalagg1021]))) 3 40 3 Compute Scalar Compute Scalar DEFINE:([Expr1005]=Convert([globalagg1021])) [Expr1005]=Convert([globalagg1021]) 1.0 0.0 0.0000001 11 33391.48 [Expr1005] NULL PLAN_ROW 0 1.01 1 |--Stream Aggregate(DEFINE:([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.04 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.04 4 |--Stream Aggregate(DEFINE:([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.01032892 4 |--Hash Match(Inner Join, HASH:([a].[acc_mpdu_id])=([b].[acc_mpdu_id]), RESIDUAL:([b].[acc_mpdu_id]=[a].[acc_mpdu_id])) 3 44 43 Hash Match Inner Join HASH:([a].[acc_mpdu_id])=([b].[acc_mpdu_id]), RESIDUAL:([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.01977289 4 |--Parallelism(Repartition Streams, PARTITION COLUMNS:([a].[acc_mpdu_id])) 3 46 44 Parallelism Repartition Streams PARTITION COLUMNS:([a].[acc_mpdu_id]) NULL 516221.19 0.0 3.6283686 80 403.31906 [a].[acc_mpdu_id] NULL PLAN_ROW -1 1.01977289 4 | |--Hash Match(Inner Join, HASH:([Expr1018])=([a].[acc_remote_mta]), RESIDUAL:([a].[acc_remote_mta]=[Expr1018])) 3 47 46 Hash Match Inner Join HASH:([Expr1018])=([a].[acc_remote_mta]), RESIDUAL:([a].[acc_remote_mta]=[Expr1018]) NULL 516221.19 0.0 11.383599 80 399.69067 [a].[acc_mpdu_id] NULL PLAN_ROW -1 1.040 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.010 1 | | |--Sort(DISTINCT ORDER BY:([Expr1018] ASC)) 3 49 48 Sort Distinct Sort DISTINCT ORDER BY:([Expr1018] ASC) NULL 10.0 1.1261261E-2 1.5184008E-4 18 1.1423258E-2 [Expr1018] NULL PLAN_ROW 0 1.010 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.04249976 4 | |--Clustered Index Scan(OBJECT:([ISO_DATA].[dbo].[X400tblaccounting7].[pri_7] AS [a]), WHERE:(((([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:([ISO_DATA].[dbo].[X400tblaccounting7].[pri_7] AS [a]), WHERE:(((([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.01283618 4 |--Parallelism(Repartition Streams, PARTITION COLUMNS:([b].[acc_mpdu_id])) 3 64 44 Parallelism Repartition Streams PARTITION COLUMNS:([b].[acc_mpdu_id]) NULL 685798.63 0.0 4.8109169 180 381.60953 [b].[acc_mpdu_id] NULL PLAN_ROW -1 1.01283618 4 |--Clustered Index Scan(OBJECT:([ISO_DATA].[dbo].[X400tblaccounting7].[pri_7] AS [b]), WHERE:(([b].[acc_local_mta]='OBEBD' AND [b].[acc_record_type]='D') AND Convert([b].[acc_report])=1)) 3 65 64 Clustered Index Scan Clustered Index Scan OBJECT:([ISO_DATA].[dbo].[X400tblaccounting7].[pri_7] AS [b]), WHERE:(([b].[acc_local_mta]='OBEBD' AND [b].[acc_record_type]='D') AND Convert([b].[acc_report])=1) [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_report], [b].[acc_mpdu_id], [b].[acc_record_type], [b].[acc_local_mta] NULL PLAN_ROW -1 1.0(29 row(s) affected)----------------------------------------------------------------------ec
|
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-10-03 : 12:31:00
|
| When you post, put [ c o d e ] and [ / c o d e ] around the code examples (don't include the spaces). That formats it all nice and pretty for us.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
vandelft
Starting Member
14 Posts |
Posted - 2005-10-03 : 12:42:23
|
quote:
Originally posted by derrickleggett
When you post, put [ c o d e ] and [ / c o d e ] around the code examples (don't include the spaces). That formats it all nice and pretty for us.THanx for this tip. It's much beter now. MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA.
|
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-10-03 : 14:41:23
|
quote:
Originally posted by vandelft
[quote]Originally posted by derrickleggett>>Have you looked at the query plans to determine if indexes are being used?
yes I have used the DBCC SHOWCONTIG command and I have not yet analyzed the results.
Did I miss something here? Query plans are not the same as DBCC SHOWCONTIG. Showcontig only shows you if your indexes are "out of wack". You'd want to look at the execution plan in query analyer for the query plan. Then you can see if your query is using any of the existing indexes or if the indexes it is using are not the best choice...Regards,DanielSQL Server DBAwww.dallasteam.com |
|
|
|
vandelft
Starting Member
14 Posts |
Posted - 2005-10-04 : 06:28:03
|
quote:
Originally posted by SQLServerDBA_Dan
quote:
Originally posted by vandelft
[quote]Originally posted by derrickleggett>>Have you looked at the query plans to determine if indexes are being used?
yes I have used the DBCC SHOWCONTIG command and I have not yet analyzed the results.
Did I miss something here? Query plans are not the same as DBCC SHOWCONTIG. Showcontig only shows you if your indexes are "out of wack". You'd want to look at the execution plan in query analyer for the query plan. Then you can see if your query is using any of the existing indexes or if the indexes it is using are not the best choice...Regards,DanielSQL Server DBAwww.dallasteam.com
Hi Daniel,Thank u for your replay.I have used index tuning wizard but after it terminates I get no recommendation. I have some table traces that the index wizard uses. |
|
|
|
Next Page
|
|
|
|
|
Performance tuning SQL 2000