top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

SQL Server performence tunning

+17 votes
282 views

SQL Server 2005 Performance Tuning using the Waits and Queues is a proven methodology that allows one to identify the best opportunities to improve performance, the so called “biggest bang for the buck”. These performance improvements are likely to have a significant return on the performance tuning time investment. The methodology helps identify the areas of slow performance by looking at the problem from two directions called Waits and Queues. An analysis of Waits indicates where SQL Server is spending lots of time waiting. In addition, the biggest waits point out the most important or relevant Queues (that is, Performance Monitor counters and other data) for this workload. The cross validation of the waits analysis enables us to eliminate all except the most significant performance counters, and provides a strong indication of pressure on specific resources.

Application performance can be easily explained by looking at SQL Server waits and System or Resource queues. In SQL Server 2005, the dynamic management view (DMV) sys.dm_os_wait_stats provides a valuable source of wait information from an application perspective. The system or resource perspective uses Performance Monitor counters and other data sources to provide a breakdown of system resource usage according to resource queues. Taken together, the value of the application and resource perspectives used by the waits and queues methodology enables us to narrow in on the bottleneck and eliminate the irrelevant performance data.

In sum, Performance Tuning using the Waits and Queues methodology is an effective way to quickly identify and resolve application performance problems because it lets the user discover new and potentially unexpected problem areas, within applications or solutions without the typical guesswork that can accompany such work.

Performance tuning of applications and solutions has been around for many years. The performance of SQL Server 2005 database applications should be evaluated from several different perspectives. Each perspective tells a different section of the complete performance story. Together they paint a detailed performance picture of the whole and also cross validate observations in each specific perspective.

posted Dec 23, 2013 by Neeraj Pandey

  Promote This Article
Facebook Share Button Twitter Share Button LinkedIn Share Button


Related Articles

SQL Server: Difference between floor, ceiling and round

In SQL queries sometimes we need to round off some decimal  or float values, at that time we always think that which option to be applied while we have three different kinds of system defined SQL rounding functions-Ceiling, Floor and Round.

 

CEILING

Get the value on the right side of the decimal and returns the smallest integer greater or equal to, the specified values.

 

FLOOR

Get the value on the right side of the decimal and returns the largest integer less or equal to the specified values (only number)

 

ROUND

Rounds a positive or negative value to a specific length.

 

Example of SQL rounding functions i.e. floor, ceiling and round

 

 

Difference between Ceiling, Floor and Round in SQL Server

 READ MORE
SQL Server: ROUND Function

This SQL Server tutorial explains how to use the ROUND function in SQL Server (Transact-SQL) with syntax and examples.

Description

In SQL Server (Transact-SQL), the ROUND function returns a number rounded to a certain number of decimal places.

Syntax

The syntax for the ROUND function in SQL Server (Transact-SQL) is:

ROUND( number, decimal_places [, operation ] )

Parameters or Arguments

number

The number to round.

decimal_places

The number of decimal places rounded to. This value must be a positive or negative integer. If this parameter is omitted, the ROUND function will round the number to 0 decimal places.

operation

Optional. The operation can be either 0 or any other numeric value. When it is 0 (or this parameter is omitted), the ROUND function will round the result to the number of decimal_places. If operation is any value other than 0, the ROUND function will truncate the result to the number of decimal_places.

Note

  • If the operation parameter is 0 (or not provided), the ROUND function will round the result to the number of decimal_places.
  • If the operation parameter is non-zero, the ROUND function will truncate the result to the number of decimal_places.
  • See also the CEILING and FLOOR functions.

Applies To

The ROUND function can be used in the following versions of SQL Server (Transact-SQL):

  • SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Example

Let's look at some SQL Server ROUND function examples and explore how to use the ROUND function in SQL Server (Transact-SQL).

For example:

SELECT ROUND(125.315, 2);
Result: 125.320    (result is rounded because 3rd parameter is omitted)

SELECT ROUND(125.315, 2, 0);
Result: 125.320    (result is rounded because 3rd parameter is 0)

SELECT ROUND(125.315, 2, 1);
Result: 125.310    (result is truncated because 3rd parameter is non-zero)

SELECT ROUND(125.315, 1);
Result: 125.300    (result is rounded because 3rd parameter is omitted)

SELECT ROUND(125.315, 0);
Result: 125.000    (result is rounded because 3rd parameter is omitted)

SELECT ROUND(125.315, -1);
Result: 130.000    (result is rounded because 3rd parameter is omitted)

SELECT ROUND(125.315, -2);
Result: 100.000    (result is rounded because 3rd parameter is omitted)
 READ MORE
SQL Server: FLOOR Function

This SQL Server tutorial explains how to use the FLOOR function in SQL Server (Transact-SQL) with syntax and examples.

Description

In SQL Server (Transact-SQL), the FLOOR function returns the largest integer value that is equal to or less than a number.

Syntax

The syntax for the FLOOR function in SQL Server (Transact-SQL) is:

FLOOR( number )

Parameters or Arguments

number

The value used to determine the largest integer value that is equal to or less than a number.

Note

  • See also the CEILING and ROUND functions.

Applies To

The FLOOR function can be used in the following versions of SQL Server (Transact-SQL):

  • SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Example

Let's look at some SQL Server FLOOR function examples and explore how to use the FLOOR function in SQL Server (Transact-SQL).

For example:

SELECT FLOOR(5.9);
Result: 5

SELECT FLOOR(34.29);
Result: 34

SELECT FLOOR(-5.9);
Result: -6
 READ MORE
SQL Server: CEILING Function

This SQL Server tutorial explains how to use the CEILING function in SQL Server (Transact-SQL) with syntax and examples.

Description

In SQL Server (Transact-SQL), the CEILING function returns the smallest integer value that is greater than or equal to a number.

Syntax

The syntax for the CEILING function in SQL Server (Transact-SQL) is:

CEILING( number )

Parameters or Arguments

number

The number used to find the smallest integer value.

Note

  • See also the FLOOR and ROUND functions.

Applies To

The CEILING function can be used in the following versions of SQL Server (Transact-SQL):

  • SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Example

Let's look at some SQL Server CEILING function examples and explore how to use the CEILING function in SQL Server (Transact-SQL).

For example:

SELECT CEILING(32.65);
Result: 33

SELECT CEILING(32.1);
Result: 33

SELECT CEILING(32);
Result: 32

SELECT CEILING(-32.65);
Result: -32

SELECT CEILING(-32);
Result: -32
 READ MORE
Nested WITH clauses for Common Table Expressions in SQL Server
WITH x AS 
(
    SELECT * FROM MyTable
), 
y AS 
(
    SELECT * FROM x
)
SELECT * FROM y
 READ MORE
...