asked on

sql 2104 select into temporary table

Hi all,
I've started to have problems with our sql server with our routine query, the query actually run to select around 100-200k of records which its getting sluggish now as our data started to piled up to 7 millions or records, I spot the slowness where the query was not moving when it already collected the records in the temporary table via select * into ##__TEMP_DATA_TABLE.

My questions here is how do I know if this slowness could due to the memory shortage factor ? does this query really saved the records in memory instead of tempb disk ?

Our sql serevr running in sql 2014 32 GB of memory with 16 CPU

SOLUTION

chaau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

chaau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

motioneye

ASKER

Hi actually why I'm using select * into ##__TEMP_DATA_TABLE simply because only 5 columns return by this query , the hardest part with this query actually it has around 10 temporary tables like ##__TEMP_DATA_TABLE_1 and subsequently, so the data from 1st temporary table remain in there until we completed all 10 executions within 10 temporary tables before we end to final results, so means ##__TEMP_DATA_TABLE will be use to join with ##__TEMP_DATA_TABLE_1 and put the output in ##__TEMP_DATA_TABLE_2 then this ##__TEMP_DATA_TABLE_2 will be use to join with ##__TEMP_DATA_TABLE and ##__TEMP_DATA_TABLE_1 and ##__TEMP_DATA_TABLE_2 to get the result for ##__TEMP_DATA_TABLE_4

we don't have problems with this query except that when it goes to select millions of records stored in temporary tables the problems start to begin.

I look into memory and how it was use, I found that when I ran this query up to step 5, the query start begin to suspended and size of memory in MEMORYCLERK_SQLQERESERVATIONS = 5.5GB in size and MEMORYCLERK_SQLBUFFERPOOL=23 GB , so I ponder that might adding extra RAM might help but I'm not certain.

Vitor Montalvão

How big is the database that you're working on?
How big is the tempdb database?
Where are the tempdb files stored? Do you have enough disk space in that location?

Pawan Kumar

Can you please provide the entire query ? Do you have indexes in your temp tables?

Scott Pletcher

Probably the whole logic needs re-worked.

Since you've provided only a general description, I can only give a general answer. Cluster the temp tables to match the joins, particularly if the data going into the temp table is already sorted by that column(s).