Notes

http://thomaslarock.com/2012/01/the-5-dba-interview-questions-you-have-to-ask/
Difference between data file and log file.
The difference between the two file types is that .LOG files are typically generated automatically, while .TXT files are created by the user. For example, when a software installer is run, it may create a log file that contains a log of files that were installed. Log files typically have one entry per line, which includes information such as the filename, the action (created, moved, deleted, etc.), and the location of the file.

Every User is important to database
Most CEO dont do data entry

WHICH IS FASTER: INSERTING ONE MILLION ROWS OF DATA, OR UPDATING ONE MILLION ROWS OF DATA?
Based on the situation, ask the interviewer questions of the condition I have.
Using Cursor

Trying to solve problem yourself.

http://crankydba.com/2012/02/09/more-interview-questions/
Tell me about VLFs
Do you have any experience with MSX-TSX relationships in the SQL Server Agent?
How do you maintain INDEXes and STATISTICS in your environment today? How could you improve that?
When would you disable auto update of statistics?
How do you keep your skills current with SQL Server technology?
What’s the most compelling reason to upgrade from SQL 2005 to SQL 2008 R2?
What’s the biggest mistake you see other SQL Server DBAs make?
What’s your worse habit as a DBA?
Are you familiar with the PASS Summit or local chapters?
With what trace flags are you familiar?
What are the two types of authentication? Which is more secure?
Which will be faster, inserting a million rows of data or updating a million rows of data? (shamelessly stolen from Tom LaRock)

1. What is a four-part name?

If the candidate ever crosses database boundaries with his queries, they should know this answer. A four-part name refers to the parts of a SQL Server object name that uniquely identifies it in the SQL environment. The first part is the instance. The second part is the database. Third is the schema and fourth is the object name. So if wanted to reference a table called employee from the HumanResources schema in the AdventureWorks database on your Production instance, the four part name would look like this:

Instance.Database.Schema.Table
Production.AdventureWorks.HumanResources.Employee

4. Why would you use SQL Agent?

SQL Agent is the job scheduling mechanism in SQL Server. Jobs can be scheduled to run at a set time or when a specific event occurs. Jobs can also be executed on demand. SQL Agent is most often used to schedule administrative jobs such as backups.

https://seniordba.wordpress.com/2014/06/25/11-sql-server-dba-interview-questions/
6. What are the dynamic management views and what value do they offer?

The DMV’s are a set of system views new to SQL Server 2005 and beyond to gain insights into particular portions of the engine

Here are some of the DMV’s and the associated value:
sys.dm_exec_query_stats and sys.dm_exec_sql_text – Buffered code in SQL Server
Additional Information: Identifying the input buffer in SQL Server 2000 vs SQL Server 2005
sys.dm_os_buffer_descriptors
Additional Information: Buffer Pool Space in SQL Server 2005
sys.dm_tran_locks – Locking and blocking
Additional Information: Locking and Blocking Scripts in SQL Server 2000 vs SQL Server 2005
sys.dm_os_wait_stats – Wait stats
Additional Information: Waitstats performance metrics in SQL Server 2000 vs SQL Server 2005
sys.dm_exec_requests and sys.dm_exec_sessions – Percentage complete for a process
Additional Information: Finding a SQL Server process percentage complete with dynamic management views

9. Name 3 or more DBCC commands and their associated purpose.

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.


https://seniordba.wordpress.com/2014/03/11/understanding-acid/
Atomicity – In a transaction involving two or more discrete pieces of information, either all of the pieces are committed or none are.

Consistency – A transaction either creates a new and valid state of data, or, if any failure occurs, returns all data to its state before the transaction was started.

Isolation – A transaction in process and not yet committed must remain isolated from any other transaction.

Durability – Committed data is saved by the system such that, even in the event of a failure and system restart, the data is available in its correct state.

https://www.mssqltips.com/sqlservertip/1093/verifying-backups-with-the-restore-verifyonly-statement/
SQL Server Performance Tuning

Question 1 - Name as many native SQL Server performance monitoring and tuning tools that you know of and their associated value.
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.
Additional information - SQL Server Command Line Tools To Manage Your Server
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.
Additional information - SQL Server Performance Statistics Using a Server Side Trace
PerfmonSystem Monitor - PerfmonSystem Monitor is responsible for macro level metrics related to processes and sub systems.
Additional information - Free Microsoft Tools to Help Setup and Maintain PerfMon
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.
Additional information - Dynamic Management Views and Functions in SQL Server 2005
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.
Additional information - How To Collect Performance Data With TYPEPERF.EXE
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.
Additional information
Built-In Performance Reports in SQL Server 2005
Additional resources for this question:
Tutorial - SQL Server Performance Monitoring and Tuning
Tip Category - SQL Server Performance Tuning


Question 2 - How do you go about tuning a SQL Server query?
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.
Additional information - Query Plans in SQL Server 2000 vs SQL Server 2005
Tip Category - SQL Server Query Plans
Tutorial - SQL Server Graphical Query Plan

https://www.mssqltips.com/sql-server-tip-category/139/interview-questions-developer/