SQL DBA Interview Questions: PER-TUNING

Performance Tuning

1. Name five different tools which can be used for performance tuning and their associated purpose.

Performance Monitor\System Monitor - Tool to capture macro level performance metrics.
Profiler - Tool to capture micro level performance metrics based on the statements issued by a login, against a database or from host name.
Server Side Trace - System objects to write the detailed statement metrics to a table or file, similar to Profiler.
Dynamic Management Views and Functions - SQL Server objects with low level metrics to provide insight into a specific portion of SQL Server i.e. the database engine, query plans, Service Broker, etc.
Management Studio's Built-In Performance Reports - Ability to capture point in time metrics as pre-defined by Microsoft.
Custom scripts - Custom scripts can be developed to monitor performance, determine IO usage, monitor fragmentation, etc. all in an effort to improve performance.
Third party applications - Performance monitoring and tuning applications from vendors in the SQL Server community.

2. What is SQL Profiler?

-SQL Server provides a graphical tool which helps system administrators to monitor T-SQL statements of Database Engine.

-SQL Profiler can capture and store data related to every event to a file or a table.

-SQL Server Profiler can be used

1. To create a trace
2. To store the trace results in a table.
3. To watch the trace results when the trace runs
4. To replay the trace results
5. To start, stop, pause, and modify the trace results

3. What are the major performance killers in SQL Server ? Or What are the 12 major performance bottlenecks in sql server ?

Below are the following major performance killers in SQL Server
1. Poor Indexing
2. Inaccurate Statistics
3. Excessive Blocking and Deadlocks.
4. Non-set-based operations. Usually T-SQL cursors.
5. Poor Query Design.
6. Poor Database Design.
7. Excessive Fragmentation.
8. Nonreusable execution plans.
9. Poor execution plan, usually caused by parameter sniffing
10. Frequent recompilation of execution plans.
11. Improper use of cursors.
12. Excessive use of improper configuration of tempDB

4. Explain the NOLOCK optimizer hint and some advantage and disadvantage of NOLOCK hint?

1. The NOLOCK query hint allows SQL Server to ignore the normal locks that are placed and held for a transaction allowing the query to complete without having to wait for the first transaction to finish and therefore release the locks.

2. This is one short term fix to help prevent locking, blocking or deadlocks.
However, when the NOLOCK hint is used, dirty data is read which can compromise the results returned to the user.

5. What are the steps you will take to improve performance of a poor performing query?

This is a very open ended question and there could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be: No indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, procedures and triggers without SET NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables. Some of the tools/ways that help you troubleshooting performance problems are: SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET STATISTICS IO ON, SQL Server Profiler, Windows NT /2000 Performance monitor, Graphical execution plan in Query Analyzer.

6. Explain three different approaches to capture a query plan.

SHOWPLAN_TEXT
SHOWPLAN_ALL
Graphical Query Plan
sys.dm_exec_query_optimizer_info
sys.dm_exec_query_plan
sys.dm_exec_query_stats

7. Name three different options to capture the input (code) for a query in SQL Server.

DBCC INPUTBUFFER
fn_get_sql
sys.dm_exec_sql_text

8. Explain query execution plan?

- The optimizer available in SQL Server optimizes the code to be effectively executed.
- A query execution plan shows how this optimizer would run the query.
- Query execution plan can be viewed by :
- Using the Show Execution Plan option available in Query Analyzer,
- Displaying Estimated Execution Plan on the query dropdown menu,
- Use the SET SHOWPLAN_TEXT ON command before running a query and capturing the execution plan event in a SQL Server Profiler trace.

9. What are the execution plan recommendations ?

Following are the execution plan cache recommendations

1. Explicitly Parametrized Variable Parts of a Query
2. Create Stored Procedures to Implement Business Functionality
3. Code with sp_executesql to avoid Stored Procedure Maintenance.
4. Implement the Prepare/Execute Model to Avoid Resending s Query String
5. Avoid Ad Hoc Queries
6. Prefer sP_execute over EXECUTE for the dynamic Queries
7. Parametrized Variable Parts of Queries with Care
8. Do not Allow Implicit Resolution of Object in Queries.

10. What do you mean by an execution plan? Why is it used? How would you view it?

a.) An execution plan can be called as a road map that graphically or textually shows the data retrieval methods which have been chosen by the SQL Server query optimizer, for a stored procedure or ad- hoc query.

b.) It is used because it is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure.

c.) There exists an option called "Show Execution Plan" in Query Analyzer. If this option is turned on, it will display query execution plan in separate window when the query is run again.

11. When is the UPDATE_STATISTICS command and when it is used?

UPDATE_STATISTICS command is used when a huge amount of data is required to process. When deletions, modification or Bulk Copy occurs on the table which is having huge amount of, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the statistics of the indexes or indexed view of the tables accordingly.

Updates query optimization required statistics updates on a table or indexed view. By default, the query optimizer updates statistics as necessary to improve the query plan; in some cases you need to improve query performance by using UPDATE STATISTICS command or thestored procedure sp_updatestats to update statistics more frequently than the default updates.

Updating statistics ensures that queries compile with up-to-date statistics.

However, updating statistics causes queries to recompile. It is recommend to not updating statistics too frequently because there is a always performance trade-off between improving query plans and the time it takes to recompile queries. UPDATE STATISTICS can use tempdb to sort the sample of rows for building statistics.

Example:
1. Updating All Statistics with sp_updatestats

EXEC sp_updatestats

2. Update all statistics on a table

UPDATE STATISTICS TableName

3. Update the statistics for an index

UPDATE STATISTICS TableName, IndexName

12. When is the UPDATE_STATISTICS command used?

- When the processing of large data is done, this command is used.
- Whenever large number of deletions, modification or copy takes place into the tables, the indexes need to be updated to take care of these changes. UPDATE_STATISTICS performs this job.

13 What is the use of UPDATE_STATISTICS command?

A This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

14. What is Statistics? Where it will be stored? How to find it?

Stats exist on every index, however stats can also exist on columns that are not indexed.

Sys.stats

UPDATE STATISTICS Sales.SalesOrderDetail;

UPDATE STATISTICS Sales.SalesOrderDetail AK_SalesOrderDetail_rowguid;

15. What is table scan?

16. Index Seek Vs. Index Scan (Table Scan)

A table scan is performed on a table which does not have an index upon it (a heap) –
it looks at the rows in the table. An index scan is performed on an indexed table – the index itself.

Index Scan retrieves all the rows from the table. Index Seek retrieves selective rows from the table.

Index Scan:

Index Seek:

Index Scan is nothing but scanning on the data pages from the first page to the last page. If there is an index on a table, and if the query is touching a larger amount of data, which means the query is retrieving more than 50 percent or 90 percent of the data, and then optimizer would just scan all the data pages to retrieve the data rows. If there is no index, then you might see a Table Scan (Index Scan) in the execution plan.

Index seeks are generally preferred for the highly selective queries. What that means is that the query is just requesting a fewer number of rows or just retrieving the other 10 (some documents says 15 percent) of the rows of the table.

In general query optimizer tries to use an Index Seek which means that optimizer has found a useful index to retrieve record set. But if it is not able to do so either because there is no index or no useful indexes on the table then SQL Server has to scan all the records that satisfy query condition.

17. Difference between Index Scan and Index Seek

Table1 has five columns: Col1, Col2, Col3, Col4, Col5
Index1 on Table1 contains two columns: Col1, Col3
Query1 on Table1 retrieves two columns: Col1, Col5

Now when Query1 is ran on Table1 it will use search predicates Col1,Col5 to figure out if it will use Index1 or not. As Col1, Col5 of Query1are not same as Col1, Col3 or Index1 there are good chances that Query1 will not use Index1. This scenario will end up in table scan. If Query1 would have used Index1 it would have resulted in table seek.

Index Scan happens when index definition cannot point close on single row to satisfy search predicates. In this case SQL Server has to scan multiple pages to find range of rows which satisfy search predicates. In case of Index Seek SQL Server finds single row matching search predicates using index definition.

18. What Happens During a Table Scan

Suppose you aren't much of an organized person, and your cubicle at work isn't really well organized. You've got papers laying all around, some filed, some stacked, some in the filing cabinet, and some in folders. Generally speaking, it's hard to find data like that; then suppose your boss asks you for all of your notes related to a specific project that you are working on. Unless you work for a very large company with ample developers and enough resources to have you dedicate yourself to one project at a time, you probably have multiple projects going on, and notes on all of them are mixed around the cubicle.

So how do you find everything?

Answer: you look through every stack, folder, drawer, and box you've got; and then hope against hope you have it all. Well, this is essentially a table scan, your cubicle is a table, and you my friend, are the Query Processing engine.

When a query is submitted to SQL Server, it attempts to determine the best way to execute a query, generating what is called a "Query Execution Plan", that describes how it will go about fulfilling the request to find every note related to the project, and return them to the boss. The reason these are typically considered a Very Bad Thing ® is because when SQL Server has to search through all of the data in a table to satisfy a query, it consumes more resources than it really needs to.

19. How to Avoid Table Scans

If you are an organized person, you keep all of your project notes neatly organized, filed away, and close to each other. SQL Server has its way of doing this as well, it's called an index; an index is a way of keeping track of what data you have, and where it resides in order to enable a fast retrieval of it when it is requested.

Now, building an index in SQL Server is easy, however knowing when and where to build them is a bit more of an art. SQL Server performance tuning is absolutely dependent on indexes, and being knowledgeable enough about how to write queries that take advantage of them is essential. Generally speaking, if a column in a table is referenced frequently in queries, then it should have an index on it, the more read intensive your database is, the more this holds true. It is not usually a good idea to have indexes on every column in a table for multiple reasons, chief among these is space considerations; under the covers, indexes are copies of the data from specific columns in a table extracted and organized for fast searching, so to build an index will take up space, roughly equivalent to the volume of data being indexed.

Another consideration to take in account when building indexes is whether or not your data is added to or updated very frequently; the more often data in a table changes, the greater the impact indexes will have in performance. Every time indexed data changes, SQL Server has to ensure that the index is kept up to date, which adds to the processing overhead which often times is overlooked in database design and performance considerations.

20. T-SQL command for Execution plan?

When SET SHOWPLAN_ALL is ON, SQL Server returns execution information for each statement without executing it, and Transact-SQL statements are not executed. After this option is set ON, information about all subsequent Transact-SQL statements are returned until the option is set OFF. For example, if a CREATE TABLE statement is executed while SET SHOWPLAN_ALL is ON, SQL Server returns an error message from a subsequent SELECT statement involving that same table, informing users that the specified table does not exist. Therefore, subsequent references to this table fail. When SET SHOWPLAN_ALL is OFF, SQL Server executes the statements without generating a report.

SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL cannot be specified inside a stored procedure; they must be the only statements in a batch.

21. How can I modify an Execution Plan to use a Seek instead of a Scan?

When SQL Server is looking for your data probably one of the largest things which will make SQL Server switch from a seek to a scan is when some of the columns are you looking for are not included in the index you want it to use. Most often this will have SQL Server fall back to doing a clustered index scan, since the Clustered index contains all the columns in the table. This is one of the biggest reasons (in my opinion at least) that we now have the ability to INCLUDE columns in an index, without adding those columns to the indexed columns of the index. By including the additional columns in the index we increase the size of the index, but we allow SQL Server to read the index, without having to go back to the clustered index, or to the table itself to get these values.

22. What is the difference between a scan and a seek?

A scan returns the entire table or index. A seek efficiently returns rows from one or more ranges of an index based on a predicate. For example, consider the following query:

select OrderDate from Orders where OrderKey = 2

Scan

With a scan, we read each row in the orders table, evaluate the predicate “where OrderKey = 2” and, if the predicate is true (i.e., if the row qualifies), return the row. In this case, we refer to the predicate as a “residual” predicate. To maximize performance, whenever possible we evaluate the residual predicate in the scan. However, if the predicate is too expensive, we may evaluate it in a separate filter iterator. The residual predicate appears in text showplan with the WHERE keyword or in XML showplan with the <Predicate> tag.

Here is the text showplan (slightly edited for brevity) for this query using a scan:

|--Table Scan(OBJECT:([ORDERS]), WHERE:([ORDERKEY]=(2)))

The following figure illustrates the scan:

Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate. However, if the table is large and if most of the rows do not qualify, we touch many more pages and rows and perform many more I/Os than is necessary.

Seek

Going back to the example, if we have an index on OrderKey, a seek may be a better plan. With a seek, we use the index to navigate directly to those rows that satisfy the predicate. In this case, we refer to the predicate as a “seek” predicate. In most cases, we do not need to re-evaluate the seek predicate as a residual predicate; the index ensures that the seek only returns rows that qualify. The seek predicate appears in the text showplan with the SEEK keyword or in XML showplan with the <SeekPredicates> tag.

Here is the text showplan for the same query using a seek:

|--Index Seek(OBJECT:([ORDERS].[OKEY_IDX]), SEEK:([ORDERKEY]=(2)) ORDERED FORWARD)

The following figure illustrates the seek:

Since a seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table. Thus, a seek is generally a more efficient strategy if we have a highly selective seek predicate; that is, if we have a seek predicate that eliminates a large fraction of the table.

A note about showplan

In showplan, we distinguish between scans and seeks as well as between scans on heaps (an object with no index), clustered indexes, and non-clustered indexes. The following table shows all of the valid combinations:

	Scan	Seek
Heap	Table Scan
Clustered Index	Clustered Index Scan	Clustered Index Seek
Non-clustered Index	Index Scan	Index Seek

23. Index Scans and Table Scans

An index scan or table scan is when SQL Server has to scan the data or index pages to find the appropriate records. A scan is the opposite of a seek, where a seek uses the index to pinpoint the records that are needed to satisfy the query. The reason you would want to find and fix your scans is because they generally require more I/O and also take longer to process. This is something you will notice with an application that grows over time. When it is first released performance is great, but over time as more data is added the index scans take longer and longer to complete.

To find these issues you can start by running Profiler or setting up a server side trace and look for statements that have high read values. Once you have identified the statements then you can look at the query plan to see if there are scans occurring.

Here is a simple query that we can run. First use Ctrl+M to turn on the actual execution plan and then execute the query.

SELECT * FROM Person.Contact

Here we can see that this query is doing a Clustered Index Scan. Since this table has a clustered index and there is not a WHERE clause SQL Server scans the entire clustered index to return all rows. So in this example there is nothing that can be done to improve this query.

In this next example I created a new copy of the Person.Contact table without a clustered index and then ran the query.

SELECT * FROM Person.Contact2

Here we can see that this query is doing a Table Scan, so when a table has a Clustered Index it will do a Clustered Index Scan and when the table does not have a clustered index it will do a Table Scan. Since this table does not have a clustered index and there is not a WHERE clause SQL Server scans the entire table to return all rows. So again in this example there is nothing that can be done to improve this query.

In this next example we include a WHERE clause for the query.

SELECT * FROM Person.Contact WHERE LastName = 'Russell'

Here we can see that we still get the Clustered Index Scan, but this time SQL Server is letting us know there is a missing index. If you right click on the query plan and select Missing Index Details you will get a new window with a script to create the missing index.

Let's do the same thing for our Person.Contact2 table.

SELECT * FROM Person.Contact2 WHERE LastName = 'Russell'

We can see that we still have the Table Scan, but SQL Server doesn't offer any suggestions on how to fix this.

Another thing you could do is use the Database Engine Tuning Advisor to see if it gives you any suggestions. If I select the query in SSMS, right click and select Analyze Query in Database Engine Tuning Advisor the tools starts up and I can select the options and start the analysis.

Below is the suggestion this tool provides and we can see that recommends creating a new index, so you can see that using both tools can be beneficial.

Create New Index

So let's create the recommended index on Person.Contact and run the query again.

USE [AdventureWorks]

CREATE NONCLUSTERED INDEX [IX_LastName]

ON [Person].[Contact] ([LastName])

SELECT * FROM Person.Contact WHERE LastName = 'Russell'

Here we can see the query plan has changed and instead of a Clustered Index Scan we now have an Index Seek which is much better. We can also see that there is now a Key Lookup operation which we will talk about in the next section.

Summary

By finding and fixing your Index Scans and Table Scans you can drastically improve performance especially for larger tables. So take the time to identify where your scans may be occurring and create the necessary indexes to solve the problem. One thing that you should be aware of is that too many indexes also causes issues, so make sure you keep a balance on how many indexes you create for a particular table.

24. Discovering Unused Indexes?

When SQL Server 2005 was introduced it added Dynamic Management Views (DMVs) that allow you to get additional insight as to what is going on within SQL Server. One of these areas is the ability to see how indexes are being used. There are two DMVs that we will discuss. Note that these views store cumulative data, so when SQL Server is restated the counters go back to zero, so be aware of this when monitoring your index usage.

DMV - sys.dm_db_index_operational_stats

This DMV allows you to see insert, update and delete information for various aspects for an index. Basically this shows how much effort was used in maintaining the index based on data changes.

If you query the table and return all columns, the output may be confusing. So the query below focuses on a few key columns. To learn more about the output for all columns you can check out Books Online.

SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME],

I.[NAME] AS [INDEX NAME],

A.LEAF_INSERT_COUNT,

A.LEAF_UPDATE_COUNT,

A.LEAF_DELETE_COUNT

FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS (db_id(),NULL,NULL,NULL ) A

INNER JOIN SYS.INDEXES AS I

ON I.[OBJECT_ID] = A.[OBJECT_ID]

AND I.INDEX_ID = A.INDEX_ID

WHERE OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

Below we can see the number of Inserts, Updates and Deletes that occurred for each index, so this shows how much work SQL Server had to do to maintain the index.

DMV - sys.dm_db_index_usage_stats

This DMV shows you how many times the index was used for user queries. Again there are several other columns that are returned if you query all columns and you can refer to Books Online for more information.

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],

I.[NAME] AS [INDEX NAME],

USER_SEEKS,

USER_SCANS,

USER_LOOKUPS,

USER_UPDATES

FROM SYS.DM_DB_INDEX_USAGE_STATS AS S

INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID

WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1

AND S.database_id = DB_ID()

Here we can see seeks, scans, lookups and updates.

The seeks refer to how many times an index seek occurred for that index. A seek is the fastest way to access the data, so this is good.
The scans refers to how many times an index scan occurred for that index. A scan is when multiple rows of data had to be searched to find the data. Scans are something you want to try to avoid.
The lookups refer to how many times the query required data to be pulled from the clustered index or the heap(does not have a clustered index). Lookups are also something you want to try to avoid.
The updates refers to how many times the index was updated due to data changes which should correspond to the first query above.

Identifying Unused Indexes

So based on the output above you should focus on the output from the second query. If you see indexes where there are no seeks, scans or lookups, but there are updates this means that SQL Server has not used the index to satisfy a query but still needs to maintain the index. Remember that the data from these DMVs is reset when SQL Server is restarted, so make sure you have collected data for a long enough period of time to determine which indexes may be good candidates to be dropped.

25. SQL Server utilizing 100% CPU

-Statistics

-I/O utilization(Waiting for I/O process)

-reasons could be due to high number of compilation or recompilation, bad query, poor cursor.

- Please see SQL Statistics: Batch Requests/sec, SQL Statistics: SQL Compilations/sec, SQL Statistics: SQL Recompilations/sec

26. SQL Server Performance Tips?

SQL Server Performance Tips

SQL Server performance tuning can consume a considerable amount of time and effort. The following list is a quick guideline that you should keep in mind when designing and developing SQL Server database applications:

User Defined Functions (UDF)

Refrain from using user defined functions (UDF) in a select statement that may potentially return many records. UDFs are executed as many times as there are rows in a returned result. A query that returns 100,000 rows calls the UDF 100,000 times.

SQL Server table indexes

Create SQL statements that utilize defined table indexes. Using indexes minimizes the amount of table scan which in most cases will be much slower than an index scan.

Multiple disks

The single best performance increase on a SQL Server computer comes from spreading I/O among multiple drives. Adding memory is a close second. Having many smaller drives is better than having one large drive for SQL Server machines. Even though the seek time is faster in larger drives, you will still get a tremendous performance improvement by spreading files, tables, and logs among more than one drive.

Disk controllers

Different disk controllers and drivers use different amounts of CPU time to perform disk I/O. Efficient controllers and drivers use less time, leaving more processing time available for user applications and increasing overall throughput.

SQL Server foreign keys

Ensure that all your tables are linked with foreign keys. foreign keys enhance the performance of queries with joins. Database tables inside each application are naturally related. Islands of tables are rarely needed if your application’s business logic is well defined.

SQL Server primary keys

Ensure that every table has a primary key. if you can’t find a natural set of columns to serve as a primary key, create a new column and make it a primary key on the table.

Processor (CPU)

When you examine processor usage, consider the type of work the instance of SQL Server is performing. If SQL Server is performing a lot of calculations, such as queries involving aggregates or memory-bound queries that require no disk I/O, 100 percent of the processor’s time can be used. If this causes the performance of other applications to suffer, try changing the workload of the queries with aggregates.

27, "What is difference between Session.Clear and Session.abandon?"

A Session.Clear() will clear the contents of the session.

Session.abandon() will clear the contents of the session. It also terminates that session. Generally when we wants to end the current session, we will call abandon() method. Hence, abandon() method fires session_end event also.

28. What is an execution plan in sql server ?

SQL query define what you want to do but does not tell to the server how to do it. Using an SQL query, for instance you write a query to retrieve all employee working in Neekhra Technologies Pvt. Ltd. When the server receives the statement, the first thing it does is to parse it [Lexical Analysis]. If there is no syntax error, the server continue to execute your request. It will decide the best way to compute the results. The server chooses whether it is best way to read completely the table of employees, or whether using an index on the employee id column would be faster. It compares the cost of all possible approaches. The way that a statement can be physically executed is called an execution plan or a query plan.

The part of the sql server database that is responsible for computing the optimal execution plan is called the query optimizer. The query optimizer takes its decision on its knowledge of the database content and query optimizer elements available in the database/tables.

29. How to do health checkups for servers can explain?

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.

1. Sql server services status

2. Database status and utilization
3. Read errorlog
3. Blocking and locking status
4. Long running or open transactions
5. Cpu and memory utilization

30. what is """aba_lockinfo""""in sql server dba?

Aba_lockinfo - New Version Available

If you are using my lock-monitoring procedure aba_lockinfo, there is now a
new version available at http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
I recommend that you replace your existing version with this one.

Functionally, there are only minor difference, but the older version did
not have acceptable performance when there were a large number of locks.
This version is designed to be more lean on resources.

If you are using text mode from Query Analyzer, there is a new parameter
@fancy that may be of interest to you. The default is zero, but if you
set it to 1, aba_lockinfo dynamically sets the column widths, and you also
get a blank line between processes. (This requires some extra steps, which
it is not the default.) In grid mode, @fancy does not matter much.

31. What is CTE (Common Table Expression)?

· When a complex SQL statement has number of joins then it can be made easier by using Common Table Expression.

· Consider the following SQL statement.

SELECT * FROM (
SELECT emp.EmpName,dept.Department,emp.Gender FROM Employee emp
left join Department dept on emp.DeptID = dept.DeptID) E
WHERE E.Gender = ‘Male’
ORDER BY T.EmpName

The syntax of CTE is as follow

- The CTE Name (followed by WITH keyword)
- The Column List (Optional)
- The Query (Appears within parentheses after the AS keyword)

If we write the above messy query using CTE it would be like

With E(EmpName, Department, Gender)
AS
(
SELECT emp.EmpName,dept.Department,emp.Gender FROM Employee emp
left join Department dept on emp.DeptID = dept.DeptID
)
SELECT * FROM E
WHERE E.Gender = ‘Male’
ORDER BY E.EmpName

This way the query can be made more readable and easy to understand.

32. How can i find deleted rows in a table ?

-Verify audit tables for the particular table

-If no log backups happend after deletion then use fn_dblog function to read log

-If log backup are taken after deletion use fn_dump_dblog which reads information from log backups

33. What is the Difference between a Truncate command and Delete command?

-DELETE returns numbers of rows that is deleted, whereas in TRUNCATE does not return any such value.
-Truncate can not be rolled back while Delete can be.
-You can put a where clause in DELETE but in TRUNCATE where clause cannot be used, hence the whole table data is truncated.

-DELETE does not reset the Identity value but TRUNCATE reset the Identity value to first value.
-Truncate keeps the lock on table while Delete keeps the lock on each row.
-Trigger is not fired in Truncate while it happens in Delete.

34. What is meaning of NOLOCK Hint in a Select query?

ANSWER:

It means that the query will read the uncommitted data and hence will not wait for the table to unlock itself to return the result set.

35. What is a Fillfactor?

A fillfactor is a percentage which we put while creating an Index as how much free space we want to keep in sql page. If while creating an Index we put the fill factor as 50%, then the page will be 50% filled and it will allow inserting up to 50% of the data, before splitting the page.

The 'fill factor' option indicate how full SQL Server will create each index page.
When the index page doesn’t have free space for inserting a new row, SQL Server will create new index page and transfer some rows from the previous index page to the new index page. This process is called page split.
If we want to reduce the number of page splits then we can use Fill factor option. Using Fill factor SQL will reserve some space on each index page.
The fill factor is a value from 1 through 100 that indicates the percentage of the index page to be left empty. The default value for fill factor is 0.
If the table contains the data which is not changed frequently then we can set the fill factor option to 100. When the table's data is modified frequently, we can set the fill factor option to 80% or as we want.

36. What is a WITH(NOLOCK)?

WITH(NOLOCK) is used to unlock the data which is locked by the transaction that is not yet committed. This command is used before SELECT statement.
When the transaction is committed or rolled back then there is no need to use NOLOCK function because the data is already released by the committed transaction.
Syntax: WITH(NOLOCK)
Example:
SELECT * FROM EmpDetails WITH(NOLOCK)
WITH(NOLCOK) is similar as READ UNCOMMITTED

37. What are Magic tables in SQL Server?

-In SQL Server there are two system tables “Inserted” and “Deleted” called Magic tables.

-These are not the physical tables but the virtual tables generally used with the triggers to retrieve the inserted, deleted or updated rows.

-When a record is inserted in the table that record will be there on INSERTED Magic table.

-When a record is updated in the table that existing record will be there on DELETED Magic table and modified data will be there in INSERTED Magic table.

-When a record is deleted from that table that record will be there on DELETED Magic table.

38. Differentiate between a Local and a Global temporary table?

- A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.
- Global temporary tables (created with a double “##”) are visible to all sessions.
- Global temporary tables are dropped when the session that created it ends, and all other sessions have stopped referencing it.

39. Can you explain about buffer cash and log Cache in sql server?

Buffer Cache: Buffer cache is a memory pool in which data pages are read. It performance of the buffer cache is indicated as follows: 95% indicates that pages that were found in the memory are 95% of time. Another 5% is needed for physical disk access. If the value falls below 90%, it is the indication of more physical memory requirement on the server.

Log Caches: Log cache is a memory pool used to read and write the log pages. A set of cache pages are available in each log cache. The synchronization is reduced between log and data buffers by managing log caches separately from the buffer cache.

40. What are Sparse columns?

Sparse column is a type of column which is used to optimize storage for null values.
When a column there is big number of null then by defining that column as spars column we can save a large amount of disk space.
The drawback of sparse column is that it requires more space for the non null values. When we define a column as sparse it requires additional 4 Byte for not null values.
For example, a DATETIME column in a non-sparse column required 8 bytes of storage whereas if we define that column as a sparse column then it will require 12 bytes.
It is not possible to set ROWGUIDCOL and IDENTITY properties in sparse column.

41. Difference between estimated execution plan & actual execution plan
estimated execution plan does't run query & actual execution plan is runs query returns also.

42. SQL SERVER – Wait Types

When I was working with couple of frequent blocking issue in our production servers, I have noticed different types of wait resources which are very common and can be very useful but people often overlooked these while troubleshooting. I also feel that "wait stats" is one of the most under-utilized performance troubleshooting methodologies in the SQL Server.

Any time a user connection is waiting, SQL Server keep track of wait time. For example, the application requests resources such as I/O, locks, or memory and can wait for the resource to be available.

There are few system wait types which are always present and there are a few wait types which can really cause a heavy bottleneck for your system.

Types of Waits

As per BOL, there are three types of wait types, namely:

Resource waits

Resource waits occur when a worker requests access to a resource that is not available because the resource is being used by some other worker or is not yet available. Examples of resource waits are locks, latches, network and disk I/O waits. Lock and latch waits are waits on synchronization objects

Queue waits

Queue waits occur when a worker is idle, waiting for work to be assigned. Queue waits are most typically seen with system background tasks such as the deadlock monitor and deleted record cleanup tasks. These tasks will wait for work requests to be placed into a work queue. Queue waits may also periodically become active even if no new packets have been put on the queue.

External waits

External waits occur when a SQL Server worker is waiting for an external event, such as an extended stored procedure call or a linked server query, to finish. When you diagnose blocking issues, remember that external waits do not always imply that the worker is idle, because the worker may actively be running some external code.

To check the wait types for any server, just run the following simple T-SQL:

SELECT * FROM sys.dm_os_wait_stats

43. SQL Server Memory Nodes – How many do I have?

SQL Server Memory Nodes serve the foundation for SQL Server Memory and they have one-to-one mapping with physical NUMA nodes. For example, if your system has four physical NUMA nodes, your SQL instance will have four Memory Nodes. In case your system does not have NUMA, SQL instance will still have one Memory Node, node 0. Additionally, there will always be one dedicated node for DAC (Dedicated Administrator Connection).

How can you find how many nodes your SQL instance has?

Select * from dm_os_nodes

How can you track statistics for each node?

There is perfmon object SQL Server:: Memory Node (I guess, this is a new object since SQL Server 2012)

You can also track node information through DBCC MEMORYSTATUS.

44.

SQL DBA Interview Questions

Scroll Bar

PER-TUNING

No comments:

Post a Comment

DisableRC