SQL Server Performance Tuning Question and Answers
Q. What are the tools available for performance tuning/monitoring?
A: Here are the few tools list:
-Performance Studio: Act as a Central Data Repository, Collect Selected SQL Server Performance Data and Display Performance Reports
-Activity Monitor: It displays graphically about Processes, Resource Waits, Datafile I/O, Recent expensive Quires.
-Database Tuning Advisor (DTA): Recommend indexes
-Profiler: Can run traces and find out the expensive/long running quires/transactions
-Execution Plans: There are three types Graphical, Text and XML.
-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
-PerfMon: Windows native tool to view / monitor the performance of both sql and windows servers
Q. What is the process of tuning the Performance?
A: There is no specific process its all depends upon the kind of issue:
-Identification – Use native tools like Profiler, Query Tuning Advisor, Query Execution Plans, Performance Monitor, system stored procedures, dynamic management views, custom stored procedures or third party tools.
-Analysis – Analyze the data to determine the core problems.
-Providing Solution –
Creating new index on appropriate columns
Altering the complex quires to make them use the existing indexes.
By Updating Statistics for Tables and Views.
By Rebuilding and Reorganizing indexes.
By Resolving blocking problems.
By removing Deadlocks.
-Testing – Test the various options to ensure they perform better and do not cause worse performance in other portions of the application
-Knowledge sharing – Share your experience with the team to ensure they understand the problem and solution, so the issue does not occur again
Q. How do you troubleshoot performance related problems in SQL Server?
A: There are different ways of troubleshooting performance issues and there is no right or wrong way of troubleshooting performance issues, it is just my way of approaching in identifying the problem and then taking corrective actions.
– First try to understand more details about the performance issues. Normally performance problems are reported by Developers or Client as per feedback from end users or some monitoring tools reporting specific performance issue on the server.
– Ask Developers or Client questions to understand what they mean by performance problems and gather mode details to pinpoint issue to a specific application or a specific functionality of the application.
– Troubleshooting approach will differ for general performance problems affecting whole application or more specific problem like slowness with specific Stored Procedure or Query. Next steps are specific to general performance troubleshooting.
– Check the overall hardware resource usage like, CPU usage on the server, Memory usage on the server, I/O usage on the server and Network usage to see if there is any spikes in any of their usage. If yes, then drill further down in that direction, if every thing looks normal, then will proceed with checking at SQL Server level.
– Check SQL Server errorlogs and eventlogs for any errors.
– Check for any blocking or heavy locking or high number of suspended sessions.
– Check wait stats to see the top waits.
– Checking if there are regular maintenance on the SQL Server like rebuilding indexes and update of statistics. If not, then will implement those which will significantly improve the performance.
– Will run DMV’s to identify Top Duration, Top CPU, Top Read or Write intensive queries and try to tune them by creating appropriate indexes or report them to developer suggesting to re-write those queries.
– Checking for SQL configuration settings like, MaxDoP, SQL Max Server Memory, Lock Pages in Memory, Instant File Initialization, Auto-Growth settings, etc.
Q. What kind of Ques you should ask to Developers or reporter to better understand more about the performance issue?
A: – What kind of performance issue are you seeing, can you be more specific? It is often said that the Application is slow or website is slow.
– Are there any specific feature or a webpage of the application that is slow or is it that entire application is slow?
– Since when you started seeing performance problems?
– Have you started seeing these problems after recent upgrades to Application or SQL Server or OS and any other changes? or Were there anything changed recently on the system, like applying patches, Hardware or Application changes performed recently?
– Are you aware of any changes to the data or increase in number of users on the SQL Server recently?
– So far have you observed anything that can point in a direction where could be the problem?
– Have you checked application and web server to make sure problem does not lie there itself? How you came to conclusion that problem is with SQL Server?
– Do we have any baseline on the application performance or query performance like, how much time it used to take before and how much time it taking now?
– Have you performed any troubleshooting thus far and what are your findings, if any, so far?
Q. How do you troubleshoot slowness with a specific Stored Procedure or a Query?
A: – First, get more details like, how much time on an average this query was taking previously(baseline)? were there any changes to the stored procedure or query recently? How often does this query runs? Does the problem only happen on this server or other servers too, meaning can this be reproduced on Test or Dev servers?
– Check if this query is being blocked by other sessions.
– Check if this query is waiting some any resource using wait stats DMV’s.
– Check if statistics are up to date for the tables and indexes used in the Stored procedure or the query.
– Check fragmentation of the objects in the Stored procedure or the query.
– Collect execution plan of the Stored Procedure and the statements inside the SP or the query. Also collect Read/Write and Time execution statistics of the query.
– Check for any missing indexes based on the execution plan, based on table or clustered index scans.
– Try and suggest to developers if the query needs to be rewritten in a better way like replacing temp variables with tempdb tables or usage of functions in where clause.
Q. What are the tools available for performance tuning/monitoring?
A: Here are the few tools list:
-Performance Studio: Act as a Central Data Repository, Collect Selected SQL Server Performance Data and Display Performance Reports
-Activity Monitor: It displays graphically about Processes, Resource Waits, Datafile I/O, Recent expensive Quires.
-Database Tuning Advisor (DTA): Recommend indexes
-Profiler: Can run traces and find out the expensive/long running quires/transactions
-Execution Plans: There are three types Graphical, Text and XML.
-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
-PerfMon: Windows native tool to view / monitor the performance of both sql and windows servers
Q. What is the process of tuning the Performance?
A: There is no specific process its all depends upon the kind of issue:
-Identification – Use native tools like Profiler, Query Tuning Advisor, Query Execution Plans, Performance Monitor, system stored procedures, dynamic management views, custom stored procedures or third party tools.
-Analysis – Analyze the data to determine the core problems.
-Providing Solution –
Creating new index on appropriate columns
Altering the complex quires to make them use the existing indexes.
By Updating Statistics for Tables and Views.
By Rebuilding and Reorganizing indexes.
By Resolving blocking problems.
By removing Deadlocks.
-Testing – Test the various options to ensure they perform better and do not cause worse performance in other portions of the application
-Knowledge sharing – Share your experience with the team to ensure they understand the problem and solution, so the issue does not occur again
Q. How do you troubleshoot performance related problems in SQL Server?
A: There are different ways of troubleshooting performance issues and there is no right or wrong way of troubleshooting performance issues, it is just my way of approaching in identifying the problem and then taking corrective actions.
– First try to understand more details about the performance issues. Normally performance problems are reported by Developers or Client as per feedback from end users or some monitoring tools reporting specific performance issue on the server.
– Ask Developers or Client questions to understand what they mean by performance problems and gather mode details to pinpoint issue to a specific application or a specific functionality of the application.
– Troubleshooting approach will differ for general performance problems affecting whole application or more specific problem like slowness with specific Stored Procedure or Query. Next steps are specific to general performance troubleshooting.
– Check the overall hardware resource usage like, CPU usage on the server, Memory usage on the server, I/O usage on the server and Network usage to see if there is any spikes in any of their usage. If yes, then drill further down in that direction, if every thing looks normal, then will proceed with checking at SQL Server level.
– Check SQL Server errorlogs and eventlogs for any errors.
– Check for any blocking or heavy locking or high number of suspended sessions.
– Check wait stats to see the top waits.
– Checking if there are regular maintenance on the SQL Server like rebuilding indexes and update of statistics. If not, then will implement those which will significantly improve the performance.
– Will run DMV’s to identify Top Duration, Top CPU, Top Read or Write intensive queries and try to tune them by creating appropriate indexes or report them to developer suggesting to re-write those queries.
– Checking for SQL configuration settings like, MaxDoP, SQL Max Server Memory, Lock Pages in Memory, Instant File Initialization, Auto-Growth settings, etc.
Q. What kind of Ques you should ask to Developers or reporter to better understand more about the performance issue?
A: – What kind of performance issue are you seeing, can you be more specific? It is often said that the Application is slow or website is slow.
– Are there any specific feature or a webpage of the application that is slow or is it that entire application is slow?
– Since when you started seeing performance problems?
– Have you started seeing these problems after recent upgrades to Application or SQL Server or OS and any other changes? or Were there anything changed recently on the system, like applying patches, Hardware or Application changes performed recently?
– Are you aware of any changes to the data or increase in number of users on the SQL Server recently?
– So far have you observed anything that can point in a direction where could be the problem?
– Have you checked application and web server to make sure problem does not lie there itself? How you came to conclusion that problem is with SQL Server?
– Do we have any baseline on the application performance or query performance like, how much time it used to take before and how much time it taking now?
– Have you performed any troubleshooting thus far and what are your findings, if any, so far?
Q. How do you troubleshoot slowness with a specific Stored Procedure or a Query?
A: – First, get more details like, how much time on an average this query was taking previously(baseline)? were there any changes to the stored procedure or query recently? How often does this query runs? Does the problem only happen on this server or other servers too, meaning can this be reproduced on Test or Dev servers?
– Check if this query is being blocked by other sessions.
– Check if this query is waiting some any resource using wait stats DMV’s.
– Check if statistics are up to date for the tables and indexes used in the Stored procedure or the query.
– Check fragmentation of the objects in the Stored procedure or the query.
– Collect execution plan of the Stored Procedure and the statements inside the SP or the query. Also collect Read/Write and Time execution statistics of the query.
– Check for any missing indexes based on the execution plan, based on table or clustered index scans.
– Try and suggest to developers if the query needs to be rewritten in a better way like replacing temp variables with tempdb tables or usage of functions in where clause.
No comments:
Post a Comment