old sql2k5 folder: oltp

Showing posts with label oltp. Show all posts

Friday, March 23, 2012

OLTP vs Reporting Database vs OLAP Database

Dear All,
I have a fundamental questions regarding my design of my SQL Server
Database.
Current SQL Server 2000 Design Setting (in one server)
-1 Database for OLTP
-1 Database for OLAP -> Generating Cubes
Planned SQL Server 2000 Design Setting (in two servers):
-1 Database for OLTP -> at Server A
-1 Database for Reporting Purposes -> at Server B
-1 Database for OLAP -> at Server B
My Question -> Is it necessary to separate reporting database and OLAP
database?
Any sugestion please.
Thanks
Robert Lie
if you want to provide reports based on a copy of your OLTP database, so you
can create a "read-only" database optimized for reporting purpose.
you can provide (near)real time reports.
this usage as an advantage: you don't impat you operationnal server by
executing complex queries. but.. garbage in garbage out...
if you want to have a common and cleansed database for both reporting and
analysis to insure that you'll allways display the same information, use the
same database for RS + OLAP. but, generally, this database in not real-time
updated.
so what is your primary usage for your reports?
- operationnal purpose
- analytical purpose
but you can also provide reports based on the 2 databases regarding the
performance of your queries.
if you have some performance problems with your OLTP database (the copy on
the server B) then the OLAP database and cubes will improove the response
time.
also, look at the number of users... 100 users on a 10seconds report vs 100
users on a 1second report...
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:uD8T74gSFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> I have a fundamental questions regarding my design of my SQL Server
> Database.
> Current SQL Server 2000 Design Setting (in one server)
> -1 Database for OLTP
> -1 Database for OLAP -> Generating Cubes
> Planned SQL Server 2000 Design Setting (in two servers):
> -1 Database for OLTP -> at Server A
> -1 Database for Reporting Purposes -> at Server B
> -1 Database for OLAP -> at Server B
> My Question -> Is it necessary to separate reporting database and OLAP
> database?
> Any sugestion please.
> Thanks
> Robert Lie
|||Here my considerations
1. Reporting Database:
- To provide operational reports with 10 minutes delay.
- Not Disturbing OLTP Server
- Incrase response time for users who access reports (estimation
max 20 users in the same time)
2. OLAP Database:
- To generate Cubes
- To boost the Cubes generation performance since it won't
affected by users who access reports.
- For analytical purpose
And both of them are not real-time.
Please explain further about garbage in garbage out?
So what do you think of my considerations?
Thanks a lot
Robert Lie
Jj wrote:
> if you want to provide reports based on a copy of your OLTP database, so you
> can create a "read-only" database optimized for reporting purpose.
> you can provide (near)real time reports.
> this usage as an advantage: you don't impat you operationnal server by
> executing complex queries. but.. garbage in garbage out...
> if you want to have a common and cleansed database for both reporting and
> analysis to insure that you'll allways display the same information, use the
> same database for RS + OLAP. but, generally, this database in not real-time
> updated.
> so what is your primary usage for your reports?
> - operationnal purpose
> - analytical purpose
> but you can also provide reports based on the 2 databases regarding the
> performance of your queries.
> if you have some performance problems with your OLTP database (the copy on
> the server B) then the OLAP database and cubes will improove the response
> time.
> also, look at the number of users... 100 users on a 10seconds report vs 100
> users on a 1second report...
>
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:uD8T74gSFHA.2520@.TK2MSFTNGP09.phx.gbl...
>
>
|||creating a copy of the OLTP database is good choice
you can use the log shipping function to do this (or other methods)
(how do you plan to synchronize your databases?)
generating cubes will not lock your database if you do incremental updates
on a optimized cube. (or just a litlle lock)
Partitions will help you for the cube process step.
But your design appear to be good.
garbage in garbage out = bad data quality in the source = bad quality in
output (non existant clientID, duplicated product name, duplicated
records...). so an ETL tool can improove the data quality during the
process. or if you want to synchronize multiple sources (like reference
tables and operationnal tables)
But these steps reduce the loading time by adding data cleansing time.
if you think next step (which is SQL 2005) you'll have a lot of new features
to help you.
for example, you will be able to load directly a partition of your cube
while you'll load your database, so 1 read and 2 destinations
(and there is a lot of options)
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23RX3SehSFHA.248@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Here my considerations
> 1. Reporting Database:
> - To provide operational reports with 10 minutes delay.
> - Not Disturbing OLTP Server
> - Incrase response time for users who access reports (estimation
> max 20 users in the same time)
> 2. OLAP Database:
> - To generate Cubes
> - To boost the Cubes generation performance since it won't affected by
> users who access reports.
> - For analytical purpose
> And both of them are not real-time.
> Please explain further about garbage in garbage out?
> So what do you think of my considerations?
> Thanks a lot
> Robert Lie
>
> Jj wrote:
|||Actually what I want to do is not just copy the OLTP Database, but DTS
from OLTP tables to summaries form Tables to eliminate the join and
complex calculation when show up some reports.
-> What do you think?
Since my SQL Server 2000 is standard edition so I can't do a kind of
cube partition.
Thanks
Jj wrote:
> creating a copy of the OLTP database is good choice
> you can use the log shipping function to do this (or other methods)
> (how do you plan to synchronize your databases?)
> generating cubes will not lock your database if you do incremental updates
> on a optimized cube. (or just a litlle lock)
> Partitions will help you for the cube process step.
> But your design appear to be good.
> garbage in garbage out = bad data quality in the source = bad quality in
> output (non existant clientID, duplicated product name, duplicated
> records...). so an ETL tool can improove the data quality during the
> process. or if you want to synchronize multiple sources (like reference
> tables and operationnal tables)
> But these steps reduce the loading time by adding data cleansing time.
> if you think next step (which is SQL 2005) you'll have a lot of new features
> to help you.
> for example, you will be able to load directly a partition of your cube
> while you'll load your database, so 1 read and 2 destinations
> (and there is a lot of options)
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:%23RX3SehSFHA.248@.TK2MSFTNGP15.phx.gbl...
>
sql

OLTP vs Reporting Database vs OLAP Database

Dear All,
I have a fundamental questions regarding my design of my SQL Server
Database.
Current SQL Server 2000 Design Setting (in one server)
-1 Database for OLTP
-1 Database for OLAP -> Generating Cubes
Planned SQL Server 2000 Design Setting (in two servers):
-1 Database for OLTP -> at Server A
-1 Database for Reporting Purposes -> at Server B
-1 Database for OLAP -> at Server B
My Question -> Is it necessary to separate reporting database and OLAP
database?
Any sugestion please.
Thanks
Robert Lieif you want to provide reports based on a copy of your OLTP database, so you
can create a "read-only" database optimized for reporting purpose.
you can provide (near)real time reports.
this usage as an advantage: you don't impat you operationnal server by
executing complex queries. but.. garbage in garbage out...
if you want to have a common and cleansed database for both reporting and
analysis to insure that you'll allways display the same information, use the
same database for RS + OLAP. but, generally, this database in not real-time
updated.
so what is your primary usage for your reports?
- operationnal purpose
- analytical purpose
but you can also provide reports based on the 2 databases regarding the
performance of your queries.
if you have some performance problems with your OLTP database (the copy on
the server B) then the OLAP database and cubes will improove the response
time.
also, look at the number of users... 100 users on a 10seconds report vs 100
users on a 1second report...
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:uD8T74gSFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> I have a fundamental questions regarding my design of my SQL Server
> Database.
> Current SQL Server 2000 Design Setting (in one server)
> -1 Database for OLTP
> -1 Database for OLAP -> Generating Cubes
> Planned SQL Server 2000 Design Setting (in two servers):
> -1 Database for OLTP -> at Server A
> -1 Database for Reporting Purposes -> at Server B
> -1 Database for OLAP -> at Server B
> My Question -> Is it necessary to separate reporting database and OLAP
> database?
> Any sugestion please.
> Thanks
> Robert Lie|||Here my considerations
1. Reporting Database:
- To provide operational reports with 10 minutes delay.
- Not Disturbing OLTP Server
- Incrase response time for users who access reports (estimation
max 20 users in the same time)
2. OLAP Database:
- To generate Cubes
- To boost the Cubes generation performance since it won't
affected by users who access reports.
- For analytical purpose
And both of them are not real-time.
Please explain further about garbage in garbage out?
So what do you think of my considerations?
Thanks a lot
Robert Lie
Jj wrote:
> if you want to provide reports based on a copy of your OLTP database, so y
ou
> can create a "read-only" database optimized for reporting purpose.
> you can provide (near)real time reports.
> this usage as an advantage: you don't impat you operationnal server by
> executing complex queries. but.. garbage in garbage out...
> if you want to have a common and cleansed database for both reporting and
> analysis to insure that you'll allways display the same information, use t
he
> same database for RS + OLAP. but, generally, this database in not real-tim
e
> updated.
> so what is your primary usage for your reports?
> - operationnal purpose
> - analytical purpose
> but you can also provide reports based on the 2 databases regarding the
> performance of your queries.
> if you have some performance problems with your OLTP database (the copy on
> the server B) then the OLAP database and cubes will improove the response
> time.
> also, look at the number of users... 100 users on a 10seconds report vs 10
0
> users on a 1second report...
>
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:uD8T74gSFHA.2520@.TK2MSFTNGP09.phx.gbl...
>
>
>|||creating a copy of the OLTP database is good choice
you can use the log shipping function to do this (or other methods)
(how do you plan to synchronize your databases?)
generating cubes will not lock your database if you do incremental updates
on a optimized cube. (or just a litlle lock)
Partitions will help you for the cube process step.
But your design appear to be good.
garbage in garbage out = bad data quality in the source = bad quality in
output (non existant clientID, duplicated product name, duplicated
records...). so an ETL tool can improove the data quality during the
process. or if you want to synchronize multiple sources (like reference
tables and operationnal tables)
But these steps reduce the loading time by adding data cleansing time.
if you think next step (which is SQL 2005) you'll have a lot of new features
to help you.
for example, you will be able to load directly a partition of your cube
while you'll load your database, so 1 read and 2 destinations
(and there is a lot of options)
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23RX3SehSFHA.248@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Here my considerations
> 1. Reporting Database:
> - To provide operational reports with 10 minutes delay.
> - Not Disturbing OLTP Server
> - Incrase response time for users who access reports (estimation
> max 20 users in the same time)
> 2. OLAP Database:
> - To generate Cubes
> - To boost the Cubes generation performance since it won't affected by
> users who access reports.
> - For analytical purpose
> And both of them are not real-time.
> Please explain further about garbage in garbage out?
> So what do you think of my considerations?
> Thanks a lot
> Robert Lie
>
> Jj wrote:|||Actually what I want to do is not just copy the OLTP Database, but DTS
from OLTP tables to summaries form Tables to eliminate the join and
complex calculation when show up some reports.
-> What do you think?
Since my SQL Server 2000 is standard edition so I can't do a kind of
cube partition.
Thanks
Jj wrote:
> creating a copy of the OLTP database is good choice
> you can use the log shipping function to do this (or other methods)
> (how do you plan to synchronize your databases?)
> generating cubes will not lock your database if you do incremental updates
> on a optimized cube. (or just a litlle lock)
> Partitions will help you for the cube process step.
> But your design appear to be good.
> garbage in garbage out = bad data quality in the source = bad quality in
> output (non existant clientID, duplicated product name, duplicated
> records...). so an ETL tool can improove the data quality during the
> process. or if you want to synchronize multiple sources (like reference
> tables and operationnal tables)
> But these steps reduce the loading time by adding data cleansing time.
> if you think next step (which is SQL 2005) you'll have a lot of new featur
es
> to help you.
> for example, you will be able to load directly a partition of your cube
> while you'll load your database, so 1 read and 2 destinations
> (and there is a lot of options)
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:%23RX3SehSFHA.248@.TK2MSFTNGP15.phx.gbl...
>
>

OLTP vs OLAP (Data Warehouse) as a DataSource in Analysis Services 2005

Hello,

Can I import an OLTP (Reltional DB) as a Data Source into SQL Server
Analysis Services 2005 and then use the Cube Wizard and the new Data
Source View feature to create the OLAP model ?

Or do I have to first design an OLAP Data Warehouse with a Star Schema
and then import this DW as a Data Source into my Analysis Services
Project.

With SQL Server 2000 , OLAP would be the way to go..but with SQL
Server 2005 , it seems as though the wizard and data source view
features do half the work for you.

I have an OLTP DB and am not sure which route I should take ! Any
suggestions / input would be much appreciated.

Thanks in Advance...

Regards
RusszeeHere's the followup answer to my own post...

Just what I thought...It's the new UDM all the way in SQL Server
2005 !!!

"SQL Server 2005 Analysis Services can create UDM cubes without the
intermediate step of building a star schema data warehouse. The UDM
enables organizations to build reporting applications directly against
a production system and doesn't require an intermediate data warehouse
as in the past. This is because the UDM doesn't require the data to be
in a star or snowflake schema, but can connect to any data source
whose data is stored in a third normal form. "

Source : http://www.microsoft.com/technet/pr...5/solvngbp.mspx

OLTP vs Decision Support

Whilst on the Nth hour (n = many) of my magical journey through MS Sql BOL I've come across OLTP Vs Decision Support. After a couple of searches here could someone shore up the following for me please...

A decision support database is the same as warehouse database.

This is for static data commonly used for reporting and analysis.

OLTP is a live database (accomodates inserts, deletes, updates etc).

Is that right?

Also would it be fair to assume that a decision support database is generally going to be spawned from the historical data of an OLTP database? Any real world examples of these two terms would be greatly appreciated too.

Cheers

DanOLAP

Yes it comes from OLTP...dimensions, fact tables, star schemas, snowflakes...

All the buzz words...

basically you migrate data from your database to basically a read only version of the data...

You then may also have processes to derive data...and store it...for example all the sales for department stores for the month

You derive the data by state, by day, whatever...

However people need to see data that's what you build...

Now query is a lot faster because the work has already been done for the end user...

google up some non platform specific concepts...|||OK, makes sense and I only had to read it twice!

Cheerssql

OLTP SQL Memory problem

My OLTP SQL server usually serves ~2700 connections. Twice in the past we've
had memory problem when there is more traffic. In that situation, the number
of connections goes up to 4000. The connections are legitimate and it's been
proved that there are not leaking connections.
I see flood of errors such as the following in the error log. The server has
4GB of memory and /3GB switch is on. SQL server is configured to use the max
memory.
Where do I being to troubleshoot this problem?
Thanks
---
2003-11-04 13:59:44.12 spid3100 Query Memory Manager: Grants=0 Waiting=0
Maximum=245613 Available=245613
2003-11-04 13:59:44.14 spid3167 Buffer Distribution: Stolen=10410 Free=85
Procedures=843
Inram=0 Dirty=23719 Kept=0
I/O=0, Latched=6, Other=303289
2003-11-04 13:59:44.14 spid3167 Buffer Counts: Commited=338352
Target=338352 Hashed=327015
InternalReservation=516 ExternalReservation=0 Min Free=172
2003-11-04 13:59:44.14 spid3167 Procedure Cache: TotalProcs=283
TotalPages=843 InUsePages=389
2003-11-04 13:59:44.14 spid3167 Dynamic Memory Manager: Stolen=11252 OS
Reserved=21680
OS Committed=21667
OS In Use=21665
Query Plan=1080 Optimizer=0
General=2578
Utilities=7 Connection=29191
2003-11-04 13:59:44.14 spid3167 Global Memory Objects: Resource=2303
Locks=59
SQLCache=121 Replication=2
LockBytes=2 ServerGlobal=45
Xact=74Kam,
4GB for a system serving 4000 users seems a little low. I don't know how
large your DB is or how much other parts of the memory are being used but
each connection takes up 12 KB + (3 * Network Packet Size). Then you have
all the other objects and the OS that need memory as well. You don't give
any clues as to what kind of memory issues you are having so it's hard to
say if your just low on ram or there is something else going on. I would
suggest you give MS PSS a call and have them work with you directly and they
can suggest some traces and diags to help pinpoint the issue. Without any
other info I would have to say you are probably just short on ram.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
--
Andrew J. Kelly
SQL Server MVP
"Kam" <krajabi@.hotmail.com> wrote in message
news:3faacbff$1@.news.microsoft.com...
> My OLTP SQL server usually serves ~2700 connections. Twice in the past
we've
> had memory problem when there is more traffic. In that situation, the
number
> of connections goes up to 4000. The connections are legitimate and it's
been
> proved that there are not leaking connections.
>
> I see flood of errors such as the following in the error log. The server
has
> 4GB of memory and /3GB switch is on. SQL server is configured to use the
max
> memory.
>
> Where do I being to troubleshoot this problem?
>
> Thanks
>
>
> ---
> 2003-11-04 13:59:44.12 spid3100 Query Memory Manager: Grants=0 Waiting=0
> Maximum=245613 Available=245613
> 2003-11-04 13:59:44.14 spid3167 Buffer Distribution: Stolen=10410
Free=85
> Procedures=843
> Inram=0 Dirty=23719 Kept=0
> I/O=0, Latched=6, Other=303289
> 2003-11-04 13:59:44.14 spid3167 Buffer Counts: Commited=338352
> Target=338352 Hashed=327015
> InternalReservation=516 ExternalReservation=0 Min Free=172
> 2003-11-04 13:59:44.14 spid3167 Procedure Cache: TotalProcs=283
> TotalPages=843 InUsePages=389
> 2003-11-04 13:59:44.14 spid3167 Dynamic Memory Manager: Stolen=11252 OS
> Reserved=21680
> OS Committed=21667
> OS In Use=21665
> Query Plan=1080 Optimizer=0
> General=2578
> Utilities=7 Connection=29191
> 2003-11-04 13:59:44.14 spid3167 Global Memory Objects: Resource=2303
> Locks=59
> SQLCache=121 Replication=2
> LockBytes=2 ServerGlobal=45
> Xact=74
>|||Andrew,
Some more information based on my observation
- Memory usuage is at 95%+ range
- CPU usage however seems normal ~ 45%
- There is no other application except SQL server
- SQL does accept any more connection when this issue occurs
- When I tried to log into the profiler, I got a message saying Memory is
Low
- There was a rolling blocking senario on the server too
4000 * [12,288 + (3 * 4,096)] = 98,304,000
Andrew, ~93 MB of memory doesn't look that much to me considering SQL
controls 3GB on that server.
Thanks,
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uNFR#LMpDHA.1656@.tk2msftngp13.phx.gbl...
> Kam,
> 4GB for a system serving 4000 users seems a little low. I don't know how
> large your DB is or how much other parts of the memory are being used but
> each connection takes up 12 KB + (3 * Network Packet Size). Then you have
> all the other objects and the OS that need memory as well. You don't give
> any clues as to what kind of memory issues you are having so it's hard to
> say if your just low on ram or there is something else going on. I would
> suggest you give MS PSS a call and have them work with you directly and
they
> can suggest some traces and diags to help pinpoint the issue. Without any
> other info I would have to say you are probably just short on ram.
> http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL Support
> http://www.mssqlserver.com/faq/general-pss.asp MS PSS
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Kam" <krajabi@.hotmail.com> wrote in message
> news:3faacbff$1@.news.microsoft.com...
> > My OLTP SQL server usually serves ~2700 connections. Twice in the past
> we've
> > had memory problem when there is more traffic. In that situation, the
> number
> > of connections goes up to 4000. The connections are legitimate and it's
> been
> > proved that there are not leaking connections.
> >
> >
> >
> > I see flood of errors such as the following in the error log. The server
> has
> > 4GB of memory and /3GB switch is on. SQL server is configured to use the
> max
> > memory.
> >
> >
> >
> > Where do I being to troubleshoot this problem?
> >
> >
> >
> > Thanks
> >
> >
> >
> >
> >
> > ---
> >
> > 2003-11-04 13:59:44.12 spid3100 Query Memory Manager: Grants=0
Waiting=0
> > Maximum=245613 Available=245613
> >
> > 2003-11-04 13:59:44.14 spid3167 Buffer Distribution: Stolen=10410
> Free=85
> > Procedures=843
> >
> > Inram=0 Dirty=23719 Kept=0
> >
> > I/O=0, Latched=6, Other=303289
> >
> > 2003-11-04 13:59:44.14 spid3167 Buffer Counts: Commited=338352
> > Target=338352 Hashed=327015
> >
> > InternalReservation=516 ExternalReservation=0 Min Free=172
> >
> > 2003-11-04 13:59:44.14 spid3167 Procedure Cache: TotalProcs=283
> > TotalPages=843 InUsePages=389
> >
> > 2003-11-04 13:59:44.14 spid3167 Dynamic Memory Manager: Stolen=11252
OS
> > Reserved=21680
> >
> > OS Committed=21667
> >
> > OS In Use=21665
> >
> > Query Plan=1080 Optimizer=0
> >
> > General=2578
> >
> > Utilities=7 Connection=29191
> >
> > 2003-11-04 13:59:44.14 spid3167 Global Memory Objects: Resource=2303
> > Locks=59
> >
> > SQLCache=121 Replication=2
> >
> > LockBytes=2 ServerGlobal=45
> >
> > Xact=74
> >
> >
>|||It may not look like much by itself but it does have to share that with
everything else that takes up memory. If you had blocking issues then you
could also have a ton of locks being held which also use up memory. It may
be the locks that are starting the trouble so you might want to find out why
the blocking happened. But it still sounds like you are right at the edge
of using all the memory effectively for the load and it is likely to happen
again and again.
--
Andrew J. Kelly
SQL Server MVP
"Kam" <krajabi@.hotmail.com> wrote in message
news:3faaeda8$1@.news.microsoft.com...
> Andrew,
> Some more information based on my observation
> - Memory usuage is at 95%+ range
> - CPU usage however seems normal ~ 45%
> - There is no other application except SQL server
> - SQL does accept any more connection when this issue occurs
> - When I tried to log into the profiler, I got a message saying Memory is
> Low
> - There was a rolling blocking senario on the server too
> 4000 * [12,288 + (3 * 4,096)] = 98,304,000
> Andrew, ~93 MB of memory doesn't look that much to me considering SQL
> controls 3GB on that server.
> Thanks,
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uNFR#LMpDHA.1656@.tk2msftngp13.phx.gbl...
> > Kam,
> >
> > 4GB for a system serving 4000 users seems a little low. I don't know
how
> > large your DB is or how much other parts of the memory are being used
but
> > each connection takes up 12 KB + (3 * Network Packet Size). Then you
have
> > all the other objects and the OS that need memory as well. You don't
give
> > any clues as to what kind of memory issues you are having so it's hard
to
> > say if your just low on ram or there is something else going on. I
would
> > suggest you give MS PSS a call and have them work with you directly and
> they
> > can suggest some traces and diags to help pinpoint the issue. Without
any
> > other info I would have to say you are probably just short on ram.
> >
> > http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL
Support
> > http://www.mssqlserver.com/faq/general-pss.asp MS PSS
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Kam" <krajabi@.hotmail.com> wrote in message
> > news:3faacbff$1@.news.microsoft.com...
> > > My OLTP SQL server usually serves ~2700 connections. Twice in the past
> > we've
> > > had memory problem when there is more traffic. In that situation, the
> > number
> > > of connections goes up to 4000. The connections are legitimate and
it's
> > been
> > > proved that there are not leaking connections.
> > >
> > >
> > >
> > > I see flood of errors such as the following in the error log. The
server
> > has
> > > 4GB of memory and /3GB switch is on. SQL server is configured to use
the
> > max
> > > memory.
> > >
> > >
> > >
> > > Where do I being to troubleshoot this problem?
> > >
> > >
> > >
> > > Thanks
> > >
> > >
> > >
> > >
> > >
> >
> ---
> > >
> > > 2003-11-04 13:59:44.12 spid3100 Query Memory Manager: Grants=0
> Waiting=0
> > > Maximum=245613 Available=245613
> > >
> > > 2003-11-04 13:59:44.14 spid3167 Buffer Distribution: Stolen=10410
> > Free=85
> > > Procedures=843
> > >
> > > Inram=0 Dirty=23719 Kept=0
> > >
> > > I/O=0, Latched=6, Other=303289
> > >
> > > 2003-11-04 13:59:44.14 spid3167 Buffer Counts: Commited=338352
> > > Target=338352 Hashed=327015
> > >
> > > InternalReservation=516 ExternalReservation=0 Min Free=172
> > >
> > > 2003-11-04 13:59:44.14 spid3167 Procedure Cache: TotalProcs=283
> > > TotalPages=843 InUsePages=389
> > >
> > > 2003-11-04 13:59:44.14 spid3167 Dynamic Memory Manager: Stolen=11252
> OS
> > > Reserved=21680
> > >
> > > OS Committed=21667
> > >
> > > OS In Use=21665
> > >
> > > Query Plan=1080 Optimizer=0
> > >
> > > General=2578
> > >
> > > Utilities=7 Connection=29191
> > >
> > > 2003-11-04 13:59:44.14 spid3167 Global Memory Objects: Resource=2303
> > > Locks=59
> > >
> > > SQLCache=121 Replication=2
> > >
> > > LockBytes=2 ServerGlobal=45
> > >
> > > Xact=74
> > >
> > >
> >
> >
>|||under perfmon, process object, sqlservr instance, what is
the Virtual Bytes, Working Set,
also get the Virtual Memory size from task manager for the
SqlServr.exe process.
under perfmon SQLServer Buffer Manager, what is the total
pages?
One common problem when SQL Server use 2 or 3GB of memory
is that too much memory and virtual address space gets
allocated to buffers, leaving inadequate address space for
other data structures.
While SQL Server will release buffers to free up memory
for other applications, it does not appear to free up
buffers to make more address space available for other SQL
Server internal requests
By default, SQL reserves 256M of address space for other
than buffers, that's why you frequently see 1.75GB
physical memory used for standard and 2.75GB for /3GB mode,
almost all of the address space is used for data buffers,
and most of the reserved 256M is unused.
in your case, it could be that you need more than 275M but
SQL has already allocated 2.75GB for buffers, and there no
more address space for other uses
provide the above info, but you can also try the -gxxx
startup parameter with say -g384 leaving 384MB for other
stuff
>--Original Message--
>Andrew,
>Some more information based on my observation
>- Memory usuage is at 95%+ range
>- CPU usage however seems normal ~ 45%
>- There is no other application except SQL server
>- SQL does accept any more connection when this issue
occurs
>- When I tried to log into the profiler, I got a message
saying Memory is
>Low
>- There was a rolling blocking senario on the server too
>4000 * [12,288 + (3 * 4,096)] = 98,304,000
>Andrew, ~93 MB of memory doesn't look that much to me
considering SQL
>controls 3GB on that server.
>Thanks,
>
>"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
message
>news:uNFR#LMpDHA.1656@.tk2msftngp13.phx.gbl...
>> Kam,
>> 4GB for a system serving 4000 users seems a little
low. I don't know how
>> large your DB is or how much other parts of the memory
are being used but
>> each connection takes up 12 KB + (3 * Network Packet
Size). Then you have
>> all the other objects and the OS that need memory as
well. You don't give
>> any clues as to what kind of memory issues you are
having so it's hard to
>> say if your just low on ram or there is something else
going on. I would
>> suggest you give MS PSS a call and have them work with
you directly and
>they
>> can suggest some traces and diags to help pinpoint the
issue. Without any
>> other info I would have to say you are probably just
short on ram.
>> http://support.microsoft.com/default.aspx?scid=fh;EN-
US;sql SQL Support
>> http://www.mssqlserver.com/faq/general-pss.asp MS PSS
>> --
>> Andrew J. Kelly
>> SQL Server MVP
>>
>> "Kam" <krajabi@.hotmail.com> wrote in message
>> news:3faacbff$1@.news.microsoft.com...
>> > My OLTP SQL server usually serves ~2700 connections.
Twice in the past
>> we've
>> > had memory problem when there is more traffic. In
that situation, the
>> number
>> > of connections goes up to 4000. The connections are
legitimate and it's
>> been
>> > proved that there are not leaking connections.
>> >
>> >
>> >
>> > I see flood of errors such as the following in the
error log. The server
>> has
>> > 4GB of memory and /3GB switch is on. SQL server is
configured to use the
>> max
>> > memory.
>> >
>> >
>> >
>> > Where do I being to troubleshoot this problem?
>> >
>> >
>> >
>> > Thanks
>> >
>> >
>> >
>> >
>> >
>> > ---
--
>> >
>> > 2003-11-04 13:59:44.12 spid3100 Query Memory
Manager: Grants=0
>Waiting=0
>> > Maximum=245613 Available=245613
>> >
>> > 2003-11-04 13:59:44.14 spid3167 Buffer
Distribution: Stolen=10410
>> Free=85
>> > Procedures=843
>> >
>> > Inram=0 Dirty=23719 Kept=0
>> >
>> > I/O=0, Latched=6, Other=303289
>> >
>> > 2003-11-04 13:59:44.14 spid3167 Buffer Counts:
Commited=338352
>> > Target=338352 Hashed=327015
>> >
>> > InternalReservation=516
ExternalReservation=0 Min Free=172
>> >
>> > 2003-11-04 13:59:44.14 spid3167 Procedure Cache:
TotalProcs=283
>> > TotalPages=843 InUsePages=389
>> >
>> > 2003-11-04 13:59:44.14 spid3167 Dynamic Memory
Manager: Stolen=11252
>OS
>> > Reserved=21680
>> >
>> > OS Committed=21667
>> >
>> > OS In Use=21665
>> >
>> > Query Plan=1080 Optimizer=0
>> >
>> > General=2578
>> >
>> > Utilities=7 Connection=29191
>> >
>> > 2003-11-04 13:59:44.14 spid3167 Global Memory
Objects: Resource=2303
>> > Locks=59
>> >
>> > SQLCache=121 Replication=2
>> >
>> > LockBytes=2 ServerGlobal=45
>> >
>> > Xact=74
>> >
>> >
>>
>
>.
>

OLTP Best Practices

Are there any best practices for high volume OLTP systems (20M trans / day)?
Such as sp_updatestats, index defrag, file group alignment, online backup,
etc...
Any advice is greatly appreciated.
Thanks
Bryan DoveI am looking for some general best practices. Such as how often should
sp_updatestats be run, how often should the t-log be backed up, etc... We
are having a debate over what is necessary, versus what is overkill for many
of the maintenance-type processes.
Thanks
Bryan Dove
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uooxAywgDHA.2456@.TK2MSFTNGP12.phx.gbl...
> They are all good practices for most OLTP systems but how, when etc
depends
> a lot on each individual app's needs. Maybe this is a good place to
start.
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/operate/opsguide/default.asp
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Bryan Dove" <bryan.dove@.nospam.ndchealth.com> wrote in message
> news:eLkA51ugDHA.3324@.TK2MSFTNGP11.phx.gbl...
> > Are there any best practices for high volume OLTP systems (20M trans /
> day)?
> > Such as sp_updatestats, index defrag, file group alignment, online
backup,
> > etc...
> >
> > Any advice is greatly appreciated.
> >
> > Thanks
> >
> > Bryan Dove
> >
> >
>|||Hi Bryan,
I think how often the transaction log backup should be run is dependent on
your actual requirements. For example, if you have the following backup
plan:
Time Event
8:00 A.M. Back up database
Noon Back up transaction log
4:00 P.M. Back up transaction log
6:00 P.M. Back up database
8:00 P.M. Back up transaction log
10:00 P.M. Failure occurs
Then we may lose the data between 8PM and 10PM.
In "Analyzing Availability and Recovery Requirements" topic in SQL Server
2000 Books Online, there are some basic questions to help you analyze your
availability and recovery requirements:
What are your availability requirements? What portion of each day must the
database be online?
What is the financial cost of downtime to your business?
If you experience media failure, such as a failing disk drive, what is the
acceptable downtime?
In case of a disaster, such as the loss of a server in a fire, what is the
acceptable downtime?
How important is it to never lose a change?
How easy would it be to re-create lost data?
Does your organization employ system or database administrators?
Who will be responsible for performing backup and recovery operations, and
how will they be trained?
Here are some questions to help you choose the tools, techniques, and
hardware appropriate for your site:
How large is each database?
How often does the data in each database change?
Are some tables modified more often than others?
What are your critical database production periods?
When does the database experience heavy use, resulting in frequent inserts
and updates?
Is transaction log space consumption likely to be a problem due to heavy
update activity?
Is your database subject to periodic bulk data loading?
Is your database subject to risky updates or application errors that may
not be detected immediately?
Is your database server part of a SQL Server 2000 failover cluster for high
availability?
Is your database in a multi-server environment with centralized
administration?
"Planning for Disaster Recovery"
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a
d_bkprst_7kvb.asp>
Designing a Backup and Restore Strategy
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a
d_bkprst_63eh.asp>
- Support WebCast: SQL Server 2000 Database Recovery: Backup and Restore
<http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc120
500/wcblurb120500.asp>
- INF: Disaster Recovery Planning for SQL Server
<http://support.microsoft.com/?kbid=169039>
<http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtech
nol/sql/reskit/sql2000/part4/c1561.asp>
In addition, making some experiments and comparisons would be necessary in
a production system, as it is more effective.
Bill Cheng
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--
| From: "Bryan Dove" <bryan.dove@.nospam.ndchealth.com>
| References: <eLkA51ugDHA.3324@.TK2MSFTNGP11.phx.gbl>
<uooxAywgDHA.2456@.TK2MSFTNGP12.phx.gbl>
| Subject: Re: OLTP Best Practices
| Date: Fri, 26 Sep 2003 09:28:03 -0400
| Lines: 46
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.0
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Message-ID: <#yiyZIDhDHA.2080@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: 12.5.227.202
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:308507
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| I am looking for some general best practices. Such as how often should
| sp_updatestats be run, how often should the t-log be backed up, etc... We
| are having a debate over what is necessary, versus what is overkill for
many
| of the maintenance-type processes.
|
| Thanks
|
| Bryan Dove
|
|
|
| "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
| news:uooxAywgDHA.2456@.TK2MSFTNGP12.phx.gbl...
| > They are all good practices for most OLTP systems but how, when etc
| depends
| > a lot on each individual app's needs. Maybe this is a good place to
| start.
| >
| >
|
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechn
ol/sql/maintain/operate/opsguide/default.asp
| >
| > --
| >
| > Andrew J. Kelly
| > SQL Server MVP
| >
| >
| > "Bryan Dove" <bryan.dove@.nospam.ndchealth.com> wrote in message
| > news:eLkA51ugDHA.3324@.TK2MSFTNGP11.phx.gbl...
| > > Are there any best practices for high volume OLTP systems (20M trans /
| > day)?
| > > Such as sp_updatestats, index defrag, file group alignment, online
| backup,
| > > etc...
| > >
| > > Any advice is greatly appreciated.
| > >
| > > Thanks
| > >
| > > Bryan Dove
| > >
| > >
| >
| >
|
|
||||Good advice on the tlog backups.
What about the sp_updatestats. How often should that take place? Our
database is an OLTP, third normal form, about 20,000,000 trans per day.
Thanks
Bryan Dove
""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
news:ADWtU5IhDHA.2272@.cpmsftngxa06.phx.gbl...
> Hi Bryan,
> I think how often the transaction log backup should be run is dependent on
> your actual requirements. For example, if you have the following backup
> plan:
> Time Event
> 8:00 A.M. Back up database
> Noon Back up transaction log
> 4:00 P.M. Back up transaction log
> 6:00 P.M. Back up database
> 8:00 P.M. Back up transaction log
> 10:00 P.M. Failure occurs
> Then we may lose the data between 8PM and 10PM.
> In "Analyzing Availability and Recovery Requirements" topic in SQL Server
> 2000 Books Online, there are some basic questions to help you analyze your
> availability and recovery requirements:
> What are your availability requirements? What portion of each day must the
> database be online?
>
> What is the financial cost of downtime to your business?
>
> If you experience media failure, such as a failing disk drive, what is the
> acceptable downtime?
>
> In case of a disaster, such as the loss of a server in a fire, what is the
> acceptable downtime?
>
> How important is it to never lose a change?
>
> How easy would it be to re-create lost data?
>
> Does your organization employ system or database administrators?
>
> Who will be responsible for performing backup and recovery operations, and
> how will they be trained?
> Here are some questions to help you choose the tools, techniques, and
> hardware appropriate for your site:
> How large is each database?
>
> How often does the data in each database change?
>
> Are some tables modified more often than others?
>
> What are your critical database production periods?
>
> When does the database experience heavy use, resulting in frequent inserts
> and updates?
>
> Is transaction log space consumption likely to be a problem due to heavy
> update activity?
>
> Is your database subject to periodic bulk data loading?
>
> Is your database subject to risky updates or application errors that may
> not be detected immediately?
>
> Is your database server part of a SQL Server 2000 failover cluster for
high
> availability?
>
> Is your database in a multi-server environment with centralized
> administration?
>
> "Planning for Disaster Recovery"
>
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a
> d_bkprst_7kvb.asp>
> Designing a Backup and Restore Strategy
>
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a
> d_bkprst_63eh.asp>
> - Support WebCast: SQL Server 2000 Database Recovery: Backup and Restore
>
<http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc120
> 500/wcblurb120500.asp>
> - INF: Disaster Recovery Planning for SQL Server
> <http://support.microsoft.com/?kbid=169039>
>
<http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtech
> nol/sql/reskit/sql2000/part4/c1561.asp>
> In addition, making some experiments and comparisons would be necessary in
> a production system, as it is more effective.
>
> Bill Cheng
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> --
> | From: "Bryan Dove" <bryan.dove@.nospam.ndchealth.com>
> | References: <eLkA51ugDHA.3324@.TK2MSFTNGP11.phx.gbl>
> <uooxAywgDHA.2456@.TK2MSFTNGP12.phx.gbl>
> | Subject: Re: OLTP Best Practices
> | Date: Fri, 26 Sep 2003 09:28:03 -0400
> | Lines: 46
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.3790.0
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Message-ID: <#yiyZIDhDHA.2080@.TK2MSFTNGP12.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: 12.5.227.202
> | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:308507
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | I am looking for some general best practices. Such as how often should
> | sp_updatestats be run, how often should the t-log be backed up, etc...
We
> | are having a debate over what is necessary, versus what is overkill for
> many
> | of the maintenance-type processes.
> |
> | Thanks
> |
> | Bryan Dove
> |
> |
> |
> | "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> | news:uooxAywgDHA.2456@.TK2MSFTNGP12.phx.gbl...
> | > They are all good practices for most OLTP systems but how, when etc
> | depends
> | > a lot on each individual app's needs. Maybe this is a good place to
> | start.
> | >
> | >
> |
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechn
> ol/sql/maintain/operate/opsguide/default.asp
> | >
> | > --
> | >
> | > Andrew J. Kelly
> | > SQL Server MVP
> | >
> | >
> | > "Bryan Dove" <bryan.dove@.nospam.ndchealth.com> wrote in message
> | > news:eLkA51ugDHA.3324@.TK2MSFTNGP11.phx.gbl...
> | > > Are there any best practices for high volume OLTP systems (20M trans
/
> | > day)?
> | > > Such as sp_updatestats, index defrag, file group alignment, online
> | backup,
> | > > etc...
> | > >
> | > > Any advice is greatly appreciated.
> | > >
> | > > Thanks
> | > >
> | > > Bryan Dove
> | > >
> | > >
> | >
> | >
> |
> |
> |
>|||Hi Bryan,
sp_updatestats runs UPDATE STATISTICS against all user-defined tables in
the current database. In addition, the statistics update may cause stored
procedures that access the table data to be re-compiled in SQL Server. It
will reflect the up-to-date data distribution of the database data and may
let SQL Server choose better execution plans. However, re-compilation of
stored procedures can impact SQL Server performance very much.
According to my experience, if you find your stored procedures performance
degrade after long time of running and recompiling it can resolve the
problem, then it indicates that SQL Server may need the up-to-date
statistics. Then we can run sp_updatestats periodically (usually daily
update is fine.)
This posting is provided "AS IS" with no warranties, and confers no rights.
Regards,
Bill Cheng
Microsoft Support Engineer
--
| From: "Bryan Dove" <bryan.dove@.nospam.ndchealth.com>
| References: <eLkA51ugDHA.3324@.TK2MSFTNGP11.phx.gbl>
<uooxAywgDHA.2456@.TK2MSFTNGP12.phx.gbl>
<#yiyZIDhDHA.2080@.TK2MSFTNGP12.phx.gbl>
<ADWtU5IhDHA.2272@.cpmsftngxa06.phx.gbl>
| Subject: Re: OLTP Best Practices
| Date: Sun, 28 Sep 2003 19:26:10 -0400
| Lines: 201
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.0
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Message-ID: <OWK$ofhhDHA.616@.TK2MSFTNGP11.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: pcp648536pcs.mkethn01.fl.comcast.net 68.35.203.37
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:308722
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Good advice on the tlog backups.
|
| What about the sp_updatestats. How often should that take place? Our
| database is an OLTP, third normal form, about 20,000,000 trans per day.
|
| Thanks
|
| Bryan Dove
|
| ""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
| news:ADWtU5IhDHA.2272@.cpmsftngxa06.phx.gbl...
| > Hi Bryan,
| >
| > I think how often the transaction log backup should be run is dependent
on
| > your actual requirements. For example, if you have the following backup
| > plan:
| > Time Event
| > 8:00 A.M. Back up database
| > Noon Back up transaction log
| > 4:00 P.M. Back up transaction log
| > 6:00 P.M. Back up database
| > 8:00 P.M. Back up transaction log
| > 10:00 P.M. Failure occurs
| >
| > Then we may lose the data between 8PM and 10PM.
| >
| > In "Analyzing Availability and Recovery Requirements" topic in SQL
Server
| > 2000 Books Online, there are some basic questions to help you analyze
your
| > availability and recovery requirements:
| >
| > What are your availability requirements? What portion of each day must
the
| > database be online?
| >
| >
| > What is the financial cost of downtime to your business?
| >
| >
| > If you experience media failure, such as a failing disk drive, what is
the
| > acceptable downtime?
| >
| >
| > In case of a disaster, such as the loss of a server in a fire, what is
the
| > acceptable downtime?
| >
| >
| > How important is it to never lose a change?
| >
| >
| > How easy would it be to re-create lost data?
| >
| >
| > Does your organization employ system or database administrators?
| >
| >
| > Who will be responsible for performing backup and recovery operations,
and
| > how will they be trained?
| > Here are some questions to help you choose the tools, techniques, and
| > hardware appropriate for your site:
| >
| > How large is each database?
| >
| >
| > How often does the data in each database change?
| >
| >
| > Are some tables modified more often than others?
| >
| >
| > What are your critical database production periods?
| >
| >
| > When does the database experience heavy use, resulting in frequent
inserts
| > and updates?
| >
| >
| > Is transaction log space consumption likely to be a problem due to heavy
| > update activity?
| >
| >
| > Is your database subject to periodic bulk data loading?
| >
| >
| > Is your database subject to risky updates or application errors that may
| > not be detected immediately?
| >
| >
| > Is your database server part of a SQL Server 2000 failover cluster for
| high
| > availability?
| >
| >
| > Is your database in a multi-server environment with centralized
| > administration?
| >
| >
| >
| > "Planning for Disaster Recovery"
| >
|
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a
| > d_bkprst_7kvb.asp>
| >
| > Designing a Backup and Restore Strategy
| >
|
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a
| > d_bkprst_63eh.asp>
| >
| > - Support WebCast: SQL Server 2000 Database Recovery: Backup and Restore
| >
|
<http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc120
| > 500/wcblurb120500.asp>
| >
| > - INF: Disaster Recovery Planning for SQL Server
| > <http://support.microsoft.com/?kbid=169039>
| >
| >
|
<http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtech
| > nol/sql/reskit/sql2000/part4/c1561.asp>
| >
| > In addition, making some experiments and comparisons would be necessary
in
| > a production system, as it is more effective.
| >
| >
| > Bill Cheng
| > Microsoft Online Partner Support
| >
| > Get Secure! - www.microsoft.com/security
| > This posting is provided "as is" with no warranties and confers no
rights.
| > --
| > | From: "Bryan Dove" <bryan.dove@.nospam.ndchealth.com>
| > | References: <eLkA51ugDHA.3324@.TK2MSFTNGP11.phx.gbl>
| > <uooxAywgDHA.2456@.TK2MSFTNGP12.phx.gbl>
| > | Subject: Re: OLTP Best Practices
| > | Date: Fri, 26 Sep 2003 09:28:03 -0400
| > | Lines: 46
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.3790.0
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Message-ID: <#yiyZIDhDHA.2080@.TK2MSFTNGP12.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.server
| > | NNTP-Posting-Host: 12.5.227.202
| > | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:308507
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > |
| > | I am looking for some general best practices. Such as how often should
| > | sp_updatestats be run, how often should the t-log be backed up, etc...
| We
| > | are having a debate over what is necessary, versus what is overkill
for
| > many
| > | of the maintenance-type processes.
| > |
| > | Thanks
| > |
| > | Bryan Dove
| > |
| > |
| > |
| > | "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
| > | news:uooxAywgDHA.2456@.TK2MSFTNGP12.phx.gbl...
| > | > They are all good practices for most OLTP systems but how, when etc
| > | depends
| > | > a lot on each individual app's needs. Maybe this is a good place to
| > | start.
| > | >
| > | >
| > |
| >
|
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechn
| > ol/sql/maintain/operate/opsguide/default.asp
| > | >
| > | > --
| > | >
| > | > Andrew J. Kelly
| > | > SQL Server MVP
| > | >
| > | >
| > | > "Bryan Dove" <bryan.dove@.nospam.ndchealth.com> wrote in message
| > | > news:eLkA51ugDHA.3324@.TK2MSFTNGP11.phx.gbl...
| > | > > Are there any best practices for high volume OLTP systems (20M
trans
| /
| > | > day)?
| > | > > Such as sp_updatestats, index defrag, file group alignment, online
| > | backup,
| > | > > etc...
| > | > >
| > | > > Any advice is greatly appreciated.
| > | > >
| > | > > Thanks
| > | > >
| > | > > Bryan Dove
| > | > >
| > | > >
| > | >
| > | >
| > |
| > |
| > |
| >
|
|
|

Wednesday, March 21, 2012

OLTP and Reporting databases seperated?

we are using an object database for our OLTP but for reporting we havegot some issues about performance as the cpu becames a bottleneck.Andwe want to be able torun on low end computers...

One of our team members suggested to replicate the object database to aSQL table.But just a single one.The most denormalized thing ever.(358 coloumns)

is this the fastest way we can get in reporting?

*we don't want harddisk,ram or cpu to became a bottleneck. ( must run on cheap staff)

Replicating to SQL Server from object database is heterogeneous replication and replication is one of those things in SQL Server that is work in progress, so you may not get the results you expect. If you want to remain denormalized you can still reduce the columns by creating UNION ALL Views, if you choose to normalize the table start at the lowest level files and association because what you have now is just a flat file. Hope this helps.

|||thanks.I guess RDBMS is the fastest way to present table to UI.

OLTP and OLAP database

Can someone explain in general what they mean? their concepts, and their designs? I know they have something to do with report designs rite?

Just doing some homework for an interview which might ask the q.

I'll reading from various links, but just want to know what u guys, in your words, would explain this ?In couple words:

OLTP - database for registering data (transactions - new data, any updates). Usually OLTP databases keep data what could be changed (for instance - something was sold and can be returned during one month, after month sale is final).
OLTP databases are normalized up to third normal form (for increasing speed of transactions).

WAREHOUSE database keeps historical data about something (let say about final sales). Usually WAREHOUSE databases are very big.
WAREHOUSE databases are denormalized (for increasing speed of reports).

There is no OLAP database (at least it is not regular database). OLAP is a technology for getting any kind of reports for analysis historical data.
OLAP can use OLTP or WAREHOUSE databases. It is possible to use OLAP from Microsoft or somebody else - but anybody can create special "OLAP" for his database (of course - question is about efficiency).

I guess anybody will understand that you know something about databases after this answer.

May be somebody else could add something or change my answer.|||thanks for yer reply. I've look through the web and gettin to know more about this things.

Although I have previously been in db designs, didn't really know there were such terms for such things :)|||OLTP = On Line Transaction Processing
OLAP = In Line Analysis Processing used to be DSS

OLTP is set up as earlier stated and designed for fast transaction throughput.

OLAP which does not exactly = Wharehouse is usualy denormalized to simplify queries and tables are usually heavily indexed.

You can see whre the two designs can clash.|||Sorry another question on this

When it comes to OLTP and OLAP, usually data in summarized form will have to be transfered to the OLAP databases. In terms of performance, how will the transfer affect the current OLTP which may be running missing critical system and cannot afford to have their performance be degraded??
What are the possible solutions to this?

thx|||The most succeful systems I have seen had the OLTP data replicated to the OLAP environment and then summerized in the OLAP environment.
This is in now way the only solution, just least impact on either system.

Usually you can get by with doing most of the summerizing and denomalizing on the OLTP side and finish up on the OLAP side.

Your companies resources and the nature of your OLTP and OLAP environment will dictate whee yo udo the work.|||thanks
already done that ... just wanted to find out if there's any other way round it =)sql

OLTP and OLAP

Hello All,
Is a good idea to have OLTP and OLAP database in the same machine?
Thanks
Leandro L S
Vitoria-ES / BRAZILno. your olap will eat up all your memory, so they will compete which result
in performance loss.
jobi
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:#OMVE$YuDHA.2340@.TK2MSFTNGP12.phx.gbl...
> Hello All,
> Is a good idea to have OLTP and OLAP database in the same machine?
> Thanks
> Leandro L S
> Vitoria-ES / BRAZIL
>

OLTP access

Hi,
With my OLTP on another server, OLAP cube design works up to the point of
actually processing the cube, then it errors processing the dimension.
If I move the OLTP database (SQL Server 2000) onto the machine running the
OLAP it works fine.
It seems to be a rights issue but the ODBC tests work fine and the cube
design has no trouble viewing the OLTP schema.
In its 'normal' position the OLTP is on a Win2k Server that is part of a
work group.
The OLAP is on a Win2k Domain Controller.
Any clues on how to establish a successful processing of the OLTP in its
normal position would be appreciated.
Thanks
Bobhave you made sure the services for OLTP sql server and AS on the other
server are started with the same admin account and not local?
When you are in the design in AS, you are authenticated using your login to
the sql server source so it is ok there, but when you are processing, it is
authenticated using the login that start up AS, so that login might not have
access to the sql server tables to process the dimensions.
"Bob" wrote:

> Hi,
> With my OLTP on another server, OLAP cube design works up to the point of
> actually processing the cube, then it errors processing the dimension.
> If I move the OLTP database (SQL Server 2000) onto the machine running the
> OLAP it works fine.
> It seems to be a rights issue but the ODBC tests work fine and the cube
> design has no trouble viewing the OLTP schema.
> In its 'normal' position the OLTP is on a Win2k Server that is part of a
> work group.
> The OLAP is on a Win2k Domain Controller.
> Any clues on how to establish a successful processing of the OLTP in its
> normal position would be appreciated.
> Thanks
> Bob
>
>|||Hi,
Thanks for your reply.
I altered the OLAP service to run under administrator but it has made no
difference.
This is extremely poor design in IMHO. If the ODBC object can connect to
the OLTP, the OLAP should be able to process, end of story.
There is no trusted connection between the OLAP machine (Win2k Domain
Controller) and the OLTP machine (Win2k Server but running as a member of a
workgroup)
So I am relying ODBC to sort out the security issues.
e.g.. Access can use ODBC to link to tables in the OLTP so how come the OLAP
is so frail?
I have tried to create an ODBC connection to the OLTP that uses SQL server
authentication but have failed . I keep getting
'Not associated with a trusted SQL server connection'.
In a perfect world I would promote the OLTP to be a domain controller of its
own domain and establish a trust relationship between the to domains. But no
can do.
I think Bill's merry men should be looking at this.
Either I am missing something fundamental or the OLAP connection needs
redesigning.
No way should it be this difficult to use in this situation.
i.e. Anything that Access can do, OLAP should be able to do. The security
implications are the same.
regards
Bob
"bc" <bc@.discussions.microsoft.com> wrote in message
news:D6258097-009A-492A-BA75-2AC40A664968@.microsoft.com...
> have you made sure the services for OLTP sql server and AS on the other
> server are started with the same admin account and not local?
> When you are in the design in AS, you are authenticated using your login
to
> the sql server source so it is ok there, but when you are processing, it
is
> authenticated using the login that start up AS, so that login might not
have[vbcol=seagreen]
> access to the sql server tables to process the dimensions.
>
> "Bob" wrote:
>
of[vbcol=seagreen]
the[vbcol=seagreen]

OLTP access

Hi,
With my OLTP on another server, OLAP cube design works up to the point of
actually processing the cube, then it errors processing the dimension.
If I move the OLTP database (SQL Server 2000) onto the machine running the
OLAP it works fine.
It seems to be a rights issue but the ODBC tests work fine and the cube
design has no trouble viewing the OLTP schema.
In its 'normal' position the OLTP is on a Win2k Server that is part of a
work group.
The OLAP is on a Win2k Domain Controller.
Any clues on how to establish a successful processing of the OLTP in its
normal position would be appreciated.
Thanks
Bob
have you made sure the services for OLTP sql server and AS on the other
server are started with the same admin account and not local?
When you are in the design in AS, you are authenticated using your login to
the sql server source so it is ok there, but when you are processing, it is
authenticated using the login that start up AS, so that login might not have
access to the sql server tables to process the dimensions.
"Bob" wrote:

> Hi,
> With my OLTP on another server, OLAP cube design works up to the point of
> actually processing the cube, then it errors processing the dimension.
> If I move the OLTP database (SQL Server 2000) onto the machine running the
> OLAP it works fine.
> It seems to be a rights issue but the ODBC tests work fine and the cube
> design has no trouble viewing the OLTP schema.
> In its 'normal' position the OLTP is on a Win2k Server that is part of a
> work group.
> The OLAP is on a Win2k Domain Controller.
> Any clues on how to establish a successful processing of the OLTP in its
> normal position would be appreciated.
> Thanks
> Bob
>
>
|||Hi,
Thanks for your reply.
I altered the OLAP service to run under administrator but it has made no
difference.
This is extremely poor design in IMHO. If the ODBC object can connect to
the OLTP, the OLAP should be able to process, end of story.
There is no trusted connection between the OLAP machine (Win2k Domain
Controller) and the OLTP machine (Win2k Server but running as a member of a
workgroup)
So I am relying ODBC to sort out the security issues.
e.g.. Access can use ODBC to link to tables in the OLTP so how come the OLAP
is so frail?
I have tried to create an ODBC connection to the OLTP that uses SQL server
authentication but have failed . I keep getting
'Not associated with a trusted SQL server connection'.
In a perfect world I would promote the OLTP to be a domain controller of its
own domain and establish a trust relationship between the to domains. But no
can do.
I think Bill's merry men should be looking at this.
Either I am missing something fundamental or the OLAP connection needs
redesigning.
No way should it be this difficult to use in this situation.
i.e. Anything that Access can do, OLAP should be able to do. The security
implications are the same.
regards
Bob
"bc" <bc@.discussions.microsoft.com> wrote in message
news:D6258097-009A-492A-BA75-2AC40A664968@.microsoft.com...
> have you made sure the services for OLTP sql server and AS on the other
> server are started with the same admin account and not local?
> When you are in the design in AS, you are authenticated using your login
to
> the sql server source so it is ok there, but when you are processing, it
is
> authenticated using the login that start up AS, so that login might not
have[vbcol=seagreen]
> access to the sql server tables to process the dimensions.
>
> "Bob" wrote:
of[vbcol=seagreen]
the[vbcol=seagreen]

OLEDB Source Table Locks?

Hi All,

Is it possible that an OLEDB Data Flow Source is imposing locks on the source tables? The source is an SQL Server OLTP environment, and although the package will be scheduled to run nightly when the application sees little to no use, I want to be sure that the process isn't impacting any application functions.

Thanks for the advice!

Rocco

An OLE-DB source will impose the same locks as if you were running the SELECT satement yourself from any other tool, so normaly you would expect some shared locks to allow you to consistently read the data requested.

Using Profiler will show you details of the SQL statement, and you can also choose to show locks details in profier, just filter for the SSIS connection/machine/user/application to focus on SSIS generated information as opposed to regular application traffic.

Friday, March 23, 2012

OLTP vs Reporting Database vs OLAP Database

OLTP vs Reporting Database vs OLAP Database

OLTP vs Reporting Database vs OLAP Database

OLTP vs Reporting Database vs OLAP Database

OLTP vs Reporting Database vs OLAP Database

OLTP vs OLAP (Data Warehouse) as a DataSource in Analysis Services 2005

OLTP vs Decision Support

OLTP SQL Memory problem

OLTP Best Practices

Wednesday, March 21, 2012

OLTP and Reporting databases seperated?

OLTP and OLAP database

OLTP and OLAP

OLTP access

OLTP access

OLEDB Source Table Locks?

old sql2k5 folder

Blog Archive

About Me