Analyzing the SQL Diagram begins by looking for the smallest, underlined number. In our case it
is 0.002 next to the CLASS table. To limit the number of rows the query needs to process,
starting here will trim our result sets the soonest. In other words, if we can make SQL Server
start with this table the query [...]
Posts under ‘sql tuning’
Analyzing the SQL Diagram
Entity Relationship Diagram
Now to understand the relationships of the tables involved in the sql statement. The sample SQL statement we will tune is as follows and answers the question: “Who registered for the SQL Tuning class within the last day?”:
SELECT s.fname, s.lname, r.signup_date
FROM student s
INNER JOIN registration r ON s.student_id = r.student_id
INNER JOIN class c ON r.class_id [...]
Gathering SQL Statement Metrics
The next phase in getting better performance out of your sql server application and to tune sql statements is to gather critical information and metrics about the SQL statement. These metrics should include the following:
How long does the statement take now?
What is acceptable to the end users? If they want the query to return [...]
Identify Database Wait Time View
Part – 3 in “How to identify which sql statements to tune” involves identifying database wait time view. Wait time information is very critical to successfully tune SQL statements. When SQL statements execute, SQL Server has instrumented their code to give information about where it is spending time. If a statement runs for 3 minutes, [...]
How to know which sql statement to tune
Part two of “How to know which sql statement to tune”. When we mention End-to-End view, you may think of the performance of the application from the web browser or client application, through the application server and to the database. That is technically correct, but from a business perspective, you should also know the SQL and [...]
How to identify the correct SQL statement to tune
When you are ready to tune something, where do you start, how do you identify the correct SQL statement to tune? Avoid the mistake of picking a SQL statement that looks interesting, although that could be valid for practicing. Have a method to choose the SQL. The SQL statement can come from a variety of [...]
SQL Tuning Process
Working with many other customers, we have developed a sql tuning process that works very well for us. This does not mean it will work for you as is, but it’s a good starting point for anyone. The process centers around four main steps:
1. Identify – pick the correct SQL statement to tune and avoid [...]
Challenges to SQL tuning
There are many challenges you will face when undertaking a tuning project for SQL Server – SQL tuning is difficult. To do it correctly, you need to be very familiar with many different aspects of the application. From a technical standpoint, you need to understand execution plans, the way the database instance is executing the [...]
No SQL tuner is an Island
Natalie is a DBA, she most likely did not write the application or the SQL statements that are performing poorly. If you are a developer, you may not have a view into the production instances to see exactly which SQL statement is performing poorly. One of the biggest mistakes we see is DBAs or developers [...]
