configure Defaultmaxbuffersize and DefaultmaxbufferRows

i want to improve the performance of my ssis...im left with last 3 bottleneks...3 huge tables ...here are the details :

table 1 :rows : 40 million +

size of each row..1 -2 KB

i had set the default max buffer to the max..100 MB and default max buffer rows to 100,000 ... considering that average row size is 1 kb.

table 2:

rows :17 million

row size : 280 bytes

again i have set the max buffer to 100 MB and default max buffer row to 300,000 .applyin the same logic..

table 3

rows: 59 million

size per row : 85 bytes..

as the row size was small...i made default max buffer row to 1000000 that'll still be less than 100 mb..which i had set as default max buffer size....

the first 2 have given a considerable speedup..but the 3rd one (as i feared) has gone dead slow...

ne ideas..and is one huge buffer better..or many small buffers...

Can you describe what transformations you're applying to the data from table 3? And what source and destination adapters you're using?|||its a oledb source and destination....the data flow tasks i mentioned (and few other similar) r called from a sequence container in control flow... the transformation is minimal...thouh there r 2 columns compared in the where clause(just 1 in others)..and i'm not sure of indexes on that as im not in control of that DB ... ya that may be a reason...but can u suggest anything apart from that..|||

I'd suggest that you try to determine the location of the bottleneck:

To check the source, try writing out the data from the source into a Raw File, and replacing the source adapter in your existing package with a Raw File Source adapter that's pointed at this file.

To check the destination, replace the existing destination adapter with an unconfigured Export Column transform.

To check the transforms, do both of the previous things.

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!

configuration settings for a database

[reposted from comp.databases.ms-sqlserver] - sorry
Hi,
I would like to know how to get a list of configuration settings for a
database.
Is there a table that I can select this info from?
Or a file (perhaps an ini file?) of some kind?
Things like timeout settings, rollback space, table space, etc.
Thanks
Hi,
Server wide parameters will be in MASTER database , sysconfigures tables
You can either use
select * from master..sysconfigures
or use
sp_configure system procedure to view and change parameters.
Database informations:-
You can get the database informations like, number of file groups (Table
space in oracle) from sysfiles tables of each database.
use <dbname>
go
select * from sysfiles
Thanks
Hari
MCDBA
"Disco Octopus" <discooctopusN05PAM@.yahoo.com> wrote in message
news:h_MEc.118$qd4.9010@.news.optus.net.au...
> [reposted from comp.databases.ms-sqlserver] - sorry
> Hi,
> I would like to know how to get a list of configuration settings for a
> database.
> Is there a table that I can select this info from?
> Or a file (perhaps an ini file?) of some kind?
> Things like timeout settings, rollback space, table space, etc.
> Thanks
>
|||run sp_helpdb in Query Analyzer and look at the status field
can do individually and do sp_helpdb <dbname> . ie sp_helpdb pubs
Or in Enterprise Manager , select database and change the default view to
Taskpad as well as Right Click / Properties and Options.
cheers,
Andy.
"Disco Octopus" <discooctopusN05PAM@.yahoo.com> wrote in message
news:h_MEc.118$qd4.9010@.news.optus.net.au...
> [reposted from comp.databases.ms-sqlserver] - sorry
> Hi,
> I would like to know how to get a list of configuration settings for a
> database.
> Is there a table that I can select this info from?
> Or a file (perhaps an ini file?) of some kind?
> Things like timeout settings, rollback space, table space, etc.
> Thanks
>

Configuration question

We do several imports into our database per day which may contain > 150k
records in 1 table, 50k in another and 10k in a third. These imports need to
run within a transaction because if any part of the import fails we need to
rollback all the data.
I recently got a Suspect database with a corrupt log file due to the very
large transaction file.
Is there any specific maintenance I should do or configuration setting to
better handle this? I guess I would like to clear the log and shrink it
after each day. Also, there seems to be an implicit table lock when the
imports are done. Is there a way to avoid this?
Thanks,
Joe
To keep the trans log down, you want to keep the transaction small. But in
your case, this is unavoidable. All you could do is to schedule this import
at low time. Also, you might consider dumping the data into staging tables
before inserting into the base tables.
-oj
"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:e3AY2XjQFHA.2252@.TK2MSFTNGP15.phx.gbl...
> We do several imports into our database per day which may contain > 150k
> records in 1 table, 50k in another and 10k in a third. These imports need
> to
> run within a transaction because if any part of the import fails we need
> to
> rollback all the data.
> I recently got a Suspect database with a corrupt log file due to the very
> large transaction file.
> Is there any specific maintenance I should do or configuration setting to
> better handle this? I guess I would like to clear the log and shrink it
> after each day. Also, there seems to be an implicit table lock when the
> imports are done. Is there a way to avoid this?
> Thanks,
> Joe
>

Configuration question

We do several imports into our database per day which may contain > 150k
records in 1 table, 50k in another and 10k in a third. These imports need to
run within a transaction because if any part of the import fails we need to
rollback all the data.
I recently got a Suspect database with a corrupt log file due to the very
large transaction file.
Is there any specific maintenance I should do or configuration setting to
better handle this? I guess I would like to clear the log and shrink it
after each day. Also, there seems to be an implicit table lock when the
imports are done. Is there a way to avoid this?
Thanks,
JoeTo keep the trans log down, you want to keep the transaction small. But in
your case, this is unavoidable. All you could do is to schedule this import
at low time. Also, you might consider dumping the data into staging tables
before inserting into the base tables.
--
-oj
"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:e3AY2XjQFHA.2252@.TK2MSFTNGP15.phx.gbl...
> We do several imports into our database per day which may contain > 150k
> records in 1 table, 50k in another and 10k in a third. These imports need
> to
> run within a transaction because if any part of the import fails we need
> to
> rollback all the data.
> I recently got a Suspect database with a corrupt log file due to the very
> large transaction file.
> Is there any specific maintenance I should do or configuration setting to
> better handle this? I guess I would like to clear the log and shrink it
> after each day. Also, there seems to be an implicit table lock when the
> imports are done. Is there a way to avoid this?
> Thanks,
> Joe
>

Configuration question

We do several imports into our database per day which may contain > 150k
records in 1 table, 50k in another and 10k in a third. These imports need to
run within a transaction because if any part of the import fails we need to
rollback all the data.
I recently got a Suspect database with a corrupt log file due to the very
large transaction file.
Is there any specific maintenance I should do or configuration setting to
better handle this? I guess I would like to clear the log and shrink it
after each day. Also, there seems to be an implicit table lock when the
imports are done. Is there a way to avoid this?
Thanks,
JoeTo keep the trans log down, you want to keep the transaction small. But in
your case, this is unavoidable. All you could do is to schedule this import
at low time. Also, you might consider dumping the data into staging tables
before inserting into the base tables.
-oj
"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:e3AY2XjQFHA.2252@.TK2MSFTNGP15.phx.gbl...
> We do several imports into our database per day which may contain > 150k
> records in 1 table, 50k in another and 10k in a third. These imports need
> to
> run within a transaction because if any part of the import fails we need
> to
> rollback all the data.
> I recently got a Suspect database with a corrupt log file due to the very
> large transaction file.
> Is there any specific maintenance I should do or configuration setting to
> better handle this? I guess I would like to clear the log and shrink it
> after each day. Also, there seems to be an implicit table lock when the
> imports are done. Is there a way to avoid this?
> Thanks,
> Joe
>

Configuration Package doesn′t work

Good morning....
I work with many packages in 3 environments...development, homologation and production.
I had in each enviroment the table WCOT_Config with your configurations for this enviroment. There's a connection called Config wich have a expression
ServerName = System::MachineName to pickup the right server for each enviroment.

My package catches the configuration that is in the machine which the expression of the Config points. This

was functioning perfectly, but now my new packages that i place in the

production server are importing for the development server.

Suddenly the configuration stopped to function, but only for the new packages.

I have to check anything else for the package uses the configuration ?

Sorry my poor english ! heeheh

Thanks to everybody

Hy people,
I found the problem....the configuration was using the wrong connection
Thanks to everybody...

Configuration file for dts package

Hi all,
I am building a DTS pakage to read from a text file and Populate a SQL
Server table. I am using the Enterprise manager to build the package.
I wanted to know if there is a way where I can specify information like the
path of the source text files, Login/pwd,server name of the detination
server in a separate "configuration file".
The reason I am looking for this is that I might need to move my packages to
different severs who may have different name and login/pwd. Also I dont want
to hadrcode the source file path in the package.
I think It is possible with UDL files but I dont know how.
any suggestions will be highly appreciated.
Cheers,
siajHi
Good stuff is here
www.sqldts.com
"siaj" <siaj@.discussions.microsoft.com> wrote in message
news:5FF4F79F-29D6-4B2E-99DD-9EB65724F768@.microsoft.com...
> Hi all,
> I am building a DTS pakage to read from a text file and Populate a SQL
> Server table. I am using the Enterprise manager to build the package.
> I wanted to know if there is a way where I can specify information like
the
> path of the source text files, Login/pwd,server name of the detination
> server in a separate "configuration file".
> The reason I am looking for this is that I might need to move my packages
to
> different severs who may have different name and login/pwd. Also I dont
want
> to hadrcode the source file path in the package.
>
> I think It is possible with UDL files but I dont know how.
> any suggestions will be highly appreciated.
> Cheers,
> siaj|||You can use a text file or an xml document to save this info. Open it from
your package a populate global variables.
AMB
"siaj" wrote:

> Hi all,
> I am building a DTS pakage to read from a text file and Populate a SQL
> Server table. I am using the Enterprise manager to build the package.
> I wanted to know if there is a way where I can specify information like th
e
> path of the source text files, Login/pwd,server name of the detination
> server in a separate "configuration file".
> The reason I am looking for this is that I might need to move my packages
to
> different severs who may have different name and login/pwd. Also I dont wa
nt
> to hadrcode the source file path in the package.
>
> I think It is possible with UDL files but I dont know how.
> any suggestions will be highly appreciated.
> Cheers,
> siaj|||I discussed some methods here in my article:
http://vyaskn.tripod.com/sql_server...t_practices.htm
You could make use of Dynamic Properties task, .ini files.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"siaj" <siaj@.discussions.microsoft.com> wrote in message
news:5FF4F79F-29D6-4B2E-99DD-9EB65724F768@.microsoft.com...
Hi all,
I am building a DTS pakage to read from a text file and Populate a SQL
Server table. I am using the Enterprise manager to build the package.
I wanted to know if there is a way where I can specify information like the
path of the source text files, Login/pwd,server name of the detination
server in a separate "configuration file".
The reason I am looking for this is that I might need to move my packages to
different severs who may have different name and login/pwd. Also I dont want
to hadrcode the source file path in the package.
I think It is possible with UDL files but I dont know how.
any suggestions will be highly appreciated.
Cheers,
siaj|||Thanks every body ...
These stff should help me.
siaj
"siaj" wrote:

> Hi all,
> I am building a DTS pakage to read from a text file and Populate a SQL
> Server table. I am using the Enterprise manager to build the package.
> I wanted to know if there is a way where I can specify information like th
e
> path of the source text files, Login/pwd,server name of the detination
> server in a separate "configuration file".
> The reason I am looking for this is that I might need to move my packages
to
> different severs who may have different name and login/pwd. Also I dont wa
nt
> to hadrcode the source file path in the package.
>
> I think It is possible with UDL files but I dont know how.
> any suggestions will be highly appreciated.
> Cheers,
> siaj

Configuration Changes

We store our configurations in a SQL Server configuration table. We have SSIS packages that can be used in different senarios. Each of these senarios requires different configuration values.

Is it possible to change the SSIS configuration key used for a package at run time? i.e. we'd like to be able to use a job that says run this SSIS package but use configuration values with that name.

I think this is possible, yeah. If your packages need to be executed in different scenarios then you effectively need to "parameterise" those packages. Is that correct?

If so, you should lookat using the /SET option of dtexec.exe. It is provided for just this situation.

Configurations aren't really what you after. They are more about making your packages location independent, whereas it seems to me that you want to parameterise your packages.

Good luck with it.

-Jamie

|||

Thanks. We'll have to think through this a bit. *If* we could change the filter at package invocation then configurations would fit our needs perfectly. We'll probabbly look for a way to set several SSIS variables from a SQL table and use a /SET to specify which set of values to populate variables from.

I think we could pull it off with configurations using multiple tables, but I'm not fond of that either. I guess it's rough to be picky. :)

|||That's the point of configuration tables in SQL Server though -- to make things flexible...

Perhaps you could store your multiple configurations in a table and then with a stored procedure or something, update the SSIS Configuration table with the appropriate values.|||

We actually do that for sub packages, but it doesn't seem appropriate here. If I update the single set of SSIS values from two different jobs at close to the same time, it's likely one of them will run with the wrong values. Tracking that down seems like more effort than I'd like to spend if it occured.

My colleague had an idea we're investigating. Using environment values. We're thinking that perhaps by creating a batch file that sets a value on the fly and then issues a dtexec that we might achieve the result we want without putting values permanently into the evironment or exposing sensitive data.

|||

Ok, I think we have it working. We created a batch file called SSISWithConfiguration.bat:
SET env="Configuration";"[dbo].[SSIS Configurations]";%2
dtexec /DTS %1

In the job we just pass the path to the SSIS package and the Configuration filter values we want to use. The environment variables will be separate for each invocation and we only have one batch file to deploy ever. (We always call a configuration connection called Configuration). Execution looks like:
C:\MSSQL\SSISWithConfiguration.bat "\MSDB\folder\packageName" "New filter name"

In the package we just create an indirect SQL Server configuration with the environment variable as env.

During development we can use configuration settings directly and then when ready to deploy a reusable package switch to indirect (that way we don't have to have permanent environment variables on the developers desktops and the configuration table in development will get updated).

Finally, in the package, we have an XML file configuration listed first to move the Configuration connection between development, test, and production. This gets deployed once on each developers box and once on each server.

The last piece we wrote was a small utility that will let us pick a configuration setting and will write the SQL script to insert the configuration settings after deleting any existing values. We save that three times and change the values for development, test, and production.

Thanks for all the ideas and support. Sometimes it's just looking at stuff a bit different :)

Configuration

Hi,
I have a sql server database (1 table, 500.000 records with 10 fields and
full text search index) and an ASP.NET website (IIS).
What is a good harware configuration ?
A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
have sql server running on is own server ? Or 2 Go RAM ?
Thanks.
Sorry for the consulting answer but "It depends". You don't mention any
response time expectations, expected usuage, size of the IIS site, or any
availability requirements.
In general, a production SQL Server should have two or more processors.
Memory is generally very cheap and more is better.
"Steph" <no_spam@.no_spam.org> wrote in message
news:OFdiaVRZFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a sql server database (1 table, 500.000 records with 10 fields and
> full text search index) and an ASP.NET website (IIS).
> What is a good harware configuration ?
> A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
> have sql server running on is own server ? Or 2 Go RAM ?
> Thanks.
>
|||Try starting off by actually using distributed architecture and buy a
seperate, dedicate box for the SQL Server installation; otherwise, you'll be
back here again in a few months sqwaking about how much memory SS is
consuming and starving your poor little web pages to death.
If you wan to keep everything in a single soup can, buy a mainframe or a
midrange system.
Anthony Thomas

"Steph" <no_spam@.no_spam.org> wrote in message
news:OFdiaVRZFHA.2128@.TK2MSFTNGP15.phx.gbl...
Hi,
I have a sql server database (1 table, 500.000 records with 10 fields and
full text search index) and an ASP.NET website (IIS).
What is a good harware configuration ?
A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
have sql server running on is own server ? Or 2 Go RAM ?
Thanks.
sqlsql

Configuration

Hi,
I have a sql server database (1 table, 500.000 records with 10 fields and
full text search index) and an ASP.NET website (IIS).
What is a good harware configuration ?
A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
have sql server running on is own server ? Or 2 Go RAM ?
Thanks.Sorry for the consulting answer but "It depends". You don't mention any
response time expectations, expected usuage, size of the IIS site, or any
availability requirements.
In general, a production SQL Server should have two or more processors.
Memory is generally very cheap and more is better.
"Steph" <no_spam@.no_spam.org> wrote in message
news:OFdiaVRZFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a sql server database (1 table, 500.000 records with 10 fields and
> full text search index) and an ASP.NET website (IIS).
> What is a good harware configuration ?
> A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
> have sql server running on is own server ? Or 2 Go RAM ?
> Thanks.
>|||Try starting off by actually using distributed architecture and buy a
seperate, dedicate box for the SQL Server installation; otherwise, you'll be
back here again in a few months sqwaking about how much memory SS is
consuming and starving your poor little web pages to death.
If you wan to keep everything in a single soup can, buy a mainframe or a
midrange system.
Anthony Thomas
"Steph" <no_spam@.no_spam.org> wrote in message
news:OFdiaVRZFHA.2128@.TK2MSFTNGP15.phx.gbl...
Hi,
I have a sql server database (1 table, 500.000 records with 10 fields and
full text search index) and an ASP.NET website (IIS).
What is a good harware configuration ?
A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
have sql server running on is own server ? Or 2 Go RAM ?
Thanks.

Configuration

Hi,
I have a sql server database (1 table, 500.000 records with 10 fields and
full text search index) and an ASP.NET website (IIS).
What is a good harware configuration ?
A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
have sql server running on is own server ? Or 2 Go RAM ?
Thanks.Sorry for the consulting answer but "It depends". You don't mention any
response time expectations, expected usuage, size of the IIS site, or any
availability requirements.
In general, a production SQL Server should have two or more processors.
Memory is generally very cheap and more is better.
"Steph" <no_spam@.no_spam.org> wrote in message
news:OFdiaVRZFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a sql server database (1 table, 500.000 records with 10 fields and
> full text search index) and an ASP.NET website (IIS).
> What is a good harware configuration ?
> A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
> have sql server running on is own server ? Or 2 Go RAM ?
> Thanks.
>|||Try starting off by actually using distributed architecture and buy a
seperate, dedicate box for the SQL Server installation; otherwise, you'll be
back here again in a few months sqwaking about how much memory SS is
consuming and starving your poor little web pages to death.
If you wan to keep everything in a single soup can, buy a mainframe or a
midrange system.
Anthony Thomas
"Steph" <no_spam@.no_spam.org> wrote in message
news:OFdiaVRZFHA.2128@.TK2MSFTNGP15.phx.gbl...
Hi,
I have a sql server database (1 table, 500.000 records with 10 fields and
full text search index) and an ASP.NET website (IIS).
What is a good harware configuration ?
A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
have sql server running on is own server ? Or 2 Go RAM ?
Thanks.

Conection

Hello!!
Please help me!!
I want to know, if SQL server has a table where I can
find how many connections ar in this moment, or a
command. Or beteer,I what to know how many computers ar
login to SQL server ( USER ID or Network card ID) in this
moment.
Thank you,
Ionut
Hi,
execute the below procedure from query analyzer:-
sp_who
or
use the system table to query the user connections, host name, network
address etc..:-
select * from master..sysprocesses
Thanks
Hari
MCDBA
"Ionut Terhes" <tjonut@.yahoo.com> wrote in message
news:c94a01c438f1$c6f56020$a001280a@.phx.gbl...
> Hello!!
> Please help me!!
> I want to know, if SQL server has a table where I can
> find how many connections ar in this moment, or a
> command. Or beteer,I what to know how many computers ar
> login to SQL server ( USER ID or Network card ID) in this
> moment.
> Thank you,
> Ionut
|||You can also use sp_who2 which has additional information than sp_who.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Conection

Hello!!
Please help me!!
I want to know, if SQL server has a table where I can
find how many connections ar in this moment, or a
command. Or beteer,I what to know how many computers ar
login to SQL server ( USER ID or Network card ID) in this
moment.
Thank you,
IonutHi,
execute the below procedure from query analyzer:-
sp_who
or
use the system table to query the user connections, host name, network
address etc..:-
select * from master..sysprocesses
Thanks
Hari
MCDBA
"Ionut Terhes" <tjonut@.yahoo.com> wrote in message
news:c94a01c438f1$c6f56020$a001280a@.phx.gbl...
> Hello!!
> Please help me!!
> I want to know, if SQL server has a table where I can
> find how many connections ar in this moment, or a
> command. Or beteer,I what to know how many computers ar
> login to SQL server ( USER ID or Network card ID) in this
> moment.
> Thank you,
> Ionut|||You can also use sp_who2 which has additional information than sp_who.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Conditonal WHERE clause

Hi,

I have a sproc, called spGetJobs, which is querying a table called Jobs. Jobs are either filled or not filled. If filled, the DateJobFilled field will have a date value. If not filled, that field is null. The sproc takes a parameter to indicate either take all jobs or only unfilled jobs. I tried to solve this with a CASE statement in the WHERE clause, as in the following:

ALTER PROCEDURE dbo.spGetJobs
(
@.UnfilledJobs bit, -- if 1, get only unfilled jobs, else all jobs
@.StartDate smalldatetime
)
AS

select j.JobID, c.ClientID, j.JobStart, j.JobEnd
from Jobs j
join Clients c on j.ClientID = c.ClientID
where j.JobStart >= @.StartDate
and j.Role = 'client'
and (case when @.UnfilledJobs = 1 then j.JobFilledDate is not null

else 1 = 1 end)

However, VS complains of a syntax error when I try to save this.

I suppose I could construct the SELECT statement as a string and then execute it, but would rather not have to do that. Any suggestions as how to make a conditional where clause?

Thanks.

I think the problem is when your AND clause here:

Code Snippet

and (case when @.UnfilledJobs = 1 then j.JobFilledDate is not null

else 1 = 1 end)

What are you trying to accomplish with this clause? Maybe you need something like this?

Code Snippet

and ( @.UnfilledJobs = 1 and j.JobFilledDate is null or
@.unfilledJobs = 0
)

or maybe:

Code Snippet

and ( j.JobFilledDate is null or @.unfilledJobs = 0 )

|||

For Better performance use the if .. else statement; You can avoid the table scan,

Code Snippet

ALTER PROCEDURE dbo.spGetJobs

(

@.UnfilledJobs bit, -- if 1, get only unfilled jobs, else all jobs

@.StartDate smalldatetime

)

AS

If @.UnfilledJobs = 1

select j.JobID, c.ClientID, j.JobStart, j.JobEnd

from Jobs j

join Clients c on j.ClientID = c.ClientID

where j.JobStart >= @.StartDate

and j.Role = 'client'

and j.JobFilledDate is not null

else

select j.JobID, c.ClientID, j.JobStart, j.JobEnd

from Jobs j

join Clients c on j.ClientID = c.ClientID

where j.JobStart >= @.StartDate

and j.Role = 'client'

|||

Thanks. Your second code snippet did the trick. I had previously considered the if..else construct suggested by the next message, but the query is actually much more complex than what I posted (I stripped out all the unnecessary joins to simplify the issue) and I really don't want to repeat the entire query. Also, I don't think performance will be a significant issue here.

conditions, expressions

I have a table

CREATE TABLE [dbo].[CmnLanguage]
(
[Id] [char](2) NOT NULL CONSTRAINT PkCmnLanguage_Id PRIMARY KEY,
[EnglishName] [varchar](26) NOT NULL,
[NativeName] [nvarchar](26) NOT NULL,
[DirectionType] [smallint] NOT NULL,
[IsVisible] [bit] NOT NULL,
[CreatedDateTime] [datetime] NOT NULL DEFAULT GETDATE(),
[ModifiedDateTime] [datetime] NULL
)

We will use these 3 queries

select * from CmnLanguage where IsVisible = 0
select * from CmnLanguage where IsVisible = 1
select * from CmnLanguage

I want to make a method which handles these queries.

But at the back end on Stored Procedures

We have to write 3 queries

Which I don't want to do.

I want to minimize the queries and conditions

and want to just write one for these 3

Can any one do it?

How about this:

SET ANSI_NULLSONGOSET QUOTED_IDENTIFIERONGOCREATE PROCEDURE dbo.sp_MyProcedure(@.IsVisibleAS BIT =NULL)ASBEGINSELECT*FROM[dbo].[CmnLanguage]WHERE[IsVisible] =CASEWHEN @.IsVisibleISNULLTHEN [IsVisible]ELSE @.IsVisibleENDENDGO

|||

Nice.

Very Useful.

Thanks.

Conditions on latest record

I have a table that has records layed out as so:

Table:
fd_Id INT IDENTITY (1, 1)
fd_User VARCHAR(30)
fd_Effective DATETIME

Data could be as follows:
1 | "user1" | 6/20/2001
2 | "user2" | 6/1/2002
3 | "user2" | 6/5/2002
4 | "user2" | 6/5/2002
5 | "user2" | 2/1/2002
6 | "user3" | 9/1/2003
7 | "user3" | 10/2/2002
8 | "user4" | 1/1/2005

What I need to retrieve from that table is the SINGLE LATEST item of
each fd_User.

Results:
1 | "user1" | 6/20/2001
3 | "user2" | 6/5/2002 (or 4 | "user2" | 6/5/2002) since the dates are
the same but only 1 of them
6 | "user3" | 9/1/2003
8 | "user4" | 1/1/2005Untested

SELECT
MAX(FD_ID) AS 'FD_ID',
FD_USER,
MAX(FD_EFFECTIVE) AS 'FD_EFFECTIVE'
FROM F_TABLE
GROUP FD_USER|||select min(a.fd_Id) as fd_Id,
a.fd_User,
a.fd_Effective
from mytable a
inner join (select fd_User,max(fd_Effective) as fd_Effective
from mytable
group by fd_User) b on a.fd_User=b.fd_User and
a.fd_Effective=b.fd_Effective
group by a.fd_User,a.fd_Effective|||Verticon:: wrote:
> I have a table that has records layed out as so:
> Table:
> fd_Id INT IDENTITY (1, 1)
> fd_User VARCHAR(30)
> fd_Effective DATETIME
> Data could be as follows:
> 1 | "user1" | 6/20/2001
> 2 | "user2" | 6/1/2002
> 3 | "user2" | 6/5/2002
> 4 | "user2" | 6/5/2002
> 5 | "user2" | 2/1/2002
> 6 | "user3" | 9/1/2003
> 7 | "user3" | 10/2/2002
> 8 | "user4" | 1/1/2005
> What I need to retrieve from that table is the SINGLE LATEST item of
> each fd_User.
> Results:
> 1 | "user1" | 6/20/2001
> 3 | "user2" | 6/5/2002 (or 4 | "user2" | 6/5/2002) since the dates are
> the same but only 1 of them
> 6 | "user3" | 9/1/2003
> 8 | "user4" | 1/1/2005

First add the constraint that you're apparently missing:

ALTER TABLE tbl
ADD CONSTRAINT ak1_tbl
UNIQUE (fd_User, fd_Effective);

Then:

SELECT fd_Id, fd_User, fd_Effective
FROM tbl
WHERE fd_Effective =
(SELECT MAX(fd_Effective)
FROM tbl AS t
WHERE t.fd_User = tbl.fd_User);

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--sqlsql

Conditionaly hiding rows in a table in a single datagroup

Hello,
I have a report which takes data from a stored procedure and then reveals
some text on a row depending on whether or not a bit is true. The data coming
from teh stored procedure is a single row for a contact, and it displays a
number of rows in the report.
When we run the report into a PDF the data is all tidily on one page,
however in the web browser it is only showng 4 database rows per page (even
if there is only one additional line being displayed), this is because there
are so many rows (10), though the data isnt spaced out it is all at the top
of the report. Is there a way of getting more data to display in the report ?
Many Thanks
ChrisHi Chris,
I understood you would like to hide rows in a table, however I am not sure
in what condition you would like to hide the row? Would you please provide
us some detailed scenario examples?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Conditionally required field

How can I make a field required based on the status of other fields? I have
a Users table for my app that I also reference in forms that are filled out
by everyone. Most users don't need to use this table for login, so they
don't require a password. Each user has a UserName, Password, and a bit for
each privelege that I offer. If all priveleges are 0, I want to make the
password an optional field so I don't have to some up with a bunch of
passwords or use a random character generator. However, if they do have
priveleges, they are required to have a password so that if someone finds ou
t
their UserName (not hard at all), they still can't log in under a priveleged
account.
Thanks in advance
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/AppsRules.
Most people thing of rules in an IF.. THEN format which simply won't work.
Think of a rule as a boolean function where YES/TRUE accepts the row and
NO/FALSE rejects the row. Your requirements would lead to a rule like this:
Priv1 <> 0 OR Priv2<> 0 OR PRiv3 <> 0 OR Password <> ''
Look up CREATE RULE and sp_bindrule in BOL for syntax details.
Geoff N. Hiten
Microsoft SQL Server MVP
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:848BFC64-0105-42CC-8F1D-E1C4BAF25D1E@.microsoft.com...
> How can I make a field required based on the status of other fields? I
> have
> a Users table for my app that I also reference in forms that are filled
> out
> by everyone. Most users don't need to use this table for login, so they
> don't require a password. Each user has a UserName, Password, and a bit
> for
> each privelege that I offer. If all priveleges are 0, I want to make the
> password an optional field so I don't have to some up with a bunch of
> passwords or use a random character generator. However, if they do have
> priveleges, they are required to have a password so that if someone finds
> out
> their UserName (not hard at all), they still can't log in under a
> priveleged
> account.
> Thanks in advance
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||Look up CHECK constraints in SQL Server Books Online. You can easily write
one up based on the column values in a single row.
Anith|||Try:
create table t
(
PK int primary key
, UserID char (5) not null
, Password varchar (15) null
, priv1 bit not null
, priv2 bit not null
, priv3 bit not null
, constraint CK_t check (
case
when cast (priv1 as int) + priv2 + priv3 = 0 then 1
when Password is not null then 1
else 0
end = 1)
)
go
insert t values (1, 'Me', null, 0, 0, 0)
insert t values (2, 'You', null, 1, 0, 0) -- fails
insert t values (3, 'Him', 'pwd', 1, 0, 0)
go
drop table t
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:848BFC64-0105-42CC-8F1D-E1C4BAF25D1E@.microsoft.com...
How can I make a field required based on the status of other fields? I have
a Users table for my app that I also reference in forms that are filled out
by everyone. Most users don't need to use this table for login, so they
don't require a password. Each user has a UserName, Password, and a bit for
each privelege that I offer. If all priveleges are 0, I want to make the
password an optional field so I don't have to some up with a bunch of
passwords or use a random character generator. However, if they do have
priveleges, they are required to have a password so that if someone finds
out
their UserName (not hard at all), they still can't log in under a priveleged
account.
Thanks in advance
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/Apps

Conditionally Expand a Table

I have a form that has a table in the center with text boxes above and below
where the table may contain 1 to n number of rows. When the table has 4 or
more rows the page looks well balanced but anything less makes it look too
compressed.
I would like to conditionally add some blank table rows and have been
attempting this by adding rows and setting the visibility property using
RowCount(). Problem is I have 2 groups in this table so have separate row
counts. I did a test and added RowCount() to a text box in the table header
and it shows the total for the entire table. Is there a syntax for RowCount
that will allow me to reference the header RowCount from each of the group
row visibility properties?
Or is there a better way to set a minimum table size?
Thankstry putting your items inside of a rectangle as a group - I think your table
will expand and contract within the bounds of the rectangle and keep your
text boxed on either side too.
"Mike Harbinger" wrote:
> I have a form that has a table in the center with text boxes above and below
> where the table may contain 1 to n number of rows. When the table has 4 or
> more rows the page looks well balanced but anything less makes it look too
> compressed.
> I would like to conditionally add some blank table rows and have been
> attempting this by adding rows and setting the visibility property using
> RowCount(). Problem is I have 2 groups in this table so have separate row
> counts. I did a test and added RowCount() to a text box in the table header
> and it shows the total for the entire table. Is there a syntax for RowCount
> that will allow me to reference the header RowCount from each of the group
> row visibility properties?
> Or is there a better way to set a minimum table size?
> Thanks
>
>