SQL Server DBA Interview Questions and Answers – SQL Server DBA Tools and Executables

Every DBA uses multiple tools to perform the day to day Database Administration tasks to make his\her life easy. In this blog I am writing all about the SQL Server related tools and executables which are used by all level of DBAs for various tasks.

1) What is SSMS?

SSMS stands for SQL Server Management Studio. SQL Server Management Studio (SSMS) is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. SSMS combines a broad group of graphical tools with a number of rich script editors to provide access to SQL Server to developers and administrators of all skill levels. SSMS combines the features of Enterprise Manager, Query Analyzer, and Analysis Manager, included in previous releases of SQL Server, into a single environment. In addition, SSMS works with all components of SQL Server such as Reporting Services and Integration Services.

2) What is SQL Server profiler?

Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. We can capture and save data about each event to a file or table to analyze later. For example, we can monitor a SQL Server database to see which stored procedures are affecting performance by executing too slowly. SQL Server Profiler is used for activities such as:

• Stepping through problem queries to find the cause of the problem.
• Finding and diagnosing slow-running queries.
• Capturing the series of Transact-SQL statements that lead to a problem. The saved trace can then be used to replicate the problem on a test server where the problem can be diagnosed.
• Monitoring the performance of SQL Server to tune workloads using Database Engine Tuning Advisor.

3) What is Database Engine Tuning Advisor?

SQL Server includes another performance tool called the Database Engine Tuning Advisor or DTA.  This tool allows you to have SQL Server analyze one statement or a batch of statements that you captured by running a Profiler or server side trace.  The tool will then go through each statement to determine where improvements can be made and then presents you with options for improvement. The Database Engine Tuning Advisor is basically a tool that helps you figure out if additional indexes are helpful as well as partitioning.  Here is a summary of the options:

• Adding indexes (clustered, non-clustered, and indexed views)
• Adding partitioning
• Adding statistics including multi-column statistics which are not created automatically even when you have the AUTO_CREATE_STATISTICS database option set to ON

4) What is SQL Server Configuration Manager and its usages?

SQL Server Configuration Manager is a tool to manage the services associated with SQL Server, to configure the network protocols used by SQL Server, and to manage the network connectivity configuration from SQL Server client computers.

SQLServerManager10.msc

5) What is Reporting Services Configuration manager?

Reporting Services Configuration Manager is used to configure a Reporting Services Native Mode installation. If we installed a report server by using the files-only installation option, we must use this tool to configure the server before we can use it. If we installed a report server by using the default configuration installation option, we can use this tool to verify or modify the settings that were specified during setup. Reporting Services Configuration Manager can be used to configure a local or remote report server instance.

6) What is Resource Governor?

SQL Server provides Resource Governor, a feature than we can use to manage SQL Server workload and system resource consumption. Resource Governor enables us to specify limits on the amount of CPU and memory that incoming application requests can use.Resource Governor enables us to manage SQL Server workloads and resources by specifying limits on resource consumption by incoming requests.

7) What is Replication Monitor?

Microsoft SQL Server Replication Monitor is a graphical tool that allows us to monitor the overall health of a replication topology. Replication Monitor provides detailed information on the status and performance of publications and subscriptions, allowing us to answer common questions, such as:

• Is my replication system healthy?
• Which subscriptions are slow?
• How far behind is my transactional subscription?
• How long will it take a transaction committed now to reach a Subscriber in transactional replication?
• Why is my merge subscription slow?
• Why is an agent not running?

8) What is Always on DashBoard?

Database administrators use the AlwaysOn Dashboard to obtains an at-a-glance view the health of an AlwaysOn availability group and its availability replicas and databases in SQL Server 2012. Some of the typical uses for the AlwaysOn Dashboard are:

• Choosing a replica for a manual failover.
• Estimating data loss if you force failover.
• Evaluating data-synchronization performance.
• Evaluating the performance impact of a synchronous-commit secondary replica

9) What is SQL Server upgrade advisor?

SQL Server Upgrade Advisor helps you prepare for upgrades to SQL Server 2012. Upgrade Advisor analyzes installed components from earlier versions of SQL Server, and then generates a report that identifies issues to fix either before or after you upgrade.

10) What is Activity Monitor in SQL Server 2012?

SQL Server Activity Monitor is a feature in SQL Server Management Studio that displays information about the SQL Server processes and their effect on SQL Server performance Activity Monitor consists of several panes – Overview, Processes, Resource Waits, Data File I/O, and Recent Expensive Queries. Panes can be expanded and collapsed. The activities are queried only when the specific pane is expanded

11) What is SQLCMD?

sqlcmd utility in SQL Server is a command-line tool that lets us submit T-SQL statements or batches to local and remote instances of SQL Server. The utility is extremely useful for repetitive database tasks such as batch processing or unit testing. It also provides an easy way of simulating load to a database under development.

12) What is OSQL?

osql utility allows us to enter Transact-SQL statements, system procedures, and script files. This utility uses ODBC to communicate with the server.

13) What is bcp?

bcp utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. Except when used with the queryout option, the utility requires no knowledge of Transact-SQL. To import data into a table, you must either use a format file created for that table or understand the structure of the table and the types of data that are valid for its columns.

14) What is SQLPS module and what are its usages?

By importing the SQLPS module into a Windows PowerShell 2.0 environment, we can work with SQL Server from Powersehll. The module loads and registers the SQL Server snap-ins and manageability assemblies.

After importing the sqlps module into Windows PowerShell, we can then:

• Interactively run Windows PowerShell commands.
• Run Windows PowerShell script files.
• Run SQL Server cmdlets.
• Use the SQL Server provider paths to navigate through the hierarchy of SQL Server objects.
• Use the SQL Server manageability object models (such as Microsoft.SqlServer.Management.Smo) to manage SQL Server objects.

15) What is Data Quality Client?

Data Quality Client application enables you to perform data quality operations using a standalone tool. This application enables you to create knowledge bases, create and run data quality projects, and perform administrative tasks.

16) What are Extended events in SQL Server 2012?

Extended Events has a highly scalable and highly configurable architecture that allows users to collect as much or as little information as is necessary to troubleshoot or identify a performance problem.

17) What is SQLDiag?

SQLdiag utility is a general purpose diagnostics collection utility that can be run as a console application or as a service. You can use SQLdiag to collect logs and data files from SQL Server and other types of servers, and use it to monitor your servers over time or troubleshoot specific problems with your servers. SQLdiag is intended to expedite and simplify diagnostic information gathering for Microsoft Customer Support Services.

SQLdiag can collect the following types of diagnostic information:

• Windows performance logs
• Windows event logs
• SQL Server Profiler traces
• SQL Server blocking information
• SQL Server configuration information

18) What is PSSDIAg Manager Utility?

PSSDIAG is a general purpose diagnostic collection utility that Microsoft Product Support Services uses to collect various logs and data files. PSSDIAG can natively collect Performance Monitor logs, SQL Profiler traces, SQL Server blocking script output, Windows Event Logs, and SQLDIAG output. The data collection can be customized by enabling or disabling any of these log types, by changing the sample interval of the blocking script and the Performance Monitor logs, and by modifying the specific events and counters for SQL Profiler and Performance Monitor to capture.

19) What are Database Maintenance Plans?

Database Maintenance Plans allow us to automate many database administration tasks in Microsoft SQL Server. We can create maintenance plans using an easy wizard-based process without any knowledge of Transact-SQL.

We may perform the following tasks within a database maintenance plan:

• Shrinking a database
• Backing up a database
• Performing an operator notification
• Updating database statistics
• Verifying the integrity of a database
• Cleaning up leftover maintenance files
• Executing a SQL Server Agent job
• Executing a Transact-SQL statement
• Rebuilding an index
• Reorganizing an index
• Cleaning up database histories

20) What is SQL Nexus Tool?

SQL Nexus is a tool that helps you identify the root cause of SQL Server performance issues. It loads and analyzes performance data collected by SQLDiag and PSSDiag. It can dramatically reduce the amount of time you spend manually analyzing data.

21) What is tablediff.exe? What are the usages?

The tablediff utility is used to compare the data in two tables for non-convergence, and is particularly useful for troubleshooting non-convergence in a replication topology. This utility can be used from the command prompt or in a batch file to perform the following tasks:

• A row by row comparison between a source table in an instance of Microsoft SQL Server acting as a replication Publisher and the destination table at one or more instances of SQL Server acting as replication Subscribers.
• Perform a fast comparison by only comparing row counts and schema.
• Perform column-level comparisons.
• Generate a Transact-SQL script to fix discrepancies at the destination server to bring the source and destination tables into convergence.
• Log results to an output file or into a table in the destination database.

22) What is Distrib.exe?

Replication Distribution Agent (Distrib.exe) is an executable that moves the snapshot (for snapshot replication and transactional replication) and the transactions held in the distribution database tables (for transactional replication) to the destination tables at the Subscribers.

23) What is logread.exe?

The Replication Log Reader Agent is an executable that monitors the transaction log of each database configured for transactional replication and copies the transactions marked for replication from the transaction log into the distribution database.

24) What is snapshot.exe?

Replication Snapshot Agent is an executable file that prepares snapshot files containing schema and data of published tables and database objects, stores the files in the snapshot folder, and records synchronization jobs in the distribution database.

25) What is DTExec.exe?

dtexec command prompt utility is used to configure and execute SQL Server Integration Services packages.

26) What is DTutil.exe?

The dtutil command prompt utility is used to manage SQL Server Integration Services packages. The utility can copy, move, delete, or verify the existence of a package.

27) What is Data Profile Viewer?

Data profiles can be Viewed and analyzed in the data profiling process using Data Profile Viewer. We can view these profiles after we have run the Data Profiling task inside an Integration Services package and computed the data profiles.

28) What is Project Conversion Wizard?

Integration Services Project Conversion Wizard converts a complete project to the project deployment model.

29) What is Policy Management Tool?

Policy Management tool is used to create policies to manage entities on the server, such as the instance of SQL Server, databases, or other SQL Server objects. Policy-based management in SQL Server assists the database administrators in defining and enforcing policies that tie to database objects and instances. These policies allow the administrator to configure and manage SQL server across the enterprise.

30) What is Master Data Services Configuration?

Master Data Services Configuration Manager is the tool that we use to create or configure a Master Data Services database. After we create a database, we can use Master Data Services Configuration Manager to create a web application and to enable integration with Data Quality Services. Finally, we can use Master Data Services Configuration Manager to associate an MDS database with an MDS web application.

31) What is the use of setup.exe under the below folder

C:\Program Files\Microsoft SQL Server\100\Setup BootStrap\Release

Before SQL Server 2008 if there was a requirement to rebuild the master databases then its required SQL Server complete Setup. But starting SQL Server 2008 we don’t need SQL server setup anymore, system databases can be rebuild with the setup.exe located in the above location.

References: Thanks to the all the SQL Server techies who wrote and shared the valuable information in the below blogs which helped me a lot to prepare this series of Questions. Also big thanks to Microsoft Documentation which contains each and everything about their product.

http://www.mssqltips.com/sqlservertutorial/286/database-engine-tuning-advisor/

https://technet.microsoft.com/en-us/library/bb933866(v=sql.110).aspx

https://www.simple-talk.com/sql/sql-tools/sql-server-sqlcmd-basics/

http://sqlnexus.codeplex.com/

http://blog.sqlauthority.com/2009/11/13/sql-server-policy-based-management-create-evaluate-and-fix-policies/