An aggregate function for most/last frequent?

Is there any function to do something like:
SELECT MOSTFREQUENT(MyCol) FROM MyTable
so that if MyCol had the vals:
'A'
'A'
'B'
'A'
'C'
'C'
'B'
'A'
the result would be 'A'
as opposed to having to do something (roughly) like:
SELECT MyCol FROM MyTable WHERE MyCol =
(SELECT MyCol FROM (
SELECT TOP 1 MyCol, COUNT(1) FROM MyTable ORDER BY COUNT(1) DESC
))In t-SQL, try:
SELECT TOP 1 col
FROM tbl
GROUP BY col
ORDER BY COUNT( col ) DESC ;
Anith|||Try,
select top 1 with ties mycol
from mytable
group by mycolumn
order by count(*) desc
AMB
"Arthur Dent" wrote:

> Is there any function to do something like:
> SELECT MOSTFREQUENT(MyCol) FROM MyTable
> so that if MyCol had the vals:
> 'A'
> 'A'
> 'B'
> 'A'
> 'C'
> 'C'
> 'B'
> 'A'
> the result would be 'A'
> as opposed to having to do something (roughly) like:
> SELECT MyCol FROM MyTable WHERE MyCol =
> (SELECT MyCol FROM (
> SELECT TOP 1 MyCol, COUNT(1) FROM MyTable ORDER BY COUNT(1) DESC
> ))
>
>|||That couldn't really be a SQL aggregate function because it's a set rather
than a scalar value - there could be more than one value of equal frequency.
David Portas
SQL Server MVP
--|||That works, and is pretty clean enough...
Thanks!
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uoUQFAenFHA.3312@.tk2msftngp13.phx.gbl...
> In t-SQL, try:
> SELECT TOP 1 col
> FROM tbl
> GROUP BY col
> ORDER BY COUNT( col ) DESC ;
> --
> Anith
>|||Here is a version with the new CTE syntax:
WITH Histogram (mycol, tally)
AS (SELECT mycol, COUNT(*)
FROM Foobar
GROUP BY mycol)
SELECT mycol
FROM Histogram
WHERE tally = (SELECT MAX(tally) FROM Histogram);|||Very good point... i hadnt thought of that.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:b_adnZ2dnZ1yEcKgnZ2dnQzGZ9-dnZ2dRVn-yZ2dnZ0@.giganews.com...
> That couldn't really be a SQL aggregate function because it's a set rather
> than a scalar value - there could be more than one value of equal
> frequency.
> --
> David Portas
> SQL Server MVP
> --
>|||Watch out for the solutions based on the TOP modifier because the results
may be non-deterministic. Unless you use the WITH TIES option or specify a
sort order that is unique you will just get some unpredictable "top" value
returned when there are duplicate values.
David Portas
SQL Server MVP
--

An additional SQL 2005 instance - How much extra resources does it consume ?

Dear all,
In order to migrate my existing SQL 2000 databases to SQL 2005, I want to
install an additional SQL 2005 instance, how much extra computer resources,
eg. CPU, memory, ... will it consume ?
Do you have any ideal ?
Or where can I get this information ?
Please tell me if you know, thanks a lot.It depends on how much heavily you are gonna use it. I can at least say
that, every connection to SQL Server 2005 will consume 28KB if I'm
remembering correctly :)
Other stuff is environment-specific. How densely you use your databases,
your queries...
--
Ekrem Önsoy
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:470a4545$1@.127.0.0.1...
> Dear all,
> In order to migrate my existing SQL 2000 databases to SQL 2005, I want to
> install an additional SQL 2005 instance, how much extra computer
> resources,
> eg. CPU, memory, ... will it consume ?
> Do you have any ideal ?
> Or where can I get this information ?
> Please tell me if you know, thanks a lot.
>|||Thanks, I will migrate my SQL 2000 databases to this additional SQL 2005
instance one by one, so you can see, there will be no change either in the
total no. of databases or database work load.
However, people can still agrue that the server has one more SQL 2005
instance, it might consume a certain amount of resources, so, I have to know
how much resources will the SQL 2005 instance consume.
Or where can I get this information ?
Please tell me if you know.
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:795C55B8-B3D7-4134-8023-9C411C74E363@.microsoft.com...
> It depends on how much heavily you are gonna use it. I can at least say
> that, every connection to SQL Server 2005 will consume 28KB if I'm
> remembering correctly :)
> Other stuff is environment-specific. How densely you use your databases,
> your queries...
> --
> Ekrem Önsoy
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:470a4545$1@.127.0.0.1...
> > Dear all,
> >
> > In order to migrate my existing SQL 2000 databases to SQL 2005, I want
to
> > install an additional SQL 2005 instance, how much extra computer
> > resources,
> > eg. CPU, memory, ... will it consume ?
> >
> > Do you have any ideal ?
> > Or where can I get this information ?
> >
> > Please tell me if you know, thanks a lot.
> >
> >
>|||It is impossible to say. You are in the best position to answer that if you
are migrating existing dbs on the same server you should have an idea
already. Two instances will always use more resources than a single instance
but how it breaks down will be different depends on many factors, none of
which we know anything about. Can't you just upgrade the 2000 instance to
2005 and keep the dbs that require it in 80 compatibility mode?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:470a515c$1@.127.0.0.1...
> Thanks, I will migrate my SQL 2000 databases to this additional SQL 2005
> instance one by one, so you can see, there will be no change either in the
> total no. of databases or database work load.
> However, people can still agrue that the server has one more SQL 2005
> instance, it might consume a certain amount of resources, so, I have to
> know
> how much resources will the SQL 2005 instance consume.
> Or where can I get this information ?
> Please tell me if you know.
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:795C55B8-B3D7-4134-8023-9C411C74E363@.microsoft.com...
>> It depends on how much heavily you are gonna use it. I can at least say
>> that, every connection to SQL Server 2005 will consume 28KB if I'm
>> remembering correctly :)
>> Other stuff is environment-specific. How densely you use your databases,
>> your queries...
>> --
>> Ekrem Önsoy
>>
>> "cpchan" <cpchaney@.netvigator.com> wrote in message
>> news:470a4545$1@.127.0.0.1...
>> > Dear all,
>> >
>> > In order to migrate my existing SQL 2000 databases to SQL 2005, I want
> to
>> > install an additional SQL 2005 instance, how much extra computer
>> > resources,
>> > eg. CPU, memory, ... will it consume ?
>> >
>> > Do you have any ideal ?
>> > Or where can I get this information ?
>> >
>> > Please tell me if you know, thanks a lot.
>> >
>> >
>

An Access OnFormat procedure would be replaced in SQL Reporting Services by what?

In the field, the operators are presented with 5 categories, each category having between 3 and 5 possible selections, for example:

Weather - clear(1), cloudy(2), rain(4) or snow(8).

If the operator selected cloudy and rain, the value of Weather would be 6.

I want to print a report in the office that will list the categories and the available selections for each, putting an "X" next to those selections that were checked by the operators.

In Access, it was easy - I plugged a decode procedure into the OnFormat event...

cnum = obj![WEATHR]
If cnum - 8 >= 0 Then
obj![SNOW] = "X"
cnum = cnum - 8
End If
If cnum - 4 >= 0 Then
obj![RAIN] = "X"
cnum = cnum - 4
End If
If cnum - 2 >= 0 Then
obj![CLOUDY] = "X"
cnum = cnum - 2
End If
If cnum - 1 >= 0 Then
obj![CLEAR] = "X"
End If

Making the change to SQL Reporting Services, I'm clueless...as you can tell, I'm not even sure how to ask the question.

Any help would be greatly appreciated.

There are probably a number of other ways to do this, this is just the first that came to mind.

Add the following on the Code tab of your project (select Report..Report Properties):

Public Function DecodeWeather(ByVal Weather As Integer, ByVal WeatherType As String) As String
DIM cnum AS Integer = Weather
If cnum - 8 >= 0 Then
If WeatherType = "SNOW" Then
Return "X"
Else
cnum = cnum - 8
End If
End If
If cnum - 4 >= 0 Then
If WeatherType = "RAIN" Then
Return "X"
Else
cnum = cnum - 4
End If
End If
If cnum - 2 >= 0 Then
If WeatherType = "CLOUDY" Then
Return "X"
Else
cnum = cnum - 2
End If
End If
If cnum - 1 >= 0 Then
If WeatherType = "CLEAR" Then
Return "X"
End If
End If

End Function

Then add a calculated field to your dataset (on the Layout tab, right click the dataset and choose add) named Snow and add the following to the expression:

=Code.DecodeWeather(Fields!ID.Value, "SNOW")

Repeat for the other fields, passing the appropriate name to the function.

Hope this helps.

|||

Excellent answer!

Thank you.

An Access OnFormat procedure would be replaced in SQL Reporting Services by what?

In the field, the operators are presented with 5 categories, each category having between 3 and 5 possible selections, for example:

Weather - clear(1), cloudy(2), rain(4) or snow(8).

If the operator selected cloudy and rain, the value of Weather would be 6.

I want to print a report in the office that will list the categories and the available selections for each, putting an "X" next to those selections that were checked by the operators.

In Access, it was easy - I plugged a decode procedure into the OnFormat event...

cnum = obj![WEATHR]
If cnum - 8 >= 0 Then
obj![SNOW] = "X"
cnum = cnum - 8
End If
If cnum - 4 >= 0 Then
obj![RAIN] = "X"
cnum = cnum - 4
End If
If cnum - 2 >= 0 Then
obj![CLOUDY] = "X"
cnum = cnum - 2
End If
If cnum - 1 >= 0 Then
obj![CLEAR] = "X"
End If

Making the change to SQL Reporting Services, I'm clueless...as you can tell, I'm not even sure how to ask the question.

Any help would be greatly appreciated.

There are probably a number of other ways to do this, this is just the first that came to mind.

Add the following on the Code tab of your project (select Report..Report Properties):

Public Function DecodeWeather(ByVal Weather As Integer, ByVal WeatherType As String) As String
DIM cnum AS Integer = Weather
If cnum - 8 >= 0 Then
If WeatherType = "SNOW" Then
Return "X"
Else
cnum = cnum - 8
End If
End If
If cnum - 4 >= 0 Then
If WeatherType = "RAIN" Then
Return "X"
Else
cnum = cnum - 4
End If
End If
If cnum - 2 >= 0 Then
If WeatherType = "CLOUDY" Then
Return "X"
Else
cnum = cnum - 2
End If
End If
If cnum - 1 >= 0 Then
If WeatherType = "CLEAR" Then
Return "X"
End If
End If

End Function

Then add a calculated field to your dataset (on the Layout tab, right click the dataset and choose add) named Snow and add the following to the expression:

=Code.DecodeWeather(Fields!ID.Value, "SNOW")

Repeat for the other fields, passing the appropriate name to the function.

Hope this helps.

|||

Excellent answer!

Thank you.

An "EXISTS" Problem

Hey, guys,
Below are my DDL,
CREATE TABLE [dbo].[test1] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[A] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
)
CREATE TABLE [dbo].[test2] (
[A] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
)
test1 table
id A B
--
1 c 1
2 c 2
3 c 3
4 c 4
6 b
9 d
10 e
test2 table
A B
--
c 1
b 1
b 2
d 1
result table
id A B
--
2 c 2
3 c 3
4 c 4
6 b
9 d
Here is my sql to get the result table
SELECT P.id,P.A,P.B
FROM test1 P left outer join test2 R on P.A=R.A
WHERE (NOT EXISTS
(SELECT *
FROM test2 Q
WHERE P.A = Q.A AND P.A + P.B = Q.A + Q.B))
and R.A is not null
group by P.id,P.A,P.B
Can this SQL command be neater?
thanks a lot.
AllenHere are a couple of other methods, although 'cleaner' is a bit subjective'.
Personally, I prefer the NOT EXISTS technique over LEFT JOIN.
INSERT INTO test1 VALUES(1,'c',1)
INSERT INTO test1 VALUES(2,'c',2)
INSERT INTO test1 VALUES(3,'c',3)
INSERT INTO test1 VALUES(4,'c',4)
INSERT INTO test1 VALUES(6,'b', NULL)
INSERT INTO test1 VALUES(9,'d', NULL)
INSERT INTO test1 VALUES(10,'e', NULL)
GO
INSERT INTO test2 VALUES('c', 1)
INSERT INTO test2 VALUES('b', 1)
INSERT INTO test2 VALUES('b', 2)
INSERT INTO test2 VALUES('d', 1)
GO
SELECT P.id, P.A, P.B
FROM test1 P
JOIN test2 R ON P.A = R.A
WHERE NOT EXISTS
(
SELECT *
FROM test2 Q
WHERE
P.A = Q.A AND P.B = Q.B
)
GROUP BY P.id, P.A, P.B
GO
SELECT P.id, P.A, P.B
FROM test1 P
JOIN test2 R ON P.A = R.A
LEFT JOIN test2 Q ON P.A = Q.A AND P.B = Q.B
WHERE Q.A IS NULL
GROUP BY P.id, P.A, P.B
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Allen" <cpchen@.cht.com.tw> wrote in message
news:u19bQ77tDHA.2408@.tk2msftngp13.phx.gbl...
> Hey, guys,
> Below are my DDL,
> CREATE TABLE [dbo].[test1] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [A] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
> )
> CREATE TABLE [dbo].[test2] (
> [A] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
> )
> test1 table
> id A B
> --
> 1 c 1
> 2 c 2
> 3 c 3
> 4 c 4
> 6 b
> 9 d
> 10 e
> test2 table
> A B
> --
> c 1
> b 1
> b 2
> d 1
>
> result table
> id A B
> --
> 2 c 2
> 3 c 3
> 4 c 4
> 6 b
> 9 d
> Here is my sql to get the result table
> SELECT P.id,P.A,P.B
> FROM test1 P left outer join test2 R on P.A=R.A
> WHERE (NOT EXISTS
> (SELECT *
> FROM test2 Q
> WHERE P.A = Q.A AND P.A + P.B = Q.A + Q.B))
> and R.A is not null
> group by P.id,P.A,P.B
>
> Can this SQL command be neater?
>
> thanks a lot.
> Allen
>

Ampersand in email subscription report parameter

Hello everyone,

I'm trying to set up e-mail delivery report subscriptions. When I enter report parameters, it accepts the standard MDX syntax (ex. [Time].[All Times].[2006]), but when I try to access a member by it's key:

[Time].&[2006]

It raises the following error:

Default value or value provided for the report parameter 'pTime' is not a valid value. (rsInvalidReportParameter)

They seem to have the same problem here (see last paragraphs of the article):

http://www.microsoft.com/technet/prodtechnol/sql/2005/smartreports.mspx

Any idea would be very welcome. Thanks!

TGA

I have the same problem, I've tried replacing the ampersand with &amp; and %26 but the parameter is still not excepted.

Ampersand in email subscription report parameter

Hello everyone,

I'm trying to set up e-mail delivery report subscriptions. When I enter report parameters, it accepts the standard MDX syntax (ex. [Time].[All Times].[2006]), but when I try to access a member by it's key:

[Time].&[2006]

It raises the following error:

Default value or value provided for the report parameter 'pTime' is not a valid value. (rsInvalidReportParameter)

They seem to have the same problem here (see last paragraphs of the article):

http://www.microsoft.com/technet/prodtechnol/sql/2005/smartreports.mspx

Any idea would be very welcome. Thanks!

TGA

I have the same problem, I've tried replacing the ampersand with &amp; and %26 but the parameter is still not excepted.

Ampersand in Analysis Services Report Alert

I have an alert in AS 2005 that will start a reporting services report. I want to pass the current member position in a hierarchy using a parameter:

settostr({[Dim].[MyHierarchy].currentmember}) .

That causes a URL like this:

http://hosting/reportserver/?%2fReport%20Project6/Report1&DimCurrentMember={[Dim].[MyHierarchy].[Lvl1].&[all].&[west].&[300]}&rs:Command=Render&rs:Renderer=PDF

How do I get AS to use %26 for the &'s .

In order to deal with this scenario, you can use MDX function UrlEscapeFragment. So your code will become:

UrlEscapeFragment(settostr({[Dim].[MyHierarchy].currentmember}) )

|||

Thank you!

I have been searching all over for this solution.

|||Yes - this is not very documented, except for the fact that the sample AdventureWorks cube uses this function in the "Sales Reason Comparisons" report action.

Ampersand in Analysis Services Report Alert

I have an alert in AS 2005 that will start a reporting services report. I want to pass the current member position in a hierarchy using a parameter:

settostr({[Dim].[MyHierarchy].currentmember}) .

That causes a URL like this:

http://hosting/reportserver/?%2fReport%20Project6/Report1&DimCurrentMember={[Dim].[MyHierarchy].[Lvl1].&[all].&[west].&[300]}&rs:Command=Render&rs:Renderer=PDF

How do I get AS to use %26 for the &'s .

In order to deal with this scenario, you can use MDX function UrlEscapeFragment. So your code will become:

UrlEscapeFragment(settostr({[Dim].[MyHierarchy].currentmember}) )

|||

Thank you!

I have been searching all over for this solution.

|||Yes - this is not very documented, except for the fact that the sample AdventureWorks cube uses this function in the "Sales Reason Comparisons" report action.

amount of transactions in a period of time on sql server 2000

Is there a native tool (profile,trace,performance) feature I can use to determine the amount of transactions that occur throughout the day? Or is there a system table that keeps track of this(would be preferable .. less strain on the system)?

I assume figuring out the transaction in a certain period will enable me to calculate the busiest periods...I need to know the busiest period of the day...how do I do this without putting an additional strain on the server (can I use a different machine other than the server to save a trace) ...I need to determine strain on (processor,memory, and disk).

I also need to get a count on the largest number of users (running transactions) on the server simultaneously.

Any help/advice would be deeply appreciated

You need to run profiler for this purpose... there is no system table as such which stores all the transactions... What u can do is.. run profiler and store it as table and query the table as u want... Be sure what all are the data u need to capture... Profiler can cause performance degradation.... Capture only the required data by selecting proper column filter and event

Madhu

|||

Running PROFILER on full day is not a good thing if you still have performance problems, lately.

http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm - automating such trace.

Refer:
select count(*) as TotalConnections from master..sysprocesses
go

select substring(db_name(dbid),1,30) as DB_name ,count(*) as Connection
from master..sysprocesses
group by substring(db_name(dbid),1,30)

amount of Records SQL 2005 can handle

Hi.
What are the limitations of SQL 2005? I mean the number of tables,
relationships, mappings within the relationships and number of
records?
we have a customer with 64 bit AMD server used as the CRM databse
server.However, the database server is performing absolutely bad in
the recent days.
The CRM server is 32 bit.
Questions:
1- is there a problem of the read/write/create of 64 bit and 32 bit
server entegration?
2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
3- What are the limitations? I mean if there are about 300 tables,
total of 900 relationships in the database and about 3 million
records, would it do bad to the system?
ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
is used by SQL where the CPU is about 90%. the system is running at
more than 60% CPU and a few times a day it reaches 100% and the system
cannot work.
Check out "Maximum Capacity Specifications" in the BOL. However, this
sounds more like an application issue. You may want to use the profiler to
find out which queries are giving you grief and troubleshoot those.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181395208.873615.83570@.n4g2000hsb.googlegrou ps.com...
Hi.
What are the limitations of SQL 2005? I mean the number of tables,
relationships, mappings within the relationships and number of
records?
we have a customer with 64 bit AMD server used as the CRM databse
server.However, the database server is performing absolutely bad in
the recent days.
The CRM server is 32 bit.
Questions:
1- is there a problem of the read/write/create of 64 bit and 32 bit
server entegration?
2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
3- What are the limitations? I mean if there are about 300 tables,
total of 900 relationships in the database and about 3 million
records, would it do bad to the system?
ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
is used by SQL where the CPU is about 90%. the system is running at
more than 60% CPU and a few times a day it reaches 100% and the system
cannot work.
|||Hi
"aduvv" wrote:

> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
>
As well as Tom's comments..
It is not clear if SQL Server is the 32 bit edition or 64 bit version on
your system. It is obviously better for a 64bit OS to run a 64 bit version of
SQL Server. Use the query SELECT @.@.VERSION to determine this.
John
|||what's appends at the disk level? do you see high activity?
do you have other activities at the same time? (like backup, or other
scheduled tasks)
do you update your statistics at a regular basis? and/or defrag the indexes?
what is the disk subsystem?
how many disks are dedicated for the data files, how many for the log files
and how many for tempdb?
have you identify the queries which cause the issue?
for your questions:
1. there is no issue, I have some x64 and x32 servers which works fine in
any scenario
2. AMD cpu provides excellent performance for SQL Server
3. check the BOL for the limits, but I think you are far away from the
maximum capacity. We have a database with 2000 tables. and the biggest
tables contains around 10 million of rows.
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181395208.873615.83570@.n4g2000hsb.googlegrou ps.com...
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
>
|||Hello
As someone else on here has mentioned, run the SQL profiling tools,
SQL is very efficient at adding / view data. The problems start to
occur with large tables that have no indexes, requiring SQL server to
start scanning tables every time it requests a record.
Missing Indexes will exponentially slow down a database and so will
poorly written code that doesn't take advantage of SQL server
features.
The databases I work with are in the hundreds of Gigabytes, with
tables also in the 10's millions rows, constantly being added and the
performance is fast on very standard hardware (dual core, 4 gb ram
etc) - the only times I see SQL become very busy is when the code
talking to SQL is inefficient :-)
The actual limit for SQL 64 is so huge you won't get there in the next
5 years :-) and if you search google for SQL VLDB information you can
find SQL scales to very large organisations / data warehouses.
On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
|||Hi. Thank you for the comments.
As in our case, the Database server is 64 bit and SQL is 64 bit as
well. I forgot to mention is before.
The Indexes are working fine as well. The ndexing job is working
every night and putting things in order.
The problem is in CRM actually. When people try to make a search on
CRM they wait for a serious amount of time.
When the query is run from the databse, it takes nearly no time, less
than 1 second.
but when CRM started to slow down, there was no reason. The only
difference is that we were migrating data into the system.
2 million rows have been migrated in 1 night I think. can this have an
effect?
When the data migration started, system started to slow down. Can you
make a comment on tthis?
On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
<mlbro...@.googlemail.com> wrote:[vbcol=seagreen]
> Hello
> As someone else on here has mentioned, run the SQL profiling tools,
> SQL is very efficient at adding / view data. The problems start to
> occur with large tables that have no indexes, requiring SQL server to
> start scanning tables every time it requests a record.
> Missing Indexes will exponentially slow down a database and so will
> poorly written code that doesn't take advantage of SQL server
> features.
> The databases I work with are in the hundreds of Gigabytes, with
> tables also in the 10's millions rows, constantly being added and the
> performance is fast on very standard hardware (dual core, 4 gb ram
> etc) - the only times I see SQL become very busy is when the code
> talking to SQL is inefficient :-)
> The actual limit for SQL 64 is so huge you won't get there in the next
> 5 years :-) and if you search google for SQL VLDB information you can
> find SQL scales to very large organisations / data warehouses.
> On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
>
>
>
|||It could be that you are using a bulk insert and have used the TABLELOCK
option, which - as its name implies - locks the table. Thus, your users
can't get at it. This is an application design problem, not a SQL Server
problem.
Again, you may want to use the profiler to localize the problem.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181495227.503420.169040@.w5g2000hsg.googlegro ups.com...
Hi. Thank you for the comments.
As in our case, the Database server is 64 bit and SQL is 64 bit as
well. I forgot to mention is before.
The Indexes are working fine as well. The ndexing job is working
every night and putting things in order.
The problem is in CRM actually. When people try to make a search on
CRM they wait for a serious amount of time.
When the query is run from the databse, it takes nearly no time, less
than 1 second.
but when CRM started to slow down, there was no reason. The only
difference is that we were migrating data into the system.
2 million rows have been migrated in 1 night I think. can this have an
effect?
When the data migration started, system started to slow down. Can you
make a comment on tthis?
On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
<mlbro...@.googlemail.com> wrote:[vbcol=seagreen]
> Hello
> As someone else on here has mentioned, run the SQL profiling tools,
> SQL is very efficient at adding / view data. The problems start to
> occur with large tables that have no indexes, requiring SQL server to
> start scanning tables every time it requests a record.
> Missing Indexes will exponentially slow down a database and so will
> poorly written code that doesn't take advantage of SQL server
> features.
> The databases I work with are in the hundreds of Gigabytes, with
> tables also in the 10's millions rows, constantly being added and the
> performance is fast on very standard hardware (dual core, 4 gb ram
> etc) - the only times I see SQL become very busy is when the code
> talking to SQL is inefficient :-)
> The actual limit for SQL 64 is so huge you won't get there in the next
> 5 years :-) and if you search google for SQL VLDB information you can
> find SQL scales to very large organisations / data warehouses.
> On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
>
>
>
|||so the issue appear during the insert of the 2 millions of records.
how do you migrate the data?
do you use the bulk insert method or row by row insert?
your users probably suffer locking issue.
what is the disk system? have setup your log files on a dedicated set of
disks?
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181495227.503420.169040@.w5g2000hsg.googlegro ups.com...
> Hi. Thank you for the comments.
> As in our case, the Database server is 64 bit and SQL is 64 bit as
> well. I forgot to mention is before.
> The Indexes are working fine as well. The ndexing job is working
> every night and putting things in order.
> The problem is in CRM actually. When people try to make a search on
> CRM they wait for a serious amount of time.
> When the query is run from the databse, it takes nearly no time, less
> than 1 second.
> but when CRM started to slow down, there was no reason. The only
> difference is that we were migrating data into the system.
> 2 million rows have been migrated in 1 night I think. can this have an
> effect?
> When the data migration started, system started to slow down. Can you
> make a comment on tthis?
> On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
> <mlbro...@.googlemail.com> wrote:
>
>
>
|||On Jun 9, 6:20 am, aduvv <erdemerdem1...@.gmail.com> wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
How to see the SQL SERVER status: http://www.sqlhacks.com/faqs/simple_monitoring
SELECT @.@.CONNECTIONS AS 'Connections', @.@.CPU_BUSY AS '% usage',
@.@.ERROR AS 'Error',
@.@.IO_BUSY AS 'I/O', @.@.LANGUAGE AS 'Language', @.@.LOCK_TIMEOUT AS 'Lock
timeout',
@.@.MAX_CONNECTIONS AS 'Max Connections', @.@.MAX_PRECISION AS
'Precision',
@.@.PACK_RECEIVED AS 'Packet received', @.@.PACK_SENT AS 'Packets Sent',
@.@.PACKET_ERRORS AS 'Packet Errors', @.@.SERVERNAME AS 'Server',
@.@.SERVICENAME AS 'Services', @.@.TOTAL_ERRORS AS 'Errors',
@.@.TOTAL_READ AS 'Reads', @.@.TOTAL_WRITE AS 'Writes', @.@.VERSION AS
'Version';
This includes samples and explanations on how to do it.
Also new this week:
SQL Server index performance
SQL Server - optimization:index performance
How to group items into a fixed number of bucket with MS SQL Server
How to have a simple server monitoring in MS SQL Server
What's the current version of MS SQL Server used?
What are all the triggers used in a database - Formatting syv
What are all the views in a database in MS SQL Server?
What are all the stored procedures in a database in MS SQL Server? -
Formatting syv
What's the structure of a table with MS SQL Server?

amount of Records SQL 2005 can handle

Hi.
What are the limitations of SQL 2005? I mean the number of tables,
relationships, mappings within the relationships and number of
records?
we have a customer with 64 bit AMD server used as the CRM databse
server.However, the database server is performing absolutely bad in
the recent days.
The CRM server is 32 bit.
Questions:
1- is there a problem of the read/write/create of 64 bit and 32 bit
server entegration?
2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
3- What are the limitations? I mean if there are about 300 tables,
total of 900 relationships in the database and about 3 million
records, would it do bad to the system?
ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
is used by SQL where the CPU is about 90%. the system is running at
more than 60% CPU and a few times a day it reaches 100% and the system
cannot work.Check out "Maximum Capacity Specifications" in the BOL. However, this
sounds more like an application issue. You may want to use the profiler to
find out which queries are giving you grief and troubleshoot those.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181395208.873615.83570@.n4g2000hsb.googlegroups.com...
Hi.
What are the limitations of SQL 2005? I mean the number of tables,
relationships, mappings within the relationships and number of
records?
we have a customer with 64 bit AMD server used as the CRM databse
server.However, the database server is performing absolutely bad in
the recent days.
The CRM server is 32 bit.
Questions:
1- is there a problem of the read/write/create of 64 bit and 32 bit
server entegration?
2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
3- What are the limitations? I mean if there are about 300 tables,
total of 900 relationships in the database and about 3 million
records, would it do bad to the system?
ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
is used by SQL where the CPU is about 90%. the system is running at
more than 60% CPU and a few times a day it reaches 100% and the system
cannot work.|||Hi
"aduvv" wrote:

> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
>
As well as Tom's comments..
It is not clear if SQL Server is the 32 bit edition or 64 bit version on
your system. It is obviously better for a 64bit OS to run a 64 bit version o
f
SQL Server. Use the query SELECT @.@.VERSION to determine this.
John|||what's appends at the disk level? do you see high activity?
do you have other activities at the same time? (like backup, or other
scheduled tasks)
do you update your statistics at a regular basis? and/or defrag the indexes?
what is the disk subsystem?
how many disks are dedicated for the data files, how many for the log files
and how many for tempdb?
have you identify the queries which cause the issue?
for your questions:
1. there is no issue, I have some x64 and x32 servers which works fine in
any scenario
2. AMD cpu provides excellent performance for SQL Server
3. check the BOL for the limits, but I think you are far away from the
maximum capacity. We have a database with 2000 tables. and the biggest
tables contains around 10 million of rows.
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181395208.873615.83570@.n4g2000hsb.googlegroups.com...
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
>|||Hello
As someone else on here has mentioned, run the SQL profiling tools,
SQL is very efficient at adding / view data. The problems start to
occur with large tables that have no indexes, requiring SQL server to
start scanning tables every time it requests a record.
Missing Indexes will exponentially slow down a database and so will
poorly written code that doesn't take advantage of SQL server
features.
The databases I work with are in the hundreds of Gigabytes, with
tables also in the 10's millions rows, constantly being added and the
performance is fast on very standard hardware (dual core, 4 gb ram
etc) - the only times I see SQL become very busy is when the code
talking to SQL is inefficient :-)
The actual limit for SQL 64 is so huge you won't get there in the next
5 years :-) and if you search google for SQL VLDB information you can
find SQL scales to very large organisations / data warehouses.
On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.|||Hi. Thank you for the comments.
As in our case, the Database server is 64 bit and SQL is 64 bit as
well. I forgot to mention is before.
The Indexes are working fine as well. The ndexing job is working
every night and putting things in order.
The problem is in CRM actually. When people try to make a search on
CRM they wait for a serious amount of time.
When the query is run from the databse, it takes nearly no time, less
than 1 second.
but when CRM started to slow down, there was no reason. The only
difference is that we were migrating data into the system.
2 million rows have been migrated in 1 night I think. can this have an
effect?
When the data migration started, system started to slow down. Can you
make a comment on tthis?
On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
<mlbro...@.googlemail.com> wrote:[vbcol=seagreen]
> Hello
> As someone else on here has mentioned, run the SQL profiling tools,
> SQL is very efficient at adding / view data. The problems start to
> occur with large tables that have no indexes, requiring SQL server to
> start scanning tables every time it requests a record.
> Missing Indexes will exponentially slow down a database and so will
> poorly written code that doesn't take advantage of SQL server
> features.
> The databases I work with are in the hundreds of Gigabytes, with
> tables also in the 10's millions rows, constantly being added and the
> performance is fast on very standard hardware (dual core, 4 gb ram
> etc) - the only times I see SQL become very busy is when the code
> talking to SQL is inefficient :-)
> The actual limit for SQL 64 is so huge you won't get there in the next
> 5 years :-) and if you search google for SQL VLDB information you can
> find SQL scales to very large organisations / data warehouses.
> On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
>
>
>
>
>
>|||It could be that you are using a bulk insert and have used the TABLELOCK
option, which - as its name implies - locks the table. Thus, your users
can't get at it. This is an application design problem, not a SQL Server
problem.
Again, you may want to use the profiler to localize the problem.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181495227.503420.169040@.w5g2000hsg.googlegroups.com...
Hi. Thank you for the comments.
As in our case, the Database server is 64 bit and SQL is 64 bit as
well. I forgot to mention is before.
The Indexes are working fine as well. The ndexing job is working
every night and putting things in order.
The problem is in CRM actually. When people try to make a search on
CRM they wait for a serious amount of time.
When the query is run from the databse, it takes nearly no time, less
than 1 second.
but when CRM started to slow down, there was no reason. The only
difference is that we were migrating data into the system.
2 million rows have been migrated in 1 night I think. can this have an
effect?
When the data migration started, system started to slow down. Can you
make a comment on tthis?
On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
<mlbro...@.googlemail.com> wrote:[vbcol=seagreen]
> Hello
> As someone else on here has mentioned, run the SQL profiling tools,
> SQL is very efficient at adding / view data. The problems start to
> occur with large tables that have no indexes, requiring SQL server to
> start scanning tables every time it requests a record.
> Missing Indexes will exponentially slow down a database and so will
> poorly written code that doesn't take advantage of SQL server
> features.
> The databases I work with are in the hundreds of Gigabytes, with
> tables also in the 10's millions rows, constantly being added and the
> performance is fast on very standard hardware (dual core, 4 gb ram
> etc) - the only times I see SQL become very busy is when the code
> talking to SQL is inefficient :-)
> The actual limit for SQL 64 is so huge you won't get there in the next
> 5 years :-) and if you search google for SQL VLDB information you can
> find SQL scales to very large organisations / data warehouses.
> On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
>
>
>
>
>
>|||so the issue appear during the insert of the 2 millions of records.
how do you migrate the data?
do you use the bulk insert method or row by row insert?
your users probably suffer locking issue.
what is the disk system? have setup your log files on a dedicated set of
disks?
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181495227.503420.169040@.w5g2000hsg.googlegroups.com...
> Hi. Thank you for the comments.
> As in our case, the Database server is 64 bit and SQL is 64 bit as
> well. I forgot to mention is before.
> The Indexes are working fine as well. The ndexing job is working
> every night and putting things in order.
> The problem is in CRM actually. When people try to make a search on
> CRM they wait for a serious amount of time.
> When the query is run from the databse, it takes nearly no time, less
> than 1 second.
> but when CRM started to slow down, there was no reason. The only
> difference is that we were migrating data into the system.
> 2 million rows have been migrated in 1 night I think. can this have an
> effect?
> When the data migration started, system started to slow down. Can you
> make a comment on tthis?
> On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
> <mlbro...@.googlemail.com> wrote:
>
>
>|||On Jun 9, 6:20 am, aduvv <erdemerdem1...@.gmail.com> wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
How to see the SQL SERVER status: [url]http://www.sqlhacks.com/faqs/simple_monitoring[/
url]
SELECT @.@.CONNECTIONS AS 'Connections', @.@.CPU_BUSY AS '% usage',
@.@.ERROR AS 'Error',
@.@.IO_BUSY AS 'I/O', @.@.LANGUAGE AS 'Language', @.@.LOCK_TIMEOUT AS 'Lock
timeout',
@.@.MAX_CONNECTIONS AS 'Max Connections', @.@.MAX_PRECISION AS
'Precision',
@.@.PACK_RECEIVED AS 'Packet received', @.@.PACK_SENT AS 'Packets Sent',
@.@.PACKET_ERRORS AS 'Packet Errors', @.@.SERVERNAME AS 'Server',
@.@.SERVICENAME AS 'Services', @.@.TOTAL_ERRORS AS 'Errors',
@.@.TOTAL_READ AS 'Reads', @.@.TOTAL_WRITE AS 'Writes', @.@.VERSION AS
'Version';
This includes samples and explanations on how to do it.
Also new this week:
SQL Server index performance
SQL Server - optimization:index performance
How to group items into a fixed number of bucket with MS SQL Server
How to have a simple server monitoring in MS SQL Server
What's the current version of MS SQL Server used?
What are all the triggers used in a database - Formatting syv
What are all the views in a database in MS SQL Server?
What are all the stored procedures in a database in MS SQL Server? -
Formatting syv
What's the structure of a table with MS SQL Server?

amount of Records SQL 2005 can handle

Hi.
What are the limitations of SQL 2005? I mean the number of tables,
relationships, mappings within the relationships and number of
records?
we have a customer with 64 bit AMD server used as the CRM databse
server.However, the database server is performing absolutely bad in
the recent days.
The CRM server is 32 bit.
Questions:
1- is there a problem of the read/write/create of 64 bit and 32 bit
server entegration?
2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
3- What are the limitations? I mean if there are about 300 tables,
total of 900 relationships in the database and about 3 million
records, would it do bad to the system?
ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
is used by SQL where the CPU is about 90%. the system is running at
more than 60% CPU and a few times a day it reaches 100% and the system
cannot work.Check out "Maximum Capacity Specifications" in the BOL. However, this
sounds more like an application issue. You may want to use the profiler to
find out which queries are giving you grief and troubleshoot those.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181395208.873615.83570@.n4g2000hsb.googlegroups.com...
Hi.
What are the limitations of SQL 2005? I mean the number of tables,
relationships, mappings within the relationships and number of
records?
we have a customer with 64 bit AMD server used as the CRM databse
server.However, the database server is performing absolutely bad in
the recent days.
The CRM server is 32 bit.
Questions:
1- is there a problem of the read/write/create of 64 bit and 32 bit
server entegration?
2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
3- What are the limitations? I mean if there are about 300 tables,
total of 900 relationships in the database and about 3 million
records, would it do bad to the system?
ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
is used by SQL where the CPU is about 90%. the system is running at
more than 60% CPU and a few times a day it reaches 100% and the system
cannot work.|||Hi
"aduvv" wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
>
As well as Tom's comments..
It is not clear if SQL Server is the 32 bit edition or 64 bit version on
your system. It is obviously better for a 64bit OS to run a 64 bit version of
SQL Server. Use the query SELECT @.@.VERSION to determine this.
John|||what's appends at the disk level? do you see high activity?
do you have other activities at the same time? (like backup, or other
scheduled tasks)
do you update your statistics at a regular basis? and/or defrag the indexes?
what is the disk subsystem?
how many disks are dedicated for the data files, how many for the log files
and how many for tempdb?
have you identify the queries which cause the issue?
for your questions:
1. there is no issue, I have some x64 and x32 servers which works fine in
any scenario
2. AMD cpu provides excellent performance for SQL Server
3. check the BOL for the limits, but I think you are far away from the
maximum capacity. We have a database with 2000 tables. and the biggest
tables contains around 10 million of rows.
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181395208.873615.83570@.n4g2000hsb.googlegroups.com...
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
>|||Hello
As someone else on here has mentioned, run the SQL profiling tools,
SQL is very efficient at adding / view data. The problems start to
occur with large tables that have no indexes, requiring SQL server to
start scanning tables every time it requests a record.
Missing Indexes will exponentially slow down a database and so will
poorly written code that doesn't take advantage of SQL server
features.
The databases I work with are in the hundreds of Gigabytes, with
tables also in the 10's millions rows, constantly being added and the
performance is fast on very standard hardware (dual core, 4 gb ram
etc) - the only times I see SQL become very busy is when the code
talking to SQL is inefficient :-)
The actual limit for SQL 64 is so huge you won't get there in the next
5 years :-) and if you search google for SQL VLDB information you can
find SQL scales to very large organisations / data warehouses.
On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.|||Hi. Thank you for the comments.
As in our case, the Database server is 64 bit and SQL is 64 bit as
well. I forgot to mention is before.
The Indexes are working fine as well. The ndexing job is working
every night and putting things in order.
The problem is in CRM actually. When people try to make a search on
CRM they wait for a serious amount of time.
When the query is run from the databse, it takes nearly no time, less
than 1 second.
but when CRM started to slow down, there was no reason. The only
difference is that we were migrating data into the system.
2 million rows have been migrated in 1 night I think. can this have an
effect?
When the data migration started, system started to slow down. Can you
make a comment on tthis?
On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
<mlbro...@.googlemail.com> wrote:
> Hello
> As someone else on here has mentioned, run the SQL profiling tools,
> SQL is very efficient at adding / view data. The problems start to
> occur with large tables that have no indexes, requiring SQL server to
> start scanning tables every time it requests a record.
> Missing Indexes will exponentially slow down a database and so will
> poorly written code that doesn't take advantage of SQL server
> features.
> The databases I work with are in the hundreds of Gigabytes, with
> tables also in the 10's millions rows, constantly being added and the
> performance is fast on very standard hardware (dual core, 4 gb ram
> etc) - the only times I see SQL become very busy is when the code
> talking to SQL is inefficient :-)
> The actual limit for SQL 64 is so huge you won't get there in the next
> 5 years :-) and if you search google for SQL VLDB information you can
> find SQL scales to very large organisations / data warehouses.
> On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
> > Hi.
> > What are the limitations of SQL 2005? I mean the number of tables,
> > relationships, mappings within the relationships and number of
> > records?
> > we have a customer with 64 bit AMD server used as the CRM databse
> > server.However, the database server is performing absolutely bad in
> > the recent days.
> > The CRM server is 32 bit.
> > Questions:
> > 1- is there a problem of the read/write/create of 64 bit and 32 bit
> > server entegration?
> > 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> > 3- What are the limitations? I mean if there are about 300 tables,
> > total of 900 relationships in the database and about 3 million
> > records, would it do bad to the system?
> > ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> > is used by SQL where the CPU is about 90%. the system is running at
> > more than 60% CPU and a few times a day it reaches 100% and the system
> > cannot work.|||It could be that you are using a bulk insert and have used the TABLELOCK
option, which - as its name implies - locks the table. Thus, your users
can't get at it. This is an application design problem, not a SQL Server
problem.
Again, you may want to use the profiler to localize the problem.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181495227.503420.169040@.w5g2000hsg.googlegroups.com...
Hi. Thank you for the comments.
As in our case, the Database server is 64 bit and SQL is 64 bit as
well. I forgot to mention is before.
The Indexes are working fine as well. The ndexing job is working
every night and putting things in order.
The problem is in CRM actually. When people try to make a search on
CRM they wait for a serious amount of time.
When the query is run from the databse, it takes nearly no time, less
than 1 second.
but when CRM started to slow down, there was no reason. The only
difference is that we were migrating data into the system.
2 million rows have been migrated in 1 night I think. can this have an
effect?
When the data migration started, system started to slow down. Can you
make a comment on tthis?
On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
<mlbro...@.googlemail.com> wrote:
> Hello
> As someone else on here has mentioned, run the SQL profiling tools,
> SQL is very efficient at adding / view data. The problems start to
> occur with large tables that have no indexes, requiring SQL server to
> start scanning tables every time it requests a record.
> Missing Indexes will exponentially slow down a database and so will
> poorly written code that doesn't take advantage of SQL server
> features.
> The databases I work with are in the hundreds of Gigabytes, with
> tables also in the 10's millions rows, constantly being added and the
> performance is fast on very standard hardware (dual core, 4 gb ram
> etc) - the only times I see SQL become very busy is when the code
> talking to SQL is inefficient :-)
> The actual limit for SQL 64 is so huge you won't get there in the next
> 5 years :-) and if you search google for SQL VLDB information you can
> find SQL scales to very large organisations / data warehouses.
> On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
> > Hi.
> > What are the limitations of SQL 2005? I mean the number of tables,
> > relationships, mappings within the relationships and number of
> > records?
> > we have a customer with 64 bit AMD server used as the CRM databse
> > server.However, the database server is performing absolutely bad in
> > the recent days.
> > The CRM server is 32 bit.
> > Questions:
> > 1- is there a problem of the read/write/create of 64 bit and 32 bit
> > server entegration?
> > 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> > 3- What are the limitations? I mean if there are about 300 tables,
> > total of 900 relationships in the database and about 3 million
> > records, would it do bad to the system?
> > ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> > is used by SQL where the CPU is about 90%. the system is running at
> > more than 60% CPU and a few times a day it reaches 100% and the system
> > cannot work.|||so the issue appear during the insert of the 2 millions of records.
how do you migrate the data?
do you use the bulk insert method or row by row insert?
your users probably suffer locking issue.
what is the disk system? have setup your log files on a dedicated set of
disks?
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181495227.503420.169040@.w5g2000hsg.googlegroups.com...
> Hi. Thank you for the comments.
> As in our case, the Database server is 64 bit and SQL is 64 bit as
> well. I forgot to mention is before.
> The Indexes are working fine as well. The ndexing job is working
> every night and putting things in order.
> The problem is in CRM actually. When people try to make a search on
> CRM they wait for a serious amount of time.
> When the query is run from the databse, it takes nearly no time, less
> than 1 second.
> but when CRM started to slow down, there was no reason. The only
> difference is that we were migrating data into the system.
> 2 million rows have been migrated in 1 night I think. can this have an
> effect?
> When the data migration started, system started to slow down. Can you
> make a comment on tthis?
> On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
> <mlbro...@.googlemail.com> wrote:
>> Hello
>> As someone else on here has mentioned, run the SQL profiling tools,
>> SQL is very efficient at adding / view data. The problems start to
>> occur with large tables that have no indexes, requiring SQL server to
>> start scanning tables every time it requests a record.
>> Missing Indexes will exponentially slow down a database and so will
>> poorly written code that doesn't take advantage of SQL server
>> features.
>> The databases I work with are in the hundreds of Gigabytes, with
>> tables also in the 10's millions rows, constantly being added and the
>> performance is fast on very standard hardware (dual core, 4 gb ram
>> etc) - the only times I see SQL become very busy is when the code
>> talking to SQL is inefficient :-)
>> The actual limit for SQL 64 is so huge you won't get there in the next
>> 5 years :-) and if you search google for SQL VLDB information you can
>> find SQL scales to very large organisations / data warehouses.
>> On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
>> > Hi.
>> > What are the limitations of SQL 2005? I mean the number of tables,
>> > relationships, mappings within the relationships and number of
>> > records?
>> > we have a customer with 64 bit AMD server used as the CRM databse
>> > server.However, the database server is performing absolutely bad in
>> > the recent days.
>> > The CRM server is 32 bit.
>> > Questions:
>> > 1- is there a problem of the read/write/create of 64 bit and 32 bit
>> > server entegration?
>> > 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
>> > 3- What are the limitations? I mean if there are about 300 tables,
>> > total of 900 relationships in the database and about 3 million
>> > records, would it do bad to the system?
>> > ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
>> > is used by SQL where the CPU is about 90%. the system is running at
>> > more than 60% CPU and a few times a day it reaches 100% and the system
>> > cannot work.
>
>
>|||On Jun 9, 6:20 am, aduvv <erdemerdem1...@.gmail.com> wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
How to see the SQL SERVER status: http://www.sqlhacks.com/faqs/simple_monitoring
SELECT @.@.CONNECTIONS AS 'Connections', @.@.CPU_BUSY AS '% usage',
@.@.ERROR AS 'Error',
@.@.IO_BUSY AS 'I/O', @.@.LANGUAGE AS 'Language', @.@.LOCK_TIMEOUT AS 'Lock
timeout',
@.@.MAX_CONNECTIONS AS 'Max Connections', @.@.MAX_PRECISION AS
'Precision',
@.@.PACK_RECEIVED AS 'Packet received', @.@.PACK_SENT AS 'Packets Sent',
@.@.PACKET_ERRORS AS 'Packet Errors', @.@.SERVERNAME AS 'Server',
@.@.SERVICENAME AS 'Services', @.@.TOTAL_ERRORS AS 'Errors',
@.@.TOTAL_READ AS 'Reads', @.@.TOTAL_WRITE AS 'Writes', @.@.VERSION AS
'Version';
This includes samples and explanations on how to do it.
Also new this week:
SQL Server index performance
SQL Server - optimization:index performance
How to group items into a fixed number of bucket with MS SQL Server
How to have a simple server monitoring in MS SQL Server
What's the current version of MS SQL Server used?
What are all the triggers used in a database - Formatting syv
What are all the views in a database in MS SQL Server?
What are all the stored procedures in a database in MS SQL Server? -
Formatting syv
What's the structure of a table with MS SQL Server?

Amount of RAM required ?

We are going to replace the old SQL Server 2000 to a new
Hardware running Windows 2003 Server.
We are planning how much memory should be used. At the
present monent, we are using 2GB RAM and the performance
is OK. We find that the Average RAM and Peak RAM used is
around 2000 MB. According to Network Administrator, it is
because SQL uses all memory available for caching /
performance optimisation.
Is it necessary for us to purchase more RAM for the new
server ? Is it possible for SQL Server 2000 Standard to
access more than 2 GB RAM ?
Thanks
SQL Server standard edition can only access 2GB of ram. However, I would
make sure the operating system and other programs have enough Ram. So
depending on other software buy three or four GB of ram and set SQL Servers
min and max memory to 2GB so it takes it up front.
"Jason" <anonymous@.discussions.microsoft.com> wrote in message
news:0a2601c59bb6$38a863c0$a601280a@.phx.gbl...
> We are going to replace the old SQL Server 2000 to a new
> Hardware running Windows 2003 Server.
> We are planning how much memory should be used. At the
> present monent, we are using 2GB RAM and the performance
> is OK. We find that the Average RAM and Peak RAM used is
> around 2000 MB. According to Network Administrator, it is
> because SQL uses all memory available for caching /
> performance optimisation.
> Is it necessary for us to purchase more RAM for the new
> server ? Is it possible for SQL Server 2000 Standard to
> access more than 2 GB RAM ?
> Thanks
|||did you hear "Danny" <someone@.nowhere.com> say in
news:wtyJe.1283$1S.438@.trnddc04:

> SQL Server standard edition can only access 2GB of ram. However, I
> would make sure the operating system and other programs have enough
> Ram. So depending on other software buy three or four GB of ram and
> set SQL Servers min and max memory to 2GB so it takes it up front.
ditto. I use Standard in the office exclusively (about 18 instances) and
I only put more than 4Gb in the box if I am installing a second instance
of SQL on the same box. If you have extra money to spend, you may want to
consider additional processors. SQL makes very good use of the additional
processors.
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||It's impossible to say whether you need more memory or not without a few
metrics. A couple perfmon counters you should look at are the buffer
cache hit ratio (SQLServer:Buffer Manager | Buffer cache hit ratio) and
the page life expectancy (SQLServer:Buffer Manager | Page life expectancy).
SQL Server will cache data pages that are accessed, so the SQL Server
address space fills up with stuff and will remain filled with stuff
until it needs to free up the memory for another app, the OS or more
recently accessed data pages.
The cache hit ratio will tell you what percentage of your data requests
are satisfied from RAM (should be pretty high, like 99+ percent).
The page life expectancy is how long SQL Server expects a data page to
remain in RAM before needing to be swapped out to disk to free up the
memory for other apps or data. It's measured in seconds and I like to
make sure my servers have pages staying in RAM for at least 5 minutes on
average, preferable an hour or more.
Those two counters, while they won't give you a complete picture of the
memory pressure on a SQL server, will give you a fairly good idea of
whether or not you need to think about increasing physical RAM in a box.
And, yes, SQL Server is capable of managing a good deal more memory than
2G. With different editions, SQL Server 2000 can handle up to 64G
(http://www.microsoft.com/sql/techinf...KChooseEd.mspx).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Jason wrote:

>We are going to replace the old SQL Server 2000 to a new
>Hardware running Windows 2003 Server.
>We are planning how much memory should be used. At the
>present monent, we are using 2GB RAM and the performance
>is OK. We find that the Average RAM and Peak RAM used is
>around 2000 MB. According to Network Administrator, it is
>because SQL uses all memory available for caching /
>performance optimisation.
>Is it necessary for us to purchase more RAM for the new
>server ? Is it possible for SQL Server 2000 Standard to
>access more than 2 GB RAM ?
>Thanks
>
|||Dear Mike,
Thank you for your advice.
I would like to ask 3 questions:
1) Does it mean that each instance can access 2GB. In other words, if there are 3 instances installed, each of them can access 2GB or 6GB in total.
2) RAM Required - We have asked a consultant for Server Virtualization / Consolidation. Their suggestion for RAM usage is 8GB just 2GB + 2GB + 2GB (As we have 3 SQL Servers). However, we know that two of the SQL Servers are under utilized BUT their report tells us that they are using 2GB.
3) CPU Required - They say that we only need a dual CPU server. In the past, one of the SQL Server uses 4 processors while the other 2 are 2 processor servers.
Any suggestion is highly appreciated.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:uRBjwE8mFHA.1968@.TK2MSFTNGP14.phx.gbl...
It's impossible to say whether you need more memory or not without a few metrics. A couple perfmon counters you should look at are the buffer cache hit ratio (SQLServer:Buffer Manager | Buffer cache hit ratio) and the page life expectancy (SQLServer:Buffer Manager | Page life expectancy).
SQL Server will cache data pages that are accessed, so the SQL Server address space fills up with stuff and will remain filled with stuff until it needs to free up the memory for another app, the OS or more recently accessed data pages.
The cache hit ratio will tell you what percentage of your data requests are satisfied from RAM (should be pretty high, like 99+ percent).
The page life expectancy is how long SQL Server expects a data page to remain in RAM before needing to be swapped out to disk to free up the memory for other apps or data. It's measured in seconds and I like to make sure my servers have pages staying in RAM for at least 5 minutes on average, preferable an hour or more.
Those two counters, while they won't give you a complete picture of the memory pressure on a SQL server, will give you a fairly good idea of whether or not you need to think about increasing physical RAM in a box.
And, yes, SQL Server is capable of managing a good deal more memory than 2G. With different editions, SQL Server 2000 can handle up to 64G (http://www.microsoft.com/sql/techinf...KChooseEd.mspx).
mike hodgson
blog: http://sqlnerd.blogspot.com
Jason wrote:
We are going to replace the old SQL Server 2000 to a new
Hardware running Windows 2003 Server.
We are planning how much memory should be used. At the
present monent, we are using 2GB RAM and the performance
is OK. We find that the Average RAM and Peak RAM used is
around 2000 MB. According to Network Administrator, it is
because SQL uses all memory available for caching /
performance optimisation.
Is it necessary for us to purchase more RAM for the new
server ? Is it possible for SQL Server 2000 Standard to
access more than 2 GB RAM ?
Thanks
|||Jason,
1) Does it mean that each instance can access 2GB. In other words, if there are 3 instances installed, each of them can access 2GB or 6GB in total.
SQL Server Standard edition can utilize 2 GB memory for each instance maximum. So 2 GB only will be utilized for each instance. Incase
1 instance is down then 2 GB allocated for that instance will be free.
2) RAM Required - We have asked a consultant for Server Virtualization / Consolidation. Their suggestion for RAM usage is 8GB just 2GB + 2GB + 2GB (As we have 3 SQL Servers). However, we know that two of the SQL Servers are under utilized BUT their report tells us that they are using 2GB.
Looks like you have kept the memory paramter static (Fixed). In this case 2 GB will be reserved to sql server and will not be shared across for other applications.
USE the Perfmon -- Memory -- availableMBytes counter to get the exact value during peak time.
3) CPU Required - They say that we only need a dual CPU server. In the past, one of the SQL Server uses 4 processors while the other 2 are 2 processor servers.
If your application is less processor intensive then you could go for 2 CPU servers. Again run the Perfmon and identify the CPU usage. If the CPU usage is <50% with
2 CPU duing peak then you do not have any CPU issue.
Measure cpu using the counter -- PROCESSOR -- % PROCESSOR TIME.
Thanks
Hari
SQL Server MVP
"Jason" <anonymous@.discussions.microsoft.com> wrote in message news:%23Qip5aAnFHA.3648@.TK2MSFTNGP10.phx.gbl...
Dear Mike,
Thank you for your advice.
I would like to ask 3 questions:
1) Does it mean that each instance can access 2GB. In other words, if there are 3 instances installed, each of them can access 2GB or 6GB in total.
2) RAM Required - We have asked a consultant for Server Virtualization / Consolidation. Their suggestion for RAM usage is 8GB just 2GB + 2GB + 2GB (As we have 3 SQL Servers). However, we know that two of the SQL Servers are under utilized BUT their report tells us that they are using 2GB.
3) CPU Required - They say that we only need a dual CPU server. In the past, one of the SQL Server uses 4 processors while the other 2 are 2 processor servers.
Any suggestion is highly appreciated.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:uRBjwE8mFHA.1968@.TK2MSFTNGP14.phx.gbl...
It's impossible to say whether you need more memory or not without a few metrics. A couple perfmon counters you should look at are the buffer cache hit ratio (SQLServer:Buffer Manager | Buffer cache hit ratio) and the page life expectancy (SQLServer:Buffer Manager | Page life expectancy).
SQL Server will cache data pages that are accessed, so the SQL Server address space fills up with stuff and will remain filled with stuff until it needs to free up the memory for another app, the OS or more recently accessed data pages.
The cache hit ratio will tell you what percentage of your data requests are satisfied from RAM (should be pretty high, like 99+ percent).
The page life expectancy is how long SQL Server expects a data page to remain in RAM before needing to be swapped out to disk to free up the memory for other apps or data. It's measured in seconds and I like to make sure my servers have pages staying in RAM for at least 5 minutes on average, preferable an hour or more.
Those two counters, while they won't give you a complete picture of the memory pressure on a SQL server, will give you a fairly good idea of whether or not you need to think about increasing physical RAM in a box.
And, yes, SQL Server is capable of managing a good deal more memory than 2G. With different editions, SQL Server 2000 can handle up to 64G (http://www.microsoft.com/sql/techinf...KChooseEd.mspx).
mike hodgson
blog: http://sqlnerd.blogspot.com
Jason wrote:
We are going to replace the old SQL Server 2000 to a new
Hardware running Windows 2003 Server.
We are planning how much memory should be used. At the
present monent, we are using 2GB RAM and the performance
is OK. We find that the Average RAM and Peak RAM used is
around 2000 MB. According to Network Administrator, it is
because SQL uses all memory available for caching /
performance optimisation.
Is it necessary for us to purchase more RAM for the new
server ? Is it possible for SQL Server 2000 Standard to
access more than 2 GB RAM ?
Thanks
|||Dear Hari,
Thank you for your advice.
1) I haven't installed more than 1 instance on a SQL Server. It is just for my interest only. I want to confirm I have followed what you mean = Each instance can be allocated 2 GB and if that instance is down, that 2GB will be of no use to other instances ?
2) NO. All of the SQL Servers RAM is dynamically allocated. I am wondering when we get the new Server, maybe we will assign 2.5GB for each Virtual Server. And I can assign 2GB to each SQL Server static. I would like to know if we statically allocate RAM, should I choose Minimum = 2GB and Maximum = 2GB ?
3) My boss just wonders why in the past for each SQL Server, at least 2 CPU are required, now for 3 Virtual Servers, only 2 processors are required. Anyhow, we have to look into the Virtual Server requirement and it is recommended by them. On the other hand, we are now using 20 CALs for each SQL Server, if we reduce the number of CPU of 1 SQL Server from 4 to 2, can we get any saving in licensing ?
Thanks
Jason
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:OyF9IpAnFHA.2852@.TK2MSFTNGP15.phx.gbl...
Jason,
1) Does it mean that each instance can access 2GB. In other words, if there are 3 instances installed, each of them can access 2GB or 6GB in total.
SQL Server Standard edition can utilize 2 GB memory for each instance maximum. So 2 GB only will be utilized for each instance. Incase
1 instance is down then 2 GB allocated for that instance will be free.
2) RAM Required - We have asked a consultant for Server Virtualization / Consolidation. Their suggestion for RAM usage is 8GB just 2GB + 2GB + 2GB (As we have 3 SQL Servers). However, we know that two of the SQL Servers are under utilized BUT their report tells us that they are using 2GB.
Looks like you have kept the memory paramter static (Fixed). In this case 2 GB will be reserved to sql server and will not be shared across for other applications.
USE the Perfmon -- Memory -- availableMBytes counter to get the exact value during peak time.
3) CPU Required - They say that we only need a dual CPU server. In the past, one of the SQL Server uses 4 processors while the other 2 are 2 processor servers.
If your application is less processor intensive then you could go for 2 CPU servers. Again run the Perfmon and identify the CPU usage. If the CPU usage is <50% with
2 CPU duing peak then you do not have any CPU issue.
Measure cpu using the counter -- PROCESSOR -- % PROCESSOR TIME.
Thanks
Hari
SQL Server MVP
"Jason" <anonymous@.discussions.microsoft.com> wrote in message news:%23Qip5aAnFHA.3648@.TK2MSFTNGP10.phx.gbl...
Dear Mike,
Thank you for your advice.
I would like to ask 3 questions:
1) Does it mean that each instance can access 2GB. In other words, if there are 3 instances installed, each of them can access 2GB or 6GB in total.
2) RAM Required - We have asked a consultant for Server Virtualization / Consolidation. Their suggestion for RAM usage is 8GB just 2GB + 2GB + 2GB (As we have 3 SQL Servers). However, we know that two of the SQL Servers are under utilized BUT their report tells us that they are using 2GB.
3) CPU Required - They say that we only need a dual CPU server. In the past, one of the SQL Server uses 4 processors while the other 2 are 2 processor servers.
Any suggestion is highly appreciated.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:uRBjwE8mFHA.1968@.TK2MSFTNGP14.phx.gbl...
It's impossible to say whether you need more memory or not without a few metrics. A couple perfmon counters you should look at are the buffer cache hit ratio (SQLServer:Buffer Manager | Buffer cache hit ratio) and the page life expectancy (SQLServer:Buffer Manager | Page life expectancy).
SQL Server will cache data pages that are accessed, so the SQL Server address space fills up with stuff and will remain filled with stuff until it needs to free up the memory for another app, the OS or more recently accessed data pages.
The cache hit ratio will tell you what percentage of your data requests are satisfied from RAM (should be pretty high, like 99+ percent).
The page life expectancy is how long SQL Server expects a data page to remain in RAM before needing to be swapped out to disk to free up the memory for other apps or data. It's measured in seconds and I like to make sure my servers have pages staying in RAM for at least 5 minutes on average, preferable an hour or more.
Those two counters, while they won't give you a complete picture of the memory pressure on a SQL server, will give you a fairly good idea of whether or not you need to think about increasing physical RAM in a box.
And, yes, SQL Server is capable of managing a good deal more memory than 2G. With different editions, SQL Server 2000 can handle up to 64G (http://www.microsoft.com/sql/techinf...KChooseEd.mspx).
mike hodgson
blog: http://sqlnerd.blogspot.com
Jason wrote:
We are going to replace the old SQL Server 2000 to a new
Hardware running Windows 2003 Server.
We are planning how much memory should be used. At the
present monent, we are using 2GB RAM and the performance
is OK. We find that the Average RAM and Peak RAM used is
around 2000 MB. According to Network Administrator, it is
because SQL uses all memory available for caching /
performance optimisation.
Is it necessary for us to purchase more RAM for the new
server ? Is it possible for SQL Server 2000 Standard to
access more than 2 GB RAM ?
Thanks
|||Jason wrote:

> Dear Hari,
> Thank you for your advice.
> 1) I haven't installed more than 1 instance on a SQL Server. It is
> just for my interest only. I want to confirm I have followed what you
> mean = Each instance can be allocated 2 GB and if that instance is
> down, that 2GB will be of no use to other instances ?
No. If that 2GB is freed up it can be used by any application
(including other SQL instances). Configuring a SQL instance to only use
a maximum of 2GB of RAM doesn't reserve that physical memory for the exe
(unless it is running and you've turned off the standard dynamic memory
management). I think what Hari is trying to point out here is that if
the SQL instance is only using a max of 2GB then that is how much memory
will be available to other apps if that SQL instance shuts down.

> 2) NO. All of the SQL Servers RAM is dynamically allocated. I am
> wondering when we get the new Server, maybe we will assign 2.5GB for
> each Virtual Server. And I can assign 2GB to each SQL Server static.
> I would like to know if we statically allocate RAM, should I choose
> Minimum = 2GB and Maximum = 2GB ?
Personally, if you're talking about < 4GB of RAM then I would leave all
the instances set to allocate memory dynamically. Unless, one SQL
instance is particularly important and is having to fight for its memory
(eg. a production instance fighting with UAT & development instances for
the memory it needs), in which case I would still let it allocate memory
dynamically but would set a minimum server memory for that important
server of, say, 2GB. If you enable the /3GB switch in boot.ini then
each instance can grab up to 3GB of RAM if needed, but with the dynamic
memory management they will release RAM, if necessary, when other apps
make requests for it and there's not enough free.

> 3) My boss just wonders why in the past for each SQL Server, at least
> 2 CPU are required, now for 3 Virtual Servers, only 2 processors are
> required. Anyhow, we have to look into the Virtual Server requirement
> and it is recommended by them. On the other hand, we are now using 20
> CALs for each SQL Server, if we reduce the number of CPU of 1 SQL
> Server from 4 to 2, can we get any saving in licensing ?
There is a difference between a virtual server and a named SQL
instance. Virtual servers relate to SQL Server running on a Windows
cluster using Microsoft Clustering Services (MSCS). I think you meant
to say "SQL instance" rather than "virtual server".
It's possible that the CPUs you buy today are faster than the CPUs that
were in your previous boxes, in which case you may be able to get away
with fewer physical CPUs in your new box. Also, hyper-threading (which
may not have been an attribute of your old CPUs) may make a difference
to the maximum SQL load your new CPUs can handle. (All of this is
purely conjecture because I don't know all your details.) You should
keep an eye on the Processor perfmon counters on your new box as you
start to add load to make sure the fewer CPUs are up to the job. As a
general rule of thumb though, SQL Server is typically a CPU bound
application so you shouldn't skimp on the processors if you can afford it.
In terms of licensing, you should talk to you friendly Microsoft
licensing person because it's a rather convoluted area that not many
people are on top of (myself included). But my understand of the client
CALs is that 1 client CAL will allow 1 client computer to connect to an
number of purchased SQL server instances on your network (or should that
be MS Domain?). If you're licensed by client CALs then the number of
CPUs in each server doesn't make any difference - that only comes into
play when you're license by "per processor". I think that's right but
you should check that with Microsoft.
Hope that helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
[vbcol=seagreen]
> Thanks
> Jason
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com
> <mailto:hari_prasad_k@.hotmail.com>> wrote in message
> news:OyF9IpAnFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Jason,
> 1) Does it mean that each instance can access 2GB. In other
> words, if there are 3 instances installed, each of them can access
> 2GB or 6GB in total.
> SQL Server Standard edition can utilize 2 GB memory for each
> instance maximum. So 2 GB only will be utilized for each instance.
> Incase
> 1 instance is down then 2 GB allocated for that instance will be
> free.
> 2) RAM Required - We have asked a consultant for Server
> Virtualization / Consolidation. Their suggestion for RAM usage is
> 8GB just 2GB + 2GB + 2GB (As we have 3 SQL Servers). However, we
> know that two of the SQL Servers are under utilized BUT their
> report tells us that they are using 2GB.
> Looks like you have kept the memory paramter static (Fixed). In
> this case 2 GB will be reserved to sql server and will not be
> shared across for other applications.
> USE the Perfmon -- Memory -- availableMBytes counter to get the
> exact value during peak time.
> 3) CPU Required - They say that we only need a dual CPU server.
> In the past, one of the SQL Server uses 4 processors while the
> other 2 are 2 processor servers.
> If your application is less processor intensive then you could go
> for 2 CPU servers. Again run the Perfmon and identify the CPU
> usage. If the CPU usage is <50% with
> 2 CPU duing peak then you do not have any CPU issue.
> Measure cpu using the counter -- PROCESSOR -- % PROCESSOR TIME.
> Thanks
> Hari
> SQL Server MVP
>
>
> "Jason" <anonymous@.discussions..microsoft.com
> <mailto:anonymous@.discussions.microsoft.com>> wrote in message
> news:%23Qip5aAnFHA.3648@.TK2MSFTNGP10.phx.gbl...
> Dear Mike,
> Thank you for your advice.
> I would like to ask 3 questions:
> 1) Does it mean that each instance can access 2GB. In other
> words, if there are 3 instances installed, each of them can
> access 2GB or 6GB in total.
> 2) RAM Required - We have asked a consultant for Server
> Virtualization / Consolidation. Their suggestion for RAM
> usage is 8GB just 2GB + 2GB + 2GB (As we have 3 SQL Servers).
> However, we know that two of the SQL Servers are under
> utilized BUT their report tells us that they are using 2GB.
> 3) CPU Required - They say that we only need a dual CPU
> server. In the past, one of the SQL Server uses 4 processors
> while the other 2 are 2 processor servers.
> Any suggestion is highly appreciated.
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com
> <mailto:mike.hodgson@.mallesons.nospam.com>> wrote in
> message news:uRBjwE8mFHA.1968@.TK2MSFTNGP14.phx.gbl...
> It's impossible to say whether you need more memory or not
> without a few metrics. A couple perfmon counters you
> should look at are the buffer cache hit ratio
> (SQLServer:Buffer Manager | Buffer cache hit ratio) and
> the page life expectancy (SQLServer:Buffer Manager | Page
> life expectancy).
> SQL Server will cache data pages that are accessed, so the
> SQL Server address space fills up with stuff and will
> remain filled with stuff until it needs to free up the
> memory for another app, the OS or more recently accessed
> data pages.
> The cache hit ratio will tell you what percentage of your
> data requests are satisfied from RAM (should be pretty
> high, like 99+ percent).
> The page life expectancy is how long SQL Server expects a
> data page to remain in RAM before needing to be swapped
> out to disk to free up the memory for other apps or data.
> It's measured in seconds and I like to make sure my
> servers have pages staying in RAM for at least 5 minutes
> on average, preferable an hour or more.
> Those two counters, while they won't give you a complete
> picture of the memory pressure on a SQL server, will give
> you a fairly good idea of whether or not you need to think
> about increasing physical RAM in a box.
> And, yes, SQL Server is capable of managing a good deal
> more memory than 2G. With different editions, SQL Server
> 2000 can handle up to 64G
> (http://www.microsoft.com/sql/techinf...KChooseEd.mspx).
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Jason wrote:
|||Hi,
from a licensing point of view...
if you have processors licenses for SQL Server and you want to use it in Virtual Server you have to pay ONLY for physical processors on the server.
So if you have 2 processors in the server, you pay 2 proc. licenses for SQL Server and you can use it in all your guest os.
You only have to pay for the OS used in the guest, not for SQL.
if you have CALs, its client access licenses, so the licenses are by user, and again there is no impact in your guests systems. 1 CAL give access to the user to all your SQL Servers installations.
And now, with dual core processors, you can setup a Dual Opteron Dual Core server (so windows see 4 processors) and you pay your SQL Server for only the 2 physical processors.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%23FlBIsXnFHA.3572@.TK2MSFTNGP09.phx.gbl...
Jason wrote:
Dear Hari,
Thank you for your advice.
1) I haven't installed more than 1 instance on a SQL Server. It is just for my interest only. I want to confirm I have followed what you mean = Each instance can be allocated 2 GB and if that instance is down, that 2GB will be of no use to other instances ?
No. If that 2GB is freed up it can be used by any application (including other SQL instances). Configuring a SQL instance to only use a maximum of 2GB of RAM doesn't reserve that physical memory for the exe (unless it is running and you've turned off the standard dynamic memory management). I think what Hari is trying to point out here is that if the SQL instance is only using a max of 2GB then that is how much memory will be available to other apps if that SQL instance shuts down.
2) NO. All of the SQL Servers RAM is dynamically allocated. I am wondering when we get the new Server, maybe we will assign 2.5GB for each Virtual Server. And I can assign 2GB to each SQL Server static. I would like to know if we statically allocate RAM, should I choose Minimum = 2GB and Maximum = 2GB ?
Personally, if you're talking about < 4GB of RAM then I would leave all the instances set to allocate memory dynamically. Unless, one SQL instance is particularly important and is having to fight for its memory (eg. a production instance fighting with UAT & development instances for the memory it needs), in which case I would still let it allocate memory dynamically but would set a minimum server memory for that important server of, say, 2GB. If you enable the /3GB switch in boot.ini then each instance can grab up to 3GB of RAM if needed, but with the dynamic memory management they will release RAM, if necessary, when other apps make requests for it and there's not enough free.
3) My boss just wonders why in the past for each SQL Server, at least 2 CPU are required, now for 3 Virtual Servers, only 2 processors are required. Anyhow, we have to look into the Virtual Server requirement and it is recommended by them. On the other hand, we are now using 20 CALs for each SQL Server, if we reduce the number of CPU of 1 SQL Server from 4 to 2, can we get any saving in licensing ?
There is a difference between a virtual server and a named SQL instance. Virtual servers relate to SQL Server running on a Windows cluster using Microsoft Clustering Services (MSCS). I think you meant to say "SQL instance" rather than "virtual server".
It's possible that the CPUs you buy today are faster than the CPUs that were in your previous boxes, in which case you may be able to get away with fewer physical CPUs in your new box. Also, hyper-threading (which may not have been an attribute of your old CPUs) may make a difference to the maximum SQL load your new CPUs can handle. (All of this is purely conjecture because I don't know all your details.) You should keep an eye on the Processor perfmon counters on your new box as you start to add load to make sure the fewer CPUs are up to the job. As a general rule of thumb though, SQL Server is typically a CPU bound application so you shouldn't skimp on the processors if you can afford it.
In terms of licensing, you should talk to you friendly Microsoft licensing person because it's a rather convoluted area that not many people are on top of (myself included). But my understand of the client CALs is that 1 client CAL will allow 1 client computer to connect to an number of purchased SQL server instances on your network (or should that be MS Domain?). If you're licensed by client CALs then the number of CPUs in each server doesn't make any difference - that only comes into play when you're license by "per processor". I think that's right but you should check that with Microsoft.
Hope that helps.
mike hodgson
blog: http://sqlnerd.blogspot.com
Thanks
Jason
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:OyF9IpAnFHA.2852@.TK2MSFTNGP15.phx.gbl...
Jason,
1) Does it mean that each instance can access 2GB. In other words, if there are 3 instances installed, each of them can access 2GB or 6GB in total.
SQL Server Standard edition can utilize 2 GB memory for each instance maximum. So 2 GB only will be utilized for each instance. Incase
1 instance is down then 2 GB allocated for that instance will be free.
2) RAM Required - We have asked a consultant for Server Virtualization / Consolidation. Their suggestion for RAM usage is 8GB just 2GB + 2GB + 2GB (As we have 3 SQL Servers). However, we know that two of the SQL Servers are under utilized BUT their report tells us that they are using 2GB.
Looks like you have kept the memory paramter static (Fixed). In this case 2 GB will be reserved to sql server and will not be shared across for other applications.
USE the Perfmon -- Memory -- availableMBytes counter to get the exact value during peak time.
3) CPU Required - They say that we only need a dual CPU server. In the past, one of the SQL Server uses 4 processors while the other 2 are 2 processor servers.
If your application is less processor intensive then you could go for 2 CPU servers. Again run the Perfmon and identify the CPU usage. If the CPU usage is <50% with
2 CPU duing peak then you do not have any CPU issue.
Measure cpu using the counter -- PROCESSOR -- % PROCESSOR TIME.
Thanks
Hari
SQL Server MVP
"Jason" <anonymous@.discussions..microsoft.com> wrote in message news:%23Qip5aAnFHA.3648@.TK2MSFTNGP10.phx.gbl...
Dear Mike,
Thank you for your advice.
I would like to ask 3 questions:
1) Does it mean that each instance can access 2GB. In other words, if there are 3 instances installed, each of them can access 2GB or 6GB in total.
2) RAM Required - We have asked a consultant for Server Virtualization / Consolidation. Their suggestion for RAM usage is 8GB just 2GB + 2GB + 2GB (As we have 3 SQL Servers). However, we know that two of the SQL Servers are under utilized BUT their report tells us that they are using 2GB.
3) CPU Required - They say that we only need a dual CPU server. In the past, one of the SQL Server uses 4 processors while the other 2 are 2 processor servers.
Any suggestion is highly appreciated.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:uRBjwE8mFHA.1968@.TK2MSFTNGP14.phx.gbl...
It's impossible to say whether you need more memory or not without a few metrics. A couple perfmon counters you should look at are the buffer cache hit ratio (SQLServer:Buffer Manager | Buffer cache hit ratio) and the page life expectancy (SQLServer:Buffer Manager | Page life expectancy).
SQL Server will cache data pages that are accessed, so the SQL Server address space fills up with stuff and will remain filled with stuff until it needs to free up the memory for another app, the OS or more recently accessed data pages.
The cache hit ratio will tell you what percentage of your data requests are satisfied from RAM (should be pretty high, like 99+ percent).
The page life expectancy is how long SQL Server expects a data page to remain in RAM before needing to be swapped out to disk to free up the memory for other apps or data. It's measured in seconds and I like to make sure my servers have pages staying in RAM for at least 5 minutes on average, preferable an hour or more.
Those two counters, while they won't give you a complete picture of the memory pressure on a SQL server, will give you a fairly good idea of whether or not you need to think about increasing physical RAM in a box.
And, yes, SQL Server is capable of managing a good deal more memory than 2G. With different editions, SQL Server 2000 can handle up to 64G (http://www.microsoft.com/sql/techinf...KChooseEd.mspx).
mike hodgson
blog: http://sqlnerd.blogspot.com
Jason wrote:
We are going to replace the old SQL Server 2000 to a new
Hardware running Windows 2003 Server.
We are planning how much memory should be used. At the
present monent, we are using 2GB RAM and the performance
is OK. We find that the Average RAM and Peak RAM used is
around 2000 MB. According to Network Administrator, it is
because SQL uses all memory available for caching /
performance optimisation.
Is it necessary for us to purchase more RAM for the new
server ? Is it possible for SQL Server 2000 Standard to
access more than 2 GB RAM ?
Thanks