Sql Server Performance Tuning Interview Questions And Answers

What Is The Importance Of A Recovery Model

Primarily, the recovery model is chosen keeping in view the amount of data loss one can afford. If one expects to have minimal or no data loss, choosing the Full recovery model is a good choice.

Depending on the recovery model of a database, the behavior of the database log file changes. I would recommend you read more material on log backups and log file behavior and so on to understand in depth.

When Looking At The Estat Events Report You See That You Are Getting Busy Buffer Waits Is This Bad How Can You Find What Is Causing It

Ans: Buffer busy waits may indicate contention in redo, rollback, or data blocks. You need to check the v$waitstat view to see what areas are causing the problem.

The value of the count column tells where the problem is, the class column tells you with what. UNDO is rollback segments, DATA is database buffers.

What Is Index Block Splitting Different Types Of Splitting

Index entries are managed in a orderly fashion in index structure. So if a new key is getting added, and there no free space of the key, then index block will be splitted.

Two types of splitting.

Also Check: How To Dress For An Interview Female

What Do You Mean By Data Integrity

Data integrity is the assurance of accuracy and consistency of data over its whole life cycle. It is a critical aspect of the design, implementation, and usage of systems that store, process, or retrieve data.

Data integrity also defines integrity constraints for enforcing business rules on data when it is entered into a database or application.

Q11 Explain Performance Tuning Enhancements

Ans: Oracle includes many performance tuning enhancements like:

• Automatic Performance Diagnostic and Tuning Features
• Automatic Shared Memory Management – Automatic Shared Memory Management puts Oracle in control of allocating memory within the SGA
• Wait Model Improvements – A number of views have been updated and added to improve the wait model.
• Automatic Optimizer Statistics Collection – gathers optimizer statistics using a scheduled job called GATHER_STATS_JOB
• Dynamic Sampling – enables the server to improve performance
• CPU Costing – default cost model for the optimizer , with the cost unit as time Optimizer Hints
• Rule-Based Optimizer Obsolescence – No more used
• Tracing Enhancements – End to End Application Tracing allows a client process to be identified via the client identifier rather than the typical session id
• SAMPLE Clause Enhancements Hash Partitioned Global Indexes

Read Also: What Is Excel Test For Interview

What Is The Use Of The Update: Statistics Command

Ans: SQL Server updates query optimization statistics regularly in a database table or indexed view. In this regard, the SQL server’s query optimizer performs this function by default. UPDATE_STATISTICS is the command that allows updating query statistics frequently in database tables. As a result, it improves query plans and query performance as well.

What Is A Unique Constraint

Unique constraints ensure that all the values in a column are different. For example, if we assign a unique constraint to the e_name column in the following table, then every entry in this column should have a unique value.

First, we will create a table.

create table stu2)

Now, we will insert the records.

insert into stu2 valuesinsert into stu2 valuesinsert into stu2 values

Output:

A PRIMARY KEY constraint will automatically have a UNIQUE constraint. However, unlike a PRIMARY KEY, multiple UNIQUE constraints are allowed per table.

Also Check: Interview Questions In Java Programming

How Can You Distinguish Between Substr And Charindex

Ans: Given the starting position and length details, SUBSTR helps extract a substring from the specified string.

The syntax for this function is given by:

SUBSTRING

On the contrary, the CHARINDEX function helps identify a substring’s position from the specified string.

The syntax for this function is given by:

CHARINDEX

Q6 What Is Translate And Decode In Oracle

Ans: Translate: translate function replaces a sequence of characters in a string with another set of characters. The replacement is done single character at a time.

Syntax: translate

Example: translate

The DECODE function compares one expression to one or more other expressions and, when the base expression is equal to a search expression, it returns the corresponding result expression or, when no match is found, returns the default expression when it is specified, or NA when it is not.

Syntax: DECODE

Example: SELECT employee_name, decode result FROM employee

Don’t Miss: What Is The Star Method Of Interviewing

Q7 What Are Some Common Clauses Used With Select Query In Sql

The following are some frequent SQL clauses used in conjunction with a SELECT query:

WHERE clause: In SQL, the WHERE clause is used to filter records that are required depending on certain criteria.ORDER BY clause: The ORDER BY clause in SQL is used to sort data in ascending or descending order depending on specified field .GROUP BY clause: GROUP BY clause in SQL is used to group entries with identical data and may be used with aggregation methods to obtain summarised database results.HAVING clause in SQL is used to filter records in combination with the GROUP BY clause. It is different from WHERE, since the WHERE clause cannot filter aggregated records.

What Is A Unique Key

A Unique key constraint uniquely identified each record in the database. This provides uniqueness for the column or set of columns.

A Primary key constraint has automatic unique constraint defined on it. But not, in the case of Unique Key.

There can be many unique constraint defined per table, but only one Primary key constraint defined per table.

Read Also: How Can I Prepare For An Interview

Using Sql Optimization Tools

PhoenixNAP Global IT Services mentions several tools you can use for SQL optimization. Therefore, you should be familiar with the different profiling, tracing, monitoring, metrics and reporting tools available to optimize SQL databases. Be prepared to name three to five different tools that you’ve used when optimizing a SQL database.

When answering SQL optimization interview questions in this area, make sure you can explain what each tool does and how it contributes to the overall optimization process. Tell the interviewer if there are other tools you haven’t used to optimize databases but that you’d like to use or that appear to have beneficial features.

What Is Auto: Increment

AUTO_INCREMENT is used in SQL to automatically generate a unique number whenever a new record is inserted into a table.

Since the primary key is unique for each record, this primary field is added as the AUTO_INCREMENT field so that it is incremented when a new record is inserted.

The AUTO-INCREMENT value starts from 1 and is incremented by 1 whenever a new record is inserted.

Syntax:

CREATE TABLE Employee NOT NULL,Employee_designation varcharAge int,PRIMARY KEY )

Do check out our Blog on PL/SQL Interview Questions to crack your SQL Interview.

Read Also: How To Prepare For Pharmacy Technician Interview

What Do You Know About The Stuff Function

The stuff function deletes a part of the string and then inserts another part into the string, starting at a specified position.

Syntax:

STUFF

Here, String1 is the one that will be overwritten. Position indicates the starting location for overwriting the string. Length is the length of the substitute string, and String2 is the string that will overwrite String1.

Example:

select stuff

This will change âSQL Tutorialâ to âPython Tutorialâ

Output:

Python Tutorial

What Do You Mean By Magic Tables In Sql Server

Ans: Magic tables are virtual tables that exist in two types INSERTED AND DELETED. They hold the information of the newly INSERTED and DELETED rows. The INSERTED table will have the newly inserted rows on top of it. The DELETED tables will have the recently deleted rows on top of it on similar tracks. Magic tables are stored in tempDB.

You May Like: Interview With An Entrepreneur Questions And Answers

What Is Sql Server Agent

SQL Server Agent plays an important role in the daily work of SQL Server administrators or DBAs. This is one of the important parts of SQL Server. The aim of the server agent is to easily implement tasks using a scheduler engine that enables the tasks to be performed at scheduled times. SQL Server Agent uses SQL Server to store scheduled management task information.

Q73 What Is The Acid Property In A Database

ACID stands for Atomicity, Consistency, Isolation, Durability. It is used to ensure that the data transactions are processed reliably in a database system.

• Atomicity: Atomicity refers to the transactions that are completely done or failed where transaction refers to a single logical operation of a data. It means if one part of any transaction fails, the entire transaction fails and the database state is left unchanged.
• Consistency: Consistency ensures that the data must meet all the validation rules. In simple words, you can say that your transaction never leaves the database without completing its state.
• Isolation: The main goal of isolation is concurrency control.
• Durability: Durability means that if a transaction has been committed, it will occur whatever may come in between such as power loss, crash or any sort of error.

You May Like: Food And Beverage Director Interview Questions

Note: While Answering Questions Regarding Threshold Values And Performance Counter Values It Really Depends On Lot Many Things Ex: Type Of Database Server And Database Configurations Sla With The Business Owners Etc Like These Lot Many Parameters Will Be Considered While Determining A Threshold Values

One of our blog reader Jagadesh suggested how they calculate and keep threshold value for Page Life Expectancy.

It really depends on the amount of memory allocated to your instance of SQL Server. When working with my clients the value I start with is taking / 4 * 300. This would be the minimum value. On a server with 32GB allocated to SQL this value would be *300 = 2400 seconds or 40 minutes.

What Is A Transaction Log And Why Is It Important

Ans: Transaction log records all transactions and related database modifications of every transaction. To be precise, it records the beginning of a transaction, the changes during the transaction, and the last COMMIT or ROLLBACK of the transaction. The transaction log is one of the vital components in database management since it is used to retrieve the database to a consistent state if there is a system failure.

You May Like: Amazon Data Engineer Interview Questions

Q2: What Is The Difference Between Index Rebuild And Index Reorganize Operations

Index fragmentation can be resolved by rebuilding and reorganizing SQL Server indexes regularly. The Index Rebuild operation removes fragmentation by dropping the index and creating it again, defragmenting all index levels, compacting the index pages using the Fill Factor values specified in rebuild command, or using the existing value if not specified and updating the index statistics using FULLSCAN of all the data.

The Index Reorganize operation physically reorders leaf level pages of the index to match the logical order of the leaf nodes. The index reorganizes operation will be always performed online. Microsoft recommends fixing index fragmentation issues by rebuilding the index if the fragmentation percentage of the index exceeds 30%, where it recommends fixing the index fragmentation issue by reorganizing the index if the index fragmentation percentage exceeds 5% and less than 30%.

For more information, see the article: .

What Is The Difference Between Stored Procedures And Functions

Ans: In stored procedures, codes are usually compiled, and these compiled codes are executed when the program calls them. But in functions, on the contrary, codes are compiled and executed every time they are called.

Also, there must be a return while executing functions, whereas it is optional while executing stored functions. Furthermore, functions can be called from stored procedures, but stored procedures cannot be called from functions.

Don’t Miss: How To Conduct A Mock Interview

What Is Referential Integrity

Ans: Referential integrity aims at keeping SQL databases consistent. It is achieved using a group of rules that enforces relationships among data in tables. Generally, referential integrity is enforced with the support of foreign key constraints. Besides, it can be enforced with the help of check constraints with user-defined functions and triggers.

Q1: What Is The Main Difference Between A Clustered And Non

A Non-clustered index is different from a Clustered index in that, the underlying table rows will not be stored and sorted based on the Non-clustered key, and the leaf level nodes of the Non-clustered index are made of index pages instead of data pages. The index pages of the Non-clustered index contain Non-clustered index key values with pointers to the storage location of these rows in the underlying heap table or the Clustered index.

For more information, see the article: .

Recommended Reading: How To Crack Google Data Analyst Interview

What Are Cursors Explain The Different Types

Example: “Cursors allow row-by-row processing of the result sets. The types of cursors are static, dynamic, forward-only and keyset-driven. A static cursor makes a temporary copy of the data and stores it in tempdb and modifications of the base table are not reflected in data returned by fetches made by the cursor. A dynamic cursor reflects all changes in the base table, forward-only cursor can only fetch data sequentially from first to last and a keyset-driven cursor uniquely identifies a row built in tempdb.”

What Is A Filtered Index

Ans: A filtered index is nothing but a non-clustered index with an optimized disk-based restore. It uses a filter predicate to select a portion of rows in a table. A filtered index is created when a column has fewer relevant values for queries. Hence, it helps to improve query performance, reduce storage costs, and index maintenance.

Recommended Reading: How To Prepare For Python Coding Interview

State The Differences Between Views And Tables

Q: What Are The Pros And Cons Of Using Online Index Creation Or Rebuilding Options

Setting the ONLINE option to ON when you create or rebuild the index will enable other data retrieving or modification processes on the underlying table to continue, preventing the index creation process from locking the table. On the other hand, the ONLINE index creation or rebuilding process will take longer time than the offline default index creation process.

For more information, see the article: .

Also Check: How To Prepare For A Qa Interview

What Can Cause A High Value For Recursive Calls How Can This Be Fixed

Ans: A high value for recursive calls is caused by improper cursor usage, excessive dynamic space management actions, and or excessive statement re-parses.

You need to determine the cause and correct it By either relinking applications to hold cursors, using proper space management techniques or ensure repeat queries are placed in packages for proper reuse.

What Do You Mean By Ssrs In Sql Server

Ans: SSRS is known as SQL Server Reporting Services, which provides a group of on-premises tools and services. SSRS allows creating, deploying, and managing mobile and paginated reports.

• Paginated Reports: They produce up-to-date reports with the support of updated tools and new features.
• Mobile Reports: This type has a format that adapts to different devices.
• Web Portal: Paginated reports, excel workbooks, KPIs, and mobile reports can be organized and viewed through the web portal. You can view the web portal using any modern browser.

Don’t Miss: How To Record An Interview On Your Computer

Q53 What Is The Difference Between Clustered And Non

The differences between the clustered and non clustered index in SQL are :

Compare Local Variables And Global Variables

Ans:

Read Also: What To Do Before An Interview

What Are The Different Components Of Replication And What Is Their Use

The 3 main components in Replication are Publisher, Distributor, and Subscriber. The publisher is the data source of a publication. The distributor is responsible for distributing the database objects to one or more destinations. The subscriber is the destination where the publisher’s data is copied/replicated.