how to configure SQL Server 7.x memory

Hi,
I am curious what the steps are to follow to configure the
SQL server memory settings please?
How do you tell what the current setting is?
We have 500 MB of RAM and an additional 500 MB of RAM was
added. How do you give more memory to SQL server?
In previous posts that I have scanned for memory in here,
what is awe?
Thanks,
Cynthia
(only temporarily assisting to optimize the SQL Server, as
I am not a SQL Server DBA..any help is appreciated).Until you reach 4GB of physical RAM, PAE and AWE are unnecessary settings.
Normally, SQL will adjust itself to take all memory and leave 8-10MB free.
This is good if the server is dedicated to SQL. If other applications run
on SQL, you may want to limit how much memory SQL uses. That can be a trial
and error process since the memory consumption depends on the behavior of
other applications.
So, if it is a dedicated SQL server, leave it alone. Once the OS sees the
memory, SQL will take advantage of it.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Cynthia" <anonymous@.discussions.microsoft.com> wrote in message
news:940601c40525$32a37b00$a101280a@.phx.gbl...
> Hi,
> I am curious what the steps are to follow to configure the
> SQL server memory settings please?
> How do you tell what the current setting is?
> We have 500 MB of RAM and an additional 500 MB of RAM was
> added. How do you give more memory to SQL server?
> In previous posts that I have scanned for memory in here,
> what is awe?
> Thanks,
> Cynthia
> (only temporarily assisting to optimize the SQL Server, as
> I am not a SQL Server DBA..any help is appreciated).
>|||Geoff,
Thank you for the quick response. Does this mean you do
not have to run sp_configure "Memory" (similar command as
in sybase) to fix a set amt of memory if it is less than 4
gb in size?
Thanks
Cynthia
>--Original Message--
>Until you reach 4GB of physical RAM, PAE and AWE are
unnecessary settings.
>Normally, SQL will adjust itself to take all memory and
leave 8-10MB free.
>This is good if the server is dedicated to SQL. If other
applications run
>on SQL, you may want to limit how much memory SQL uses.
That can be a trial
>and error process since the memory consumption depends on
the behavior of
>other applications.
>So, if it is a dedicated SQL server, leave it alone.
Once the OS sees the
>memory, SQL will take advantage of it.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Cynthia" <anonymous@.discussions.microsoft.com> wrote in
message
>news:940601c40525$32a37b00$a101280a@.phx.gbl...
the
was
here,
as
>
>.
>|||Actually, there are commands to set min and max server memory. These are
for two circumstances, 1) there is more than 4GB of physical RAM in the box.
2) If you want/need to manage memory yourself. If neither apply and you
want SQL to take care of memory management, just leave it alone.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:8df701c40528$402761d0$a301280a@.phx.gbl...
> Geoff,
> Thank you for the quick response. Does this mean you do
> not have to run sp_configure "Memory" (similar command as
> in sybase) to fix a set amt of memory if it is less than 4
> gb in size?
> Thanks
> Cynthia
> unnecessary settings.
> leave 8-10MB free.
> applications run
> That can be a trial
> the behavior of
> Once the OS sees the
> message
> the
> was
> here,
> as

How to compare the previous row with current row in SQL Server 2000

Hi,
I have to compare the previous row with current row in a table in SQL Server 2000. Please help me how to do this in a optimized way. Table contains nearly 30 columns and rows count is more than 10 digits.Rows in a result set have no ipmlicit order, so you have to define what "previous" means for this to make any sense. If you mean the previous row in an arbitrary result set (like SELECT * FROM mytable), then your question is meaningless in a SQL context.

-PatP|||My problem is by taking any result set order by like (SELECT * from my table ORDER By Primary key column)
and then Comparing 1st row with 2nd
2nd row with 3rd
3rd row with 4th and so on...|||CREATE TABLE #patp (
patpId INT NOT NULL
CONSTRAINT XPKpatp PRIMARY KEY (patpId)
, baggage VARCHAR(20) NOT NULL
)

INSERT INTO #patp (patpId, baggage)
SELECT 1, 'One' UNION
SELECT 2, 'Two' UNION
SELECT 3, 'Three' UNION
SELECT 5, 'Five' UNION
SELECT 7, 'Seven' UNION
SELECT 11, 'Eleven' UNION
SELECT 13, 'Thirteen' UNION
SELECT 17, 'Seventeen' UNION
SELECT 19, 'Nineteen' UNION
SELECT 23, 'Twenty-Three'

SELECT *
FROM #patp AS a
JOIN #patp AS b
ON (b.patpId = (SELECT Max(z.patpId)
FROM #patp AS z
WHERE z.patpId < a.patpId))-PatP|||Thank you for your response.

Exactly my problem is:

Main Table

AccountNo Version Status Qunatity LastVersion
1 8 I 0 1

History Table

AccountNo Version Status Qunatity LastVersion
1 2 I 0 0
1 3 I 0 0
1 4 I 1 0
1 5 A 1 0
1 6 A 1 0
1 7 I 0 0
1 8 I 0 1

Child Table

AccountNo DocumentID Version Flag LastVersion
1 10 2 0 0
1 11 2 1 0
1 10 3 0 1
1 11 3 1 1

Here I have to filter the duplicate records in History table and child table

Scenario is:

I have to compare first row(2 nd version) with 2 nd row(3 rd version),
if status = I and all columns matches excluding version and LastVersion
then compare in child table for same accountNo for same version,
i.e., 2 version rows with 3 rd version rows for same account no
and documentID, if matches then delete 2nd version row in history and
child tables, like that I have to compare each row. This is only for status I.
finally I have to rearrange all versions and main table will get updated with
the row with higher version in the history table for same accountNo.

Finally result is:

Main Table

AccountNo Version Status Qunatity LastVersion
1 6 I 0 1

History Table (deleted 3 and 8 the version in the original history table,
rearranged versions and last version as 1 for last record

AccountNo Version Status Qunatity LastVersion
1 2 I 0 0
1 3 I 1 0
1 4 A 1 0
1 5 A 1 0
1 6 I 0 1

Child Table

AccountNo DocumentID Version Flag Lastversion
1 10 2 0 1
1 11 2 1 1

Problem: I used cursors to fetch records and compare one by one.
that table is having huge data. Only for retrieving all records it will take
30 min. So totally it is taking nearly 2 hrs. It is having morethan 30 columns.
So please could you tell me how to optimize this.|||Hello every one,

Please help me regarding this issue.
I used cursors to delete the duplicate records.
Nearly for 1000 records it is taking more than 1 hr.
I have record count more than 10 digit.
How to optimize this??|||Yeah, cursors are to be avoided in SQL server, as you are finding out. Use set-based logic to remove your duplicates instead.
Give us the DDL creation script for your table, and the fields you want to comprise a unique key.|||Did you read the hint link at the top of the forum?

Also the order of database has no meaning.

Do you have an IDENTITY Column or a datetime column for when the row was added?

Read the Hint sticky and post some info.

how to compare current date with sql db datetime data type

Hi,

I am using one datetime data type ( name: date_added ) and getdate() as default value. I want to display only those records added today. How I can compare current date with date_added.

Thanks
ManojSELECT * FROM tablename WHERE CONVERT(varchar(10),date_added,112)=CONVERT(varchar(10),GetDate(),112)

This converts both dates to yyyymmdd format, and then compares them. A better alternative might be:

SELECT * FROM tablename WHERE date_added>=CONVERT(datetime,CONVERT(varchar(10),GetDate(),112))

This presumes that there are no records added later than today. It compares the date_added to getdate() return value, converted to yyyymmdd, then converted back to a date.

How to choose the hardware for SQL Server wit large data

Hi,
I have a large data base and data keeps adding daily. I have the current
number of rows and row size. How do i calculate the hardware power required
for such data? is there any method to calculate that? using which i may go
for server with 10 processcer with 1tb hard drive and some 5gb ram.
Below is my data detail.
Table Current Rows Daily addition Chars per Row
table 1 6,900,000 10000 30
table 2 4,000,000 5000 85
table 3 4,000,000 5000 35
table 4 2,000,000 2500 30
table 5 20,000 2500 65
table 6 4,000,000 5000 105
Please help me.
Regards,
Kiran7,000,000 is not a big table in database ...
I had one back in 2002 with more than 150,000,000 rows (in an automatic
partitionned view - ok) (Hardware : 2 processors, < 40GBytes).
By the way, you need to evaluate more points:
* peek number of simultaneous connections / peek value of 'count per minute'
* mean size of row data stored and returned
...
Sl.
"N Kiran Kumar" <N Kiran Kumar@.discussions.microsoft.com> a écrit dans le
message de news: 46BB099A-37CB-4D25-9728-1110CB9804CD@.microsoft.com...
> Hi,
> I have a large data base and data keeps adding daily. I have the
> current
> number of rows and row size. How do i calculate the hardware power
> required
> for such data? is there any method to calculate that? using which i may go
> for server with 10 processcer with 1tb hard drive and some 5gb ram.
> Below is my data detail.
> Table Current Rows Daily addition Chars per Row
> table 1 6,900,000 10000 30
> table 2 4,000,000 5000 85
> table 3 4,000,000 5000 35
> table 4 2,000,000 2500 30
> table 5 20,000 2500 65
> table 6 4,000,000 5000 105
> Please help me.
> Regards,
> Kiran
>
>|||Hi
Along with other posts!!
You should also talk to your hardware supplier! The hardware does depend on
what you are doing with your database and usually that tends to change over
time with new releases/requirements etc... Looking at the current hardware
and performance counters you may get some idea, whatever you come up with may
have to be compromised by budget.
As far as disc space goes you will probably need to intellegently place your
data, therefore look for more spindles (or the ability to add more) rather
than fewer discs with higher capacity.
This site and these articles may help
http://www.sql-server-performance.com/articles/per/hardware_planning_p1.aspx
John
"N Kiran Kumar" wrote:
> Hi,
> I have a large data base and data keeps adding daily. I have the current
> number of rows and row size. How do i calculate the hardware power required
> for such data? is there any method to calculate that? using which i may go
> for server with 10 processcer with 1tb hard drive and some 5gb ram.
> Below is my data detail.
> Table Current Rows Daily addition Chars per Row
> table 1 6,900,000 10000 30
> table 2 4,000,000 5000 85
> table 3 4,000,000 5000 35
> table 4 2,000,000 2500 30
> table 5 20,000 2500 65
> table 6 4,000,000 5000 105
> Please help me.
> Regards,
> Kiran
>
>

How to Choose Method to Transfer Data from Production to Reporting database

My current project now is how to synchronize (data transfer) between
Production Database & Reporting Database using DTS, but I have no idea
how to do it and using which the best method. Need to know, that two
database have different structure tables - of course - and have a
thousand records inside, and our transaction working 24 hours, so
that's imposible to delete all records at Reporting Database and
replace with new data. The method that I've considered now is :
[vbcol=seagreen]
- 0 : haven't transfered at all
- 1 : the record have been modified
- 2 : have transfered to Reporting database
So I only need delete all records at Reporting Services which match
record at Production have flag 1, and then transfered all records that
have flag 0 and 1.
But the weakness of my method is how to synchronize for deleted
records? Should I save the primary key of deleted records, or is there
any other method?
Please give me suggestion/comment for my method.
Thanks
To determine what records have been deleted from MyTable in the Reporting
Database, you can left join MyTable from the Reporting Database (RD) back to
the Production Database (PD) on the primary key (pkey) and select
RD.MyTable.pkey where PD.Mytable.pkey is null.
Now, to delete these records from the Reporting version of MyTable:
delete from RD.MyTable where pkey in (the above subquery)
"Resant" <resant_v@.yahoo.com> wrote in message
news:1120701842.102486.317180@.f14g2000cwb.googlegr oups.com...
> My current project now is how to synchronize (data transfer) between
> Production Database & Reporting Database using DTS, but I have no idea
> how to do it and using which the best method. Need to know, that two
> database have different structure tables - of course - and have a
> thousand records inside, and our transaction working 24 hours, so
> that's imposible to delete all records at Reporting Database and
> replace with new data. The method that I've considered now is :
> - 0 : haven't transfered at all
> - 1 : the record have been modified
> - 2 : have transfered to Reporting database
> So I only need delete all records at Reporting Services which match
> record at Production have flag 1, and then transfered all records that
> have flag 0 and 1.
> But the weakness of my method is how to synchronize for deleted
> records? Should I save the primary key of deleted records, or is there
> any other method?
> Please give me suggestion/comment for my method.
> Thanks
>
|||I understand what you mean, but I've hundred records, so using that
query, I think is still have slow performance.
|||Actually, I would expect the query portion to run within a few seconds, if
the two tables are joined using an indexed column.
"Resant" <resant_v@.yahoo.com> wrote in message
news:1120784708.699397.48280@.f14g2000cwb.googlegro ups.com...
> I understand what you mean, but I've hundred records, so using that
> query, I think is still have slow performance.
>

How to Choose Method to Transfer Data from Production to Reporting database

My current project now is how to synchronize (data transfer) between
Production Database & Reporting Database using DTS, but I have no idea
how to do it and using which the best method. Need to know, that two
database have different structure tables - of course - and have a
thousand records inside, and our transaction working 24 hours, so
that's imposible to delete all records at Reporting Database and
replace with new data. The method that I've considered now is :
[vbcol=seagreen]
- 0 : haven't transfered at all
- 1 : the record have been modified
- 2 : have transfered to Reporting database
So I only need delete all records at Reporting Services which match
record at Production have flag 1, and then transfered all records that
have flag 0 and 1.
But the weakness of my method is how to synchronize for deleted
records? Should I save the primary key of deleted records, or is there
any other method?
Please give me suggestion/comment for my method.
ThanksTo determine what records have been deleted from MyTable in the Reporting
Database, you can left join MyTable from the Reporting Database (RD) back to
the Production Database (PD) on the primary key (pkey) and select
RD.MyTable.pkey where PD.Mytable.pkey is null.
Now, to delete these records from the Reporting version of MyTable:
delete from RD.MyTable where pkey in (the above subquery)
"Resant" <resant_v@.yahoo.com> wrote in message
news:1120701842.102486.317180@.f14g2000cwb.googlegroups.com...
> My current project now is how to synchronize (data transfer) between
> Production Database & Reporting Database using DTS, but I have no idea
> how to do it and using which the best method. Need to know, that two
> database have different structure tables - of course - and have a
> thousand records inside, and our transaction working 24 hours, so
> that's imposible to delete all records at Reporting Database and
> replace with new data. The method that I've considered now is :
>
> - 0 : haven't transfered at all
> - 1 : the record have been modified
> - 2 : have transfered to Reporting database
> So I only need delete all records at Reporting Services which match
> record at Production have flag 1, and then transfered all records that
> have flag 0 and 1.
> But the weakness of my method is how to synchronize for deleted
> records? Should I save the primary key of deleted records, or is there
> any other method?
> Please give me suggestion/comment for my method.
> Thanks
>|||I understand what you mean, but I've hundred records, so using that
query, I think is still have slow performance.|||Actually, I would expect the query portion to run within a few seconds, if
the two tables are joined using an indexed column.
"Resant" <resant_v@.yahoo.com> wrote in message
news:1120784708.699397.48280@.f14g2000cwb.googlegroups.com...
> I understand what you mean, but I've hundred records, so using that
> query, I think is still have slow performance.
>