未加星标

SQL Server Timeouts During Backups and CHECKDB

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二04 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏

So you’re hosting your SQL Server in the cloud say Amazon EC2, Azure VM, or Google Compute Engine and you’ve noticed that when you’re running a backup or a DBCC CHECKDB, you suffer from extreme performance problems.

Queries run slow, and even worse, applications report timeout errors even just trying to connect to SQL Server. More symptoms can include database mirroring and cluster failovers. What’s going on?

To understand it, let’s look at a quick sketch of your infrastructure:


SQL Server Timeouts During Backups and CHECKDB

I told you it was a fast sketch

Your SQL Server virtual machine lives on a physical host, and it accesses your storage via the network plain old Ethernet, the same Ethernet you’re using right now to surf our web site.

The advantage of Ethernet-connected storage is that it’s really, really cheap to build and manage.

The drawback of Ethernet-connected storage is that if your network connection isn’t really robust, then it’s really, really easy to saturate. 1Gb Ethernet maxes out at around 100MB/sec for comparison, a single $250 1TB SSD pushes around 500MB/sec. During high-throughput activities like backups and corruption checking, yourstorage is more than capable of pouring tons of data into your SQL Server thereby completely saturating your network connection.

It gets worse: in most cases, you’re not the only VM on a given host, so your cloud provider has to throttle your network throughput.

So your network connection matters a lot.

Faster (and/or separate) networks are certainly available to you in the cloud it’s just a matter of budget. For example, the excellent ec2instances.info lists all of the VM types at Amazon . It includes columns for Network Performance, plus a whole bunch of EBS columns that aren’t shown by default (click the Columns dropdown to see them):


SQL Server Timeouts During Backups and CHECKDB

EC2instances.info

Theeye-opening column isEBS Optimized: Throughput how much you can get from your storage in a best case scenario, typically streaming sequential reads like backups. (Don’t expect to get that from small random activities like OLTP database operations.)

Sorting by that column, here are your capabilities for the very largest VMs:


SQL Server Timeouts During Backups and CHECKDB

EC2 instances by throughput

While 1250 MB/sec is good, that’s also expensive: those two instance types are $8/hour and $19/hour. Once you’re past those, the throughput simply plummets right down to that single $250 SSD we were discussing earlier.

So what’s an admin to do?

One option is to bypass the network entirely. Note how in the architecture sketch, local ephemeral solid state storage isn’t hooked up through the network at all. Ephemeral storage is blazin’ fast and super cheap (included with most SQL-Server-sized instance types these days).

There’s just one little drawback: it can disappear at any time.

So if you’re going to use that for user databases, you have to protect your instances using technologies like Always On Availability Groups or database mirroring. Those can get you automatic failover with no data loss (not guaranteed, though), at the cost of slower deletes/updates/inserts due to synchronous writes across multiple servers.

Or, uh, you could just skip backups and CHECKDB. I wish I was joking, but I’ve seen more and more folks simply opt to run with scissors rather than have timeouts. That’s a bummer.

The cloud: giving you new ways to save money and run with scissors.

Erik says:The Cloud: Like getting a haircut from your ex-girlfriend.

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLSQL ServerEBS
分页:12
转载请注明
本文标题:SQL Server Timeouts During Backups and CHECKDB
本站链接:http://www.codesec.net/view/480452.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(28)