How To Performance Tune Your SQL Server - Part One

Introduction

This article will give you the clear knowledge on performance tuning of the SQL Server; while many developers aren't very aware of this performance tuning, this article will make them feel comfortable to handle it in future.


Source: Slideshare.net

Database Tuning:

Database tuning can be an incredibly difficult task, particularly when working with large-scale data where even the most minor change can have a dramatic impact on performance.

SQL database tuning will be handled by a Database Administrator (DBA) and in most organizations a developer doesn't do that. But believe me, there are plenty of developers out there who have to perform DBA-like tasks. Further, in many of the companies I’ve seen that do have DBAs, they often struggle to work well with developers—the positions simply require different modes of problem solving capability.



There many facets to the program and many things to consider if you want to correctly tune a SQL Server instance. Performance tuning is definitely one of those areas in which experience is the best teacher. But you need to start somewhere. And, as is common to many things, it all starts by having a good foundation to build upon.

SQL Performance Tuning:

We are going to look at these three factors as the first phase and making a proper modification related to these strategies will have a positive improvement in the performance.

• Execution Plan
• Query Optimization
• Monitor the Performance

Its main function is to graphically display the data retrieval methods chosen by the SQL Server query optimizer. If you’ve never seen them before, here’s a detailed walkthrough.



To retrieve the execution plan (in SQL Server Management Studio), just click “Include Actual Execution Plan” (CTRL + M) before running your query.

Physical Operation:

The physical operator used, such as Hash Join or Nested Loops.

Physical operators displayed in red indicate that the query optimizer has issued a warning, such as missing column statistics or missing join predicates.

Logical Operation:

The logical operator that matches the physical operator, such as the Inner Join operator. The logical operator is listed after the physical operator at the top of the ToolTip.

Estimated Row Size:

The estimated size of the row produced by the operator (bytes).

Estimated I/O Cost:

The estimated cost of all I/O activity for the operation. This value should be as low as possible.



Estimated CPU Cost:

The estimated cost of all CPU activity for the operation.

Estimated Operator Cost:

The cost to the query optimizer for executing this operation.

The cost of this operation as a percentage of the total cost of the query is displayed in parentheses.

Estimated sub-tree cost:

The total cost to the query optimizer for executing this operation and all operations preceding it in the same subtree.

Estimated Number of Rows:

The number of rows produced by the operator.

More memory is required by the system.
The "age" of the plan has reached zero.

Age = Cost * Plan Reused
Internal process periodically scans the objects in the cache and decreases this value by one each time.
The plan isn't currently being referenced by an existing connection.

Recompilation of an execution plan can be forced by:

• Changing the schema of a table referenced by the query.
• Changing/Dropping an index used by the query.
• Calling the function, sp_recompile.
• Large number of inserts or deletes in tables referenced by a query.
• Significant growth of the inserted or deleted tables.

Execution Plan Formats:

• Graphical Plans.
• Text Plans.
• SHOWPLAN_ALL
• SHOWPLAN_TEXT
• XML Plans
• SHOWPLAN_XML
• STATISTICS_XML