Description
Hardware issues are the most common issues that encounters with the SQL Server. And describe how to properly configure the hardware for the best performance and scalability, power management in terms of performance and scalability and discuss a lot about processor selection for SQL Server because it's really important for both the licensing purposes and for overall performance and scalability in a particular machine.
Description
The most common hardware issues that occurs in the SQL Server are:
- Using older server with older achievements, slower processors and lower PCLe slots performance.
- Using database servers where someone made a bad process or choice.
- Using a four sockets database servers where a two sockets servers would be a better choice.
- Having a database server without sufficient RAM.
- Using inappropriate BIOS settings for the workloads.
Conceptual
figure
- Node - Is a communication networks.
- SQL Server Service Stopped - Is a command for stopping the sqlservr.exe.
- SQL Server Service Running - Is a command for running the sqlservr.exe.
- Database - Is a logical collection of related data.
- Virtual Node - Is a virtual machine running runner.
Description
In key BIOS settings the turbo boost or turbo core should always be enabled, where absolutely no performance or longevity downside from enabling. Hyper threading should be enabled in most situations and that give more capacity for OLTP workload that hurts the performance for some DW workloads. And disable HT with the virtualization and if HT is enabled on the host machine, VCPUs will use logical CPU cores.
Description
Power management can be done at the hardware level and at the hyper visor level or software level in windows and all of these can have a noticeable negative effect on the server performance especially for the database servers. Now power management is a good idea for laptops it makes the battery last longer and reduce its electric bill. The small amount of power that doesn't used by the power management.
And there are many key difference that enabled and disable the power management and reduce the processor clock speed of the processor and also reduces the PCIe slot voltage which effect on the storage subsystems. And so the three levels that run into this hardware level and UEFI BIOS. Power management run into a hypervisor level and hypervisor such as VMware or HyperV, and also at the windows level with the windows server power plan. And the latest generation processors tends to handles power management more efficiently.
Description
PCIe slot throughput limits depends upon how old the server might have in PCIe version 1, version 2, or version 3. And these different versions of PCIe have different throughput limits.
These PCIe 1.0 is old version server that contains x4 slot with a time frame with size of 750MB/Sec which might decide to plug into that slot that is severely constrained by that limit. For example, a RAID controller or a PCIs add-in card that is used for flash storage, or even a high-speed network interface card that can perform on 10GB or 40GB/Sec that as a severely limited by that x4 slot.
The PCIc 2.0 version which as roughly double the bandwidth for an x4 or an x8 slot. But even these, which are going to be found in the previous generation servers that as well limited for some of the higher end things that should be plug into that slot.
The PCIc 3.0 can be performed on intel xeon E5's and intel xeon E7 or with newer processors which has these kind of processors then it will double the bandwidth of 2.0 and contains the highest end that going to plug into those slots such as flash storage cards and 100GB Ethernet cards with the things of that nature and not throttled by the slot itself.
- PCIe 1.0 Bus(one-way)
- PCIe 2.0 Bus(one-way)
- PCIe 3.0 Bus(one-way)
Description
For deciding the processor for choosing a new SQL Server implementation. The processor specification details has to be used for modern intel processors by paying a very close attention to the detailed specifications of the processors, and these processor specifications are from the intel ARK database. And focus on maximizing the single threaded processor performance for giving the core count processor that contain two or three different skews in that product line.
The Microsoft doesn't care whether picking up a slow processor or a fast processor,and they just care about the core count when it come to SQL Server licensing for the performance of each and every SQL Server core. The physical core counts are really important because they drive costs of the licensing by read the following links and choose a processor for SQL Server.
Key Points
- Hardware Issues - Are the most common type of issues that encounter with the SQL Server.
- Common hardware issues - Are the issues that occurs in the SQL Server.
- Key BIOS Settings - Which will enable the turbo boost or turbo cores.
- Power Management - Performed on the hardware level and hyper visor level.
- PCIe throughput limit - Contain different version versions of servers.