May 17, 2012 at 4:07 am
I will show how you could use your own Optimizer and statistics to tune queries which can not be tuned in other ways.
But before taking this path you need to make sure the following.
1. The person who is going to do this should know the data pefectly and most importantly should know how this is being queried and all kind of patterns related to proc execuetion.
2. Try with "optimize for unknown". This will basically avoid the parameter sniffing but still will provide a single plan which should be best for most of queries. Lots of queries should be benefitted by doing this.If this doesnt give you desired results go to next step.
3. Then use the recompile hint. This will give best plan for all of your execution but will have extra overhead of the recompilation. Most of your queries should be benefitted by doing this. This should be enough for 99% of queries. But if your data is too much skewed so that even recompile is not giving you what you want.Go to step 4
4. Try to have your own optimizer which is described below.
/*
I will explain how you could use custom statistics and could have your own optimizer which could perfrom better
then sql optimizer when the statistics are skewed.In these cases optimizer uses the averag stats and generate a bad plan
which is not good for all scenario.
Let us start by creating a table with skewed data. This will have one row for id 1,2 for id 2 and so on...
This table is being accessed based on id say 5 times a minute during day. The table is accessed through a proc.
*/
if OBJECT_ID('dbo.SkewedTable','U') is not null
begin
drop table SkewedTable
end
go
create table SkewedTable
( rowid int identity not null,
id int not null,
uselessdata char(2) not null
)
go
alter table SkewedTable add constraint PK_SkewedTable_Rowid primary key (rowid)
go
--this will insert around 2 million rows..
insert into SkewedTable with(tablock) (id,uselessdata)
select id.cnt as id,'UD'
from Nums id,Nums recs
where id.cnt <= 2000
and recs.cnt <= id.cnt
order by recs.cnt,id.cnt
go
create nonclustered index IX_SkewedTable_ID on SkewedTable(id,rowid)
go
--update the statistics
update Statistics SkewedTable with fullscan
go
--check the size of the table..
select * from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.SkewedTable','U'),null,null,null)
go
/*
Index_depth is 3 for IX_SkewedTable_ID Index_depth for Index on Primary key that is clustered index is 3 as well
Thus to access a signle row using the index IX_SkewedTable_ID will clock 6 logical IO's.
But to access the row with more data say for id 100 it will be around 3* 100 + a few logical IO for non clustered index
scan.On an average we could say that to get a row from table IOs will be number of rows retruned * 3
Table page count is 4698...
Thus threshold where the non clustered index seek + key lookup will perform better than table scan if number
of rows returned are less than 4698/3 = 1550 approx...
If rows returned are more than 1550 then table scan will perform better..This threshold and analysis will be part of your
custom statistics and your own optimizer..
Also using index scan + key lookup will cause random IO's and speed of Random IO's are less than sequential when
It will be read from disk.
*/
dbcc show_statistics ('dbo.SkewedTable','IX_SkewedTable_ID')
go
---now create the proc to access the data
if OBJECT_ID('dbo.uspSkewedTableQuery','P') is not null
begin
drop procedure uspSkewedTableQuery
end
go
create procedure uspSkewedTableQuery
(
@id int
)
as
begin
select * from SkewedTable
where id = @id
end
go
--now execute the proc
set statistics io,time on
go
exec uspSkewedTableQuery 1
--It used just 6 IO's and used the non clustered index as expcted IX_SkewedTable_ID.
--Plan is in the cache so It will be used every time.
--now exec query with say id 100
exec uspSkewedTableQuery 100
--As expected used 303 Logical IO's and 88ms elapsed time.
--Try with 1000 rows
exec uspSkewedTableQuery 1000
--3006 IO's and 79 ms elapsed time.It has less elapsed time than 100 rows.I checked it multiple times with similar results. That is why I am
--not using elapsed time as part of my tuning criteria. I will write something on this as well some day.
--try with threshold value
exec uspSkewedTableQuery 1550
--4656 Logical IO's.It is almost same as table scan cost and 152 ms elapsed time
--Try with 1600
exec uspSkewedTableQuery 1600
--4806 Logical IO's.It is aroudn 100 more than table scan cost and 172 ms elapsed time
--Try with 2000
exec uspSkewedTableQuery 2000
--6007 Logical IO's.It is aroudn 1300 more than table scan cost and 103 ms elapsed time
--Now we will recompile the proc and call with 2000
--Try with 2000
exec sp_recompile 'SkewedTable'
exec uspSkewedTableQuery 2000
--4738 IO's and 115ms elapsed time.It used parallelism as well..
/*
So till now we tried to find what was the threshold beyond which table scan is better than index seek + lookup.
Now suppose this proc was created in proc and is executed 5 times a minute and the number of rows are quite random
If First executeion was for id 1. It will use the nested loop.Then every execution will use this plan..
Thus any call which returns more than 1570 rows will use the inefficient plan.i.e. approc 20% of total queries if we
consider call for each id will happen almost same number of times.
There is no gurantee that first call will be for id 1.What if it is for 2000 rows. It will use table scan
and so does any other call to the proc. Thus just to return 1 row it has to do around 4738 IO's. This will
cause around 80% of queries to perfoprm badly and the performance difference is way too mcuh between efficient and
and inefficient plan.
In my case the ratio is 20 to 80.It could be any ratio.
This is where we could use a better plan if we know the data very well and how that data will grow in future etc.
So what could we do to improve the perforamnce of all calls. One simpel thing comes to mind.Use recompile so that
most efficient plan is generated everytime.Let us use the recompile.
*/
if OBJECT_ID('dbo.uspSkewedTableQuery','P') is not null
begin
drop procedure uspSkewedTableQuery
end
go
create procedure uspSkewedTableQuery
(
@id int
)
as
begin
select * from SkewedTable
where id = @id
option (recompile)
end
go
--now try some execution
exec uspSkewedTableQuery 2000 -- Nice table scan
exec uspSkewedTableQuery 100 --Nice index seek + key loopkup
exec uspSkewedTableQuery 1 --Nice index seek + key loopkup
/*
Recompile solved the problem and we are fine. Nothing needs to be done.
Let me tell you the disadvantages of the recompile. I mentioned that query is executed quite frequently aorund 5
plans per minute...Recompilations uses resources such as latches ,locks ,cpu time etc.5 recompilations per minute will
throw some of other procs out of the cache and will cause recompilation of other procs as well. Thus these could have very bad effects
on busy system with a number of users.This usually results in less scalable application.
However, there is one more issue.Some of execution might still not get the best plan.Our threshold is 1570.Above which
table scan is better than index seek + look up.So let us try with 1600.
*/
exec uspSkewedTableQuery 1600 --what happened no table scan but a index seek + key lookup.4900 IO's
--Optimizer has selected a less efficient plan.
--Now choose 1700
exec uspSkewedTableQuery 1700 -- no table scan till now around 5200 IO's
--try 1800 rows
exec uspSkewedTableQuery 1800 --still no table scan around 5500 IO's. What the hell is wrong with
--optimizer i know that it should use table scan as that needs just 4700 IO's.
exec uspSkewedTableQuery 1900 --Finally i could see table scan.Is there something wrong with the optimizer?
--Maybe or maybe not.We are just considering IO cost but optimizer uses CPU cost as well which as a developer we do not
--have any idea.But I do not know on what basis it is using the cpu cost..But doing 800 more io's in case of 1800 rows
--doesnt sound right even optimizer is using the cpu cost as well. I will write on this as well soon..If I will get something
--conclusive.
--Thus around 15% queries still did not perform as expected even with recompile.Also, it added overhead for all 100%
--queries. Only 5% queries got benefitted at the expense of all the 100% queries.
/*
What could we do to make all the queries 100% efficient? We will create our own optimizer and statistics
for our optimizer based on our query and data.How do we do that?I will show you..
As you have noticed that estimated number of rows shown when the proc was using recompile for the statement were
not near actual number of rows specially for values which return less number of rows.For 100 it was shwoing estimated rows
as 64.5 but actual rows are 100. Thus we need a way to get a better idea of the estimated rows.Based on this estimated
rows we will decide what plan to choose. Thus we need to create two tables
One will keep the estimated rows needed by us and other one will just keep one row and 2 columns.It will have threshold value
beyond which table scan is better than index seek + key lookup.In Our case it is 1570.
I am creating the table to keep threshold value so that if you need to do similarly with other tables and queries.use the same table.
*/
if OBJECT_ID('dbo.Thresholds','U') is not null
begin
drop table Thresholds
end
go
create table Thresholds
(
Tablename sysname not null,
ThresholdValue int not null
)
go
--You can create an index as well if you keep the data for more tables..
insert into Thresholds
select 'SkewedTable',1600
--truncate table Thresholds
/*
I have used 1600 as threshold value instead of 1570 as threshold value. This I have done to compensate some of the
cpu cost.
*/
go
-- You can add another column called index name as well in above table and then use the diff threshold values
--for eahc index on the table..
--Now create the our own stats table
if OBJECT_ID('dbo.SkewedTableStats','U') is not null
begin
drop table SkewedTableStats
end
go
create table SkewedTableStats
(
id int primary key not null,
Rowcnt int not null
)
go
insert into SkewedTableStats
select id,COUNT(*) from
SkewedTable
group by id
go
update Statistics SkewedTableStats with fullscan
go
--Now we got our own stats use our own optimizer which can decide what should be the
--plan based on the input param @id
if OBJECT_ID('dbo.uspSkewedTableQuery','P') is not null
begin
drop procedure uspSkewedTableQuery
end
go
create procedure uspSkewedTableQuery
(
@id int
)
as
begin
declare @estrowcnt int,@thresholdvalue int,@actualrowcnt int
--find the number os rows returned by the given id
select @estrowcnt=Rowcnt from SkewedTableStats where id = @id
--find the threshold value
select @thresholdvalue= ThresholdValue from Thresholds where Tablename = 'SkewedTable'
--select @thresholdvalue,@estrowcnt
--now check whether @estrowcnt is greater than @thresholdvalue if @estrwocount > @threshold value use table scan
--else use index seek + key klookup
if (@estrowcnt >= @thresholdvalue)
begin
--Table scan..
select * from SkewedTable with (index =PK_SkewedTable_RowId )
where id = @id
end
else
begin
--index seek
select * from SkewedTable with (index =IX_SkewedTable_ID )
where id = @id
end
end
go
--now is time to test few scenarios
exec uspSkewedTableQuery 1
go
--Index seek 6 IO's + 1 IO
exec uspSkewedTableQuery 100
go
--Index seek 303 IO's
exec uspSkewedTableQuery 1600
go
--Table scan (clustered index scan) 4738 IO's
exec uspSkewedTableQuery 1800
go
--Table scan (clustered index scan) 4738 IO's
exec uspSkewedTableQuery 2000
go
--Table scan (clustered index scan) 4738 IO's
/*
Great we got the plan we wanted for all scenarios yet no recompiles.Just by using our little knowledge about the data
and how it is used.
This is great but it has overhead as well which needs to be considered. What are these overheads?
1. We are querying two extra tables. Innout case this is just 3 IO's which is negligible and mostly it will be negligible
only. Thus you can ignore this kind of overhead.
2. Thresholds table updates.It has the threshold value this will remain same most of time except when the index depth
of the index used will be changed this needs to be updated. Thus this table needs very few updates.Thus You can update
it during weekend or once a week on a time when there are not much activity. This can be ignored as well.
3. Stats table. This table have little bit overhead as the data in the main table will be changed this table needs to be updated.Below are possible ways to update this table
and the overheads associated with it.
a. If the data in the table changes in similar proportions then you do not need to update the table real time.Rather update once a day.
THus overhead is to populate it everyday with latest data. This is also negligible most of times unless the index size on tables is too huge.
b. If the data changes randomly and there are lots of fluctuations then you can have a trigger to update the stats table. Even auto stats will have similar kind of
overhead.
c. This is my favourite way to update the stats table with almost negligible overhead in case when data fluctuates too much.But this could be used with specific
kidn of queries and not always.
e.g. in our proc we can use this strategy as the final query will return actual number of rows in the table.If there were sarg on final query which restricts the
result set we can not use this strategy.
if OBJECT_ID('dbo.uspSkewedTableQuery','P') is not null
begin
drop procedure uspSkewedTableQuery
end
go
create procedure uspSkewedTableQuery
(
@id int
)
as
begin
declare @estrowcnt int,@thresholdvalue int,@actualrowcnt int
--find the number os rows returned by the given id
select @estrowcnt=Rowcnt from SkewedTableStats where id = @id
--find the threshold value
select @thresholdvalue= ThresholdValue from Thresholds where Tablename = 'SkewedTable'
--select @thresholdvalue,@estrowcnt
--now check whether @estrowcnt is greater than @thresholdvalue if @estrwocount > @threshold value use table scan
--else use index seek + key klookup
if (@estrowcnt >= @thresholdvalue)
begin
--Table scan..
select * from SkewedTable with (index =PK_SkewedTable_RowId )
where id = @id
set @actualrowcnt = @@rowcnt
--if actual row count falls below threshold than update the actual rowc ount in stats table.
if (@actualrowcnt < @thresholdvalue)
begin
update SkewedTableStats
set rowcnt = @actualrowcnt
where id= @id
end
end
else
begin
--index seek
select * from SkewedTable with (index =IX_SkewedTable_ID )
where id = @id
set @actualrowcnt = @@rowcnt
if (@actualrowcnt >= @thresholdvalue)
begin
update SkewedTableStats
set rowcnt = @actualrowcnt
where id= @id
end
end
end
go
So this has minimum overhead and kind of resemble the async update stats configure parameter of SQL server
I have mostly used above 3 methods to update my stats table.Specially 1 and 3.
4. In SQL server you could use an indexed view instead of the stats table. It will have its overhead as well.However, if you have indexed view on this table
and which uses the aggregate function for some other purpose in your application. Then this is the best way as you will have almost minimal overhead.
As you have seen that using your own optimizer and stats have some overhead. Thus before using this strategy make sure that the gain in performance is more than
the overhead associated with it. If your stats table could be updated using methods in 1,3 and 4. Then it has almost no overhead.
There are cases when you do not need stats table at all in that case you can use this strategy without any overhead. I will explain this in next post.
Fine.What kind of scenario we can use our own stats and optimizer.
1. In the case when the column (s) data is too much skewed and almost all the values in the column are used to get the data from the table.e.g. in this case.
2. When the column data is distributed almost equally but your queries used < ,> or between instead of equal. This strategy could be used to get best plan.
3. This strategy deciding your own plan based on stats will eliminate parameter sniffing (however, you will still see the estimated number of rows etc based on first
execution but performance wise parameter sniffing is almost eliminated). Thus if you have parameter sniffing try to use this to eliminate.
4. This can be used when you get the data ito some intermediate table like temp table and then you get the data from your main tables based on that temp table.
I will explain this in next post. This doesnt need a stats table at all.
Conclusion: You can have your own optimizer and stats to make sure that you get the best plan. This depends mainly on the data you have,how this data is accessed etc.
*/
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 17, 2012 at 5:34 am
And then what do you do when the distribution of the data changes?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 17, 2012 at 5:55 am
I have mentioned in the script that this startegy depends on knowing future growth of your data.
In the application where I have implemented this I had an extra table which had the proc name and a flag called UseMyoptimizer. Thus check the flag as first statement in the procedure. If it is set use my own optimizer other wise the select statement(the one which is bringing the data). If the data distribution is changed and one plan is the best for all set this flag to N so that SQL serv optimizer will be used. Same Flag could be used while updating the statistics table. Thus when you no longer needs your own statistics no need to update it.
I usually test my proc against the rdbms optimizer every 4 week to make sure that my optimizer is better than sql one. The day SQL one will be better than mine I will switch the flag.
True power of this will be realized when you get the data into a temp table from other proc or other sql statements and then you join this temp table with your table. In This case, no need to keep the stats table and thus almost no overhead.But I still uses the flags. You never know when optimizer is changed to perforam better.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 17, 2012 at 6:33 am
There are many, many better ways to deal with parameter sniffing problems than forcing specific indexes based on hardcoded row values that have to be manually updated (how long will that re-evaluation take for several hundred procedures?) and probably aren't supportable by anyone other than the original developer
Option recompile
Option optimise for
Dynamic SQL
Modifying index design
Multiple stored procedures
More than one of those
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 17, 2012 at 7:34 am
What is other better way to remove parameter sniffing aprt from the recompile.I would like to know. This s only when you have skewed distribution.When optimizer is not doing what you expect then only try it.
There are no manual updates. It all depends on your data and you strategy to update the stats table.
BTW, DBA's update the stats as well manually to make sure that your system works fine.Simlar way you can have you own stats updated either automatically or manually.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 17, 2012 at 7:40 am
Gullimeel (5/17/2012)
What is other better way to remove parameter sniffing aprt from the recompile.I would like to know. This s only when you have skewed distribution.When optimizer is not doing what you expect then only try it.There are no manual updates. It all depends on your data and you strategy to update the stats table.
BTW, DBA's update the stats as well manually to make sure that your system works fine.Simlar way you can have you own stats updated either automatically or manually.
The options that Gail listed. Using the OPTIMIZE FOR hint is one way to deal with parameter sniffing. Either using OPTIMIZE FOR a particular value to get a particular execution plan, or OPTIMIZE FOR UNKNOWN to go for a generic plan. Better still would be to put in filtered indexes that will allow for indexes focused on the skew of data. Combine that with either a RECOMPILE or separate stored procedures & you have a process that uses well established routes rather than simply applying an index hint to take all control away from the optimizer.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 17, 2012 at 7:55 am
None of these Optimize for wont work when you want different plan based on your criteria. Once a plan is generated then that plan will be used unless it is out of the plan cache.
You will use "Optimize for" option when you are sure that most of your execution are very well satisfied with a particular type of plan. If that is the case then my method is not suitable at all because one plan for all queries are handled perfectly by optimizer.
As far as taking control away from the optimizer is concerned we always want to do that ( by not recompiling our procs and statements ). Once Plan is generated you have already taken the control from optimizer (unless off course you have some 20% rows are changed or statistics are updated etc).
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 17, 2012 at 7:58 am
Gullimeel (5/17/2012)
None of these Optimize for wont work when you want different plan based on your criteria. Once a plan is generated then that plan will be used unless it is out of the plan cache.You will use "Optimize for" option when you are sure that most of your execution are very well satisfied with a particular type of plan. If that is the case then my method is not suitable at all because one plan for all queries are handled perfectly by optimizer.
As far as taking control away from the optimizer is concerned we always want to do that ( by not recompiling our procs and statements ). Once Plan is generated you have already taken the control from optimizer (unless off course you have some 20% rows are changed or statistics are updated etc).
Using different procedures results in different plans & it's still not trying to force stuff on the optimizer.
If your tuning approach is to try to fight the optimizer, more power to you, but not everyone is going to agree.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 17, 2012 at 8:10 am
I wish you'd posted this this in the scripts area so it could be voted upon. . .
May 17, 2012 at 8:31 am
I do not want to fight optimizer.I want to help optimizer where he can not fight on his own.
It is like "Optimize For" hint.Just helping optimizer to generate the plan ,which we want (this in fact is bad statement.What we want might not be the best) rather which is best.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 17, 2012 at 8:34 am
I have put it there as well.I guess it will be published in next few days. I have not put this method to test whether people will like it or not.It is just to show the different perspective of looking into perfroamnce problems.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 17, 2012 at 11:01 am
OPTION (RECOMPILE) would be my choice here. The execution plans generated are not cached, so pushing other plans out of cache is not a concern. On my laptop, recompiling the statement in the procedure took 150µs (0.15 milliseconds, if you prefer). This is a very small price to pay for the very good plans produced for all cases - and certainly less than the overhead of executing even one of the extra statements in the procedure required by the 'own optimizer' code. If the procedure were being called many thousands of times a second, it might be a concern, but the 5 times per minute quoted in the comments is a very long way from that. Again, the overhead of the extra statements in the procedure would likely always be more expensive than the 150µs compilation time.
Optimizing for logical I/O alone is a serious error. There are good reasons that the optimizer's costing component considers many factors (including buffer pool use, processor time, worker thread usage, context switches, expected physical and logical I/O). Not all logical I/O is equal: a bookmark lookup in a heap requires one logical I/O. The same lookup on a clustered table with an index depth of 3 requires 3 logical I/Os, and yet key lookups are not three times slower than RID lookups. The 'own optimizer' code makes no allowance for the cost of the gather streams exchange and parallel scan (using multiple worker threads) for example.
There are other considerations (such as running tests with Actual Execution Plan OFF!) and understanding the role of attributes like prefetch on the nested loops join, but I would be here all day addressing every issue in that demo script. Full marks for thinking creatively about a problem, but I would strongly encourage you to rethink this (where rethinking equals throwing it away and using standard techniques like RECOMPILE instead).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 17, 2012 at 11:07 am
Just to jump on the bandwagon: I've been performance tuning SQL Server for 11 years now, and I'd never use this "own optimizer" solution. Lots of better, more standard, more scalable, ways to do this.
All you seem to be doing is making future refactoring/tuning much, much harder.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 17, 2012 at 11:30 am
The overhead of the querying two extra tables is just 3 logical IO's.5 compilation a minute is not a big overhead in isolation but when you have other processes going on it could cause the contention on proc cache. If you see the script i have mentioned that the other problem with using recompile is that it doesnt generate in my case a table scan untill it has around 1900 rows.
As you will see that in my script I have mentioned that the cost is not alone decided by the logical IO but it includes cpu as well as physical IO's but usually CPU factor is not as big as the logical or pysical IO's. Thus I have added some extra logical IO's to actual threshold value to determine where the cost of table scan would be less than the index seek + key lookup. Thus you could try different method to come up with a correct threshold value. e.g.
If table is not memory then physical Io's will be better with the table scan than the Index scan. Thus this could be a factor where you could decrease the threshold.
Thus You have to find a good value for threshold.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 17, 2012 at 11:42 am
I am not saying that it will make your future tuning easy or something like that. You have to keep looking for whether you query is still performaning better than the query without using the hints. It will be on going process.Tuning and improvements are constant.
Rgearding you have never used your own optimizer in 11 years. I am sure once in these 11 years you must have used hints 🙂 or have changed the sql so that it exploits the optimizer in better way.
e.g. In SQL server 2008 R2 you have aggregate anaytical function but these are not performing better than the alternate long methods. Thus you may not use aggregate analytical function but use the alternates and that doesnt mean that you are making future tuning hard ( it is possible that in ext version of SQL server aggregate analytical function perfoms better than alternate methods).
I have to think about the future but I can not ignore the present as well.
I would like to know the more scalable method.I know one that is recompile.Any other is welcome..
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
Viewing 15 posts - 1 through 15 (of 49 total)
You must be logged in to reply to this topic. Login to reply