Performance problems after updating to SQL Server 2005

We have updated to SQL Server 2005, let’s say, in a hurry without thinking or testing. Databases were attached to the new instance of SQL Server 2005. It looked great when I tested it alone but then a new day come and as all users logged into the system we had got a big problem. The response times are very long and users receive time out errors all the time.

A little background:

The instance of SQL Server 2005 is installed on the same server as 2000 was installed on. 2000 has been uninstalled. It is a Xenon 3.2 GHz with 2GB RAM and SCSI raid. Data and logs are on different spins.

Application is an old ASP code and some parts are not optimized at all. But it worked fine on SQL Server 2000.

What could be the problem?

I really don’t want to downgrade to SQL Server 2000.

Hi Stolin,

Just wondering if you ran UPDATE STATS after the upgrade.

If not, This is the first thing I would do. UPDATE STATS with FULL SCAN.

Good luck and let us know how u get on

Jag

|||

Thank You,

That’s exactly what I did right after I’d posted the message. I also rebuild the indexes. Let’s hope that’s enough.

Performance problem: SSAS 2005 + ProClarity #2 (Update)

Hi,

I have updated my earlier post on performance (Thomas was helping me),

had some updates, thoughts and queries.

Please do check that.

here is the link:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=929316&SiteID=1

apologies for this post, but I am not sure if the older ones that get updated are seen. if you want me to open another post for it , please do tell me.

thanks a lot.

Regards

I have posted an answere on your original post.

Regards

Thomas Ivarsson

Performance of SQL2k5 with snapshot isolation level turned on

I did several tests of performance of SQL2k5, and I see quite strange result
s.
I restored database from SQL2k, rebuilded indexes, updated statistics. I did
all what described in migration process. Backuped the resulting database. In
each of next tests I restored the database from backup. Next I run tests.
Since our application, OLTP, is very performance sensitive, we need to have
good distribution of transaction execution time. In case if I run tests on
SQL2k5, when snapshot isolation level is not turned on, I see good
distribution - less than 0.5% of transactions executing more than 200ms,
average execution time is ~5 ms. In case if I change compatibility level to
90 and turn on allow_snapshot_isolation, behavior is dramatically changing.
Near 5% of transactions start to execute more than 200 ms, average execution
time is also significally increasing. There are no transactions which are
really use snapshot isolation level, stored procedures are exacly same as
before. There is no any memory pressure, no IO bottlenecks. During the tests
counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2 GB
size, but only ~200 MB of the data was read in the tests. 1.2GB write cache
-
it means what all writing during the test was done only to write cache on
CX500, tests were not so long to fill the entire write cache. Windows 2003
x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
CPU load was ~50% in tests without snapshot isolation level, and ~70% with
tests with snapshot level. 2CPU.
From the test it looks like I can not remove replication to QueryDB and run
queries on same computer where OLTP load...
Why impact of snapshot isolation level is so big?As soon as you change the isolation level to either Snapshot Isolation or
Read Committed Snapshot, SQL Server will start saving ALL updated data in
the version store in tempdb, whether or not any queries every read the
versioned data. So every changed row will have additional overhead
associated with it.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
>I did several tests of performance of SQL2k5, and I see quite strange
>results.
> I restored database from SQL2k, rebuilded indexes, updated statistics. I
> did
> all what described in migration process. Backuped the resulting database.
> In
> each of next tests I restored the database from backup. Next I run tests.
> Since our application, OLTP, is very performance sensitive, we need to
> have
> good distribution of transaction execution time. In case if I run tests on
> SQL2k5, when snapshot isolation level is not turned on, I see good
> distribution - less than 0.5% of transactions executing more than 200ms,
> average execution time is ~5 ms. In case if I change compatibility level
> to
> 90 and turn on allow_snapshot_isolation, behavior is dramatically
> changing.
> Near 5% of transactions start to execute more than 200 ms, average
> execution
> time is also significally increasing. There are no transactions which are
> really use snapshot isolation level, stored procedures are exacly same as
> before. There is no any memory pressure, no IO bottlenecks. During the
> tests
> counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
> 5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2
> GB
> size, but only ~200 MB of the data was read in the tests. 1.2GB write
> cache -
> it means what all writing during the test was done only to write cache on
> CX500, tests were not so long to fill the entire write cache. Windows 2003
> x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
> CPU load was ~50% in tests without snapshot isolation level, and ~70% with
> tests with snapshot level. 2CPU.
> From the test it looks like I can not remove replication to QueryDB and
> run
> queries on same computer where OLTP load...
> Why impact of snapshot isolation level is so big?
>|||I understand what snapshot isolation adds some overhead, but I expected
lesser overhead. CPU consumption near 1.4 times more in case if snapshot
option turned on. It means what for most of performance sensitive OLTP
applications, which currently use SQL2k and which use replication to another
database used for longer queries, can not remove replication and run queries
on same server where main OLTP load after migration to SQL2k5. Quite
disappointing...
"Kalen Delaney" wrote:

> As soon as you change the isolation level to either Snapshot Isolation or
> Read Committed Snapshot, SQL Server will start saving ALL updated data in
> the version store in tempdb, whether or not any queries every read the
> versioned data. So every changed row will have additional overhead
> associated with it.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
>
>|||There is a lot to consider with this. Snapshot isolation or read committed
snapshot write every single transaction into the version store. Since the
version store is in tempdb, in a high volume environment, you are in effect
pounding tempdb with every single transaction you issue. These are intended
to be applied when you have situations when you do not want reads to block
writes, BUT there is a performance trade-off when you implement that
functionality.
There are a couple of things you can do to minimize the impact of the
version store overhead. First, you need to move tempdb to its own disk
device so that it doesn't have to compete with everything else. Secondly,
you can add additional files (equal to the nmber of processors) to tempdb
that are of equal size in order to stripe the writes. BUT, you are still
going to have a performance impact.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:C3C9D444-5CD1-4C9A-9F2F-4BCD5DF26019@.microsoft.com...[vbcol=seagreen]
>I understand what snapshot isolation adds some overhead, but I expected
> lesser overhead. CPU consumption near 1.4 times more in case if snapshot
> option turned on. It means what for most of performance sensitive OLTP
> applications, which currently use SQL2k and which use replication to
> another
> database used for longer queries, can not remove replication and run
> queries
> on same server where main OLTP load after migration to SQL2k5. Quite
> disappointing...
> "Kalen Delaney" wrote:
>

Performance of SQL2k5 with snapshot isolation level turned on

I did several tests of performance of SQL2k5, and I see quite strange results.
I restored database from SQL2k, rebuilded indexes, updated statistics. I did
all what described in migration process. Backuped the resulting database. In
each of next tests I restored the database from backup. Next I run tests.
Since our application, OLTP, is very performance sensitive, we need to have
good distribution of transaction execution time. In case if I run tests on
SQL2k5, when snapshot isolation level is not turned on, I see good
distribution - less than 0.5% of transactions executing more than 200ms,
average execution time is ~5 ms. In case if I change compatibility level to
90 and turn on allow_snapshot_isolation, behavior is dramatically changing.
Near 5% of transactions start to execute more than 200 ms, average execution
time is also significally increasing. There are no transactions which are
really use snapshot isolation level, stored procedures are exacly same as
before. There is no any memory pressure, no IO bottlenecks. During the tests
counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2 GB
size, but only ~200 MB of the data was read in the tests. 1.2GB write cache -
it means what all writing during the test was done only to write cache on
CX500, tests were not so long to fill the entire write cache. Windows 2003
x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
CPU load was ~50% in tests without snapshot isolation level, and ~70% with
tests with snapshot level. 2CPU.
From the test it looks like I can not remove replication to QueryDB and run
queries on same computer where OLTP load...
Why impact of snapshot isolation level is so big?
As soon as you change the isolation level to either Snapshot Isolation or
Read Committed Snapshot, SQL Server will start saving ALL updated data in
the version store in tempdb, whether or not any queries every read the
versioned data. So every changed row will have additional overhead
associated with it.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
>I did several tests of performance of SQL2k5, and I see quite strange
>results.
> I restored database from SQL2k, rebuilded indexes, updated statistics. I
> did
> all what described in migration process. Backuped the resulting database.
> In
> each of next tests I restored the database from backup. Next I run tests.
> Since our application, OLTP, is very performance sensitive, we need to
> have
> good distribution of transaction execution time. In case if I run tests on
> SQL2k5, when snapshot isolation level is not turned on, I see good
> distribution - less than 0.5% of transactions executing more than 200ms,
> average execution time is ~5 ms. In case if I change compatibility level
> to
> 90 and turn on allow_snapshot_isolation, behavior is dramatically
> changing.
> Near 5% of transactions start to execute more than 200 ms, average
> execution
> time is also significally increasing. There are no transactions which are
> really use snapshot isolation level, stored procedures are exacly same as
> before. There is no any memory pressure, no IO bottlenecks. During the
> tests
> counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
> 5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2
> GB
> size, but only ~200 MB of the data was read in the tests. 1.2GB write
> cache -
> it means what all writing during the test was done only to write cache on
> CX500, tests were not so long to fill the entire write cache. Windows 2003
> x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
> CPU load was ~50% in tests without snapshot isolation level, and ~70% with
> tests with snapshot level. 2CPU.
> From the test it looks like I can not remove replication to QueryDB and
> run
> queries on same computer where OLTP load...
> Why impact of snapshot isolation level is so big?
>
|||I understand what snapshot isolation adds some overhead, but I expected
lesser overhead. CPU consumption near 1.4 times more in case if snapshot
option turned on. It means what for most of performance sensitive OLTP
applications, which currently use SQL2k and which use replication to another
database used for longer queries, can not remove replication and run queries
on same server where main OLTP load after migration to SQL2k5. Quite
disappointing...
"Kalen Delaney" wrote:

> As soon as you change the isolation level to either Snapshot Isolation or
> Read Committed Snapshot, SQL Server will start saving ALL updated data in
> the version store in tempdb, whether or not any queries every read the
> versioned data. So every changed row will have additional overhead
> associated with it.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
>
>
|||There is a lot to consider with this. Snapshot isolation or read committed
snapshot write every single transaction into the version store. Since the
version store is in tempdb, in a high volume environment, you are in effect
pounding tempdb with every single transaction you issue. These are intended
to be applied when you have situations when you do not want reads to block
writes, BUT there is a performance trade-off when you implement that
functionality.
There are a couple of things you can do to minimize the impact of the
version store overhead. First, you need to move tempdb to its own disk
device so that it doesn't have to compete with everything else. Secondly,
you can add additional files (equal to the nmber of processors) to tempdb
that are of equal size in order to stripe the writes. BUT, you are still
going to have a performance impact.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:C3C9D444-5CD1-4C9A-9F2F-4BCD5DF26019@.microsoft.com...[vbcol=seagreen]
>I understand what snapshot isolation adds some overhead, but I expected
> lesser overhead. CPU consumption near 1.4 times more in case if snapshot
> option turned on. It means what for most of performance sensitive OLTP
> applications, which currently use SQL2k and which use replication to
> another
> database used for longer queries, can not remove replication and run
> queries
> on same server where main OLTP load after migration to SQL2k5. Quite
> disappointing...
> "Kalen Delaney" wrote:

Performance of SQL2k5 with snapshot isolation level turned on

I did several tests of performance of SQL2k5, and I see quite strange results.
I restored database from SQL2k, rebuilded indexes, updated statistics. I did
all what described in migration process. Backuped the resulting database. In
each of next tests I restored the database from backup. Next I run tests.
Since our application, OLTP, is very performance sensitive, we need to have
good distribution of transaction execution time. In case if I run tests on
SQL2k5, when snapshot isolation level is not turned on, I see good
distribution - less than 0.5% of transactions executing more than 200ms,
average execution time is ~5 ms. In case if I change compatibility level to
90 and turn on allow_snapshot_isolation, behavior is dramatically changing.
Near 5% of transactions start to execute more than 200 ms, average execution
time is also significally increasing. There are no transactions which are
really use snapshot isolation level, stored procedures are exacly same as
before. There is no any memory pressure, no IO bottlenecks. During the tests
counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2 GB
size, but only ~200 MB of the data was read in the tests. 1.2GB write cache -
it means what all writing during the test was done only to write cache on
CX500, tests were not so long to fill the entire write cache. Windows 2003
x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
CPU load was ~50% in tests without snapshot isolation level, and ~70% with
tests with snapshot level. 2CPU.
From the test it looks like I can not remove replication to QueryDB and run
queries on same computer where OLTP load...
Why impact of snapshot isolation level is so big?As soon as you change the isolation level to either Snapshot Isolation or
Read Committed Snapshot, SQL Server will start saving ALL updated data in
the version store in tempdb, whether or not any queries every read the
versioned data. So every changed row will have additional overhead
associated with it.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
>I did several tests of performance of SQL2k5, and I see quite strange
>results.
> I restored database from SQL2k, rebuilded indexes, updated statistics. I
> did
> all what described in migration process. Backuped the resulting database.
> In
> each of next tests I restored the database from backup. Next I run tests.
> Since our application, OLTP, is very performance sensitive, we need to
> have
> good distribution of transaction execution time. In case if I run tests on
> SQL2k5, when snapshot isolation level is not turned on, I see good
> distribution - less than 0.5% of transactions executing more than 200ms,
> average execution time is ~5 ms. In case if I change compatibility level
> to
> 90 and turn on allow_snapshot_isolation, behavior is dramatically
> changing.
> Near 5% of transactions start to execute more than 200 ms, average
> execution
> time is also significally increasing. There are no transactions which are
> really use snapshot isolation level, stored procedures are exacly same as
> before. There is no any memory pressure, no IO bottlenecks. During the
> tests
> counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
> 5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2
> GB
> size, but only ~200 MB of the data was read in the tests. 1.2GB write
> cache -
> it means what all writing during the test was done only to write cache on
> CX500, tests were not so long to fill the entire write cache. Windows 2003
> x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
> CPU load was ~50% in tests without snapshot isolation level, and ~70% with
> tests with snapshot level. 2CPU.
> From the test it looks like I can not remove replication to QueryDB and
> run
> queries on same computer where OLTP load...
> Why impact of snapshot isolation level is so big?
>|||I understand what snapshot isolation adds some overhead, but I expected
lesser overhead. CPU consumption near 1.4 times more in case if snapshot
option turned on. It means what for most of performance sensitive OLTP
applications, which currently use SQL2k and which use replication to another
database used for longer queries, can not remove replication and run queries
on same server where main OLTP load after migration to SQL2k5. Quite
disappointing...
"Kalen Delaney" wrote:
> As soon as you change the isolation level to either Snapshot Isolation or
> Read Committed Snapshot, SQL Server will start saving ALL updated data in
> the version store in tempdb, whether or not any queries every read the
> versioned data. So every changed row will have additional overhead
> associated with it.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
> >I did several tests of performance of SQL2k5, and I see quite strange
> >results.
> > I restored database from SQL2k, rebuilded indexes, updated statistics. I
> > did
> > all what described in migration process. Backuped the resulting database.
> > In
> > each of next tests I restored the database from backup. Next I run tests.
> > Since our application, OLTP, is very performance sensitive, we need to
> > have
> > good distribution of transaction execution time. In case if I run tests on
> > SQL2k5, when snapshot isolation level is not turned on, I see good
> > distribution - less than 0.5% of transactions executing more than 200ms,
> > average execution time is ~5 ms. In case if I change compatibility level
> > to
> > 90 and turn on allow_snapshot_isolation, behavior is dramatically
> > changing.
> > Near 5% of transactions start to execute more than 200 ms, average
> > execution
> > time is also significally increasing. There are no transactions which are
> > really use snapshot isolation level, stored procedures are exacly same as
> > before. There is no any memory pressure, no IO bottlenecks. During the
> > tests
> > counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
> > 5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2
> > GB
> > size, but only ~200 MB of the data was read in the tests. 1.2GB write
> > cache -
> > it means what all writing during the test was done only to write cache on
> > CX500, tests were not so long to fill the entire write cache. Windows 2003
> > x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
> > CPU load was ~50% in tests without snapshot isolation level, and ~70% with
> > tests with snapshot level. 2CPU.
> > From the test it looks like I can not remove replication to QueryDB and
> > run
> > queries on same computer where OLTP load...
> > Why impact of snapshot isolation level is so big?
> >
>
>|||There is a lot to consider with this. Snapshot isolation or read committed
snapshot write every single transaction into the version store. Since the
version store is in tempdb, in a high volume environment, you are in effect
pounding tempdb with every single transaction you issue. These are intended
to be applied when you have situations when you do not want reads to block
writes, BUT there is a performance trade-off when you implement that
functionality.
There are a couple of things you can do to minimize the impact of the
version store overhead. First, you need to move tempdb to its own disk
device so that it doesn't have to compete with everything else. Secondly,
you can add additional files (equal to the nmber of processors) to tempdb
that are of equal size in order to stripe the writes. BUT, you are still
going to have a performance impact.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:C3C9D444-5CD1-4C9A-9F2F-4BCD5DF26019@.microsoft.com...
>I understand what snapshot isolation adds some overhead, but I expected
> lesser overhead. CPU consumption near 1.4 times more in case if snapshot
> option turned on. It means what for most of performance sensitive OLTP
> applications, which currently use SQL2k and which use replication to
> another
> database used for longer queries, can not remove replication and run
> queries
> on same server where main OLTP load after migration to SQL2k5. Quite
> disappointing...
> "Kalen Delaney" wrote:
>> As soon as you change the isolation level to either Snapshot Isolation or
>> Read Committed Snapshot, SQL Server will start saving ALL updated data in
>> the version store in tempdb, whether or not any queries every read the
>> versioned data. So every changed row will have additional overhead
>> associated with it.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "andsm" <andsm@.discussions.microsoft.com> wrote in message
>> news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
>> >I did several tests of performance of SQL2k5, and I see quite strange
>> >results.
>> > I restored database from SQL2k, rebuilded indexes, updated statistics.
>> > I
>> > did
>> > all what described in migration process. Backuped the resulting
>> > database.
>> > In
>> > each of next tests I restored the database from backup. Next I run
>> > tests.
>> > Since our application, OLTP, is very performance sensitive, we need to
>> > have
>> > good distribution of transaction execution time. In case if I run tests
>> > on
>> > SQL2k5, when snapshot isolation level is not turned on, I see good
>> > distribution - less than 0.5% of transactions executing more than
>> > 200ms,
>> > average execution time is ~5 ms. In case if I change compatibility
>> > level
>> > to
>> > 90 and turn on allow_snapshot_isolation, behavior is dramatically
>> > changing.
>> > Near 5% of transactions start to execute more than 200 ms, average
>> > execution
>> > time is also significally increasing. There are no transactions which
>> > are
>> > really use snapshot isolation level, stored procedures are exacly same
>> > as
>> > before. There is no any memory pressure, no IO bottlenecks. During the
>> > tests
>> > counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data
>> > on
>> > 5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have
>> > 2
>> > GB
>> > size, but only ~200 MB of the data was read in the tests. 1.2GB write
>> > cache -
>> > it means what all writing during the test was done only to write cache
>> > on
>> > CX500, tests were not so long to fill the entire write cache. Windows
>> > 2003
>> > x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
>> > CPU load was ~50% in tests without snapshot isolation level, and ~70%
>> > with
>> > tests with snapshot level. 2CPU.
>> > From the test it looks like I can not remove replication to QueryDB and
>> > run
>> > queries on same computer where OLTP load...
>> > Why impact of snapshot isolation level is so big?
>> >
>>
>>

performance of indexed views

I see their benefit -- trust me. one question I have is that it looks like my indexed view has to get updated each time the underlying base table changes. What happens if I do an insert or bcp into the underlying table -- will me table go offline while this data gets re-aggregated -- is there a way for me to schedule this? I see UDAs as well, and I think they are more flexible, but I'd be concerned that they are getting too far away from the optimizer and how are they refreshed -- recompile of the code?

Help..and I know it's marketing -- but what direction is the best for people that use the product, UDAs or Indexed Views.

Indexed views get updated in-line with table updates, just as if you basically had an additional index on the base table. So, if you perform an insert into the base table, it will not commit/finish until all indexes on the base table AND indexes from materialized views are updated. They basically are very similair in the way that the engine ensures additional indexes on the base table are kept updated. The same applies to how statistics are updated and managed, the same as the corresponding base table. An indexed view has an associated b-tree(s) structure backing it that must be kept up to date just as the base table is in real-time with data modifications.

As for Indexed Views vs. UDA's, they are 2 totally different technologies for different uses...I really don't see how the 2 would even compare. A UDA is calculated from an instruction and dataset you pass it at runtime, with no physical backing at all (unless you create for example an indexed computed column that is based on the UDA, but I won't go there). A UDA is basically the same as using any of the existing built-in aggregate functions in SQL Server (i.e. sum(), count(), min(), max(), etc.).

HTH

|||

The other thing to be aware of is that you always have to have a clustered index on an indexed view (this is what defines it) If you therefore have additional indexes on the indexed view, you will have the additional knock on impact when the underlying data is changed.

Indexed views are great in that they provide any easy way of providing the indexed view data quickly to the user with little effort, however you need to appreciate the impact on performance as well as the restrictions on how data is updated (SET statements etc).

|||I don't understand the 2nd part of the response you say they are different technologies, I agree, but they can be used for the same things no? If I wnatd to sum the sales of a bunch of regions and roll them up I could use an Indexed View for that -- aka summary table -- no? That's what I can do with a UDA? Help me here..|||Hi Chad -- I think there's something I just picked up on that I didn't relase -- that the Indexed View is a preset aggregate, while the UDA takes the data set and perform the aggregation dynamically, correct? If so, then is this just not a set of logic to run on the data set? Would you recommend a UDA over calling a SP to do the work? Do you see a lot of people using this, or would suggest it? What does it buy me -- just trying to understand how much CLR objects we want and should use based on the countless resources that say it's good for some stuff and not for others :)|||

Yes, that is correct. You could use either to achieve what you are attempting to get, however the biggest difference between the 2 technologies is that an indexed view is materialized on-disk, just like a table, and a UDA performs the aggregation on the data-set at time of request, like a query against a table.

A UDA is just like you mentioned, basically logic that is performed against the data set...the advantage of a UDA in 2005 is that you can create your own aggregates that don't already exist as pre-defined (i.e. sum(), min(), max())...for example, you could create a median() aggregate for example.

As for why you would use a UDA over a stored procedure would come down to a couple of things that would be different for many different scenarios, including performance, encapsulation, type of use, etc. For example, you could use a UDA just like you could use an existing pre-defined aggregate (i.e. within a select statement), like this:

select sum(column), myuda(column) from table

whereas you couldn't do the above with a stored procedure. Also, you may need to perform complex computational logic on the data, which the CLR would be better at. However, if you're just grabbing data, a stored procedure may be better...all would depend on the scenario.

HTH,