- What are the various tools you used for Sql Server Query optimization/Performance tuning
- Explain how do you optimize Server for better performance
- Explain how do you optimize Database for better performance ( Explain in details about, what all we can do with TempDB for better performance)
- Explain how do you optimize Query for better performance
- Do you use any DMVs to know about the high cost query and to find any blockings issues in your server/ database.
- What are the various templates in profiler
- What are the various events in profiler
- What are the various filters you have used in profiler
- How/ where do you export the traces captured from profiler.
- What is the permission required to run profiler.
- Estimated execution plan Vs Actual execution plan.
- How do you read the execution plan
- Type of execution plan ( formats)
- What are the things in Execution plan which you look very first while tuning a query.
- What is Table scan, Index scan, index seek
- What do you do when you see a Table scan in Execution plan.
- What is Hash Join, Merge Join, Nested Loop
- What is CPU Cost , IO Cost, Query Cost
- For better performance what will be your choice among Temp table and Table variable, and Justify your answer.
- What are the best practices you always follow while creating a SP
- How do you find if any index is required to get better performance from a query.
- Why it is a bad practice to start the name of SP with ‘SP_*******’
- What is Dynamic SQL, what is Pros and Cons of Dynamic SQL.
- What is the Usage of SET STATISTICS IO and SET STATISTICS TIME
- What is a parameter sniffing.
- Is it a good practice to call the UDF in where clause.
- What is logical reads, how do you reduce the logical reads, Explain with examples.
- What is fill factor, fragmentation, index rebuilt, index re-organize.
SQL Diary & my notes 