Database tuning approach
- Proactive approach
- Reactive approach
Best Approach: Use a mix of both.
Be aware (collect information) of the followings that may have significant impact on the performance.
Hardware: Server, Available memory, number of processors, disk subsystems.
Network Infrastructure: Network Cards, Switches, and the rest of your LAN and WAN.
Operating System: Make sure the OpSys is optimally configured for SQL Server.
Database Engine: Know about the SQL Server Architecture and about your operational environments.
Database: Tune database properly
Client Connection: How clients connect to the server
Monitoring and Tuning Hardware: Use Network Monitor Agent, Performance Logs and Alerts, System Monitor, and Task Manager.
System monitor (perfmon.exe) is a great tool for the purpose Using perfmon.exe you can also find where the bottleneck is Processor Subsystem, Memory Subsystem, I/O Subsystem
Monitoring and Tuning SQL Server:Tools:
- DBCC Commands
- Dynamic Management Views
- SQL Server Profiler
- SQL Server Management Studio
- System Stored Procedures
- SQL Trace
System Monitor: when SQL server is installed many new parameters (performance objects and their counters) are added to System Monitor (perfmon.exe) that help to monitor SQL Server performance.
SQL Server Profiler: This tool is used to capture traces like client activities, stored procedure calls, lock activities. SQL server profiler can help to determine if the client application is at fault.
SQL Trace: create traces using using stored procedures.
Tuning the tempdb system database
- Capacity plan and pre-allocate adequate space
- Separate the log file from the database file
- Use multiple secondary files for the tempDB system database
- Use a faster disk
- Use an appropriate RAID solution.
- Use local disk sybsystem for tempdb.
Tuning Database Layout
Use file and filegroup architecture properly.
File: Use multiple data files for your databases.
Filegroups:example: create two file groups and place them in different drives. Create tables in one file group and create non-clustered indexes in the other. table I/O and Index I/O are now divided.
From: http://sitestree.com/?p=4810
Categories:38
Tags:
Post Data:2012-08-07 00:06:40
Shop Online: <a href='https://www.ShopForSoul.com/' target='new' rel="noopener">https://www.ShopForSoul.com/</a>
(Big Data, Cloud, Security, Machine Learning): Courses: <a href='http://Training.SitesTree.com' target='new' rel="noopener"> http://Training.SitesTree.com</a>
In Bengali: <a href='http://Bangla.SaLearningSchool.com' target='new' rel="noopener">http://Bangla.SaLearningSchool.com</a>
<a href='http://SitesTree.com' target='new' rel="noopener">http://SitesTree.com</a>
8112223 Canada Inc./JustEtc: <a href='http://JustEtc.net' target='new' rel="noopener">http://JustEtc.net (Software/Web/Mobile/Big-Data/Machine Learning) </a>
Shop Online: <a href='https://www.ShopForSoul.com'> https://www.ShopForSoul.com/</a>
Medium: <a href='https://medium.com/@SayedAhmedCanada' target='new' rel="noopener"> https://medium.com/@SayedAhmedCanada </a>
