?
Solved

Performance issue when inserting into temporary # table

Posted on 2010-08-27
6
Medium Priority
?
802 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
 

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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1500 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

777 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