Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

Junior SQL Server DBA Interview Questions

memorize.ai (lvl 286)

(0)

10 cards

()

Section 1

Preview this deck

SQL Server Backup and Recovery What are 2 options to validate whether or not a backup will restore successfully?

Front

1 / 10

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

6 years ago

Date created

Mar 1, 2020

Cards (10)

Section 1

(10 cards)

SQL Server Backup and Recovery What are 2 options to validate whether or not a backup will restore successfully?

Front

Restore the backup as a portion of a testing process or log shipping. Restore the backup with the Verify Only option.

Back

What are included columns with respect to SQL Server indexing?

Front

A new type of index was developed in SQL Server 2005 and beyond that assists in situations where a covering index is needed. Indexes with Included Columns are nonclustered indexes that have the following benefits: Columns defined in the include statement, called non-key columns, are not counted in the number of columns by the database engine. Columns that previously could not be used in queries, like nvarchar(max), can be included as a non-key column. A maximum of 1023 additional columns can be used as non-key columns.

Back

SQL Server Business Intelligence Name some new features from Data Transformation Services to SQL Server Integration Services.

Front

SSIS checkpoints. SSIS logging. SSIS package configurations. SSIS Breakpoint. Dynamic flat file connections. SSIS batch processing. MERGE JOIN.

Back

SQL Server Maintenance What are the three options in SQL Server 2005 and beyond to rebuild indexes?

Front

CREATE INDEX with DROP_EXISTING DROP INDEX and CREATE INDEX ALTER INDEX

Back

How can you issue a full backup and not interrupt the LSN's?

Front

Issue a copy only backup.

Back

Name 3 or more DBCC commands and their associated purpose.

Front

DBCC CACHESTATS - Displays information about the objects currently in the buffer cache. DBCC CHECKDB - This will check the allocation of all pages in the database as well as check for any integrity issues. DBCC CHECKTABLE - This will check the allocation of all pages for a specific table or index as well as check for any integrity issues. DBCC DBREINDEX - This command will reindex your table. If the indexname is left out then all indexes are rebuilt. If the fillfactor is set to 0 then this will use the original fillfactor when the table was created. DBCC PROCCACHE - This command will show you information about the procedure cache and how much is being used. DBCC MEMORYSTATUS - Displays how the SQL Server buffer cache is divided up, including buffer activity. DBCC SHOWCONTIG - This command gives you information about how much space is used for a table and indexes. Information provided includes number of pages used as well as how fragmented the data is in the database. DBCC SHOW_STATISTICS - This will show how statistics are laid out for an index. You can see how distributed the data is and whether the index is really a good candidate or not. DBCC SHRINKFILE - This will allow you to shrink one of the database files. This is equivalent to doing a database shrink, but you can specify what file and the size to shrink it to. Use the sp_helpdb command along with the database name to see the actual file names used. DBCC SQLPERF - This command will show you much of the transaction logs are being used. DBCC TRACEON - This command will turn on a trace flag to capture events in the error log. Trace Flag 1204 captures Deadlock information. DBCC TRACEOFF - This command turns off a trace flag.

Back

SQL Server Performance Tuning Name as many native SQL Server performance monitoring and tuning tools that you know of and their associated value.

Front

System objects - System objects such as sp_who2, sp_lock, fn_get_sql, etc. provide a simple means to capture basic metrics related to locking, blocking, executing code, etc. Profiler - In a nutshell, Profiler provides the lowest common denominator of activity on a SQL Server instance. Profiler captures per session code with the ability to filter the data collection based on database, login, host name, application name, etc. in order to assess the IO, CPU usage, time needed, etc. Perfmon\System Monitor - Perfmon\System Monitor is responsible for macro level metrics related to processes and sub systems. Dynamic Management Views and Functions - New to SQL Server 2005 and beyond, the Dynamic Management Views and Functions offer a real time view into the SQL Server sub systems. TYPEPERF.EXE - TYPEPERF.EXE is a command line tool included with the Windows operating system that writes performance data to the command window or to a file. It is necessary to capture performance data whenever you are trying to diagnose performance issues on a server. Performance data provides information on the server's utilization of the processor, memory, and disk, as well as SQL Server-specific performance data. SQL Server Management Studio Built-in Performance Reports - As part of the installation of SQL Server 2005 and beyond a number of performance-related reports are installed. To get to these reports open the SQL Server Management Studio (SSMS) and connect to a SQL Server instance. If you don't have an instance of Reporting Services installed then the icon will be disabled.

Back

SQL Server Database Design What happens when you add a column in the middle of a table (dbo.Test1) in SQL Server Management Studio?

Front

Management Studio creates a temporary table called dbo.Tmp_Test1 with the new structure. If there is data in the original table dbo.Test1 this data is inserted into the new temp table dbo.Tmp_Test1 (now you have two sets of the same data). The original table dbo.Test1 is dropped. The new table dbo.Tmp_Test1 is renamed to dbo.Test1. If the table has indexes all of the indexes are recreated.

Back

How do you backup Analysis Services databases?

Front

Create the XML statement to backup the Analysis Services databases, then create a SQL Server Agent Job to perform the task on a daily basis.

Back

How do you go about tuning a SQL Server query?

Front

Identify the query causing the issue. Review the query plan by issuing SHOWPLAN_TEXT, SHOWPLAN_ALL, Graphical Query Plan or sys.dm_exec_query_stats. Review the individual query components to determine which components of the query have the highest cost. Outline options to improve the query such as moving from cursor based logic to set based logic or vice versa, changing the JOIN order, WHERE clause or ORDER BY clause, adding indexes, removing indexes, creating covering indexes, etc. Test the options to determine the associated performance improvement. Implement the solution.

Back