AWE Memory on SQL Server 2000(SP4)

Hello everybody
We have windows 2003 server (8GB) with SQL Server 2004(sp4) +one small
application installed on it.
We also have \3gb parameter in the boot.ini
SQL Server is using 2,7 GB.
Application is using three processes , that each one is about 50 MB .
I took a Page Life expectancy counter which was under 300 for about 10-15
minuets and after grown up till 320 ... and then droped again...and up
again..
Do you think I need to enable AWE? If you do , how much memory to give to
SQL Server?
I have heard that there is a problem with SP4 and AWE, am I right?
Thank you.Definitely enable AWE. Also make sure that /PAE is in the boot.ini file.
You will need to apply at least one hotfix after SP4 to get AWE to woork
correctly.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Alex" <test@.hotmail.com> wrote in message
news:uzB1UnkkHHA.1240@.TK2MSFTNGP04.phx.gbl...
> Hello everybody
> We have windows 2003 server (8GB) with SQL Server 2004(sp4) +one small
> application installed on it.
> We also have \3gb parameter in the boot.ini
> SQL Server is using 2,7 GB.
> Application is using three processes , that each one is about 50 MB .
> I took a Page Life expectancy counter which was under 300 for about 10-15
> minuets and after grown up till 320 ... and then droped again...and up
> again..
> Do you think I need to enable AWE? If you do , how much memory to give to
> SQL Server?
> I have heard that there is a problem with SP4 and AWE, am I right?
> Thank you.
>
>
>
>|||"Alex" <test@.hotmail.com> wrote in message
news:uzB1UnkkHHA.1240@.TK2MSFTNGP04.phx.gbl...
> Hello everybody
> We have windows 2003 server (8GB) with SQL Server 2004(sp4) +one small
> application installed on it.
> We also have \3gb parameter in the boot.ini
> SQL Server is using 2,7 GB.
> Application is using three processes , that each one is about 50 MB .
> I took a Page Life expectancy counter which was under 300 for about 10-15
> minuets and after grown up till 320 ... and then droped again...and up
> again..
> Do you think I need to enable AWE? If you do , how much memory to give to
> SQL Server?
> I have heard that there is a problem with SP4 and AWE, am I right?
> Thank you.
I believe you need Enterprise versions of SQL Server and your OS to use AWE.
If you have these I would add /PAE and /3GB to boot.ini, install the SP4
hotfix pertaining to AWE and then enable AWE in SQL Server limiting SQL
Server to 6GB
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
When you restart SQL Server you should see Address Windowing Extensions
Enabled in the log.
You can also use the SQL Performance counter-Total Server Memory to confirm
that SQL is using 6GB.
Read these:
http://support.microsoft.com/kb/274750
http://www.sql-server-performance.com/awe_memory.asp|||Hi,
What is the hotfix after SP4 installed ?
Do I need to apply the hotfix if I am running SP3a on SQL2000 ?
JT
"Terri" wrote:
> "Alex" <test@.hotmail.com> wrote in message
> news:uzB1UnkkHHA.1240@.TK2MSFTNGP04.phx.gbl...
> > Hello everybody
> > We have windows 2003 server (8GB) with SQL Server 2004(sp4) +one small
> > application installed on it.
> >
> > We also have \3gb parameter in the boot.ini
> >
> > SQL Server is using 2,7 GB.
> > Application is using three processes , that each one is about 50 MB .
> >
> > I took a Page Life expectancy counter which was under 300 for about 10-15
> > minuets and after grown up till 320 ... and then droped again...and up
> > again..
> >
> > Do you think I need to enable AWE? If you do , how much memory to give to
> > SQL Server?
> > I have heard that there is a problem with SP4 and AWE, am I right?
> >
> > Thank you.
> I believe you need Enterprise versions of SQL Server and your OS to use AWE.
> If you have these I would add /PAE and /3GB to boot.ini, install the SP4
> hotfix pertaining to AWE and then enable AWE in SQL Server limiting SQL
> Server to 6GB
> sp_configure 'show advanced options', 1
> RECONFIGURE
> GO
> sp_configure 'awe enabled', 1
> RECONFIGURE
> GO
> sp_configure 'max server memory', 6144
> RECONFIGURE
> GO
>
> When you restart SQL Server you should see Address Windowing Extensions
> Enabled in the log.
>
> You can also use the SQL Performance counter-Total Server Memory to confirm
> that SQL is using 6GB.
>
> Read these:
> http://support.microsoft.com/kb/274750
> http://www.sql-server-performance.com/awe_memory.asp
>
>
>|||The hotfix (build 2040) fixes a bug introduced in SP4 related to AWE on
32-bit host computers. The hotfix is only necessary if you have installed
SP4 and yout host computer has more than 4GB of physicl memory.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:D1A4714E-D506-4D9F-912E-C44AB8595E92@.microsoft.com...
> Hi,
> What is the hotfix after SP4 installed ?
> Do I need to apply the hotfix if I am running SP3a on SQL2000 ?
> JT
> "Terri" wrote:
>> "Alex" <test@.hotmail.com> wrote in message
>> news:uzB1UnkkHHA.1240@.TK2MSFTNGP04.phx.gbl...
>> > Hello everybody
>> > We have windows 2003 server (8GB) with SQL Server 2004(sp4) +one small
>> > application installed on it.
>> >
>> > We also have \3gb parameter in the boot.ini
>> >
>> > SQL Server is using 2,7 GB.
>> > Application is using three processes , that each one is about 50 MB .
>> >
>> > I took a Page Life expectancy counter which was under 300 for about
>> > 10-15
>> > minuets and after grown up till 320 ... and then droped again...and
>> > up
>> > again..
>> >
>> > Do you think I need to enable AWE? If you do , how much memory to give
>> > to
>> > SQL Server?
>> > I have heard that there is a problem with SP4 and AWE, am I right?
>> >
>> > Thank you.
>> I believe you need Enterprise versions of SQL Server and your OS to use
>> AWE.
>> If you have these I would add /PAE and /3GB to boot.ini, install the SP4
>> hotfix pertaining to AWE and then enable AWE in SQL Server limiting SQL
>> Server to 6GB
>> sp_configure 'show advanced options', 1
>> RECONFIGURE
>> GO
>> sp_configure 'awe enabled', 1
>> RECONFIGURE
>> GO
>> sp_configure 'max server memory', 6144
>> RECONFIGURE
>> GO
>>
>> When you restart SQL Server you should see Address Windowing Extensions
>> Enabled in the log.
>>
>> You can also use the SQL Performance counter-Total Server Memory to
>> confirm
>> that SQL is using 6GB.
>>
>> Read these:
>> http://support.microsoft.com/kb/274750
>> http://www.sql-server-performance.com/awe_memory.asp
>>
>>

AWE and set working set size

Hello everybody!
System description:
OS: Windows Server 2003 Enterprise Edition SP1
RAM: 16 Gb
SQL: SQL Server 2000 Enterprise Edition SP3
SQL Server has "awe enabled"=1 and "set working set size"=1 at the same time.
"Max server memory" is limited by 12 Gb. SQLServer:MemoryManager:Total
Server Memory counter shows that SQL Server consumes all of the 12 Gb memory.
Allmost every day the client receives ODBC error 1222 (lock timeout) even if
only one user is connected to the database.
BOL says that if "awe enabled"=1 memory is not swapped out by default.
www.microsoft.com says that if you change "set working set size" from its
default value it may involve 844 and 845 errors (page latch timeout).
So the questions are:
1. Can two simulaneously enabled options ("awe enabled" and "set working set
size") result in such conflict?
2. Can enabling set working set size result in ODBC error 1222?
Thank youI don't know if that configuration will cause that error but I doubt it.
There is no reason to have SET Working Set Size set to 1 when you have AWE
since AWE in 2000 is not dynamic to begin with. AWE also allocates all the
memory up to the Max memory setting on startup. I would turn it off and see
if the errors go away and I see no need to have in on anyway.
--
Andrew J. Kelly SQL MVP
"RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
news:CFCDD61D-ACE4-4C25-ABF1-50395C763B8E@.microsoft.com...
> Hello everybody!
> System description:
> OS: Windows Server 2003 Enterprise Edition SP1
> RAM: 16 Gb
> SQL: SQL Server 2000 Enterprise Edition SP3
> SQL Server has "awe enabled"=1 and "set working set size"=1 at the same
> time.
> "Max server memory" is limited by 12 Gb. SQLServer:MemoryManager:Total
> Server Memory counter shows that SQL Server consumes all of the 12 Gb
> memory.
> Allmost every day the client receives ODBC error 1222 (lock timeout) even
> if
> only one user is connected to the database.
> BOL says that if "awe enabled"=1 memory is not swapped out by default.
> www.microsoft.com says that if you change "set working set size" from its
> default value it may involve 844 and 845 errors (page latch timeout).
> So the questions are:
> 1. Can two simulaneously enabled options ("awe enabled" and "set working
> set
> size") result in such conflict?
> 2. Can enabling set working set size result in ODBC error 1222?
> Thank you
>|||Thank you, Mr.Kelly. Before I read you answer I had decided to act by trial
and error and I advice to turn "working set size" off. My client informed me
that the error did not disappear but it got less frequent.|||The Lock Timeout setting is infinite by default in SQL Server. IF they are
getting this message that means someone somewhere changed the setting. I
would look for any code that has SET LOCK_TIMEOUT in it and find out why and
what value they set it to.
--
Andrew J. Kelly SQL MVP
"RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
news:69717477-0287-4FB8-9F65-96168F25F2BB@.microsoft.com...
> Thank you, Mr.Kelly. Before I read you answer I had decided to act by
> trial
> and error and I advice to turn "working set size" off. My client informed
> me
> that the error did not disappear but it got less frequent.|||Yes, I see, but can only one user working with the server lock himself? I
mean there are situations when only one user works with the database but the
error occurs all the same.
"Andrew J. Kelly" wrote:
> The Lock Timeout setting is infinite by default in SQL Server. IF they are
> getting this message that means someone somewhere changed the setting. I
> would look for any code that has SET LOCK_TIMEOUT in it and find out why and
> what value they set it to.
> --
> Andrew J. Kelly SQL MVP
>
> "RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
> news:69717477-0287-4FB8-9F65-96168F25F2BB@.microsoft.com...
> > Thank you, Mr.Kelly. Before I read you answer I had decided to act by
> > trial
> > and error and I advice to turn "working set size" off. My client informed
> > me
> > that the error did not disappear but it got less frequent.
>
>|||Sure. If the user is trying to get an exclusive table lock there are lots of
things that can prevent him from getting one. I think you need to see the
code and why they are setting this and under what conditions it is
happening.
--
Andrew J. Kelly SQL MVP
"RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
news:057E64D8-ED9F-4442-9623-FECE3BDB865E@.microsoft.com...
> Yes, I see, but can only one user working with the server lock himself? I
> mean there are situations when only one user works with the database but
> the
> error occurs all the same.
> "Andrew J. Kelly" wrote:
>> The Lock Timeout setting is infinite by default in SQL Server. IF they
>> are
>> getting this message that means someone somewhere changed the setting. I
>> would look for any code that has SET LOCK_TIMEOUT in it and find out why
>> and
>> what value they set it to.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
>> news:69717477-0287-4FB8-9F65-96168F25F2BB@.microsoft.com...
>> > Thank you, Mr.Kelly. Before I read you answer I had decided to act by
>> > trial
>> > and error and I advice to turn "working set size" off. My client
>> > informed
>> > me
>> > that the error did not disappear but it got less frequent.
>>|||There is a general interface setting which allow users to set time-out (via
the setting the code receives time interval for SET LOCK_TIMEOUT statement).
The only thing I'm going to recommend is to increase the value. Thank you,
Mr.Kelly, you have been very helpful.
"Andrew J. Kelly" wrote:
> Sure. If the user is trying to get an exclusive table lock there are lots of
> things that can prevent him from getting one. I think you need to see the
> code and why they are setting this and under what conditions it is
> happening.
> --
> Andrew J. Kelly SQL MVP
>
> "RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
> news:057E64D8-ED9F-4442-9623-FECE3BDB865E@.microsoft.com...
> > Yes, I see, but can only one user working with the server lock himself? I
> > mean there are situations when only one user works with the database but
> > the
> > error occurs all the same.
> >
> > "Andrew J. Kelly" wrote:
> >
> >> The Lock Timeout setting is infinite by default in SQL Server. IF they
> >> are
> >> getting this message that means someone somewhere changed the setting. I
> >> would look for any code that has SET LOCK_TIMEOUT in it and find out why
> >> and
> >> what value they set it to.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
> >> news:69717477-0287-4FB8-9F65-96168F25F2BB@.microsoft.com...
> >> > Thank you, Mr.Kelly. Before I read you answer I had decided to act by
> >> > trial
> >> > and error and I advice to turn "working set size" off. My client
> >> > informed
> >> > me
> >> > that the error did not disappear but it got less frequent.
> >>
> >>
> >>
>
>

Avoid to Write the Schema Name in a Function Call in SQL 2005

Avoid Writing the Schema Name in a Function Call in SQL 2005

Hi, everybody, already I try to certificate my code in Oracle PL/SQL to T-SQL to SQL Server 2005, but I have a little trouble, When I Translate the code with SQL Migration Assistant for Oracle (SSMA), this tool, put the schema name before the function name, that is a good practice, but I can avoid to do that, because, The company have a lot of calls in hard-code, and when I execute the call without the schema name, SQL2005 send me a error message, like say, “is not recognize as built-in function name”.

Somebody knows what I can do to do that?

Thanks

KC:

If the function is a scalar function you must precede the function name with the schema; it is a requirement for the way Transact SQL Works.

Avoid Cursor: detect change in a timeline hierarchy

Hi everybody,
I was wondering if I have another choice then using cursor here... see
this simple exemple:
Employee,Date,Department
1234,2001-01-01,10
1234,2001-01-02,11
1234,2001-01-03,10
1234,2001-01-04,10
1234,2001-01-05,10
1234,2001-01-06,10
1234,2001-01-07,10
1234,2001-01-08,10
1234,2001-01-09,10
I want to get a recordset like this one
Employee,From,To,Department
1234,2001-01-01,2001-01-01,10
1234,2001-01-02.2001-02-02,11
1234,2001-01-03,2001-01-09,10
Currently I use a cursor that "scan" for everyday if there is a change
and then append a row in a table but this is so slow... Is there a
better "way" to proceed?
Thanks!If the number of columns returned should vary depending on the content of
the data, then what you want is called a crosstab. There are ways to
implement this using SQL:
http://www.aspfaq.com/show.asp?id=2462
However, perhaps the best method (most flexible, easiest to maintain, etc)
would be to use an Excel pivot table, Crystal Reports crosstab, etc.
http://www.cpearson.com/excel/pivots.htm
"Mathieu Dumais-Savard" <matdumsa@.gmail.com> wrote in message
news:1140462555.378614.215210@.g43g2000cwa.googlegroups.com...
> Hi everybody,
> I was wondering if I have another choice then using cursor here... see
> this simple exemple:
> Employee,Date,Department
> 1234,2001-01-01,10
> 1234,2001-01-02,11
> 1234,2001-01-03,10
> 1234,2001-01-04,10
> 1234,2001-01-05,10
> 1234,2001-01-06,10
> 1234,2001-01-07,10
> 1234,2001-01-08,10
> 1234,2001-01-09,10
>
> I want to get a recordset like this one
> Employee,From,To,Department
> 1234,2001-01-01,2001-01-01,10
> 1234,2001-01-02.2001-02-02,11
> 1234,2001-01-03,2001-01-09,10
> Currently I use a cursor that "scan" for everyday if there is a change
> and then append a row in a table but this is so slow... Is there a
> better "way" to proceed?
> Thanks!
>|||Hi JT, thanks for your answer...
However, the number of columns is not subject to any change and the
statement must be usable in a store proc to "compress" the table... I
did a simple schema to explain it but the table contains millions of
rows of department/day/employee... All I wanna do is to "shrink down"
this table into a more friendly from..to manner...
JT wrote:
> If the number of columns returned should vary depending on the content of
> the data, then what you want is called a crosstab. There are ways to
> implement this using SQL:
> http://www.aspfaq.com/show.asp?id=2462
> However, perhaps the best method (most flexible, easiest to maintain, etc)
> would be to use an Excel pivot table, Crystal Reports crosstab, etc.
> http://www.cpearson.com/excel/pivots.htm
>
> "Mathieu Dumais-Savard" <matdumsa@.gmail.com> wrote in message
> news:1140462555.378614.215210@.g43g2000cwa.googlegroups.com...|||"Mathieu Dumais-Savard" <matdumsa@.gmail.com> wrote in message
news:1140462555.378614.215210@.g43g2000cwa.googlegroups.com...
> Hi everybody,
> I was wondering if I have another choice then using cursor here... see
> this simple exemple:
> Employee,Date,Department
> 1234,2001-01-01,10
> 1234,2001-01-02,11
> 1234,2001-01-03,10
> 1234,2001-01-04,10
> 1234,2001-01-05,10
> 1234,2001-01-06,10
> 1234,2001-01-07,10
> 1234,2001-01-08,10
> 1234,2001-01-09,10
>
> I want to get a recordset like this one
> Employee,From,To,Department
> 1234,2001-01-01,2001-01-01,10
> 1234,2001-01-02.2001-02-02,11
> 1234,2001-01-03,2001-01-09,10
> Currently I use a cursor that "scan" for everyday if there is a change
> and then append a row in a table but this is so slow... Is there a
> better "way" to proceed?
> Thanks!
>
Try the following. If you include DDL and sample data like this in future we
won't have to guess stuff like keys, datatypes, etc. You'll find you usually
get faster and better answers that way. Note my assumption about your key.
CREATE TABLE employees (employee INTEGER NOT NULL, date DATETIME NOT NULL,
department INTEGER NOT NULL, PRIMARY KEY (employee, date));
INSERT INTO employees VALUES (1234,'20010101',10);
INSERT INTO employees VALUES (1234,'20010102',11);
INSERT INTO employees VALUES (1234,'20010103',10);
INSERT INTO employees VALUES (1234,'20010104',10);
INSERT INTO employees VALUES (1234,'20010105',10);
INSERT INTO employees VALUES (1234,'20010106',10);
INSERT INTO employees VALUES (1234,'20010107',10);
INSERT INTO employees VALUES (1234,'20010108',10);
INSERT INTO employees VALUES (1234,'20010109',10);
SELECT employee,
MIN(date) AS from_dt, MAX(date) AS to_dt,
department
FROM
(SELECT E1.employee, E1.date,
MIN(E2.date) AS xdate, E1.department
FROM employees AS E1
LEFT JOIN employees AS E2
ON E1.employee = E2.employee
AND E1.date < E2.date
AND E1.department <> E2.department
GROUP BY E1.employee, E1.date, E1.department) AS T
GROUP BY employee, xdate, department ;
Hope this helps.
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/ms130214(en-US,SQL.90).aspx
--|||Wow you really rock!
Your guess about my DDL is right...
Now How can I adapt this query if I have many hierarchy field (more
then just department, let's say I have supervisor_1, supervisor_2,
title, etc...)?|||If this is your desired result:
Employee
From
To
Department
.. then what you need is a group by query.
select
EmployeeID,
DepartmentID,
min(Date) as FromDate,
max(Date) as ToDate,
from
Employee
group by
EmployeeID,
DepartmentID
"Mathieu Dumais-Savard" <matdumsa@.gmail.com> wrote in message
news:1140465351.266005.229520@.f14g2000cwb.googlegroups.com...
> Hi JT, thanks for your answer...
> However, the number of columns is not subject to any change and the
> statement must be usable in a store proc to "compress" the table... I
> did a simple schema to explain it but the table contains millions of
> rows of department/day/employee... All I wanna do is to "shrink down"
> this table into a more friendly from..to manner...
>
> JT wrote:
>|||Glad I helped.

> Now How can I adapt this query if I have many hierarchy field (more
> then just department, let's say I have supervisor_1, supervisor_2,
> title, etc...)?
I'm sure I covered that too:

> If you include DDL and sample data like this in future we
> won't have to guess stuff like keys, datatypes, etc. You'll find you
> usually
> get faster and better answers that way.
To extend my query add the constant values on which you want to group (like
employee) to the GROUP BYs and the values for which you want to track
changes (department) to the join using <>. Post some sample data and show
your required end result if you need more.
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/ms130214(en-US,SQL.90).aspx
--|||Excellent, it worked David and I understood "the logic behind"...
However, it's a little slow with a big table but I guess that my table
is too big (1755202 rows) for that kind of logic to be effective... But
still beter then the cursor :p
BTW Do you have a site/repository where you can discover all those new
way of using T-SQL in a more effective way? Like challenge with answer
and so on? I would like to improve my skill so my designs ability will
be better.
Thanks!|||Advanced Transact-SQL for SQL Server 2000
http://www.amazon.com/gp/product/18...glance&n=283155
Joe Celko's SQL for Smarties: Advanced SQL Programming
http://www.amazon.com/gp/product/15...glance&n=283155
Joe Celko's SQL Puzzles and Answers
http://www.amazon.com/gp/product/15...glance&n=283155
Joe Celko's Trees and Hierarchies in SQL for Smarties
http://www.amazon.com/gp/product/15...glance&n=283155
AMB
"Mathieu Dumais-Savard" wrote:

> Excellent, it worked David and I understood "the logic behind"...
> However, it's a little slow with a big table but I guess that my table
> is too big (1755202 rows) for that kind of logic to be effective... But
> still beter then the cursor :p
> BTW Do you have a site/repository where you can discover all those new
> way of using T-SQL in a more effective way? Like challenge with answer
> and so on? I would like to improve my skill so my designs ability will
> be better.
> Thanks!
>|||David gave you an answer, but you need to change the schema now.
You want a Base table with the (start_time, end_time) times in it and
use a NULL for the current row You can then use COALESE( end_time,
CURRENT_TIMESTAMP) in queries and reports. It will run a lot faster
than cratign it on the fly over and over.
First buy a copy of SQL FOR SMARTIES. It is a great classic. And I
need the money.
Then look for a seocnd edition of my SQL PUZZLES within a year.