Thanks all for your replies.

This seems to be more of an academic question then.

Cheers!

jezemine (4/22/2008)

---

Adam says ISNULL is about 10% faster:

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/performance-isnull-vs-coalesce.aspx

but 10% of what? The time your query spends evaluating ISNULL or COALESCE will generally be very tiny compared to the time...

what's negligible? 1%?

even if COALESCE is 1% faster, having it occur hundreds/thousands of times in frequently run code, should still incur a small overhead, would it not?

Thanks for the reply.

We actually found the culprit.

It was a monster execution plan that at the point of failure registered over a billion logical reads in Profiler.

We were able...

yes, I believe so.

Perhaps we can request that from Microsoft to include in their next release.

riga1966 (4/21/2008)

---

Mario,

Thank you very much for your queries.

They seem to be a much faster way to quickly identify

the most "troubled SQL code" in the system.

Question.

What are the Individual Query and...

Glad it was helpful guys! 🙂

Here is a query to gather useful missing indexes:

--Get missing indexes

SELECT

index_advantage

,user_seeks

,last_user_seek

,avg_total_user_cost

,avg_user_impact

, equality_columns

,included_columns

,[statement]

FROM

(SELECT

user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage

,migs.*...

An alternative to using SQL Profiler is querying system DMVs (provided your system has been running for a sufficiently long time for enough stats to be collected in memory).

Costliest queries...

A suspended SPID is waiting for a resource (CPU, data from cache) to become available.

For an excellent discussion of this topic I would recommend:

SQL Server 2005 Performance Tuning using...

Are you statistics up-to-date (WITH FULLSCAN)?

Are your indexes defragmented?

Are your queries performing full table scans, where an index would have helped?

These are some of the things you need to be...

If you are referring to the Database-Engine Tuning Advisor (DTA), then yes, that tool has a long way to go to become dependable for index analysis.

However, SQL 2005 ships with...

After checking the ORACLE installer, found that the provider is already installed.

The issue is caused by missing registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\Providers\OraOLEDB.Oracle

I have manually added this key to the registry and...

It looks like we are missing the ORAOLEDB.ORACLE provider. When I check in the registry I do not see it:

Any advice on how to install it?

I'm running the following query (on 1 line) and getting same error:

Same query works in our test environment.