where is the actual physical location of views, sp's and dts packages?
The same place as was mention when you asked this question 4 times ago. :-)
"mat" wrote:
> where is the actual physical location of views, sp's and dts packages?
Showing posts with label physical. Show all posts
Showing posts with label physical. Show all posts
Thursday, March 22, 2012
architecture
where is the actual physical location of views, sp's and dts packages?
Views and stored procs are objects within a database. Their definitions are
stored in sysobjects, syscomments, and others system tables within their
database. Physical storage is in the data files.
DTS package definitions are stored in msdb. Look for system tables named
like 'sysdts%'. Physical storage will be in the msdb data file.
DTS packages
"mat" wrote:
> where is the actual physical location of views, sp's and dts packages?
sql
Views and stored procs are objects within a database. Their definitions are
stored in sysobjects, syscomments, and others system tables within their
database. Physical storage is in the data files.
DTS package definitions are stored in msdb. Look for system tables named
like 'sysdts%'. Physical storage will be in the msdb data file.
DTS packages
"mat" wrote:
> where is the actual physical location of views, sp's and dts packages?
sql
architecture
where is the actual physical location of views, sp's and dts packages?
as far as I know, views and sps are part of your database file (.mdf) stored
as objects with the system tables. Typically a view is thought of as a
virtual table, or a stored query. The results of using a view are not
permanently stored in the database. The data accessed through a view is
actually constructed using standard T-SQL select command and can come from
one to many different base tables or even other views.
I hope this helps...
"mat" wrote:
> where is the actual physical location of views, sp's and dts packages?
as far as I know, views and sps are part of your database file (.mdf) stored
as objects with the system tables. Typically a view is thought of as a
virtual table, or a stored query. The results of using a view are not
permanently stored in the database. The data accessed through a view is
actually constructed using standard T-SQL select command and can come from
one to many different base tables or even other views.
I hope this helps...
"mat" wrote:
> where is the actual physical location of views, sp's and dts packages?
architecture
where is the actual physical location of views, sp's and dts packages?Views and stored procs are objects within a database. Their definitions are
stored in sysobjects, syscomments, and others system tables within their
database. Physical storage is in the data files.
DTS package definitions are stored in msdb. Look for system tables named
like 'sysdts%'. Physical storage will be in the msdb data file.
DTS packages
"mat" wrote:
> where is the actual physical location of views, sp's and dts packages?
stored in sysobjects, syscomments, and others system tables within their
database. Physical storage is in the data files.
DTS package definitions are stored in msdb. Look for system tables named
like 'sysdts%'. Physical storage will be in the msdb data file.
DTS packages
"mat" wrote:
> where is the actual physical location of views, sp's and dts packages?
architecture
where is the actual physical location of views, sp's and dts packages?Views and stored procs are objects within a database. Their definitions are
stored in sysobjects, syscomments, and others system tables within their
database. Physical storage is in the data files.
DTS package definitions are stored in msdb. Look for system tables named
like 'sysdts%'. Physical storage will be in the msdb data file.
DTS packages
"mat" wrote:
> where is the actual physical location of views, sp's and dts packages?
stored in sysobjects, syscomments, and others system tables within their
database. Physical storage is in the data files.
DTS package definitions are stored in msdb. Look for system tables named
like 'sysdts%'. Physical storage will be in the msdb data file.
DTS packages
"mat" wrote:
> where is the actual physical location of views, sp's and dts packages?
architecture
where is the actual physical location of views, sp's and dts packages?as far as I know, views and sps are part of your database file (.mdf) stored
as objects with the system tables. Typically a view is thought of as a
virtual table, or a stored query. The results of using a view are not
permanently stored in the database. The data accessed through a view is
actually constructed using standard T-SQL select command and can come from
one to many different base tables or even other views.
I hope this helps...
"mat" wrote:
> where is the actual physical location of views, sp's and dts packages?
as objects with the system tables. Typically a view is thought of as a
virtual table, or a stored query. The results of using a view are not
permanently stored in the database. The data accessed through a view is
actually constructed using standard T-SQL select command and can come from
one to many different base tables or even other views.
I hope this helps...
"mat" wrote:
> where is the actual physical location of views, sp's and dts packages?
architecture
where is the actual physical location of views, sp's and dts packages?The same place as was mention when you asked this question 4 times ago. :-)
"mat" wrote:
> where is the actual physical location of views, sp's and dts packages?
"mat" wrote:
> where is the actual physical location of views, sp's and dts packages?
Architectural Design Question ?
We currently have a number of SSAS 2000 physical cubes and a single KPI virtual cube that our finance department reports from.A colleague is prototyping a new approach in SSAS 2005 and I’m looking for some feedback.
Please see http://www.dailyware.com/SSAS2005Design.jpg for a high level overview.The lines in red illustrate custom .Net applications that push aggregated data from cubes to some other reporting source (SQL tables, XML?) that our finance department will report from.
Thought that come to my mind include: many points of failure, significant custom development required, none-mainstream, high maintenance cost….
Thanks,
Gary
Same as in AS2000 , in AS2005 you can consolidate several different cubes into a single one using new feature called Linked Measure groups.
Another point here is: you should look into providing your end users not only with static reports, but give them ability for Ad-hoc analysis of your data. There are quite a few applications talking directly to Analysis Services.
For instance, you will see great improvements in Pivot Tables in upcoming release of Office 2007.
As for the building redundancy into your system, you can use ether NLB clustering or MSCS ( microsoft clustering services ) solutions.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
I agree...my concern with the proposed design is the fact that it will make it difficult to use several tools coming out later this year. Moreover, our users have requirements to drill into data and this model makes that difficult.
Thanks for the input,
Gary
|||Let me ask another question....do you consider the attached design to be very non-mainstream and possibly difficult to support?|||To save maitanance costs assosiated with having many cubes, many customers choose to create a single cube ( where possible) with multiple measure groups.
This is one of the main advantages switiching to AS2005, you can bring several fact tables into a single cube and point your reports and client appications to it.
You would start splitting cube into separate once if your calculation logic becomes complex and begins to slow down query performance.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Hi Gary,
Edward already provided detailed feedback - I would just add that, what jumped out at me was the KPI (SQL/XML) layer between the cubes and reporting applications. What is its purpose - you can easily create static reports directly from cubes, in addition to the ad hoc analytic capabilities that Edward mentioned?
What I can conceive of, in the context of KPI's, is the need for supporting metadata beyond what is provided for KPI's in AS 2005. Things like the role of a KPI within the framework of a customized Performance Management Scorecard - an example of this would be the Business Scorecard Manager:
http://office.microsoft.com/en-us/assistance/HA012225141033.aspx
Friday, February 24, 2012
Application architecture
Hi all,
I am developing a production optimization application. The application is a
distributed application with several physical machines doing things like
measurements, calculations and control and they all communicate through
TCP/IP using .Net remoting.
A requirement for the application is that it should be able to process 100
items / minute.
I am interested in using SQL server to store data about my produced items
and also about the system itself. For instance storing performance counters
from the different machines.
The logging to the database is not a critical feature of the system and it's
important that the writing to the database does not harm the performance of
other more critical functions in the system.
To handle the communication with the database I have a few altenative
architectures that I would like you to comment on (or please feel free to
suggest any other solutions)
1. Use a single SQL server that all machines on the network communicate
directly to. Should I use asynchrounous write to limit the impact on the
writing system?
2. Use separate instances on each machine (SQL Express) and then copy or
move the data when the system is idle.
3. Use a middle layer that resides on a separate machine that handles all
the communication to the SQL server. Essentially dump all messages on this
resource and write them from here when there is an opportunity. All problems
only affect this machine.
I would Like to use alternative 1 but I'm concerned about the availiabilty.
I have had problems before with another application that writes to an SQL
server over an office network where the SQL server does not respond (or
responds slowly) and cause performance degrading in my application.
Please give me some advice on this matter
Best regards
Henrik
SQL Server will cache any new data or data changes and handle the disk
writing on its own time.
If you are inserting new data, then the operation will be very fast.
(Sometimes, when updating existing data, slowness can be encountered because
other users may be locking and/or blocking the data. I have production
systems that are capable of accepting new data inserts at a rate of
thousands per second -so your requirement of hundreds per minute will be no
issue for SQL Server.
The main issue affecting the speed you desire is that the location of the
Transaction Log file (*.ldf) should be on a dedicated drive or array. The
log file writes all changes, serialized, and any other activity on the drive
will move the drive heads, and then they have to return to the last location
to write the next piece of data. If there are no other activities on the
drive, then the drive heads are 'always' in the right location, and the
speed is optimal.
Choice #1, and do not bother trying async writes.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Henrik" <henrik_@.community.nospam> wrote in message
news:7188099B-03F9-4A01-9C9D-5BD41CD1234E@.microsoft.com...
> Hi all,
> I am developing a production optimization application. The application is
> a
> distributed application with several physical machines doing things like
> measurements, calculations and control and they all communicate through
> TCP/IP using .Net remoting.
> A requirement for the application is that it should be able to process 100
> items / minute.
> I am interested in using SQL server to store data about my produced items
> and also about the system itself. For instance storing performance
> counters
> from the different machines.
> The logging to the database is not a critical feature of the system and
> it's
> important that the writing to the database does not harm the performance
> of
> other more critical functions in the system.
> To handle the communication with the database I have a few altenative
> architectures that I would like you to comment on (or please feel free to
> suggest any other solutions)
> 1. Use a single SQL server that all machines on the network communicate
> directly to. Should I use asynchrounous write to limit the impact on the
> writing system?
> 2. Use separate instances on each machine (SQL Express) and then copy or
> move the data when the system is idle.
> 3. Use a middle layer that resides on a separate machine that handles all
> the communication to the SQL server. Essentially dump all messages on this
> resource and write them from here when there is an opportunity. All
> problems
> only affect this machine.
> I would Like to use alternative 1 but I'm concerned about the
> availiabilty.
> I have had problems before with another application that writes to an SQL
> server over an office network where the SQL server does not respond (or
> responds slowly) and cause performance degrading in my application.
> Please give me some advice on this matter
> Best regards
> Henrik
>
>
|||Hello Henrik,
I'd also vote for alternative 1 since it is most simple and efficient. As
Arnie said, SQL itself cache data writing. If there is no blocking issues
on the server, its preformance shall be very good. You may want to adjust
your application/Stored procedures/queries carefully to avoid/alleviate
blocking issues so that SQL Server can keep quick response time, especailly
when multiple clients are inserting/updating same tables at same time to
SQL Server.
Also, performance tuning of SQL Server itself is important for better
response time.
224453 INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking
Problems
<http://support.microsoft.com/?id=224453>
271509 INF: How to Monitor SQL Server 2000 Blocking
<http://support.microsoft.com/?id=271509>
Q224587 INF: Troubleshooting Application Performance with SQL Server
<http://support.microsoft.com/support/kb/articles/q224/5/87.asp>
TechNet Support WebCast:Performance troubleshooting and analysis in
Microsoft SQL Server 2000
http://support.microsoft.com/?id=838622
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx#EYBAG
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
I am developing a production optimization application. The application is a
distributed application with several physical machines doing things like
measurements, calculations and control and they all communicate through
TCP/IP using .Net remoting.
A requirement for the application is that it should be able to process 100
items / minute.
I am interested in using SQL server to store data about my produced items
and also about the system itself. For instance storing performance counters
from the different machines.
The logging to the database is not a critical feature of the system and it's
important that the writing to the database does not harm the performance of
other more critical functions in the system.
To handle the communication with the database I have a few altenative
architectures that I would like you to comment on (or please feel free to
suggest any other solutions)
1. Use a single SQL server that all machines on the network communicate
directly to. Should I use asynchrounous write to limit the impact on the
writing system?
2. Use separate instances on each machine (SQL Express) and then copy or
move the data when the system is idle.
3. Use a middle layer that resides on a separate machine that handles all
the communication to the SQL server. Essentially dump all messages on this
resource and write them from here when there is an opportunity. All problems
only affect this machine.
I would Like to use alternative 1 but I'm concerned about the availiabilty.
I have had problems before with another application that writes to an SQL
server over an office network where the SQL server does not respond (or
responds slowly) and cause performance degrading in my application.
Please give me some advice on this matter
Best regards
Henrik
SQL Server will cache any new data or data changes and handle the disk
writing on its own time.
If you are inserting new data, then the operation will be very fast.
(Sometimes, when updating existing data, slowness can be encountered because
other users may be locking and/or blocking the data. I have production
systems that are capable of accepting new data inserts at a rate of
thousands per second -so your requirement of hundreds per minute will be no
issue for SQL Server.
The main issue affecting the speed you desire is that the location of the
Transaction Log file (*.ldf) should be on a dedicated drive or array. The
log file writes all changes, serialized, and any other activity on the drive
will move the drive heads, and then they have to return to the last location
to write the next piece of data. If there are no other activities on the
drive, then the drive heads are 'always' in the right location, and the
speed is optimal.
Choice #1, and do not bother trying async writes.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Henrik" <henrik_@.community.nospam> wrote in message
news:7188099B-03F9-4A01-9C9D-5BD41CD1234E@.microsoft.com...
> Hi all,
> I am developing a production optimization application. The application is
> a
> distributed application with several physical machines doing things like
> measurements, calculations and control and they all communicate through
> TCP/IP using .Net remoting.
> A requirement for the application is that it should be able to process 100
> items / minute.
> I am interested in using SQL server to store data about my produced items
> and also about the system itself. For instance storing performance
> counters
> from the different machines.
> The logging to the database is not a critical feature of the system and
> it's
> important that the writing to the database does not harm the performance
> of
> other more critical functions in the system.
> To handle the communication with the database I have a few altenative
> architectures that I would like you to comment on (or please feel free to
> suggest any other solutions)
> 1. Use a single SQL server that all machines on the network communicate
> directly to. Should I use asynchrounous write to limit the impact on the
> writing system?
> 2. Use separate instances on each machine (SQL Express) and then copy or
> move the data when the system is idle.
> 3. Use a middle layer that resides on a separate machine that handles all
> the communication to the SQL server. Essentially dump all messages on this
> resource and write them from here when there is an opportunity. All
> problems
> only affect this machine.
> I would Like to use alternative 1 but I'm concerned about the
> availiabilty.
> I have had problems before with another application that writes to an SQL
> server over an office network where the SQL server does not respond (or
> responds slowly) and cause performance degrading in my application.
> Please give me some advice on this matter
> Best regards
> Henrik
>
>
|||Hello Henrik,
I'd also vote for alternative 1 since it is most simple and efficient. As
Arnie said, SQL itself cache data writing. If there is no blocking issues
on the server, its preformance shall be very good. You may want to adjust
your application/Stored procedures/queries carefully to avoid/alleviate
blocking issues so that SQL Server can keep quick response time, especailly
when multiple clients are inserting/updating same tables at same time to
SQL Server.
Also, performance tuning of SQL Server itself is important for better
response time.
224453 INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking
Problems
<http://support.microsoft.com/?id=224453>
271509 INF: How to Monitor SQL Server 2000 Blocking
<http://support.microsoft.com/?id=271509>
Q224587 INF: Troubleshooting Application Performance with SQL Server
<http://support.microsoft.com/support/kb/articles/q224/5/87.asp>
TechNet Support WebCast:Performance troubleshooting and analysis in
Microsoft SQL Server 2000
http://support.microsoft.com/?id=838622
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx#EYBAG
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Application architecture
Hi all,
I am developing a production optimization application. The application is a
distributed application with several physical machines doing things like
measurements, calculations and control and they all communicate through
TCP/IP using .Net remoting.
A requirement for the application is that it should be able to process 100
items / minute.
I am interested in using SQL server to store data about my produced items
and also about the system itself. For instance storing performance counters
from the different machines.
The logging to the database is not a critical feature of the system and it's
important that the writing to the database does not harm the performance of
other more critical functions in the system.
To handle the communication with the database I have a few altenative
architectures that I would like you to comment on (or please feel free to
suggest any other solutions)
1. Use a single SQL server that all machines on the network communicate
directly to. Should I use asynchrounous write to limit the impact on the
writing system?
2. Use separate instances on each machine (SQL Express) and then copy or
move the data when the system is idle.
3. Use a middle layer that resides on a separate machine that handles all
the communication to the SQL server. Essentially dump all messages on this
resource and write them from here when there is an opportunity. All problems
only affect this machine.
I would Like to use alternative 1 but I'm concerned about the availiabilty.
I have had problems before with another application that writes to an SQL
server over an office network where the SQL server does not respond (or
responds slowly) and cause performance degrading in my application.
Please give me some advice on this matter
Best regards
HenrikSQL Server will cache any new data or data changes and handle the disk
writing on its own time.
If you are inserting new data, then the operation will be very fast.
(Sometimes, when updating existing data, slowness can be encountered because
other users may be locking and/or blocking the data. I have production
systems that are capable of accepting new data inserts at a rate of
thousands per second -so your requirement of hundreds per minute will be no
issue for SQL Server.
The main issue affecting the speed you desire is that the location of the
Transaction Log file (*.ldf) should be on a dedicated drive or array. The
log file writes all changes, serialized, and any other activity on the drive
will move the drive heads, and then they have to return to the last location
to write the next piece of data. If there are no other activities on the
drive, then the drive heads are 'always' in the right location, and the
speed is optimal.
Choice #1, and do not bother trying async writes.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Henrik" <henrik_@.community.nospam> wrote in message
news:7188099B-03F9-4A01-9C9D-5BD41CD1234E@.microsoft.com...
> Hi all,
> I am developing a production optimization application. The application is
> a
> distributed application with several physical machines doing things like
> measurements, calculations and control and they all communicate through
> TCP/IP using .Net remoting.
> A requirement for the application is that it should be able to process 100
> items / minute.
> I am interested in using SQL server to store data about my produced items
> and also about the system itself. For instance storing performance
> counters
> from the different machines.
> The logging to the database is not a critical feature of the system and
> it's
> important that the writing to the database does not harm the performance
> of
> other more critical functions in the system.
> To handle the communication with the database I have a few altenative
> architectures that I would like you to comment on (or please feel free to
> suggest any other solutions)
> 1. Use a single SQL server that all machines on the network communicate
> directly to. Should I use asynchrounous write to limit the impact on the
> writing system?
> 2. Use separate instances on each machine (SQL Express) and then copy or
> move the data when the system is idle.
> 3. Use a middle layer that resides on a separate machine that handles all
> the communication to the SQL server. Essentially dump all messages on this
> resource and write them from here when there is an opportunity. All
> problems
> only affect this machine.
> I would Like to use alternative 1 but I'm concerned about the
> availiabilty.
> I have had problems before with another application that writes to an SQL
> server over an office network where the SQL server does not respond (or
> responds slowly) and cause performance degrading in my application.
> Please give me some advice on this matter
> Best regards
> Henrik
>
>|||Hello Henrik,
I'd also vote for alternative 1 since it is most simple and efficient. As
Arnie said, SQL itself cache data writing. If there is no blocking issues
on the server, its preformance shall be very good. You may want to adjust
your application/Stored procedures/queries carefully to avoid/alleviate
blocking issues so that SQL Server can keep quick response time, especailly
when multiple clients are inserting/updating same tables at same time to
SQL Server.
Also, performance tuning of SQL Server itself is important for better
response time.
224453 INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking
Problems
<http://support.microsoft.com/?id=224453>
271509 INF: How to Monitor SQL Server 2000 Blocking
<http://support.microsoft.com/?id=271509>
Q224587 INF: Troubleshooting Application Performance with SQL Server
<http://support.microsoft.com/suppor...s/q224/5/87.asp>
TechNet Support WebCast:Performance troubleshooting and analysis in
Microsoft SQL Server 2000
http://support.microsoft.com/?id=838622
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/pr...fprb.mspx#EYBAG
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
I am developing a production optimization application. The application is a
distributed application with several physical machines doing things like
measurements, calculations and control and they all communicate through
TCP/IP using .Net remoting.
A requirement for the application is that it should be able to process 100
items / minute.
I am interested in using SQL server to store data about my produced items
and also about the system itself. For instance storing performance counters
from the different machines.
The logging to the database is not a critical feature of the system and it's
important that the writing to the database does not harm the performance of
other more critical functions in the system.
To handle the communication with the database I have a few altenative
architectures that I would like you to comment on (or please feel free to
suggest any other solutions)
1. Use a single SQL server that all machines on the network communicate
directly to. Should I use asynchrounous write to limit the impact on the
writing system?
2. Use separate instances on each machine (SQL Express) and then copy or
move the data when the system is idle.
3. Use a middle layer that resides on a separate machine that handles all
the communication to the SQL server. Essentially dump all messages on this
resource and write them from here when there is an opportunity. All problems
only affect this machine.
I would Like to use alternative 1 but I'm concerned about the availiabilty.
I have had problems before with another application that writes to an SQL
server over an office network where the SQL server does not respond (or
responds slowly) and cause performance degrading in my application.
Please give me some advice on this matter
Best regards
HenrikSQL Server will cache any new data or data changes and handle the disk
writing on its own time.
If you are inserting new data, then the operation will be very fast.
(Sometimes, when updating existing data, slowness can be encountered because
other users may be locking and/or blocking the data. I have production
systems that are capable of accepting new data inserts at a rate of
thousands per second -so your requirement of hundreds per minute will be no
issue for SQL Server.
The main issue affecting the speed you desire is that the location of the
Transaction Log file (*.ldf) should be on a dedicated drive or array. The
log file writes all changes, serialized, and any other activity on the drive
will move the drive heads, and then they have to return to the last location
to write the next piece of data. If there are no other activities on the
drive, then the drive heads are 'always' in the right location, and the
speed is optimal.
Choice #1, and do not bother trying async writes.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Henrik" <henrik_@.community.nospam> wrote in message
news:7188099B-03F9-4A01-9C9D-5BD41CD1234E@.microsoft.com...
> Hi all,
> I am developing a production optimization application. The application is
> a
> distributed application with several physical machines doing things like
> measurements, calculations and control and they all communicate through
> TCP/IP using .Net remoting.
> A requirement for the application is that it should be able to process 100
> items / minute.
> I am interested in using SQL server to store data about my produced items
> and also about the system itself. For instance storing performance
> counters
> from the different machines.
> The logging to the database is not a critical feature of the system and
> it's
> important that the writing to the database does not harm the performance
> of
> other more critical functions in the system.
> To handle the communication with the database I have a few altenative
> architectures that I would like you to comment on (or please feel free to
> suggest any other solutions)
> 1. Use a single SQL server that all machines on the network communicate
> directly to. Should I use asynchrounous write to limit the impact on the
> writing system?
> 2. Use separate instances on each machine (SQL Express) and then copy or
> move the data when the system is idle.
> 3. Use a middle layer that resides on a separate machine that handles all
> the communication to the SQL server. Essentially dump all messages on this
> resource and write them from here when there is an opportunity. All
> problems
> only affect this machine.
> I would Like to use alternative 1 but I'm concerned about the
> availiabilty.
> I have had problems before with another application that writes to an SQL
> server over an office network where the SQL server does not respond (or
> responds slowly) and cause performance degrading in my application.
> Please give me some advice on this matter
> Best regards
> Henrik
>
>|||Hello Henrik,
I'd also vote for alternative 1 since it is most simple and efficient. As
Arnie said, SQL itself cache data writing. If there is no blocking issues
on the server, its preformance shall be very good. You may want to adjust
your application/Stored procedures/queries carefully to avoid/alleviate
blocking issues so that SQL Server can keep quick response time, especailly
when multiple clients are inserting/updating same tables at same time to
SQL Server.
Also, performance tuning of SQL Server itself is important for better
response time.
224453 INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking
Problems
<http://support.microsoft.com/?id=224453>
271509 INF: How to Monitor SQL Server 2000 Blocking
<http://support.microsoft.com/?id=271509>
Q224587 INF: Troubleshooting Application Performance with SQL Server
<http://support.microsoft.com/suppor...s/q224/5/87.asp>
TechNet Support WebCast:Performance troubleshooting and analysis in
Microsoft SQL Server 2000
http://support.microsoft.com/?id=838622
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/pr...fprb.mspx#EYBAG
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to:
Posts (Atom)