?
Solved

Finding SQL Server 2012 performance bottlenecks

Posted on 2014-03-13
5
Medium Priority
?
280 Views
Last Modified: 2014-03-31
I'm a newbie to SQL Server and 2012 in particular and need help on what to do and where to look.

There is a process that run data Loads into our staging database tables for a data warehouse is taking forever. THe CPU is pegged during this time. THe process to load the development environment runs in half the time with the same volume of data.

Physical layout is as follows for development:

SQL Server database server has 16Gb of ram and 2 CPUs.

SQL Server 2012 and MS Analysis services run on one server with an application server on another box. The development app server has 16GB of ram and 1 CPU.

The staging environment has 3 servers in its configuration.

SQL Server 2012 on one server, MS Analysis services run on a second server with an application server on a third box.

The staging database server has 16GB of ram and 1 CPU. THe DAS and APP server each have 16GB of ram and 1 CPU apiece.

The way the data loads work is that there is an SSIS package that loads from flat files into staging tables.  It then runs some validation against those tables and modifies the records (removes some).  And then the staging tables are used to populate Fact and Dimension tables in the staging data warehouse database.  It is at this point that the data in the facts and dimensions are used to process the cube on the DAS server.

This process, as mentioned before, runs well in dev, but crawls in staging. The code is supposedly the same in both environments.

THe biggest differences that I know of are the three server configuration in staging, with the single CPUs on each server, versus the two server configuration and 2 CPUs on dev database server. Each server has lots of free memory during this processing.

These are all virtual servers under VMware so if I need to add another CPU, it's not a big deal.

Thanks!
0
Comment
Question by:data_bits
[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
  • 2
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39927548
Please try to disable the parallel query execution
0
 
LVL 35

Accepted Solution

by:
Bembi earned 1500 total points
ID: 39927777
So, if it is not a big deal, try to add another CPU....
The other point would be to runn a basic performance counter on the machine, which processor, Ram, disc IO and network counters to see, if you can indentify which part produces the most traffic. If you can indentify one of the the sources, you can dig a bit more into the deepness.

Also the windows resource monitor (from task manager) can give you some hints, observe the reaction while running your job.

At least from my experience, disc throughput is ofte an issue.
0
 

Author Comment

by:data_bits
ID: 39927794
Thanks, Bembi and Guy for your suggestions.

Guy- looks like query paralellism is turned off.

Bembi-I'll try to watch it while its doing the staging load next time.

THanks!
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39928625
if it's already turned off, please check the sql profiler trace to see which queries are using more CPU or Reads or Writes on the prod than on the test
0
 

Author Closing Comment

by:data_bits
ID: 39967492
Adding the extra vCPU and index helped out greatly.
0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

762 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