Performance of SQL Server 2005 Query

SQL Server 2005 Table-valued Function weird performance

From these query plans it looks like you could benefit from an index like this (if I inferred your DB schema right):

CREATE INDEX IX_call_company_date ON call (company_id, date)

In general this seems to be a standard query optimization problem and the table-valued functions aren't making a difference here actually.

Performance of OR?

You can't count on short circuit evaluation in TSQL.

The optimiser is free to evaluate the conditions in which ever order it sees fit and may in some circumstances evaluate both parts of an expression even when the second evaluation cannot change the result of the expression (Example).

That is not to say it never does short circuit evaluation however. You may well get a start up predicate on the expensive condition so it is only executed when required.

Additionally the presence of the OR in your query can convert a sargable search condition into an unsargable one meaning that indexes are not used optimally. Especially in SQL Server 2005 (In 2008 OPTION (RECOMPILE) can help here).

For example compare the plans for the following. The version with OR ends up doing a full index scan rather than an index seek to the specific values.

DECLARE @number INT;
SET number  = 0;

SELECT COUNT(*)
FROM master..spt_values
WHERE @number IS NULL OR number = 0

SELECT COUNT(*)
FROM master..spt_values
WHERE  number = 0

SQL Server 2005 Performance Issues and Testing

SQLServer 2008 has a resource governor that allows you to throttle the CPU and/or I/O based on the db username that is used for a given connection. If upgrading to SQL Server 2008 is an option for you, your stats assembly could use a dedicated database username that allows it to consume 100% of the CPU unless another db connection needs CPU, then it yields to the other processes.

This works very well. We have queries and reports that do complex statistical analysis of thousands of data points. It too is CPU intensive. Before we upgraded to SQL Server 2008, we have all sorts of performance issues. Now everything runs smooth. The other new features of 2008 are nice, but the resource governor is the real reason we upgraded.

Query performance difference between SQL Server 2000 and SQL Server 2005

MSSQL Server undergoes significant improvements in every version.whenever you are migrating between the database versions you should take a look at the changes that have been made in the newer versions.

---

Related Topics