Profiler

Hey,
I want to run the profiler in certain times during the
day. Instead of me opening and closing sessions of
profiler, i extracted the script and run it through query
analyser (then schedula it as a job and run at the times i
want). I am testing this now and there seems to be a
problem. I run the script i got and when i execute it in
query anaylser the profiler file is created in the
location i want but no data is being written to it. I
cannot delete it because "the file is being used"... does
anyone know why this happenes? any work arrounds?
Help please!!Hi Claudia
This is normal.
Data is only written to the trace file in 128K chunks. So as soon as there
is 128K worth of events, you'll see some size for the file. If you stop and
close the trace, it will also write all the remaining data to the trace
file, or if the server is stopped.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:03dd01c39954$e85764d0$a001280a@.phx.gbl...
> Hey,
> I want to run the profiler in certain times during the
> day. Instead of me opening and closing sessions of
> profiler, i extracted the script and run it through query
> analyser (then schedula it as a job and run at the times i
> want). I am testing this now and there seems to be a
> problem. I run the script i got and when i execute it in
> query anaylser the profiler file is created in the
> location i want but no data is being written to it. I
> cannot delete it because "the file is being used"... does
> anyone know why this happenes? any work arrounds?
> Help please!!sql

Production release methodology

Hi,

Our DBA group is debating production release methodology. We always
perfect our deployment package (typically a script) against the
database in a Staging environment, before executing the package
against Production. One side argues that the safest approach is to
create a script containing all schema changes, data changes, stored
procs, functions, etc. Run that script against Staging until it is
error-free. Then, when you run it against Production on Release Night
you know it will also be error-free, since Staging is a copy of
Production. However, any changes to the deployment, such as updates
to procs (and there are always a bunch in the pre-deployment period)
must be manually implemented on the script.

The other side wants to take advantage of the .NET environment. We
keep all procs, views, and functions in a Release folder on VSS,
execute a Get Latest Version (Recursive) from VisualStudio, and run
against Staging/Production. Any changes to the procs are
automatically propagated from VSS to VS, and there is no manual
editing of the script. You still need a script for table and data
changes. The "script everything" side of the debate feels this method
is not as reliable, especially since when views are nested, you will
get compile errors and must execute the run-on several times until all
the views compile. The "script" side feels that any errors are
unacceptable in a Production environment.

What is the opinion out there?

Thanks!"Theodore Feldman" <theodore.feldman@.fmglobal.com> wrote in message
news:26032dec.0401090715.1721f762@.posting.google.c om...
> Hi,
> Our DBA group is debating production release methodology. We always
> perfect our deployment package (typically a script) against the
> database in a Staging environment, before executing the package
> against Production. One side argues that the safest approach is to
> create a script containing all schema changes, data changes, stored
> procs, functions, etc. Run that script against Staging until it is
> error-free. Then, when you run it against Production on Release Night
> you know it will also be error-free, since Staging is a copy of
> Production. However, any changes to the deployment, such as updates
> to procs (and there are always a bunch in the pre-deployment period)
> must be manually implemented on the script.
> The other side wants to take advantage of the .NET environment. We
> keep all procs, views, and functions in a Release folder on VSS,
> execute a Get Latest Version (Recursive) from VisualStudio, and run
> against Staging/Production. Any changes to the procs are
> automatically propagated from VSS to VS, and there is no manual
> editing of the script. You still need a script for table and data
> changes. The "script everything" side of the debate feels this method
> is not as reliable, especially since when views are nested, you will
> get compile errors and must execute the run-on several times until all
> the views compile. The "script" side feels that any errors are
> unacceptable in a Production environment.
> What is the opinion out there?
> Thanks!

One other possible approach is to prepare your Staging database with the
latest tables and other objects, taken from VSS, then use a DB comparison
tool to generate a sync script for deployment to production. This can save a
lot of time in deployment, and also makes it much harder to 'forget' any
minor or last minute changes.You would still need a script for data
modifications, of course. I use the Red Gate tools in more or less that way,
and they work well:

http://www.red-gate.com/sql/summary.htm

But there are plenty of other ways to deploy code, so it's probably going to
come down to whatever works best for your team, given the tools and
knowledge you have available.

Simon|||"Theodore Feldman" <theodore.feldman@.fmglobal.com> wrote in message
news:26032dec.0401090715.1721f762@.posting.google.c om...
> Hi,
> Our DBA group is debating production release methodology. We always
> perfect our deployment package (typically a script) against the
> database in a Staging environment, before executing the package
> against Production. One side argues that the safest approach is to
> create a script containing all schema changes, data changes, stored
> procs, functions, etc. Run that script against Staging until it is
> error-free. Then, when you run it against Production on Release Night
> you know it will also be error-free, since Staging is a copy of
> Production. However, any changes to the deployment, such as updates
> to procs (and there are always a bunch in the pre-deployment period)
> must be manually implemented on the script.
> The other side wants to take advantage of the .NET environment. We
> keep all procs, views, and functions in a Release folder on VSS,
> execute a Get Latest Version (Recursive) from VisualStudio, and run
> against Staging/Production. Any changes to the procs are
> automatically propagated from VSS to VS, and there is no manual
> editing of the script. You still need a script for table and data
> changes. The "script everything" side of the debate feels this method
> is not as reliable, especially since when views are nested, you will
> get compile errors and must execute the run-on several times until all
> the views compile. The "script" side feels that any errors are
> unacceptable in a Production environment.
> What is the opinion out there?

The database is still central and will always be so.
The application environment changes every season.
It might have been VB last year but this year its .net.
This is history.

It is for ever a compromise under current methodologies,
but my opinion is that applications should be written with
more and more of the code outside the application and in
the database environment.

In the end, there will only be the database. The application
environment external to the rdbms is a redundancy. I would
not be fuelling a redundancy unless you dont have any other
options.

Pete Brown
Falls Creek
Oz|||We keep all SQL scripts under source control and build release packages
based on only labeled versions. This provides more control over what gets
promoted. We use a custom .Net program to run the scripts, control the
execution sequence and provide enhanced logging. One can achieve similar
results with OSQL.

I suggest you treat database implementation script development like you do
code - as an iterative build process. If you find errors, correct the
source controlled scripts, create another release package build and try
again against a fresh staging database until you get it right. This
discipline will ensure that the same stuff that was blessed in your QA/UAT
environment is the same that gets promoted.

Also, check out http://www.abaris.se/abaperls/index.html for a public domain
configuration management toolset. Even if you choose not to use it, it may
give you some ideas for automating the CM process.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Theodore Feldman" <theodore.feldman@.fmglobal.com> wrote in message
news:26032dec.0401090715.1721f762@.posting.google.c om...
> Hi,
> Our DBA group is debating production release methodology. We always
> perfect our deployment package (typically a script) against the
> database in a Staging environment, before executing the package
> against Production. One side argues that the safest approach is to
> create a script containing all schema changes, data changes, stored
> procs, functions, etc. Run that script against Staging until it is
> error-free. Then, when you run it against Production on Release Night
> you know it will also be error-free, since Staging is a copy of
> Production. However, any changes to the deployment, such as updates
> to procs (and there are always a bunch in the pre-deployment period)
> must be manually implemented on the script.
> The other side wants to take advantage of the .NET environment. We
> keep all procs, views, and functions in a Release folder on VSS,
> execute a Get Latest Version (Recursive) from VisualStudio, and run
> against Staging/Production. Any changes to the procs are
> automatically propagated from VSS to VS, and there is no manual
> editing of the script. You still need a script for table and data
> changes. The "script everything" side of the debate feels this method
> is not as reliable, especially since when views are nested, you will
> get compile errors and must execute the run-on several times until all
> the views compile. The "script" side feels that any errors are
> unacceptable in a Production environment.
> What is the opinion out there?
> Thanks!|||theodore.feldman@.fmglobal.com (Theodore Feldman) wrote in message news:<26032dec.0401090715.1721f762@.posting.google.com>...
> Hi,
> Our DBA group is debating production release methodology. We always
> perfect our deployment package (typically a script) against the
> database in a Staging environment, before executing the package
> against Production. One side argues that the safest approach is to
> create a script containing all schema changes, data changes, stored
> procs, functions, etc. Run that script against Staging until it is
> error-free. Then, when you run it against Production on Release Night
> you know it will also be error-free, since Staging is a copy of
> Production. However, any changes to the deployment, such as updates
> to procs (and there are always a bunch in the pre-deployment period)
> must be manually implemented on the script.
> The other side wants to take advantage of the .NET environment. We
> keep all procs, views, and functions in a Release folder on VSS,
> execute a Get Latest Version (Recursive) from VisualStudio, and run
> against Staging/Production. Any changes to the procs are
> automatically propagated from VSS to VS, and there is no manual
> editing of the script. You still need a script for table and data
> changes. The "script everything" side of the debate feels this method
> is not as reliable, especially since when views are nested, you will
> get compile errors and must execute the run-on several times until all
> the views compile. The "script" side feels that any errors are
> unacceptable in a Production environment.
> What is the opinion out there?
> Thanks!

We use www.red-gate.com to generate a script that would update
production to the current version of the staging database. This only
applies to the schema (and some static data tables).

The rest, ie procs, views, functions, etc comes direct from
SourceSafe. If there are dependencies, we either use alpabetical
ordering (default) or a custom script that applies inner
functions/views etc first.

We have also setup an nightly batch process that does a trial upgrade
from staging to production every night to pick up errors as soon as
possible.

To me it sounds like your DBAs are trying to keep themselves in a job.
Manually creating these scripts can be a time consuming, laborious and
uninteresting process. They used to use this process at a previous
role and we changed|||[posted and mailed, please reply in news]

Theodore Feldman (theodore.feldman@.fmglobal.com) writes:
> Our DBA group is debating production release methodology. We always
> perfect our deployment package (typically a script) against the
> database in a Staging environment, before executing the package
> against Production. One side argues that the safest approach is to
> create a script containing all schema changes, data changes, stored
> procs, functions, etc. Run that script against Staging until it is
> error-free. Then, when you run it against Production on Release Night
> you know it will also be error-free, since Staging is a copy of
> Production. However, any changes to the deployment, such as updates
> to procs (and there are always a bunch in the pre-deployment period)
> must be manually implemented on the script.
> The other side wants to take advantage of the .NET environment. We
> keep all procs, views, and functions in a Release folder on VSS,
> execute a Get Latest Version (Recursive) from VisualStudio, and run
> against Staging/Production. Any changes to the procs are
> automatically propagated from VSS to VS, and there is no manual
> editing of the script. You still need a script for table and data
> changes. The "script everything" side of the debate feels this method
> is not as reliable, especially since when views are nested, you will
> get compile errors and must execute the run-on several times until all
> the views compile. The "script" side feels that any errors are
> unacceptable in a Production environment.

I just finished another posting on CM issues, see the topic "Script
Question", which gives an outline of how we work in our shop. Here
I will just supplement with your specific issues.

Basically, we go the script way, but we have a tool which is smart enough
to permit regeneration of the script as well as manual tweaks. This gives
the best of both worlds.

A completely manually maintained script is a fragile solution, which is
open to casual mistakes, and risky.

If I did not have a tool at hand, I would probably start from your
..Net alternative, and then find solutions to problems like nested views.

I recall that there is a stored procedure builder that comes with the
SQL Server 2000 Resource Kit. I have never came around to evaluate it,
but I know it works from SourceSafe.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Production error: "The schema script [...] could not be propagated to the subscriber"

Hi,
A member of my team recently made a few minor changes to our database.
Several (not all) subscribers are now failing to replicate with the
following error:
The schema script 'exec sp_repldropcolumn
'[dbo].[IP_Application_Updates]', 'update_id', 1' could not be
propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
------
A column was added to or dropped from the replicated table.
(Source: l07052 (Agent); Error number: 27160)
------
' because it is a primary key column.
(Source: l07052 (Data source); Error number: 21264)
------
This was a problem a few months ago but seemed to have been resolved.
In particular, nothing at all has changed recently in the
IP_Application_Updates table. I'm very happy for that table to remain
exactly as it is.
Can anyone suggest a way to fix this? I've got a bunch of users who
are unable to replicate their database and they'll turn nasty shortly.
In the absence of an elegant fix I'll cheerfully go with a way to
change the offending script to something innocuous, but I don't know
where or why the script exists or why the subscribers are trying to run
it. I will be very grateful if anyone can help me to get my
subscribers past this error.
Many thanks
Alex
Whenever the Merge Agent runs, it first makes sure that the schema [any
change as a result of DDL] is in sync, before the
data could be replicated. All the schema changes in the publisher are stored
in sysmergeschemachange table on the
publisher database. You can take a look at this table and see the schema
changes that have been executed at the publisher.
I believe the Merge Agent is trying to drop the column at the subscriber,
but unable to do it because it is the primary key column.
One work around would be to remove the primary key constraint at the
subscriber, if you don't need that column any more.
<WombatDeath@.gmail.com> wrote in message
news:1169249897.423772.218970@.l53g2000cwa.googlegr oups.com...
> Hi,
> A member of my team recently made a few minor changes to our database.
> Several (not all) subscribers are now failing to replicate with the
> following error:
> The schema script 'exec sp_repldropcolumn
> '[dbo].[IP_Application_Updates]', 'update_id', 1' could not be
> propagated to the subscriber.
> (Source: Merge Replication Provider (Agent); Error number: -2147201001)
> ------
> A column was added to or dropped from the replicated table.
> (Source: l07052 (Agent); Error number: 27160)
> ------
> ' because it is a primary key column.
> (Source: l07052 (Data source); Error number: 21264)
> ------
> This was a problem a few months ago but seemed to have been resolved.
> In particular, nothing at all has changed recently in the
> IP_Application_Updates table. I'm very happy for that table to remain
> exactly as it is.
> Can anyone suggest a way to fix this? I've got a bunch of users who
> are unable to replicate their database and they'll turn nasty shortly.
> In the absence of an elegant fix I'll cheerfully go with a way to
> change the offending script to something innocuous, but I don't know
> where or why the script exists or why the subscribers are trying to run
> it. I will be very grateful if anyone can help me to get my
> subscribers past this error.
> Many thanks
> Alex
>
|||Hi Gayathri
Thank you for replying so quickly. The problem I have is that the
table in question is in use, and the column in question should be the
PK of that table.
We have a scheduled job which recreates the snapshot once a week, on
Saturday. I've just taken a look and most tables have three records in
sysmergeschemachange, but the IP_Application_Updates table has an
additional four:
exec sp_repldropcolumn '[dbo].[IP_Application_Updates]', 'update_id', 1
alter table [dbo].[IP_Application_Updates] drop column update_id
exec sp_repladdcolumn '[dbo].[IP_Application_Updates]','update_id',[int
IDENTITY (1, 1) NOT NULL], '%', 1
alter table [dbo].[IP_Application_Updates] add update_id int IDENTITY
(1, 1) NOT NULL
This is presumably what's causing the problem. I could perhaps "fix"
it by just deleting those four records from the system table but I can
just imagine causing all manner of havoc with my ignorant tinkering.
Assuming that nobody in my team has had any reason whatsoever to make
any alterations to this table in recent months, is it possible to
hazard a guess as to why the merge agent is attempting to recreate the
update_id column? And is there any way to make it stop?
Thanks
Alex
|||Hi,
The only reason I see a DDL row in 'sysmergeschemachange' table is because
of a user action after
the intial snapshot has been taken.
Trying to delete the rows from the system table could pop up some more
issues.
You could do two things:
1. drop the primary key constraint at the failing subscriber. This should
probably allow
the merge agent to complete succesfully [if this is the only problem].
2. Else, you might have to re-initalize the failing subscribers ...
Hope this helps.
-- Gayathri TK
<WombatDeath@.gmail.com> wrote in message
news:1169255444.925914.213970@.11g2000cwr.googlegro ups.com...
> Hi Gayathri
> Thank you for replying so quickly. The problem I have is that the
> table in question is in use, and the column in question should be the
> PK of that table.
> We have a scheduled job which recreates the snapshot once a week, on
> Saturday. I've just taken a look and most tables have three records in
> sysmergeschemachange, but the IP_Application_Updates table has an
> additional four:
> exec sp_repldropcolumn '[dbo].[IP_Application_Updates]', 'update_id', 1
> alter table [dbo].[IP_Application_Updates] drop column update_id
> exec sp_repladdcolumn '[dbo].[IP_Application_Updates]','update_id',[int
> IDENTITY (1, 1) NOT NULL], '%', 1
> alter table [dbo].[IP_Application_Updates] add update_id int IDENTITY
> (1, 1) NOT NULL
> This is presumably what's causing the problem. I could perhaps "fix"
> it by just deleting those four records from the system table but I can
> just imagine causing all manner of havoc with my ignorant tinkering.
> Assuming that nobody in my team has had any reason whatsoever to make
> any alterations to this table in recent months, is it possible to
> hazard a guess as to why the merge agent is attempting to recreate the
> update_id column? And is there any way to make it stop?
> Thanks
> Alex
>
|||Hi Gayathri
Thanks very much; we'll try dropping the PK constraint and see what
happens.
Thanks again for your help,
Alex

Producing week ending dates for each week of the year

Hi

I am trying to produce a script that will show date for the last day of the week(Sunday). The script will have to show dates for all weeks of the year. Does anyone know how this can be done?

Thanks

Quote:

Originally Posted by rcr69er

Hi

I am trying to produce a script that will show date for the last day of the week(Sunday). The script will have to show dates for all weeks of the year. Does anyone know how this can be done?

Thanks

hai try this-----------
declare
v_date date;
begin
select next_day('01-JAN-07','sunday') into v_date from dual;
for i in 0..51 loop
dbms_output.put_line(to_char(to_date((v_date)+7*i) ));
end loop;
end;sql

ProcessRowInput not containing any Row data

I have a script component as a destination. I believe I'm doing something very simple, but for some reason, I'm not getting back any data.

When I just msgbox the data in Row.Address, I get nothing. If I msgbox Row.Address.toString, I get "Object reference not set to instance of an object."

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
MessageBox.Show(Row.Address)
End Sub

I have Address as an input and output column in my script component setup. Any help? It seems like it's gotta be something simple, but I'm pulling my (little bit of) hair out to find it.

mmmm... think my problem is elsewhere. thanks anyways!

Processing one 'fact partition or measure group' weekly, while others are daily?

Is this possible to have done in 2 different (or 1 intelligent) AS deployment script(s)? Sunday would be the full processing day, while other days would just handle the smaller fact areas.

You could use Integration Services with XMLA scripts to accomplish this...

Frank

|||

Is this type of thing something that's available in standard edition? I tried making a new connection with type of Integration Services, but I'm not sure where to go from there, as the object exlorer appears to give no options to make a new package or whatever route I'd need to take to make a new schedule.

Could you provide more info as to how it would be created and then scheduled (or is this a manual thing if not an option provided by SSIS)?

|||

Yes, this is available in the standard edition.

how to create an SSIS package: http://msdn2.microsoft.com/en-us/library/ms141178.aspx

This thread explains a couple of different ways to do this: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1626707&SiteID=1

Frank|||

I will take a look over the article. Thanks.

I also want to ask again, is it possible to process all fact tables but one (not by specifying the name of the facts, but only the name of the one I don't want to process)?

Procedure vs Script

I have a complex procedure (300+ lines, but it used to be over 2000). The procedure uses one table variable and three temporary tables with indexes. The temporary tables are joined for the final output.

The problem is that the process takes 24 seconds if I execute the statements from query analyzer, but executing a procedure with same logic causes the process to take nearly six times as long.

Any ideas on what might cause a process to run six times longer as a procedure than as a script? The execution plans appear to be the same, and the same login is being used for both.

blindmanBlindman, just a few questions

Are there parameters passed to the procedure/script?

Just to be absolutely clear, if you cut and paste the meat of the procedure into Query Analyzer the meat will run faster than the procedure itself? I just want to make sure that the same client (and all network hops) are similar for both tests there.

About how much data is being joined in the three tables and returned in the resultset?|||Yes, the procedure takes about a dozen optional parameters. I am supplying three in my test.

Yes, the meat is the same (it tastes like chicken). I just remove the CREATE PROCEDURE header and replace it with explicit variable declarations.

For my test parameters I am only returning five lines of data, though this is culled, calculated, and aggregated from many much larger tables.

The temporarty tables are being loaded with about 30,000 rows.

blindman|||Did you run sp_recompile on the stored procedure ?|||Have you tried using table data type instead ?|||Tried recompiling. No effect.

Bounced the server. This made both executions run slow (so cache may have something to do with it).

Can't use table datatypes for the remaining three temporary data sets because I need to have an indexes on them or the procedure takes forever.

blindman|||I have been experiencing the same myself .. though the difference between the script and proc execution time is not more than 5 secs ...
might have something to do with the executions plans and way they are retrieved for sp and statements|||How about this: Using the meat (lightly basted, of course), put in different values for the parameters, and see if different query plans come out the other side.

I had one case where SQL Server actually chose a poor execution plan for a stored procedure. We had to make sure that the first set of parameters queried by it was the single set of parameters that gave the best overall plan. Could you be seeing a similar effect?|||I tried putting WITH RECOMPILE at the start of the procedure. Wouldn't this accomplish the same thing? It did not affect the execution time.

blindman|||That would regenerate the query plan for each execution (effectively that should have reduced it to just the meat). In my problem, I had 3 different query plans of which one was outlandishly inappropriate even for the parameters it was generated by, one middling plan, and one that was acceptable for all combinations. This only works if the query plan coming out the otherside of the optimizer is significantly different for each set of parameters.

Is it possible that the index statistics on the temp tables are not considered, since they are not available at runtime? As an experiment, what happens if you add permanent tables in place of the temp tables?|||I have to have temporary tables so that it can be multi-user.

Plus, it appeared to be running fine last week.

blindman|||If it was running fine last week, do you happen to know if any significant change has happened to the data? Purge, update or load? You cna check on the last time statistics were updated for each index by running the stats_date function. I think SQL Server automatically runs update statistics on a table if it notices a 10% difference in the number of rows, but maybe not necessarily on a mass update of values in the table you may want to join on.|||Our admin tells me we are running low on drive space. Perhaps that is affecting tempdb or drive cacheing.

blindman|||Low disk space in and of itself should not matter, but it could be a second symptom of a more general problem. It could be you are getting enough data in the database(s) to overwhelm the caching algorithms, and are now seeing more paging of the data. Kindly ask the users to stop putting so many orders into the database, as it could be slowing the system down ;-).

On a more serious note, see if you can get the admin to run a few dbcc showcontig statements on the tables and indexes you are using. You may be able to rebuild the indexes, and get more rows per read of the disk.|||This is a development database on a development server, so no new data is going into it and the table sizes are not changing.

The DBCC Showcontig is a possiblity I'll look into.

In the meantime, I'm goint to try running it on a server with more drive space just to see if it makes a difference.

blindman|||You never told us what show plan is telling you...

Is it one big transaction?

Did you do a trace?

Why not put in

SELECT Datetime() AS systime, 'Process x Starting' As Sysmessage

to find out where the bottleneck is...|||In the meantime, I'm goint to try running it on a server with more drive space just to see if it makes a difference.
That would make sense, since tempdb relies on the speed/size of the drive - as you run out of disk space, I have seen peformance impacts since it takes longer to find free clusters/sectors as tempdb grows ... You might try to alter the tempdb database to increase its initial size. You might have a few bad clusters as well - but I would expect more drastic results if that were the case.|||Update:

One (large) statement in the script uses parallelism when executed as a script, but does not use parallelism when executed as a procedure. This appears to be dragging down the procedure performance.

Anybody seen this before or have any idea what might cause the optimizer to choose a plan without parallelism for a procedure?

blindman|||Can you attach the sproc so we can take a look?

Why would the optimizer not thread out in a sproc?

Are you running on 2 different boxes?|||I think the main requirement to use parallelism is sheer volume of data. We ran into a bug where parallelism on a 3 proc box would hit a self-deadlock. The porblem was "fixed" in SP3a. Now SQL detects the deadlock, and bounces the query with an error. Microsoft's fix was to guess at indexes, until one eliminated parellelism.

What I can not figure out, though, is how they can have two different query plans, when you run the stored proc with recompile.|||You can try to add WITH (MAXDOP <number of CPUs available to your SQL box>).

But I think it may have something to do with recompiles. Try to run Profiler and add SP:Recompile event class.|||That would make the query use fewer processors. What Blindman needs is a MINDOP query hint.