SQL Server & Middleware DBA Flashcards
(53 cards)
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
