{"id":6319,"date":"2018-07-18T11:41:54","date_gmt":"2018-07-18T16:41:54","guid":{"rendered":"http:\/\/www.filecloud.com\/blog\/?p=6319"},"modified":"2020-03-12T17:54:07","modified_gmt":"2020-03-12T22:54:07","slug":"the-art-of-database-performance-optimization","status":"publish","type":"post","link":"https:\/\/www.filecloud.com\/blog\/the-art-of-database-performance-optimization\/","title":{"rendered":"The Art of Database Performance Optimization"},"content":{"rendered":"<p>Advancements in relational database management systems (RDBMS) and availability of better hardware have made sure that response times for SQL queries are going down. However, there is still a lot that a developer can do while designing the database and writing SQL queries that can help optimize database performance. It\u2019s also important to steer clear of the common mistakes that database admins (DBAs) commit. Because most of the databases use the same design concepts, DBAs have every reason to advance their understanding of database performance optimization. In this guide, we\u2019ll talk at length about how the best in the industry do it.<\/p>\n<p>\u00a0<\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\"alignnone size-full wp-image-6320\" src=\"https:\/\/www.filecloud.com\/blog\/wp-content\/uploads\/2018\/07\/secure-DB.jpg\" alt=\"\" width=\"474\" height=\"310\"><\/p>\n<p>\u00a0\n<\/p>\n<h2><\/h2>\n<h2>Investing In A Good Monitoring System<\/h2>\n<p>A monitoring system is the biggest asset in the hands of system administrators. A wholesome monitoring solution is effectively half your database performance optimization headaches solved.<\/p>\n<p>The system that you choose must be able to comprehensively showcase details of systems, tools, and applications from your IT ecosystem. Such a system goes a long way in helping database admins:<\/p>\n<ul>\n<li>Maintain complete oversight of the database operations across the IT landscape<\/li>\n<li>Creating alerts based on certain kind of information requests and workloads<\/li>\n<li>Get automated warnings of anomalous database use requests<\/li>\n<li>Quickly locating choke points and bottlenecks in the databases<\/li>\n<li>Taking preemptive action to secure, maintain, and optimize databases<\/li>\n<\/ul>\n<p>\u00a0<\/p>\n<h2>Database Statistics<\/h2>\n<p>For any SQL optimizer, one of the most important resources is in the form of database statistics. These stats comprise the following information:<\/p>\n<ul>\n<li>Tables in the catalog<\/li>\n<li>Indexes of the tables<\/li>\n<li>Interrelationships between these indices<\/li>\n<\/ul>\n<p>Optimizers use these stats to dynamically decide the least expensive path to serve a query. In many database performance audits, it\u2019s generally found that stats are outdated. The result, queries won\u2019t be served using the least expensive paths. This increases the overall response time of the database and sends performance KPIs for a toss.<\/p>\n<p>Database admins need to always ensure that stats for their databases remain updated. For whichever RDBMS product you\u2019re using, the product manual will have all the necessary information on the commands you can execute to update stats.<\/p>\n<p>\u00a0<\/p>\n<p>\u00a0<\/p>\n<h2>Determine the Expected Growth<\/h2>\n<p>Indices are a double-edged sword. Without them, your select queries will take too long to return results. Too many indices, on the other hand, reduce the performance of DML queries (insert, update, and delete).<\/p>\n<p>Indices can have a significant negative impact on DML queries. While creating an index, DBAs can specify a value for the fill factor. This helps reduce the potential negative impact.<\/p>\n<p>When you create an index, the data in the column is stored on disk. Next, when new rows of data are created, or the values of the data elements within the column are changed, then the index needs to be reorganized. This takes a toll on DML queries.<\/p>\n<p>A solution is to specify the expected growth for an index when you know that new rows of data will be regularly added. In some RDBMS solutions, this option is known as fill factor, and in others, it\u2019s called PCTFREE (percent free).<\/p>\n<p>\u00a0<\/p>\n<h2>Specify Indices in Select Queries<\/h2>\n<p>Mostly, the optimizer chooses the appropriate index for particular table based on statistics. However, you can also specify the index name in the select query. Most databases offer this option, using which you can take control of the performance of regularly used select queries. The syntax and method of specifying an index within a select query differ across databases.<\/p>\n<p>\u00a0<\/p>\n<h2>Evaluation Database Explanations<\/h2>\n<p>The database returns an explanation for every select query created by the optimizer. This explanation can provide tremendous insights to DBAs in their efforts to optimize databases. Using the explanations, they can fine tune SQL queries and deliver significant database performance improvements. Each database produces offers its unique syntax for DBAs to use to get the explanations. Also, we recommend you try out one of the many 3rd party tools to run explanation commands against databases. For instance, WinSQL Professional is one of the renowned tools that also offer database query explanations analysis as one of its features.<\/p>\n<p>\u00a0<\/p>\n<h2>Database Splitting in Hard Drives<\/h2>\n<p>You might already know the speed limitations of input-output operations for hard disks. When the size of your databases increases, these limitations become all the more prominent.<\/p>\n<p>Some databases, thankfully, allow databases to be split across multiple hard disks. Some even allow splitting tables across different hard disks. Since more heads work simultaneously to fetch data in such an arrangement, the speed of operations is tremendously improved.<\/p>\n<p>\u00a0<\/p>\n<p>\u00a0<\/p>\n<h2>Limit the Amount of Data in Play<\/h2>\n<p>Lesser the amount of data retrieved, faster will the response be. Kind of obvious? Well, only if this were followed, most enterprise database performance measures would be a lot better. Most of the data filtering must be carried out at the server end and only a minimal amount of it kept at the client end. Because of this, limited data is sent on the wire, and the response speeds are correspondingly better.<\/p>\n<p>The database level optimizations are not limited to query design improvements. There\u2019s a lot that database administrators can achieve merely by understanding database performance with the help of a strong monitoring system.<\/p>\n<p>\u00a0<\/p>\n<h2>Collaboration between Database Developers and Administrators<\/h2>\n<p>In most enterprises and SMBs, DBAs mostly handle database tuning tasks. However, there are several developers that essentially do DBA like tasks. However, developers and administrators don\u2019t always find it easy to work along with each other. This is caused because of factors such as different key responsive areas (KRAs), different reporting structures, and often, a lack of interpersonal skills.<\/p>\n<p>Developers need to understand the reason why DBAs are not inclined to embrace rapid changes. That\u2019s because they need to view and manage the database as a whole. The impact of the smallest of database problems could be gigantic. DBAs, in turn, should understand the developers\u2019 need to know the status of the database and assist them in carrying out tests in quality clients.<\/p>\n<p>\u00a0<\/p>\n<h2>Concluding Remarks<\/h2>\n<p>Start with the technical aspects of database performance management, then move on to monitoring and insight drove improvements, followed by a focus on driving synergies among DBAs and database developers.<\/p>\n<p>\u00a0<\/p>\n<p><em><strong>Author: Rahul Sharma<\/strong><\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Advancements in relational database management systems (RDBMS) and availability of better hardware have made sure that response times for SQL queries are going down. However, there is still a lot that a developer can do while designing the database and writing SQL queries that can help optimize database performance. It\u2019s also important to steer clear [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[17],"tags":[957,953,955,954,956],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v20.13 (Yoast SEO v20.13) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>The Art of Database Performance Optimization - FileCloud blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.filecloud.com\/blog\/the-art-of-database-performance-optimization\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The Art of Database Performance Optimization\" \/>\n<meta property=\"og:description\" content=\"Advancements in relational database management systems (RDBMS) and availability of better hardware have made sure that response times for SQL queries are going down. However, there is still a lot that a developer can do while designing the database and writing SQL queries that can help optimize database performance. It\u2019s also important to steer clear [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.filecloud.com\/blog\/the-art-of-database-performance-optimization\/\" \/>\n<meta property=\"og:site_name\" content=\"FileCloud blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/tonidopage\" \/>\n<meta property=\"article:published_time\" content=\"2018-07-18T16:41:54+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-03-12T22:54:07+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.filecloud.com\/blog\/wp-content\/uploads\/2018\/07\/secure-DB.jpg\" \/>\n<meta name=\"author\" content=\"Team FileCloud\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@getfilecloud\" \/>\n<meta name=\"twitter:site\" content=\"@getfilecloud\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Team FileCloud\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.filecloud.com\/blog\/the-art-of-database-performance-optimization\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.filecloud.com\/blog\/the-art-of-database-performance-optimization\/\"},\"author\":{\"name\":\"Team FileCloud\",\"@id\":\"https:\/\/www.filecloud.com\/blog\/#\/schema\/person\/8a8df071f564aa2c10fa07d6ce60c935\"},\"headline\":\"The Art of Database Performance Optimization\",\"datePublished\":\"2018-07-18T16:41:54+00:00\",\"dateModified\":\"2020-03-12T22:54:07+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.filecloud.com\/blog\/the-art-of-database-performance-optimization\/\"},\"wordCount\":1041,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.filecloud.com\/blog\/#organization\"},\"keywords\":[\"Database Monitoring\",\"Database Optimization\",\"DBA\",\"DBMS\",\"SQL\"],\"articleSection\":[\"Productivity\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.filecloud.com\/blog\/the-art-of-database-performance-optimization\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.filecloud.com\/blog\/the-art-of-database-performance-optimization\/\",\"url\":\"https:\/\/www.filecloud.com\/blog\/the-art-of-database-performance-optimization\/\",\"name\":\"The Art of Database Performance Optimization - FileCloud blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.filecloud.com\/blog\/#website\"},\"datePublished\":\"2018-07-18T16:41:54+00:00\",\"dateModified\":\"2020-03-12T22:54:07+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.filecloud.com\/blog\/the-art-of-database-performance-optimization\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.filecloud.com\/blog\/the-art-of-database-performance-optimization\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.filecloud.com\/blog\/the-art-of-database-performance-optimization\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.filecloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The Art of Database Performance Optimization\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.filecloud.com\/blog\/#website\",\"url\":\"https:\/\/www.filecloud.com\/blog\/\",\"name\":\"FileCloud blog\",\"description\":\"Topics on Private cloud, On-Premises, Self-Hosted, Enterprise File Sync and Sharing\",\"publisher\":{\"@id\":\"https:\/\/www.filecloud.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.filecloud.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.filecloud.com\/blog\/#organization\",\"name\":\"FileCloud\",\"url\":\"https:\/\/www.filecloud.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.filecloud.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.filecloud.com\/blog\/wp-content\/uploads\/2016\/02\/filecloud_logo_comparison.jpg\",\"contentUrl\":\"https:\/\/www.filecloud.com\/blog\/wp-content\/uploads\/2016\/02\/filecloud_logo_comparison.jpg\",\"width\":155,\"height\":40,\"caption\":\"FileCloud\"},\"image\":{\"@id\":\"https:\/\/www.filecloud.com\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/tonidopage\",\"https:\/\/twitter.com\/getfilecloud\",\"https:\/\/www.linkedin.com\/company\/codelathe\",\"https:\/\/www.pinterest.com\/filecloud\/filecloud\/\",\"https:\/\/www.youtube.com\/channel\/UCbU5gTFdNCPESA5aGipFW6g\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.filecloud.com\/blog\/#\/schema\/person\/8a8df071f564aa2c10fa07d6ce60c935\",\"name\":\"Team FileCloud\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.filecloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/b5818ab931b69298f500d8a184fd2384?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/b5818ab931b69298f500d8a184fd2384?s=96&d=mm&r=g\",\"caption\":\"Team FileCloud\"},\"sameAs\":[\"http:\/\/www.filecloud.com\"],\"url\":\"https:\/\/www.filecloud.com\/blog\/author\/admin\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"The Art of Database Performance Optimization - FileCloud blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.filecloud.com\/blog\/the-art-of-database-performance-optimization\/","og_locale":"en_US","og_type":"article","og_title":"The Art of Database Performance Optimization","og_description":"Advancements in relational database management systems (RDBMS) and availability of better hardware have made sure that response times for SQL queries are going down. However, there is still a lot that a developer can do while designing the database and writing SQL queries that can help optimize database performance. It\u2019s also important to steer clear [&hellip;]","og_url":"https:\/\/www.filecloud.com\/blog\/the-art-of-database-performance-optimization\/","og_site_name":"FileCloud blog","article_publisher":"https:\/\/www.facebook.com\/tonidopage","article_published_time":"2018-07-18T16:41:54+00:00","article_modified_time":"2020-03-12T22:54:07+00:00","og_image":[{"url":"https:\/\/www.filecloud.com\/blog\/wp-content\/uploads\/2018\/07\/secure-DB.jpg"}],"author":"Team FileCloud","twitter_card":"summary_large_image","twitter_creator":"@getfilecloud","twitter_site":"@getfilecloud","twitter_misc":{"Written by":"Team FileCloud","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.filecloud.com\/blog\/the-art-of-database-performance-optimization\/#article","isPartOf":{"@id":"https:\/\/www.filecloud.com\/blog\/the-art-of-database-performance-optimization\/"},"author":{"name":"Team FileCloud","@id":"https:\/\/www.filecloud.com\/blog\/#\/schema\/person\/8a8df071f564aa2c10fa07d6ce60c935"},"headline":"The Art of Database Performance Optimization","datePublished":"2018-07-18T16:41:54+00:00","dateModified":"2020-03-12T22:54:07+00:00","mainEntityOfPage":{"@id":"https:\/\/www.filecloud.com\/blog\/the-art-of-database-performance-optimization\/"},"wordCount":1041,"commentCount":0,"publisher":{"@id":"https:\/\/www.filecloud.com\/blog\/#organization"},"keywords":["Database Monitoring","Database Optimization","DBA","DBMS","SQL"],"articleSection":["Productivity"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.filecloud.com\/blog\/the-art-of-database-performance-optimization\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.filecloud.com\/blog\/the-art-of-database-performance-optimization\/","url":"https:\/\/www.filecloud.com\/blog\/the-art-of-database-performance-optimization\/","name":"The Art of Database Performance Optimization - FileCloud blog","isPartOf":{"@id":"https:\/\/www.filecloud.com\/blog\/#website"},"datePublished":"2018-07-18T16:41:54+00:00","dateModified":"2020-03-12T22:54:07+00:00","breadcrumb":{"@id":"https:\/\/www.filecloud.com\/blog\/the-art-of-database-performance-optimization\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.filecloud.com\/blog\/the-art-of-database-performance-optimization\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.filecloud.com\/blog\/the-art-of-database-performance-optimization\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.filecloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"The Art of Database Performance Optimization"}]},{"@type":"WebSite","@id":"https:\/\/www.filecloud.com\/blog\/#website","url":"https:\/\/www.filecloud.com\/blog\/","name":"FileCloud blog","description":"Topics on Private cloud, On-Premises, Self-Hosted, Enterprise File Sync and Sharing","publisher":{"@id":"https:\/\/www.filecloud.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.filecloud.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.filecloud.com\/blog\/#organization","name":"FileCloud","url":"https:\/\/www.filecloud.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.filecloud.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.filecloud.com\/blog\/wp-content\/uploads\/2016\/02\/filecloud_logo_comparison.jpg","contentUrl":"https:\/\/www.filecloud.com\/blog\/wp-content\/uploads\/2016\/02\/filecloud_logo_comparison.jpg","width":155,"height":40,"caption":"FileCloud"},"image":{"@id":"https:\/\/www.filecloud.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/tonidopage","https:\/\/twitter.com\/getfilecloud","https:\/\/www.linkedin.com\/company\/codelathe","https:\/\/www.pinterest.com\/filecloud\/filecloud\/","https:\/\/www.youtube.com\/channel\/UCbU5gTFdNCPESA5aGipFW6g"]},{"@type":"Person","@id":"https:\/\/www.filecloud.com\/blog\/#\/schema\/person\/8a8df071f564aa2c10fa07d6ce60c935","name":"Team FileCloud","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.filecloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/b5818ab931b69298f500d8a184fd2384?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/b5818ab931b69298f500d8a184fd2384?s=96&d=mm&r=g","caption":"Team FileCloud"},"sameAs":["http:\/\/www.filecloud.com"],"url":"https:\/\/www.filecloud.com\/blog\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/www.filecloud.com\/blog\/wp-json\/wp\/v2\/posts\/6319"}],"collection":[{"href":"https:\/\/www.filecloud.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.filecloud.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.filecloud.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.filecloud.com\/blog\/wp-json\/wp\/v2\/comments?post=6319"}],"version-history":[{"count":2,"href":"https:\/\/www.filecloud.com\/blog\/wp-json\/wp\/v2\/posts\/6319\/revisions"}],"predecessor-version":[{"id":26005,"href":"https:\/\/www.filecloud.com\/blog\/wp-json\/wp\/v2\/posts\/6319\/revisions\/26005"}],"wp:attachment":[{"href":"https:\/\/www.filecloud.com\/blog\/wp-json\/wp\/v2\/media?parent=6319"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.filecloud.com\/blog\/wp-json\/wp\/v2\/categories?post=6319"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.filecloud.com\/blog\/wp-json\/wp\/v2\/tags?post=6319"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}