Finding SQL Server 2012 performance bottlenecks
Posted on 2014-03-13
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.