functions performance question

I'm sure everybody agrees on the idea that SQL server 2000 functions are not well taken care of as far as performance optimization. I'm not sure if it is because they don't have excution plans? or if they have it but it's not optimized or whatever reason. The fact is they are slow!

My question for experts in that field is if this issue has been addressed in SQL 2005? or we still going to avoid the functions as much as possible?

Thanks,

Robert.

Hi Robert,

Could you please be more specific? In general it should be the other way around, see http://msdn2.microsoft.com/en-us/library/ms191007.aspx

Reasons for slowdown may be numerous and require further investigation.

Thank you,

Boris.

functions performance question

I'm sure everybody agrees on the idea that SQL server 2000 functions are not well taken care of as far as performance optimization. I'm not sure if it is because they don't have excution plans? or if they have it but it's not optimized or whatever reason. The fact is they are slow!

My question for experts in that field is if this issue has been addressed in SQL 2005? or we still going to avoid the functions as much as possible?

Thanks,

Robert.

Hi Robert,

Could you please be more specific? In general it should be the other way around, see http://msdn2.microsoft.com/en-us/library/ms191007.aspx

Reasons for slowdown may be numerous and require further investigation.

Thank you,

Boris.

Fully Qualified Query Accross Databases

Are there any perfmonace or query optimization limitations or issues that arise when issueing a fully qualified query across multiple databases on the same Instance of SQL Server. In other words are all features of query optimization fully supported in queries that span databases on the same Instance.

The only limitations that I'm aware of are the same limitations if the queries only run against one database, i.e. the databases have to be properly optimized for performance. If one database is properly indexed and one is not the bottleneck will be the database that isn't properly indexed.
|||

That is a fact, but I am really trying to determine if there are any optimizer limitations or nuances like there are if you are using partitioning.

|||The fact that there may be partitioning really isn't relevant since partitioning is only undertaken to optimize query performance anyway by distributing data across multiple files. As I mentioned, if the databases are optimized, whether through the use of partitioning or any other strategy, then cross database queries aren't an issue. There aren't any optimizer limitations or nuances that need to be considered.

If you were performing distributed queries across multiple linked servers or across multiple resource engines, i.e. SQL Server/Oracle then there might be some things to consider but even then, the issue would probably still come down to whether the databases are properly optimized.

Full-Text Search - performance cost?

I am conducting performance tuning & optimization of a SQL2K5 database. The server has Full-Text Search Service enabled and running. However, I don't believe the applications are actually using this Service and I am considering disabling it to conserve resources since apps are performing high transactions that are consuming significant resources (memory, CPU).

How much of a performance cost does Full-Text search service impose on the server and is this a recommended way of conserving resource consumption if the Service is not being used or needed by the application?

I haven't found much in Technet, KB or MSDN to address this topic.

Thanks,
Phil

If the FTS is not being used then you must disable it in order to get the system resources back on track.

One thing is sure that FTS has been optimized in SQL 2005 as compared to SQL 2000 version as it refers http://msdn2.microsoft.com/en-us/library/ms345119.aspx here. IMHO you need to disable if you are not using at all on the application.