Performance Tuning - Resource Bottle Neck

CPU bottleneck:

We have to monitor the CPU load that can identify the resources that are overworked.

• High Signal wait causes CPU bottleneck. It should be <25%.  A query which is ready to execute and waiting for its turn is called runnable query, which is responsible signal wait. Property:   % previlize time <25%
• Plan re-use is <90 causes CPU bottleneck. The computation of plan reuse is (Batch requests – SQL compilations)/Batch requests. 

Memory bottleneck:

We need to monitor over all physical and virtual memory to ensure it is not fully allocated.

• Consistently low average page life expectancy causes the issue. Once any page is copied to buffer then it has to be there at least 300 ms or 5 minutes. <5 minutes is a problem. The reasons could be memory pressure, missing indexes and cache flush.

• Consistently low SQL cache hit ratio cause the issue. It should be >90%.Anything less may indicate memory pressure or missing indexes. Buffer cache hit ratio tells how many pages read from buffer and how many pages read from the disk.

Disk IO bottleneck:

The SQL Server reads and writes to the database on a regular basis. A slow response during processing can result in decreased SQL performance.

• High average disk seconds per read/write causes the issue. It shows the average time of data reads and writes from the disk and to the disk. Sustained high values for disk second/read > 20 milli seconds is the problem.
•       Big IOs such as table and range scans due to missing indexes.
• 
  
• Network bottleneck:
  
  
  Even with fast and smooth running server, a client who accesses the server over a slow network will encounter delays. The network bytes per second metric <50% causes issue.
  • 
    
  
  
• 
  

Performance Tuning - Execution Plan

The plan should be read from right to left.

Check the Graphical execution plan of a stored procedure / Query

·         Table Scan – Index is missing

·         Index Scan – Proper indexes are not using

·         Bookmark Lookup – Limit the number of columns in the select list

·         Filter – Remove any functions from where clause, May require additional indexes

·         Sort – Does the data really need to be sorted? Can an index be used to avoid sorting? Can sorting be done at the client more efficiently?

·         Dataflow Arrow – High density: Sometimes you find few rows as outcome but the arrow line density indicates the query/proc processing huge number of rows

·          Cost – Can easily find out which table / operation taking much time

From the execution plan we can find out the bottleneck and give the possible solution to avoid the latency. Either the user must be mapped to sysadmin, db_owner, db_creator or he/she will be granted the permission “Show Plan”.

GRANT SHOWPLAN TO [username]

Performance Tuning - Performance tuning/monitoring tools

1. Activity Monitor: It displays graphically about Processes, Resource Waits, Datafile I/O, Recent expensive Quires.
2. Database Tuning Advisor (DTA): Recommend indexes and statistics
3. Profiler: Can run traces and find out the expensive/long running quires/transactions
4. Execution Plans: There are three types Graphical, Text and XML.
5. DMV: Dynamic management views shows the current state of the sql server
6. Extended Events: Used for more granular details with less performance overhead than profiler.
7. PerfMon: Windows native tool to view / monitor the performance of both sql and windows servers
8. Third Party: Redgate products

Performance Tuning - Slowness with a specific Stored Procedure or a Query - PART 2/2

Troubleshoot slowness with a specific Stored Procedure or a Query

1.        First, get more details like, how much time on an average this query was taking previously (baseline)?

2.        Check whether any changes to the stored procedure or query recently

3.        Find out how often this query does run

4.        Check whether the issue is reproduced on Test or Dev. severs

5.        Check if this query is being blocked by other sessions.

6.        Check whether any deadlocks are happened

7.        Check if this query is waiting some any resource using wait stats DMV’s.

8.        Check if statistics are up to date for the tables

9.        Check for any missing indexes/un-used indexes

10.     Check fragmentation of the objects in the stored procedure or the query.

11.     Collect execution plan of the Stored Procedure and the statements inside the SP or the query.

12.     Collect Read/Write and Time execution metrics of the query.

13.     Check for excess recompilations of the stored procedure.

14.     Last option is rewriting the query by following best practices.

Performance Tuning - General Performance Issues in the Server Level - PART 1/2

Normally performance problems are reported by application developers or Clients.

First, we have to gather information about the performance issue like

·         Check whether the issue is for entire application or any specific feature in the application.

·         Check whether the issue is after recent upgrades to application or sqlserver or OS or any hardware changes.

·         Check whether any changes to the data or increase in number of users on the SQL Server recently.

·         Collect the baseline metrics on the application or query i.e. how much time it used to take before and how much time it taking now?

Troubleshooting approach will differ for general performance problems affecting whole application or more specific problem like slowness with specific Stored Procedure or Query.

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.

·         If yes, then drill further down in that direction, if everything looks normal, then will proceed with checking at SQL Server level.

·         Check SQL Server error logs and event logs for any errors.

·         Check for any blocking or deadlocks.

·         Check waits stats to see the top waits.

·         Check if there are regular maintenance on the SQL Server like rebuilding indexes and update of statistics. If not, then implement those which will significantly improve the performance.

·         Run DMV’s to identify Top Duration, Top CPU, and 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.

Above steps should help in understanding the performance problem and in fixing the same.

SQL Queries - Real time frequently asked by Companies - PART 3/5 - Questions

I have started posting a series of sqlserver queries that are asked in interviews. This can be a great start of practising the queries.

This is the third part of the series. I suggest you to view the PART 1, PART 2 for better visibility.

Happy Job Hunt!! ALL THE BEST!!

1.        Write a query to find TOP X records from each group?

2.        Write a query to find Max Salary from each department?

3.        There is a table which contains two columns Student and Marks. Now, we need to find all the students, whose marks are greater than average marks i.e. list of above average students?

4.        Write a query to find duplicate rows in a table?

5.        Write a query to find current system date?

6.        Write a query to display the date in MM/DD/YYYY format?

7.        Write a query to get only time part of a date time data type?

8.        Write a query to find year from date?

9.        Write a query to find the difference in days between two dates?

10.     Write a query to add or subtract days in a date?

SQL Queries - Real time frequently asked by Companies - PART 2/5 - Questions

I have started posting a series of sqlserver queries that are asked in interviews. This can be a great start of practising the queries.

This is the second part of the series. I suggest you to view the PART 1 before coming to this section as it is a bit complex than the other one.

I will provide answers to the below questions shortly. Please keep on visit this page for more updates.

Happy Job Hunt!! ALL THE BEST!!

1.        Write a query to display nth highest salary of an employee?

2.        Write a query to print numbers from 1 to 10 using CTE?

3.        Write a query to find all employees whose salary is higher than their department average salary?

4.        Write a query to delete duplicate rows from a table?

5.        Write a query to get top 3 sales person information who has sold maximum order value?

SQL Queries - Real time frequently asked by Companies - PART 1/5 - Questions

I have started posting a series of sqlserver queries that are asked in interviews. This can be a great start of practising the queries.

I will provide the answers at the end of each part. 

Happy Job Hunt!! ALL THE BEST!!

1.        Write a query to display the employee id, employee name along with their manager name based on the below tables.

Employee                                                       

Emp ID	Name
1	X
2	Y
3	Z

Manager                                                        

Emp ID	Mgr ID
1	3
2	3
3

2.        I have Member and Employee tables.

Member Table                                           Employee Table

o    Member ID                               Employee ID

o    First Name                                Member ID      

o    Last Name                               EmployeeManagerMemberID

(2a) Get Employee Name and Manager name?

(2b) Get the name of the employee who doesn’t have manager?

3.        I have Employee and Department table. The

Employee table contains columns like empid, deptid, salary.

Department table contains columns like deptid, deptName.

3(a) Write a query to get count of employee for each department.

        The output should contain two columns (DeptName, count of employees).

3(b) Write a query to get the list of department which are having employee more than 10?

3(c) Write a query to get the list of employees who are there in the multiple

        departments?

4.        I have 3 tables Student, Subject and Student Subject tables. The structure of the tables are as follows

Student Table              :  stud_ID, stud_name,stud_address

Subject Table             :  sub_ID, sub_name

StudentSubject Table :  stud_ID, sub_ID

             4(a) Display student name, student address and subject name for a provided student ID?

             4(b) Display the entire student names who are not studying the provided 

                     subject name?

5.        I have a table With Column A having 6 rows 1, 2, 3,4,5,6 respectively. Write a query to get the sum of value pair equal to a given number. Eg: If the given number is 5 then it should display rows like 1,4 & 2,3 etc.