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)
12 Comments. Leave new
Developers ;-)
Users.
People in general.
DFW or O’Hare.
I Think
95% Solution Design
2% RAM
2% Disk
1% CPU
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. …
You forgot one more thing thst is monitor and Printer
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!
Pinal,
You missed a SQL Server specific bottleneck candidate:
TempDB
Regards,
Sakthi
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.
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,
Instant File Initialization
TempDB
Server / Virtualization Settings
and
The Unexpected lol
Main board bus speed.
tempdb…