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

Query Optimization:

When optimizing your database server, you need to tune the performance of individual queries. This is as important as—perhaps even more important than—tuning other aspects of your server installation, that affect performance, such as hardware and software configurations.

Even if your database server runs on the most powerful hardware available, its performance can be negatively affected by a handful of misbehaving queries. In fact, even one bad query, sometimes called a "runaway query," can cause serious performance issues for your database.

Conversely, the overall performance of your database can be greatly improved by tuning a set of most expensive or most often executed queries. In this article, I will look at some of the techniques you can employ to identify and tune the most expensive and worst performing queries on your server.

Use ANSI standard

SELECT * FROM employee e1 INNER JOIN employee _dtl e2
ON e1.id = e2.id

Instead of,

SELECT * FROM employee e1, employee_dtl e2
WHERE e1.id = e2.id

Use as few as possible variables,

Use “SET NOCOUNT ON”

Don't use the "sp_" prefix in a stored procedure name,

SELECT vs SET

WHERE clause operators,

Use - =, LIKE ‘AB%’, EXISTS, IN
Not to Use - <>, LIKE ‘%AB’, NOT IN


                                             Source: Slideshare.net

 

• Do not use functions in WHERE clause.
• Avoid DISTINCT and ORDER BY.
• Avoid using cursors.
• Subquery vs. JOINs.
• CREATE TABLE vs. SELECT INTO.
• Proper Indexing.
• Cache hit Event.

Columns to Index

• Create indexes on frequently searched columns, such as:
• Primary keys
• Foreign keys or other columns that are used frequently in joining tables
• Columns that are searched for ranges of key values
• Columns that are accessed in sorted order
• Columns Not to Index
• Do not index columns that:
• You seldom reference in a query.

Contains a few unique values. For example, an index on a column with only two values, such as male and female, returns a high percentage of rows and is not beneficial.

Are defined with bit, text, and image data types. Columns with these data types cannot be indexed.

Monitor the Performance:

Microsoft SQL Server provides a comprehensive set of tools for monitoring events in SQL Server and for tuning the physical database design. The choice of tool depends on the type of monitoring or tuning to be done and the particular events to be monitored.

SQL Profiler

SQL Server Profiler tracks engine process events, such as the start of a batch or a transaction, enabling you to monitor server and database activity (for example, deadlocks, fatal errors, or login activity). You can capture SQL Server Profiler data to a SQL Server table or a file for later analysis, and you can also replay the events captured on SQL Server step by step, to see exactly what happened.


Built-in functions

Built-in functions display snapshot statistics about SQL Server activity since the server was started; these statistics are stored in predefined SQL Server counters. For example, @@CPU_BUSY contains the amount of time the CPU has been executing SQL Server code; @@CONNECTIONS contains the number of SQL Server connections or attempted connections; and @@PACKET_ERRORS contains the number of network packets occurring on SQL Server connections.

System Monitor

System Monitor primarily tracks resource usage, such as the number of buffer manager page requests in use, enabling you to monitor server performance and activity using predefined objects and counters or user-defined counters to monitor events. System Monitor (Performance Monitor in Microsoft Windows NT 4.0) collects counts and rates rather than data about the events (for example, memory usage, number of active transactions, number of blocked locks, or CPU activity). You can set thresholds on specific counters to generate alerts that notify operators.

System Monitor works on Microsoft Windows Server and Windows operating systems. It can monitor (remotely or locally) an instance of SQL Server on Windows NT 4.0 or later.

The key difference between SQL Server Profiler and System Monitor is that SQL Server Profiler monitors Database Engine events, whereas System Monitor monitors resource usage associated with server processes.

Choosing a Monitoring Tool: