M
Mark Byington
Are there any documented guidelines on Database tuning and Server tuning for
PS2007?
PS2007?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
P
Paul Conroy
Hi Mark,
There was a significant amount of DB optimisations implemented in SP1.
I don't believe there is documentation specific to PS2007 but there's plenty
of generic SQL tuning documents available from Technet/MSDN and the net.
Having done several SQL performance reviews for customers I've noticed some
common issues including:-
Multiple instances of SQL each using MAX available memory. Throttle MEM of
individual instances of SQL on the same box. Leave enough MEM (approx 1GB)
for the OS.
If you're using more than 4GB of RAM on your SQL box, Upgrade to 64bit.
There are certain operates in SQL 32bit that cannot address paged memory.
You can always benefit from more memory in SQL. The less that SQL needs to
read from disk, the faster the transactions are going to be. Monitor the SQL
Buffer counter in perform, if this is constantly below 90% then you need more
memory.
Don't install logs and DB files on the same physical disks. Log write
sequentially whilst DB's are random read/write. Having them on the same disk
can cause disk thrashes and will impact on disk io. Monitor the
Physical/Logical disk Queue length, for your SQL disks. If it is constantly
greater than 2, then the disks are underperforming. Offload DBs or logs onto
another drive.
Ensure you have sufficient free space in your DB's (more than 30%). Growing
the DB causing IO's to stop for the duration of the grow operation. This
also is effected by increase file defragmentation. On that note, defrag you
db's regularly to improve disk IO.
Ensure your log files are backed up so they truncate. This is often
overlooked causing the log files to blow out the available space on the disk.
Tempdb is single threaded when it had only one DB file, so operations which
require the use of the Tempdb queue for it's availability. It's best
practice to create a DB file in Tempdb for each CPU/Core running the server
to enable multi threaded ops.
http://technet.microsoft.com/en-us/library/ms175527.aspx
Disk are often not configured for optimal performance. Wrong RAID, disk
Offset etc. See this article for more info
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1262122,00.html
As a last resort, take a trace of the PS DB's and run the tuning advisor
against it. This will recommend where performance can be gained through
additional indexes etc.
There is however an issue of supportability you need to consider. MSFT do
not support the changing of schema for any DB other than the Reporting DB.
If, you alter the schema of the other DB's then you "may" be required to
reverse them if you raise a support call with MSFT. Your SQL DBA should able
to write a Stored Proc to implement any changes, similarly a Stored Proc
could be used to remove these changes. Keeping all changes in the same
Stored Proc provides a good record of any changes that have been made to the
schema.
HTH
Paul
There was a significant amount of DB optimisations implemented in SP1.
I don't believe there is documentation specific to PS2007 but there's plenty
of generic SQL tuning documents available from Technet/MSDN and the net.
Having done several SQL performance reviews for customers I've noticed some
common issues including:-
Multiple instances of SQL each using MAX available memory. Throttle MEM of
individual instances of SQL on the same box. Leave enough MEM (approx 1GB)
for the OS.
If you're using more than 4GB of RAM on your SQL box, Upgrade to 64bit.
There are certain operates in SQL 32bit that cannot address paged memory.
You can always benefit from more memory in SQL. The less that SQL needs to
read from disk, the faster the transactions are going to be. Monitor the SQL
Buffer counter in perform, if this is constantly below 90% then you need more
memory.
Don't install logs and DB files on the same physical disks. Log write
sequentially whilst DB's are random read/write. Having them on the same disk
can cause disk thrashes and will impact on disk io. Monitor the
Physical/Logical disk Queue length, for your SQL disks. If it is constantly
greater than 2, then the disks are underperforming. Offload DBs or logs onto
another drive.
Ensure you have sufficient free space in your DB's (more than 30%). Growing
the DB causing IO's to stop for the duration of the grow operation. This
also is effected by increase file defragmentation. On that note, defrag you
db's regularly to improve disk IO.
Ensure your log files are backed up so they truncate. This is often
overlooked causing the log files to blow out the available space on the disk.
Tempdb is single threaded when it had only one DB file, so operations which
require the use of the Tempdb queue for it's availability. It's best
practice to create a DB file in Tempdb for each CPU/Core running the server
to enable multi threaded ops.
http://technet.microsoft.com/en-us/library/ms175527.aspx
Disk are often not configured for optimal performance. Wrong RAID, disk
Offset etc. See this article for more info
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1262122,00.html
As a last resort, take a trace of the PS DB's and run the tuning advisor
against it. This will recommend where performance can be gained through
additional indexes etc.
There is however an issue of supportability you need to consider. MSFT do
not support the changing of schema for any DB other than the Reporting DB.
If, you alter the schema of the other DB's then you "may" be required to
reverse them if you raise a support call with MSFT. Your SQL DBA should able
to write a Stored Proc to implement any changes, similarly a Stored Proc
could be used to remove these changes. Keeping all changes in the same
Stored Proc provides a good record of any changes that have been made to the
schema.
HTH
Paul
M
Mark Byington
I'll pass it all along to my DBA - thanks, Paul