:::: MENU ::::

Posts Categorized / DBA Interview questions

  • Sep 25 / 2008
  • 0
DBA Interview questions, dbDigger, Storage, System Administration

Understanding RAID levels for storage systems

As data is of primary importance for any organization. And DBAs pay utmost attention to make sure the reliability and performance of Relational data. SQL Server databases are configured on several levels of RAID. There are many RAID levels available on modern RAID controllers. Only a subset of these is most useful when configuring a Microsoft SQL Server. Each RAID level has a specific use and benefit. Using the wrong type of RAID level can not only hurt system performance, but also add more cost to your server configuration.
Experts recommend that you never use software arrays on a database server. Use of software arrays requires additional CPU power from Windows in order to calculate which physical disk the data is written to.
In hardware arrays, this overhead is offloaded to a physical PCI, PCIe or PCIx card within the computer (or within the SAN device), which has its own processor and software dedicated to this task.

RAID 1 – Mirror.
A RAID 1 array is most useful for high write files, such as the page file, transaction logs and tempdb database. A RAID 1 array takes two physical disks and creates an exact duplicate of the primary drive on the backup drive. There is no performance gain or loss when using a RAID 1 array. This array can survive a single drive failure without incurring any data loss.

RAID 5 – Redundant Stripe Set.
A RAID 5 array is most useful for high read files such as the database files (mdf and ndf files) and file shares. It is the most cost-effective, high-speed RAID configuration. With a RAID 5 array, there is a performance impact while writing data to the array because a parity bit must be calculated for each write operation performed. For read performance, the basic formula is (n-1)*o where n is the number of disks in the RAID 5 array and o is the number of I/O operations each disk can perform. Note: While this calculation is not perfectly accurate, it is generally considered close enough for most uses. A RAID 5 array can survive a single drive failure without incurring any data loss.

RAID 6 – Double Redundant Stripe Set.
Like a RAID 5 array, a RAID 6 array is most useful for high read files such as the database and file shares. With RAID 6, there is also a performance impact while writing data to the array because two parity bits must be calculated for each write operation performed. The same basic formula is used to calculate the potential performance of the drives (n-2)*o. A RAID 6 array can survive two drive failures without incurring any data loss.Because of the dual parity bits with RAID 6, it is more expensive to purchase than a RAID 5 array. However, RAID 6 offers a higher level of protection than RAID 5. When choosing between RAID 5 and RAID 6, consider the length of time to rebuild your array, potential loss of a second drive during that rebuild time, and cost.

RAID 10 – Mirrored Strip Sets.
A RAID 10 array is most useful for high read or high write operations. RAID 10 is extremely fast; however, it is also extremely expensive (compared to the other RAID levels available). In basic terms, a RAID 10 array is several RAID 1 arrays stripped together for performance. As with a RAID 1 array, as data is written to the active drive in the pair, it is also written to the secondary drive in the pair. A RAID 10 array can survive several drive failures so long as no two drives in a single pair are lost.

RAID 50 – Stripped RAID 5 Arrays.
A RAID 50 array is an extremely high-performing RAID array useful for very high-load databases. This type of array can typically only be done in a SAN environment. Two or more RAID 5 arrays are taken and stripped together and data is then written to the various RAID 5 arrays. While there is no redundancy between RAID 5 arrays, it’s unnecessary because the redundancy is handled within the RAID 5 arrays. A RAID 50 array can survive several drive failures so long as only a single drive per RAID 5 array fails.

Click here to read all posts related to SQL Server storage systems and I/O operations

A part separated by an article of DennyCherry

  • Sep 25 / 2008
  • 0
DBA Interview questions, dbDigger, Storage

Basic storge system terms required to consult a solution

Before you sit down with a storage administrator or engineer to map out your plans for disk configuration, here are basic preparation steps to take. Start by getting familiar with the terms below and communicate your requirements much easier.

  • RAID – Redundant Array of Inexpensive Disks, also known as Redundant Array of Independent Disks.

  • Disk subsystem – A general term that refers to the disks on the server.

  • Spindle – Spindles are another way to refer to the physical disk drives that make up the RAID array.

  • I/O Ops – Input/Output operations, usually measured per second.

  • Queuing – Number of I/O Ops that are pending completion by the disk subsystem.

  • SAN – Storage area networks are collections of storage devices and fibre switches connected together along with the servers that access the storage on the device. SAN has also become a generic term, which refers to the physical storage drives such as EMC, 3PAR and Hitachi.

  • LUN – Logical Unit Number – This is the identification number assigned to a volume when created on a SAN device.

  • Physical drive – How Windows sees any RAID array, single drive or LUN that is attached to the server.

  • Logical drive – How Windows presents drives to the user (C:, D:, E:, etc.).

  • Block size – The amount of data read from the spindles in a single read operation. This size varies per vendor from 8 KB to 256 MB.

  • Hardware array – A RAID array created using a physical RAID controller.

  • Software array – A RAID array created within Windows using the computer management snap-in.

  • Hot spare – A spindle that sits in the drive cage and is added to the array automatically in the event of a drive failure. While this does not increase capacity, it does reduce the amount of time that the array is susceptible to data loss because of a second failed drive.

  • Recovery time – Amount of time needed for the RAID array to become fully redundant after a failed drive has been replaced, either manually or automatically via a hot spare.

    Click here to read all posts related to SQL Server storage systems and I/O operations

A part separated by an article of DennyCherry

  • Sep 15 / 2008
  • 0
DBA best practices, DBA Interview questions, dbDigger

Do nots for a DBA

Here are some actions that a DBA needs to avoid under given circumstances

  • Do not re build an index during working hours
  • Do not stop the database engine without warning/notice
  • Do not perform a service pack upgrade during working hours
  • Do not start any backup/restore process, if you have intensive I/O going on
  • Do not de fragment the drive which ha database files
  • Do not configure screen savers on production servers, if you have cpu intensive tasks there
  • Do not perform any vendor upgrades without testing or backing up
  • Aug 15 / 2008
  • 0
DBA Interview questions, dbDigger, Indexes

Satistics updation with Reindex

Question: Does SQL Server update statistics with Reindex on a table in SQL Server 2005?

Answer: Yes, with full table scan details

Explanation: Whenever an index is rebuilt the stats on the table is updated with the details of a full table scan.

Ref: Search Engine Q&A #10: Rebuilding Indexes and Updating Statistics

Note: The Question of day is taken from SQLServercentral.com. I want to compile selected QOD.

  • Aug 09 / 2008
  • 0
DBA best practices, DBA Interview questions, dbDigger

DBA Best Practices Day to Day

DBA may keep a defined set of practices that should be implemented daily to detect/overcome any anomaly. Following is a sketch of general daily practices for a DBA .

  1. Check OS Event Logs, SQL Server Logs, and Security Logs for unusual events.
  2. Verify that all scheduled jobs have run successfully.
  3. Confirm that backups have been made and successfully saved to a secure location.
  4. Monitor disk space to ensure your SQL Servers won’t run out of disk space.
  5. Throughout the day, periodically monitor performance using both System Monitor and Profiler.
  6. Use Enterprise Manager/Management Studio to monitor and identify blocking issues.
  7. Keep a log of any changes you make to servers, including documentation of any performance issues you identify and correct.
  8. Create SQL Server alerts to notify you of potential problems, and have them emailed to you. Take actions as needed.
  9. Run the SQL Server Best Practices Analyzer on each of your server’s instances on a periodic basis.
  10. Take some time to learn something new as a DBA to further your professional development.

Chosen from SQL Server DBA best practices By Brad M.mcGehee

  • Aug 06 / 2008
  • 0
DBA Interview questions, dbDigger, System Functions

Usefull T-SQL functions for using in scripts

Here are some use full T-SQL functions which may be help full during several operations.

--Change comptibility level of DB
EXEC sp_dbcmptlevel AdventureWorks, 90  
GO

--To get Name of SQL Server instance
SELECT @@SERVERNAME  
GO

-- To get name of sql server service
SELECT @@SERVICENAME  
GO

-- To get info about logged in user
SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'  
GO

--Check disk allocation for a database
DBCC CHECKALLOC  
GO

--Check number of connections/attempted connections since sql server started
select @@CONNECTIONS  
GO

--Display info about a db
sp_helpdb pubs  
GO

-- Display info about a table
sp_help tablename  
GO

--get list of all user databases
sp_databases  
GO

-- To Get Name of ur current working DB
select db_name()  
GO

--Retreive character map
exec sp_helpsort  
GO

These are all building blocks and will help you to get valuable information while creating triggers, logs, SPs etc.

Consult us to explore the Databases. Contact us