Reactive approach<\/li>\n<\/ul>\nBest Approach: Use a mix of both.<\/p>\n
Be aware (collect information) of the followings that may have significant impact on the performance.
Hardware: Server, Available memory, number of processors, disk subsystems. <\/p>\n
Network Infrastructure: Network Cards, Switches, and the rest of your LAN and WAN. <\/p>\n
Operating System: Make sure the OpSys is optimally configured for SQL Server. <\/p>\n
Database Engine: Know about the SQL Server Architecture and about your operational environments. <\/p>\n
Database: Tune database properly <\/p>\n
Client Connection: How clients connect to the server <\/p>\n
Monitoring and Tuning Hardware: Use Network Monitor Agent, Performance Logs and Alerts, System Monitor, and Task Manager. <\/p>\n
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 <\/p>\n
Monitoring and Tuning SQL Server:<\/b>Tools:<\/b><\/p>\n\n- DBCC Commands <\/li>\n
- Dynamic Management Views <\/li>\n
- SQL Server Profiler <\/li>\n
- SQL Server Management Studio <\/li>\n
- System Stored Procedures <\/li>\n
- SQL Trace<\/li>\n<\/ul>\n
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.<\/p>\n
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. <\/p>\n
SQL Trace: create traces using using stored procedures.<\/p>\n
Tuning the tempdb system database<\/b><\/p>\n\n- Capacity plan and pre-allocate adequate space <\/li>\n
- Separate the log file from the database file <\/li>\n
- Use multiple secondary files for the tempDB system database <\/li>\n
- Use a faster disk <\/li>\n
- Use an appropriate RAID solution. <\/li>\n
- Use local disk sybsystem for tempdb.<\/li>\n<\/ul>\n
Tuning Database Layout<\/b><\/p>\n
Use file and filegroup architecture properly.
File: Use multiple data files for your databases.<\/p>\n
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.<\/p>\n
From: http:\/\/sitestree.com\/?p=4810
Categories:38
Tags:
Post Data:2012-08-07 00:06:40<\/p>\n
Shop Online: <a href='https:\/\/www.ShopForSoul.com\/' target='new' rel=\"noopener\">https:\/\/www.ShopForSoul.com\/<\/a>\n (Big Data, Cloud, Security, Machine Learning): Courses: <a href='http:\/\/Training.SitesTree.com' target='new' rel=\"noopener\"> http:\/\/Training.SitesTree.com<\/a> \n In Bengali: <a href='http:\/\/Bangla.SaLearningSchool.com' target='new' rel=\"noopener\">http:\/\/Bangla.SaLearningSchool.com<\/a>\n <a href='http:\/\/SitesTree.com' target='new' rel=\"noopener\">http:\/\/SitesTree.com<\/a>\n 8112223 Canada Inc.\/JustEtc: <a href='http:\/\/JustEtc.net' target='new' rel=\"noopener\">http:\/\/JustEtc.net (Software\/Web\/Mobile\/Big-Data\/Machine Learning) <\/a>\n Shop Online: <a href='https:\/\/www.ShopForSoul.com'> https:\/\/www.ShopForSoul.com\/<\/a>\n Medium: <a href='https:\/\/medium.com\/@SayedAhmedCanada' target='new' rel=\"noopener\"> https:\/\/medium.com\/@SayedAhmedCanada <\/a>\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"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 … <\/p>\n
Continue reading<\/a><\/p>\n","protected":false},"author":8,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[1917],"tags":[],"class_list":["post-69264","post","type-post","status-publish","format-standard","hentry","category-fromsitestree-com","item-wrap"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":22515,"url":"http:\/\/bangla.sitestree.com\/?p=22515","url_meta":{"origin":69264,"position":0},"title":"SQL Server performance tuning using Windows Performance Monitor #Root #By Sayed Ahmed #MS SQL Server #SQL Server Reporting","author":"Author-Check- Article-or-Video","date":"March 15, 2021","format":false,"excerpt":"Microsoft SQL Server's performance can be monitored using the system performance and resource monitoring tool as comes with Windows Operating Systems. With that information, you can tune for optimum performance. I found the following resource to be very useful SQL Server performance tuning using Windows Performance Monitor: http:\/\/www.sqlshack.com\/sql-server-performance-tuning-using-windows-performance-monitor\/ You can\u2026","rel":"","context":"In "FromSitesTree.com"","block_context":{"text":"FromSitesTree.com","link":"http:\/\/bangla.sitestree.com\/?cat=1917"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":22513,"url":"http:\/\/bangla.sitestree.com\/?p=22513","url_meta":{"origin":69264,"position":1},"title":"SQL Server: Basics of Dynamic Management Views and DBCC Commands #Root #By Sayed Ahmed #MS SQL Server #SQL Server Reporting","author":"Author-Check- Article-or-Video","date":"March 15, 2021","format":false,"excerpt":"Inspired By: Tuning Queries: SQl Server - http:\/\/salearningschool.com\/displayArticle.php?table=Articles&articleID=1156&title=Tuning%20Queries:%20SQl%20Server What are these? Dynamic Management Views, and DBCC Commands Dynamic Management Views: \"Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.\" From: http:\/\/msdn.microsoft.com\/en-us\/library\/ms188754.aspx DBCC Statements:\u2026","rel":"","context":"In "FromSitesTree.com"","block_context":{"text":"FromSitesTree.com","link":"http:\/\/bangla.sitestree.com\/?cat=1917"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":69262,"url":"http:\/\/bangla.sitestree.com\/?p=69262","url_meta":{"origin":69264,"position":2},"title":"Dynamic Management Views #38","author":"Author-Check- Article-or-Video","date":"August 16, 2021","format":false,"excerpt":"Dynamic Management Views provide ways to analyze database performance and to find the cause behind bad-performing SQL server databases\/instances. Bad performance can be due to: poor design, insufficient memory, poorly configured system, disk bottlenecks, poorly written queries and many othersPreviously Windows System Monitor(perfmon.exe) and SQL server profiler were used to\u2026","rel":"","context":"In "FromSitesTree.com"","block_context":{"text":"FromSitesTree.com","link":"http:\/\/bangla.sitestree.com\/?cat=1917"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":70215,"url":"http:\/\/bangla.sitestree.com\/?p=70215","url_meta":{"origin":69264,"position":3},"title":"Must Read: Database Driven Software Development #106","author":"Author-Check- Article-or-Video","date":"August 29, 2021","format":false,"excerpt":"If you are developing database driven applications, you must need to know and clearly understand the following concepts. Yes, you also need to apply them in your design and implementation. If you do not follow proper database design strategies, many complexities will arise, adjusting new requirements may become cumbersome to\u2026","rel":"","context":"In "FromSitesTree.com"","block_context":{"text":"FromSitesTree.com","link":"http:\/\/bangla.sitestree.com\/?cat=1917"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":69274,"url":"http:\/\/bangla.sitestree.com\/?p=69274","url_meta":{"origin":69264,"position":4},"title":"SQL Server Short-notes at JustETC #38","author":"Author-Check- Article-or-Video","date":"August 16, 2021","format":false,"excerpt":"How to optimize query response time? How to optimize SQL Server Cursors What are cursors? SQL Server Hints Scalability: SQL Server 2005 Scaling Out Dynamic Management Views SQL Server Database System Tuning SQL Server: Index SQL Server : Backups SQL Server Short-notes at JustEtc SQL Server Short-notes at JustEtc From:\u2026","rel":"","context":"In "FromSitesTree.com"","block_context":{"text":"FromSitesTree.com","link":"http:\/\/bangla.sitestree.com\/?cat=1917"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":69272,"url":"http:\/\/bangla.sitestree.com\/?p=69272","url_meta":{"origin":69264,"position":5},"title":"SQL Server Short-notes at JustEtc #38","author":"Author-Check- Article-or-Video","date":"August 16, 2021","format":false,"excerpt":"Query Optimization How to optimize query response time? SQL Server How to optimize SQL Server Cursors What are cursors? SQL Server Hints Scalability: SQL Server 2005 Scaling Out Dynamic Management Views SQL Server Database System Tuning SQL Server: Index SQL Server : Backups From: http:\/\/sitestree.com\/?p=5221 Categories:38Tags: Post Data:2012-10-04 19:57:27 Shop\u2026","rel":"","context":"In "FromSitesTree.com"","block_context":{"text":"FromSitesTree.com","link":"http:\/\/bangla.sitestree.com\/?cat=1917"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts\/69264","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/users\/8"}],"replies":[{"embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=69264"}],"version-history":[{"count":0,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts\/69264\/revisions"}],"wp:attachment":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=69264"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=69264"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=69264"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}