Database Server Loading

[tonylnng]

Hi all,

When I run a complex SQL statement on Database Server it use 100% loading on CPU, another application can't get any resource and just wait until timeout. Even though, I set the timeout to unlimited but still waiting until the SQL
statement finished.

I have to check Microsoft library and set the Background process to "Application" in order to make the Server hold some resource for another application, but not work.

I would like to ask any alternatives that can separate CPU resources that would not fully occupied to SQL Server?

Database Server Platform
OS: Windows 2K Server
Database: SQL 2000

Any input is highly appreciated. Thanks!


Tony

 

[tlbroadbent]

You are trying to fix the server when the problem is the application. The SQL statement is not efficient. The first rule in SQL optimization is "Optimize the application." You might add more processors and find that the query still uses 100% of al the processor time.

Setting timeout values on the server doesn't change the timeout value of the application. This must be done through the application because it is the application that is timing out while waiting for the server to respond.

Are the tables in the query properly indexed? Does the query utilize existing indexes? Have you analyzed the query to see where the slowdown occurs? Have you used the index tuning wizard to determine if changed or new indexes would improve performance?

How many processors and how much memory is on the server? What is the Disk configuration?

Check the following for a list of performance related articles.

http://tlbroadbent.home.attbi.com/sql/ss_performance.htm

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[tonylnng]

Actually, I am a not sure do the SQL Server utilized the indexes but could be sure that I have to make the index on all primary keys.

Below SQL statement is worked but just take some time to finish, talking about 3 hours at lease. While the SQL is running there is no any other application can run properly.

I do think the problem is come from 2 points,

1. Union, it might take much time on merging data. According to the execution plan, most of all resource is spent to Union.

2. Left outer join, it is also waste server resource to process.

Below SQL statement involved 4 unions the first three SQL is running quite first, around 1 or 2 mins. The last two is the main problem; I have run each SQL individually.

All data around 50,000 records roughly the server only have 1 CPU, PIII and 128M Ram. Hard disk around 10GB and the data file is stored in local drive.


SELECT C.case_no AS CaseNo,
C.status AS Status,
'' AS VchDate,
'' AS VchType,
'' AS VchNo,
Nature,
BaseAmt,
C.solicitor Solicitor
FROM tb_case C
INNER JOIN (SELECT case_no,
'O' AS Nature,
SUM(CASE dc_typ WHEN 'D' THEN amt
WHEN 'C' THEN -1 * amt END) AS BaseAmt
FROM tb_case_o_open
GROUP BY case_no,
dc_typ) D
ON C.case_no = D.case_no
UNION
SELECT C.case_no AS CaseNo,
C.status AS Status,
'' AS VchDate,
'' AS VchType,
'' AS VchNo,
Nature,
BaseAmt,
C.solicitor Solicitor
FROM tb_case C
INNER JOIN (SELECT case_no,
'D' as Nature,
SUM(CASE dc_typ WHEN 'D' THEN amt
WHEN 'C' THEN -1 * amt END) AS BaseAmt
FROM tb_case_d_open
GROUP BY case_no) E
ON C.case_no = E.case_no
UNION
SELECT C.case_no AS CaseNo,
C.status AS Status,
'' AS VchDate,
'' AS VchType,
'' AS VchNo,
Nature,
BaseAmt,
C.solicitor Solicitor
FROM tb_case C
INNER JOIN (SELECT case_no,
'C' AS Nature,
SUM(CASE dc_typ WHEN 'D' THEN amt
WHEN 'C' THEN -1 * amt END) AS BaseAmt
FROM tb_case_c_open
GROUP BY case_no,
dc_typ) F
ON C.case_no = F.case_no
UNION
SELECT C.case_no AS CaseNo,
C.status AS Status,
B.vch_dte AS VchDate,
A.vch_typ AS VchType,
A.vch_no AS VchNo,
A.nature AS Nature,
-ISNULL(base_amt, 0) AS BaseAmt,
C.solicitor Solicitor
FROM tb_case C
LEFT JOIN tb_sub_gl A
ON A.case_no = C.case_no
AND dc_typ = 'C'
LEFT JOIN tb_gl_hd B
ON ((A.vch_typ = B.vch_typ
AND A.vch_no = B.vch_no
AND A.vch_seq = B.vch_seq)
OR ((A.bill_typ = 'PB'
OR A.bill_typ = 'CN')
AND A.bill_typ = B.vch_typ
And A.bill_no = B.vch_no))
AND in_dte > = '2002-04-01 00:00:00'
UNION
SELECT C.case_no AS CaseNo,
C.status AS Status,
B.vch_dte AS VchDate,
A.vch_typ AS VchType,
A.vch_no AS VchNo,
A.nature AS Nature,
ISNULL(base_amt, 0) AS BaseAmt,
C.solicitor Solicitor
FROM tb_case C
LEFT JOIN tb_sub_gl A
ON A.case_no = C.case_no
AND dc_typ = 'D'
LEFT JOIN tb_gl_hd B
ON ((A.vch_typ = B.vch_typ
AND A.vch_no = B.vch_no
AND A.vch_seq = B.vch_seq)
OR ((A.bill_typ = 'PB'
OR A.bill_typ = 'CN')
AND A.bill_typ = B.vch_typ
And A.bill_no = B.vch_no))
AND in_dte > = '2002-04-01 00:00:00'
ORDER BY CaseNo,
VchDate,
VchType,
VchNo,
Nature

 

[tlbroadbent]

The default behavior of UNION is to eliminate duplicates. In order to eliminate duplicates the result set must be ordered and each compared with the last row to determine if it is a duplicate. If each of the queries returns unique data, you can speed up the query by using UNION ALL rather than UNION.

Make sure the columns referenced in the Where clauses are indexed. Case_no should be indexed on all tables where it exists.

I recommend a couple of additional changes. Here is the complete query with the changes.

SELECT
C.case_no AS CaseNo,
C.status AS Status,
'' AS VchDate,
'' AS VchType,
'' AS VchNo,
Nature,
BaseAmt,
C.solicitor Solicitor
FROM tb_case C
INNER JOIN
(SELECT
case_no,
'O' AS Nature,
SUM(CASE dc_typ
WHEN 'D' THEN amt
WHEN 'C' THEN -1 * amt END) AS BaseAmt
FROM tb_case_o_open
GROUP BY case_no, dc_typ) D
ON C.case_no = D.case_no
UNION ALL
SELECT
C.case_no AS CaseNo,
C.status AS Status,
'' AS VchDate,
'' AS VchType,
'' AS VchNo,
Nature,
BaseAmt,
C.solicitor Solicitor
FROM tb_case C
INNER JOIN
(SELECT case_no,
'D' as Nature,
SUM(CASE dc_typ
WHEN 'D' THEN amt
WHEN 'C' THEN -1 * amt END) AS BaseAmt
FROM tb_case_d_open
GROUP BY case_no) E
ON C.case_no = E.case_no
UNION ALL
SELECT
C.case_no AS CaseNo,
C.status AS Status,
'' AS VchDate,
'' AS VchType,
'' AS VchNo,
Nature,
BaseAmt,
C.solicitor Solicitor
FROM tb_case C
INNER JOIN
(SELECT case_no,
'C' AS Nature,
SUM(CASE dc_typ
WHEN 'D' THEN amt
WHEN 'C' THEN -1 * amt END) AS BaseAmt
FROM tb_case_c_open
GROUP BY case_no, dc_typ) F
ON C.case_no = F.case_no
UNION ALL
SELECT
C.case_no AS CaseNo,
C.status AS Status,
B.vch_dte AS VchDate,
A.vch_typ AS VchType,
A.vch_no AS VchNo,
A.nature AS Nature,
-ISNULL(base_amt, 0) AS BaseAmt,
C.solicitor Solicitor
FROM tb_case C
LEFT JOIN tb_sub_gl A
ON A.case_no = C.case_no
AND dc_typ = 'C'
LEFT JOIN tb_gl_hd B
ON ((A.vch_typ = B.vch_typ
AND A.vch_no = B.vch_no
AND A.vch_seq = B.vch_seq)
WHERE ((A.bill_typ = 'PB'
OR A.bill_typ = 'CN')
AND A.bill_typ = B.vch_typ
And A.bill_no = B.vch_no))
AND in_dte > = '2002-04-01 00:00:00'
UNION ALL
SELECT
C.case_no AS CaseNo,
C.status AS Status,
B.vch_dte AS VchDate,
A.vch_typ AS VchType,
A.vch_no AS VchNo,
A.nature AS Nature,
ISNULL(base_amt, 0) AS BaseAmt,
C.solicitor Solicitor
FROM tb_case C
LEFT JOIN tb_sub_gl A
ON A.case_no = C.case_no
AND dc_typ = 'D'
LEFT JOIN tb_gl_hd B
ON ((A.vch_typ = B.vch_typ
AND A.vch_no = B.vch_no
AND A.vch_seq = B.vch_seq)
WHERE ((A.bill_typ = 'PB'
OR A.bill_typ = 'CN')
AND A.bill_typ = B.vch_typ
And A.bill_no = B.vch_no))
AND in_dte > = '2002-04-01 00:00:00'
ORDER BY
CaseNo,
VchDate,
VchType,
VchNo,
Nature
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[tonylnng]

Terry,

Thanks for your advice on SQL turning.

When I run the SQL it use 100% CPU loading on server. Does the SQL Server or Windows 2000 have any method to control the resources? Now, when the program generates report by above SQL then another users cannot run the program. ~_~'

As I know Oracle is able to assign CPU resource for each database; does SQL Server has similar thing?

Thanks again!



Tony

 

[tlbroadbent]

I know that the query is running at 100% of processor. You mentioned that. SQL Server does not provide for resource allocation by database or process.

Have you tried any of the optimization recommendations? If yes, did you note any difference in performance?

How many processors on your server? Maybe you need more processor ppower. How much memory? Too little memory can hinder performance. What other processes run on the server?

Here is another recommendation that I should have given at first. Don't use a UNION query. Create a temporary table or table variable to hold the results of each individual query. Insert the result of each query into the temp table. Then select from the temp table as the final step. You should find that this process is much less resource intensive than the current query. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[tonylnng]

Terry,

CPU: PIII (Single CPU)
RAM: 256M Ram
Hard disk: 10G

I have rebuilt all indexes and defrag all tables again but also take much time to finish. Temp table is also another good alternative for consideration.

Thanks!


Tony

 

[topher0303]

There's not a whole lot of memory on that box especially for running Win2K Server and SQL Server. When the SQL is running, check out the available memory and page faults in task manager. While virtual memory is nice on desktop to keep from running out of memory, it is extremely bad when you have to start using virtual memory on a database server. A database server should do little to no memory swapping. This causes extreme contention on your hard drives. Combine this with a poorly tuned sql statement and you have a real problem.

Chris.

 

[tlbroadbent]

I concur with Chris. You have a seriously underpowered server for SQL 2K. However, you should still be able to use it if the queries are tuned.

How many physical disk drives are on the server? I hope you don't say "1!" How much disk space remains on each drive? What is the processor speed? Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[tonylnng]

I also think it might not strong enough for such heavy workload and thanks for your advice. I will try to take a look at virtual memory on the server.

Yes, you right the server only has 1 physical disk drive.

If upgrade is another alternative we will consider it. Would you please give me your advice about server config., such as CPU, RAM and hard disk?

Thanks for your help!


Tony

 

[tlbroadbent]

Check out the SQL Server 2000 Operations Guide on the MS TechNet website. It is a valuable resource. There is a section about configuration.

http://www.microsoft.com/technet/prodtechnol/sql/maintain/operate/opsguide/default.asp

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.