Addressing the Performance issues in SQL Server:-
Troubleshooting SQL Server performance and SQL Server performance Tuning are most important skill for a DBA which provides lot of value and differentiates between an average DBA and a very good and skilled DBA.
Below are some of the commonly asked SQL Server performance tuning questions (FAQ) and their answers provided in-line.
How do you approach or troubleshoot performance problems in SQL Server?
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.
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, MaxDrop, SQL Max Server Memory, Lock Pages in Memory, Instant File Initialization, Auto-Growth settings, etc.
– 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, MaxDrop, SQL Max Server Memory, Lock Pages in Memory, Instant File Initialization, Auto-Growth settings, etc.
Above steps should help in understanding the performance problem and in fixing the same.
What question do you ask Developers or Client to understand more about the performance issue?
– 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?
– 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?
It is common that Developers or Client as expected do not have answers to most of these questions and general answer is entire Application is slow.
How do you troubleshoot slowness with a specific Stored Procedure or a Query?
– 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.
– 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.
What SQL Server tools are available to troubleshooting performance problems?
– SQL Server Management Studio (SSMS), helps us to run the queries or DMV’s, We can generate execution query plans, Read/Write statistics, Time Statistics by running the query from SSMS.
– SQL Server Profiler trace can be used to identify all the queries running during the problem time and which queries are taking longer times to complete.
– SQL Server Database Tuning Adviser (DTA) is available to check for missing index and statistics recommendations.
– SQL Server DMV’s available to get lot of in-depth details about query statistics and execution plans.
– SQL Server Performance Dashboard to generate performance related reports(This needs to be installed as additional plugin)
– Starting SQL Server 2008 R2, we can use SQL Server Extended Events can be used for more granular details with less performance overhead than profiler.
– Starting SQL Server 2008 R2, we can Management Data Warehouse (MDW) for performance troubleshooting.
– SQL Server Management Studio (SSMS), helps us to run the queries or DMV’s, We can generate execution query plans, Read/Write statistics, Time Statistics by running the query from SSMS.
– SQL Server Profiler trace can be used to identify all the queries running during the problem time and which queries are taking longer times to complete.
– SQL Server Database Tuning Adviser (DTA) is available to check for missing index and statistics recommendations.
– SQL Server DMV’s available to get lot of in-depth details about query statistics and execution plans.
– SQL Server Performance Dashboard to generate performance related reports(This needs to be installed as additional plugin)
– Starting SQL Server 2008 R2, we can use SQL Server Extended Events can be used for more granular details with less performance overhead than profiler.
– Starting SQL Server 2008 R2, we can Management Data Warehouse (MDW) for performance troubleshooting.
How can we proactively try and make sure that performance issues do not occur?
– Follow best practices related to Operating System, SAM, Network
– Follow SQL Server best practices and SQL Server configuration settings for best performance.
– Make sure there are regular maintenance jobs like smart index maintenance tasks like rebuilding or reorganizing indexes based on fragmentation, Smart Updating statistics with Full Scan.
– Using DMV’s or other monitoring tools to monitor the resource usage on server like CPU, Memory and Disk I/O.
– Monitor for occurrences of locking, blocking and suspended sessions.
– Monitor for occurrences of Waits in SQL Server using waitstats DMV’s
– Baseline SQL Server and server resource usage.
– Follow best practices related to Operating System, SAM, Network
– Follow SQL Server best practices and SQL Server configuration settings for best performance.
– Make sure there are regular maintenance jobs like smart index maintenance tasks like rebuilding or reorganizing indexes based on fragmentation, Smart Updating statistics with Full Scan.
– Using DMV’s or other monitoring tools to monitor the resource usage on server like CPU, Memory and Disk I/O.
– Monitor for occurrences of locking, blocking and suspended sessions.
– Monitor for occurrences of Waits in SQL Server using waitstats DMV’s
– Baseline SQL Server and server resource usage.
No comments:
Post a Comment