August 9, 2018 at 11:21 am
Hi, the statement below takes 100 min to execute, and I'm wondering if there's anything I can look at to speed that up. The table has 22 columns (5 varchar), 700 million rows.
create clustered columnstore index CCI_transaction_request_detail ON dbo.transaction_request_detail
The system is SQL Server Standard 2017, CU6. It's a new system meant for data warehousing. It's not in production yet, not much else executing on it currently.
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS Hyperthread_Ratio,
cpu_count/hyperthread_ratio AS Physical_CPU_Count,
physical_memory_kb/1024 AS Physical_Memory_in_MB
FROM sys.dm_os_sys_info
-- 8 4 2 40959
I don't think it's memory bound
select requested_memory_kb, granted_memory_kb from sys.dm_exec_query_memory_grants
-- shows 947,112 for both
sp_whoisactive has NULL in wait_info, no blocking_session_id, and at 90 min mark shows:
-- reads 28,586,385, writes 1,739,259, physical_reads 10,508,621
August 10, 2018 at 7:30 am
Slowness in the disk subsystem ? Do you have any I/O waits going on ? Are you cramped for disk space ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 14, 2018 at 2:18 pm
Not cramped for space, got 500GB free still. I'm running the create columnstore index command again now, and ran the query below to look at waits
select * from sys.dm_exec_session_wait_stats
where session_id = 65
order by wait_time_ms DESC
Here's the top 5
wait_type waiting_tasks_count wait_time_ms
RESERVED_MEMORY_ALLOCATION_EXT 204789 14494
SOS_SCHEDULER_YIELD 259159 12159
MEMORY_ALLOCATION_EXT 3764335 3440
PAGEIOLATCH_SH 192 963
PAGEIOLATCH_EX 233 136
Not sure how to interpret that. Let me know if there's anything else I should look at, appreciate the help!
August 14, 2018 at 8:20 pm
And after 6 hours and 18 columnstore indexes created, top 5 waits are:
wait_type waiting_tasks_count wait_time_ms
PAGEIOLATCH_SH 1641491 266954
RESERVED_MEMORY_ALLOCATION_EXT 3814428 259338
SOS_SCHEDULER_YIELD 3650643 191846
MEMORY_ALLOCATION_EXT 65909154 62201
IO_COMPLETION 45868 2028
August 15, 2018 at 6:47 am
Divide 40 GB by 722,000,000 and you'll find the result is roughly just shy of 60. So your table can't fit in RAM unless those 22 columns average less than 3 bytes of storage each, and that seems highly unlikely. I'm not saying that you have to be able to fit your table in memory to get things to work, but if you are going to create a data warehouse, just 40 GB seems seriously under-sized, and especially with just 2 processors (not sure on core count, but with hyper-threading ratio at 4, they may both be dual core, which might mean you have 4 cores). I'd be beefing up that box on RAM considerably, based on what it may be expected to do as a data warehouse, moreso than just for the columnstore indexes... As your top waits do seem to involve memory allocation, I can't say you're not memory bound from the point of view of creating columnstore indexes.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply