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 (http://blog.SQLAuthority.com)

About these ads

12 thoughts on “SQL SERVER – Potential Bottlenecks for Performance

  1. 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. …

  2. 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!

  3. 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.

  4. 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,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s