MAX and DATEDIFF: February 2012

Saturday, February 25, 2012

Maximize Sql Server 2005 Memory.

Hi ,
I just installed my new server with spec show below ,
1. Win 2003 R2 32 bit
2. Sql Server 2005 32 bit
3. 4 Processor 2800 Mhz
4. 8 GB of RAM.
As I know , enable an AWE will maximize the memory usage of the Sql
Server. Is it true ? Before this , I need to set the "Lock Page Memory" ?
My intention is simple , is to make sure Sql Server 2005 fully untilize
the memory since this server only have one instance and with no other's
application sharing with it.
Please advise how is the proper configuration. Many thanks.
Travis TanIf this is a brand new server you might have been better off going with the
x64bit versions of the OS and SQL Server (assuming the hardware is x64 bit
capable) so AWE would have been unnecessary. But in this case you do need to
turn on AWE and PAE (PAE may be turned on by the OS automatically) for the
OS in order to get the most out of this. Then set the MAX Memory of SQL
Server to no more than 7GB to allow the OS to have enough memory to operate
properly.
Andrew J. Kelly SQL MVP
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:EFA5E09C-B8C5-4B9F-B665-B71509CB41C5@.microsoft.com...
> Hi ,
> I just installed my new server with spec show below ,
> 1. Win 2003 R2 32 bit
> 2. Sql Server 2005 32 bit
> 3. 4 Processor 2800 Mhz
> 4. 8 GB of RAM.
> As I know , enable an AWE will maximize the memory usage of the Sql
> Server. Is it true ? Before this , I need to set the "Lock Page Memory" ?
> My intention is simple , is to make sure Sql Server 2005 fully untilize
> the memory since this server only have one instance and with no other's
> application sharing with it.
> Please advise how is the proper configuration. Many thanks.
> --
> Travis Tan

Maximize Sql Server 2005 Memory.

Hi ,
I just installed my new server with spec show below ,
1. Win 2003 R2 32 bit
2. Sql Server 2005 32 bit
3. 4 Processor 2800 Mhz
4. 8 GB of RAM.
As I know , enable an AWE will maximize the memory usage of the Sql
Server. Is it true ? Before this , I need to set the "Lock Page Memory" ?
My intention is simple , is to make sure Sql Server 2005 fully untilize
the memory since this server only have one instance and with no other's
application sharing with it.
Please advise how is the proper configuration. Many thanks.
--
Travis TanIf this is a brand new server you might have been better off going with the
x64bit versions of the OS and SQL Server (assuming the hardware is x64 bit
capable) so AWE would have been unnecessary. But in this case you do need to
turn on AWE and PAE (PAE may be turned on by the OS automatically) for the
OS in order to get the most out of this. Then set the MAX Memory of SQL
Server to no more than 7GB to allow the OS to have enough memory to operate
properly.
--
Andrew J. Kelly SQL MVP
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:EFA5E09C-B8C5-4B9F-B665-B71509CB41C5@.microsoft.com...
> Hi ,
> I just installed my new server with spec show below ,
> 1. Win 2003 R2 32 bit
> 2. Sql Server 2005 32 bit
> 3. 4 Processor 2800 Mhz
> 4. 8 GB of RAM.
> As I know , enable an AWE will maximize the memory usage of the Sql
> Server. Is it true ? Before this , I need to set the "Lock Page Memory" ?
> My intention is simple , is to make sure Sql Server 2005 fully untilize
> the memory since this server only have one instance and with no other's
> application sharing with it.
> Please advise how is the proper configuration. Many thanks.
> --
> Travis Tan

Maximize memory usage on active-active sql cluster

we have an active-active sql cluster configured as follow:
server A - 4gb ram - sql server instance #1
server B - 4gb ram - sql server instance #2
currently each sql instance is configured to use at most 2gb of ram.
in case of failover, both instances switch on one server, effectively
usinge all 4gb without paging.
but it's quite a waste. under normal condition each server is not
using 2gb.
one option we are evaluating is configuring each server to use
min=1gb and max=3gb. and _disabling_ tha paging file.
disabling tha paging file should overcome the problem that, in case of
failover, the two instaces will start paging effectively rendering the
server unusable.
do you think it could work?
do you have any other suggestions?
thanks in advance,
cheers
/ettore
Hi
If you disable the paging file (which you can't as there is always some
kernel code paged out), then the other instance could not fail over as there
would be no RAM for it. So a failover cluster would be useless.
With the /3GB switch, user addressable memory limit is 3GB. The OS keeps 1Gb
for itself. Even with 2Gb, you are over committing memory. 1.5Gb maximum
should be your limit per instance to cater for failover.
Regards
Mike
"Ettore Pancini" wrote:

> we have an active-active sql cluster configured as follow:
> server A - 4gb ram - sql server instance #1
> server B - 4gb ram - sql server instance #2
> currently each sql instance is configured to use at most 2gb of ram.
> in case of failover, both instances switch on one server, effectively
> usinge all 4gb without paging.
> but it's quite a waste. under normal condition each server is not
> using 2gb.
> one option we are evaluating is configuring each server to use
> min=1gb and max=3gb. and _disabling_ tha paging file.
> disabling tha paging file should overcome the problem that, in case of
> failover, the two instaces will start paging effectively rendering the
> server unusable.
> do you think it could work?
> do you have any other suggestions?
> thanks in advance,
> cheers
> /ettore
>
|||thanks for answering Mike,
so, let's forget about disable paging. i understand it wont work.
and, thanks for pointing out the /3GB switch stuff wich I wasn't aware
of.
but, from BoL, reading from this page:
Dynamically Managing Memory Between Multiple Instances
exactly from this sentence "Once the amount of memory reaches the
point where only 4 MB to 10 MB is free, the instances begin competing
with each other for memory." and forward on. it seems that when in
competition, instances could free memory based on workload.
so suppose this scenario:
- instance configures as min=1.5GB, max=3.0GB
- normally each intance expand their usage to 3gb
- then one node fails
- instance wich was on the failing node is moved
- the two instances start competing
- the other instance starts to free memory
do you think it could work?
regarding paging, in the same article, BoL states that the dynamic
memory management algorithm will "ensures that the overall amount of
allocated memory remains under the level that would generate paging"
cheers, Ettore

> If you disable the paging file (which you can't as there is always some
> kernel code paged out), then the other instance could not fail over as there
> would be no RAM for it. So a failover cluster would be useless.
> With the /3GB switch, user addressable memory limit is 3GB. The OS keeps 1Gb
> for itself. Even with 2Gb, you are over committing memory. 1.5Gb maximum
> should be your limit per instance to cater for failover.
> Regards
> Mike
>
|||That's true, they can both live on the same node and compete for the
same 3GB of memory (one of my clusters is doing that at the moment in
fact). However, if they're both fairly active instances then data pages
will get swapped in and out of memory more often and the average page
life expectancy will be substantially reduced. My 2 instances are
normally fairly sedate in terms of memory allocation when on separate
nodes. At the moment they're allocating & deallocating pages in memory
like there's no tomorrow.
That's good if what you're after is to use all of your memory all of the
time. But if you're after a nice stable buffer cache then it would be
better not having them compete with each other for the same RAM.
Note bene, if you enable AWE memory in SQL (to allow a virtual address
space of more than 4GB for SQLServer; remember the kernel address space
is alway 1 or 2GB depending upon whether you enable /3GB or not) then
dynamic memory management is automatically turned off. This is because
to use physical memory above 4GB the process needs to lock pages in
memory and cannot swap pages out to disk. This means that SQL Server's
dynamic memory management goes out the window and you have to set a max
server memory limit for all instances that may reside on that node
(presumedly all nodes in the cluster have the same amount of physical
RAM) and the sum of those max server memory settings should be less than
the total physical RAM in presented to the O/S.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Ettore Pancini wrote:
[vbcol=seagreen]
>thanks for answering Mike,
>so, let's forget about disable paging. i understand it wont work.
>and, thanks for pointing out the /3GB switch stuff wich I wasn't aware
>of.
>but, from BoL, reading from this page:
>Dynamically Managing Memory Between Multiple Instances
>exactly from this sentence "Once the amount of memory reaches the
>point where only 4 MB to 10 MB is free, the instances begin competing
>with each other for memory." and forward on. it seems that when in
>competition, instances could free memory based on workload.
>so suppose this scenario:
>- instance configures as min=1.5GB, max=3.0GB
>- normally each intance expand their usage to 3gb
>- then one node fails
>- instance wich was on the failing node is moved
>- the two instances start competing
>- the other instance starts to free memory
>do you think it could work?
>regarding paging, in the same article, BoL states that the dynamic
>memory management algorithm will "ensures that the overall amount of
>allocated memory remains under the level that would generate paging"
>cheers, Ettore
>
>

Maximize Data Throughput for Network Application

Contradictory statement exists in BOL which is

version SQL 2005 SP1:

Local:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/29ce373e-18f8-46ff-aea6-

15bbb10fb9c2.htm

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/34950321-2bfd-4679-8f1b-

0a0a440eb443.htm

Online:

http://msdn2.microsoft.com/en-us/library/ms178067.aspx

http://msdn2.microsoft.com/en-us/library/ms179301.aspx

Both documents address the setting "Maximize data throughput for network applications".

While both documents state "This option may limit memory available to SQL Server for normal operation". Then one document advises to enable and the other to

disable.

I would like to know if this is simply an oversight or if the contradiction is somehow correct and due to the context by which each document is presented.

I don't see where they contradict - they seem to say the same thing:

http://msdn2.microsoft.com/en-us/library/ms178067.aspx

"Maximize Data Throughput for Network Applications
If the Maximize data throughput for network applications network connection option is selected, the operating system gives priority to applications that perform buffered I/O operations by caching their I/O pages in file system cache. This option may limit memory available to SQL Server for normal operation."

http://msdn2.microsoft.com/en-us/library/ms179301.aspx
"Maximize Data Throughput for Network Application
If the Maximize data throughput for file sharing option is selected in Network Connection, the operating system gives priority to applications that perform buffered input/output (I/O) operations by caching their I/O pages in file system cache. This option may limit memory available to SQL Server for normal operation."

Can you elaborate?

Thanks -

Buck Woody

|||

But there is contradictory. You said in the first paragraf "If the Maximize data throughput for network applications network connection option is selected" and in the second paragraf you said "If the Maximize data throughput for file sharing option is selected "

What is the correct setting?

|||I see that now. It may be because one deals with SQL Server with no options, the other with AWE memory. When you enable AWE memory the large pool is a bit different. Sorry for the confusion.

Maximize Data Throughput for Network Application

Contradictory statement exists in BOL which is

version SQL 2005 SP1:

Local:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/29ce373e-18f8-46ff-aea6-

15bbb10fb9c2.htm

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/34950321-2bfd-4679-8f1b-

0a0a440eb443.htm

Online:

http://msdn2.microsoft.com/en-us/library/ms178067.aspx

http://msdn2.microsoft.com/en-us/library/ms179301.aspx

Both documents address the setting "Maximize data throughput for network applications".

While both documents state "This option may limit memory available to SQL Server for normal operation". Then one document advises to enable and the other to

disable.

I would like to know if this is simply an oversight or if the contradiction is somehow correct and due to the context by which each document is presented.

I don't see where they contradict - they seem to say the same thing:

http://msdn2.microsoft.com/en-us/library/ms178067.aspx

Can you elaborate?

Thanks -

Buck Woody

|||

What is the correct setting?

|||I see that now. It may be because one deals with SQL Server with no options, the other with AWE memory. When you enable AWE memory the large pool is a bit different. Sorry for the confusion.

Maximize Data Throughput for Network Application

Contradictory statement exists in BOL which is

version SQL 2005 SP1:

Local:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/29ce373e-18f8-46ff-aea6-

15bbb10fb9c2.htm

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/34950321-2bfd-4679-8f1b-

0a0a440eb443.htm

Online:

http://msdn2.microsoft.com/en-us/library/ms178067.aspx

http://msdn2.microsoft.com/en-us/library/ms179301.aspx

Both documents address the setting "Maximize data throughput for network applications".

While both documents state "This option may limit memory available to SQL Server for normal operation". Then one document advises to enable and the other to

disable.

I would like to know if this is simply an oversight or if the contradiction is somehow correct and due to the context by which each document is presented.

I don't see where they contradict - they seem to say the same thing:

http://msdn2.microsoft.com/en-us/library/ms178067.aspx

Can you elaborate?

Thanks -

Buck Woody

|||

What is the correct setting?

|||I see that now. It may be because one deals with SQL Server with no options, the other with AWE memory. When you enable AWE memory the large pool is a bit different. Sorry for the confusion.

MAXFILE Size won't Change

SQL Server 2005 64 Bit Enterprise Edition.

When I run the the following command I get command completed successfully.

ALTER DATABASE Apollo_Replication
MODIFY FILE (NAME = Apollo_Replication_log,
MAXSIZE = 'UNLIMITED')

However

when I look at the database properties the file growth is still

restricted to 2 GB. I even tried to edit the property through

SSMS. Same results...It looks like it saves ok but when I go back

in... no change.
The only thing unique about the db is it is the subscriber from an Oracle publisher.
Has anyone seen this little feature before?Are you sure it is 2GB and not 2TB? The architectural limit for each log file is 2TB.|||oops it is 2 TB. But why can't I change the properties to unlimited file growth? My current size is 31 GB. I am trying to shrink the file back down to a reasonable size and change the properties.|||

UNLIMITED is never really unlimited. For log files it is 2TB. For data files it is actually 16TB. Its just a reporting issue on what the UI or report considers to be "unlimited."

MAXERRORS in BCP Does'nt work

Hi,
I'm trying to use MAXERRORS option (-m switch) to force loading to continue
even though errors occur, but the load stop at the first error occur, all
batch is commited and no error is logged in error file (-e hint)
here is the commande I'm using :
exec master.dbo.xp_cmdshell 'bcp "DBName.dbo.TableName" in
"Path\FileName.tab" -m1000 -e"Path\ErrorFile" -T -E -n -q -S"ServerName"'
Is there any people successfuly use MAXERRORS option ?
Thanks for your helpMark (sauvemark@.yahoo.com) writes:
> I'm trying to use MAXERRORS option (-m switch) to force loading to
> continue even though errors occur, but the load stop at the first error
> occur, all batch is commited and no error is logged in error file (-e
> hint)
> here is the commande I'm using :
> exec master.dbo.xp_cmdshell 'bcp "DBName.dbo.TableName" in
> "Path\FileName.tab" -m1000 -e"Path\ErrorFile" -T -E -n -q -S"ServerName"'
> Is there any people successfuly use MAXERRORS option ?
Yes, I've used it successfully. But it depends on what sort of error you
get. What is the exact output you get?
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|||> Yes, I've used it successfully
Glad to here this, I thought this a bug

> What is the exact output you get?
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY
KEY constraint
I apreciate your help
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97E969C5660BYazorman@.127.0.0.1...
> Mark (sauvemark@.yahoo.com) writes:
"Path\FileName.tab" -m1000 -e"Path\ErrorFile" -T -E -n -q -S"ServerName"'
> Yes, I've used it successfully. But it depends on what sort of error you
> get. What is the exact output you get?
>
> --
> 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|||Mark (sauvemark@.yahoo.com) writes:
> Glad to here this, I thought this a bug
>
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of
> PRIMARY KEY constraint
OK, so the errors that MAXERRORS control are conversion errors within the
file. That is, you are supposed to provide a decimal value, but the
valiue BCP finds is not decimal, so it cannot convert.
This is a pure SQL error, and for a PK violation the standard rule
applies: the entire statement is rolled back. That is, the entire BCP.
Unless you set a batch size. Then only the batch(es) with the violating
key are dropped. (At least I believe that BCP will continue, but I
have not tested, and now the hour is late.)
Setting a batchsize of 1, would give you all rows that does not violate
the PK, but that is likely to be effecient. Better is probably to use
a staging table, and then clean up the data in SQL Server.
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

Maxdop hint

Is parallelism only triggered for select statements or do
inserts/updates/deletes also go thru parallelism such as
update a
set col1 = 1
from a join b
on a.col2=b.col3 ............
or
delete from a join b on a.col1=b.col2 ........
If so, and if we want to disable parallelism, how can we do so for
deletes/updates.. I guess Im asking if i just place the option (maxdop 1)
hint at the end of the statement ?From Books Online , "Degree of Parallelism" topic
"The INSERT, UPDATE, and DELETE operators are executed serially; however,
the WHERE clause of either an UPDATE or DELETE, or SELECT portion of an
INSERT statement may be executed in parallel. The actual data changes are
then serially applied to the database."
You can disable parallelism for INSERT, UPDATE and DELETE where relevant, in
the same way as for SELECT, with OPTION (MAXDOP 1).
--
Jacco Schalkwijk
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23U6JVjvSFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Is parallelism only triggered for select statements or do
> inserts/updates/deletes also go thru parallelism such as
> update a
> set col1 = 1
> from a join b
> on a.col2=b.col3 ............
> or
> delete from a join b on a.col1=b.col2 ........
> If so, and if we want to disable parallelism, how can we do so for
> deletes/updates.. I guess Im asking if i just place the option (maxdop 1)
> hint at the end of the statement ?
>
>
>|||Parallelism can also be set globally for the server in SEM>
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23U6JVjvSFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Is parallelism only triggered for select statements or do
> inserts/updates/deletes also go thru parallelism such as
> update a
> set col1 = 1
> from a join b
> on a.col2=b.col3 ............
> or
> delete from a join b on a.col1=b.col2 ........
> If so, and if we want to disable parallelism, how can we do so for
> deletes/updates.. I guess Im asking if i just place the option (maxdop 1)
> hint at the end of the statement ?
>
>
>

maxdop 1 doesn't seem to work inside batch

I have several dynamically-generated queries that I would like to run with
the option(maxdop 1). However, if I put the queries with the option in eithe
r
Exec or sp_executesql, the option is ignored and the query runs with
parallelism (I've seen the degrees of parallelism and execution plan for thi
s
in profiler). Is there any way to do this without having to configure the
server setting?
These queries actually run inside a stored procedure, so if there is a
maxdop setting for the entire stored procedure and all the batches it
executes, that would be even better.Can you post the actual sp? The hint should affect everything in the
statement butnot the entire sp.
Andrew J. Kelly SQL MVP
"Need more Zzzz" <NeedmoreZzzz@.discussions.microsoft.com> wrote in message
news:FDD1E453-77BF-45DF-A73C-6F39D9EDB6FC@.microsoft.com...
>I have several dynamically-generated queries that I would like to run with
> the option(maxdop 1). However, if I put the queries with the option in
> either
> Exec or sp_executesql, the option is ignored and the query runs with
> parallelism (I've seen the degrees of parallelism and execution plan for
> this
> in profiler). Is there any way to do this without having to configure the
> server setting?
> These queries actually run inside a stored procedure, so if there is a
> maxdop setting for the entire stored procedure and all the batches it
> executes, that would be even better.|||tBackground: this query is fetching data from views (vStatsX). Each view is
the union of a simple SELECT from two similar tables. The query with the
maxdop option is executed WITH the option enabled in query analyzer(QA). If
I
enclose the query in an EXECUTE or sp_ExecuteSQL in QA the option is ignored
.
As I am calling this query in the sp using sp_ExecuteSQL, I'm running into
the same problem where the option is ignored.
Here is the generated query, from the profiler SP:StmtStarting:
SELECT @.Completed=@.Completed +IsNull(Count(Sup.SessionID),0),
@.Duration=@.Duration+IsNull(sum(Sup.Seconds),0)
FROM vStats4 Sup LEFT JOIN vStats1 S1 on Sup.SessionID=S1.SuperSessionID
LEFT JOIN vStats2 S2 on Sup.SessionID=S2.SuperSessionID
LEFT JOIN vStats3 S3 on Sup.SessionID=S3.SuperSessionID
WHERE Sup.date between @.dateStart and @.dateEnd and Sup.Escalated=0 and
Sup.IsAbandoned=0
AND IsNull(S1.Escalated,0)=0 AND IsNull(S2.Escalated,0)=0 AND
IsNull(S3.Escalated,0)=0
AND (Sup.Problems>0 or Sup.Questions>0 or Sup.AMSSEarches>0 OR S1.Problems>0
or S1.Questions>0 or S1.AMSSearches>0
OR S2.Problems>0 or S2.Questions>0 or S2.AMSSearches>0 OR
S3.Problems>0 or S3.Questions>0 or S3.AMSSearches>0)
OPTION(MAXDOP 1)
Here's the Profiler Output following the SP:stmtStarting:
Degree of Parallelism (7.0 Insert)
Execution Tree
--
Compute Scalar(DEFINE:([Expr1027]=[@.Completed]+i
snull([Expr1025], 0),
[Expr1028]=[@.duration]+isnull([Expr1026]
, 0)))
|--Compute Scalar(DEFINE:([Expr1025]=Convert([globa
lagg1030]),
[Expr1026]=If ([globalagg1032]=0) then NULL else [globalagg1034]))
|--Stream Aggregate(DEFINE:([globalagg1030]=SUM([p
artialagg1029]),
[globalagg1032]=SUM([partialagg1031]), [globalagg1034]=SUM([partialagg1033])
))
|--Concatenation
|--Compute Scalar(DEFINE:([partialagg1029]=[partial
agg1029]))
| |--Stream Aggregate(DEFINE:([partialagg1029]=Count
(*),
[partialagg1033]=SUM([Stats1].[Seconds])))
| |--Clustered Index
Scan(OBJECT:([noHoldCust18].[dbo].[Stats1].[MSCCSPK_20041015040614432]),
WHERE:((((([Stats1].[SuperSessionID]=NULL AND [Stats1].[Date]>=[@.dateStart])
AND [Stats1].[Date]<=[@.dateEnd]) AND (([Stats1].[Problems]>0 OR
[Stats1].[Questions]>0) OR [Stats1].[AMSSearches]>0)) AND
Convert([Stats1].[Escalated])=0) AND Convert([Stats1].[IsAbandoned])=0))
|--Compute Scalar(DEFINE:([partialagg1029]=[partial
agg1029]))
|--Stream Aggregate(DEFINE:([partialagg1029]=Count
(*),
[partialagg1033]=SUM([Stats1Archive].[Seconds])))
|--Clustered Index
Scan(OBJECT:([noHoldCust18].[dbo].[Stats1Archive].[MSCCSPK_20041015040714695]),
WHERE:((((([Stats1Archive].[SuperSessionID]=NULL AND
[Stats1Archive].[Date]>=[@.dateStart]) AND [Stats1Archive].[Date]<=[@.dateEnd])
AND (([Stats1Archive].[Problems]>0 OR [Stats1Archive].[Questions]>0) OR
[Stats1Archive].[AMSSearches]>0)) AND Convert([Stats1Archive].[Escalated])=0)
AND Convert([Stats1Archive].[IsAbandoned])=0))|||Here is how I am executing the query from within the sp:
exec sp_Executesql @.strSQL,N'@.Completed int OUTPUT,@.duration int output,
@.DateStart datetime, @.DateEnd datetime',
@.Completed output,@.duration output,@.DateStart,@.DateEnd|||I think I need more sleep...
It looks like the option IS being used in the batch calls. I reviewed the
execution plan generated when I purposely omit the option in my query and it
is different than the execution plan generated when I include the option.
This execution plan (without the option) explicitly mentions parallelism
whereas the other one does not. I'm obviously not an expert in this area but
I hope I'm on the right track now.
ompute Scalar(DEFINE:([Expr1102]=[@.Completed]+i
snull([Expr1100], 0),
[Expr1103]=[@.duration]+isnull([Expr1101]
, 0)))
|--Compute Scalar(DEFINE:([Expr1100]=Convert([globa
lagg1105]),
[Expr1101]=If ([globalagg1107]=0) then NULL else [globalagg1109]))
|--Stream Aggregate(DEFINE:([globalagg1105]=SUM([p
artialagg1104]),
[globalagg1107]=SUM([partialagg1104]), [globalagg1109]=SUM([partialagg1108])
))
|--Parallelism(Gather Streams)
|--Compute Scalar(DEFINE:([partialagg1104]=[partial
agg1104]))
etc...|||Yes I saw no indication of parallelism in the other plan. Just a note that
your where clause is almost forcing index scans. You might want to see if
you can't optimize that query.
Andrew J. Kelly SQL MVP
"Need more Zzzz" <NeedmoreZzzz@.discussions.microsoft.com> wrote in message
news:5F333045-B692-415F-9DFC-2B21FABBA6EB@.microsoft.com...
>I think I need more sleep...
> It looks like the option IS being used in the batch calls. I reviewed the
> execution plan generated when I purposely omit the option in my query and
> it
> is different than the execution plan generated when I include the option.
> This execution plan (without the option) explicitly mentions parallelism
> whereas the other one does not. I'm obviously not an expert in this area
> but
> I hope I'm on the right track now.
> ompute Scalar(DEFINE:([Expr1102]=[@.Completed]+i
snull([Expr1100], 0),
> [Expr1103]=[@.duration]+isnull([Expr1101]
, 0)))
> |--Compute Scalar(DEFINE:([Expr1100]=Convert([globa
lagg1105]),
> [Expr1101]=If ([globalagg1107]=0) then NULL else [globalagg1109]))
> |--Stream Aggregate(DEFINE:([globalagg1105]=SUM([p
artialagg1104]),
> [globalagg1107]=SUM([partialagg1104]),
> [globalagg1109]=SUM([partialagg1108])))
> |--Parallelism(Gather Streams)
> |--Compute
> Scalar(DEFINE:([partialagg1104]=[partial
agg1104]))
> etc...
>

Maxdate in table data (date range)

Hi all, First post on THIS forum...so be kind (I have my nomex suite on anyway, and am flameproof, but STILL!)

I have a table that consists of a bunch-o-gibberish, but essentially looks like this (for the sake of discussion):

PK RecID int
PK StartDate smalldatetime
PK EndDate smalldatetime
Gibberish_1 varchar(zillions and zillions)
Gibberish_2 int

OK, my query (get it? *LOL* I kill me) is this:

My PK date range is essentially a range indicating a "validity date range" or in other words - "this record applys to dates from StartDate to EndDate".

If I insert a new row, that I want to be "valid" from today on into the forseeable future. I'm thinking I would enter it with StartDate = today, and would like to figure out a good "MAXDATE" type value to put in the EndDate column so that:

1) I can find a record using a single date (i.e., the current date) and find the validation record (as defined above) - - - I'm not concerned about this part of the question, as I can build the simple query to do so).

2) NOT have to keep adjusting the EndDate to keep it ahead of the current date so that the query into the range will work.

Essentially, my question is this: What is a good value to put in the EndDate to mean "infinity" - - that will still work when I query using a single date designed to find the validation record with a range that includes it?

My boss suggested something like "January 1, 2040", but this bothers me, since my kids may be working here then :lol: and I don't want to have to force them into a Y2K-like issue where they now have to go adjust all the "maxdate" values. Anythoughts or conventions that I am unaware of here?

Thanks!
PaulSince your end date column is small datetime the max value you can store is June 6, 2079. If you change your column to datetime, max date you can put is December 31, 9999.|||Thanks...I suppose that buys enough time that it would be the problem of my GRANDkids :D so that just may do...I was just hoping for something magical like MAXDATE, so it would never have to be changed.

Oh well, it's just a typically @.nal developer concern...typical for me...it just somehow seems WRONG on so many levels to put an essentially "random" date in as the high end of an open range.

As if the database OR application will still be around in 2079 anyway *LOL*

Thanks again for confirming my worst fears (well, besides the fear of opening that container of green stuff down in the back corner of my refridgerator, that is ;) )|||I am leaving the EndDate field empty if it needs to be an open date, and in my queries still use BETWEEN:

...where @.DateParameter between DateStart and isnull(DateEnd, @.DateParameter)|||But the problem is .. his app is not gonna work after June 6, 2079 coz the max date he can put into his start date is also smalldatetime|||Hmmm...I'll check that out...I wonder if it will work though if some of the records have a valid end date?

My application will "never" have overlapping dates...but may have sequential ranges...such as:

rec1 StartDate = 01/01/2002 EndDate = 02/01/2002
rec2 StartDate = 02/02/2002 EndDate = 01/01/2004
rec3 StartDate = 01/02/2004 EndDate = NULL

will your query still work to find, say...12/31/2003 even though your query's top end is "isnull"? I guess I'll give it a test and find out.

Thanks!|||Originally posted by Enigma
But the problem is .. his app is not gonna work after June 6, 2079 coz the max date he can put into his start date is also smalldatetime

*LOL* :smacking self on the forehead: There's always THAT, if you wanna get picky ;)

Problem solved...

Though, I suppose (of Y2K taught us anything) that about May 31st, 2079 - they will change the algorithm to allow later dates...and I guess that's the brunt of my angst...I just HATE putting a "hard" date in a field that doesn't represent a hard date.

Anyway, thanks for helping with the mental exercises this morning!
Paul|||Originally posted by rdjabarov
I am leaving the EndDate field empty if it needs to be an open date, and in my queries still use BETWEEN:

...where @.DateParameter between DateStart and isnull(DateEnd, @.DateParameter)

Oops...sorry...misunderstood the function of the isnull function.|||I'd leave the end-date null, like rdjabarov suggested.|||Yeah, I seriously thought about it, but the trouble with that is my primary key includes the EndDate (and so can't be NULL).

However, that leads me to another question...does the EndDate NEED to be part of the key? Hmmmm...if the selects on this table are going to be exclusively by a single date...i.e., each day I will build a child table row by first looking to this "validation" table by using the current date...am I losing performance by not including the EndDate in the key?

My concern is that my BETWEEN clause will be less efficient if the enddate is not in the primary key.

Also keep in mind that I will also be performing selects where the BeginDate and EndDate will actually have (historical - aka past...) dates in them (as noted in an earlier post).|||You can exclude DateEnd from the PK, change the PK to non-clustered (unless you absolutely need it to be clustered), and create a clustered index with all three fields, which allows for a nullable column.

MAX_LENGTH Field does not match the value displayed?

Hi,

I wonder if anyone can help. I have installed the adventureWorksDW and when I run this query

select * from sys.all_columns where object_id = 85575343 and column_id = 2

The max_length filed value is 50 but if I view the column in explorer view the max_length is 25 I.e. ProductAlternateKey(nvarchar(25) ,null)

I don;'t know if I am looking at the correct table value?

Hi METAJOB,

nvarchar [ ( n | max ) ]

Variable-length Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for nvarchar are national char varying and national character varying.

tosc

|||

AdventureWorks is created using unicode (nchar, nvarchar, ntext).

Each 'single' character requires TWO bytes for storage. Therefore a nvarchar(25) field will require 50 bytes for storage. Max_length reports the actual storage size in bytes.

max_errors in bcp

Can any one help on this ?
I have set the -m parameter for bcp for importing but its
not working.
C:\>bcp TestDB.dbo.TestTable in E:\DataFile.txt -w -m1000
-eE:\Error.txt -SServerName -Usa -P
ThanksWhat exactly is the problem? Couyld you be more specific?
Tengiz Khartishvili
SQL Server Storage Engine
This posting is provided "as is" with no warranties and confers no rights.
"Manoj" <manoj_raheja@.hotmail.com> wrote in message
news:11a5401c3f544$0570b6c0$a401280a@.phx
.gbl...
> Can any one help on this ?
> I have set the -m parameter for bcp for importing but its
> not working.
> C:\>bcp TestDB.dbo.TestTable in E:\DataFile.txt -w -m1000
> -eE:\Error.txt -SServerName -Usa -P
>
> Thanks
>

Max/Min

I can easily get the max and min values of a dataset, but I want to get
another value in the record associated with that max or min. For example, I
am using a stored procedure to get average number of minutes per day of
week. I would want to get the min value, plus the day of week associated
with that min value. How would I go about that in reporting services?
Thanks.Using SQL:
SELECT ShipCity, 'Minimum Freight' = MIN(Freight)
FROM northwind..orders
GROUP BY ShipCity
ORDER BY 1
Using RS: See the rdl attached below that runs against the local Northwind
database.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Johnathon Dupuis" <johnathon_dupuis_no_spam@.tsged-removeme.com> wrote in
message news:uzcKK3SiEHA.1280@.TK2MSFTNGP09.phx.gbl...
> I can easily get the max and min values of a dataset, but I want to get
> another value in the record associated with that max or min. For example,
I
> am using a stored procedure to get average number of minutes per day of
> week. I would want to get the min value, plus the day of week associated
> with that min value. How would I go about that in reporting services?
> Thanks.
>
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextDecoration>Underline</TextDecoration>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Ship City</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextDecoration>Underline</TextDecoration>
<FontSize>8pt</FontSize>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Minimum Freight</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<DataSetName>Northwind</DataSetName>
<TableColumns>
<TableColumn>
<Width>1.625in</Width>
</TableColumn>
<TableColumn>
<Width>1.25in</Width>
</TableColumn>
</TableColumns>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="ShipCity">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>ShipCity</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!ShipCity.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>C</Format>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Min(Fields!Freight.Value,
"table1_Group1")</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!ShipCity.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!ShipCity.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
</TableGroup>
</TableGroups>
</Table>
</ReportItems>
<Style />
<Height>0.5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>14b06457-afff-49a5-9624-2ecc74ef5643</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>initial catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>2.875in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="ShipCity">
<DataField>ShipCity</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Freight">
<DataField>Freight</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT ShipCity, Freight
FROM Orders</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>b8405333-c29c-4c57-8ba3-7915ae7bf5eb</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>|||The sample report at the end of this posting should be close to what you
need.
The key idea here is to test if the current field matches your minimum. If
that is true the display the associated field:
=iif(Fields!OrderTotal.Value = MIN( Fields!OrderTotal.Value),
Fields!OrderDate.Value, "")
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Johnathon Dupuis" <johnathon_dupuis_no_spam@.tsged-removeme.com> wrote in
message news:uzcKK3SiEHA.1280@.TK2MSFTNGP09.phx.gbl...
> I can easily get the max and min values of a dataset, but I want to get
> another value in the record associated with that max or min. For example,
I
> am using a stored procedure to get average number of minutes per day of
> week. I would want to get the min value, plus the day of week associated
> with that min value. How would I go about that in reporting services?
> Thanks.
>
ShowAssociatedFieldBasedOnMin.rdl
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>0.75in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Order ID</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Order Total</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>If Min Order Show Order Date</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="OrderID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>OrderID</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="OrderTotal">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>OrderTotal</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderTotal.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=iif(Fields!OrderTotal.Value = MIN(
Fields!OrderTotal.Value), Fields!OrderDate.Value, "")</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>DataSet1</DataSetName>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>2in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>3a58aeb0-8249-40c7-a202-4970a3e67640</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="OrderID">
<DataField>OrderID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="OrderTotal">
<DataField>OrderTotal</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT TOP 10 Orders.OrderID, [Order
Details].UnitPrice, [Order Details].Quantity, Orders.OrderDate,
[Order Details].UnitPrice * [Order Details].Quantity
AS OrderTotal
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID
ORDER BY OrderTotal</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>663ad6a0-1d1d-4fd1-a456-dde00c15a5f1</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

Max. Ram addressed by SQL Server

Dear Sir,
Currently, I have a clustered SQL 2000 Server with 16GB ram on each Win2003
instance. I already enabled the AWE and set the max. memory used by SQL
Server at 14GB. But somebody said the max. ram can addressed by SQL Server is
less than 14GB actually, is that true? If yes, what is the max. ram can be
addressed by SQL 2000?
Also, I am planning to upgrade the platform to 64-bit Win2003 too, then what
is the max. ram can be addressed by SQL 2000 64-bit version?
Thanks a lot!
From,
Henry
This depends on the SQL Server edition and OS edition used
Windows Server 2003, Standard Edition (32-bit)
SQL Server 2000, Standard Edition (SE) 2GB
SQL Server 2000, Enterprise Edition (EE) 3GB
Windows Server 2003, Enterprise Edition (32-bit)
SQL Server 2000, Standard Edition (SE) 2GB
SQL Server 2000, Enterprise Edition (EE) 32GB
Windows Server 2003, Datacenter Edition (32-bit)
SQL Server 2000, Standard Edition (SE) 2GB
SQL Server 2000, Enterprise Edition (EE) 64GB
Windows Server 2003, Enterprise Edition (64-bit)
SQL Server 2000 EE, 64-bit Edition 64GB
Windows Server 2003, Datacenter Edition (64-bit)
SQL Server 2000 EE, 64-bit Edition 512GB
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:D3BFB45F-5B70-4905-A452-5110644C1191@.microsoft.com...
> Dear Sir,
> Currently, I have a clustered SQL 2000 Server with 16GB ram on each
> Win2003
> instance. I already enabled the AWE and set the max. memory used by SQL
> Server at 14GB. But somebody said the max. ram can addressed by SQL Server
> is
> less than 14GB actually, is that true? If yes, what is the max. ram can be
> addressed by SQL 2000?
> Also, I am planning to upgrade the platform to 64-bit Win2003 too, then
> what
> is the max. ram can be addressed by SQL 2000 64-bit version?
> Thanks a lot!
> From,
> Henry

Max. Ram addressed by SQL Server

Dear Sir,
Currently, I have a clustered SQL 2000 Server with 16GB ram on each Win2003
instance. I already enabled the AWE and set the max. memory used by SQL
Server at 14GB. But somebody said the max. ram can addressed by SQL Server i
s
less than 14GB actually, is that true? If yes, what is the max. ram can be
addressed by SQL 2000?
Also, I am planning to upgrade the platform to 64-bit Win2003 too, then what
is the max. ram can be addressed by SQL 2000 64-bit version?
Thanks a lot!
From,
HenryThis depends on the SQL Server edition and OS edition used
Windows Server 2003, Standard Edition (32-bit)
SQL Server 2000, Standard Edition (SE) 2GB
SQL Server 2000, Enterprise Edition (EE) 3GB
Windows Server 2003, Enterprise Edition (32-bit)
SQL Server 2000, Standard Edition (SE) 2GB
SQL Server 2000, Enterprise Edition (EE) 32GB
Windows Server 2003, Datacenter Edition (32-bit)
SQL Server 2000, Standard Edition (SE) 2GB
SQL Server 2000, Enterprise Edition (EE) 64GB
Windows Server 2003, Enterprise Edition (64-bit)
SQL Server 2000 EE, 64-bit Edition 64GB
Windows Server 2003, Datacenter Edition (64-bit)
SQL Server 2000 EE, 64-bit Edition 512GB
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:D3BFB45F-5B70-4905-A452-5110644C1191@.microsoft.com...
> Dear Sir,
> Currently, I have a clustered SQL 2000 Server with 16GB ram on each
> Win2003
> instance. I already enabled the AWE and set the max. memory used by SQL
> Server at 14GB. But somebody said the max. ram can addressed by SQL Server
> is
> less than 14GB actually, is that true? If yes, what is the max. ram can be
> addressed by SQL 2000?
> Also, I am planning to upgrade the platform to 64-bit Win2003 too, then
> what
> is the max. ram can be addressed by SQL 2000 64-bit version?
> Thanks a lot!
> From,
> Henry

Max. Ram addressed by SQL Server

Dear Sir,
Currently, I have a clustered SQL 2000 Server with 16GB ram on each Win2003
instance. I already enabled the AWE and set the max. memory used by SQL
Server at 14GB. But somebody said the max. ram can addressed by SQL Server is
less than 14GB actually, is that true? If yes, what is the max. ram can be
addressed by SQL 2000?
Also, I am planning to upgrade the platform to 64-bit Win2003 too, then what
is the max. ram can be addressed by SQL 2000 64-bit version?
Thanks a lot!
From,
HenryThis depends on the SQL Server edition and OS edition used
Windows Server 2003, Standard Edition (32-bit)
SQL Server 2000, Standard Edition (SE) 2GB
SQL Server 2000, Enterprise Edition (EE) 3GB
Windows Server 2003, Enterprise Edition (32-bit)
SQL Server 2000, Standard Edition (SE) 2GB
SQL Server 2000, Enterprise Edition (EE) 32GB
Windows Server 2003, Datacenter Edition (32-bit)
SQL Server 2000, Standard Edition (SE) 2GB
SQL Server 2000, Enterprise Edition (EE) 64GB
Windows Server 2003, Enterprise Edition (64-bit)
SQL Server 2000 EE, 64-bit Edition 64GB
Windows Server 2003, Datacenter Edition (64-bit)
SQL Server 2000 EE, 64-bit Edition 512GB
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:D3BFB45F-5B70-4905-A452-5110644C1191@.microsoft.com...
> Dear Sir,
> Currently, I have a clustered SQL 2000 Server with 16GB ram on each
> Win2003
> instance. I already enabled the AWE and set the max. memory used by SQL
> Server at 14GB. But somebody said the max. ram can addressed by SQL Server
> is
> less than 14GB actually, is that true? If yes, what is the max. ram can be
> addressed by SQL 2000?
> Also, I am planning to upgrade the platform to 64-bit Win2003 too, then
> what
> is the max. ram can be addressed by SQL 2000 64-bit version?
> Thanks a lot!
> From,
> Henry

Max. number of databases

Hello,
anybody knows what's the maximum number of databases i can create on a SQL
Server 2000 instance?
thanks.
AndresLook in the BOL under 'SQL Server Architecture' -> 'Implementation
Details' -> 'Maximum Capacity Specifications' It's documented as 32,767.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"andsilva" <andsilva@.hotmail.com> wrote in message
news:OEqcrb8gDHA.3784@.tk2msftngp13.phx.gbl...
> Hello,
> anybody knows what's the maximum number of databases i can create on a SQL
> Server 2000 instance?
> thanks.
>
> Andres
>|||Hi Andres,
This is documented in Books Online, under "Maximum Capacity Specifications" article
Maximum number of databases per server instance is 32767
Sincerely,
Yih-Yoon Lee [Microsoft]
Microsoft SQL Server Support
This posting is provided "AS IS" with no warranties, and confers no rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
--
| From: "andsilva" <andsilva@.hotmail.com>
| Subject: Max. number of databases
| Date: Thu, 25 Sep 2003 19:37:57 -0500
| Lines: 11
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <OEqcrb8gDHA.3784@.tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: 64.76.52.225
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:308434
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Hello,
|
| anybody knows what's the maximum number of databases i can create on a SQL
| Server 2000 instance?
|
| thanks.
|
|
| Andres
|
|
||||In theory 32,767. Practically, you probably want to test thoroughly perf, maint etc if you go above
about 100.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"andsilva" <andsilva@.hotmail.com> wrote in message news:OEqcrb8gDHA.3784@.tk2msftngp13.phx.gbl...
> Hello,
> anybody knows what's the maximum number of databases i can create on a SQL
> Server 2000 instance?
> thanks.
>
> Andres
>

Max. nodes per cluster

Hello.
I have read conflicting information about the maximum number of nodes per
cluster when using SQL Server 2000 Enterprise and Windows 2003 Enterprise (32
bit).
This article suggests it is possible to have 8 nodes
(http://www.sql-server-performance.co..._to_2005_1.asp) but in other places I have read 4 is the max.
Can someone please clear this up for me?
Four is the max for SQL Server 2000. This will rise to 8 with SQL Server
2005.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"sifuhall" <sifuhall@.discussions.microsoft.com> wrote in message
news:78CC67F6-C708-4BC4-B225-A405A11281D2@.microsoft.com...
Hello.
I have read conflicting information about the maximum number of nodes per
cluster when using SQL Server 2000 Enterprise and Windows 2003 Enterprise
(32
bit).
This article suggests it is possible to have 8 nodes
(http://www.sql-server-performance.co..._to_2005_1.asp)
but in other places I have read 4 is the max.
Can someone please clear this up for me?
|||Also see:
http://support.microsoft.com/default...b;en-us;243218
http://www.microsoft.com/technet/pro...y/hasog01.mspx
http://msdn.microsoft.com/library/de...ering_2icn.asp
http://www.microsoft.com/technet/pro.../failclus.mspx
For more information on SQL clustering.
HTH
Jerry
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YhpjH2zFHA.3588@.tk2msftngp13.phx.gbl...
> Four is the max for SQL Server 2000. This will rise to 8 with SQL Server
> 2005.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "sifuhall" <sifuhall@.discussions.microsoft.com> wrote in message
> news:78CC67F6-C708-4BC4-B225-A405A11281D2@.microsoft.com...
> Hello.
> I have read conflicting information about the maximum number of nodes per
> cluster when using SQL Server 2000 Enterprise and Windows 2003 Enterprise
> (32
> bit).
> This article suggests it is possible to have 8 nodes
> (http://www.sql-server-performance.co..._to_2005_1.asp)
> but in other places I have read 4 is the max.
> Can someone please clear this up for me?
>

Max. nodes per cluster

Hello.
I have read conflicting information about the maximum number of nodes per
cluster when using SQL Server 2000 Enterprise and Windows 2003 Enterprise (3
2
bit).
This article suggests it is possible to have 8 nodes
(http://www.sql-server-performance.c...5_1.asp
) but in other places I have read 4 is the max.
Can someone please clear this up for me?Four is the max for SQL Server 2000. This will rise to 8 with SQL Server
2005.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"sifuhall" <sifuhall@.discussions.microsoft.com> wrote in message
news:78CC67F6-C708-4BC4-B225-A405A11281D2@.microsoft.com...
Hello.
I have read conflicting information about the maximum number of nodes per
cluster when using SQL Server 2000 Enterprise and Windows 2003 Enterprise
(32
bit).
This article suggests it is possible to have 8 nodes
(http://www.sql-server-performance.c...5_1.asp
)
but in other places I have read 4 is the max.
Can someone please clear this up for me?|||Also see:
http://support.microsoft.com/defaul...kb;en-us;243218
http://www.microsoft.com/technet/pr...oy/hasog01.mspx
http://msdn.microsoft.com/library/d...
ering_2icn.asp
http://www.microsoft.com/technet/pr...n/failclus.mspx
For more information on SQL clustering.
HTH
Jerry
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YhpjH2zFHA.3588@.tk2msftngp13.phx.gbl...
> Four is the max for SQL Server 2000. This will rise to 8 with SQL Server
> 2005.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "sifuhall" <sifuhall@.discussions.microsoft.com> wrote in message
> news:78CC67F6-C708-4BC4-B225-A405A11281D2@.microsoft.com...
> Hello.
> I have read conflicting information about the maximum number of nodes per
> cluster when using SQL Server 2000 Enterprise and Windows 2003 Enterprise
> (32
> bit).
> This article suggests it is possible to have 8 nodes
> (http://www.sql-server-performance.c...5_1.a
sp)
> but in other places I have read 4 is the max.
> Can someone please clear this up for me?
>

Max. nodes per cluster

Hello.
I have read conflicting information about the maximum number of nodes per
cluster when using SQL Server 2000 Enterprise and Windows 2003 Enterprise (32
bit).
This article suggests it is possible to have 8 nodes
(http://www.sql-server-performance.com/rn_sql_server_clustering_2000_to_2005_1.asp) but in other places I have read 4 is the max.
Can someone please clear this up for me?Four is the max for SQL Server 2000. This will rise to 8 with SQL Server
2005.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"sifuhall" <sifuhall@.discussions.microsoft.com> wrote in message
news:78CC67F6-C708-4BC4-B225-A405A11281D2@.microsoft.com...
Hello.
I have read conflicting information about the maximum number of nodes per
cluster when using SQL Server 2000 Enterprise and Windows 2003 Enterprise
(32
bit).
This article suggests it is possible to have 8 nodes
(http://www.sql-server-performance.com/rn_sql_server_clustering_2000_to_2005_1.asp)
but in other places I have read 4 is the max.
Can someone please clear this up for me?|||Also see:
http://support.microsoft.com/default.aspx?scid=kb;en-us;243218
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_clustering_2icn.asp
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx
For more information on SQL clustering.
HTH
Jerry
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YhpjH2zFHA.3588@.tk2msftngp13.phx.gbl...
> Four is the max for SQL Server 2000. This will rise to 8 with SQL Server
> 2005.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "sifuhall" <sifuhall@.discussions.microsoft.com> wrote in message
> news:78CC67F6-C708-4BC4-B225-A405A11281D2@.microsoft.com...
> Hello.
> I have read conflicting information about the maximum number of nodes per
> cluster when using SQL Server 2000 Enterprise and Windows 2003 Enterprise
> (32
> bit).
> This article suggests it is possible to have 8 nodes
> (http://www.sql-server-performance.com/rn_sql_server_clustering_2000_to_2005_1.asp)
> but in other places I have read 4 is the max.
> Can someone please clear this up for me?
>

Max. No. of Columns in a Table

How many columns can be created in a table in SQL Server 2000? Is it affect
performance?1024.
The point is that a properly normalized database will never reach that
number.
Don't forger, the maximum row length is 8060 (excluding BLOB data types).
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Moorthy" <Moorthy@.discussions.microsoft.com> wrote in message
news:AB5DCB81-C11E-4128-BA9C-97E0A84F4830@.microsoft.com...
> How many columns can be created in a table in SQL Server 2000? Is it
> affect
> performance?|||The very fact that you ask this question scares me for your database design.
There are so few good RELATIONAL database designs that could actually need
the 1024 column limit that it almost a moot point. And yes, having 1000
columns in a table will not be good for performance either. For starters
every column would have to average less than 9 bytes each, so that would be
bothersome, but it would require that the server deal with all 1000 when you
do a select *
Now, I did highlight RELATIONAL (well, perhaps I screamed it,) because if
you are just using SQL Server as a data storage engine only, rather than
using a file system for all singleton access then who cares. But as that is
so unlikely, I will suggest you might want to get a database design book and
read it before doing any serious design :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Moorthy" <Moorthy@.discussions.microsoft.com> wrote in message
news:AB5DCB81-C11E-4128-BA9C-97E0A84F4830@.microsoft.com...
> How many columns can be created in a table in SQL Server 2000? Is it
> affect
> performance?|||The one stop reference for such queries is
"Maximum Capacity Specifications" section BOL.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Moorthy" <Moorthy@.discussions.microsoft.com> wrote in message
news:AB5DCB81-C11E-4128-BA9C-97E0A84F4830@.microsoft.com...
> How many columns can be created in a table in SQL Server 2000? Is it
> affect
> performance?|||
"Roji. P. Thomas" wrote:

> The one stop reference for such queries is
> "Maximum Capacity Specifications" section BOL.
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Moorthy" <Moorthy@.discussions.microsoft.com> wrote in message
> news:AB5DCB81-C11E-4128-BA9C-97E0A84F4830@.microsoft.com...
>
> Thanks for you all.
I'm not going to design new database. I'm just going to alter the database
for implementing new features in my project. That'swhy I asked this question
.

Max. memory limit for SQL Standard edition.

I read somewhere that the SQL Standard edition can only use the memory up to
2Gb.
Is it the same limit for SQL 2000 & 2005 ?
Also if I set /3GB in the boot.ini, can the memory be used up to 3Gb instead
of 2Gb ?Johnny wrote:
> I read somewhere that the SQL Standard edition can only use the
> memory up to 2Gb.
> Is it the same limit for SQL 2000 & 2005 ?
> Also if I set /3GB in the boot.ini, can the memory be used up to 3Gb
> instead of 2Gb ?
Indeed, SQL Server 2000 Standard Edition supports maximum 2GB of RAM.
However, SQL Server 2005 Standard Edition supports the maximum memory
supported by the Operating System.
On 32-bit systems, the user-mode virtual address space for each 32-bit
process is 2GB (or 3GB if you use the /3GB in boot.ini). However, most
32-bit OS-s support PAE and/or AWE. PAE allows the OS to access more
than 4GB of memory and AWE allows an application to allocate memory
beyond the 4GB limit.
For more information, see:
http://msdn2.microsoft.com/en-us/library/aa933149(SQL.80).aspx
http://msdn2.microsoft.com/en-US/library/ms143685.aspx
http://msdn2.microsoft.com/en-us/library/aa366778.aspx
http://msdn2.microsoft.com/en-us/library/aa366796.aspx
http://msdn2.microsoft.com/en-us/library/aa366527.aspx
Razvan Socol
SQL Server MVP|||Hi,
So the maximum memory limit for SQL2000 & SQL2005 is different.
In order to use more than 4Gb of memory in SQL2005, /PAE & AWE has to be
set. However, AWE is only available for SQL2005 Enterprise or Developer
edition. How Standard edition can use above 4Gb of memory.
From books online (dated April 2006).
"Support for AWE is available only in the Enterprise and Developer editions
and only applies to 32-bit operating systems. " (OR it is misleading ?)
Also from my experience, I did try to set AWE in SQL2005 standard edition
but it is not allowed. I cannot remember what the error message was. At the
end, I had to uninstall standard edition and reinstall enterprise edition.
Any ideas ? Thanks for your advices.
"Razvan Socol" wrote:
> Johnny wrote:
> > I read somewhere that the SQL Standard edition can only use the
> > memory up to 2Gb.
> > Is it the same limit for SQL 2000 & 2005 ?
> > Also if I set /3GB in the boot.ini, can the memory be used up to 3Gb
> > instead of 2Gb ?
> Indeed, SQL Server 2000 Standard Edition supports maximum 2GB of RAM.
> However, SQL Server 2005 Standard Edition supports the maximum memory
> supported by the Operating System.
> On 32-bit systems, the user-mode virtual address space for each 32-bit
> process is 2GB (or 3GB if you use the /3GB in boot.ini). However, most
> 32-bit OS-s support PAE and/or AWE. PAE allows the OS to access more
> than 4GB of memory and AWE allows an application to allocate memory
> beyond the 4GB limit.
> For more information, see:
> http://msdn2.microsoft.com/en-us/library/aa933149(SQL.80).aspx
> http://msdn2.microsoft.com/en-US/library/ms143685.aspx
> http://msdn2.microsoft.com/en-us/library/aa366778.aspx
> http://msdn2.microsoft.com/en-us/library/aa366796.aspx
> http://msdn2.microsoft.com/en-us/library/aa366527.aspx
>
> --
> Razvan Socol
> SQL Server MVP
>|||Johnny wrote:
> From books online (dated April 2006).
> "Support for AWE is available only in the Enterprise and Developer
> editions and only applies to 32-bit operating systems. " (OR it is
> misleading ?)
SQL Server 2005 Books Online (May 2007) says:
"Support for AWE is available only in the SQL Server 2005 Enterprise,
Standard, and Developer editions and only applies to 32-bit operating
systems."
See: http://msdn2.microsoft.com/en-us/library/ms187499.aspx
You can download SQL Server 2005 Books Online (May 2007) from:
http://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4
220-b133-29c1e0b6585f
However, even if you are using AWE, you are still limited by the
maximum amount of RAM accessible by the Operating System. For example,
Windows Server 2003 Standard Edition (32-bit) can only use 4GB of RAM.
To access more than 4GB of RAM on a 32-bit Windows 2003 system, you
need Windows Server 2003 Enterprise Edition or Datacenter Edition.
See (again): http://msdn2.microsoft.com/en-us/library/aa366778.aspx
--
Razvan Socol
SQL Server MVP|||Thanks for your advices.
I know all these factors have to be considered (e.g windows Enterprise
edition).
What is the best way to find out how much memory the SQL Server is currently
using ? (from task manager to show memory usage or use performance monitor).
If using performance monitor, what are the counters needed to check ? (SQL
Server memory manager ?)
Regards,
Johnny
"Razvan Socol" wrote:
> Johnny wrote:
> > From books online (dated April 2006).
> > "Support for AWE is available only in the Enterprise and Developer
> > editions and only applies to 32-bit operating systems. " (OR it is
> > misleading ?)
> SQL Server 2005 Books Online (May 2007) says:
> "Support for AWE is available only in the SQL Server 2005 Enterprise,
> Standard, and Developer editions and only applies to 32-bit operating
> systems."
> See: http://msdn2.microsoft.com/en-us/library/ms187499.aspx
> You can download SQL Server 2005 Books Online (May 2007) from:
> http://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4
> 220-b133-29c1e0b6585f
> However, even if you are using AWE, you are still limited by the
> maximum amount of RAM accessible by the Operating System. For example,
> Windows Server 2003 Standard Edition (32-bit) can only use 4GB of RAM.
> To access more than 4GB of RAM on a 32-bit Windows 2003 system, you
> need Windows Server 2003 Enterprise Edition or Datacenter Edition.
> See (again): http://msdn2.microsoft.com/en-us/library/aa366778.aspx
> --
> Razvan Socol
> SQL Server MVP
>|||Johnny wrote:
> What is the best way to find out how much memory the SQL Server is
> currently using ? (from task manager to show memory usage or use
> performance monitor). If using performance monitor, what are the
> counters needed to check ? (SQL Server memory manager ?)
I'm not sure about the best way, but one way would be DBCC MEMORYSTATUS.
--
Razvan Socol
SQL Server MVP|||You can also take a look at the following page:
SQL Server 2005 - Monitoring Memory Usage
http://msdn2.microsoft.com/en-us/library/ms176018.aspx
Ekrem Ã?nsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCBMSP, MCT
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:D86727A8-C8DB-4713-BB0A-A146991F5662@.microsoft.com...
> Thanks for your advices.
> I know all these factors have to be considered (e.g windows Enterprise
> edition).
> What is the best way to find out how much memory the SQL Server is
> currently
> using ? (from task manager to show memory usage or use performance
> monitor).
> If using performance monitor, what are the counters needed to check ? (SQL
> Server memory manager ?)
> Regards,
> Johnny
>
> "Razvan Socol" wrote:
>> Johnny wrote:
>> > From books online (dated April 2006).
>> > "Support for AWE is available only in the Enterprise and Developer
>> > editions and only applies to 32-bit operating systems. " (OR it is
>> > misleading ?)
>> SQL Server 2005 Books Online (May 2007) says:
>> "Support for AWE is available only in the SQL Server 2005 Enterprise,
>> Standard, and Developer editions and only applies to 32-bit operating
>> systems."
>> See: http://msdn2.microsoft.com/en-us/library/ms187499.aspx
>> You can download SQL Server 2005 Books Online (May 2007) from:
>> http://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4
>> 220-b133-29c1e0b6585f
>> However, even if you are using AWE, you are still limited by the
>> maximum amount of RAM accessible by the Operating System. For example,
>> Windows Server 2003 Standard Edition (32-bit) can only use 4GB of RAM.
>> To access more than 4GB of RAM on a 32-bit Windows 2003 system, you
>> need Windows Server 2003 Enterprise Edition or Datacenter Edition.
>> See (again): http://msdn2.microsoft.com/en-us/library/aa366778.aspx
>> --
>> Razvan Socol
>> SQL Server MVP

Max. length of a table in SQL 2000

Dear all,
Do you know what is the maximum length of a table name in SQL2000?
Regards,
Kwokho.128 chars
jobi
"Kwokho" <kwokho74@.hotmail.com> wrote in message
news:e#xFDHoUDHA.2240@.TK2MSFTNGP11.phx.gbl...
> Dear all,
> Do you know what is the maximum length of a table name in SQL2000?
> Regards,
> Kwokho.
>|||It is 256 characters.
You can confirm it when you check the structure of the sysobjects table in
every database.
For example,
EXEC sp_help sysobjects
The name column is where the object name resides.
"Kwokho" <kwokho74@.hotmail.com> wrote in message
news:e#xFDHoUDHA.2240@.TK2MSFTNGP11.phx.gbl...
> Dear all,
> Do you know what is the maximum length of a table name in SQL2000?
> Regards,
> Kwokho.
>|||Hi Kim,
The 256 length that is reported by sp_help for the name column in sysobjects
is the size of the column in bytes. The column is of the sysname datatype,
which is equivalent to nvarchar(128). That the size is reported as 256 bytes
is because navarchar takes 2 bytes for every character. The length is 128
characters.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Kim, KeukTae" <zyuuzika@.korea.com> wrote in message
news:OqvHIMoUDHA.2568@.tk2msftngp13.phx.gbl...
> It is 256 characters.
> You can confirm it when you check the structure of the sysobjects table in
> every database.
> For example,
> EXEC sp_help sysobjects
> The name column is where the object name resides.
> "Kwokho" <kwokho74@.hotmail.com> wrote in message
> news:e#xFDHoUDHA.2240@.TK2MSFTNGP11.phx.gbl...
> > Dear all,
> >
> > Do you know what is the maximum length of a table name in SQL2000?
> >
> > Regards,
> > Kwokho.
> >
> >
>

MAX, subquery, TOP or something else?

Ive tried many different ways to get this to work right and cant seem to get it. Should I use a Subquery, MAX, TOP or something else?!

I want the last note only for this field:("pc.Description AS Notes").

Any help is greatly appreciated!!!

CODE

SET NOCOUNT ON

SELECT
CONVERT(VARCHAR,pv.visit,101) AS DOS,
pv.TicketNumber,
ISNULL(pp.first,'')+ ' '+ ISNULL(pp.last,'')AS [Patient Name],
ISNULL(pp.Address1,'')+ ' '+ ISNULL(pp.Address2,'')+ ' '+ ISNULL(pp.City,'')+ ' '+ ISNULL(pp.State,'')+ ' '+ ISNULL(pp.Zip,'') AS [Patient Address],
IsNull(CONVERT(varchar,pp.Birthdate,101), '') AS Birthdate,
pp.PatientId,
ISNULL(ic.ListName,'') AS [Current Carrier],
ISNULL(pi.InsuredId,'') AS [Insured ID],
df.ListName AS Doctor,
df1.ListName AS Facility,
df2.ListName AS Company,
pva.PatBalance AS [Visit Pat Balance],
pva.InsBalance AS [Visit Ins Balance],
ppa.PatBalance AS [Total PatBalance],
ppa.InsBalance AS [Total InsBalance],
ISNULL(CONVERT(VARCHAR,g.LastStatement,101),'')AS [Last Statement]
-- pc.Description AS Notes,
-- MAX(pc.Created)

FROM PatientVisit pv
LEFT JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId
LEFT OUTER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
LEFT JOIN PatientInsurance pi ON pv.CurrentPICarrierId = pi.PatientInsuranceId
LEFT JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId AND pv.CurrentInsuranceCarriersId = ic.InsuranceCarriersId
LEFT JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
LEFT JOIN PatientProfileAgg ppa ON pp.PatientProfileId = ppa.PatientProfileId
INNER JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId
LEFT OUTER JOIN DoctorFacility df1 ON pv.FacilityId = df1.DoctorFacilityId
LEFT JOIN [DoctorFacility] AS df2 ON pv.[CompanyId]= df2.[DoctorFacilityId]
LEFT OUTER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId
LEFT JOIN PatientCorrespondence pc ON pp.PatientProfileId = pc.PatientProfileId AND pv.PatientVisitId = pc.PatientVisitId

WHERE pv.PatientVisitId = ('6')

-- GROUP BY pv.visit, pv.ticketnumber, pp.first, pp.last, pp.address1, pp.address2, pp.city, pp.state, pp.zip,
-- pp.birthdate, pp.patientid, ic.listname, PI.insuredid, df.listname, df1.listname, df2.listname, pva.patbalance,
-- pva.insbalance, ppa.patbalance, ppa.insbalance, g.laststatement, pc.description,pc.created

As you suggested there are a few ways you could do it, a subquery should be as good as any, and simpler than most others

SELECT ..., (SELECT TOP 1 description
FROM PatientCorrespondence
WHERE PatientProfileID = pp.PatientProfileID AND PatientVisitID = pv.PatientVisitID
ORDER BY Created DESC) AS Notes
FROM ...

|||SNMSDN - you are awesome! THANKS!

max(x) and top n

hello

i need a select than return max of top 10

for example top 10 return

5-9-6-10-78-2

i need max of them

how can i use max(x) in top n?

Try it like this

SELECT MAX(column1)FROM (SELECT TOP 10 column1FROM myTableORDER BY column2)AS A

|||

SELECTMAX(colA)AS maxColAFROM(SELECTROW_NUMBER()OVER(ORDERBY colBDESC)AS rownumber, ColAFROM yourTable)AS t1

WHERE rownumber<= 10

max(tag) for pair of matched rows (was "Need help on query")

I've got a table of transactions which are linked up in pairs based on the column 'Ref'. 'F's are the identifiers of each transaction while 'S's points to the 'F' of its matching pair. I need to select all the transactions with the larger 'Tag' for each pair, can someone point me in the right direction? :confused:

Tag Ref Type
-- -- --
1 200 F
1 201 S
2 201 F
2 200 S
3 202 F
3 203 S
4 203 F
4 202 S
5 204 F
5 205 S
6 205 F
6 204 SIs this what you are after?

select t.tag, t.ref, t.type
from t
join (select max(tag)as tag, ref from t
group by ref) b
on t.tag = b.tag
and t.ref = b.ref

giving you the result set

6 205 F
6 204 S
4 203 F
4 202 S
2 201 F
2 200 S|||Yes it is, thanks!

Max(RowId)

do we have max(rowid) kind of stuff in sql server as we have in oracle?
TIAIf you are referring to rowid as a column name in the table, yes. If you are referring to rowid as an internal pointer withing the table, no. The structure for the function is the same ... select max(<column_name>) from <table_name>|||If you are referring to rowid as a column name in the table, yes. If you are referring to rowid as an internal pointer withing the table, no. The structure for the function is the same ... select max(<column_name>) from <table_name>

Hi tomh53,

How can I get rowid in SQL Server tables...

I tried,

SELECT rowid
FROM tableName

but it doesn't work...|||How can I get rowid in SQL Server tables...you cannot, there is no such concept

:)|||you cannot, there is no such concept

:)
Rudy ... don't you just love it when they don't take the time to read the full answer ...RTFA !!|||John and Jay,

I am sure there is a way to do what you want to do in SQL Server, though it may require a different approach than in Oracle.

Explain your requirements (why do you need RowID?) and someone on the forum can help you.|||Here is a way to generate rowid

Select RowId=(Select count(*) from table where Field<=T.Field),* from Table T|||I think, I agree with r937...

I wanted it for table where there is no primary key... and wanted to select perticular record, for example,

FirstName LastName
--- ---
abc xyz
def jop
def jop

Now If I want to delete second row, how do i do it in SQL Server 2000?? I couldn;t find anyway to do that... I know it's really bad database design.. but what if you want to do it. :)|||Hi jay82

Try this

alter table TableName ADD id int identity
go
select * from TableName
go
Delete from TableName where id not in (select max(id) from TableName group by FirstName,LastName)
go
alter table TableName drop column id
go
select * from TableName|||Hi Madhivanan,

I am sure that will work.. In oracle we have concept of rowid which is really helpful..

Thanks :)|||The idea of rowid was necessary in Oracle to work around some of Oracle's other limitations. Rowid actually warps the thinking of most Oracle users so that they unconsciously code around those limitations, and come to think of rowid as a feature instead of a work-around.

The relational algebra gurus go ballistic when the concept of rowid gets mentioned, regardless of what you call it. The whole concept is flawed from a ra standpoint, because it artificially introduces an order that just isn't there in the underlying data.

I tend to disdain rowid because it truly does warp the mindset. It becomes a crutch that is so comfortable that the average user will never willingly let it go, even once they see the benefits of doing without it.

-PatP|||If the table you are dealing with is large, you may want to go a bit more of a roundabout (but probably more efficient) way.

Select the duplicate rows into a temp table

Delete the duplicates from the original table (yes, this leaves you with no records valid or duplicate)

Select distinct rows back into the original table from your temp table.

Just be careful with those deletes, and keep those backups handy.|||He hasn't said anything about wanting to delete duplicates...|||Sorry. I confused post #8 with the original poster.|||The physical order of data in a database has no meaning...even in Oracle..

Such that if you "hold on" to a rowid to use it to identify a row, and there is data that's inserted, updated and deleted, and then the data is reorganized, that rowid may well point to a different row

You're much better off creating a natural key, or (oh good lord) an IDENTITY Column that will always be assoicated with that row.

MOO

max(keyfield)

I forget, does SQLServer have an internal optimization, such that if
you have a (clustered) index on fields A and B, and you do a select
where max(A) = 'X', SQLServer does NOT have to do a scan to figure out
the max?
Thanks.
J.
A clustered index is an index but with the data at the leaf level. So if A
is the only or first column in the index (clustered or not) it can determine
what the max is with a seek and not a scan.
Andrew J. Kelly SQL MVP
"jxstern" <jxstern@.nowhere.com> wrote in message
news:pouhp01v0onje4a3tgserrtb1ijr8bljd7@.4ax.com...
>I forget, does SQLServer have an internal optimization, such that if
> you have a (clustered) index on fields A and B, and you do a select
> where max(A) = 'X', SQLServer does NOT have to do a scan to figure out
> the max?
> Thanks.
> J.
>
|||In addition to Andrew's points, I have a question. What kind of query is
[vbcol=seagreen]
? Do you expect this to return a row? How? MAX() is an aggregate
function that works on all rows, while WHERE is a clause that works on
individual rows. Maybe you can show us a more practical query ...
http://www.aspfaq.com/
(Reverse address to reply.)
|||On Mon, 15 Nov 2004 14:33:57 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>In addition to Andrew's points, I have a question. What kind of query is
>
>? Do you expect this to return a row? How? MAX() is an aggregate
>function that works on all rows, while WHERE is a clause that works on
>individual rows. Maybe you can show us a more practical query ...
OK, that was a little terse, try something like:
select name
from mytable
where trxdate = (select max(trxdate) from mytable)
So a seek is better than a scan, anyway, and it can do this even for a
clustered index because the top index page has a last row. I just
wondered if it might do even better and cache the high value, but the
important thing is that you confirm it at least an avoid the full
scan. Though, now that I look at my real code again, I suspect it's
going to scan, anyway, oh well, at least I can improve my education
from this example.
Thanks.
J.
|||> So a seek is better than a scan, anyway, and it can do this even for a
> clustered index because the top index page has a last row. I just
> wondered if it might do even better and cache the high value,
No, I don't think individual column values can be cached the way you are
describing.
However, for your education, wouldn't it be relatively simple to set up a
simple test?
CREATE TABLE Kerplunk
(
foo INT,
bar INT,
raboof INT
)
GO
CREATE CLUSTERED INDEX f ON Kerplunk(foo)
CREATE INDEX f ON Kerplunk(foo)
GO
SET NOCOUNT ON
-- <<< populate data here! >>>
-- hit Ctrl+K to see execution plan
-- it will show where scans/seeks are used...
SELECT * FROM Kerplunk WHERE foo = (SELECT MAX(foo) FROM Kerplunk)
SELECT * FROM Kerplunk WHERE bar = (SELECT MAX(bar) FROM Kerplunk)
SELECT * FROM Kerplunk WHERE raboof = (SELECT MAX(raboof) FROM Kerplunk)
GO
DROP TABLE Kerplunk
GO
|||On Mon, 15 Nov 2004 15:18:06 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>No, I don't think individual column values can be cached the way you are
>describing.
Just the high and low.

>However, for your education, wouldn't it be relatively simple to set up a
>simple test?
Well, yeah, I guess, but sometimes it feels more productive to ask
than to do the reverse engineering, and some happy lurker might
benefit thereby.

>CREATE TABLE Kerplunk
>(
> foo INT,
> bar INT,
> raboof INT
>)
>GO
>CREATE CLUSTERED INDEX f ON Kerplunk(foo)
>CREATE INDEX f ON Kerplunk(foo)
on (bar), I suppose you meant.

>GO
>SET NOCOUNT ON
>-- <<< populate data here! >>>
>-- hit Ctrl+K to see execution plan
>-- it will show where scans/seeks are used...
>SELECT * FROM Kerplunk WHERE foo = (SELECT MAX(foo) FROM Kerplunk)
>SELECT * FROM Kerplunk WHERE bar = (SELECT MAX(bar) FROM Kerplunk)
>SELECT * FROM Kerplunk WHERE raboof = (SELECT MAX(raboof) FROM Kerplunk)
>GO
>DROP TABLE Kerplunk
>GO
|||> Just the high and low.
My statement remains as is.

> Well, yeah, I guess, but sometimes it feels more productive to ask
> than to do the reverse engineering, and some happy lurker might
> benefit thereby.
Perhaps, but unless we know every single detail about your environment,
you're only going to get educated guesses.

> on (bar), I suppose you meant.
Yes, I did.
|||On Mon, 15 Nov 2004 17:41:19 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>My statement remains as is.
>
>Perhaps, but unless we know every single detail about your environment,
>you're only going to get educated guesses.
>
>Yes, I did.
CREATE TABLE Kerplunk
(
foo INT,
bar INT,
raboof INT
)
GO
CREATE CLUSTERED INDEX f ON Kerplunk(foo)
CREATE INDEX b ON Kerplunk(bar)
GO
-- <<< populate data here! >>>
SET NOCOUNT ON
declare @.ix int
set @.ix = 0
while @.ix < 100000
begin
insert into Kerplunk values (@.ix, @.ix, @.ix)
set @.ix = @.ix + 1
end
-- hit Ctrl+K to see execution plan
-- it will show where scans/seeks are used...
SELECT * FROM Kerplunk WHERE foo = (SELECT MAX(foo) FROM Kerplunk)
-- .00640
SELECT * FROM Kerplunk WHERE bar = (SELECT MAX(bar) FROM Kerplunk)
-- .00650
SELECT * FROM Kerplunk WHERE raboof = (SELECT MAX(raboof) FROM
Kerplunk)
-- 1.13
GO
DROP TABLE Kerplunk
GO

Saturday, February 25, 2012

Maximize Sql Server 2005 Memory.

Maximize Sql Server 2005 Memory.

Maximize memory usage on active-active sql cluster

Maximize Data Throughput for Network Application

Maximize Data Throughput for Network Application

Maximize Data Throughput for Network Application

MAXFILE Size won't Change

MAXERRORS in BCP Does'nt work

Maxdop hint

maxdop 1 doesn't seem to work inside batch

Maxdate in table data (date range)

MAX_LENGTH Field does not match the value displayed?

max_errors in bcp

Max/Min

Max. Ram addressed by SQL Server

Max. Ram addressed by SQL Server

Max. Ram addressed by SQL Server

Max. number of databases

Max. nodes per cluster

Max. nodes per cluster

Max. nodes per cluster

Max. No. of Columns in a Table

Max. memory limit for SQL Standard edition.

Max. length of a table in SQL 2000

MAX, subquery, TOP or something else?

CODE

max(x) and top n

max(tag) for pair of matched rows (was "Need help on query")

Max(RowId)

max(keyfield)

MAX and DATEDIFF

Blog Archive

About Me