Today’s blog is the beginning of your lessons on Write Logs. So what’s the definition of a write log. Well when a SQL Server session waits on the WRITELOG wait type, it’s waiting to write the contents of that log cache to disk where of course the transaction log is stored.
We will explain the process [...]
Posts under ‘sql server tuning’
Write Logs
Analyzing the SQL Diagram
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 [...]
Creating a SQL Diagram
We would like to introduce a technique that we use to tune SQL statements correctly without the trial and error that we were often faced with before. The concept of SQL Diagramming was introduced to us through a book named SQL Diagramming by Dan Tow. For a complete understanding of this topic we encourage you [...]
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 [...]
Table and Index Statistics
The next step is to gather information about each table being accessed inefficiently. These tables come from a review of the execution plan and are a result of finding the highest execution steps. There is no use gathering data for objects that are already being accessed efficiently. Confio Software has a script on their support [...]
SQL Execution Plan
Let’s talk about your SQL execution plan, once you have broken the query down you need to understand how each component behaves. Yeah, sure you do! This is where execution plans help supply costing information, data access paths, join operations and many other things.
As you would have guessed, not all plans are the same. Do [...]
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 [...]
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 [...]
