SQL Server DBA Interview Questions

 

1) MS SQL Server Vs MySQL

Answer: MS SQL Server and  MySQL both are used widely for enterprise database systems
MySql is open-source Real-time Database management system,MS SQL Server is the licensed proprietary software, MySQL is used to update the database,MySQL server is used to maintain the data

Q2) What purpose does the model database server?

Answer: It is used for the template for all kinds of databases which is created for the instance of MS SQL Server. When we started SQL Server it will create TEMPDB is create. This kind of databases should exist on SQL Servers only

Q3) How do you trace the traffic hitting a SQL Server?

Answer: It is the SQL Sever profiler to monitor the traffic. By using this utility we control the traffic on a particular instance.

Q4) What types of replication are supported in SQL Server?

Answer: It is divided into 3 types of replications. They are
Snap: It is used to capture snap short of instance
Merge: It uses the snap replication and it will  act as a central repository serve
Dealing: it will be based on both merge and snap

Q5) Why would you use SQL Agent?

Answer: SQL Agent is a mechanism of job programming, it is used to monitor the job for the events in server know more at SQL server dba training

Q6) What happens on checkpoint?

Answer: Checkpoints are one of the data recovery mechanisms in the SQL server. It is used to maintain the modifications and changes  in a cache of the database page


7. What is DBCC?

DBCC statements are Database Console Commands and come in four flavors:

  1. Maintenance
  2. Informational
  3. Validation
  4. Miscellaneous

Maintenance commands are those commands that allow the DBA to perform maintenance activities on the database such as shrinking a file.

Informational commands provide feedback regarding the database such as providing information about the procedure cache. Validation commands include commands that validate the database such as the ever-popular CHECKDB. Finally, miscellaneous commands are those that obviously don’t fit in the other three categories. This includes statements like DBCC HELP, which provides the syntax for a given DBCC command.

8. How can you control the amount of free space in your index pages?

You can set the fill factor on your indexes. This tells SQL Server how much free space to leave in the index pages when re-indexing. The performance benefit here is fewer page splits (where SQL Server has to copy rows from one index page to another to make room for an inserted row) because there is room for growth built into the index.

9. Why would you call Update Statistics?

Update Statistics is used to force a recalculation of query optimization statistics for a table or indexed view. Query optimization statistics are automatically recomputed, but in some cases, a query may benefit from updating those statistics more frequently.

Beware though that re-computing the query statistics causes queries to be recompiled. This may or may not negate all performance gains you might have achieved by calling update statistics. In fact, it could have a negative impact on performance depending on the characteristics of the system.

10. What is a correlated sub-query?

A correlated sub-query is a nested query that is linked to the outer query. For instance, say I wanted to find all the employees who have not entered their time for the week. I could query the Employee table to get their first and last name, but I need to look at the time entry table to see if they’ve entered their time or not. I can’t do a straight join here because I’m looking for the absence of time data, so I’ll do a correlated sub-query similar to this:

11. What authentication modes does SQL Server support?

SQL Server supports Windows Authentication and mixed-mode. Mixed-mode allows you to use both Windows Authentication and SQL Server Authentication to log into your SQL Server. It’s important to note that if you use Windows Authentication, you will not be able to log in.

12. Explain your SQL Server DBA Experience?

This is a generic question often asked by many interviewers. Explain what are the different SQL Server Versions you have worked on, what kind of administration of those instances has been done by you.

Your role and responsibilities carried out in your earlier projects would be of significance to the potential employer. This is the answer that lets the interviewer know how suitable are you for the position to which you are being interviewed.

13. What are the different SQL Server Versions you have worked on?

The answer would be depending on the versions you have worked on, I would say I have experience working in SQL Server 7, SQL Server 2000, 2005, and 2008. If you have worked only on some versions be honest in saying that, remember, no one would be working on all versions, it varies from individual to individual. know more at SQL Server dba online course

14. What are the different types of Indexes available in SQL Server?

mentioned such as Unique, XML, Spatial and Filtered Indexes. More on these Indexes later.

15. What is the difference between Clustered and Non-Clustered Index?

In a clustered index, the leaf level pages are the actual data pages of the table. When a clustered index is created on a table, the data pages are arranged accordingly based on the clustered index key. There can only be one Clustered index on a table.

In a Non-Clustered index, the leaf level pages do not contain data pages instead it contains pointers to the data pages. There can multiple non-clustered indexes on a single table.

16. What are the new features in SQL Server 2005 when compared to SQL Server 2000?

There are quite a lot of changes and enhancements in SQL Server 2005. A few of them are listed here:

  • Database Partitioning
  • Dynamic Management Views
  • System Catalog Views
  • Resource Database
  • Database Snapshots
  • SQL server dba online training

Support for Analysis Services on a Failover Cluster.

  1. Profiler being able to trace the MDX queries of the Analysis Server.
  2. Peer-to-peer Replication
  3. Database Mirroring

17. What are the High-Availability solutions in SQL Server and differentiate them briefly?

Failover Clustering, Database Mirroring, Log Shipping, and Replication are the High-Availability features available in SQL Server. I would recommend reading this blog of mine which explains the differences between these 4 features.

18. How do you troubleshoot errors in a SQL Server Agent Job?

Inside SSMS, in Object Explorer under SQL Server Agent look for Job Activity Monitor. The job activity monitor displays the current status of all the jobs on the instance. Choose a particular job that failed, right-click, and choose view history from the drop-down menu.

The execution history of the job is displayed and you may choose the execution time (if the job failed multiple times during the same day). There would information such as the time it took to execute that Job and details about the error that occurred. know more at SQL Server dba online training from India

19. What is the default Port No on which SQL Server listens?

1433

20. How many files can a Database contain in SQL Server? How many types of data files exist in SQL Server? How many of those files can exist for a single database?

1. A Database can contain a maximum of 32,767 files.
2. There are Primarily 2 types of data files Primary data file and Secondary data file(s)
3. There can be only one Primary data file and multiple secondary data files as long as the total # of files is less than 32,767 files

Answer: DBCC Stands for database console commands it is available in 4 types.They are

Comments

Post a Comment

Popular posts from this blog

What is Tableau?

Image
  What is Tableau? Tableau  is a powerful and fastest growing data visualization tool used in the Business Intelligence Industry. It helps in simplifying raw data in a very easily understandable format. Tableau helps create the data that can be understood by professionals at any level in an organization. It also allows non-technical users to create customized dashboards. know more at Tableau online training Data analysis is very fast with Tableau tool and the visualizations created are in the form of dashboards and worksheets. The best features of Tableau software are Data Blending Real time analysis Collaboration of data The great thing about Tableau software is that it doesn’t require any technical or any kind of programming skills to operate. The tool has garnered interest among the people from all sectors such as business, researchers, different industries, etc. Tableau Product Suite The Tableau Product Suite consists of Tableau Desktop Tableau Public Tableau Online Tablea...
Read more

Python Applications

Image
  Python is known for its general-purpose nature that makes it applicable in almost every domain of software development. Python makes its presence in every emerging field. It is the fastest-growing programming language and can develop any application. know more at Python online training Here, we are specifying application areas where Python can be applied. 1) Web Applications We can use Python to develop web applications. It provides libraries to handle internet protocols such as HTML and XML, JSON, Email processing, request, beautifulSoup, Feedparser, etc. One of Python web-framework named Django is used on  Instagram . Python provides many useful frameworks, and these are given below: Django and Pyramid framework(Use for heavy applications) Flask and Bottle (Micro-framework) Plone and Django CMS (Advance Content management) 2) Desktop GUI Applications The GUI stands for the Graphical User Interface, which provides a smooth interaction to any application. Python provides a...
Read more