Blog - comments

When I studied Oracle New Feature Guide "Media Failure: PDB SYSTEM Data File" , I was surprised tha...
Hayat Khan

Really a nice article to study.

Thanks,

Amol Bhoite

ana 100 goid

mokok

ana 100 gold

mokok

Bonjour,

Tout d'abord merci pour cet article. J'aimerai savoir si ACFS est gratuit ?

Chris

Chris
Blog Antonio De Santo Three basic points on SQL Server Best Practice

dbi services Blog

Welcome to the dbi services Blog! This blog focuses on IT infrastructure - featuring news, troubleshooting, and tips & tricks. It covers database, middleware, and OS technologies such as Oracle, Microsoft SQL Server, Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on the postings!

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

Three basic points on SQL Server Best Practice

A few weeks ago, I went to the Swiss Database Days 2013 and followed the "Practical SQL Server Performance Troubleshooting" workshop. During this workshop, I have seen a different way of troubleshooting, but I was surprised to see that some participants were not aware that some basic SQL Server Best Practice do not depend on the instance settings. This blog posting is a reminder of some of these points.

 

Swiss Database Days 2013: SQL Server Best Practice

As I said, I was suprised to see that some workshop participants were not aware of (or did not pay attention to) the fact that some basic SQL Server Best Practice do not depend on the instance settings:

  1. The power options of Windows Server
  2. NUMA settings
  3. Disks layout and configuration

 

Windows Server power options

Let's start with the Windows Server "power options". In the "Control Panel" under the "Power Options", you can change the settings to reduce the energy consuption.

I do understand that for laptop or desktop computers (using Windows 7 or 8) some energy saving options should be actived.

But I don't understand why Microsoft sets the "balanced" power plan for Windows Server per default, as this is a OS that will be used on servers.

The setting "Balanced" will reduce the performance and reaction of the server. In the worst case, people will buy a bigger server with more energy consuption to get more performance. So the recommandation is to put it on "high performance".

 

Non-Uniform Memory Access (NUMA)

My second point is NUMA. As the number of processors is increasing, it becomes more difficult to install a parallel access to the (shared) memory of all these additional processors.

NUMA tries to reduce this problem by dividing memory in different parts and giving a restricted access to groups of processors (Nodes). As memory is splitted, the number of parallel access will be increased and will reduce the number of conflicts accessing the memory. Memory allocated to another group of processesors can still be accessed, but this is done through the memory bus of the other processor group and will be slower.

I will not go into details here, if you want to know more about it, I have added some links below. What is important here is to make sure that the server hardware is used in the best way if it is NUMA compatible, so that it gives the best performances.

To verify the configuration, you can use the "coreinfo.exe" tool.
This can be downloaded here: http://technet.microsoft.com/en-us/sysinternals/cc835722.aspx

 

Disks layout and configuration

And now the last point: the disks. This is a very important point, but many people think that SANs or other disk systems have resolved this problem and that it does not need to be considered anymore.

Unfortunately, this is wrong! You should always take in consideration "why" the disk is used and "how". The responses to these questions will give you an answer of what can be used and also what cannot be used!

Then, depending on price and (needed) performance, the final choiCe can be made. Follow this link to have the different possible RAID configurations depending on needed performance and the amount of disks (price): http://blogs.technet.com/b/sql_server_isv/archive/2010/11/11/sql-server-drive-configurations.aspx

If you need more performance, you can also replace the RAID1 or RAID5 with RAID10 disks.

You also need to pay attention to the alignement of the MasterBoot sectors. If your disks have been formated in NTFS with Windows Server 2008 or higher, this is automatically done. If your disks were formated with older Windows versions, please check it with the following command in "cmd" and if needed align them.

  • wmic.exe partition get BlockSize, startingOffset, Name, Index

To align them you need to reformate them, which means that you will loose everthing that is on the drive. Move your files somewhere else or make a backup of them, before formating the drives.

As I have stated before, these are only three basic points of SQL Server Best Practice that you should consider before any installation.

Here are some links with background information:

 

NUMA

http://en.wikipedia.org/wiki/Non-Uniform_Memory_Access
http://msdn.microsoft.com/en-us/library/ms178144%28v=sql.105%29.aspx

http://technet.microsoft.com/en-us/sysinternals/cc835722.aspx


Disks

http://technet.microsoft.com/en-us/library/cc966412.aspx
http://technet.microsoft.com/en-us/library/cc966534.aspx

http://blogs.technet.com/b/sql_server_isv/archive/2010/11/11/sql-server-drive-configurations.aspx

http://msdn.microsoft.com/en-us/library/dd758814%28v=sql.100%29.aspx

Rate this blog entry:
1

Antonio De Santo is Consultant at dbi services. He has more than fifteen years experience in database administration, engineering, and optimization. He is specialized in Oracle database and Microsoft SQL Server technologies such as installation, migration, best practices, backup & recovery, and performance analysis. Antonio de Santo is Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server implementation and maintenance. Prior to joining dbi services, Antonio De Santo was Consultant at Trivadis in Basel. Antonio De Santo holds an engineering diploma from the Biel School of Engineering in Switzerland. His branch-related experience covers Financial Services / Banking, Chemicals & Pharmaceuticals, Manufacturing, Energy, Automotive, etc.

Comments

  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest Saturday, 19 April 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)
NewsOfficesContact

Contact

Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter