Performance Tuning
1. Which Tools are used for Performance Tuning?
There are many tools are used for Performance Tuning.
a. Windows Tools for monitoring applications :
Performance monitor - Performance counters and Logs , Task Manager and Network Manager.
b. SQL Server Tolls for monitoring components :
SQL Trace , SQL Profiler , DMV's, System Stored procedures , Graphical Show Plan , Activity Monitor , DBCC , Built-in Functions , Trance Flags.
2. How to identify longest running queries ?
There are three ways to identify slow running queries.
1. Profiler ( By using duration of the query )
2. Sys.dm_exec_query_stats and sys.dm_exeec_requests DMV's
3. DBCC OPENTRAN
3. How to analyse query performance ?
We can analyze query performance in three ways.
T-SQL : SET SHOWPLAN_ALL ON/OFF , SET SHOWPLAN_TEXT ON/OFF.
SSMS : Estimated execution plan & Actual execution plan.
4. Reasons for Slow Running Queries ?
There are a number of common reasons fo slow-running queries.
1. Lack of useful indexes , Lack of useful data striping (RAID).
2. Blockings , Table Scans , Lack of useful partitioning.
3. Missing or out of date statistics .
4. Slow network communication.
5. Insufficient memory available for SQL Server.
6. Insufficient disk space.
7. Excess recompilation of stored procedures.
8. Procedures and Triggers without SET NOCOUNT ON.
5. How to increase query performance ?
We can improve the query performance in the following ways.
1. Add indexes if required.
2. Run update statistics for out of date statistics.
3. Resolving blocking issues.
4. Add space to DB files or TempDB , if that are not having enough space.
5. Reduce the too much normalization.
6. Using Temporary tables instead of cursors.
7. SPs and Triggers are with SET NOCOUNT ON.
8. Unnecessarly complicated joins.
6. Explain about profiler / What are the users of Profiler?
SQL Profiler can capture SQL Server events from the server to analyse or troubleshoot performance problems such as finding slow-running queries , monitoring and optimization.
Advantages :
* Find the worst performing queries.
* Identify the cause of a deadlock.
* Monitoring stored procedure performance.
* Audit SQL Server activity.
* Monitoring T-SQL activity per user.
7. Event captured in SQL Profiler ?
1. Sessions , Transactions and Tuning.
2. Security Audits.
3. Table Scans.
4. T-SQL statements , stored procedures.
5. Cursors , Locks , dead locks etc.
8. What are the common failures in SQL Server ?
There are three common failures occurs in SQL Server.
Database Failures
Physical Server Failures
SQL Server service failures
Causes of Database Failures :
There are three common issues will cause database failures. Log filer viewr is very useful to diagnose these problems that will occurs in SQL Server 2005.
1. Database has run out of Disk Space :
If database is on line and running out of disk space the data cannot be inserted into the database.
If the data file becomes full during recovery then database engine marks the database as "Resource Pending".
2. T. Log Full :
If the database is online and the T.Log becomes full then the database engine issues 9002 error and it is read-only state and will not allow updates.
If the T.Log becomes full during recovery then the database engine marks the database as "Resource Pending"
3. TempDB has run out of Disk space :
Temp db stores temporary objects such as stored procedures , cursors, tables ,table variables , the results of join. SQL Server creates temp db every time while restarting the SQL Server . We cannot take backup of temp db . Rebuilding indexes consumes more space in temp db . Its recovery model is simple and database id is 2 . If tempdb runs out of disk space then it causes significant problems and the errors are written to SQL server log. These errors 1101,1105,3959,3967,3958,3966 indicates Temdb has insufficient space.
Cause of Physical Server failures :
There are five common issues will causes physical server failures.
* Disk Failures.
* Memory Failures.
* Processor Failures.
* Network Card Failures.
* RAID Failures
9. If data file is full what you will do ?
If the primary data file is full we can add secondary data file.
10. If Log file is full what you will do ?
If the T.Log file is full then the database issues 9002 error and it is in read-only state and will not allow updates.
* Backing up the Log.
* Adding one or more Log Files.
* Moving the Log to another disk.
* Increasing Log file size or enabling auto growth.
* Terminating long-running transactions.
11. If the server has completely failed then how you will restore onto a new server ?
Build the windows server and restore the domain logins to supports windows authentication
Install SQL Server in single-user mode and restore the master database.
Put SQL Server in single-user mode and restore the master database.
Restore the MSDB database.
If the Model database was modified restore it.
Restore the user databases.
12. How to recover crashed SQL Server with existing data files and logs?
* Before installing of SQL Server take the backup of existing datafiles and transaction logs.
* Rename the data files and transaction logs for all system stored databases master , model,msdb and user databases to file_name_old.mdf and file_name_old.ldf.
* Install the SQL Server.
* Stop the services for SQL Server
* Rename the new datafiles and transaction logs for all systeam databasels to file_name_new.mdf and file_name_new.ldf.
* Rename the old data files and transaction logs for all system databases to original name i.e file_name.mdf,file_name.ldf.
* Start the services for SQL Server.
* Check all user and system databases exists on the server and nothing is off line.
* Verify the SQL Server and Windows Logins.
13. How to understand the database engine errors ?
If any error occurs the server writes error messages (database mail, sql agent , sql server and windows NT) to logs . These logs having error number , error message , servity , state , procedure name , line number. We can easy to understand errors by viewing log file viewer.
14. What is Severity level in event log ? at what Severity level are written to the SQL Server log ?
The severity level tells how bad the error is . Above 19 severity level errors are written to the SQL Server log.
15. If a server performance is slow ? How you can trouble shoot problem ?
16. If a database perfromance is slow ? How can you do performance tuning?
What are the performance issues ? Explain them ?
1. Which Tools are used for Performance Tuning?
There are many tools are used for Performance Tuning.
a. Windows Tools for monitoring applications :
Performance monitor - Performance counters and Logs , Task Manager and Network Manager.
b. SQL Server Tolls for monitoring components :
SQL Trace , SQL Profiler , DMV's, System Stored procedures , Graphical Show Plan , Activity Monitor , DBCC , Built-in Functions , Trance Flags.
2. How to identify longest running queries ?
There are three ways to identify slow running queries.
1. Profiler ( By using duration of the query )
2. Sys.dm_exec_query_stats and sys.dm_exeec_requests DMV's
3. DBCC OPENTRAN
3. How to analyse query performance ?
We can analyze query performance in three ways.
T-SQL : SET SHOWPLAN_ALL ON/OFF , SET SHOWPLAN_TEXT ON/OFF.
SSMS : Estimated execution plan & Actual execution plan.
4. Reasons for Slow Running Queries ?
There are a number of common reasons fo slow-running queries.
1. Lack of useful indexes , Lack of useful data striping (RAID).
2. Blockings , Table Scans , Lack of useful partitioning.
3. Missing or out of date statistics .
4. Slow network communication.
5. Insufficient memory available for SQL Server.
6. Insufficient disk space.
7. Excess recompilation of stored procedures.
8. Procedures and Triggers without SET NOCOUNT ON.
5. How to increase query performance ?
We can improve the query performance in the following ways.
1. Add indexes if required.
2. Run update statistics for out of date statistics.
3. Resolving blocking issues.
4. Add space to DB files or TempDB , if that are not having enough space.
5. Reduce the too much normalization.
6. Using Temporary tables instead of cursors.
7. SPs and Triggers are with SET NOCOUNT ON.
8. Unnecessarly complicated joins.
6. Explain about profiler / What are the users of Profiler?
SQL Profiler can capture SQL Server events from the server to analyse or troubleshoot performance problems such as finding slow-running queries , monitoring and optimization.
Advantages :
* Find the worst performing queries.
* Identify the cause of a deadlock.
* Monitoring stored procedure performance.
* Audit SQL Server activity.
* Monitoring T-SQL activity per user.
7. Event captured in SQL Profiler ?
1. Sessions , Transactions and Tuning.
2. Security Audits.
3. Table Scans.
4. T-SQL statements , stored procedures.
5. Cursors , Locks , dead locks etc.
8. What are the common failures in SQL Server ?
There are three common failures occurs in SQL Server.
Database Failures
Physical Server Failures
SQL Server service failures
Causes of Database Failures :
There are three common issues will cause database failures. Log filer viewr is very useful to diagnose these problems that will occurs in SQL Server 2005.
1. Database has run out of Disk Space :
If database is on line and running out of disk space the data cannot be inserted into the database.
If the data file becomes full during recovery then database engine marks the database as "Resource Pending".
2. T. Log Full :
If the database is online and the T.Log becomes full then the database engine issues 9002 error and it is read-only state and will not allow updates.
If the T.Log becomes full during recovery then the database engine marks the database as "Resource Pending"
3. TempDB has run out of Disk space :
Temp db stores temporary objects such as stored procedures , cursors, tables ,table variables , the results of join. SQL Server creates temp db every time while restarting the SQL Server . We cannot take backup of temp db . Rebuilding indexes consumes more space in temp db . Its recovery model is simple and database id is 2 . If tempdb runs out of disk space then it causes significant problems and the errors are written to SQL server log. These errors 1101,1105,3959,3967,3958,3966 indicates Temdb has insufficient space.
Cause of Physical Server failures :
There are five common issues will causes physical server failures.
* Disk Failures.
* Memory Failures.
* Processor Failures.
* Network Card Failures.
* RAID Failures
9. If data file is full what you will do ?
If the primary data file is full we can add secondary data file.
10. If Log file is full what you will do ?
If the T.Log file is full then the database issues 9002 error and it is in read-only state and will not allow updates.
* Backing up the Log.
* Adding one or more Log Files.
* Moving the Log to another disk.
* Increasing Log file size or enabling auto growth.
* Terminating long-running transactions.
11. If the server has completely failed then how you will restore onto a new server ?
Build the windows server and restore the domain logins to supports windows authentication
Install SQL Server in single-user mode and restore the master database.
Put SQL Server in single-user mode and restore the master database.
Restore the MSDB database.
If the Model database was modified restore it.
Restore the user databases.
12. How to recover crashed SQL Server with existing data files and logs?
* Before installing of SQL Server take the backup of existing datafiles and transaction logs.
* Rename the data files and transaction logs for all system stored databases master , model,msdb and user databases to file_name_old.mdf and file_name_old.ldf.
* Install the SQL Server.
* Stop the services for SQL Server
* Rename the new datafiles and transaction logs for all systeam databasels to file_name_new.mdf and file_name_new.ldf.
* Rename the old data files and transaction logs for all system databases to original name i.e file_name.mdf,file_name.ldf.
* Start the services for SQL Server.
* Check all user and system databases exists on the server and nothing is off line.
* Verify the SQL Server and Windows Logins.
13. How to understand the database engine errors ?
If any error occurs the server writes error messages (database mail, sql agent , sql server and windows NT) to logs . These logs having error number , error message , servity , state , procedure name , line number. We can easy to understand errors by viewing log file viewer.
14. What is Severity level in event log ? at what Severity level are written to the SQL Server log ?
The severity level tells how bad the error is . Above 19 severity level errors are written to the SQL Server log.
15. If a server performance is slow ? How you can trouble shoot problem ?
16. If a database perfromance is slow ? How can you do performance tuning?
What are the performance issues ? Explain them ?
Hi Hum ,
ReplyDeleteThanks for your reply . Thanks for reminding me about profile update. I have updated it now . Interview questions might be repeated hence I was felt like to remind few points again and again.