SQL Server & Middleware DBA Flashcards
How do you ensure data integrity in SQL Server?
Ensuring data integrity in SQL Server involves implementing constraints such as primary keys, foreign keys, unique constraints, and check constraints. These constraints enforce rules at the database level to maintain consistent and accurate data.
Candidates should also discuss using triggers and stored procedures to enforce business rules and data validation. Look for responses that show a comprehensive understanding of data integrity mechanisms and their practical application in SQL Server.
How would you identify and troubleshoot a SQL Server performance issue caused by high CPU usage?
Check the Server Tools: Perf Mon
DMVs: sys.dm_exec_query_stats, sys.dm_exec_requests
Once identified, you can analyze and optimize these queries by adding appropriate indexes, adjusting query logic, or updating statistics.
How do you approach optimizing SQL Server memory usage?
- Set appropriate Min and Max Server memory to ensure SQL Server does not consume all the available sys memory.
- Monitor Memory Usage through DMVs such as sys.dm_os_memory_clerks and sys.dm_exec_query_memory_grants
What methods do you use to monitor SQL Server performance on an ongoing basis?
- Use Built-in tools such as Profiler, Extended Events, and Perf Mon
- DMVs such as sys.dm_os_wait_stats and sys.dm_exec_query_stats
How do you handle and optimize SQL Server tempdb usage?
- Ensure it’s configured correctly which means one tempdb data file per CPU and all files the same size and distributed across separate storage devices to balance I/O
- Monitor through DMVs such as sys.dm_db_file_space_usage
- Ensure Best Practices such as avoiding large temp tables and minimizing tempdb contention
What are some common causes of SQL Server slow performance, and how would you resolve them?
Common Causes: Poorly Optimized Queries, lack of proper indexing, high CPU or memory usage, and I/O bottlenecks
How to Resolve: Analyze and optimize slow-running queries, indexes are appropriately created and maintained, properly monitor server resource usage
How would you approach the process of indexing a large SQL Server table to improve query performance?
To index a large SQL Server table, you would start by analyzing the queries that frequently access this table to determine which columns are most often used in WHERE clauses, JOIN conditions, and ORDER BY clauses. These columns are good candidates for indexing.
Next, you would create appropriate indexes, such as clustered or non-clustered indexes, based on the query patterns. It’s also essential to regularly update statistics to ensure the SQL Server query optimizer has the most accurate information.
How would you handle database corruption?
- Identify the corrupted data by running DBCC CHECKDB.
- Attempt to repair the corruption using DBCC REPAIR_ALLOW_DATA_LOSS, REPAIR_FAST, or REPAIR_REBUILD.
- If repairs fail, restore the database from the latest clean backup.
- Investigate and resolve the root cause to prevent future occurrences.
What is a composite index? What is a good use case?
- Indexes multiple columns in combination.
- Queries filtering on multiple columns, like first_name and last_name.
How do you optimize a slow-running query?
To optimize a slow-running query, I would first analyze the query execution plan to identify any bottlenecks or areas causing delays. I look for things like full table scans, missing indexes, or inefficient joins.
If the query is performing a full table scan, adding appropriate indexes to the columns used in the WHERE clause or JOIN operations can significantly improve performance. For instance, if the query frequently filters on a column, an index on that column can reduce the data retrieval time.
I also consider rewriting the query to simplify it or break it down into smaller parts if possible. For example, using subqueries or temporary tables helps streamline complex queries.
Additionally, I check for other factors, such as the proper use of joins, avoiding unnecessary columns in the SELECT statement, and ensuring that the statistics on the tables are up-to-date. These steps help ensure the query runs as efficiently as possible.
How would you handle database deadlocks?
I would first try to identify the root cause of the deadlock by reviewing the database logs and deadlock graphs, which provide detailed information about the involved transactions and the resources they are contending for.
Depending on the query, I would potentially change the isolation level to read uncommitted or adding the nolock hint. I would also look at the query to see if it’s possible to break it up into shorter transactions or implementing temp tables.
Might suggest a deadlock retry mechanism if deadlocks are frequently.
What is database partitioning and when would you use it?
Database partitioning involves dividing a large table into smaller, more manageable pieces called partitions. Each partition is stored separately and can be queried individually, which can significantly improve performance and manageability, especially for very large datasets.
I would use partitioning when a table grows so large that query performance starts to degrade.
For instance, in a table storing historical transaction data, I might partition the data by month or year.
What is an OLTP database and how would you optimize one?
OLTP systems are designed for managing transactional data, focusing on fast query processing, high concurrency, and maintaining data integrity. They typically involve a large number of short, write-heavy transactions, such as insert, update, and delete operations.
To optimize an OLTP database, I would use techniques like normalization to reduce data redundancy, implement appropriate indexing to speed up query execution while keeping the number of indexes small to reduce overhead on write operations, and use efficient transaction management to handle concurrent access.
How would you optimize a SQL query?
- Query execution plan analysis to identify bottlenecks and missing indexes
- Make sure indexes on columns used in WHERE, JOIN, and ORDER BY clauses (where appropriate)
- Avoid Select *, retrieve only the amount of data needed
- Avoid Correlated subqueries
- Limiting rows with WHERE
Explain the difference between WHERE and HAVING clauses.
WHERE - Filter rows before any grouping occurs, and it applies to individual rows in a the table. Used with SELECT, UPDATE, and DELETE
HAVING - Filter GROUPS OF ROWS created by GROUP BY clause. Used with aggregate functions such as COUNT, SUM, AVG
What are the differences between INNER JOIN, LEFT JOIN, and RIGHT JOIN in SQL?
- An INNER JOIN returns only the rows with a match between the two tables based on the join condition.
- A LEFT JOIN returns all the rows from the left table and the matched rows from the right table; if there is no match, NULL values are returned for the columns from the right table.
- A RIGHT JOIN is similar to a LEFT JOIN, but it returns all the rows from the right table and the matched rows from the left table, filling in NULLs where there is no match.
Which DMV would you use to identify blocking sessions?
sys.dm_trans_locks
Which DMV would you use to look for resource contention?
sys.dm_os_waiting_tasks
Which DMV would you use to see what is currently running?
sys.dm_exec_requests
How would you see who is on the DB currently and if anything is being blocked in their session?
sp_who2 and the isblocking column
What are the 4 types of locks?
S -> Select
X -> Exclusive
IX -> Intended Exclusive
U -> Update
What are the types of Isolation Levels?
- Read Uncommitted
- Read Committed
- Repeatable Read
- Snapshot
- Serializable
What is a correlated subquery?
a subquery that references columns from the outer query. It is re-executed for each row processed by the outer query. This makes it more dynamic, but potentially less efficient.
Which DMV will show you whether you might need to rebuild or reorganize an index?
sys.dm_db_index_physical_stats
What is contained in the Query Store?
Plan Store - Used for storing estimated execution plan information
Runtime stats score - Used for storing execution statistics information
Wait stats store - For persisting wait stats information
How do you enable the query store?
ALTER DATABASE <DATABASE_NAME> SET QUERY_STORE = ON</DATABASE_NAME>
What are some views in the query store?
Regressed Queries - shows queries that have regressed over time
Top Resource Consuming Queries
Query Waits Statistics - Analyzes most active wait categories
What are some columns you might use in PERFMON?
Processing(_Total)% Processor Time
Paging File(_Total)% Usage
PhysicalDisk(_Total)\Avg. Disk sec/Read and Avg. Disk sec/Write
SQLServer:Buffer Manager\Page Life Expectancy
What is Page Life Expectancy?
Page life expectancy indicates how long SQL Server expects a page to live in memory. You should monitor this value over time, and evaluate sudden drops. Such drops in the counter’s value could indicate poor query patterns, external memory pressure (for example, the server running a large SSIS package) or could just be normal system processing like running a consistency check on a large database
Which waits could indicate CPU Contention?
SOS_SCHEDULER YIELD
CXPACKET
Which waits could indicate Storage System Performance issues?
PAGEIOLATCH_SH
What does it mean if a query is SARGable?
Refers to a predicate (WHERE clause) in a specific format that can use an index to speed up execution of a query
What types of expressions are NON-SARGABLE?
- LIKE with a wildcard at the Front
- Functions in the where clause
What is Parameter Sniffing and why can this be bad?
When the database engine executes a query for the first time, it will optimize the query based on the initial value of the parameter which will reduce the overall workload of compiling queries on the server.
This can be bad if the initial value of the parameter produces a non-optimal execution plan for parameters used down the line.
What two categories do blocking problems usually fall into?
- Poor Transactional Design
- Long Running Transactions
Which DMV will allow you to see what sessions are currently waiting?
sys.dm_exec_session_wait_stats
Which wait type could be indicative of a blocking problem?
LCK_M_X
Which wait type could be indicative of TempDB Contention?
PAGEIOLATCH_UP or PAGELATCH_EX on tempdb system pages
What are some of the requirements for setting up a SQL Server Failover Cluster?
Almost all the same requirements as a RAC db in Oracle. Virtual network name for SQL Server, VIP for SQL Server, IP for Public and Private for each node in failover cluster, Shared Drives for SQL Server Data and Log Files, Quorum Disk, and MSDTC Disk.
What are the prereqs for AlwaysOn?
Windows Failover Cluster Services on each node, full backup of primary db, full recovery mode, shared folder for backups
How would you configure an AG to move all read operations to the secondary database?
Configure an AG listener with ApplicationIntent=ReadOnly
Can you explain what a deadlock is and how SQL Server handles deadlocks?
A deadlock occurs when two or more sessions are waiting for each other to release locks, in a circular chain, so that they can proceed with their transaction. SQL Server detects deadlocks automatically and resolves them by choosing one process as a deadlock victim and rolling back its transaction, allowing other transactions to proceed.
What are the considerations for securing SQL Server?
Authentication should be Windows Authentication where possible. Use TDE for Encryption at Rest and secure data in flight with SSL/TLS. Regularly apply SQL and OS Patches.
What is DBCC and what are some examples?
Database Console Commands, DBCC CHECKDB, DBCC SHRINKDATABASE, DBCC SHRINKFILE
Tell me about your process for troubleshooting Database Problems.
- Identifying the issue by gathering information on symptoms and error messages
- Isolate the problem by determining if it is related to the database, application, or infrastructure
- Conduct tests to pinpoint the root cause and apply the necessary fixes
- Post resolution, monitor the DB to ensure stability and document the issue, analysis, and resolution steps for further analysis
How does SQL Server handle transactions, and what are the ACID properties?
SQL Server uses transactions to ensure a sequence of operations is completed successfully and maintains data integrity. The ACID properties are:
- Atomicity (all operations complete or none do) - Consistency (data remains consistent before and after the transaction) - Isolation (transactions are isolated from each other) - Durability (once committed, changes are permanent)
What is SQL Profiler/Extended Events, and how are they used?
SQL Server Profiler is a tool for monitoring and analyzing SQL Server events. It captures and records data about each event, such as query execution, performance metrics, and errors. It allows database administrators to diagnose performance issues, identify slow-running queries, and audit SQL Server activity. The captured trace data can be saved and replayed to troubleshoot issues or optimize SQL Server performance.
What cmdlet in Powershell allows you to see more info on a given cmdlet?
Get-Help <Cmdlet_name></Cmdlet_name>
How would you find all cmdlets in Powershell that have dba in their title?
Get-Command dba
How would you find the server-level configuration settings using T-SQL?
SELECT * FROM SYS.CONFIGURATIONS ORDER BY NAME
What table has information such as object, type, and created date?
sys.objects
Which table has information on all user and system database files?
sys.master_files
What table will allow you to see info on the database’s recovery model?
sys.databases
