TOP 14 SQL SERVER PERFORMANCE TUNING INTERVIEW QUESTIONS PDF FOR EXPERIENCED

SQL SERVER PERFORMANCE TUNING INTERVIEW QUESTIONS PDF FOR EXPERIENCED

1). What is SQL Profiler?
Ans: Microsoft SQL Server Profiler is a rich graphical user interface to SQL Trace for monitoring T-SQL Statements of Database Engine. The events are saved in a trace file that can later be analysed or used to replay a specific series of steps when trying to diagnose a problem.

2). What is the use of Profiler?
Ans: The functions SQL Server Profiler tool can perform have been listed below:
         Creating trace
         Watching trace
         Storing trace
         Replaying trace

3). What do you understand by database tuning advisor?
Ans: Database Engine Tuning Advisor examines how queries are processed in the databases you specify, and then recommends how you can improve query processing performance by modifying database structures such as indexes, indexed views, and partitioning.

4).  What is an execution plan?
Ans: Execution plan is nothing but sql server provide an interface to check how a query was executed. By execution plan you can know why a given query is performing thousands of scans and you can modify your query accordingly and make it fast.

5). Command/query used for clear procedure cache?
Ans: DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

6). How you will make CURSOR fast?
Ans: There are many way, one of them is by using "LOCAL" AND "FAST_FORWARD" keywords at the declaration time.
like : Declare cr Cursor LOCAL FAST_FORWARD
FOR <your select statement>
Note: Fast_Forward: if your cursor is read-only then use it.

7). How can you optimize stored procedures?
1. Use the SET NOCOUNT ON statement
2. Use the schema name with the object name
3. Do not use the prefix "sp_" in the name
4. Use IF EXISTS (SELECT 1) instead of SELECT *
5. Avoid using cursors
6. Keep the transactions short as possible.
- Use sp_executesql instead of the EXEC statement for dynamic SQL

8). Why to avoid naming stored procs with "sp_" prefix - Slows down execution
- Causes SQL Server to search for the stored proc in the master db

9). What is the difference in the performance between INSERT TOP (N) INTO Table and using TOP with INSERT? 
INSERT TOP(N) is faster, but ignores ORDER BY

10). How to Stop Log File Growing too Big? 
If your Transaction Log file was growing too big and you wanted to manage its size, then instead of truncating transaction log file, you should choose one of the options mentioned below.
1) Convert the Recovery Model to Simple Recovery
If you change your recovery model to Simple Recovery Model, then you will not encounter the extraordinary growth of your log file. However, please note if you have one long running transaction it will for sure grow your log file till the transaction is complete.
2) Start Taking Transaction Log Backup
In this Full Recovery Model, your transaction log will grow until you take a backup of it. You need to take the T-Log Backup at a regular interval. This way, your log would not grow beyond some limits.

11). Suppose that you have 100 lines of query ,this query is executed successfully with in 5 min ,next day again you have to run the same query that time query is take a long time around 1 hour ,So how can you analyze the query..(what happen query performance is decreased)
Ans: You can explain your own way, but what I think that there would be some memory allocation problem. So first I will check is there any other users allocated more memory in the server, if yes try when sever has sufficient memory. Next check when the tables got analyzed. Next check is there any degree of parallelism has changed. There are so many factors causing performance.

12). 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.

13). How can you get confidence with your governance strategy? 
Good reporting is the key. Pull the 35 most resource-consuming queries, the most execution #'s, the most I/O, the most CPU usage, significant events, trends in disk usage.

14). How do you optimize queries? 
You can use SQL Profiler to locate long running queries
In general, keep your DBs normalized
Move queries to stored procedures:SPs are compiled and run on the server
Limit your searches as much as possible-only grab what you need
Don't use joins if you don't have to, they are expensive. Try to join on indexed fields if you can.
Add indexes to fields you often filter by, especially if they are an Integer, Boolean, or Number
You can limit the number of rows returned, or possibly do a pagination, so you don't have to get a giant amount of rows.

15). How Fixed Length and Variable Length Data types affect performance in Sql server Explain with example?

16). What is forwarding pointer in SQL Server? How it is helpful in performance optimization?