where to store stored procedure in federated db

Hi,

While our current server is at its maximum in hardware terms and can hardly support the the database usage in performance terms anymore, we are planning to use a the federated database concept and set up a second server.
the database can easily be split in several funtional areas and therefore we want to put complete tables on the second server (is this wise?) and create a partitioned view for each table. I am wondering where to put the stored procedures that makes us of these tables to gain as much performance as possible. Should they reside on the 2nd server and called from a stored procedure on the 1st server (so application does not need to be changed), or can they just stay on the 1st server and simply use the partitioned view?

Any help is appreciated!

Luis,

No, we are not planning to put all the tables on the second server only the tables belonging to a specific functional area which will be initial 2 tables storing images and sounds(both globs). Also the indexes will than be stored on 2nd server. All other tables will stay on the 1st server
The application, which is a web application, only connects to the 1st server. the application should see both physical servers as one (logical) server.

I hope this explained it a bit more.

Marnix

Well - some thoughts ... these are just thoughts - never had the chance to work on federated db's ...<br /><br />Would it make sense to try and split the system on functional lines, and try and house stored procedures on the same server as the data so try and keep stored procedures that reference tables A,B and C on the same server as table A,B and C. This would make be logical especially if you can segment your database in functional type area that hang together well. For example production and order and sotck type code and tables might live on one server , while say billing and client information on another, and then obviously order type processing would work across both ... it *seems* to me it would make sense to try and keep as little cross over stored procedures as possible.<br /><br />Now someone who knows about federated db's can shoot down my comments <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br /><br /><br />Panic, Chaos, Disorder ... my work here is done --unknown

Luis,

Is the place of the SP not relevant for which server it will take processor power? In other words will the CPU power be used from the server that stores the table and not from the server that stores the SP? We want to shift as much CPU usage possible to the 2nd server.

Hi marnix,

I'd place the stored procedures on the server that has the tables referred to within the procedures. Then you'd need to add a proxy set of procedures in the database to which the application connects. Where a proxy procedure is just one that does nothing except call the same named procedure on the other server.

You may need to think about recoverability, i.e. what happens if one of the servers needs to be restored? Does it matter that the database may be out of synch with each other?

As a first step though, you might like to turn on something like Profiler and just check that there are no queries taking more than say 100ms duration/cpu...?

Cheers
Twan

porting an existing application to a federated database is an enormously difficult task.

before commiting to such, i really think it is worth discussing:
1) what are your current hw details and avg cpu util
2) how much you expect user load to increase by
3) what performace analysis has been done

Hi Joechang,

Can you explain why porting the application to a federated db is such a difficult task?

Our current HW is:
2x PIV 3.06 Xeon processor
3 Gb internal memory
5x SCSI 36 GB RAID 5 HD

The avg CPU util is above 90% every evening with aprox 2500 concurrent users. (when less than 2000 users the CPU util is about 80% what is fine with us regarding performance)
All queries have short execution times and are executed via SP. Large number of queries are executed.

We expect that the user load will increase with 500 concurrent users within 6 months which we can not handle.

While we will put entire tables on the second server I don't expect enormous problems.
If there are particular things we need to take into account using a federated db please let me know.

Marnix

Luis,

I just monitored the counters, The Average Disk Queue Length is about 0.35 with a max at 3.7

Marnix

Luis,

Are there specific points in the implementation when changing to federal to optimise Processor use?
We are planning to store SP with max processor use on the 2nd server and also store the tables being used by these SP's on this server.

Marnix

Luis,

Thanks for the advice. We will have a 2nd server next week for testing. I will post the experiences.

Marnix

How high is the value for memory pages/sec?

If you run profiler to look for statements taking longer than say 100ms, do you get anything?

Cheers
Twan

i scoped out a Federate project once for one of my customers,
~100 tables and 200 stored procs that needed to be changed and retested
other people had input, but it looked like 10 developers x 2 months, (much of this in testing) given US personnel costs
it was cheaper to go with an 8 CPU system (PIII days)

its possible your application is not as complex, so porting may be simpler,
also, i was not convinced on the scaling to expect

with sw performance tuning, i gave them 4X at $20K, so it became a moot point anyways.
is your application mostly stored procedures?
if so, i can give you a script for parsing profiler traces for cpu usage by stored proc.
in almost all cases, some tuning should give you reasonable additional bandwidth,

i also think you ought to consider a 4xXeon 2.8, even this costs more than an additional 2xXeon box, it will require less development porting effort

Twan,

The most of the time the value for pages/sec=0. The avg = 0.4 (max=a peak of 30)
There is one (heavily used) stored procedure that takes more than 100 ms (aprox 250 ms depending on the input parameters). This is a SP that dynamically builds the SQL statement. We already put some effort in changing this SP but only with little result.

Joechang: Our application is mostly SP so I would be very interested in your script.

Marnix

Are you willing/able to post the code in the proc to see if anyone here can think of ways to improve it?

Cheers
Twan

Off Course. Here it is:<br /><br />create procedure dbo.sp_rp_Search_Members_Extended_v3_0<br />*<br />* Description: Fetches @ROWS records and a record count for search<br />* actions on the viewSearch VIEW. Also returns the size <br />* of the picture (if available)<br />*<br />*<br />* Parameters: 23<br />*<br />* Returned recordsets: 2 (count, data) <br />*<br />*********************************************************************/<br />@geslacht int,<br />@geslacht_gezocht nvarchar(1),<br />@leeftijd_min int,<br />@leeftijd_max int,<br />@foto nvarchar(2),<br />@land nvarchar(20) = '1,2,999',<br />@aantal_kinderen nvarchar(1),<br />@opleiding nvarchar(255),<br />@haarkleur nvarchar(255),<br />@haarlengte nvarchar(255),<br />@figuur nvarchar(255),<br />@lengte nvarchar(255),<br />@gewicht nvarchar(255),<br />@kleur_ogen nvarchar(255),<br />@roken nvarchar(255),<br />@alcohol nvarchar(255),<br />@geloof nvarchar(255),<br />@gewenste_relatie nvarchar(1),<br />@provincie nvarchar(40) = '1,2,3,4,5,6,7,8,9,10,11,12',<br />@kinderwens nvarchar(1),<br />@burgerlijke_staat nvarchar(255),<br />@sterrenbeeld nvarchar(255),<br />@laatstGewijzigd bigint,<br />@pageno int,<br />@rows int,<br />@view int<br />as<br />SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED<br /><br />DECLARE @sqlGeslacht nvarchar(255),<br />@provincieStr nvarchar(100),<br /> @landStr nvarchar(40),<br />@opleidingStr nvarchar(255),<br /> @haarkleurStr nvarchar(255),<br />@haarlengteStr nvarchar(255),<br />@burgerlijke_staatStr nvarchar(255),<br />@sterrenbeeldStr nvarchar(255),<br />@figuurStr nvarchar(255),<br />@lengteStr nvarchar(255),<br />@gewichtStr nvarchar(255),<br />@kleur_ogenStr nvarchar(255),<br />@rokenStr nvarchar(255),<br />@alcoholStr nvarchar(255),<br />@geloofStr nvarchar(255),<br />@sqlOpleiding nvarchar(255),<br /> @sqlHaarkleur nvarchar(255),<br />@sqlHaarlengte nvarchar(255),<br />@sqlFiguur nvarchar(255),<br />@sqlLengte nvarchar(255),<br />@sqlGewicht nvarchar(255),<br />@sqlKleur_ogen nvarchar(255),<br />@sqlRoken nvarchar(255),<br />@sqlAlcohol nvarchar(255),<br />@sqlGeloof nvarchar(255),<br /> @sqlFoto nvarchar(255),<br />@sqlKinderen nvarchar(255),<br /> @sqlProvincie nvarchar(255),<br />@sqlBurgerlijke_staat nvarchar(255),<br />@sqlSterrenbeeld nvarchar(255),<br />@sqlKinderwens nvarchar(255),<br />@sqlLand nvarchar(255),<br />@sqlRelatie nvarchar(255),<br /> @whereSql nvarchar(1000),<br /> @cursor nvarchar(1000),<br /> @rowCount int,<br />@sql nvarchar(1000)<br />/* Create Where clause */<br /><br />-- make a list for an IN clause<br />SET @provincieStr = replace(@provincie,',',''',''')<br />SET @burgerlijke_staatStr = replace(@burgerlijke_staat,',',''',''')<br />SET @sterrenbeeldStr = replace(@sterrenbeeld,',',''',''')<br />SET @landStr = replace(@land,',',''',''')<br />SET @opleidingStr = replace(@opleiding,',',''',''')<br />SET @haarkleurStr = replace(@haarkleur,',',''',''')<br />SET @haarlengteStr = replace(@haarlengte,',',''',''')<br />SET @figuurStr = replace(@figuur,',',''',''')<br />SET @lengteStr = replace(@lengte,',',''',''')<br />SET @gewichtStr = replace(@gewicht,',',''',''')<br />SET @kleur_ogenStr = replace(@kleur_ogen,',',''',''')<br />SET @rokenStr = replace(@roken,',',''',''')<br />SET @alcoholStr = replace(@alcohol,',',''',''')<br />SET @geloofStr = replace(@geloof,',',''',''')<br /><br />-- create individual where statements based upon variabele<br />if @aantal_kinderen = ''<br /> SET @sqlKinderen = '' <br />else <br /> SET @sqlKinderen = CHAR(13) + CHAR(9) + ' and aantal_kinderen_id ' + <br /> CASE @aantal_kinderen<br /> WHEN 1 THEN '&gt;1'<br /> WHEN 0 THEN '=1'<br /> END<br /><br />if @provincie = ''<br /> SET @sqlProvincie = ''<br />else <br /> SET @sqlProvincie = CHAR(13) + CHAR(9) + 'and provincie_id in (''' + @provincieStr + ''')' <br /><br />if @burgerlijke_staat = '' <br /> SET @sqlBurgerlijke_staat = ''<br />else <br /> SET @sqlBurgerlijke_staat = CHAR(13) + CHAR(9) + 'and burgerlijke_staat_id in (''' + @burgerlijke_staatStr + ''')' <br /><br />if @sterrenbeeld = '' <br /> SET @sqlSterrenbeeld = ''<br />else <br /> SET @sqlSterrenbeeld = CHAR(13) + CHAR(9) + 'and sterrenbeeld_id in (''' + @sterrenbeeldStr + ''')' <br /><br />if @foto = '0' or @foto = ''<br /> SET @sqlFoto = ''<br />else <br /> SET @sqlFoto = CHAR(13) + CHAR(9) + ' and ImgWidth ' + <br />CASE @foto <br />WHEN 1 THEN 'IS NOT NULL'<br />WHEN 2 THEN 'IS NULL'<br />END<br /><br />if @land = ''<br /> SET @sqlLand = ''<br />else <br /> SET @sqlLand = CHAR(13) + CHAR(9) + ' and land_id = ' + @landStr + ''<br /><br />if @opleiding = ''<br /> SET @sqlOpleiding = ''<br />else <br /> SET @sqlOpleiding = CHAR(13) + CHAR(9) + ' and opleiding_id in (''' + @opleidingStr + ''')'<br /><br />if @haarkleur = ''<br /> SET @sqlHaarkleur = ''<br />else <br /> SET @sqlHaarkleur = CHAR(13) + CHAR(9) + ' and haarkleur_id in (''' + @haarkleurStr + ''')'<br /><br />if @haarlengte = ''<br /> SET @sqlHaarlengte = ''<br />else <br /> SET @sqlHaarlengte = CHAR(13) + CHAR(9) + ' and haarlengte_id in (''' + @haarlengteStr + ''')'<br /><br />if @figuur = ''<br /> SET @sqlFiguur = ''<br />else <br /> SET @sqlFiguur = CHAR(13) + CHAR(9) + ' and figuur_id in (''' + @figuurStr + ''')'<br /><br />if @lengte = ''<br /> SET @sqlLengte = ''<br />else <br /> SET @sqlLengte = CHAR(13) + CHAR(9) + ' and lengte_id in (''' + @lengteStr + ''')'<br /><br />if @gewicht = ''<br /> SET @sqlGewicht = ''<br />else <br /> SET @sqlGewicht = CHAR(13) + CHAR(9) + ' and gewicht_id in (''' + @gewichtStr + ''')'<br /><br />if @kleur_ogen = ''<br /> SET @sqlKleur_ogen = ''<br />else <br /> SET @sqlKleur_ogen = CHAR(13) + CHAR(9) + ' and kleur_ogen_id in (''' + @kleur_ogenStr + ''')'<br /><br />if @roken = ''<br /> SET @sqlRoken = ''<br />else <br /> SET @sqlRoken = CHAR(13) + CHAR(9) + ' and roken_id in (''' + @rokenStr + ''')'<br /><br />if @alcohol = ''<br /> SET @sqlAlcohol = ''<br />else <br /> SET @sqlAlcohol = CHAR(13) + CHAR(9) + ' and alcohol_id in (''' + @alcoholStr + ''')'<br /><br />if @geloof = ''<br /> SET @sqlGeloof = ''<br />else <br /> SET @sqlGeloof = CHAR(13) + CHAR(9) + ' and geloof_id in (''' + @geloofStr + ''')'<br /><br />if @geslacht_gezocht = '0' or @geslacht_gezocht = ''<br /> SET @sqlGeslacht = ''<br />else <br /> SET @sqlGeslacht = CHAR(13) + CHAR(9) + ' and geslacht_id = ' + @geslacht_gezocht + ''<br /><br />if @gewenste_relatie = '0' or @gewenste_relatie = '' <br /> SET @sqlRelatie = ''<br />else <br /> SET @sqlRelatie = CHAR(13) + CHAR(9) + ' and gewenste_relatie_id = ' + @gewenste_relatie + ''<br /><br />if @kinderwens = '0' or @kinderwens = ''<br /> SET @sqlKinderwens = ''<br />ELSE<br /> SET @sqlKinderwens = CHAR(13) + CHAR(9) + ' and kinderwens_id = ' + @kinderwens + '' <br /><br /><br />-- build the where statement<br />SET @whereSql = 'where <br /> (ik_zoek_id = 0 or ik_zoek_id = ' + CONVERT (nvarchar(1),@geslacht) + ')<br /> and geboortedatum between ' + CONVERT (nvarchar(<img src='/community/emoticons/emotion-11.gif' alt='8)' />,@leeftijd_max) + ' AND ' + CONVERT (nvarchar(<img src='/community/emoticons/emotion-11.gif' alt='8)' />,@leeftijd_min) + ' ' <br />+ @sqlGeslacht <br /> + @sqlProvincie <br /> + @sqlLand<br /> + @sqlKinderen<br /> + @sqlOpleiding<br />+ @sqlHaarkleur<br />+ @sqlHaarlengte<br />+ @sqlFiguur<br />+ @sqlLengte<br />+ @sqlGewicht<br />+ @sqlKleur_ogen<br />+ @sqlRoken<br />+ @sqlAlcohol<br />+ @sqlGeloof<br />+ @sqlRelatie<br />+ @sqlBurgerlijke_staat<br />+ @sqlSterrenbeeld<br />+ @sqlKinderwens<br /><br /><br />/* Determine records to select based upon pageno<br /> when pageno &gt; 1 then 10 records starting with pageno*10 from the complete resultset<br /> should be returned */<br /><br />if @pageno &gt; 1<br /> BEGIN<br /> SET @rowCount = @rows * @pageno - @rows <br /> SET ROWCOUNT @rowCount <br /> SET @cursor = '<br /> DECLARE C1 CURSOR<br /> GLOBAL<br /> STATIC <br /> FOR SELECT LaatstGewijzigd <br /> FROM dbo.viewSearch ' + @whereSql + @sqlFoto +<br /> ' AND laatstgewijzigd &gt; ' + Convert(nvarchar(14),@laatstGewijzigd) + <br /> ' ORDER BY LaatstGewijzigd ASC'<br /> <br /> -- create cursor<br /> exec (@cursor)<br /><br /> -- open cursor <br /> OPEN C1<br /> <br /> -- retrieve the last row from the cursor and put it in a variable<br /> FETCH LAST FROM C1 into @laatstGewijzigd<br /><br /> -- close cursor<br /> CLOSE C1<br /> DEALLOCATE C1<br /> END<br /><br />-- return the total number of hits<br />IF (@foto = '0' or @foto = '')<br />exec (' select count(*) as Aantal from dbo.members <br /> ' + @whereSql + 'AND displayen = 1 AND DeleteFlag = 0'<br /> )<br />ELSE IF (@foto = 1 )<br />exec (' select count(*) as Aantal from dbo.members ' + @whereSql +' AND Foto_Aanwezig=1 AND displayen = 1 AND DeleteFlag = 0')<br />ELSE IF (@foto = 2) <br />exec (' select count(*) as Aantal from dbo.members ' + @whereSql +' AND Foto_Aanwezig=0 AND displayen = 1 AND DeleteFlag = 0')<br /><br /><br /><br />-- return the data<br />SET @whereSql = @whereSql + @sqlFoto + ' and laatstgewijzigd &gt; ' + Convert(nvarchar(14),@laatstGewijzigd) + ' ORDER BY LaatstGewijzigd'<br /><br />SET ROWCOUNT @rows<br /><br />IF @view = 1 <br />BEGIN<br />exec (' select OproepNummer, Code, Voornaam, GeboorteDatum, Geslacht_ID, Ik_zoek_id, Gewenste_relatie_id, Provincie_id, StatusFull, LaatstIngelogd, StatusOnline, Sterrenbeeld_id, Oproep, imgHeight, imgWidth from dbo.viewSearch<br /> ' + @whereSql )<br />END<br />ELSE<br />BEGIN<br />exec (' select OproepNummer, Code, Voornaam, GeboorteDatum, Geslacht_ID, Ik_zoek_id, Gewenste_relatie_id, Provincie_id, StatusOnline, Sterrenbeeld_id, StatusFull, ThumbHeight, ThumbWidth, imgHeight, imgWidth from dbo.viewSearch<br /> ' + @whereSql )<br />END<br /> <br /><br /><br />GO

the script show where your resources are going,<br />you already know where your resources are going, but here it is anyways<br /><br />1) simplest method<br />run profiler (save to file) with EventClass: SP:Completed <br />Data Columns: EventClass, TextData, Duration, SPID, StartTime, DatabaseID, ObjectID,<br /> NestLevel<br /><br />after run, save to a table on some other server<br /><br />SELECT DatabaseID, ObjectID, dwt = SUM(Duration), duration = AVG(Duration), MIN(RowNumber)<br />FROM table WHERE eventclass =43 AND NestLevel = 1<br />GROUP BY dwt DESC<br /><br />the above method does not have CPU info, which may be important in your case because of your high cpu, resulting in large diff between duration and cpu<br /><br />2) more complex method<br />run profiler (save to file)<br />Events: Stored Procedures -&gt; RPC:Completed (EventClass 10)<br /> TSQL -&gt; SQL:BatchCompleted (EventClass 12)<br /><br />Data Columns: EventClass, TextData, CPU, Reads, Writes, Duration, SPID, StartTime,<br /> DatabaseID<br /><br />after run, save to table on other than prod server<br />if everything goes through RPC, you can drop SQL:BatchCompleted, and drop the parse for 'execute '<br /><br />SELECT dbid, EventClass, RowNumber, wt, dwt, cnt, CPU = wt/cnt,<br /> Duration = dwt/cnt, MaxCPU, Reads, TextData<br />FROM (<br />SELECT dbid, EventClass, RowNumber=MIN(RowNumber),<br /> wt=SUM(CPU), dwt=SUM(Duration), cnt=count(*), <br /> dev = AVG(CONVERT(decimal(18,5),CPU)*CPU),<br /> MaxCPU=MAX(CPU), Reads=AVG(Reads), Duration=0.0, TextData<br />FROM (<br />SELECT dbid, EventClass, RowNumber, CPU, Reads, Duration,<br /> CASE <br /> WHEN (PA&gt;5) THEN -- exec sp<br />CASE WHEN CHARINDEX(' ',TextData,PA)&gt;0 -- with parameters<br /> THEN SUBSTRING(TextData,PA,CHARINDEX(' ',TextData,PA)-PA)<br /> ELSE SUBSTRING(TextData,PA,L-PA) END -- without parameters<br /> WHEN (PB&gt;<img src='/community/emoticons/emotion-11.gif' alt='8)' /> THEN -- execute sp <br />CASE WHEN CHARINDEX(' ',TextData,PB)&gt;0 --with parameters<br /> THEN SUBSTRING(TextData,PB,CHARINDEX(' ',TextData,PB)-PB)<br /> ELSE SUBSTRING(TextData,PB,L-PB) END -- without parameters<br /> END AS TextData<br /> FROM ( SELECT dbid, EventClass, RowNumber, CPU, Reads, Duration,<br /> CHARINDEX('exec ',TextData,1)+5 AS PA, CHARINDEX('execute ',TextData,1)+8 AS PB,<br /> LEN(TextData) AS L, TextData <br /> FROM ( SELECT dbid = DatabaseID, EventClass, RowNumber, CPU, Reads, Duration,<br /> LTRIM(CONVERT(VARCHAR(7000),TextData)) AS TextData <br /> FROM VTrace WHERE EventClass IN (10,12) ) t ) s <br /> WHERE (PA&gt;5 OR PB&gt;<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />) r<br />GROUP BY dbid, EventClass, TextData<br />) p<br />ORDER BY wt DESC<br />

on your stored proc.
can you run it on an alternate system not being use for anything else?
if so, run profiler, capture
SP:Recompile

i am curious how recompiles there are.
try to figure which statements cause a recompile, and put that statement in a nested stored procedure

also, overall, i would consider building the exec string on the app server, then sending the exec to a stored proc, if single step, you don't need the stored proc, but in your case, multi-statement, stored is easier

joechang,

I run the SP on an alternate system where nothing is running. When I execute the SP (tried with several input parameters), no SP:Recompile is shown in Profiler. If have also captured this in the production environment and there where also no SP:Recompile statements in Profiler.

I will try doing a test with building the exec statement in the app sever. I did already a test passing the @whereSql as a string to the SP and not build it there. Did hardly made any difference.

i think means that the dynamic sql requires a compile instead of a recompile<br />if building the @whereSql outside did not make a difference,<br />then i would look for where the cpu is going,<br /><br />run the sp preferably with the parameters that with the longer durations<br /><br />1) with profiler capturing sp<img src='/community/emoticons/emotion-7.gif' alt=':s' />tmtcompleted<br />this will show which statements require the most time<br /><br />2) from query analyzer, with SET STATISTICS PROFILE ON<br />this to see the execution plan, <br />match the profiler captured statement for CPU &gt; 31 with the execution plan<br /><br />also, how many tables are in the view?<br />are you on enterprise edition? if so, consider an indexed view<br /><br />i think you can get some benefit as follows:<br />pick a parameter set that could generate more than 3-4 pages,<br />measure the cost for page 1, then the cost for returning page 1 + 2 (not just page 2)<br /><br />i think you will find that the cost structure is a + b*(pages)<br /><br />so i think you might be better off always returning 3-4 pages, so if the user decides to look at page 2, 3 or 4, no call to the db is required<br />

Joechang,<br /><br />Thanks for the advice. I run the sp<img src='/community/emoticons/emotion-7.gif' alt=':s' />tmtcompleted in profiler and I noticed directly that the count(*) takes the most time (&gt; 140ms). However the count is on a single table and the columns in the where clause are within an index or only contains a 0 or 1.<br />I tried several things to make the count faster but as soon as a column which contains only a 0 or 1 in the DB (tiny int)is in the where clause the duration is over 100 (cpu almost equal to duration). Any ideas?

what does the execution plans for each variation show?
does one plan only index seek and joins,
while the bad ones show either a scan or index seek followed by bookmark lookups
there is an enormous performance difference between a covered index
i would recommend an index on: Foto_Aanwezig,displayen,DeleteFlag
and change the first count(*) to include a specification on foto_xx

Something I notice that would probably not make much of a difference but I thought is worth mentioning, is that you don't really need the cursor to get the laatstGewijzigd.


if @pageno > 1
BEGIN
SET @rowCount = @rows * @pageno - @rows
sp_executesql N'SELECT TOP 1 @laatstGewijzigd = x.LaatstGewijzigd
FROM
(
SELECT TOP ' + convert(varchar, @rowCount) + ' LaatstGewijzigd
dbo.viewSearch ' + @whereSql + @sqlFoto +
' AND laatstgewijzigd > ' + Convert(nvarchar(14),@laatstGewijzigd) +
' ORDER BY LaatstGewijzigd ASC
) x ORDER BY x.LaatstGewijzigd DESC '
END
Bambola.

if that works, then also see if a DESC index on tableX.LaatstGewijzigd helps

because the actual query involves any combination of SARG, there is no simple solution,
the stored proc in this case has no advantage over a direct sql statement in that the statement must be parsed in any case,
In situations where dynamic sql can be avoided within a stored proc, it is generally worth avoiding it.
in some situations, where there are a number of SARG options, but a few specific combinations occurs with sufficient regularity, i will write hard coded sql for those combinations, and use dynamic sql for others. then i avoid dynamic sql in a reasonable percentage of calls, just not always

joechang,

Following up on your question about the execution plan.

The count(*) that is fast:
select count(*) from
members
where geboortedatum > 19710101
and geboortedatum < 19800101
uses an index seek,Stream Aggregate,Compute Scalar

The count(*) that is slow e.g.:
select count(*) from
members
where geboortedatum > 19710101
and geboortedatum < 19800101
and ik_zoek_id = 1
uses an Clustered Index Scan, Stream Aggregate,Parallelism, Stream Aggregate, Compute Scalar

ik_zoek_id is a tinyint field which can only contain a 0 or 1 like the other possible fields in the where clause. It is not indexed but while it can only hold a 0 or 1 it seems to me it should not be indexed to reduce overhead during update or insert. Or am I wrong here?

anytime an index saves a scan in a select query (that is actually called), the benefit outweighs the insert update delete overhead,
the insert overhead applies to the index, so having an extra tinyint column should not incur overhead,
if the column is updated frequently, then that would add overhead,
but first, i would get rid of the table/clustered index scan

Try to add ik_zoek_id to the index on geboortedatum to create a covered index ( geboortedatum, ik_zoek_id ). This should resolve the performance problem of this query. If there are other cases where ik_zoek_id can be combined with other SARGS then again, add it to the end of the index. Basically the proc is generating a large number of different SQL statements, for optimal performance they should have a covered index if they can, or an index with a very slective leading column. In terms of overhead, if you are saying that this proc is heavily used and may account for a large part of the load on the server, then indexing the tables involved is likely to be a small price compared with going down a federated route <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />Cheers<br />Twan

Thanks Guys,

I have messed around with the indexes and got an performance improvement of about 80%. I always thought that an index on a column storing a boolean (0 or 1) had hardly any impact. What was I wrong!

Marnix