ORDER BY in a query makes query 6 times slower

[uparna]

Hello Friends ,
I have been asked to help a colleague of mine who is an MSSQL programmer. He is facing this peculiar problem : There is a query on a complex view, which works fine without the "order by" clause. As soon as we try to sort the query , the query takes 6 times more time to return the result set. It is a view which access around 10 tables and some views. I was shocked to find that there were only a couple of joins and the rest of them were all LEFT joins (ie outer joins). I know for sure that Left Joins are performance hogs. It is strange though that the performance is affected only when we try to sort the result set. Since the number of tables is too much , we cannot do any index analysis on this to find out where the problem is. All the individual tables do have indexes on them. I am an oracle DBA and i know that SORTS introducing performance problems is sometimes due to badly configured SORT_AREA_SIZE s and bad TEMPORARY tablespace configuration in Oracle. I donnot know their equivalents in MSSQL. Help me !

Regards,
S. Jayaram Uparna .

 

[SQLSister]

Did you try using the index tuning wizard?

 

[maswien]

I think the database tempDB is used to do such kind of things, you can monitor the size of tempDB and see if there is a problem in allocating space for sorting.

 

[uparna]

Mjia,
Excellent piece of info. Let me check this out.

SqlSister,
I did do an index analysis and the Explain plan shows that the indexes are used everywhere with no FTS taking place anywhere. Unfortunately , nearly 70-80% of the time is taken by Sorting....help !!!

Regards,
S. Jayaram Uparna .

 

[royjimenez]

can u use a store procedure ?

'cause u could make one and to use #table for avoiding to multiple all the tables at the same time.

Regards

 

[uparna]

Hi ,
Yup , that is an option that we are already exploring....but I am really interested in finding out the real reason for the STORT to behave badly...

Regards,
S. Jayaram Uparna .

 

[royjimenez]

ORDER BY sort the information but if there aren't indexes it will loop for each row. If your view or select is very important, and it is frecuently used u should make an index.
In each ORDER BY , the information sorted is kept in a temp table, it does slower the performance.

You are multipling too many rows ( I think ) 'cause i have many tables in your view. U should avoid to multiple tables with many rows ( as details ). U can get a better performance using a store procedure for getting the rows by parts.

 

[uparna]

Hi ,
Well, i just rechecked my explain plan for the query WITH the Order By as well, and there are no FTS's going on : there are Indexes being used everywhere. So i am kind of sure now that the problem is not due to Indexes. Let me get to this point: we ARE in the process of breaking up the view and using a stored procedure. But i want to know the following
1. How do sorts happen in MSSQL : do they happen in the RAM or on the DISK ?
2.Whereever they happen , what are the parameters that govern how much space is allocated for this ?
3.Like Oracle has Hints in them , does MSSQL have hints in it? If so , what are the Hints that would help me to reduce the sort time?

Thanks ! You guys are making me live through this agony...

Regards,
S. Jayaram Uparna .

 

[SQLSister]

Look in books online for hints, you can force the use of a particular index or indexes.

How many total records are being returned? Are you alwasys using the same order? It might be worth investigating setting up a clustered index on the appropriate field in each table. Usually the default is to set that on the primary key, but if you are using integer primary keys, then a clustered index on a different field might make more sense.

Oh yeah, another thought. Are your statistics up-to-date. i can see where out of date stats might make a sort slower.

 

[mihaildamjan]

This is from the t-sql help.
Hints go in FROM clause.

****************************
FROM
Specifies the tables, views, derived tables, and joined tables used in DELETE, SELECT, and UPDATE statements.

Syntax
[ FROM { < table_source > } [ ,...n ] ]

< table_source > ::=
table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]
| view_name [ [ AS ] table_alias ] [ WITH ( < view_hint > [ ,...n ] ) ]
| rowset_function [ [ AS ] table_alias ]
| user_defined_function [ [ AS ] table_alias ]
| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
| < joined_table >

< joined_table > ::=
< table_source > < join_type > < table_source > ON < search_condition >
| < table_source > CROSS JOIN < table_source >
| [ ( ] < joined_table > [ ) ]

< join_type > ::=
[ INNER | { { LEFT | RIGHT | FULL } [ OUTER] } ]
[ < join_hint > ]
JOIN



Table Hints
A table hint specifies a table scan, one or more indexes to be used by the query optimizer, or a locking method to be used by the query optimizer with this table and for this SELECT. Although this is an option, the query optimizer can usually pick the best optimization method without hints being specified.



Caution Because the query optimizer of SQL Server usually selects the best execution plan for a query, it is recommended that <join_hint>, <query_hint>, <table_hint>, and <view_hint> only be used as a last resort by experienced developers and database administrators.


The table hints are ignored if the table is not accessed by the query plan. This may be a result of the optimizer's choice not to access the table at all, or because an indexed view is accessed instead. In the latter case, the use of an indexed view may be prevented by using the OPTION (EXPAND VIEWS) query hint.

The use of commas between table hints is optional but encouraged. Separation of hints by spaces rather than commas is supported for backward compatibility.

The use of the WITH keyword is encouraged, although it is not currently required. In future releases of SQL Server, WITH may be a required keyword.

In SQL Server 2000, all lock hints are propagated to all the base tables and views that are referenced in a view. In addition, SQL Server performs the corresponding lock consistency checks.

If a table (including system tables) contains computed columns and the computed columns are computed by expressions or functions accessing columns in other tables, the table hints are not used on those tables (the table hints are not propagated). For example, a NOLOCK table hint is specified on a table in the query. This table has computed columns that are computed by a combination of expressions and functions (accessing columns in another table). The tables referenced by the expressions and functions do not use the NOLOCK table hint when accessed.

SQL Server does not allow more than one table hint from each of the following groups for each table in the FROM clause:

Granularity hints: PAGLOCK, NOLOCK, ROWLOCK, TABLOCK, or TABLOCKX.


Isolation level hints: HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE.
The NOLOCK, READUNCOMMITTED, and READPAST table hints are not allowed for tables that are targets of delete, insert, or update operations.

Syntax
< table_hint > ::=
{ INDEX ( index_val [ ,...n ] )
| FASTFIRSTROW
| HOLDLOCK
| NOLOCK
| PAGLOCK
| READCOMMITTED
| READPAST
| READUNCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
| XLOCK
}

Arguments
INDEX ( index_val [ ,...n ] )

Specifies the name or ID of the indexes to be used by SQL Server when processing the statement. Only one index hint per table can be specified.

If a clustered index exists, INDEX(0) forces a clustered index scan and INDEX(1) forces a clustered index scan or seek. If no clustered index exists, INDEX(0) forces a table scan and INDEX(1) is interpreted as an error.

The alternative INDEX = syntax (which specifies a single index hint) is supported only for backward compatibility.

If multiple indexes are used in the single hint list, the duplicates are ignored and the rest of the listed indexes are used to retrieve the rows of the table. The order of the indexes in the index hint is significant. A multiple index hint also enforces index ANDing and SQL Server applies as many conditions as possible on each index accessed. If the collection of hinted indexes is not covering, a fetch is performed after retrieving all the indexed columns.



Note If an index hint referring to multiple indexes is used on the fact table in a star join, SQL Server ignores the index hint and returns a warning message. Also, index ORing is disallowed for a table with an index hint specified.


The maximum number of indexes in the table hint is 250 nonclustered indexes.

FASTFIRSTROW

Equivalent to OPTION (FAST 1). For more information, see FAST in the OPTION clause in SELECT.

HOLDLOCK

Equivalent to SERIALIZABLE. (For more information, see SERIALIZABLE later in this topic.) The HOLDLOCK option applies only to the table or view for which it is specified and only for the duration of the transaction defined by the statement in which it is used. HOLDLOCK cannot be used in a SELECT statement that includes the FOR BROWSE option.

NOLOCK

Equivalent to READUNCOMMITTED. For more information, see READUNCOMMITTED later in this topic.

PAGLOCK

Takes shared page locks where a single shared table lock is normally taken.

READCOMMITTED

Specifies that a scan is performed with the same locking semantics as a transaction running at READ COMMITTED isolation level. For more information about isolation levels, see SET TRANSACTION ISOLATION LEVEL.

READPAST

Specifies that locked rows are skipped (read past). For example, assume table T1 contains a single integer column with the values of 1, 2, 3, 4, 5. If transaction A changes the value of 3 to 8 but has not yet committed, a SELECT * FROM T1 (READPAST) yields values 1, 2, 4, 5. READPAST applies only to transactions operating at READ COMMITTED isolation and reads past only row-level locks. This lock hint is used primarily to implement a work queue on a SQL Server table.

READUNCOMMITTED

Specifies that dirty reads are allowed. This means that no shared locks are issued and no exclusive locks are honored. Allowing dirty reads can result in higher concurrency, but at the cost of lower consistency. If READUNCOMMITTED is specified, it is possible to read an uncommitted transaction or to read a set of pages rolled back in the middle of the read; therefore, error messages may result. For more information about isolation levels, see SET TRANSACTION ISOLATION LEVEL.



Note If you receive the error message 601 when READUNCOMMITTED is specified, resolve it as you would a deadlock error (1205), and retry your statement.


REPEATABLEREAD

Specifies that a scan is performed with the same locking semantics as a transaction running at REPEATABLE READ isolation level. For more information about isolation levels, see SET TRANSACTION ISOLATION LEVEL.

ROWLOCK

Specifies that a shared row lock is taken when a single shared page or table lock is normally taken.

SERIALIZABLE

Equivalent to HOLDLOCK. Makes shared locks more restrictive by holding them until the completion of a transaction (instead of releasing the shared lock as soon as the required table or data page is no longer needed, whether or not the transaction has been completed). The scan is performed with the same semantics as a transaction running at the SERIALIZABLE isolation level. For more information about isolation levels, see SET TRANSACTION ISOLATION LEVEL.

TABLOCK

Specifies that a shared lock is taken on the table held until the end-of-statement. If HOLDLOCK is also specified, the shared table lock is held until the end of the transaction.

TABLOCKX

Specifies that an exclusive lock is taken on the table held until the end-of-statement or end-of-transaction.

UPDLOCK

Specifies that update locks instead of shared locks are taken while reading the table, and that they are held until the end-of-statement or end-of-transaction.

XLOCK

Specifies that exclusive locks should be taken and held until the end of transaction on all data processed by the statement. If specified with PAGLOCK or TABLOCK, the exclusive locks apply to the appropriate level of granularity.