SQL SERVER – Potential Bottlenecks for Performance

In recent GIDS presentation, I was asked can I name potential bottlenecks for performance. I was taken back to my collage life with this question. I remember that I have memorized following names as potential bottlenecks.

  • CPU
  • RAM
  • Hard Disk
  • Network
  • Application Code

Today when I look back at this, I still think the same reference is correct. It seems very interesting that technology has really moved ahead but the essence and basics of  any subject are still same.

Can you think of any other kind of bottleneck, which is not subset of above five topics which I have mentioned.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Prevent Constraint to Allow NULL
Next Post
SQL SERVER – How to Use Decode in SQL Server?

Related Posts

No results found.

12 Comments. Leave new

  • Allen McGuire
    April 22, 2011 7:45 am

    Developers ;-)

    Reply
  • Users.

    People in general.

    DFW or O’Hare.

    Reply
  • I Think

    95% Solution Design

    2% RAM

    2% Disk

    1% CPU

    Reply
  • Here is my classification, based on experience: (ordered by lost dangerous items on top)

    1. Lack of a DBA
    2. DBA with lack of understanding of SQL Server concepts
    3. Lack of communication between DBA and Developers
    4. Poor security – a server with 30 sysadmins eager to do ‘work’
    5. poorly configured storage
    6. Memory
    7. CPU
    8. …

    Reply
  • You forgot one more thing thst is monitor and Printer

    Reply
  • Allen McGuire
    April 22, 2011 5:54 pm

    In all seriousness, while humans are a known bottleneck, I think Pinal is looking for hardware/configuration items that may lead to performance issues. Configuration is certainly another one that can affect your DBMS. Examples include:

    1) MAXDOP configuration – if your system is largely an OLTP one, the default of 0 may be better suited to be set to 2 or even 1.
    2) Cost Threshold for Parallelism – similar to #1, you can adjust this to set the bar for which queries qualify for parallelism.
    3) If your developers write a lot of raw SQL, that’s unfortunate, but your system may benefit by setting the “Optimize for Ad Hoc Workloads” setting to True.
    4) Lack of tuning. It only takes one bad query to take an entire system to its knees. Run traces periodically and look for statements that take more than 12800 8K page reads (100GB) – tune them. Repeat as necessary.
    5) Recovery model and maintenance tasks – make sure your backup plan matches the needs of the business and doesn’t impede on system performance. Doing transaction log backups on a huge, busy database will likely cause issues, as will doing full-backups. Define a maintenance window if you can and ensure all outside activity respects that window.
    6) Basic configurations – make sure you configure your DBMS to use the memory it will need. I typically leave 2-3GB to “everything else” and set the max memory to (total-2GB). Don’t let the system surprise you – you tell it what it can use.

    Use security to manage who can do what – don’t rely on just telling them. Developers often have an urge to run queries against production data to see what’s going on, but often times they end up contending with normal operations. Minimize that.

    Hope that helps – if I think of more I’ll share!

    Reply
  • Pinal,

    You missed a SQL Server specific bottleneck candidate:
    TempDB

    Regards,
    Sakthi

    Reply
  • Hi Pinal,
    In addition to the list you have, I would like to mention about the kind of environments one sets up for testing performance. In order to get a good picture of performance, the test and prod environments should mimic each other atleast in terms of the data volume.

    Reply
  • Hi Pinal,
    I would like to add the following to your list:
    1. processor and processor architecture 32vs 64 bit..
    2. SQL Server Edition, O/S Edition,

    Reply
  • Instant File Initialization
    TempDB
    Server / Virtualization Settings

    and
    The Unexpected lol

    Reply
  • Main board bus speed.

    Reply
  • tempdb…

    Reply

Leave a Reply

Menu