Database setting

. The BPA recommend that the model database setting for the items below be set to on. I can accomplish this task through the query analyzer and run the set command. (Set ANSI_NULLS on). The response is positive but when I re-run the report the setting are back off.

Why?

QUOTED_IDENTIFIER
ANSI_NULLS
ANSI_WARNINGS
ANSI_PADDING
ANSI_NULL_DFLT_ON
CONCAT_NULL_YIELDS_NULLDon't the setting only last for the scope of the session?

That's why they have to be coded inside the sporcs?

I'll have to look a more defenitive answer...but I'll just be fgetting from BOL|||Garry,

Those settings are only taking effect for that single session i.e. within Query Analyzer.

To make any permanent changes to the model database, you need to right click it in Query Analyzer and check the options under properties. You should save a copy of the database first in case you should find that you need to return to the default settings.

Please note carefully this article in case you need to reattach your model database:

http://support.microsoft.com/?id=224071

We would advise leaving the model database at default settings.

Use the following from Query Analyzer to check the settings:
Sp_helpdb
And also check for databaseproperty in Books Online
Syntax
DATABASEPROPERTY( database , property )

USE master

SELECT DATABASEPROPERTY('model', 'IsANSINullDEFAULT')|||Well its up to you, but most people would leave the model database alone. It depends on your particular needs more than anything else. Remember the model database is only a template used to create new databases, so if you are bringing databases to this machine from another server, then the model database settings will have no effect.

Any conflict here is due to ANSI compatibility levels. SQL Server does not always default to the ANSI compatible levels.

Please review this article for a note on the various database settings and their effects:-

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_03_6ohf.asp

These two commands give you information on your current connection details, and on the database settings that may be configured respectively:-

sp_dboption

dbcc useroptions|||why did you post the question if you already had the answer?|||Perhaps it was a rhetorical question?

Read the top of his posts. He was just copying information from MicroCoughed.sql

Database Optimizations

We have a database that is getting fairly heavy use. Our client complained
that on a certain task it was performing slower than usual. The last time
this happened I manually ran our optimazations job and it fixed the problem.
After that I set up our optimazations job to run every night. This kept
anything from happening for a while but just today we got the same call
again and again it was fixed when I ran the optimazations job. How often
are heavly used databases typically suppose to run the optimizations? Any
help on this topic?
Thanks,
BrandonBrandon Owensby wrote:
> We have a database that is getting fairly heavy use. Our client
> complained that on a certain task it was performing slower than
> usual. The last time this happened I manually ran our optimazations
> job and it fixed the problem. After that I set up our optimazations
> job to run every night. This kept anything from happening for a
> while but just today we got the same call again and again it was
> fixed when I ran the optimazations job. How often are heavly used
> databases typically suppose to run the optimizations? Any help on
> this topic?
> Thanks,
> Brandon
What optimizations? How can you be sure you don't have performance
problems related to query performance? What about disk? CPU? There are a
number of issues that can be the cause. Most times it's the SQL itself.
Properly tuned queries reduce disk and CPU overhead greatly.
--
David Gugick
Imceda Software
www.imceda.com|||Brandon
It is time to start tuning your queries/sp/views. In my experience 80% of
such kind of problems is caused by application (poor written queries).
"Brandon Owensby" <123@.abc.com> wrote in message
news:%23KDUctfvEHA.1392@.TK2MSFTNGP14.phx.gbl...
> We have a database that is getting fairly heavy use. Our client
complained
> that on a certain task it was performing slower than usual. The last time
> this happened I manually ran our optimazations job and it fixed the
problem.
> After that I set up our optimazations job to run every night. This kept
> anything from happening for a while but just today we got the same call
> again and again it was fixed when I ran the optimazations job. How often
> are heavly used databases typically suppose to run the optimizations?
Any
> help on this topic?
> Thanks,
> Brandon
>

Database Optimizations

We have a database that is getting fairly heavy use. Our client complained
that on a certain task it was performing slower than usual. The last time
this happened I manually ran our optimazations job and it fixed the problem.
After that I set up our optimazations job to run every night. This kept
anything from happening for a while but just today we got the same call
again and again it was fixed when I ran the optimazations job. How often
are heavly used databases typically suppose to run the optimizations? Any
help on this topic?
Thanks,
Brandon
Brandon Owensby wrote:
> We have a database that is getting fairly heavy use. Our client
> complained that on a certain task it was performing slower than
> usual. The last time this happened I manually ran our optimazations
> job and it fixed the problem. After that I set up our optimazations
> job to run every night. This kept anything from happening for a
> while but just today we got the same call again and again it was
> fixed when I ran the optimazations job. How often are heavly used
> databases typically suppose to run the optimizations? Any help on
> this topic?
> Thanks,
> Brandon
What optimizations? How can you be sure you don't have performance
problems related to query performance? What about disk? CPU? There are a
number of issues that can be the cause. Most times it's the SQL itself.
Properly tuned queries reduce disk and CPU overhead greatly.
David Gugick
Imceda Software
www.imceda.com
|||Brandon
It is time to start tuning your queries/sp/views. In my experience 80% of
such kind of problems is caused by application (poor written queries).
"Brandon Owensby" <123@.abc.com> wrote in message
news:%23KDUctfvEHA.1392@.TK2MSFTNGP14.phx.gbl...
> We have a database that is getting fairly heavy use. Our client
complained
> that on a certain task it was performing slower than usual. The last time
> this happened I manually ran our optimazations job and it fixed the
problem.
> After that I set up our optimazations job to run every night. This kept
> anything from happening for a while but just today we got the same call
> again and again it was fixed when I ran the optimazations job. How often
> are heavly used databases typically suppose to run the optimizations?
Any
> help on this topic?
> Thanks,
> Brandon
>

Database Optimizations

We have a database that is getting fairly heavy use. Our client complained
that on a certain task it was performing slower than usual. The last time
this happened I manually ran our optimazations job and it fixed the problem.
After that I set up our optimazations job to run every night. This kept
anything from happening for a while but just today we got the same call
again and again it was fixed when I ran the optimazations job. How often
are heavly used databases typically suppose to run the optimizations? Any
help on this topic?
Thanks,
BrandonBrandon Owensby wrote:
> We have a database that is getting fairly heavy use. Our client
> complained that on a certain task it was performing slower than
> usual. The last time this happened I manually ran our optimazations
> job and it fixed the problem. After that I set up our optimazations
> job to run every night. This kept anything from happening for a
> while but just today we got the same call again and again it was
> fixed when I ran the optimazations job. How often are heavly used
> databases typically suppose to run the optimizations? Any help on
> this topic?
> Thanks,
> Brandon
What optimizations? How can you be sure you don't have performance
problems related to query performance? What about disk? CPU? There are a
number of issues that can be the cause. Most times it's the SQL itself.
Properly tuned queries reduce disk and CPU overhead greatly.
David Gugick
Imceda Software
www.imceda.com|||Brandon
It is time to start tuning your queries/sp/views. In my experience 80% of
such kind of problems is caused by application (poor written queries).
"Brandon Owensby" <123@.abc.com> wrote in message
news:%23KDUctfvEHA.1392@.TK2MSFTNGP14.phx.gbl...
> We have a database that is getting fairly heavy use. Our client
complained
> that on a certain task it was performing slower than usual. The last time
> this happened I manually ran our optimazations job and it fixed the
problem.
> After that I set up our optimazations job to run every night. This kept
> anything from happening for a while but just today we got the same call
> again and again it was fixed when I ran the optimazations job. How often
> are heavly used databases typically suppose to run the optimizations?
Any
> help on this topic?
> Thanks,
> Brandon
>