Have a look at Roys marvelous series: What, when and who? Auditing 101[/url]

On top of the previous replies you may want to double check the need for this instance to enable ad hoc workload optimization.

( just to prevent cache spill ram for...

and above all ... meet your peers !

indeed.

( for both sqlserver instances please, RTM is already a couple of years old and there are many fixes implemented with the SPs )

You only need to apply the...

You posted in a SQL 2005 forum !

Your server is SQL2008R2 !

btw the current service pack...

Can you post these properties of your two sqlserver instances ?

Select Serverproperty('Edition') as Edition

, Serverproperty('ProductVersion') as ProductVersion

,...

is the data for your computed column considered static ?

if yes, use an insert trigger that gets that data and puts is in a regular column.

if no,

How about...

I would double check:

1- network bandwith and consumption ( drastically enhanced with SQL2008 ! in case you still need an upgrade reason )

2- I/O subsystem performance

3- ram pressure

of...

and chances this shouldn't be a SQLFunction at all, but a regular stored procedure.

Keep in mind, if a function doesn't perform well, it will drag down the query using it...

Did you try this ?

SELECT distinct

vs.volume_mount_point

, vs.volume_id

, vs.logical_volume_name

...

there is no catch block in your trigger. Is that how you intend it ? If so, don't use the try.

just my 2ct, to add to previous responders replies:

In your sproc, chances are filter arguments aren't sniffed to your advantage at query plan generation time.

Reason for that is you are...

check your sqlserver errorlog file to see what it is reporting about that database.

Keep in mind, you may have to process more than one errorlog file to get to the...

3ms is the tollerance for a datetime data type.

why are you using implict transactions.

as in most cases, taking control yourself, using explicit transactions ( begin tran .... commit/rollback )...

build up a solid view !

It's the combination that counts ... if it fits your needs and goals !

http://technet.microsoft.com/en-us/library/hh393576.aspx

http://msdn.microsoft.com/en-us/library/jj215886.aspx

http://sqlblog.com/blogs/kevin_kline/archive/2012/07/26/high-availability-white-papers-and-resources-for-sql-server.aspx