June 17, 2002 at 2:36 pm
hi all,
We use MS SQL 7.0 and Cognos Impromptu 7.0 as our datawarehouse technology. Cognos is the front end business intelligence tool. We have about 1GB of RAM for this server. For the past 1 week strange things seem to happen in our system. For e.g a report that a user has been running monthly does not run but gives a DBMS -9 error. I tried to run this report from my desk, and i looked at the trace, it says CPU 55 % and RAM about 390MB. Still the report does not run. All the tables are of the same size nothing really has increased. I have added about 3/4 more users to the database. So last thursday i created the same report on my desk, this time it worked fine (this time i rebooted SQL server). However, today when i try to run the same report, it does nothing. Please tell me if i need to increase the RAM size or there is a performance tuning that i need to do in SQL server before i run the reports.
June 17, 2002 at 4:55 pm
55% CPU and 390MB of RAM should be a none issue. Have you tried running the query the report is submitting in QA to see if it returns when the report is failing? You can use Profiler to get this.
There may be some issue killing it that the reporting software is not making you aware of. If so the QA may give you an idea.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 18, 2002 at 3:13 pm
I think the page file was very small. For instance our RAM size is 1GB but our Page file size was only 512MB. I have increased the page file on the D drive by another 2GB. Hope this will fix the issue. Also, how do i invoke the query analyzer?
June 18, 2002 at 3:44 pm
It is an application from the client install option of SQL. Like EM you just execute it, should be in the MS SQL Tools program folder. If not but you can find EM, open EM to the server and on the tools menu choose Query Analyzer. You will have to either know the query exactly or capture the Profiler SQL:STMTbegin.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 18, 2002 at 4:02 pm
i have been using query analyzer extensively, so sorry about the previous question. What you are saying is, i should test the query and see where the problem is? I have to write this big sql to do it. I think i may do it. At the same time you recommend me to use the profiler to test the performance?
June 18, 2002 at 4:33 pm
Actually no, you can use Profiler to catch the query being sent from the client, this will give you the exact SQL query statement. Then take this and paste into QA and run.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 18, 2002 at 6:47 pm
i did copy the query from the trace into QA and looks like its giving the same result, the query just keeps trying and trying with no result. What is surprising is we never had this problem until two weeks back. Seems like the query is not executing. Anything else i should look into?
June 18, 2002 at 7:41 pm
Try
SET SHOWPLAN_ALL ON
GO
YOURQUERYHERE
GO
SET SHOWPLAN_ALL OFF
GO
see if you get the query plan information. Try using sp_updatestats to make sure statistics are inline, then DBCC UPDATEUSAGE to make sure no inaccuracies in sysindexes, and try DBCC DBREINDEX to defragment you indexes (if this system has a lot of records you will want to do this at a time when no to very minimal actvity is going on). These things can bring the speed backup and overtime the issues they correct are the biggest contributors to speed drops.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 19, 2002 at 8:59 am
ok This is what i see..can you tell me if you see anything unusual here?
StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ --------------------------------------------------------------------------------------------------------------- -------- ------------------------------ -------- ------------------------
select T1.MONTH , T1.YEAR , T2.REG ,
sum(T1.UNIT_TOTAL_AMOUNT-T1.UNIT_DISC_AMOUNT)
from ODS.dbo.CUSTMST T4, ODS.dbo.ICEDETHST T1,
ODS.dbo.SALESMAN T5, ODS.dbo.REGION T2, ODS.dbo.ITMMSTUSR T3
where T4.CUST_NBR=T1.SHIPTO and T4.SLS_CODE=T5.SLS and T 1 1 0 NULL NULL NULL NULL 1.0 NULL NULL NULL 17.661644 NULL NULL SELECT 0 NULL
|--Compute Scalar(DEFINE:([Expr1005]=If ([Expr1007]=0) then NULL else [Expr1008])) 1 2 1 Compute Scalar Compute Scalar DEFINE:([Expr1005]=If ([Expr1007]=0) then NULL else [Expr1008]) [Expr1005]=If ([Expr1007]=0) then NULL else [Expr1008] 1.0 0.0 1.6140028E-4 27 17.661644 [T1].[MONTH], [T1].[YEAR], [T2].[REG], [Expr1005] NULL PLAN_ROW 0 1.0
|--Stream Aggregate(GROUP BY:([T1].[MONTH], [T1].[YEAR], [T2].[REG]) DEFINE:([Expr1007]=COUNT([T1].[UNIT_TOTAL_AMOUNT]-[T1].[UNIT_DISC_AMOUNT]), [Expr1008]=SUM([T1].[UNIT_TOTAL_AMOUNT]-[T1].[UNIT_DISC_AMOUNT]))) 1 3 2 Stream Aggregate Aggregate GROUP BY:([T1].[MONTH], [T1].[YEAR], [T2].[REG]) [Expr1007]=COUNT([T1].[UNIT_TOTAL_AMOUNT]-[T1].[UNIT_DISC_AMOUNT]), [Expr1008]=SUM([T1].[UNIT_TOTAL_AMOUNT]-[T1].[UNIT_DISC_AMOUNT]) 1.0 0.0 1.6140028E-4 27 17.661644 [T1].[MONTH], [T1].[YEAR], [T2].[REG], [Expr1007], [Expr1008] NULL PLAN_ROW 0 1.0
|--Nested Loops(Inner Join) 1 4 3 Nested Loops Inner Join NULL NULL 55.982243 0.0 2.3400577E-4 187 17.661482 [T1].[MONTH], [T1].[YEAR], [T2].[REG], [T1].[UNIT_TOTAL_AMOUNT], [T1].[UNIT_DISC_AMOUNT] NULL PLAN_ROW 0 1.0
|--Filter(WHERE:([T5].[REG]='170')) 1 5 4 Filter Filter WHERE:([T5].[REG]='170') NULL 55.982243 0.0 1.0076804E-5 120 17.608335 [T1].[MONTH], [T1].[YEAR], [T1].[UNIT_TOTAL_AMOUNT], [T1].[UNIT_DISC_AMOUNT] NULL PLAN_ROW 0 1.0
| |--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([ODS].[dbo].[SALESMAN] AS [T5]) WITH PREFETCH) 1 6 5 Bookmark Lookup Bookmark Lookup BOOKMARK:([Bmk1002]), OBJECT:([ODS].[dbo].[SALESMAN] AS [T5]) WITH PREFETCH [T5].[REG] 55.982243 6.2500001E-3 0.0 120 17.608309 [T1].[MONTH], [T1].[YEAR], [T1].[UNIT_TOTAL_AMOUNT], [T1].[UNIT_DISC_AMOUNT], [T5].[REG] NULL PLAN_ROW 0 1.0
| |--Nested Loops(Inner Join) 1 9 6 Nested Loops Inner Join NULL NULL 55.982243 0.0 2.3400577E-4 82 17.60206 [T1].[MONTH], [T1].[YEAR], [T1].[UNIT_TOTAL_AMOUNT], [T1].[UNIT_DISC_AMOUNT], [Bmk1002] NULL PLAN_ROW 0 1.0
| |--Parallelism(Gather Streams) 1 12 9 Parallelism Gather Streams NULL NULL 55.982243 0.0 2.8786434E-2 69 17.590961 [T1].[MONTH], [T1].[YEAR], [T1].[UNIT_TOTAL_AMOUNT], [T1].[UNIT_DISC_AMOUNT], [T4].[SLS_CODE] NULL PLAN_ROW 0 1.0
| | |--Hash Match(Inner Join, HASH:([T1].[ITEM_NBR])=([T3].[PART_NBR]), RESIDUAL:([T1].[ITEM_NBR]=[T3].[PART_NBR])) 1 13 12 Hash Match Inner Join HASH:([T1].[ITEM_NBR])=([T3].[PART_NBR]), RESIDUAL:([T1].[ITEM_NBR]=[T3].[PART_NBR]) NULL 55.982243 0.0 0.67830217 69 17.562176 [T1].[MONTH], [T1].[YEAR], [T1].[UNIT_TOTAL_AMOUNT], [T1].[UNIT_DISC_AMOUNT], [T4].[SLS_CODE] NULL PLAN_ROW 1 1.0
| | |--Parallelism(Distribute Streams, PARTITION COLUMNS:([T1].[ITEM_NBR])) 1 14 13 Parallelism Distribute Streams PARTITION COLUMNS:([T1].[ITEM_NBR]) NULL 4.3447914 0.0 2.8537428E-2 61 10.166805 [T1].[MONTH], [T1].[YEAR], [T1].[UNIT_TOTAL_AMOUNT], [T1].[UNIT_DISC_AMOUNT], [T4].[SLS_CODE], [T1].[ITEM_NBR] NULL PLAN_ROW 1 1.0
| | | |--Nested Loops(Inner Join, WHERE:([T4].[CUST_NBR]=[T1].[SHIPTO])) 1 15 14 Nested Loops Inner Join WHERE:([T4].[CUST_NBR]=[T1].[SHIPTO]) NULL 4.3447914 0.0 0.60553151 61 10.138268 [T1].[MONTH], [T1].[YEAR], [T1].[UNIT_TOTAL_AMOUNT], [T1].[UNIT_DISC_AMOUNT], [T4].[SLS_CODE], [T1].[ITEM_NBR] NULL PLAN_ROW 0 1.0
| | | |--Sort(ORDER BY:([T1].[ITEM_NBR] ASC)) 1 16 15 Sort Sort ORDER BY:([T1].[ITEM_NBR] ASC) NULL 1.0 1.1261261E-2 9.9999997E-5 48 6.5422821 [T1].[MONTH], [T1].[YEAR], [T1].[UNIT_TOTAL_AMOUNT], [T1].[UNIT_DISC_AMOUNT], [T1].[ITEM_NBR], [T1].[SHIPTO] NULL PLAN_ROW 0 1.0
| | | | |--Table Scan(OBJECT:([ODS].[dbo].[ICEDETHST] AS [T1]), WHERE:([T1].[YEAR]=2 AND [T1].[MONTH]=5)) 1 17 16 Table Scan Table Scan OBJECT:([ODS].[dbo].[ICEDETHST] AS [T1]), WHERE:([T1].[YEAR]=2 AND [T1].[MONTH]=5) [T1].[MONTH], [T1].[YEAR], [T1].[UNIT_TOTAL_AMOUNT], [T1].[UNIT_DISC_AMOUNT], [T1].[ITEM_NBR], [T1].[SHIPTO] 1.0 5.7398009 0.43954611 150 6.179347 [T1].[MONTH], [T1].[YEAR], [T1].[UNIT_TOTAL_AMOUNT], [T1].[UNIT_DISC_AMOUNT], [T1].[ITEM_NBR], [T1].[SHIPTO] NULL PLAN_ROW 0 1.0
| | | |--Table Spool 1 18 15 Table Spool Lazy Spool NULL NULL 48288.0 0.81405407 8.6918399E-3 20 2.7747176 [T4].[SLS_CODE], [T4].[CUST_NBR] NULL PLAN_ROW 0 3.0
| | | |--Table Scan(OBJECT:([ODS].[dbo].[CUSTMST] AS [T4])) 1 19 18 Table Scan Table Scan OBJECT:([ODS].[dbo].[CUSTMST] AS [T4]) [T4].[SLS_CODE], [T4].[CUST_NBR] 48288.0 1.5368378 5.3195301E-2 108 1.5900331 [T4].[SLS_CODE], [T4].[CUST_NBR] NULL PLAN_ROW 0 1.0
| | |--Parallelism(Repartition Streams, PARTITION COLUMNS:([T3].[PART_NBR])) 1 23 13 Parallelism Repartition Streams PARTITION COLUMNS:([T3].[PART_NBR]) NULL 104963.83 0.0 0.54901564 123 7.2660804 [T3].[PART_NBR] NULL PLAN_ROW 1 1.0
| | |--Filter(WHERE:(If (([T3].[PCC]='G01' OR [T3].[PCC]='G29') OR [T3].[PCC]='G03') then 'GAS' else If ([T3].[PCC]='G02') then 'MIX' else If ((like([T3].[PCC], 'C%') OR like([T3].[PCC], 'S%')) OR like([T3].[PCC], 1 24 23 Filter Filter WHERE:(If (([T3].[PCC]='G01' OR [T3].[PCC]='G29') OR [T3].[PCC]='G03') then 'GAS' else If ([T3].[PCC]='G02') then 'MIX' else If ((like([T3].[PCC], 'C%') OR like([T3].[PCC], 'S%')) OR like([T3].[PCC], 'M%')) then 'EQU' else If ([T3].[PCC]='G07' OR [T3].[PCC NULL 104963.83 0.0 0.01998846 123 6.7170649 [T3].[PART_NBR] NULL PLAN_ROW 1 1.0
| | |--Table Scan(OBJECT:([ODS].[dbo].[ITMMSTUSR] AS [T3])) 1 25 24 Table Scan Table Scan OBJECT:([ODS].[dbo].[ITMMSTUSR] AS [T3]) [T3].[PART_NBR], [T3].[PCC] 111047.0 5.3101711 0.1222302 123 5.4324012 [T3].[PART_NBR], [T3].[PCC] NULL PLAN_ROW 1 1.0
| |--Index Seek(OBJECT:([ODS].[dbo].[SALESMAN].[NCI_SLS] AS [T5]), SEEK:([T5].[SLS]=Convert([T4].[SLS_CODE])) ORDERED) 1 70 9 Index Seek Index Seek OBJECT:([ODS].[dbo].[SALESMAN].[NCI_SLS] AS [T5]), SEEK:([T5].[SLS]=Convert([T4].[SLS_CODE])) ORDERED [Bmk1002] 1.0 6.3284999E-3 7.9603997E-5 21 0.01086341 [Bmk1002] NULL PLAN_ROW 0 55.982243
|--Table Scan(OBJECT:([ODS].[dbo].[REGION] AS [T2]), WHERE:([T2].[REG]='170')) 1 74 4 Table Scan Table Scan OBJECT:([ODS].[dbo].[REGION] AS [T2]), WHERE:([T2].[REG]='170') [T2].[REG] 1.0 4.1282203E-2 0.0001148 76 5.2025072E-2 [T2].[REG] NULL PLAN_ROW 0 55.982243
(20 row(s) affected)
StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
---------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ -------- ------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ ---------- -------- ------------------------------ -------- ------------------------
SET SHOWPLAN_ALL ON 2 1 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL SETON 0 NULL
(1 row(s) affected)
June 19, 2002 at 6:17 pm
Your biggest enemy is the fact you have four table scans that occurr in your query thus slowing you down.
Based on what I see this is my thoughts
Add an combined index (index comprised of two or more columns) to [ODS].[dbo].[ICEDETHST] for MONTH and YEAR.
Add an index to [ODS].[dbo].[REGION] for REG.
Add and index to [ODS].[dbo].[CUSTMST] for CUST_NBR.
Add and index to [ODS].[dbo].[ITMMSTUSR] for PART_NBR.
Preferable if any other the tables above do not have a clustered index or the clustered index is an identity field that is not queried on, consider making the sugested index a clustered index for additional speed imporvements.
Again this is based on my thoughts from the Execution Plan.
If you are curious these are the things I was looking at to determine what is missing from this query.
Table Scan(OBJECT:([ODS].[dbo].[ICEDETHST] AS [T1]),
WHERE:([T1].[YEAR]=2 AND [T1].[MONTH]=5)) 1 17 16
Table Scan Table Scan OBJECT:([ODS].[dbo].[ICEDETHST] AS [T1]),
WHERE:([T1].[YEAR]=2 AND [T1].[MONTH]=5)
[T1].[MONTH],
[T1].[YEAR],
[T1].[UNIT_TOTAL_AMOUNT],
[T1].[UNIT_DISC_AMOUNT],
[T1].[ITEM_NBR],
[T1].[SHIPTO] 1.0 5.7398009 0.43954611 150 6.179347
[T1].[MONTH],
[T1].[YEAR],
[T1].[UNIT_TOTAL_AMOUNT],
[T1].[UNIT_DISC_AMOUNT],
[T1].[ITEM_NBR],
[T1].[SHIPTO] NULL PLAN_ROW 0 1.0
Nested Loops(Inner Join, WHERE:([T4].[CUST_NBR]=[T1].[SHIPTO])) 1 15 14
Nested Loops Inner Join
WHERE:([T4].[CUST_NBR]=[T1].[SHIPTO]) NULL 4.3447914 0.0 0.60553151 61 10.138268
[T1].[MONTH], [T1].[YEAR], [T1].[UNIT_TOTAL_AMOUNT], [T1].[UNIT_DISC_AMOUNT], [T4].[SLS_CODE], [T1].[ITEM_NBR] NULL PLAN_ROW 0 1.0
Table Scan(OBJECT:([ODS].[dbo].[CUSTMST] AS [T4])) 1 19 18
Table Scan Table Scan OBJECT:([ODS].[dbo].[CUSTMST] AS [T4])
[T4].[SLS_CODE], [T4].[CUST_NBR] 48288.0 1.5368378 5.3195301E-2 108 1.5900331
[T4].[SLS_CODE], [T4].[CUST_NBR] NULL PLAN_ROW 0 1.0
Hash Match(Inner Join, HASH:([T1].[ITEM_NBR])=([T3].[PART_NBR]),
RESIDUAL:([T1].[ITEM_NBR]=[T3].[PART_NBR])) 1 13 12
Hash Match Inner Join
HASH:([T1].[ITEM_NBR])=([T3].[PART_NBR]),
RESIDUAL:([T1].[ITEM_NBR]=[T3].[PART_NBR]) NULL 55.982243 0.0 0.67830217 69 17.562176
[T1].[MONTH], [T1].[YEAR], [T1].[UNIT_TOTAL_AMOUNT], [T1].[UNIT_DISC_AMOUNT], [T4].[SLS_CODE] NULL PLAN_ROW 1 1.0
Table Scan(OBJECT:([ODS].[dbo].[ITMMSTUSR] AS [T3])) 1 25 24
Table Scan Table Scan OBJECT:([ODS].[dbo].[ITMMSTUSR] AS [T3])
[T3].[PART_NBR], [T3].[PCC] 111047.0 5.3101711 0.1222302 123 5.4324012
[T3].[PART_NBR], [T3].[PCC] NULL PLAN_ROW 1 1.0
Table Scan(OBJECT:([ODS].[dbo].[REGION] AS [T2]), WHERE:([T2].[REG]='170')) 1 74 4
Table Scan Table Scan OBJECT:([ODS].[dbo].[REGION] AS [T2]),
WHERE:([T2].[REG]='170')
[T2].[REG] 1.0 4.1282203E-2 0.0001148 76 5.2025072E-2
[T2].[REG] NULL PLAN_ROW 0 55.982243
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 06/19/2002 6:18:11 PM
July 1, 2002 at 2:22 pm
antares,
thank you very much buddy. without your help we would have been lost. I was able to create those missing indexes. now system is running very fast. i couldnt reply back sooner as for the past week i was monitoring the system based upon your message. thanks again
July 12, 2002 at 10:27 pm
Glad I could help. I enjoy these kinds of things and have found reading showplan is the best way to get an understanding how to improve performance. My suggestion is everytime you write a query look at the plan and take note of what you see especially in good queries.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply