1 / 2

CREATING HIGH PERFORMING DATABASES BY SATISH KARTAN

Creating databases in SQL Server may seem to be a very simple task but the options and parameters you set may have profound implications as the data in your databases grows

satishkartan
Download Presentation

CREATING HIGH PERFORMING DATABASES BY SATISH KARTAN

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CREATING HIGH PERFORMING DATABASES BY SATISH KARTAN Creating databases in SQL Server may seem to be a very simple task but the options and parameters you set may have profound implications as the data in your databases grows. Here are some of the caveats and suggestions when creating a SQL database: Mind your initial DB size:Set the initial database size based on estimated projected growth. This will preclude unnecessary file-level fragmentation due to frequent auto-growth. Create multiple filegroups:Even if you have a single volume assigned for storing database files, Satish Kartan suggests you to create separate file groups (one for tables, one for indexes, one for archive data, etc) as this will reap benefits in many ways later on as your data grows. This will also help speed up your backups and restores as SQL native backup uses multiple reader/writer threads to perform the backup/restore if you have multiple files. Moreover, If your database grows to be a very large database (VLDB) in few years, it would be easier to maintain and segregate the file groups incurring high I/O onto new volumes. Choose your auto-growth size wisely:If the instant file initialization is enabled for the SQL Service account, then auto-growth size can be large for database files. However, instant file initialization doesn’t apply to transaction log files – set the transaction log auto-growth parameter to no more than 512MB. Segregate your DB files:Separate your database and transaction log files on to different volumes. Since writes to the database files are random in random in nature and writes to the transaction log file are sequential in nature, it would be prudent to separate them Set the block size to 64KB:Volumes storing DB files should have File Allocation Unit set to 64kB: SQL writes to databases files in 64KB unit (8 extents), so make sure that the volume storing the database files gets formatted with 64KB block

  2. size (or cluster size or file allocation unit size). Unit size of write to the transaction log varies – they are not of fixed size, so go ahead and format the transaction log volume with 64KB cluster size but that will not provide any performance benefit as the log write unit size varies. Note that if the volume is already formatted with a unit other than 64KB, you would need to schedule a downtime to move all the files off of this volume format it with 64KB cluster size and then copy back the files. Choose the right RAID type:There are different opinions in the industry about choosing the RAID5 or RAID10 for database files. If your databases is mainly a read intensive (most of them are), then a RAID5 will provide adequate performance. If the DB is write-intensive, a RAID10 will provide better performance. Transaction Log files can be stored on a RAID 1 or RAID10 volume (but not RAID 5) References: http://technet.microsoft.com/library/Cc966500 SQL Server 2000 I/O Basics: SQL Server Best Practices Article: http://technet.microsoft.com/en- us/library/cc966412.aspx RAID Levels and SQL Server: http://technet.microsoft.com/enus/library/ms190764(v=sql.105).aspx Satish Kartan has been working with SQL Server for the past 20 years. To read more, please visit Satish Kartan's blog at http://www.sqlfood.com/ where he has shared more details on this.

More Related