Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 813
  • Last Modified:

Performance issue when inserting into temporary # table

Hello guys,

Do you have a clue, what can be the root cause for low performance when inserting into temporary #table. Please see the attached picture from PerfMonitor. The SQL run in virtual Hyper-V server. The tempdb is on local harddisk.
The physical storage on physical server is RAID10.

DB server: Microsoft SQL Server 2008 (SP1) - 10.0.2766.0 (X64)   Feb 25 2010 12:51:37   Copyright (c) 1988-2008 Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1) (VM)

Thanks Performance Monitor
0
BlueArgonaut
Asked:
BlueArgonaut
1 Solution
 
Mohit VijayCommented:
Try to use temp (@) table variable instead of temp (#) tables.
also dont use order by clause on select query that is inserting data into temp table.

Also if you can put your SQL query statements, that will also help us to narrow down the problem.
0
 
BlueArgonautAuthor Commented:
I use ms sql 2008.
@ tables can't be used because the table is quite big. The query is part of ETL load and is currently necessary for load and avoidable.

I just wonder what can the root cause on system - disks/ processor/memory I'll change the query accordingly the root cause.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Kevin CrossChief Technology OfficerCommented:
Can be all three. If your tempdb has to grow to accommodate the data being loaded you are now using I/O. If the result set is large, especially if you are doing an order by, you will utilize memory heavily as the steps needed to prepare the results all will utilize memory ... if you don't have enough physical memory, then pagefile will be hit which now is doing additional I/O which also might make the CPU cycle up for the duration of the query ...

With enough resources, the CPU will spike, but the release is so instantaneous it is not an issue ... but what happens when the query is long running is that this high processor level may need to be maintained.

Hopefully that helps.
0
 
Kevin CrossChief Technology OfficerCommented:
0
 
BlueArgonautAuthor Commented:
I think that recommendation is that I should follow to investigate the exact root cause of low perfomance. Thanks for that hint.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now