analysis manager question - cube

Hi!
I have a large table (5 Million records) and want to make a cube with 20
dimensions on that.
That 20 dimensions are from 3 other tables (150000 records, 500 records,
200000 records) joined with my large table and 10 dimensions from my large
table.
That tables are daily truncated an new data records are imported with dts
jobs.
Now I build a view on sqlserver 2000 with that tables joined another.
Than I build an analysis manager my cube based on that view. My 20
dimensions I created in that cube directly (new dimension) also based every
one on that sqlserver 2000 view.
Now my questions:
1. is there a better way to create my cube and dimensions?
2. what must I with my cube and dimensions do that on the next day my new
records are also in the cube?
Thanks for ideas!Hi Hubert,
Microsoft recommends to create views on which the dimensions and facts is to
be build.Create the views with the same level of data grain and build dimen
sions.
Once the dimensions and facts table are joined together, process the cube wi
th the "Full Process" option. For the new records to be appeared in the cube
, go for "Incremental update" or "Refresh Cube".
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...

analysis manager question - cube

Hi!
I have a large table (5 Million records) and want to make a cube with 20
dimensions on that.
That 20 dimensions are from 3 other tables (150000 records, 500 records,
200000 records) joined with my large table and 10 dimensions from my large
table.
That tables are daily truncated an new data records are imported with dts
jobs.
Now I build a view on sqlserver 2000 with that tables joined another.
Than I build an analysis manager my cube based on that view. My 20
dimensions I created in that cube directly (new dimension) also based every
one on that sqlserver 2000 view.
Now my questions:
1. is there a better way to create my cube and dimensions?
2. what must I with my cube and dimensions do that on the next day my new
records are also in the cube?
Thanks for ideas!
Hi Hubert,
Microsoft recommends to create views on which the dimensions and facts is to be build.Create the views with the same level of data grain and build dimensions.
Once the dimensions and facts table are joined together, process the cube with the "Full Process" option. For the new records to be appeared in the cube, go for "Incremental update" or "Refresh Cube".
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

An Urgent help required on Performance tuning

Dear All,
We use SQL Server 2000. Inorder to increase the performance archived about
2.5 million records.
However after the archival, the performance is found to be reduced as you
can see in the below comparison details. This downsizing of almost 2.5
millions records has been followed by an "update statistics" in all table
from where, records were removed.
Can anybody give us hints : what could be the the potntial reason for
non-improvement of performance.
What all tunings are yet to be done?
( We are relatively new in this area)
Performance Performance
After Before
- To reset working status for a plant 57s
40s
- To get the menu of pre-release:
delay before having possibility to chose
plant and report for pre release 2 min 4s
2min 41s
- To select a plant 2 min 12s 2min 5s
- To pre-release the P&L2 of a plant 4 min 4s
5min 22s
Thanks and Regards
Sathian"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:eaakvo$nc6$1@.news4.fe.internet.bosch.com...
> Dear All,
> We use SQL Server 2000. Inorder to increase the performance archived about
> 2.5 million records.
> However after the archival, the performance is found to be reduced as you
> can see in the below comparison details. This downsizing of almost 2.5
> millions records has been followed by an "update statistics" in all table
> from where, records were removed.
>
> Can anybody give us hints : what could be the the potntial reason for
> non-improvement of performance.
Have you done a backup and a trancate log on that db?|||Have you rebuilt your indexes?
Phil
"Sathian" wrote:
> Dear All,
> We use SQL Server 2000. Inorder to increase the performance archived about
> 2.5 million records.
> However after the archival, the performance is found to be reduced as you
> can see in the below comparison details. This downsizing of almost 2.5
> millions records has been followed by an "update statistics" in all table
> from where, records were removed.
>
> Can anybody give us hints : what could be the the potntial reason for
> non-improvement of performance.
> What all tunings are yet to be done?
>
> ( We are relatively new in this area)
>
>
> Performance Performance
> After Before
> - To reset working status for a plant 57s
> 40s
> - To get the menu of pre-release:
> delay before having possibility to chose
> plant and report for pre release 2 min 4s
> 2min 41s
> - To select a plant 2 min 12s 2min 5s
> - To pre-release the P&L2 of a plant 4 min 4s
> 5min 22s
> Thanks and Regards
> Sathian
>
>|||It is not always a big surprise if performance does not improve when
old data is archived. If the tables were properly designed, the
indexes well chosen, and the queries done right, then databases can
often grow quite large without degrading performance, so it seems
reasonable that shrinking them back won't improve performance.
For performance to get worse when data is archived is unexpected.
Perhaps there was some other change at the same time that had a
significant impact.
One thing worth trying: don't just update statistics, rebuild the
indexes. See DBCC DBREINDEX in the documentation.
Roy Harvey
Beacon Falls, CT
On Thu, 27 Jul 2006 20:40:27 +0530, "Sathian" <sathian.t@.in.bosch.com>
wrote:
>Dear All,
>We use SQL Server 2000. Inorder to increase the performance archived about
>2.5 million records.
>However after the archival, the performance is found to be reduced as you
>can see in the below comparison details. This downsizing of almost 2.5
>millions records has been followed by an "update statistics" in all table
>from where, records were removed.
>
>Can anybody give us hints : what could be the the potntial reason for
>non-improvement of performance.
>What all tunings are yet to be done?
>
>( We are relatively new in this area)
>
>
>Performance Performance
>After Before
> - To reset working status for a plant 57s
>40s
> - To get the menu of pre-release:
>delay before having possibility to chose
>plant and report for pre release 2 min 4s
>2min 41s
> - To select a plant 2 min 12s 2min 5s
> - To pre-release the P&L2 of a plant 4 min 4s
>5min 22s
>Thanks and Regards
>Sathian
>|||As others have indicated, you need to examine your indexes. What
percentage of your data did the archiving process represent?
Sathian wrote:
> Dear All,
> We use SQL Server 2000. Inorder to increase the performance archived about
> 2.5 million records.
> However after the archival, the performance is found to be reduced as you
> can see in the below comparison details. This downsizing of almost 2.5
> millions records has been followed by an "update statistics" in all table
> from where, records were removed.
>
> Can anybody give us hints : what could be the the potntial reason for
> non-improvement of performance.
> What all tunings are yet to be done?
>
> ( We are relatively new in this area)
>
>
> Performance Performance
> After Before
> - To reset working status for a plant 57s
> 40s
> - To get the menu of pre-release:
> delay before having possibility to chose
> plant and report for pre release 2 min 4s
> 2min 41s
> - To select a plant 2 min 12s 2min 5s
> - To pre-release the P&L2 of a plant 4 min 4s
> 5min 22s
> Thanks and Regards
> Sathian|||On Thu, 27 Jul 2006 20:40:27 +0530, "Sathian" <sathian.t@.in.bosch.com>
wrote:
>Can anybody give us hints : what could be the the potntial reason for
>non-improvement of performance.
Can you show a little SQL that's running slowly?
It may be the new data is not well-distributed, so after deleting the
old data, the indexes look even worse to the optimizer, and even worse
plans are chosen. Stranger things have happened.
J.

An Urgent help required on Performance tuning

Dear All,
We use SQL Server 2000. Inorder to increase the performance archived about
2.5 million records.
However after the archival, the performance is found to be reduced as you
can see in the below comparison details. This downsizing of almost 2.5
millions records has been followed by an "update statistics" in all table
from where, records were removed.
Can anybody give us hints : what could be the the potntial reason for
non-improvement of performance.
What all tunings are yet to be done?
( We are relatively new in this area)
Performance Performance
After Before
- To reset working status for a plant 57s
40s
- To get the menu of pre-release:
delay before having possibility to chose
plant and report for pre release 2 min 4s
2min 41s
- To select a plant 2 min 12s 2min 5s
- To pre-release the P&L2 of a plant 4 min 4s
5min 22s
Thanks and Regards
Sathian"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:eaakvo$nc6$1@.news4.fe.internet.bosch.com...
> Dear All,
> We use SQL Server 2000. Inorder to increase the performance archived about
> 2.5 million records.
> However after the archival, the performance is found to be reduced as you
> can see in the below comparison details. This downsizing of almost 2.5
> millions records has been followed by an "update statistics" in all table
> from where, records were removed.
>
> Can anybody give us hints : what could be the the potntial reason for
> non-improvement of performance.
Have you done a backup and a trancate log on that db?|||Have you rebuilt your indexes?
Phil
"Sathian" wrote:

> Dear All,
> We use SQL Server 2000. Inorder to increase the performance archived about
> 2.5 million records.
> However after the archival, the performance is found to be reduced as you
> can see in the below comparison details. This downsizing of almost 2.5
> millions records has been followed by an "update statistics" in all table
> from where, records were removed.
>
> Can anybody give us hints : what could be the the potntial reason for
> non-improvement of performance.
> What all tunings are yet to be done?
>
> ( We are relatively new in this area)
>
>
> Performance Performance
> After Before
> - To reset working status for a plant 57s
> 40s
> - To get the menu of pre-release:
> delay before having possibility to chose
> plant and report for pre release 2 min 4s
> 2min 41s
> - To select a plant 2 min 12s 2min 5s
> - To pre-release the P&L2 of a plant 4 min 4s
> 5min 22s
> Thanks and Regards
> Sathian
>
>|||It is not always a big surprise if performance does not improve when
old data is archived. If the tables were properly designed, the
indexes well chosen, and the queries done right, then databases can
often grow quite large without degrading performance, so it seems
reasonable that shrinking them back won't improve performance.
For performance to get worse when data is archived is unexpected.
Perhaps there was some other change at the same time that had a
significant impact.
One thing worth trying: don't just update statistics, rebuild the
indexes. See DBCC DBREINDEX in the documentation.
Roy Harvey
Beacon Falls, CT
On Thu, 27 Jul 2006 20:40:27 +0530, "Sathian" <sathian.t@.in.bosch.com>
wrote:

>Dear All,
>We use SQL Server 2000. Inorder to increase the performance archived about
>2.5 million records.
>However after the archival, the performance is found to be reduced as you
>can see in the below comparison details. This downsizing of almost 2.5
>millions records has been followed by an "update statistics" in all table
>from where, records were removed.
>
>Can anybody give us hints : what could be the the potntial reason for
>non-improvement of performance.
>What all tunings are yet to be done?
>
>( We are relatively new in this area)
>
>
>Performance Performance
>After Before
> - To reset working status for a plant 57s
>40s
> - To get the menu of pre-release:
>delay before having possibility to chose
>plant and report for pre release 2 min 4s
>2min 41s
> - To select a plant 2 min 12s 2min 5s
> - To pre-release the P&L2 of a plant 4 min 4s
>5min 22s
>Thanks and Regards
>Sathian
>|||As others have indicated, you need to examine your indexes. What
percentage of your data did the archiving process represent?
Sathian wrote:
> Dear All,
> We use SQL Server 2000. Inorder to increase the performance archived about
> 2.5 million records.
> However after the archival, the performance is found to be reduced as you
> can see in the below comparison details. This downsizing of almost 2.5
> millions records has been followed by an "update statistics" in all table
> from where, records were removed.
>
> Can anybody give us hints : what could be the the potntial reason for
> non-improvement of performance.
> What all tunings are yet to be done?
>
> ( We are relatively new in this area)
>
>
> Performance Performance
> After Before
> - To reset working status for a plant 57s
> 40s
> - To get the menu of pre-release:
> delay before having possibility to chose
> plant and report for pre release 2 min 4s
> 2min 41s
> - To select a plant 2 min 12s 2min 5s
> - To pre-release the P&L2 of a plant 4 min 4s
> 5min 22s
> Thanks and Regards
> Sathian|||On Thu, 27 Jul 2006 20:40:27 +0530, "Sathian" <sathian.t@.in.bosch.com>
wrote:
>Can anybody give us hints : what could be the the potntial reason for
>non-improvement of performance.
Can you show a little SQL that's running slowly?
It may be the new data is not well-distributed, so after deleting the
old data, the indexes look even worse to the optimizer, and even worse
plans are chosen. Stranger things have happened.
J.sql

An Full-Text Catalog Item Count of 0?

Hi,
I've enabled a full-text index on a table having 77 thousand records on a
varchar(50) column. And yet when I when I populate the catalog:
exec sp_fulltext_catalog 'OrdersCatalog', 'start_full'
I get an item count,
SELECT fulltextcatalogproperty('OrdersCatalog', 'ItemCount'),
of 0.
This column has data 95% of the time. What am I missing?
Thank you.
--
Mark Holahan
Check the event log and the gatherer logs for messages:
It is likely you are running into one of the errors in these kb articles
http://support.microsoft.com/default...b;en-us;295034
http://support.microsoft.com/default...b;en-us;317746
http://support.microsoft.com/default...b;en-us;295772
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mark Holahan" <mark.holahan@.unifiedllc.com> wrote in message
news:Ogx2wObHFHA.2860@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've enabled a full-text index on a table having 77 thousand records on a
> varchar(50) column. And yet when I when I populate the catalog:
> exec sp_fulltext_catalog 'OrdersCatalog', 'start_full'
> I get an item count,
> SELECT fulltextcatalogproperty('OrdersCatalog', 'ItemCount'),
> of 0.
> This column has data 95% of the time. What am I missing?
> Thank you.
> --
> --
> Mark Holahan
>
|||Bull's eye! Thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ek2TowbHFHA.3880@.TK2MSFTNGP10.phx.gbl...
> Check the event log and the gatherer logs for messages:
> It is likely you are running into one of the errors in these kb articles
> http://support.microsoft.com/default...b;en-us;295034
> http://support.microsoft.com/default...b;en-us;317746
> http://support.microsoft.com/default...b;en-us;295772
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Mark Holahan" <mark.holahan@.unifiedllc.com> wrote in message
> news:Ogx2wObHFHA.2860@.TK2MSFTNGP12.phx.gbl...
>
|||Hi Hilary,
I have had a similar problem with the Full-text Catalog Item Count of 0,
after I enable some Full-text Indexing from 4 tables. I can view the
table names on the Full-text Catalog, but when double-clicked the
Catalog, the propereties page showed an Item Count of 0.
Because we are developing a back-end database for a SharePoint Services
server, based on the information from the SharePoint Administration
guide, we needed to use Domain Account, instead of the local System
account, after we saw the Item Count of 0 for our first try.
The big question is after we set up the domain account, the Item Count
was still zero.
Is something wrong with the Full-text Search configuration, or on the
SharePoint Service server?
Thanks,
Jeffrey
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Is MSSearch running under the local system account? It must be for this to
work.
Are there any error or warning messages in the application log from MSSearch
or MSSCi?
You might want to consult these kb article for more information on resolving
the typical problems you get this MSSearch and SQL Server.
http://support.microsoft.com/default...b;en-us;295034
http://support.microsoft.com/default...b;en-us;317746
http://support.microsoft.com/default...b;en-us;295772
"Jeffrey Wang" <cjeffwang@.gmail.com> wrote in message
news:ug43i2TIFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi Hilary,
> I have had a similar problem with the Full-text Catalog Item Count of 0,
> after I enable some Full-text Indexing from 4 tables. I can view the
> table names on the Full-text Catalog, but when double-clicked the
> Catalog, the propereties page showed an Item Count of 0.
> Because we are developing a back-end database for a SharePoint Services
> server, based on the information from the SharePoint Administration
> guide, we needed to use Domain Account, instead of the local System
> account, after we saw the Item Count of 0 for our first try.
> The big question is after we set up the domain account, the Item Count
> was still zero.
> Is something wrong with the Full-text Search configuration, or on the
> SharePoint Service server?
> Thanks,
> Jeffrey
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

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?

AMO: Only need to use ProcessData?

Hi, friends, please look at this:

I add and delete some data records into the FactTable in the SQL Server(Not changing any structure), and want to process the cube by AMO. I only need to use the ProcessData on the cube, right?

I add and delete some data records into the DimentionTable in the SQL Server(Not changing any structure), and want to process the cube by AMO. I only need to use the ProcessUpdate on the dimention , right?,

Moved to Analysis Services|||please have a look. Thanks.|||

ivanchain wrote:

I add and delete some data records into the FactTable in the SQL Server(Not changing any structure), and want to process the cube by AMO. I only need to use the ProcessData on the cube, right?

You would also need to do a processIndex to get the aggregations and indexes processed (the cube would work, but could be quite slow if you have designed aggregations but don't do this)

ivanchain wrote:

I add and delete some data records into the DimentionTable in the SQL Server(Not changing any structure), and want to process the cube by AMO. I only need to use the ProcessUpdate on the dimention , right?,

Yes, ProcessUpdate is all you need to do to add/delete/update records in a dimension table.

|||

Thanks!

AMO: Only need to use ProcessData?

Hi, friends, please look at this:

I add and delete some data records into the FactTable in the SQL Server(Not changing any structure), and want to process the cube by AMO. I only need to use the ProcessData on the cube, right?

I add and delete some data records into the DimentionTable in the SQL Server(Not changing any structure), and want to process the cube by AMO. I only need to use the ProcessUpdate on the dimention , right?,

Moved to Analysis Services|||please have a look. Thanks.|||

ivanchain wrote:

I add and delete some data records into the FactTable in the SQL Server(Not changing any structure), and want to process the cube by AMO. I only need to use the ProcessData on the cube, right?

You would also need to do a processIndex to get the aggregations and indexes processed (the cube would work, but could be quite slow if you have designed aggregations but don't do this)

ivanchain wrote:

I add and delete some data records into the DimentionTable in the SQL Server(Not changing any structure), and want to process the cube by AMO. I only need to use the ProcessUpdate on the dimention , right?,

Yes, ProcessUpdate is all you need to do to add/delete/update records in a dimension table.

|||

Thanks!

Am I only one with the problem using CSV files?

Simple thing - trying to set up connection for CSV file.

In Microsoft Excel - CSV is displayed OK.

However - in SSIS - some records are broken - for example records where Description field contains: ""WHITE HOT.""

I'm not expert on CSV format - but aren't double quotes used for escape in csv - and if so then WHY microsoft implements it one of their products and not it SSIS?

So what am I going to do now?Search the forums, man! You aren't alone and SSIS doesn't handle embedded quotes.|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=546729&SiteID=1|||Thanks for the link.

I was mostly pissed off that my 2 hour project was going to be days worth of work because they decided not to implement CSV.

However - it did not take long for me to write the following code in VB Script Task
(The components marked as asnwer in this linked post seemed not quite what i needed - and Im somewhat suspicious of installing another SSIS component)

Sub ProcessFile(ByVal source_file As String, ByVal destination_file As String)
Dim sReader As New System.IO.StreamReader(source_file)
Dim sWriter As New System.IO.StreamWriter(destination_file)
Do
sWriter.WriteLine(FixCsvQuotes(sReader.ReadLine()))
Loop Until sReader.Peek = -1
sWriter.Close()
sReader.Close()
End Sub

Function FixCsvQuotes(ByVal csv_row As String) As String
Dim qiStr As String = Chr(34) & "," & Chr(34) ' ","
Dim qiStr1 As String = Chr(34) & "," ' ",
Dim qiStr2 As String = "," & Chr(34) ' ,"
Dim str1 As String = ""
Dim str2 As String = ""
Dim str3 As String = ""

str1 = csv_row.Substring(0, csv_row.IndexOf(Chr(34)) + 1)
str3 = csv_row.Substring(csv_row.LastIndexOf(Chr(34)), csv_row.Length - csv_row.LastIndexOf(Chr(34)))
str2 = csv_row.Substring(str1.Length, csv_row.Length - str1.Length - str3.Length)

str2 = Replace(str2, qiStr, "|&|")
str2 = Replace(str2, qiStr1, "|&")
str2 = Replace(str2, qiStr2, "&|")

str2 = Replace(str2, Chr(34), "&#34;")

str2 = Replace(str2, "|&|", qiStr)
str2 = Replace(str2, "|&", qiStr1)
str2 = Replace(str2, "&|", qiStr2)

Return (str1 & str2 & str3)
End Function|||Note:
following string will still cause an error if in description:

...available in ""The Siena"", the most sought after ...|||

TheViewMaster wrote:

Thanks for the link.

I was mostly pissed off that my 2 hour project was going to be days worth of work because they decided not to implement CSV.

They have done.

never ever trust what Excel says about CSVs. If you want to look at the raw data, open it up in a text editor.

-Jamie

|||Well - it still doesn't work - is there a VB function which can fix CSV files for SSIS import?|||Allrite - my 3rd hack at Visual basic FixCsv function:

Function FixCsvQuotes(ByVal csv_row As String) As String
Dim qiStr0 As String = "," & Chr(34) & Chr(34) & "," ' ,"",
Dim qiStr As String = Chr(34) & "," & Chr(34) ' ","
Dim qiStr1 As String = Chr(34) & "," ' ",
Dim qiStr2 As String = "," & Chr(34) ' ,"
Dim str1 As String = ""
Dim str2 As String = ""
Dim str3 As String = ""

str1 = csv_row.Substring(0, csv_row.IndexOf(Chr(34)) + 1)
str3 = csv_row.Substring(csv_row.LastIndexOf(Chr(34)), csv_row.Length - csv_row.LastIndexOf(Chr(34)))
str2 = csv_row.Substring(str1.Length, csv_row.Length - str1.Length - str3.Length)

While InStr(str2, qiStr0) > 0
str2 = Replace(str2, qiStr0, ",&||&,")
End While

str2 = Replace(str2, qiStr, "|&|")
str2 = Replace(str2, Chr(34) & Chr(34), "&#34;")
str2 = Replace(str2, qiStr1, "|&")
str2 = Replace(str2, qiStr2, "&|")

str2 = Replace(str2, Chr(34), "&#34;")

str2 = Replace(str2, "&||&", """""")
str2 = Replace(str2, "|&|", qiStr)
str2 = Replace(str2, "|&", qiStr1)
str2 = Replace(str2, "&|", qiStr2)

Return (str1 & str2 & str3)
End Function

Am I misunderstanding the outer join?

As I understand it, a left outer join should include records from the left table which match the where clauses for that table whether or not there are records from the right table that match the where clauses for that table.

CREATE TABLE dbo.Test1 (
testnumber varchar (10) NOT NULL
) ON PRIMARY
GO

CREATE TABLE dbo.Testlink (
testnumber varchar (10) NOT NULL ,
testdate datetime NOT NULL
) ON PRIMARY
GO

INSERT into dbo.Test1 values ('0051419')
GO

INSERT into dbo.Testlink values ('0051419','2004-11-22')
GO
INSERT into dbo.Testlink values ('0051419','2004-11-29')
GO
INSERT into dbo.Testlink values ('0051419','2004-12-22')
GO

select test1.testnumber, testlink.testdate
from dbo.test1 left outer join dbo.testlink on test1.testnumber=testlink.testnumber
where test1.testnumber='0051419'

returns, as expected,

testnumber testdate
---- ----------------
0051419 2004-11-22 00:00:00.000
0051419 2004-11-29 00:00:00.000
0051419 2004-12-22 00:00:00.000

(3 row(s) affected)

adding a where clause that results in no records matching in testlink however, results in no records being returned-but I expected the record from test1 being returned with a null value in testdate. Am I misunderstanding what should be happening? Or am I simply doing something wrong?

select test1.testnumber, testlink.testdate
from dbo.test1 left outer join dbo.testlink on test1.testnumber=testlink.testnumber
where test1.testnumber='0051419'
and testlink.testdate > '10/16/2006'

testnumber testdate
---- ----------------

(0 row(s) affected)

In the report I'm creating I do need to report the test1 records that meet the selection criteria even when there are no records in testlink that meet the selection criteria for that table.

Thanks.Yes - you are missing something. Everything you say is correct until you get to the bit about ALWAYS returning all rows from the left table that meet the where clause. You can think of the where clause being applied AFTER the join. You effectively want to apply the filter on testlink BEFORE the join. You can use a Derived Table or you can rely on a handy bit of syntax:
select test1.testnumber, testlink.testdate
from dbo.test1 left outer join dbo.testlink on test1.testnumber=testlink.testnumber
and testlink.testdate > '10/16/2006'
where test1.testnumber='0051419'
Putting the expression in the join syntax applies the filter before the left join is applied and should result in your required output.

HTH|||Hmmm. Thanks-that answers my question. It doesn't quite solve my problem but that's because I didn't sufficiently outline the circumstances-and I'm not sure the remaining issue is right for this forum. The remaining problem is that I'm using Crystal Reports & don't see an option for comparing a field to a constant value in defining the link (join). I'll go out to the Crystal site & see if I can get an answer to that question there.

Thanks a lot for explaining what I was missing.

Am I missing something when trying to connect via ASP.NET 2.0?

I am at my wit's end trying to set up a simple test to retrieve some records from with ASP.NET and C#. I'm using the Express Edition on my development laptop. Whenever I try to run my application, I get the following error:

ERROR: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I have researched this problem, and done everything I can find online to fix it but no avail. I have gone to SQL Server Configuration Manager and enabled Named Pipes and TCP/IP. I have stopped and restarted the server. Local and Remote connections (Both TCP/IP and Named Pipes) are enabled in the Surface Area Configuration. I added it to the Exceptions list of the Windows firewall. And yet nothing works. I can connect to it fine with Management Studio Express, but I am totally unable to connect to the database from within my code. I've spent probably about two weeks wracking my brain trying to figure this out; does anybody know what might be causing the problem and what could fix it?what does your connection string look like.|||"Server=ORPHEUS;Database=TestDB;User ID=sa;Password=mypass;Trusted_connection=False"

I've also tried specifyinig the instance of SQL Server (e.g. Server=ORPHEUS\SQLExpress) but that does not work either.
|||

If you're trying to connect to an instance name, then i'm pretty sure you have to specify it in your connection string.

Have you tried using the examples in the docs, like this one? http://msdn2.microsoft.com/en-us/library/ms247257.aspx

|||Well what do you know... turns out I wasn't specifying it in the right format.. I should have had it was "Database=ORPHEUS\\SQLEXPRESS".

Thanks a ton! Sometimes its the most obvious things that you end up missing...

Alternative to cursor.

Hi all,
I want to get data from table and do some processing depending on
records of table.
Do i need to use cursor. Or is there any other alternative available
for this.
Any suggestion would be truely appreciated.
thanks in advance.<trialproduct2004@.yahoo.com> wrote in message
news:1160140929.983520.124390@.i3g2000cwc.googlegroups.com...
> Hi all,
> I want to get data from table and do some processing depending on
> records of table.
> Do i need to use cursor. Or is there any other alternative available
> for this.
> Any suggestion would be truely appreciated.
> thanks in advance.
>
That depends on the type of processing you need to do. In general, using
set based solutions rather than procedural (cursor) solutions is faster in
SQL Server.
If you give some specifics of your problem and the desired results etc. we
can be more helpful here.
Check out: http://www.aspfaq.com/etiquette.asp?id=5006
Rick Sawtell|||what specifically do you want to do?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
Make SQL Server faster - www.quicksqlserver.com
___________________________________
<trialproduct2004@.yahoo.com> wrote in message
news:1160140929.983520.124390@.i3g2000cwc.googlegroups.com...
> Hi all,
> I want to get data from table and do some processing depending on
> records of table.
> Do i need to use cursor. Or is there any other alternative available
> for this.
> Any suggestion would be truely appreciated.
> thanks in advance.
>|||Try to avoid cursors as they in general don't offer the performance that set
based operations do. Try to do a set based operation if possible.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<trialproduct2004@.yahoo.com> wrote in message
news:1160140929.983520.124390@.i3g2000cwc.googlegroups.com...
> Hi all,
> I want to get data from table and do some processing depending on
> records of table.
> Do i need to use cursor. Or is there any other alternative available
> for this.
> Any suggestion would be truely appreciated.
> thanks in advance.
>

Alternative to cursor in trigger?

If the update trigger returns an "inserted" table with multiple records,
is there any way to address each record individually without using a
cursor? The code below is my solution using a cursor but the DBA says
no cursors. Thank you for your help.
/* Assume "inserted" table returned multiple records */
OPEN ins_cursor
FETCH NEXT FROM ins_cursor INTO @.emailAddress, @.emailBody, @.emailSubject
WHILE @.@.FETCH_STATUS = 0
BEGIN
/* **Pseudo code for sending email to address in each record returned**
xp_sendmail
emailTo = @.emailAddress
emailSubject = @.emailSubject
emailBody = @.emailBody
****************************************
*** */
/* Write log entry for each individual email sent*/
INSERT INTO NotifyLog
(
emailTo, emailSubject, emailBody
)
Values
(
@.emailTo, @.emailSubject, @.emailBody
)
FETCH NEXT FROM ins_cursor INTO @.emailAddress, @.emailBody, @.emailSubject
END
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!I don't think there is a way to do this as requested without a cursor. The
"bad" part here isn't the word "cursor"... you have to call a stored
procedure for every single row, and whether you use a cursor or some other
fetch mechanism, you're still going to have to do the painful, iterative
approach of looping through each row, one at a time.
My suggestion: Use a scheduled job and perform this kind of row-by-row
activity there (you can mark rows as updated in the trigger, by joining the
real table against inserted on the primary key, and then un-mark each row as
the job sends each e-mail). Surely a five or two-minute interval will be
close enough to real time, without hogging all the performance it takes to
hold the transaction open while all that mail is sent (ugh).
http://www.aspfaq.com/
(Reverse address to reply.)
"Georgia" <xout@.deleted.gov> wrote in message
news:u27zY$cIFHA.3336@.TK2MSFTNGP10.phx.gbl...
> If the update trigger returns an "inserted" table with multiple records,
> is there any way to address each record individually without using a
> cursor? The code below is my solution using a cursor but the DBA says
> no cursors. Thank you for your help.
> /* Assume "inserted" table returned multiple records */
> OPEN ins_cursor
> FETCH NEXT FROM ins_cursor INTO @.emailAddress, @.emailBody, @.emailSubject
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> /* **Pseudo code for sending email to address in each record returned**
> xp_sendmail
> emailTo = @.emailAddress
> emailSubject = @.emailSubject
> emailBody = @.emailBody
> ****************************************
*** */
> /* Write log entry for each individual email sent*/
> INSERT INTO NotifyLog
> (
> emailTo, emailSubject, emailBody
> )
> Values
> (
> @.emailTo, @.emailSubject, @.emailBody
> )
> FETCH NEXT FROM ins_cursor INTO @.emailAddress, @.emailBody, @.emailSubject
> END
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Sure, use a locally defined Table variable...
Declare @.EMs Table (PKID Integer Primary Key Not Null)
Insert @.EMs (PKID) Select <PrimaryKey> From inserted
Declare @.PKid Integer
While Exists (Select * From @.EMs)
Begin
Select @.PKid = Max(PKID) From @.EMs
INSERT INTO NotifyLog
(emailTo, emailSubject, emailBody)
Select emailTo, emailSubject, emailBody
From inserted Where <PrimaryKey> = @.PKid
-- --
Delete @.EMs Where PKID = @.PKiid
End
But why not use a set based statement that "Inserts" the entire set of
records directly from the inserted table into NotifyLog table
INSERT INTO NotifyLog
(emailTo, emailSubject, emailBody)
Select emailTo, emailSubject, emailBody
From inserted
"Georgia" wrote:

> If the update trigger returns an "inserted" table with multiple records,
> is there any way to address each record individually without using a
> cursor? The code below is my solution using a cursor but the DBA says
> no cursors. Thank you for your help.
> /* Assume "inserted" table returned multiple records */
> OPEN ins_cursor
> FETCH NEXT FROM ins_cursor INTO @.emailAddress, @.emailBody, @.emailSubject
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> /* **Pseudo code for sending email to address in each record returned**
> xp_sendmail
> emailTo = @.emailAddress
> emailSubject = @.emailSubject
> emailBody = @.emailBody
> ****************************************
*** */
> /* Write log entry for each individual email sent*/
> INSERT INTO NotifyLog
> (
> emailTo, emailSubject, emailBody
> )
> Values
> (
> @.emailTo, @.emailSubject, @.emailBody
> )
> FETCH NEXT FROM ins_cursor INTO @.emailAddress, @.emailBody, @.emailSubject
> END
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>|||Don't send email notifications from a trigger. There are a number of
reasons.
1. Email is an inherently asynchronous medium so it is unnecessary and
inefficient to hold a transaction open for the duration of sending
mail.
2. If the trigger is fired inside a transaction that later rolls back
you will have sent a notification for an update that never happened.
3. If the mail server is unavailable or causes a timeout or the
notification process fails for any other reason then either you have to
prevent the update or you have to go ahead without sending a
notification. Do you really want to make the mail server a critical
point of failure for your app?
4. Yes, you'll need a cursor. Not desirable in a trigger.
For these reasons I would second Aaron's suggestion: use some other
process outside a trigger to send notifications.
David Portas
SQL Server MVP
--|||
> Sure, use a locally defined Table variable...
> Declare @.EMs Table (PKID Integer Primary Key Not Null)
> Insert @.EMs (PKID) Select <PrimaryKey> From inserted
> Declare @.PKid Integer
> While Exists (Select * From @.EMs)
> Begin
> Select @.PKid = Max(PKID) From @.EMs
> INSERT INTO NotifyLog
> (emailTo, emailSubject, emailBody)
> Select emailTo, emailSubject, emailBody
> From inserted Where <PrimaryKey> = @.PKid
> -- --
> Delete @.EMs Where PKID = @.PKiid
> End
And for the OP's benefit, this is exactly what I meant by not using an
explicit cursor but still going through the process row-by-row, which is
kind of like a wolf in sheep's clothing. In other words, neither DECLARE
CURSOR nor WHILE EXISTS/DELETE is something you're going to want to have in
a trigger.|||Why Not '
"Aaron [SQL Server MVP]" wrote:

>
> And for the OP's benefit, this is exactly what I meant by not using an
> explicit cursor but still going through the process row-by-row, which is
> kind of like a wolf in sheep's clothing. In other words, neither DECLARE
> CURSOR nor WHILE EXISTS/DELETE is something you're going to want to have i
n
> a trigger.
>
>|||Because he's going to use a cursor, or some other looping mechanism like the
one you've provided, to send mail to each recipient in the inserted table.
If you're asking why that's a bad idea, I take it you don't have much
experience with sending mail from SQL Server, and/or having transactions
wait for and/or depend on it.
http://www.aspfaq.com/
(Reverse address to reply.)
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:99713C31-6A95-445E-BB64-4D0EB353A8C6@.microsoft.com...
> Why Not '|||You'd be wrong, as well as impolite... I know about that... and I agree...
but you isaid
In other words, neither DECLARE
CURSOR nor WHILE EXISTS/DELETE is something you're going to want to have in
a trigger.
<<<<<<<<<<<<<<<<<<<<<<
Other than the obvious, (re: looping through the records rather than dealing
with them as a set) Do you have anything to teach me about using such a loo
p
in a trigger?
"Aaron [SQL Server MVP]" wrote:
> Because he's going to use a cursor, or some other looping mechanism like t
he
> one you've provided, to send mail to each recipient in the inserted table.
> If you're asking why that's a bad idea, I take it you don't have much
> experience with sending mail from SQL Server, and/or having transactions
> wait for and/or depend on it.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:99713C31-6A95-445E-BB64-4D0EB353A8C6@.microsoft.com...
>
>|||I agree. Use the trigger to post email send requests to another de-coupled
table storing the queue of emails to be sent. Use a second process, or job
to inspect the de-coupled table fro unsent emails and send them from the
second process. This will keep the email process from occurring within the
transaction.
fyi, the Service Broker in Yukon is a perfect queue, but y9ou can accomplish
the same objective in SQL Server 2K.
-Paul Nielsen, SQL Server MVP
www.sqlserverbible.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110063641.901975.135140@.z14g2000cwz.googlegroups.com...
> Don't send email notifications from a trigger. There are a number of
> reasons.
> 1. Email is an inherently asynchronous medium so it is unnecessary and
> inefficient to hold a transaction open for the duration of sending
> mail.
> 2. If the trigger is fired inside a transaction that later rolls back
> you will have sent a notification for an update that never happened.
> 3. If the mail server is unavailable or causes a timeout or the
> notification process fails for any other reason then either you have to
> prevent the update or you have to go ahead without sending a
> notification. Do you really want to make the mail server a critical
> point of failure for your app?
> 4. Yes, you'll need a cursor. Not desirable in a trigger.
> For these reasons I would second Aaron's suggestion: use some other
> process outside a trigger to send notifications.
> --
> David Portas
> SQL Server MVP
> --
>|||
The real problem here is not the cursor, it's sending mail in a trigger.
The mail is not transactional and it will slow down your transactions.
A better approach is to insert all the rows into your NotifyLog with a sent
flag. Then use a job to open a cursor on the NotifyLog for rows with
sent=0. Since its a background job using a cursor is no big deal. The
important thing is that the email will not be sent if the transaction is
rolled back.
INSERT INTO NotifyLog
(emailTo, emailSubject, emailBody, sent)
SELECT xxx emailTo, xxx emailSubject, xxx emailBody, 0
Then later
begin transaction
declare @.emailToSend table(id int, emailTo varchar(200) ...)
INSERT INTO @.emailToSend
(id, emailTo, emailSubject, emailBody)
select int, emailTo, emailSubject,emailBody
from NotifyLog (updlock,holdlock)
where sent = 0
update NotifyLog set sent = 1
where id in (select id from @.emailToSend)
commit transaction
//send all the emails
David