Solved

Performance issue when inserting into temporary # table

Posted on 2010-08-27
6
794 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 60

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 60

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

734 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