Skip to main content

Windows Operating System

To learn SQL Server the first most important thing is to learn about Windows Operating System. Here is a basic overview of what you should know about windows to learn SQL Server.

Client and server:

First of all we need to understand what are Clients and Servers. As the name suggests, clients are meant for requesting services and servers for providing services. It is not necessary to have separate hardware for residing client and server, it can be on the same hardware. When they are on different hardware they communicate with the help of network which can be either cable or wireless media.

Components of Computer:

Among the different components of a computer there are four components which you need to know before learning SQL Server as these are components that play a great role in the working of SQL Server. These components are:
  • CPU
  • RAM
  • Hard disk
  • Network

CPU: 

In CPU we basically mean processor whose speed is measured in GHz(Giga Hertz). If we talk about processor the most important thing is process. What is a process? A process is the part of program that is in execution. Actually process does not runs on itself. It requires a thread to run. Therefore it is necessary that every process has at least one thread. If we talk about SQL Server it is multithreaded in nature. It means it can run multiple threads at the same time. This leads us to the concept of multitasking and multiprocessing.

Multitasking means more than one task running at the same time. It means in single CPU(i.e. Processor), there can be more than one process running at the same time.Each process will be having its own threads which will actually be running the process.

Multiprocessing means more than one processor will be running more than one processes at the same time. It means there will be more than one processor on the same machine running their own processes which in turn will be running their own threads.


According to the concept of multiprocessing and multitasking as given above we can say that Multiprocessing system is always multitasking.

* Dual Core processors are those which are physically one processor but when operating system starts it can see 2 processors.

CPU Performance:


Percentage of CPU being used at a time is called CPU performance.

You can always see the performance of your CPU by right clicking the task bar--> start task manager--> Processes tab.

Here all the processes running on your system can be seen.



RAM:

RAM is a volatile memory which gets erased as soon as system shuts down. It so happens that as soon as something is written on an application, an area gets allocated on RAM and as soon as we save the data explicitly it goes to hard disk. When that data is fetched from hard disk windows function is called as only Operating system can interact with hardware.

Why RAM?

  • Used to allocate memory for a particular process
  • Any kind of data modification is allotted in it
  • Accessing data from RAM is faster than hard disk
  • No need of unwanted data so RAM's volatile nature is good.
Types of RAM Memory

  • Physical
  • Virtual
Physical Memory

The memory physically residing in CPU is called physical memory.

Virtual Memory


If your computer lacks the random access memory (RAM) needed to run a program or operation, Windows uses virtual memory to compensate. 
Virtual memory combines your computer’s RAM with temporary space on your hard disk. When RAM runs low, virtual memory moves data from RAM to a space called a paging file. Moving data to and from the paging file frees up RAM so your computer can complete its work.The more RAM your computer has, the faster your programs will generally run. 

The concept of 32 bit and 64 bit systems is that in a 32 bit system 232 bits of memory is accessed i.e. it can see 4 GB memory. While in case of 64 bit system 264 bits of memory is accessed i.e. it can see 6 TB of memory.

If we are having 4 GB of memory then the memory is divided into two parts first is the user mode and second is the kernel mode each having 2 GB each. Kernel mode is the reserved space which serves as the executable file when Operating System starts.

Working

If all the memory of RAM is consumed then virtual memory manager releases the memory address that is not being used for long time and starts allocating it to the new request. The data at memory address not being used for long time is thrown to page table. Getting the data to page file from RAM is called Paging and getting data from page file to RAM is called Page fault. Page file is located in hard disk.

Now, situation may arise that page fault is occurring a lot, then you can:
  • Either increase RAM
  • or move to 64 bit system.
*Page faults are bad in the sense that they makes system slow unnecessarily.
*If you are having 32 bit system and you want to increase the size as in 32 bit Operating system only 4 GB memory can be seen at a time. In that case, you can enable switch or PAE (Physical Address Extension). boot.ini gives all the information about configuration related to operating system.

For windows application you have to enable AWE (Address Window Extensions). If SQL Server demands to see more than 4 GB then first you have to enable AWE and then PAE.

Hard Disk:

Properties:

  • Permanent Storage
  • Slower than RAM
  • Non volatile in nature
  • Huge data can be stored
The folder program files(X86) signifies the folder for 32 bit system while program files folder signifies folder for 64 bit system.

You can easily see the version of operating system by clicking on start-->run-->type winver

Edition:

Types of servers in windows are called editions. Each edition has some extra feature as compared to the previous one. Generally there are 3 kinds of edition:
  • Developer
  • Enterprise and 
  • Standard
these are named according to the requirements and features it serves.

Version:

Versions are basically the releases for the existing edition. When there are bugs in first release, then fixes are made and new updates are installed. This package thus released is called version. The further updates thus done in the existing version are called cumulative updates. When lot of cumulative updates are done for same version then Service Pack is released and these are then released as SP1, SP2, SP3 etc according to the new updates. If you are installing service pack then you don't need to install cumulative updates. Each new SP is released with fixes for the existing bugs in previous SP. The number allotted to different versions is called build( ex: build 7600). Each cumulative update is linked with a build. These cumulative updates are basically the patches or fixes.

Disk Performance:

Disk performance is measured by average disk queue length which is measured in terms of disk bytes per second.
*If during read or write operation on disk the queue length(no. of processes waiting) is greater than 2 for single disk then there may be problem that could arise.
* To measure the performance you can check disk sec per read and disk sec per write. if they are greater than 0.015 then there is a problem.
To check disk performance click on start-->run-->write perfmon and press enter


Network:


In case of SQL Server SAN is used for this purpose. SAN stands for Storage Area Network. It is used to keep the backup, for attaining high speed and security purpose. It helps in utilizing the storage space. To achieve this we use different RAID Levels. These are:


  • RAID 0- Striping- Its performance is excellent but reliability is bad. It splits data evenly across two or more disks.without parity information and with speed as the intended goal. It provides no data redundancy. RAID 0 is normally used to increase performance, although it can also be used as a way to create a large logical disk out of two or more physical ones.

  • RAID 1- Mirroring- Its performance is good and reliability is high.It creates an exact copy of a set of data on two disks. This is useful when read performance or reliability is more important than data storage capacity. Such an array can only be as big as the smallest member disk. A classic RAID 1 mirrored pair contains two disks.

  • RAID 5- Parity- It is slightly slower but reliability is good. It comprises block-level striping with distributed parity. Parity information is distributed among the drives. It requires that all drives but one be present to operate. Upon failure of a single drive, subsequent reads can be calculated from the distributed parity such that no data is lost. RAID 5 requires at least three disks.
There are more RAID levels but these are the main RAID levels that are applied for achieving the space utilization properly. 

In case of SAN if queue length is greater than 2, then no problem arises but if it is greater than 8 then problem may arise if we are having 4 disks of 2 length each in a SAN.




Comments

Popular posts from this blog

SQL Server Upgrade Issues

Pre-Requisites We usually think of SQL Server simply as a database engine, while it is a data platform which supports data warehousing, reporting, data integration and data analytics. An in-depth planning is required when upgrading from one version of SQL Server to a newer version along with the features that SQL Server offers. Upgrading these extensive number of features can be remarkably complicated and time-consuming than just upgrading the database engine alone.   Below mentioned are few of the “lessons learned” that help reduce risks of data losses etc.: 1.        Application compatibility: Before planning for upgrade activity, make sure that you discuss the compatibility of SQL Server and the associated application with your application vendor.  2.        Preparation for migration: In case your upgrade activity fails due to some reason and there is no solution possible other than migration,...

Data Compression - Row & Page ( Sketch notes)

Just some notes on compression... something helpful for a beginner.

Best Practices to be followed for SQL Server

1. Memory Capping SQL Server is an application that uses as much memory as is provided to it. Setting memory too high can cause other applications to compete with SQL on the OS and setting memory too low can cause serious problems like memory pressure and performance issues. It is important to provide at least 4 GB memory for OS use, if you are running only SQL Server as an application on the server.  2. Data File Locations SQL Server accesses data and log files with very different I/O patterns. Data file access is mostly random whilst transaction log file access is sequential. Spinning disk storage requires re-positioning of the disk head for random read and write access. Sequential data is therefore more efficient than random data access. Separating files that have different access patterns helps to minimize disk head movements, and thus optimizes storage performance.  3. Auto growth Auto-growth of the database files should be set in MBs as it will al...