question on recompiles

On Monday I added a whole bunch of indexes. They were pretty much just
indexes on foreign keys. Should I have recomiled all the sp's? Since I
didn't, should I bother now? According to BOL:
But if a new index is added from which the stored procedure might benefit,
optimization does not automatically happen (until the next time the stored
procedure is run after SQL Server is restarted).
Wouldnt they also get recompiled the first time they were run, whether SQL
was restarted or not?
TIA,
ChrisR
ChrisR wrote:
> On Monday I added a whole bunch of indexes. They were pretty much just
> indexes on foreign keys. Should I have recomiled all the sp's? Since I
> didn't, should I bother now? According to BOL:
> But if a new index is added from which the stored procedure might
> benefit, optimization does not automatically happen (until the next
> time the stored procedure is run after SQL Server is restarted).
> Wouldnt they also get recompiled the first time they were run,
> whether SQL was restarted or not?
Yes, the sp will compile the first time it is run. The issue is whether
parameter sniffing is going to bite you because SQL Server decided on an
execution plan for a query before the indexes were applied. It could
continue to use the old plan even with the new index until it is
recompiled. You can flag the stored procedure for recompile using
sp_recompile. You could also use DBCC FREEPROCCACHE, but that will
affect the entire server.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Sorry David but that is not quite how it works in 2000. As soon as the
index is added any plans that reference the associated table is marked for
recompilation. So the very next time anyone tries to run that sp after the
index is created (or dropped) it will create a new plan. That new plan will
take into account the index. Whether it chooses to use it or not is up to
the optimizer but it is considered immediately after being built. So the
only things that will use the old or existing plans are ones that are in the
process of executing at the time the index is finished being created.
Andrew J. Kelly SQL MVP
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ekjWeEGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> ChrisR wrote:
> Yes, the sp will compile the first time it is run. The issue is whether
> parameter sniffing is going to bite you because SQL Server decided on an
> execution plan for a query before the indexes were applied. It could
> continue to use the old plan even with the new index until it is
> recompiled. You can flag the stored procedure for recompile using
> sp_recompile. You could also use DBCC FREEPROCCACHE, but that will affect
> the entire server.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

question on recompiles

On Monday I added a whole bunch of indexes. They were pretty much just
indexes on foreign keys. Should I have recomiled all the sp's? Since I
didn't, should I bother now? According to BOL:
But if a new index is added from which the stored procedure might benefit,
optimization does not automatically happen (until the next time the stored
procedure is run after SQL Server is restarted).
Wouldnt they also get recompiled the first time they were run, whether SQL
was restarted or not?
--
TIA,
ChrisRChrisR wrote:
> On Monday I added a whole bunch of indexes. They were pretty much just
> indexes on foreign keys. Should I have recomiled all the sp's? Since I
> didn't, should I bother now? According to BOL:
> But if a new index is added from which the stored procedure might
> benefit, optimization does not automatically happen (until the next
> time the stored procedure is run after SQL Server is restarted).
> Wouldnt they also get recompiled the first time they were run,
> whether SQL was restarted or not?
Yes, the sp will compile the first time it is run. The issue is whether
parameter sniffing is going to bite you because SQL Server decided on an
execution plan for a query before the indexes were applied. It could
continue to use the old plan even with the new index until it is
recompiled. You can flag the stored procedure for recompile using
sp_recompile. You could also use DBCC FREEPROCCACHE, but that will
affect the entire server.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Sorry David but that is not quite how it works in 2000. As soon as the
index is added any plans that reference the associated table is marked for
recompilation. So the very next time anyone tries to run that sp after the
index is created (or dropped) it will create a new plan. That new plan will
take into account the index. Whether it chooses to use it or not is up to
the optimizer but it is considered immediately after being built. So the
only things that will use the old or existing plans are ones that are in the
process of executing at the time the index is finished being created.
Andrew J. Kelly SQL MVP
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ekjWeEGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> ChrisR wrote:
>> On Monday I added a whole bunch of indexes. They were pretty much just
>> indexes on foreign keys. Should I have recomiled all the sp's? Since I
>> didn't, should I bother now? According to BOL:
>> But if a new index is added from which the stored procedure might
>> benefit, optimization does not automatically happen (until the next
>> time the stored procedure is run after SQL Server is restarted).
>> Wouldnt they also get recompiled the first time they were run,
>> whether SQL was restarted or not?
> Yes, the sp will compile the first time it is run. The issue is whether
> parameter sniffing is going to bite you because SQL Server decided on an
> execution plan for a query before the indexes were applied. It could
> continue to use the old plan even with the new index until it is
> recompiled. You can flag the stored procedure for recompile using
> sp_recompile. You could also use DBCC FREEPROCCACHE, but that will affect
> the entire server.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

question on recompiles

On Monday I added a whole bunch of indexes. They were pretty much just
indexes on foreign keys. Should I have recomiled all the sp's? Since I
didn't, should I bother now? According to BOL:
But if a new index is added from which the stored procedure might benefit,
optimization does not automatically happen (until the next time the stored
procedure is run after SQL Server is restarted).
Wouldnt they also get recompiled the first time they were run, whether SQL
was restarted or not?
--
TIA,
ChrisRChrisR wrote:
> On Monday I added a whole bunch of indexes. They were pretty much just
> indexes on foreign keys. Should I have recomiled all the sp's? Since I
> didn't, should I bother now? According to BOL:
> But if a new index is added from which the stored procedure might
> benefit, optimization does not automatically happen (until the next
> time the stored procedure is run after SQL Server is restarted).
> Wouldnt they also get recompiled the first time they were run,
> whether SQL was restarted or not?
Yes, the sp will compile the first time it is run. The issue is whether
parameter sniffing is going to bite you because SQL Server decided on an
execution plan for a query before the indexes were applied. It could
continue to use the old plan even with the new index until it is
recompiled. You can flag the stored procedure for recompile using
sp_recompile. You could also use DBCC FREEPROCCACHE, but that will
affect the entire server.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Sorry David but that is not quite how it works in 2000. As soon as the
index is added any plans that reference the associated table is marked for
recompilation. So the very next time anyone tries to run that sp after the
index is created (or dropped) it will create a new plan. That new plan will
take into account the index. Whether it chooses to use it or not is up to
the optimizer but it is considered immediately after being built. So the
only things that will use the old or existing plans are ones that are in the
process of executing at the time the index is finished being created.
Andrew J. Kelly SQL MVP
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ekjWeEGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> ChrisR wrote:
> Yes, the sp will compile the first time it is run. The issue is whether
> parameter sniffing is going to bite you because SQL Server decided on an
> execution plan for a query before the indexes were applied. It could
> continue to use the old plan even with the new index until it is
> recompiled. You can flag the stored procedure for recompile using
> sp_recompile. You could also use DBCC FREEPROCCACHE, but that will affect
> the entire server.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

Question on FOREIGN KEY CONSTRAINTS

I've recently implemented a whole bunch of foreign key constraints in a database. As a result I have issues every time I want to truncate a table to perform data loads.

Is there an easy way to tell the system to ignore these constraints for the term of a data load or truncation of data?

thank youwe had an entire thread (http://www.dbforums.com/t1100892.html) on that topic just the other day

check it out and let us know if you still have questions

Question on Changing Jobs in SQL Server 2000

We have a bunch of databases on this SQL Server 2000 and for each
database we pretty much have the same kind of job running daily and
weekly. We are running out of disk space where we used to normally
backup the databases so we now have a new drive which will be used only
for backups so I need to change all my jobs to backup the databases to
the new drive. I can do that manually by going into each job to change
the drive path, but I was wondering if there was a way to update the
system table so that I did not have to manually change all my jobs.
Any help, ideas in this regard will be greatly appreciated.
ThanksHi
This is where having devices would make the task easier! But you can script
the jobs from Enterprise manager and then globally edit the file created.
John
"shub" wrote:

> We have a bunch of databases on this SQL Server 2000 and for each
> database we pretty much have the same kind of job running daily and
> weekly. We are running out of disk space where we used to normally
> backup the databases so we now have a new drive which will be used only
> for backups so I need to change all my jobs to backup the databases to
> the new drive. I can do that manually by going into each job to change
> the drive path, but I was wondering if there was a way to update the
> system table so that I did not have to manually change all my jobs.
> Any help, ideas in this regard will be greatly appreciated.
> Thanks
>|||John
Thanks for your response. Wouldnt the script drop the existing job and
create a brand new one which would cause me to lose all my job history?
Is there a way to get around that?
Thanks|||Hi
Yes it would, the jobs would have different Ids when re-created so you would
have to export everything before doing the scripting. Another reason for
using devices.
John
"shub" wrote:

> John
> Thanks for your response. Wouldnt the script drop the existing job and
> create a brand new one which would cause me to lose all my job history?
> Is there a way to get around that?
> Thanks
>|||John,
If we had devices in place would't we still need to change tha path off
all the devices that we set up for all the different databases? Also
can you set up devices if the physical location of the back up files
are in different computers in the network, basically does it support
UNC? I will look it up myself, but I would sure appreciate your input
as well.
Thanks again|||Hi
If you need to change the job to make it use a device it will not change the
problems of loosing the history, only make it more flexible in the future.
You should only be keeping a certain amount of history as this information
will bloat your msdb, if you need to retain this for a prolonged period then
you may want to put into place a system that will copy it elsewhere.
Devices can use UNC paths, there is an example of sp_adddumpdevice in books
online that shows exactly this. If you are backing up to another machine it
is worth considering a dedicated (1GB) subnet and adding extra cards to
improve performance.
John
"shub" wrote:

> John,
> If we had devices in place would't we still need to change tha path off
> all the devices that we set up for all the different databases? Also
> can you set up devices if the physical location of the back up files
> are in different computers in the network, basically does it support
> UNC? I will look it up myself, but I would sure appreciate your input
> as well.
> Thanks again
>|||I believe when all our jobs were created originally they were all
created by The maintenance plan wizard and I don't see you have an
option of backing up databases to devices.
Maybe in the near future we need to look at redoing all our jobs to use
the devices.
Thank you for all your help.

Question on Changing Jobs in SQL Server 2000

We have a bunch of databases on this SQL Server 2000 and for each
database we pretty much have the same kind of job running daily and
weekly. We are running out of disk space where we used to normally
backup the databases so we now have a new drive which will be used only
for backups so I need to change all my jobs to backup the databases to
the new drive. I can do that manually by going into each job to change
the drive path, but I was wondering if there was a way to update the
system table so that I did not have to manually change all my jobs.
Any help, ideas in this regard will be greatly appreciated.
ThanksHi
This is where having devices would make the task easier! But you can script
the jobs from Enterprise manager and then globally edit the file created.
John
"shub" wrote:
> We have a bunch of databases on this SQL Server 2000 and for each
> database we pretty much have the same kind of job running daily and
> weekly. We are running out of disk space where we used to normally
> backup the databases so we now have a new drive which will be used only
> for backups so I need to change all my jobs to backup the databases to
> the new drive. I can do that manually by going into each job to change
> the drive path, but I was wondering if there was a way to update the
> system table so that I did not have to manually change all my jobs.
> Any help, ideas in this regard will be greatly appreciated.
> Thanks
>|||John
Thanks for your response. Wouldnt the script drop the existing job and
create a brand new one which would cause me to lose all my job history?
Is there a way to get around that?
Thanks|||Hi
Yes it would, the jobs would have different Ids when re-created so you would
have to export everything before doing the scripting. Another reason for
using devices.
John
"shub" wrote:
> John
> Thanks for your response. Wouldnt the script drop the existing job and
> create a brand new one which would cause me to lose all my job history?
> Is there a way to get around that?
> Thanks
>|||John,
If we had devices in place would't we still need to change tha path off
all the devices that we set up for all the different databases? Also
can you set up devices if the physical location of the back up files
are in different computers in the network, basically does it support
UNC? I will look it up myself, but I would sure appreciate your input
as well.
Thanks again|||Hi
If you need to change the job to make it use a device it will not change the
problems of loosing the history, only make it more flexible in the future.
You should only be keeping a certain amount of history as this information
will bloat your msdb, if you need to retain this for a prolonged period then
you may want to put into place a system that will copy it elsewhere.
Devices can use UNC paths, there is an example of sp_adddumpdevice in books
online that shows exactly this. If you are backing up to another machine it
is worth considering a dedicated (1GB) subnet and adding extra cards to
improve performance.
John
"shub" wrote:
> John,
> If we had devices in place would't we still need to change tha path off
> all the devices that we set up for all the different databases? Also
> can you set up devices if the physical location of the back up files
> are in different computers in the network, basically does it support
> UNC? I will look it up myself, but I would sure appreciate your input
> as well.
> Thanks again
>|||I believe when all our jobs were created originally they were all
created by The maintenance plan wizard and I don't see you have an
option of backing up databases to devices.
Maybe in the near future we need to look at redoing all our jobs to use
the devices.
Thank you for all your help.