Configurations like ANSI_WARNINGS

Recently I executed the MS Best Practices Analyzer in my test server, and
the report analysis alerted to me to configure the following options for
true:
ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT,
CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER to ON and NUMERIC_ROUNDABOUT to
off.
But I'm with this doubts:
If I configure these options using sp_configure 'user options', I imagine
that all my client connection goes to be configured for this configuration,
unless the client uses any SET xxx.
If I configure these options in each database with sp_dboption, when they
would have effect? They go bypass the sp_confgure configurations?
Is there some rule to help me decide if I must or it does not qualify them?
Thanks
Esio NunesHi,
sp_dboption changes settings for a database.
sp_configure to change server-level settings, and the SET statement to
change settings that affect only the current session.
Thanks
Yogish|||OK, but what means with de sp_dboption? When this setting will be applied?
Esio
"Yogish" <yogishkamathg@.icqmail.com> escreveu na mensagem
news:068B7FE1-77FF-44D6-A596-2CD81CEA01D8@.microsoft.com...
> Hi,
> sp_dboption changes settings for a database.
> sp_configure to change server-level settings, and the SET statement to
> change settings that affect only the current session.
> --
> Thanks
> Yogish|||Hi Esio,
Lets assume, you have 5 database in your SQL Server. When you use
sp_configure, it will apply to all the 5 database. And when you use
sp_dboption it will apply to a specific database (one of the 5).
HTH
--
Thanks
yogish|||I understand.
So, I have db_a and db_b. Both are with ansi warnings off.
I change with sp_dboption 'db_a','ansi warnings','on' the configuration.
Tthe user conect to db_b (with no config changed), so the ansi warning is
off, if the user change to db_a then ansi warning will be "changed" to on?
"Yogish" <yogishkamathg@.icqmail.com> escreveu na mensagem
news:353F32BC-E3D6-4B46-ACC6-1D223512E1CF@.microsoft.com...
> Hi Esio,
> Lets assume, you have 5 database in your SQL Server. When you use
> sp_configure, it will apply to all the 5 database. And when you use
> sp_dboption it will apply to a specific database (one of the 5).
> HTH
> --
> Thanks
> yogish|||That's correct.
Thanks
Yogish

Configurations like ANSI_WARNINGS

Recently I executed the MS Best Practices Analyzer in my test server, and
the report analysis alerted to me to configure the following options for
true:
ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT,
CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER to ON and NUMERIC_ROUNDABOUT to
off.
But I'm with this doubts:
If I configure these options using sp_configure 'user options', I imagine
that all my client connection goes to be configured for this configuration,
unless the client uses any SET xxx.
If I configure these options in each database with sp_dboption, when they
would have effect? They go bypass the sp_confgure configurations?
Is there some rule to help me decide if I must or it does not qualify them?
Thanks
Esio NunesHi,
sp_dboption changes settings for a database.
sp_configure to change server-level settings, and the SET statement to
change settings that affect only the current session.
--
Thanks
Yogish|||OK, but what means with de sp_dboption? When this setting will be applied?
Esio
"Yogish" <yogishkamathg@.icqmail.com> escreveu na mensagem
news:068B7FE1-77FF-44D6-A596-2CD81CEA01D8@.microsoft.com...
> Hi,
> sp_dboption changes settings for a database.
> sp_configure to change server-level settings, and the SET statement to
> change settings that affect only the current session.
> --
> Thanks
> Yogish|||Hi Esio,
Lets assume, you have 5 database in your SQL Server. When you use
sp_configure, it will apply to all the 5 database. And when you use
sp_dboption it will apply to a specific database (one of the 5).
HTH
--
Thanks
yogish|||I understand.
So, I have db_a and db_b. Both are with ansi warnings off.
I change with sp_dboption 'db_a','ansi warnings','on' the configuration.
Tthe user conect to db_b (with no config changed), so the ansi warning is
off, if the user change to db_a then ansi warning will be "changed" to on?
"Yogish" <yogishkamathg@.icqmail.com> escreveu na mensagem
news:353F32BC-E3D6-4B46-ACC6-1D223512E1CF@.microsoft.com...
> Hi Esio,
> Lets assume, you have 5 database in your SQL Server. When you use
> sp_configure, it will apply to all the 5 database. And when you use
> sp_dboption it will apply to a specific database (one of the 5).
> HTH
> --
> Thanks
> yogish|||That's correct.
--
Thanks
Yogish

Configurations error

Please help I donâ't have idea how to start!
The report server worked well. Now I have the following configurations error:
=== Pre-bind state information === LOG: DisplayName = ReportingServicesWebUserInterface
(Partial)
LOG: Appbase = file:///C:/Programme/Microsoft SQL Server/MSSQL/Reporting
Services/ReportManager
LOG: Initial PrivatePath = bin
Calling assembly : (Unknown).
===
LOG: Policy not being applied to reference at this time (private, custom,
partial, or location-based assembly bind).
LOG: Post-policy reference: ReportingServicesWebUserInterface
LOG: Attempting download of new URL
file:///C:/WINDOWS/Microsoft.NET/Framework/v1.1.4322/Temporary ASP.NET
Files/reports/432533e3/60266174/ReportingServicesWebUserInterface.DLL.
LOG: Attempting download of new URL
file:///C:/WINDOWS/Microsoft.NET/Framework/v1.1.4322/Temporary ASP.NET
Files/reports/432533e3/60266174/ReportingServicesWebUserInterface/ReportingServicesWebUserInterface.DLL.
LOG: Attempting download of new URL file:///C:/Programme/Microsoft SQL
Server/MSSQL/Reporting
Services/ReportManager/bin/ReportingServicesWebUserInterface.DLL.
LOG: Publisher policy file is not found.
LOG: No redirect found in host configuration file
(C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\aspnet.config).
LOG: Using machine configuration file from
C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\config\machine.config.
LOG: Post-policy reference: ReportingServicesWebUserInterface,
Version=8.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91Really funy!
It was really a space problem. I just delete the log-files from Reporting
services. I got space.
And it works
"Soan" wrote:
> Please help I donâ't have idea how to start!
> The report server worked well. Now I have the following configurations error:
> === Pre-bind state information ===> LOG: DisplayName = ReportingServicesWebUserInterface
> (Partial)
> LOG: Appbase = file:///C:/Programme/Microsoft SQL Server/MSSQL/Reporting
> Services/ReportManager
> LOG: Initial PrivatePath = bin
> Calling assembly : (Unknown).
> ===> LOG: Policy not being applied to reference at this time (private, custom,
> partial, or location-based assembly bind).
> LOG: Post-policy reference: ReportingServicesWebUserInterface
> LOG: Attempting download of new URL
> file:///C:/WINDOWS/Microsoft.NET/Framework/v1.1.4322/Temporary ASP.NET
> Files/reports/432533e3/60266174/ReportingServicesWebUserInterface.DLL.
> LOG: Attempting download of new URL
> file:///C:/WINDOWS/Microsoft.NET/Framework/v1.1.4322/Temporary ASP.NET
> Files/reports/432533e3/60266174/ReportingServicesWebUserInterface/ReportingServicesWebUserInterface.DLL.
> LOG: Attempting download of new URL file:///C:/Programme/Microsoft SQL
> Server/MSSQL/Reporting
> Services/ReportManager/bin/ReportingServicesWebUserInterface.DLL.
> LOG: Publisher policy file is not found.
> LOG: No redirect found in host configuration file
> (C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\aspnet.config).
> LOG: Using machine configuration file from
> C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\config\machine.config.
> LOG: Post-policy reference: ReportingServicesWebUserInterface,
> Version=8.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91

Configurations and Child Packages

Hello,

I was hoping someone might be able to clarify how configuartions work with child packages. My process has 3 levels of packages. The main packages called two child packages each of which calls more child packages.

I'm using Configurations to pass variables from parent packages to child packages. But each level of packages contains Data Flow Source/Destination connections. I was planning on having only 1 XML Configuration file in the main package that would allow the data connections for the main package to be configured for the target enviornment. When the job is scheduled I will be able to point a configuration file for use with the main packages. But will that configuration file be applied to all child packages that will run? All of the Connections Managers have the same name so I'd like to make the change in one file.

If not, am I forced to define a parent/child variable to pass the Connection String from the parent to each of the child packages? Use an expression to set the Connection String dynamically? This seems like a lot of extra work, so I'd really like to see if there is a way to do it with an XML configuration file. Even if I have to definte the same config file for each of my 20+ packages.

Thanks

-Dan

Dan,

It is eminently possible for multiple packages to use a single configuration file - I use this technique all the time. What difficulties are you having in doing this?

Tip: Indirect configuratoins can really help because once you have set them up, "picking" them for use in other packages is dead dead simple.

-Jamie

|||

I guess my confusion is just in how it will work. Perhaps I can boil it down to 2 simple questions.

1. Do I specific in every one of my packages, an XML Configuration file and point them all to the configuration file I created containing all of the common Connection Managers? Or will a child package automatically inherit the configuration of its parent package?

2. When it comes to deployment, I will schedule a job in SQL Server and I will select the XML configuartion files to use for the main package. Will the child packages know to use the same configuration file as well?

I haven't been able to run this package yet as I'm doing my final cleanup for configuration and deployment. I'm trying to get a handle on how this is going to work with the child packages to know how to finish this process. I've used configurations before but only in single package solutions.

|||

infrandom wrote:

I guess my confusion is just in how it will work. Perhaps I can boil it down to 2 simple questions.

1. Do I specific in every one of my packages, an XML Configuration file and point them all to the configuration file I created containing all of the common Connection Managers? Or will a child package automatically inherit the configuration of its parent package?

Yes to the first part of that question. No to the second part.

Which is a shame actually. I know that the subject of scoping connection managers to containers was discussed but not implemented in SSIS2005 due to time constraints. It would certainly be nice to be able to do this in order that connection managers behave just like variables and connection managers in a package can be "seen" by descendant packages. If you want to raise a ticket at Microsoft Connect requesting this functionality for a future version then I will definately vote for it.

infrandom wrote:

2. When it comes to deployment, I will schedule a job in SQL Server and I will select the XML configuartion files to use for the main package. Will the child packages know to use the same configuration file as well?

I think that is kinda answered by my answers to the previous question.

infrandom wrote:

I haven't been able to run this package yet as I'm doing my final cleanup for configuration and deployment. I'm trying to get a handle on how this is going to work with the child packages to know how to finish this process. I've used configurations before but only in single package solutions.

Sounds like you're not far away

|||

Thanks for the clarification Jamie. I was was pretty sure that I would have to add it into each of the packages. Here I go, another round through them all.

Configurations

Hello, I had a question I thought someone might be able to answer before I test my theory.

If I run an outer package through SQL Server Agent and specify it to use a configuration file, will the children packages it call also use the configuration file? My instinct is no it will not.

If this is in fact the case, would it be considered best practice to just specify the configuration file for each package through an indirect environment variable? Is it possible to set an environment variable on the set values tab within sql server agent?

Then my only concern is how would developers test packages on their machines, should they also set up the same environment variable on their machine?

Any thoughts?

Okay, I think I'm answering my own question, but perhaps others can benefit.

I think it is best to configure configurations in SSIS indirectly through an environment variable and then set the environment variable through sql server agent - command file tab, which will use the SetX command to modify the environment variable. This way all packages outer and inner can use the same dynamically configured configuration file! Although I supposse if multiple jobs tried to configure the same environment variable there could be issues, you'd have name your environment variables appropriately.

Thoughts?

|||

Configuration are not inherited between packages as you suspected.

I think you can say using indirect config files is good practice, and making you devs have the same env variable is the way to go. It actually works very well I find. I like the fact that I use the same mechanism to control connections and such like all the way from local development through to production. Configurations just work so well like that.

The Set tab in SQL Agent is not for environment variables, Set in that case is the same type of set that you do in the config file, xpath style property set, but available on teh execution command rather than wrapped in a file.

BTW, use CmdExec steps and DTEXEC, much better as you can use the step log.

|||Thanks for the response. Could you please clarify what you mean by your last statement?|||

The SSIS job step type is not very good. The CmdExec / Operating System step type supports logging of all output of the step. If you use DTEXEC and turn on step logging you get all the console output. Why is this good? Well put simply the SSIS step log capture is pants. Some errors are impossible to diagnose with it. A quick examples are file permissions, or other errors that happen before a package starts executing, see the top bit of this page -http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

|||Okay, I understand what your saying and completely agree. This is how things were done at a prior company, but here they prefer using ssis job steps for the ease of seeing what package is running. That is why I wanted to work around configuring environment variables through SQL Server Agent istead of within a batch file. Still, logging may be insufficient as you pointed out. Thanks.sqlsql

Configurations

I have read many posts about configurations. However, in practice, I cannot solve a problem that is bothering me. I have connection managers (that manage SQL Server 2005 connections) configured in a local package. I have ProtectionLevel = 1 (By the way, where do you state that you want it "DontSaveSensitive", etc?). When I deploy the package to another computer (using the deployment utility) though, I get the error "The AcquireConnection method call to the connection manager ... failed". Could someone tell me, very explicitly, how can I use configurations to solve this problem? Or are there other ways to solve the problem? The problem, of course, is that the connection managers' passwords aren't being migrated from a computer o another. Thanks a lot.

Pedro Martins

<quote>

By the way, where do you state that you want it "DontSaveSensitive", etc?

</quote>

Right-click in any empty area of the control flow tab, and select properties.

Under Security section, you will see "ProtectionLevel" property.

STEP 1:

If you want to use DontSaveSensitive level, then define a variable (say DBConnection) and sets its value to this entire string: Data Source=dbserver\dbinstance;User ID=dbuser;Password=dbpassword;Initial Catalog=YourDBName;Provider=SQLNCLI.1;Auto Translate=False

STEP 2:

Now, in the "Connection Managers" tab (usually in a thin window at the bottom), select your database connection manager, right-click and select properties. You will see "Expressions", select it - hit the ... (three dots) that appear next to it. A property expression editor window appears. In the "Property" dropdown, select "ConnectionString". Hit the ... for the expression next to it. From the list of variables in the left hand side pane, drag and drop the above variable (DBConnection) in the "Expressions:" area. Hit OK.

STEP 3: Right click in an empty area in the Control Flow tab, and select "Package Configurations...". Add a new configuration using the wizard and make sure that you select the above variable (DBConnection) as part of the configuration.

HTH,

Nitesh

|||

Can you be a little bit more specific about step 3? Must I create an XML file? What must I do specifically?

Regards,

Pedro Martins

|||

yes, you need to create an XML configuration file!

-Jamie

|||

i was trying to build my project when I got the following error:

Error 8 System.ApplicationException: Could not copy file "\\Bi4all002\cpm@.bi4all\Configura??es\BIALL2006Staging.dtsConfig" to the deployment utility output directory "D:\Visual Studio 2005\Projects\BI4ALL IS\BI4ALL IS\bin\Deployment". > System.IO.IOException: The file 'D:\Visual Studio 2005\Projects\BI4ALL IS\BI4ALL IS\bin\Deployment\BIALL2006Staging.dtsConfig' already exists. at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.File.InternalCopy(String sourceFileName, String destFileName, Boolean overwrite) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CopyFiles(ICollection fileNames, String outputPath) End of inner exception stack trace at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CopyFiles(ICollection fileNames, String outputPath) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CreateDeploymentUtility(IOutputWindow outputWindow) 0 0

As my project consists of various packages, I created configurations for all of them. Is that correct or not? Does it have something to do the problem?

Regards,

Pedro Martins

|||

Pedro Martins wrote:

As my project consists of various packages, I created configurations for all of them. Is that correct or not?

There is no right or wrong answer. Generally speaking if you are connecting to the same "thing" from multiple packages then you should share a single configuration file between them.

-Jamie

|||

Yes, I did just that. I just don't understand the error I am receiving. Do you have any hints?

Pedro Martins

configuration/load balancing

Hi

On our SQl Server (2000) we can have queries that take at least 10-20mins
(require full table scan fo billion row table). When one of these queries
is running it substantailly slows down very quick queries (sub second
queries take several seconds).

I believe there is no way to set priorities but was wondering if there
are other configuration settings that could help. The server is dual
processor so maybe setting maxdop to 1 will help. Memory size is
dynmaically assigned up to 4Gb but as the DB size is > 1Tb I'm not sure
if allowing this much memory is actually decreasing performance when the
quick query trys to get through.

Any suggestions.

Thanks
MikeMike Read wrote:
> Hi
> On our SQl Server (2000) we can have queries that take at least
> 10-20mins (require full table scan fo billion row table). When one of
> these queries is running it substantailly slows down very quick
> queries (sub second queries take several seconds).

I don't know your env and its requirements but to me the difference
between sub 1 sec and several seconds doesn't sound worth the effort
changing anything - unless, of course, you have lots of these short
queries executed in sequence and the difference sums up dramatically.

> I believe there is no way to set priorities but was wondering if there
> are other configuration settings that could help. The server is dual
> processor so maybe setting maxdop to 1 will help. Memory size is
> dynmaically assigned up to 4Gb but as the DB size is > 1Tb I'm not
> sure if allowing this much memory is actually decreasing performance
> when the quick query trys to get through.
> Any suggestions.

A random list that comes to mind:

- scheduling: make sure the long runners are done during the night or
other time when the DB is mostly idle.

- distribution of data: either via some form of replication or by moving
data from one DB to a complete different system

- optimizing SQL: additional indexes, different query conditions etc.

Cheers

robert|||Hi Robert

> I don't know your env and its requirements but to me the difference
> between sub 1 sec and several seconds doesn't sound worth the effort
> changing anything - unless, of course, you have lots of these short
> queries executed in sequence and the difference sums up dramatically.

Yes there could well be a lot of the small queries.

> - scheduling: make sure the long runners are done during the night or
> other time when the DB is mostly idle.

I'm trying to write some sort of queue to help with this but the chances
are there will always be a long running query executing at a given time.

> - distribution of data: either via some form of replication or by moving
> data from one DB to a complete different system

We're looking at getting another server to handle the long queries
so this might utilmately be the answer

> - optimizing SQL: additional indexes, different query conditions etc.

We've pretty much done what we can but some queries will always need a
full table scan.

As all queries run at the same priority I was kind of expecting a
0.1 sec query to take approx 0.2 sec (rather than 10 secs as is happening)
if another (long) query is running.

As this isn't the case I presume there's some sort of
overhead/cache/swapping occuring that I might have been able to
reduce showhow.

Thanks
Mike|||Mike Read wrote:
> Hi Robert
>> I don't know your env and its requirements but to me the difference
>> between sub 1 sec and several seconds doesn't sound worth the effort
>> changing anything - unless, of course, you have lots of these short
>> queries executed in sequence and the difference sums up dramatically.
>>
> Yes there could well be a lot of the small queries.
>>
>> - scheduling: make sure the long runners are done during the night or
>> other time when the DB is mostly idle.
>>
> I'm trying to write some sort of queue to help with this but the
> chances are there will always be a long running query executing at a
> given time.
>> - distribution of data: either via some form of replication or by
>> moving data from one DB to a complete different system
>>
> We're looking at getting another server to handle the long queries
> so this might utilmately be the answer
>> - optimizing SQL: additional indexes, different query conditions etc.
>>
> We've pretty much done what we can but some queries will always need a
> full table scan.
> As all queries run at the same priority I was kind of expecting a
> 0.1 sec query to take approx 0.2 sec (rather than 10 secs as is
> happening) if another (long) query is running.
> As this isn't the case I presume there's some sort of
> overhead/cache/swapping occuring that I might have been able to
> reduce showhow.

My guess would be that your DB is IO bound during these phases, i.e. the
long running table scans eat up all the IO bandwidth and that's slowing
you down. I'd do some measurements to verify that before you change
anything.

Kind regards

robert|||"Mike Read" <mar@.roe.ac.uk> wrote in message
news:Pine.OSF.4.63.0601251140480.472688@.reaxp06.ro e.ac.uk...
> Hi Robert
> > - distribution of data: either via some form of replication or by moving
> > data from one DB to a complete different system
> We're looking at getting another server to handle the long queries
> so this might utilmately be the answer

This may ultimately be your best answer. But...

> > - optimizing SQL: additional indexes, different query conditions etc.
> We've pretty much done what we can but some queries will always need a
> full table scan.

Why? I'd suggest perhaps posting some DDLs here. Some folks here can
sometimes do some amazing work.

> As all queries run at the same priority I was kind of expecting a
> 0.1 sec query to take approx 0.2 sec (rather than 10 secs as is happening)
> if another (long) query is running.
> As this isn't the case I presume there's some sort of
> overhead/cache/swapping occuring that I might have been able to
> reduce showhow.

Well, generally more RAM is good.

But keep in mind SQL Server 2000 Standard is limited to 2 gig of RAM.

So make sure you're using Enterprise on an OS that will permit use of more
RAM.

I'd highly suggest at least Windows 2003 for your OS and ideally moving to
SQL 2005 to boot.

For example, SQL 2005 Enterprise on Windows 2003 Enterprise can supply up to
64 Gig of RAM. (if you really have money to burn,go to Enterprise for
Itanium Systems.. 1TB of RAM. Oh and send a few checks my way. :-)

Also, you may want to if you haven't already, get more disks and partition
tables accordingly.

For example, if it's only one large table that gets scanned, move it to its
own set of disks. This will isolate the disk I/O.

> Thanks
> Mike|||Hi Greg

>>
>> We've pretty much done what we can but some queries will always need a
>> full table scan.
>>
> Why? I'd suggest perhaps posting some DDLs here. Some folks here can
> sometimes do some amazing work.

The main table is 1 billion rows of about 60 columns, we've indexed on the
most common attributes users might select on but we allow them to mine
the data using arbitary SQL so they might decide to look for an arithmetic
combination of parameters (again some of which we have anticipated and
materialized) or do stats on the columns etc.

> Well, generally more RAM is good.
> But keep in mind SQL Server 2000 Standard is limited to 2 gig of RAM.
> So make sure you're using Enterprise on an OS that will permit use of more
> RAM.
> I'd highly suggest at least Windows 2003 for your OS and ideally moving to
> SQL 2005 to boot.

We're running on Windows 2003 with 4 Gb.

> For example, SQL 2005 Enterprise on Windows 2003 Enterprise can supply up to
> 64 Gig of RAM. (if you really have money to burn,go to Enterprise for
> Itanium Systems.. 1TB of RAM. Oh and send a few checks my way. :-)

A bit out of our price range though we could almost cache the table :)

> Also, you may want to if you haven't already, get more disks and partition
> tables accordingly.
> For example, if it's only one large table that gets scanned, move it to its
> own set of disks. This will isolate the disk I/O.

Currently we spread all database/tables across 4 RAID volumes to increase
aggregate IO. Initially most queries were accessing the main large table
but the new project DB is currentlty much smaller so there is scope for
some separation

Thanks
Mike|||Mike Read (mar@.roe.ac.uk) writes:
> On our SQl Server (2000) we can have queries that take at least 10-20mins
> (require full table scan fo billion row table). When one of these queries
> is running it substantailly slows down very quick queries (sub second
> queries take several seconds).
> I believe there is no way to set priorities but was wondering if there
> are other configuration settings that could help. The server is dual
> processor so maybe setting maxdop to 1 will help. Memory size is
> dynmaically assigned up to 4Gb but as the DB size is > 1Tb I'm not sure
> if allowing this much memory is actually decreasing performance when the
> quick query trys to get through.

Setting MAXDOP to 1 will avoid the CPUs being monopolized by huge query,
but a two-way box sounds a bit thin for that amount of data.

However, the main killer here is probably memory. As you scan that 1TB
table, the cached a number of times, and all those small queries must
read from disk. 4GB also sounds a bit thin, then again, 8 or 12 GB is
not going to make that much difference anyway.

A separate server, ot at least a seprate instance for those mean queries
would be a better bet.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland

> A separate server, ot at least a seprate instance for those mean queries
> would be a better bet.

Thanks, this sounds like a reasonable way forward.

Mike|||Hello,
It sounds like you are creating a "data warehouse." Basically, there
is transactional data, where you are adding, updating, and deleting
data. For this a more normalized data schema is vastly preferred.

However, someday someone wants to pull that data out, and the users are
NOT very sophisticated about learning inner and outer joins to get
their data out.
Mostly they are looking at querying the data and reporting.

If you have a billion rows, you probably have fairly worthwhile
reporting requirements. How would you like to take your 18 minute
queries, and turn them into sub second response time? How about turning
ALL queries against a billion rows into sub second response? Look into
OLAP.

OLAP requires a different mind set. It does not solve all business
requirements, but it can take the vast majority and make them REALLY
fast.
I've implemented several OLAP solutions for various companies. It takes
a large data set to be worthwhile, and at a billion rows you are
probably approaching critical mass where you can't provide data
solutions to your data customers without it.

For grins, create a cube off your main table. You will have something
along the lines of "names of stuff." in your de normalized table. Make
3 or 4 of these "dimensions." Hopefully these "names of stuff" have no
more then 200-300 variations. Human understood Product names, or
countries or something. Dates are another great Dimension.

You will have numbers. These are frequently dollars, or counts, or
something like that. Make no more then two of these "measures."

It will process once every time you change something, so it takes some
patience to learn how to set it up.
Once you have it set up, it is a LOT faster to query.

Remember how slow it was to do table queries before you had any
indexes? OLAP is a LOT bigger step towards efficiency and speed then
indexes were over raw tables.

drop me a note if you want some help getting it going. I havent' done
one in a while, and i'm getting a little rusty, and would appreciate
being able to help someone get it going.
drmiller 100 at hotmail com

remove spaces!