Over the past couple of months, SQLskills<\/a> has embarked on a new initiative to blog about basic topics, which we\u2019re calling SQL101. We\u2019ll all be blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101<\/strong> blog posts, check out SQLskills.com\/help\/SQL101<\/a>.<\/p>\n

The Importance of Sequential Throughput for SQL Server<\/strong><\/p>\n

A number of very common, important operations that are often executed by SQL Server are potentially performance limited by the sequential throughput of the underlying storage subsystem. These include:<\/p>\n

\n
1. Full database backups and restores\n
2. Index creation and maintenance work\n
3. Initializing transactional replication snapshots and subscriptions\n
4. Initializing AlwaysOn AG replicas\n
5. Initializing database mirrors\n
6. Initializing log-shipping secondary’s\n
7. Running DBCC CHECKDB\n
8. Relational data warehouse query workloads\n
9. Relational data warehouse ETL operations<\/li>\n<\/ol>\n

   Despite this, I often see DBAs having to contend with extremely low sequential performance on their various database servers, to the detriment of their ability to meet their SLAs<\/a> for things like RPO<\/a> and RTO<\/a> (not to mention their sanity). This being the case, what if anything can you do to improve this situation?<\/p>\n

   One thing you should do is to do some storage subsystem benchmarking with tools like CrystalDiskMark<\/a> and Microsoft DiskSpd<\/a>, to find out what the potential performance of each logical drive is on the underlying machine where your SQL Server instance is running. <\/p>\n

   You can also run some simple queries and tests from SQL Server itself to see what level of sequential performance you are actually getting from your storage subsystem (which is much harder for storage administrators, SAN administrators, and storage vendors to dispute). One example is running a full database backup to a NUL device, to see the ultimate sequential read performance from where your data and log files are located. Another example is running a SELECT query with an index hint to force the query to do a clustered index scan or table scan from a relatively large table.<\/p>\n

   Note:<\/u><\/strong> You should do these kinds of tests during a maintenance window or ideally, before a new instance of SQL Server goes into Production. Otherwise, your testing could negatively affect your Production environment or the other Production activity could skew your test results.<\/p>\n

   Beyond that, here are some general steps you can take to improve overall storage system performance:<\/p>\n

   \n
   1. Make sure you have power management configured correctly at all levels (BIOS power management, hypervisor power policy, and Windows Power Plan<\/a>)\n
   2. Make sure you have Windows Instant File Initialization<\/a> enabled\n
   3. Make sure you are not under memory pressure (to reduce stress on your storage subsystem)\n
   4. Make sure you are using the latest version of SQL Server\n
   5. Make sure you have installed the latest Service Pack and Cumulative Update for your version of SQL Server\n
   6. Favor Enterprise Edition or Standard Edition (because it has better I\/O performance)\n
   7. Use compression to reduce your I\/O requirements (backup compression, data compression, and columnstore indexes\n
   8. Make sure your indexes are tuned appropriately (not too many and not too few)\n
   9. Keep your index fragmentation under control<\/li>\n<\/ol>\n

      You can watch my Pluralsight course SQL Server: Improving Storage Subsystem Performance<\/a> to get more details about this subject. You can also read my article on SQLPerformance.com, Sequential Throughput Speeds and Feeds<\/a> to get some more technical details about sequential throughput.<\/p>\n","protected":false},"excerpt":{"rendered":"

      Over the past couple of months, SQLskills has embarked on a new initiative to blog about basic topics, which we\u2019re calling SQL101. We\u2019ll all be blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17,309,35],"tags":[312],"class_list":["post-1183","post","type-post","status-publish","format-standard","hentry","category-sql-server-hardware","category-sql101","category-sql-server-storage","tag-sql101"],"yoast_head":"\n