Search
Subscribe
Categories
- Backup and Restore
- Configure
- Contact me
- CPU
- Data Architecture
- Data Import and Export
- Database Mirroring
- DBA
- DBA Scripts
- Deadlocks
- Development
- Disaster Recovery
- Disk Storage
- DMV
- Documentation
- Event Log
- Function
- High Availability
- Indexes
- Install & Setup
- Interviews
- IO Subsystem
- Jobs
- Links
- Locking
- Memory
- Monitor Performance
- MSDB
- Network
- Object Management
- Pagination
- Partitioning
- Performance
- Powershell
- Security Management
- SQL Agent
- SQL Azure
- SQL Cachestore
- SQL Error Logs
- SQL Optimizer
- SQL problems
- SQL Server Error Tips
- SQL Server Management Studio
- SQL Server Profiler
- SQL Server Tuning
- SQL View
- SQLServer-DBA News
- SSIS
- Statistics
- T-SQL
- TempDB
- Tools
- TPC-H
- Transaction Logs
- Virtualization
- Wait Stats
Recent Posts
- How to check Operating System type with Powershell
- ALTER TRACE and developers
- Predict ALTER INDEX REORGANIZE finish time
- Cannot connect to SQL Server
- Timeout occurred while waiting for latch: class 'LOG_MANAGER'
- The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time
- How to create a SQL Server Security Audit
- Cannot use Large Page Extensions: lock memory privilege was not granted
- Compare two files with Powershell
- Export-CSV Powershell
To many databases per instance.
There is no ideal number of databases per instance. Indicators are:
a)Bufferpol thrashed. For example, large table scans can cause buffer pool thrashing
b)Large amount of lazy writing – the SQL Server lazy writer maintains free buffers by monitoring for low frequency used pages and removing from the buffer cache.
The least used algorithm decides which data is pushed out of the buffer pool – working with the lazy writer.
Only one transaction log file per database is required.
There is no performance gain from maintaining multiple transaction log files.
Keep Auto Grow turned on .
Maintain a fixed amount , not a percentage. Attempt to forecast the data file size and set the size, this decreases the auto grow frequencies.
Keep Auto Shrink off.
Configuring AUTO_SHRINK to OFF – means the files are not automatically shrunk – when checks occur for unused space. I execute an Shrink when a permanent data delete occurs. If it’s a recurring insert and delete then I don’t Shrink .
Multiple filegroups.
The biggest advantage of multiple filegroups is to split multiple files across disks and multiple disk controllers.
By allowing a heavily used table to be placed in a specified filegroup – the IO can be isolated on a specific disk. Therefore distributing IO across multiple disks.
Check : SQL Server – files and filegroups. Improving database performance
Instant file initialisation.
Can offer excellent performance gain. It’s managed through the SE_MANAGE_VOLUME_NAME special privilege.
Check my post: SQL Server faster restores with instant file initialisation
Minimise physical IO.
Data in memory is also called buffer cache. Logical IO is used.
If data is not in memory than it’s physical IO.
The objective is to minimise physical IO and maximise logical IO
Performance Tuning Disk.
Disk block size tuning is a determinant for Disk IO performance. Read : Disk IO performance , disk block size tuning and SQL Servers
There are many other aspects to SQL Server database files management , including RAID configuration and drive location. The topics listed in this post: to many databases,one log per database,Auto Grow,Auto Shrink,Multiple filegroups,instant file initialization,Minimise physical IO,Disk Tuning are very useful and if implemented strategically have many positive benefits. Let me know your feedback , including experience
Author: Jack Vamvas (http://www.sqlserver-dba.com)Author: Jack Vamvas (http://www.sqlserver-dba.com)
Verify your Comment
Previewing your Comment
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |