Performance Tuning- Question and Answer
What is SQL Performance Tuning?
- Performance tuning improves system performance.
- It is a process of ensuring that the SQL statements issued by an application run in the fastest possible time.
Why is SQL Performance Tuning Required?
- SQL Statements are used to retrieve data from the database. We can get the same results by writing different SQL queries. But we need to optimize the queries for better performance.
- By tuning the performance of the queries, users benefit as they get faster results and can take quick decisions.
- The general goal of SQL performance tuning is to decrease response time, increase throughput.
What are tips to improve the performance of SQL queries?
- Use numeric as compared to the text data type.
- The unique key improves table scan performance.
- Choose a table scan for small records and seek-scan for numerous records.
- Keep index size as small as possible.
- Use an indexed view for aggregated SQL queries.
- Use the covering index to reduce RID (Row Identifier) lookup.
- Prefer to use views and stored procedures for writing long queries.
It will also help in minimizing network load.
- Make use of the SET NOCOUNT ON clause while building stored procedures. It reduces network traffic.
What is table scan?
- The table scan scans the records row by row until it finds the records and after finding the records, it gives to the user. So it can be difficult to search for numerous records.
What is seek scan?
- The seek-scan works on the index it uses the B-Tree structure to search the value.
What is Row Identifier(RID) Lookup?
- A RID is a lookup it a physical operator. The lookup occurs when a Non-clustered index created on a table.
- RID is a row locator that includes information about the location of that record such as the database file, the page, the slot numbers that helps to identify the location of the row quickly.
What is the Key Lookup operator?
- A Key Lookup occurs when data is found in a non-clustered index.
- If the table does not have a clustered index then a RID lookup occurs instead.
- The key lookup operator is the clustered equivalent of the RID lookup operator.
What is SQL Query Optimization?
- Query optimization is the process of writing the query in a way so that it could execute quickly.
What is an Execution Plan?
- The execution plan is nothing but the SQL server provides an interface to check how a query was executed.
- By execution plan, we can know why a given query is performing thousands of scans and we can modify your query accordingly and make it fast.
What is the aggregate operator in the Execution Plan?
- The Aggregate Operator is mainly used to calculate the aggregate expressions in the submitted query, by grouping the values of an aggregated column.
- The aggregate expressions include the MIN, MAX, COUNT, AVG, SUM operations
What is the SQL Profiler?
- SQL Server Profiler is a tool for tracing, recreating, and troubleshooting problems in SQL Server. SQL Server profiler creates, handles, and analyzes trace results.
How to run SQL Profiler explain steps in detail?
Step 1: Generate a trace/ load file for the selected database
- In the SQL Server Management Studio goto tools option and click on SQL Server Profiler.
- By using the server type and server name connect to the SQL Server Profiler.
- In the trace property window, select a template and provide a database name in the column filter option.
- Finally, trace files for query optimization to click on the run button. Save the trace file.
Step 2: Put that load file on database tuning advisor
- In the SQL Server Profiler open Database Engine Tuning Advisor.
- In the general tab, select the file, and database. Click on Start Analysis.
Step 3: Check the suggestions/definition made by tuning advisor
- In the recommendations tab, we can see the results. It shows how much improvement can be done.
- In the report tab, we can see suggestions like Statement cost report, Event frequency report, Index detail report, and so on.
Step 4: Implement those in the database table
- After analyzing we need to implement those in the database table for the performance improvement.
What is the command used for clear procedure cache?
- DBCC FREEPROCCACHE to clear the procedure cache.
- DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. It serves to the empty data cache.
Define SET STATISTICS IO ON and SET STATISTICS TIME statement?
- SET STATISTICS IO ON statement displays the amount of disk activity generated by the query.
- When STATISTICS IO is ON, Statistical information is displayed, and when OFF, the information isn’t displayed.
- SET STATISTICS TIME displays the amount of time needed to parse, compile, and execute each statement in the query.
Explain query plan in SQL Server?
- Execute the SQL statement has a three-step process:
PARSE:- When we execute the SQL statement firstly SQL engine checks the syntax like compilation error, etc.
OPTIMIZER:- Optimizer takes the data and analyzes data statistics. Thereafter depending on data statistics and query processing tree, it generates estimates plan.
EXECUTE:- Estimated plan fetches the SQL engine for execution. After execution, the actual plan is prepared.
What is optimizer?
- The optimizer is the most efficient way of processing the query. The optimizer is nothing but the execution of a query in an optimum manner.
- SQL parser, SQL optimizer, and a source code generator compile the SQL statement.
What are the types of Optimizer?
There are two types of optimizer:
- Rule-based optimizer
- Cost-based optimizer
What is Logical and Physical Operators?
- The logical operator has a conceptual plan whereas the physical operator has actual logic.
Explain execution plans?
Display Estimated Execution Plan:-
- The estimated plan will provide you how your query will execute on the server without actually running it.
Include Actual Execution Plan:-
- The actual plan shows what happened when the server executed the query.
Define Physical and Logical Read?
- When data read on the actual database then it called physical read.
- When data read on cache then it called logical read.
Explain the concept of Fragmentation?
- Fragmentation has numerous effects that can occur because of data modification.
- Fragmentation means your physical data and indexes are out of synchronous storing data non-contiguously on disk is known as Fragmentation.
- We can detect fragmentation by using system function sys.dm_db_index_physical_stats.
- To remove fragmentation goto object explorer ab=nd open database as well table. Right-click on the fragmented index and select REORGANIZE or REBUILD. Then click on ok.
What is the difference between REORGANIZE and REBUILD?
- Rebuild is also a DML operation that changes the system tables, affects statistics, enables disabled indexes, etc. Whereas Reorganize is a pure cleanup operation that leaves all system state as it is.
- Rebuild locks the table for the whole operation period and Reorganize doesn’t lock the table.
- Rebuilds one or more indexes for a table in the specified database. Reorganize index defragments clustered and secondary indexes of the specified table.
What are the two ways of covering index?
Covering index can be created in two ways:
- Composite key
- Include Keyboard
Include is the best way of covering index because:
- We can not create composite indexes on data types like varchar(max), XML, etc.
- The size of the index increases the key size which further impacts performances.
- It can exceed the constraint of the index key size of 900 bytes.
What is Extended Events?
- An extended event is a lightweight performance monitoring system that enables users to collect data needed to monitor and troubleshoot problems in SQL Server.
- By using the extended events, we can see details about the inner operations of the SQL system and your application.
- It was a very useful tool to see in real-time what SQL queries are being executed against your database.
What are the default sessions in extended events?
There are three default sessions:
Why the stored procedure performs better than inline queries?
- Stored procedures are precompiled and cached so the performance is much better.
- It is easier to troubleshoot a stored procedure than inline queries.
When SP:CacheInsert and SP:CacheHit occur?
- If the plan is created and inserted into the cache then SP:CacheInsert event occurs.
- If the plan selected from the cache and find procedure then SP:CacheHit event occurs.
What is Resource Governor?
- Resource Governor is a feature that can manage SQL Server Workload and System Resource Consumption.
- There are different nature of workloads like heavy, light workloads.
- If heavy workload takes all the memory from CPU and because of that other operations to get affected then it is a very bad thing.
- Resource governor is used for how much memory and CPU should be allocated to the resources.
What is the Query Store?
- A query store will store multiple execution plans of the same query. so we can analyze different execution plans for the same query.
- We can quickly recall a procedure cache. Procedure cache also stores the latest execution plan only.
- These plans stored in the procedure cache will be wiped out due to memory pressure which is not the case with the query store.
How to enable Query Store?
There are two ways to enable query store:
- ALTER DATABASE database_name SET QUERY_STORE= ON;
- In the SQL Server Management Studio, Right-click on the database on which you want to enable the query store.
- In the database property, goto query store option.
- In the general tab, set Operation Mode is off and Operation Mode is ReadWrite. Then click on ok.
What is the Unused Indexes?
- Unused index means wast of space and this means your backups will take more time and when the backup is taken all the queries run slower.
What is Database Engine Tuning Advisor?
- Another important technique for SQL performance tuning is to analyze the performance of Transact-SQL statements that are run against the database you intend to tune.
- We can use the Database Engine Tuning Advisor to analyze the performance implications.
What are Do’s and Don’ts for faster SQL queries?
- Use temp tables to improve cursor performance.
- Use partitioning to avoid large data moves.
- Use the EXISTS command instead of DISTINCT when using table joins for tables with one-to-many relationships.
- Select appropriate data type and use the appropriate naming conventions.
- Use normalization, primary key, and table-valued functions.
- Create and use the index.
- Don’t use triggers and avoid multiple joins.
- Don’t count all rows if you only need to see if data exists.
- Try not to use having a clause in select statements.
- Avoid using updates instead of a case.
- Never Use ”Sp_” for users to define the stored procedures.
Where will the generated execution plan be stored?
- The SQL Server Execution Plan will be stored in the Plan Cache memory storage.
What is the correct way of reading the execution plan?
- The correct way to read the execution plan is to start from the right side of the plan to the left side and from the top to the bottom.
- The most left SELECT operator contains the final result of the submitted query.
Can the estimated SQL execution plan result be trusted?
- This depends on the statistics. If it is updated, the result should be the same.
- When query takes a long time to execute and we need to troubleshoot it then SQL execution plan is needed.
What is the concatenation operator in the execution plan?
- The concatenation operator takes one or more data sets in sequence as inputs and returns all records from all the input data set for example- UNION ALL statement.
What is the ASSERT operator in the execution plan?
- The ASSERT operator will verify whether the inserted values meet the defined CHECK or FOREIGN KEY constraint on the table called by the query.
If you are a newbie to database learning — SQL Server recommended is the following must-watch video: -