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?
Saturday, February 25, 2012
amount of Records SQL 2005 can handle
Labels:
amount,
database,
handle,
limitations,
mappings,
microsoft,
mysql,
number,
ofrecordswe,
oracle,
records,
relationships,
server,
sql,
tables
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment