Solved

Performance issue when inserting into temporary # table

Posted on 2010-08-27
6
791 Views
Last Modified: 2013-11-15
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
Comment
Question by:BlueArgonaut
6 Comments
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33540699
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
 
LVL 7

Expert Comment

by:rashmi_vaghela
ID: 33540707
0
 

Author Comment

by:BlueArgonaut
ID: 33540780
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 33541163
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33541169
0
 

Author Closing Comment

by:BlueArgonaut
ID: 33542034
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question