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
| > | > >
| > | > >
| > | >
| > | >
| > |
| > |
| > |
| >
|
|
|