Interview Questions.

Q1. What Is Lock Escalation?

The Query Optimizer to begin with locks the specified Rows for DML operations / Retrieval operations. This also get applicable Pages and Completely table to be 'INTENT' Locked. If greater than 50% of rows are specially locked then this mechanically gets the complete PAGE or TABLE to be locked. Lock Escalation mechanism may be controlled through the usage of Locking Hints.

Q2. What Is Policy Based Management (pbm)?

Policy Based Management in SQL SERVER 2012 Administration lets in you to outline and enforce regulations for configuring and dealing with SQL Server throughout the corporation. Policy-Based Management is configured in SQL Server Management Studio (SSMS). Navigate to the Object Explorer and increase the Management node and the Policy Management node; you'll see the Policies, Conditions, and Facets nodes.

Q3. As Per Your Opinion What Are The Five Top Responsibilities Of A Dba?

I price the subsequent 5 obligations as the key obligations of a DBA.

Securing database from physical and logical integrity harm.

Restoring database from backup as part of catastrophe control plan.

Optimizing queries performance via suitable indexing and optimizing joins, where conditions, choose clause and so forth.

Designing new schema, support legacy schema, and legacy database systems.

Helping builders enhance their SQL-related code writing talent.
 

Q4. What Is The Purpose Of Sql Profiler In Sql Server?

SQL profiler is a tool to screen performance of numerous saved procedures. It is used to debug the queries and techniques. Based on performance, it identifies the sluggish executing queries. Capture any issues by capturing the occasions on production environment so that they may be solved.

Q5. What Are Recommended Options To Be Used While Using Db Mirroring?

Database Mirroring is to be configured with TCP Protocol and make certain that records over each endpoint is encrypted. Better to utilize TDE for greater safety.

Q6. What Should Be The Fill Factor For Indexes Created On Tables?

Fill thing specifies a percent that suggests how full the Database Engine should make the leaf stage of each index web page for the duration of index introduction or alteration. Fill issue have to be an integer price from 1 to 10@The default is @I favor to keep my servers default fill thing as 90.

Q7. What Are The Difference Between Primary Key And Unique Key?

An particular key cant now not be referenced as overseas key. And it could permit on null.

Q8. Where Can You Find The Error Log Information?

We can employ SQL Profiler , SQL Server Log or use xp_readerrorlog prolonged Stored Procedure to retrieve the error log information.

Q9. What Are System Databases Into Sql Server (2005/2008)

TEMPDB, MSDEB, MASTER, MSDB, mssqlsystemresource.

Q10. What Are The Basic Functions For Master, Msdb, Model, Tempdb And Resource System Databases?

The grasp database holds facts for all databases placed on the SQL Server example. As SQL Server cannot start without a functioning grasp database, we have to administer this database with care and reveal Startup Parameters in Configuration Manager.

The msdb database stores information regarding database backups, SQL Agent records, DTS applications, SQL Server jobs, and a few replication information together with for log delivery.

The tempdb holds temporary items which include worldwide and nearby temporary tables and stored approaches.

The version is basically a template database used inside the introduction of any new user database created in the instance.

The aid Database is a study-handiest database that carries all of the device objects which are blanketed with SQL Server. SQL Server device gadgets, which includes sys.Objects, are physically continued inside the Resource database, but they logically seem inside the sys schema of each database. The Resource database does not incorporate user statistics or consumer metadata.

Q11. Does Dbcc Checkdb Requires Db To Be In Single_user Mode?

Yes and No. This is difficult question. If you're using repair option with CHECKDB then you have to have the DB in unmarried user mode. Following is the technique to have your DB in a unmarried user mode.

Use master

move sp_dboption dbname, unmarried, actual

Following is the mistake which you get whilst you run the DBCC CHECKDB with repair choice wo having the DB in unmarried person mode. The equal is authentic for DBCC CHECKDB also

Q12. Explain The Different Types Of Backups Available In Sql Server?

Types of backups to be had in SQL Server:

Complete: This creates a complete stand alone photograph of the database. This backup is self established and may be restored to either the identical or a brand new database on equal or other server.

Differential: This backs up most effective the modified contents for the reason that final backup. They do now not offer lots flexibility.

Transaction log: This backs up all transaction logs because the preceding transaction log backup or the entire transaction log backup if there has no longer been one in beyond.

Files and Filegroups backup: This choice is suitable when time constraints are excessive and one can't afford to perform a complete database backup. It also wishes transaction logs backup to take location to make it worth deciding on this option. After restoring file backup, follow transaction logs to roll the document contents ahead to make it regular with the database..

Q13. How To Truncate The Log In Sql Server 2012?

BACKUP LOG TestDB WITH TRUNCATE_ONLY is long past. SQL server does no longer can help you truncate the log now in any other case complete cause of a DB is defeated. You need to ensure whether or not you want log or not. If you do now not need log then have the recovery model easy as opposed to full. If you do not need the log to be accrued in a few unique bulk logging then exchange the healing model BULK LOGGED for that period and take one TLog Backup simply before and after this transformation. I shall discuss this later in my later blog. BACKUP LOG command backs up the t-log and frees the space in the log report.

Q14. What Is The Status Of Services On Passive Node For Failover Cluster In Sql Server?

SQL offerings will be in manual and stopped. Cluster carrier will be in automatic and started out mode on each the nodes.

Q15. Can You Move The Resources After Pausing The Node?

Yes assets can be moved after pausing the node. But we cannot move them lower back until the node is paused.

Q16. What Is Scan Table/view And Seek Table/view When Its Occurs?

A Table/view SCAN takes place when no beneficial indexes exist. A TABLE SCAN reads all records, row by row, to discover the fit.

Q17. What Is The Meaning Of Lock Escalation And Why/how To Stop This?

Take into account that complete table might be locked for the processing thenn that is higher to apply TABLOCK hint and get whole table blocked. This is a pleasing manner to avoid the wastage of square server DB engine processing for lock escalation. Somewhere you could additionally want to use TABLOCKX whilst you want an special lock at the desk within the query.

Q18. What Is Replication With Database Mirroring?

Database mirroring may be used with replication to offer availability for the ebook database. Database mirroring entails  copies of a single database that normally live on distinctive computers. At any given time, most effective one reproduction of the database is currently to be had to customers that are called the major database. Updates made by using clients to the main database are implemented on the opposite replica of the database, referred to as the replicate database. Mirroring involves applying the transaction log from each insertion, update, or deletion made on the fundamental database onto the replicate database.

Q19. What Is Service Broker?

Service Broker is a message-queuing technology in SQL Server that permits builders to integrate SQL Server completely into distributed programs. Service Broker is feature which presents facility to SQL Server to send an asynchronous, transactional message. It permits a database to send a message to any other database with out anticipating the response, so the application will hold to feature if the faraway database is briefly unavailable.

Q20. What Is Data Compression?

In SQL SERVER 2008 R2, Data Compression comes in two sorts viz., Row Compression wherein It minimizes the metadata (column statistics, period, offsets, and so on.) related to each file. Numeric information types and stuck length strings are stored in variable-length storage layout, just like Varchar. Page compression makes use of the Row compression approach internally and also applies prefix stage compression.For each column in a page, reproduction prefixes are diagnosed. These prefixes are stored in compression facts headers (CI) which reside after page headers. A reference range is assigned to those prefixes and that reference number is replaced anyplace the ones prefixes are being used.

Q21. How Do You Test Your Database?

This is a completely frequent question. I would really like to describe my usual database trying out approach as well as stored method testing techniques.

Testing Databases:

Table Column statistics type and data cost validation.

Index implementation and performance development.

Constraints and Rules need to be validated for records integrity.

Application area duration and kind have to healthy the corresponding database area.

Database gadgets like saved processes, triggers, capabilities need to be examined the use of exclusive styles of enter values and checking the anticipated output variables.

Testing Stored Procedures:

Understand the requirements in terms of Business Logic.

Check if the code follows all the coding requirements.

Compare the fields’ requirements of software to the fields retrieved by means of a saved method. They need to match.

Repeatedly run the saved methods several instances with extraordinary input parameters after which evaluate the output with the predicted results.

Pass invalid enter parameters and notice if a saved technique has precise blunders dealing with.

Q22. How To View The Error Log For Any Specific Instance?

There are many approaches however I prefer following approach. Take a state of affairs while you need to locate the mistake log when the DB turned into installed a single user mode.

CREATE TABLE #Errorlog (Logdate Datetime, Processinfo

VARCHAR(20),Text VARCHAR(2000))

INSERT INTO #Errorlog

EXEC xp_readerrorlog

SELECT * FROM #Errorlog

WHERE Text Like ‘%SINGLE%USER%’

Q23. What Stored By The Master?

Server’s configurations and logins.

Q24. Which Feature In Sql Server 2008 Has Surprised You? You Can Name Just One.

Plan Freezing is a new feature I by no means notion of. I discover it very thrilling! It is protected in SQL Server 2008 CTP@SQL Server 2008 allows greater query overall performance stability and predictability by means of providing new capability to fasten down question plans. This empowers agencies to sell strong question plans across hardware server replacements, server upgrades, and production deployments.

Q25. What Is Sql Profiler. What Are The Default Templates With It?

SQL Server Profiler is a graphical user interface to SQL Trace for tracking an example of the Database Engine or Analysis Services. You can capture and shop statistics approximately every event to a document or table to research later.

Q26. I Have All The Primary Data Files, Secondary Data Files As Well As Logs. Now, Tell Me Can I Still Restore The Database Without Having A Full Backup?

You can't repair the database while not having a complete database backup. However, when you have the reproduction of all of the data files (.Mdf and .Ndf) and logs (.Ldf) when database was in running condition (or your preferred country) it's far viable to connect the database using sp_attach_db.

Q27. What Are The Joins In Sql Server?

Inner Join, Outer (Left Outer & Right Outer) Joins and Cross join.

Q28. What Is The Syntax To Execute The Sys.Dm_db_missing_index_details?

Select * from sys.Dm_db_missing_index_details

Q29. According To You What Goes Into Making The Best Database Administrator?

The number one task of DBAs is to comfy the facts. They need to be able to maintain it secure as well as reproduce it correctly, each time required. So as in line with my view, a Database Administrator who can fulfill the requirements of Securing Data and Retrieving Data is the high-quality DBA.

When I lease a DBA I usually ask them questions about backup techniques and green restoring methodologies.

Q30. Where Sql Server User Names And Passwords Are Stored In Sql Server?

They get stored in System Catalog Views sys.Server_principals and sys.Sql_logins.

Q31. What Is Stored In The Mssqlsystemresource Database?

Definition of sys gadgets, which logically indicates into all database and DMVs.

Q32. What Is Your Recommendation For A Query Running Very Slow?

Well, your question may be very hard to reply without searching on the code, software and physical server. In such situations, there are a few matters that need to be paid interest to right away.

Restart Server

Upgrade Hardware

Check Indexes on Tables and Create Indexes if necessary Make sure SQL Server has priority over different running system procedures in SQL Server settings.

Update records on the database tables.

Q33. What Is Blocking And How Would You Troubleshoot It?

Blocking occurs while a method has acquired lock on a fixed of rows, and any other system is trying to accumulate a lock on the same set of rows. In one of these case, the other system has to attend till the primary procedure finishes its process and releases the lock on the above said rows.

Use sp_lock procedure to look type of locks obtained through various periods on the server to find the reason of blocking off.

Problem is hinted by way of the WAIT reputation is a lot of rows which might be lower back as an output of sp_lock saved system execution.

Use sp_who and sp_who2 to go back more columns to get more facts around the blockading.

Use DBCC INPUTBUFFER (spid).This will display the closing 128 characters of the last T-SQL statement carried out from connection referred through spid. This way one could perceive the stored procedure or application module that triggered blocking.

To resolve blockading, you can disconnect the connection inflicting the blocking the usage of KILL command. If this does not solve the problem completely, then rewrite the saved method/module causing the block extra efficiently.

Q34. Can We Perform Backup Restore Operation On Tempdb?

NO

Q35. Where The Sql Logs Gets Stored?

It’s stored into root folder SQL server, LOG folder.

Q36. What Stored By The Model?

Templates of latest database gadgets, like tables and column.

Q37. What Stored By The Tempdb ?

Row variations, cursor, temp gadgets.

Q38. What Are The Dmvs?

Dynamic Management Views (DMV) return server state data that can be used to screen the fitness of a server example, diagnose problems, and song overall performance.

Q39. What Is Mean By Clustered Index And Non Clustered Index, Give Syntax Of Creation?

Create clustered index index_name on empmst(card)

Q40. How To Find The Version Of Sql Server?

Select @@model

Q41. Where Does The Copy Job Runs In The Log Shipping Primary Or Secondary?

Secondary server. This query is largely asked to discover whether you've got a arms on work on log delivery or now not.

Q42. How To Find The Service Pack Installed?

Select @@model Or pick out serverproperty (‘productlevel’)

Q43. Describe The Left Outer Join & Right Outer Join.

Left Outer join Retrieves the all facts from LEFT desk and matching from the RIGHT desk, and null values where is not any match. Right Outer Join simply opposite.

Q44. What Changes In The Front End Code Is Needed If Mirroring Is Implemented For The High Availability?

You need to feature handiest FAILOVER PARTNER statistics for your the front quit code. “Data Source=ServerA;Failover Partner=ServerB;Initial Catalog=AdventureWorks;Integrated Security=True;”.

Q45. What Are Truncate Options Available In Sql Server?

Use TRUNCATE_ONLY option for SQL Server 2005 systems at the same time as appearing Backup. This option isn't always available in SQL Sever 2008 and R@Instead, we use ON_TRUNCATE choice to be had within the Backup announcement.

Q46. What Is The Difference Between Lock, Block And Deadlock?

Lock: DB engine locks the rows/page/table to get right of entry to the records that's labored upon in line with the question.

Block: When one technique blocks the resources of every other method then blockading takes place. Blocking can be identified through using

SELECT * FROM sys.Dm_exec_requests wherein blocked <> 0

SELECT * FROM grasp..Sysprocesses in which blocked <> zero

Deadlock: When some thing takes place as follows: Error 1205 is said by way of SQL Server for deadlock.

Q47. What Is The Difference Between Sql Server 2000 Object Owner And Sql Server 2005 Schema?

Let us first see the completely qualified question name to get entry to a table for SQL Server 2000 and SQL Server 2005.

SQL Server 2000: [DataBaseServer].[DataBaseName].[ObjectOwner].[Table]

SQL Server 2005: [DataBaseServer].[DataBaseName].[Schema].[Table]

SQL Server 2008: [DataBaseServer].[DataBaseName].[Schema].[Table]

In SQL Server 2000, previous to dropping the person who owns database items, all of the gadgets belonging to that user either need to be dropped or their owner needs to be changed. Every time a consumer is dropped or modified, gadget admin has to undergo this inconvenient method.

In SQL Server 2005 and the later variations, in place of having access to a database via database owner, it could be accessed thru a schema. Users are assigned to schemas, and by using the usage of this schema a consumer can get right of entry to database items. Multiple users can be assigned to a single schema, and all of them can robotically obtain the same permissions and credentials because the schema to which they're assigned. Because of the same motive in SQL Server 2005 and the later versions – when a user is dropped from database – there's no negative effect at the database itself.

Q48. What Are The Steps To Take To Improve Performance Of A Poor Performing Query?

Steps to take to enhance overall performance of queries:

Use indexes successfully

Create all number one and foreign keys and relationships amongst tables.

Avoid using cursors

Avoid using Select*, alternatively point out the wanted columns and narrow the resultset as wished.

Denormalize

Use partitioned perspectives

Use temporary tables and desk variables

Reduce joins and heavy clauses like GROUP BY if no longer wanted

Implement queries as saved methods.

Have a WHERE Clause in all SELECT queries.

Use facts types accurately

Instead of NULLS use string values which include N/A

Q49. What Is Database Isolation In Sql Server?

Database isolation comes into play whilst we need to isolate the database and defend it from other things at the network. This safety is finished the usage of locks. The form of lock and the level of isolation level wished is referred as isolation stage in SQL Server.

Types of isolation levels:

READ COMMITTED: Shared locks are held even as any information is being read.

READ UNCOMMITTED: Specifies isolation stage zero locking. There are hence no shared locks or one of a kind locks. Lease restrictive of all of the isolation levels.

REPEATABLE READ: Locks are carried out on all records being utilized by a question. However, new phantom rows may be inserted into the facts set by using some other person and are covered in later reads within the modern transaction.

SERIALIZABLE: Issues a variety lock on records set, preventing different customers to update or insert records into dataset until the transaction is complete.

Q50. What Stored By The Msdb?

Scheduled jobs, Backup/Restore and DTA information.