Close Menu

SQL-SERVER

 


Capacity Planning


Before You Proceed We recommend you to read our Disclaimer below

Please test the script on your testing or Dev (development) environment's which are expendable before executing or implementing these scripts over production or realtime environment. Also get the Aprovalfrom your DBA before executing these scripts

The information contained in this website is for general information, Educational and Practice purposes only. The information is provided by .Raghuveera.com and while we endeavour to keep the information up to date and correct, we make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose. Any reliance you place on such information is therefore strictly at your own risk.

In no event will we be liable for any loss or damage including without limitation, indirect or consequential loss or damage, or any loss or damage whatsoever arising from loss of data or profits arising out of, or in connection with, the use of this website.

Through this website you are able to link to other websites which are not under the control of [business name]. We have no control over the nature, content and availability of those sites. The inclusion of any links does not necessarily imply a recommendation or endorse the views expressed within them.

Every effort is made to keep the website up and running smoothly. However, [business name] takes no responsibility for, and will not be liable for, the website being temporarily unavailable due to technical issues beyond our control.

What is Capacity Planning.

When a active RDBMS system is taken into consideration, Let the RDBMS system be SQL-Server, Oracle, DB2, Sybase, MY-SQL, or could be anything. None of these systems are automated in such a manner and they consume system resources without any control if not configured properly. Because of this improper configuration our system resources (server resources on which the RDMBS System is installed) like

  1. Storage
  2. CPU
  3. Memory
  4. Disk I/O
  5. Network
will be Impacted big time either because of over utilization or under utilization.

Let us Discuss Capacity Planning In detail

Storage :-

Data accumulates fast so Planning your diskspace depending on the type transactions (inserts) and amount of data being loaded into the tables makes us decide the amount of space to be allocated for each MDF, LDF, Log File.

Hint:-If we are capacity planning for moving an old database to a new server The script for Server and Database can help us plan the new system if applicable
Placing mdf, ndf, ldf and.bak on C drive:-
This could result in overall server running slow if OS crashes or gets corrupted. Entire C-Drive sometimes requires formatting and if the data files are placed in C drive that could pose a bigger threat if on production server
DB Growth Monitoring Sometimes There could be a situation where there is an unexpected level of DB growth. To combat situations like these we need to define a baseline on the server and constantly keep monitoring the situation automatically by comparing the current DB Status with the baseline by the help of Instance Performance Analyzing Scripts.

CPU

CPU plays a major role in the heath of your server. Its always recommended to have a 30% buffer than your expected CPU utilization. However practical reality is different and the load on a Realtime running server keeps varying with respect to time. The situation can be very different on different environments.
Cloud Environment: If your hardware is provided by a cloud provider let’s say Microsoft Azure the flexibility is that we can easily upgrade to another hardware solution If the DBA thinks that the current CPU utilization is high and an upgrade to a CPU with higher processing speed is needed. Which can be easily migrated.
Non-Cloud Environments: The situation becomes very complicated when the server is on a physical device located in our server room. In situations like these a server upgrade might be required which needs approvals from different departments which can become very time consuming and complicated and may impact the project overall.
So depending on your situation your hardware needs to be defined accordingly. I would recommend to be safe than being sorry.
Some situations of high CPU Utilization may also demand analyzing the query or indexes. Generally, one bad query can become a bottleneck which is causing heavy CPU Utilization. So whenever there is a slowness in the applications it is advisable to take a look at the longest running queries or high CPU utilization queries by looking at the cached plan’s. Indexes also need to be re-organized and rebuilt periodically during maintenance hours.

Memory

General conception of many windows users is that the higher the RAM the better the performance which is partially “TRUE”, however when it comes to SQL-server being installed on a machine the situations becomes entirely different. You could be completely wrong at Times if you think that you may need a higher Capacity RAM. Before you come to decision that you need more Ram I would recommend you to check your SQL-Server memory Configuration I would also recommend you to try running this script if you are planning to move to a new server.
Just Like any other system Resource memory also needs to be planned with a 30% buffer for situations like “Just in case” .As always better be safe than Sorry

 

Disk I/O Capacity

:- Prior to introduction of 'IN-Memory' feature in SQL server 2014 Data is primarily located on a physical hard drives where data is constantly modified by logical changes like logical reads and logical writes.The irony of the physical Hard drives system is that when one data request is issued to the hard drive the request has to wait in queue for a prior datarequest to complere The request could be a read or a write also can be called as I/O (input/output). So there is wait which is being created because of this sytem on a physical hard drive. Another lag to this system is that the hard disk has to rotate to the right spot on the disk where the data is located.This wait for rotation to take place also plays a major role in the perfomance of the SQL-Server. Disk size also plays a major role the larger the distribution of data over a physical hard disk the longer it takes for the data to be fetched.

How do i plan to counter I/O bottlenecks?

The best way to make your system a better one is by designing the application to be optimal with I/O at the very initial stages of the design.It may become a verycostly approach if at all a redesign is required.

Design: Design has to be done imlementing the highest form of normalization applicable to the system so that we have least possible data sitting on the server.Mainataing indexes (rebuilding and reorganizing) reduces the scan when a where statement is used hence lessr read's on the physical hard drive. Design teams has to consider the fact that if there is specific data overwhich moslty only read's (SELECT Statements) happen then that data has to be acrchived into a datawarehouse. The design has to be in such a way where the heavy I/O queries are fetching data from Cache primarily than from the physical drive.

Monitoring:- There arises a need for constant monitoring of key CPU metrics, which include I/O Wait % and associate that with your servers requests. and optimise the database constantly when and where required.Monitoring helps us identify bottlenecks caused by I/O.

Network

we need to make sure That the ports are configured properly even if we have to change the default port of SQL-Server.Care should be taken that the network which the sqlserv is located has the reqired bandwith and no interruptions because of the network.

Limiting Resource Utilization:

Resource utilization can be controlled by a sql server inbuilt tool called Resource governer. Resource governer is present from 2008 versions of sqlserver. Initialy we need to create a resource pool over an instance defining max and min IO.Then create a workoload groupUsing That resourcepool which we just created.Now we have to create a classifier function for the database which we need to restrict or controll . Now we have to save associate the classifier function to the Resource governor. I will be writing a seperate article for resource governor

Performance monitoring tools: -
Perfmon: Perfmon is a great tool inbuilt into windows based OS systems which can be used for monitoring the CPU utilization. Perfmon can be utilized for setting up traces, configuring or defining counters and start monitoring by generating periodic reports or Ad-hoc reports
Taskmanager : if you are a windows user you should definitely have used task manager at least once. open task manager and navigate to the performance tab you can look at the CPU utilization history or usage history. In this tab, the usage is defined according to the core.
Resourcemon:-resourcemon is a great tool which will show us a clear picture of how effectively what resource is being utilized. you can check perfomance by running queries and the effective resource utilization