SQL Server Performance

sql 2012 data warehouse and SSIS

Discussion in 'ALL SQL SERVER QUESTIONS' started by timscronin, Aug 29, 2013.

  1. timscronin New Member

    I have a new 64 core 64 gig super server to run SQL Server Enterprise Edition. It will run OLAP and Reporting Services. it will also have a fair amount of SSIS staging loads. I can expand the current servers memory. What I was thinking should I create a second instance on this box to run the SSIS loads to eliminate contention with the OLAP and SSRS function of the server?
  2. davidfarr Member

    Any performance contention that you do get is unlikely to be on either the memory or CPU power. In my opinion; the quality and speed of your disk array, and the arrangement of your files and filegroups on those disks, is where most database hardware performance is really felt. Have you recently spent time monitoring actual CPU usage and memory usage on your other servers ? It's probably using less than you suspect.

    I am frequently puzzled by DBAs who throw their server budget into RAM and CPU power while not even mentioning their disk storage system.
    No matter how great the CPU cores and RAM is and how many instances are running, all of those still rely on data that is read from, or written to, a disk storage system. A good external SAN, RAID 10 or RAID 50, SAS disks, SSD disks or even a large RAMSAN drive if you can afford it. That's where the bulk of my budget would be.

    If you install 2 or even 3 instances, but all your database files from all instances are together on the same common set of onboard disk arrays, then I expect your performance gain from multiple instances to be pretty much zero.
    Multiple instances on the same server, in my opinion, is more suited to cluster scenarios or circumstances where separate instance administration or separate instance security is required.
  3. Shehap MVP, MCTS, MCITP SQL Server

    I am pursing you to think about your storage performance just like Davidfarr has said but also I am urging you to isolate your SSRS (Reporting service) and SSDBS (Database service) rather than SSAS (Analysis Service) in 2 different SQL Failover clusters based on the same nodes or at a least 2 different standalone servers if no SAN storage is exists there, this in turn will add more CPU and memory resources for each service and thus you can specify more resources for each service

    Please let me know if any else info are needed for boosting your DB performance

Share This Page