SQL Performance Tuning Interview Questions -Part 1
SQL Performance Tuning Interview Questions -Part 2
SQL Performance Tuning Interview Questions -Part 3
SQL Performance Tuning Interview Questions -Part 4
36.The disadvantages of a hash index
Hash tables are not sorted data structures, and there are many types of queries which hash indexes can not even help with. For instance, suppose you want to find out all of the employees who are less than 40 years old.
SQL Performance Tuning Interview Questions -Part 2
SQL Performance Tuning Interview Questions -Part 3
SQL Performance Tuning Interview Questions -Part 4
36.The disadvantages of a hash index
Hash tables are not sorted data structures, and there are many types of queries which hash indexes can not even help with. For instance, suppose you want to find out all of the employees who are less than 40 years old.
37.How could you do that with a hash table index?
Well, it’s not possible because a hash table is only good for looking up key value pairs – which means queries that check for equality (like “WHERE name = ‘Jesus’”). What is implied in the key value mapping in a hash table is the concept that the keys of a hash table are not sorted or stored in any particular order. This is why hash indexes are usually not the default type of data structure used by database indexes – because they aren’t as flexible as B- trees when used as the index data structure. Also see: Binary trees versus Hash Tables.
Well, it’s not possible because a hash table is only good for looking up key value pairs – which means queries that check for equality (like “WHERE name = ‘Jesus’”). What is implied in the key value mapping in a hash table is the concept that the keys of a hash table are not sorted or stored in any particular order. This is why hash indexes are usually not the default type of data structure used by database indexes – because they aren’t as flexible as B- trees when used as the index data structure. Also see: Binary trees versus Hash Tables.
38.What are some other types of indexes?
Indexes that use a R- tree data structure are commonly used to help with spatial problems. For instance, a query like “Find all of the Starbucks within 2 kilometers of me” would be the type of query that could show enhanced performance if the database table uses a R- tree index.Another type of index is a bitmap index, which work well on columns that contain Boolean values (like true and false), but many instances of those values – basically columns with low selectivity.
Indexes that use a R- tree data structure are commonly used to help with spatial problems. For instance, a query like “Find all of the Starbucks within 2 kilometers of me” would be the type of query that could show enhanced performance if the database table uses a R- tree index.Another type of index is a bitmap index, which work well on columns that contain Boolean values (like true and false), but many instances of those values – basically columns with low selectivity.
39.How does an index improve performance?
Because an index is basically a data structure that is used to store column values, looking up those values becomes much faster. And, if an index is using the most commonly used data structure type – a B- tree – then the data structure is alsosorted. Having the column values be sorted can be a major performance enhancement – read on to find out why.Let’s say that we create a B- tree index on the Employee_Name column This means that when we search for employees named “Jesus” using the SQL we showed earlier, then the entire Employee table does not have to be searched to find employees named “Jesus”. Instead, the database will use the index to find employees named Jesus, because the index will presumably be sorted alphabetically by the Employee’s name. And, because it is sorted, it means searching for a name is a lot faster because all names starting with a “J” will be right next to each other in the index! It’s also important to note that the index also stores pointers to the table row so that other column values can be retrieved – read on for more details on that.
Because an index is basically a data structure that is used to store column values, looking up those values becomes much faster. And, if an index is using the most commonly used data structure type – a B- tree – then the data structure is alsosorted. Having the column values be sorted can be a major performance enhancement – read on to find out why.Let’s say that we create a B- tree index on the Employee_Name column This means that when we search for employees named “Jesus” using the SQL we showed earlier, then the entire Employee table does not have to be searched to find employees named “Jesus”. Instead, the database will use the index to find employees named Jesus, because the index will presumably be sorted alphabetically by the Employee’s name. And, because it is sorted, it means searching for a name is a lot faster because all names starting with a “J” will be right next to each other in the index! It’s also important to note that the index also stores pointers to the table row so that other column values can be retrieved – read on for more details on that.
40.What exactly is inside a database index?
So, now you know that a database index is created on a column in a table, and that the index stores the values in that specific column. But, it is important to understand that a database index does not store the values in the other columns of the same table. For example, if we create an index on the Employee_Name column, this means that the Employee_Age and Employee_Address column values are not also stored in the index. If we did just store all the other columns in the index, then it would be just like creating another copy of the entire table – which would take up way too much space and would be very inefficient.
So, now you know that a database index is created on a column in a table, and that the index stores the values in that specific column. But, it is important to understand that a database index does not store the values in the other columns of the same table. For example, if we create an index on the Employee_Name column, this means that the Employee_Age and Employee_Address column values are not also stored in the index. If we did just store all the other columns in the index, then it would be just like creating another copy of the entire table – which would take up way too much space and would be very inefficient.
41.An index also stores a pointer to the table row
So, the question is if the value that we are looking for is found in an index (like ‘Jesus’) , how does it find the other values that are in the same row (like the address of Jesus and his age)? Well, it’s quite simple – database indexes also storepointers to the corresponding rows in the table. A pointer is just a reference to a place in memory where the row data is stored on disk. So, in addition to the column value that is stored in the index, a pointer to the row in the table where that value lives is also stored in the index. This means that one of the values (or nodes) in the index for an Employee_Name could be something like (“Jesus”, 0×82829), where 0×82829 is the address on disk (the pointer) where the row data for “Jesus” is stored. Without that pointer all you would have is a single value, which would be meaningless because you would not be able to retrieve the other values in the same row – like the address and the age of an employee.
So, the question is if the value that we are looking for is found in an index (like ‘Jesus’) , how does it find the other values that are in the same row (like the address of Jesus and his age)? Well, it’s quite simple – database indexes also storepointers to the corresponding rows in the table. A pointer is just a reference to a place in memory where the row data is stored on disk. So, in addition to the column value that is stored in the index, a pointer to the row in the table where that value lives is also stored in the index. This means that one of the values (or nodes) in the index for an Employee_Name could be something like (“Jesus”, 0×82829), where 0×82829 is the address on disk (the pointer) where the row data for “Jesus” is stored. Without that pointer all you would have is a single value, which would be meaningless because you would not be able to retrieve the other values in the same row – like the address and the age of an employee.
42.How does a database know when to use an index?
When a query like “SELECT * FROM Employee WHERE Employee_Name = ‘Jesus’ ” is run, the database will check to see if there is an index on the column(s) being queried. Assuming the Employee_Name column does have an index created on it, the database will have to decide whether it actually makes sense to use the index to find the values being searched – because there are some scenarios where it is actually less efficient to use the database index, and more efficient just to scan the entire table. Read this article to understand more about those scenarios: Selectivity in SQL.
When a query like “SELECT * FROM Employee WHERE Employee_Name = ‘Jesus’ ” is run, the database will check to see if there is an index on the column(s) being queried. Assuming the Employee_Name column does have an index created on it, the database will have to decide whether it actually makes sense to use the index to find the values being searched – because there are some scenarios where it is actually less efficient to use the database index, and more efficient just to scan the entire table. Read this article to understand more about those scenarios: Selectivity in SQL.
43.Can you force the database to use an index on a query?
Generally, you will not tell the database when to actually use an index – that decision will be made by the database itself. Although it is worth noting that in most databases (like Oracle and MySQL), you can actually specify that you want the index to be used.
Generally, you will not tell the database when to actually use an index – that decision will be made by the database itself. Although it is worth noting that in most databases (like Oracle and MySQL), you can actually specify that you want the index to be used.
44.How to create an index in SQL:
Here’s what the actual SQL would look like to create an index on the Employee_Name column from our example earlier:
CREATE INDEX name_index
ON Employee (Employee_Name)
Here’s what the actual SQL would look like to create an index on the Employee_Name column from our example earlier:
CREATE INDEX name_index
ON Employee (Employee_Name)
45.How to create a multi-column index in SQL:
We could also create an index on two of the columns in the Employee table , as shown in this SQL:
CREATE INDEX name_index
ON Employee (Employee_Name, Employee_Age)
We could also create an index on two of the columns in the Employee table , as shown in this SQL:
CREATE INDEX name_index
ON Employee (Employee_Name, Employee_Age)
46.What is a good analogy for a database index?
A very good analogy is to think of a database index as an index in a book. If you have a book about dogs and you are looking for the section on Golden Retrievers, then why would you flip through the entire book – which is the equivalent of a full table scan in database terminology – when you can just go to the index at the back of the book, which will tell you the exact pages where you can find information on Golden Retrievers. Similarly, as a book index contains a page number, a database index contains a pointer to the row containing the value that you are searching for in your SQL.
A very good analogy is to think of a database index as an index in a book. If you have a book about dogs and you are looking for the section on Golden Retrievers, then why would you flip through the entire book – which is the equivalent of a full table scan in database terminology – when you can just go to the index at the back of the book, which will tell you the exact pages where you can find information on Golden Retrievers. Similarly, as a book index contains a page number, a database index contains a pointer to the row containing the value that you are searching for in your SQL.
47.What is the cost of having a database index?
So, what are some of the disadvantages of having a database index? Well, for one thing it takes up space – and the larger your table, the larger your index. Another performance hit with indexes is the fact that whenever you add, delete, or update rows in the corresponding table, the same operations will have to be done to your index. Remember that an index needs to contain the same up to the minute data as whatever is in the table column(s) that the index covers.
As a general rule, an index should only be created on a table if the data in the indexed column will be queried frequently.
As a general rule, an index should only be created on a table if the data in the indexed column will be queried frequently.
48.What are DMVs?
- Dynamic Management Views (DMVs), are functions that give you information on the state of the server. DMVs, for the most part, are used to monitor the health of a server. They really just give you a snapshot of what’s going on inside the server. They let you monitor the health of a server instance, troubleshoot major problems and tune the server to increase performance.
- Define a temp table - In a nutshell, a temp table is a temporary storage structure. What does that mean? Basically, you can use a temp table to store data temporarily so you can manipulate and change it before it reaches its destination format.
49.What’s the difference between a local temp table and a global temp table? -
Local tables are accessible to a current user connected to the server. These tables disappear once the user has disconnected from the server. Global temp tables, on the other hand, are available to all users regardless of the connection. These tables stay active until all the global connections are closed.
Local tables are accessible to a current user connected to the server. These tables disappear once the user has disconnected from the server. Global temp tables, on the other hand, are available to all users regardless of the connection. These tables stay active until all the global connections are closed.
50.How do you use transactions?
In general, there are three types of transactions that you can use in the SQL Server environment: BEGIN TRANSACTION, ROLL BACK TRANSACTION and COMMIT TRANSACTION. The gist behind deploying transactions is that they allow you to group multiple SQL commands into a single unit. From there, each transaction begins with a certain task, and ends when all the tasks within the transaction are complete. BEGIN TRANSACTION gets the ball rolling. ROLLBACK TRANSACTION functions a lot like an “undo” command, and COMMIT TRANSACTION completes all of the tasks within that transaction.
In general, there are three types of transactions that you can use in the SQL Server environment: BEGIN TRANSACTION, ROLL BACK TRANSACTION and COMMIT TRANSACTION. The gist behind deploying transactions is that they allow you to group multiple SQL commands into a single unit. From there, each transaction begins with a certain task, and ends when all the tasks within the transaction are complete. BEGIN TRANSACTION gets the ball rolling. ROLLBACK TRANSACTION functions a lot like an “undo” command, and COMMIT TRANSACTION completes all of the tasks within that transaction.
51.What’s the difference between a clustered and a non-clustered index?
A clustered index directly affects the way tabled data is stored on a specific disk. This means that when a clustered index is used, data is stored in sequential rows based on the index column value. This is why a table can only contain a single clustered index. Non-clustered indexes directly affect the way physical data is stored and managed within SQL Server.
A clustered index directly affects the way tabled data is stored on a specific disk. This means that when a clustered index is used, data is stored in sequential rows based on the index column value. This is why a table can only contain a single clustered index. Non-clustered indexes directly affect the way physical data is stored and managed within SQL Server.
52.What are DBCC commands?
- In very basic terms the Database Consistency Checker (DBCC)is used to aid in server maintenance. DBCC commands, many of which are completely undocumented, provide a set of commands that let you perform routing maintenance, status and validation checks. The most common DBCC commands are: DBCC CHECKALLOC (Lets you check disk allocation); DBCC OPENTRAN (Lets you check any open transactions); and DBCC HELP (shows a list of available DBCC commands to aid your server maintenance processes).
- Describe the difference between truncate and delete - The difference between these two processes is fairly simple. Truncate means to simply empty out a table. On the other hand, the delete command lets you delete entire rows from within a table, but not all of the data within that table.
53.What is a view?
A view is simply a virtual table that is made up of elements of multiple physical or “real” tables. Views are most commonly used to join multiple tables together, or control access to any tables existing in background server processes.
A view is simply a virtual table that is made up of elements of multiple physical or “real” tables. Views are most commonly used to join multiple tables together, or control access to any tables existing in background server processes.
54.What is a Query Execution Plan?
SQL Server has several built-in tools that optimize how queries are executed within their databases. A query execution plan is exactly what it sounds like – a snapshot of how the optimizing tools will execute and deploy specific queries within the database. This service helps you troubleshoot problems with jobs that don’t necessarily execute perfectly.
SQL Server has several built-in tools that optimize how queries are executed within their databases. A query execution plan is exactly what it sounds like – a snapshot of how the optimizing tools will execute and deploy specific queries within the database. This service helps you troubleshoot problems with jobs that don’t necessarily execute perfectly.
FabGreen Technologies-Madivala
FabGreenTech.com
FabGreenTech.com
No comments:
Post a Comment