Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Query seems to be constrained by single core CPU in multiple core server - any suggestions? Expand / Collapse
Author
Message
Posted Wednesday, May 8, 2013 3:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 10, 2013 8:39 AM
Points: 47, Visits: 227
I receive data from an external data provider which comes in as a single large table. The data represents balance sheet information for several different companies.

The table structure boils down to 3 columns:

1. PK Company identifier (each different company has a different id number)
2. PK Field id number (each item on the balance sheet has a different id number, so total assets might be '15', total liabilities '23' etc.)
3. The data itself

For my OLAP system, I want to populate a table that contains a single row for each company. This table will be viewed regularly by users. The columns in the table would hold all the different balance sheet items for each company.

The SELECT statement I used for inserting data from the large source table to my destination table joins up on itself using the different field numbers as follows:

SELECT
MySelectList
FROM SourceTable a
LEFT JOIN SourceTable a1
ON
a.FieldIDNumber = '1'
AND a.CompanyIdentifier = a1.CompanyIdentifier
AND a1.FieldIDNumber = '2'
LEFT JOIN SourceTable a2
ON
a.CompanyIdentifier = a2.CompanyIdentifier
AND a2.FieldIDNumber = '3'
LEFT JOIN SourceTable a3
ON
a.CompanyIdentifier = a3.CompanyIdentifier
AND a3.FieldIDNumber = '4'
LEFT JOIN SourceTable a4
ON
a.CompanyIdentifier = a4.CompanyIdentifier
AND a4.FieldIDNumber = '5'


This select statement takes a very long time to run. I tried to figure out what the bottleneck is and I think I'm CPU constrained for the following reasons:

1. Pattern of system resource use while query is running:
At the very beginning of the query execution, I see heavy reads on the database hard disks as the source table is loaded into memory, which I expected. Then for the vast majority of the time the query is running, hard disk read/write goes to zero and a single core on the server is 100% utilized. There is an occasional brief read of the database hard disk, maybe once every 20 minutes or so for about 2 or 3 minutes. The utilization of the remaining 5 cores is close to 0. Note that I have max degree of parallelism set to zero so all the 6 cores should be available to SQL server.

Previously on the same machine, I had hyper-threading enabled. Back then when I ran the same query, only 1 of the 12 threads was fully utilized. So CPU utilization went up when I disabled hyper-threading, but only until the point where 1 core was 100% utilized.



2. If I'm reading diagnostic checks from Glenn Berry correctly (and my correct reading isn't a given because I'm self-taught in all things programming), I don't think I'm memory constrained:
When I run this code:
-- SQL Server Process Address space info 
--(shows whether locked pages is enabled, among other things)
SELECT physical_memory_in_use_kb,locked_page_allocations_kb,
page_fault_count, memory_utilization_percentage,
available_commit_limit_kb, process_physical_memory_low,
process_virtual_memory_low
FROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE);

The result is 0 for process_physical_memory_low and 0 for process_virtual_memory_low.

When I run this code:
SELECT total_physical_memory_kb, available_physical_memory_kb, 
total_page_file_kb, available_page_file_kb,
system_memory_state_desc
FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);

system_memory_state_desc says 'Available physical memory is high'

When I look at the top wait types for the server, I see that the top wait type for the server is CX_PACKET, which is what the server is waiting on over 99% of the time.


I would be grateful if anyone had suggestions on how to improve the speed of execution, or could answer the following related questions:

1. My primary key on the source table includes both the company Id and the field ID number. Each of the joined tables has a different field ID number. Would the query run faster if I placed a non-clustered index on the field ID number, despite the fact that it is part of the primary key?

2. Would the following result in higher CPU utilization?
-- Use one statement to join tables a1 and a2 to table a into a temporary table.
-- Use a second statement running concurrently to join tables a3 and a4 to table a into a second temporary table. Perhaps this would utilize a different core to the first statement?
-- Use a third statement once the first two have finished to join both temporary tables together and insert into my destination table.

Post #1450466
Posted Wednesday, May 8, 2013 3:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 3:45 AM
Points: 2,873, Visits: 5,188
First of all your check for a.FieldIDNumber = '1' should better be moved into WHERE clause:
SELECT  
MySelectList
FROM SourceTable a
LEFT JOIN SourceTable a1
ON a.CompanyIdentifier = a1.CompanyIdentifier
AND a1.FieldIDNumber = '2'
LEFT JOIN SourceTable a2
ON
a.CompanyIdentifier = a2.CompanyIdentifier
AND a2.FieldIDNumber = '3'
LEFT JOIN SourceTable a3
ON
a.CompanyIdentifier = a3.CompanyIdentifier
AND a3.FieldIDNumber = '4'
LEFT JOIN SourceTable a4
ON
a.CompanyIdentifier = a4.CompanyIdentifier
AND a4.FieldIDNumber = '5'
WHERE a.FieldIDNumber = '1'

Looks like it is banking client data you are working with. Is your FieldIDNumber defined as varchar or int? If it's a numeric, then don't use quotes to avoid unnecessary data type conversion.
And the last one about your CXPACKET waits. That shows that you have problem with parallelism. Try to add OPTION (MAXDOP 1) after WHERE clause and see if it makes it run faster.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1450479
Posted Wednesday, May 8, 2013 4:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 10, 2013 8:39 AM
Points: 47, Visits: 227
Thanks for the suggestions Eugene.

First of all your check for a.FieldIDNumber = '1' should better be moved into WHERE clause

Will try, many thanks


Is your FieldIDNumber defined as varchar or int? If it's a numeric, then don't use quotes to avoid unnecessary data type conversion.


It is not defined as numeric but it should be! Looking back, I see that I had originally set it as varchar because the documentation for the data source said it was alphanumeric. However after a few years of operation, there is only numeric data in that column, so I think that was a typo in the documentation. I'll change the column to numeric and remove the quotes to speed things up. It'll take a while to do so it will be a while before I've tested your other suggestions.

And the last one about your CXPACKET waits. That shows that you have problem with parallelism. Try to add OPTION (MAXDOP 1) after WHERE clause and see if it makes it run faster.

I'll try that, many thanks. I have a feeling though that the 1 core used will still be at 100% utilization, soon to find out.
Post #1450491
Posted Wednesday, May 8, 2013 5:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:38 AM
Points: 6,861, Visits: 14,160
Can you post the actual execution plan as a .sqlplan attachment please? Makes this kind of exercise much easier.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1450497
Posted Wednesday, May 8, 2013 5:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 10, 2013 8:39 AM
Points: 47, Visits: 227
ChrisM@Work (5/8/2013)
Can you post the actual execution plan as a .sqlplan attachment please? Makes this kind of exercise much easier.


This query was so long-running (over 2 days) that I haven't actually seen it complete yet so I just have the estimated, not actual execution plan as of now. After implementing Eugene's suggestions I'll post up the actual if the query actually ever finishes for me...

In the meantime, here is the estimated plan. The table names are proprietary so I'm posting the execution plan as a jpeg with the names blocked out, does that suffice? All that's blocked out is the table name which I've callled 'SourceTable' above. That is the table that joins on itself several times.

--EDIT 2013 05 09 - removed screendump of plan I had posted here because I posted the full query plan as an attachment here: http://www.sqlservercentral.com/Forums/Attachment13640.aspx
Post #1450503
Posted Wednesday, May 8, 2013 5:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 3:45 AM
Points: 2,873, Visits: 5,188
caspersql (5/8/2013)
ChrisM@Work (5/8/2013)
Can you post the actual execution plan as a .sqlplan attachment please? Makes this kind of exercise much easier.


This query was so long-running (over 2 days) that I haven't actually seen it complete yet so I just have the estimated, not actual execution plan as of now. After implementing Eugene's suggestions I'll post up the actual if the query actually ever finishes for me...

In the meantime, here is the estimated plan. The table names are proprietary so I'm posting the execution plan as a jpeg with the names blocked out, does that suffice? All that's blocked out is the table name which I've callled 'SourceTable' above. That is the table that joins on itself several times.



Note that my example code above was simplified somewhat. There is a coalesce, group by and order by in the actual statement.


Unfortunately, I cannot see anything attached. Anyway, JPG is not very good idea for posting query plan. You better save it as XML. You need to post complete query if you want relevant help. Also, posting DDL of involved database objects would be helpful.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1450508
Posted Wednesday, May 8, 2013 5:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:26 AM
Points: 14,000, Visits: 28,380
Without the plan itself (saved & posted as XML as the previous poster said) it's hard to make serious suggestions to you. But, the one thing I can tell you, when you see a pattern with really fat data pipes on the right and really skinny ones on the left, you have a problem. It's probably missing or incorrect indexes or, possibly, out of date statistics. But without the details of information within the plan, that's just guesses.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1450510
Posted Wednesday, May 8, 2013 5:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 10, 2013 8:39 AM
Points: 47, Visits: 227
Unfortunately, I cannot see anything attached. Anyway, JPG is not very good idea for posting query plan. You better save it as XML. You need to post complete query if you want relevant help. Also, posting DDL of involved database objects would be helpful.


Okay will do thanks, the database is currently changing the datatype of the Field ID Number to numeric so I'll post it up when that's finished, but it might take a while to complete.

Edit: full query plan uploaded here: http://www.sqlservercentral.com/Forums/Attachment13640.aspx
Post #1450514
Posted Wednesday, May 8, 2013 6:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:38 AM
Points: 6,861, Visits: 14,160
Can you post the query with obfuscated table names?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1450524
Posted Wednesday, May 8, 2013 6:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 10, 2013 8:39 AM
Points: 47, Visits: 227

Can you post the query with obfuscated table names?


Yes will do after I've rewritten it slightly to factor in the datatype change for the field ID number. When that is done I will post it with obfuscated table names. It could take a while because 'SourceTable' is very large so the datatype change will take a while.
Post #1450532
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse