2

I have recently taken a job as a Senior Database Administrator at a small but rapidly growing company. My first order of business was a clean up the logical design of the main database (adding primary and foreign keys to existing tables, removing database objects that were no longer in use, eliminating redundant indexes, adding missing indexes, etc) but now that the preliminary logical cleanup is complete I have turned myself to the physical design.

The current production setup is that we have a physical database server, 4 sockets, 16 cores, hyper-threaded to have 32 logical processors with 256 GB of memory. CPU utilization, even during the busiest times, is typically under 15%.

For storage we have four drives, C, D, E and T, with C for the OS, D for the data files, E for logs and T for TempDB. The size of the primary data file for the main database is currently 650 GB, but is growing since the company is growing. There are 700 GB free on the D: drive, which is an SSD in Raid 10, with 1.45 TB of capacity in total.

We are using SQL Server 2014, but the main database is in 2008 compatibility mode, due to a performance issue with one of the stored procedures which occurs in 2012/2014 compatibility mode (In 2014 compatibility the performance problems are consistent, in 2012 compatibility the performance issue only presents itself under load)

The intermittent change that I would like to make, is to take the one 650 GB data file for the main database and split it into 2 file groups (one for the main data, the other for non-clustered indexes) with 8 files per file group. So my questions are this:

1) Should I expect any gains in performance or reliability breaking up the main data file in the manner I described?

2) How do I make these changes on a production database server where I can schedule (at most) a 30 minute downtime window per day?

Also note that while I have access to a number of servers I could use to test these changes, prior to making them in production, the non-production hardware is under-powered compared to actual production. For example, the box I am planning to use for my tests hosts a production database copy which is refreshed daily. It is a virtual machine with a 8 cores, 35.6 GB of memory and the D: Drive is a virtual disk hosted on a SAN, which has 1.19 TB of total space. So while I can test my changes on this machine, I cannot be confident that any performance increases on this machine will be representative of actual production.

So far all attempts to move towards the state I have described have involved the session_id for my session getting stuck during a DbccFilesCompact command. When this happens, the percent_complete does not increment, and the SPID is suspended, and dm_exec_requests shows it cycling through PAGEIOLATCH_SH waits on various resources. I'm not sure how long it would need to complete (if it could complete), but I have let in run for hours at a time with no progress. I can kill the process and that kill resolves immediately, but when the process is killed no progress is made towards the desired end state.

Has anyone faced this problem in their own companies? If so, what was the resolution?

migrated from stackoverflow.com Feb 11 '17 at 14:22

This question came from our site for professional and enthusiast programmers.

  • There are numerous counters you can use that tell you if you have 'memory pressure' - that is: not enough RAM. You're best off finding a guide on that and getting some metrics before adding RAM. I've always found this guy to know his stuff: brentozar.com/archive/2013/09/… – Nick.McDermaid Feb 10 '17 at 5:11
  • 3
    Please note that the extreme specificity of this question to your particular environment will limit the likelihood of you getting an authoritative and satisfying answer. The chances of someone else having had the exact same situation as you, including the particular types of data, indexes, and workloads, and then being able to advise you concretely is very low. At best, people can perhaps give you some general ideas--then when you've actually figured out what worked, you can come back and answer your own question. – ErikE Feb 16 '17 at 18:11
  • 3
    The idea of stackexchange is to have answers that will work in general for all audiences who have the same question, and at times, while we all wish to help people with their database problems, the question is just too embroiled in specific particulars that can't apply generally to a broader audience, and it becomes difficult to give an exact answer. All this said, I think it is a good enough question to stay open, and I'm curious to see the answer, myself! Please don't let this discourage you from asking other questions here. – ErikE Feb 16 '17 at 18:13
  • 2
    Could you please be more specific as to what you're asking and the details surrounding your circumstance? It's not entirely clear to me if your storage is DAS or SAN? Are all disks SSD? What are the other disks? (e.g. SAS 15k, SCSI, etc.) Are they all configured the same? Are these logical volumes on one physical disk, or are they all separate disks with separate channels? If you're on a SAN, who is the vendor and which model is it? Do you have a Fiber Channel link to the controller, or are you on iSCSI? How fast is that link? – swasheck Feb 16 '17 at 20:02
  • 3
    @MatthewSontum when you have the answers feel free to notify me – swasheck Feb 16 '17 at 22:51
3

I am not a SQL Server DBA, I use MySQL for my jobs. But some truths are universal. :-)

1) Should I expect any gains in performance or reliability breaking up the main data file in the manner I described?

I have observed that the benefit of any scalability optimization depends on the queries you run.

It's practically the definition of optimization that any method of optimization will improve one type of query workload as it worsens some other types of workloads.

2) How do I make these changes on a production database server where I can schedule (at most) a 30 minute downtime window per day?

I would set up a replica server (I gather in SQL Server land these are called Subscribers). Restructure the physical tablespaces of the replica. Then it doesn't matter how long it takes, as long as it can catch up to the master after the restructure is done.

I'm assuming SQL Server replication is asynchronous and also agnostic about physical layout, as MySQL replication is. But I don't know that SQL Server supports this.

Once you're done restructuring the replica, and it catches up to be in sync with its master, you can "cut over" probably by changing DNS entries for the respective server instances. Then the downtime is minimal, and brief, regardless of the size of the databases. The downtime should include a few moments to make sure all changes from the master have "drained" and been applied fully to the replica.

The tradeoff being that you need an extra set of DB server(s), at least temporarily. This is a case where using virtual servers or cloud servers can really save you!

  • SQL Server is different enough from MySQL that I don't think any of what you've written is directly applicable. However reading your reply did get me thinking along a train of thought that I hadn't previously considered, so in that regard it was helpful. I'll check back tomorrow / over the weekend and if there isn't a better answer I'll give you some points. – Matthew Sontum Feb 10 '17 at 3:49
  • 1
    Log Shipping would be a better approach than replication for a "quick" cutover – Kevin3NF Feb 14 '17 at 15:52
3
+25

Breaking up the data file as described would almost certainly have no performance benefit. The practice of splitting clustered and non-clustered indexes may benefit if you put them on separate mechanical arrays, but not on a single array, SSD or otherwise. Although, if you were maxing out throughput on your one SSD drive/array, splitting out to two SSD drives/arrays could theoretically benefit depending on controllers etc.

Your server is much more likely to benefit from getting Tempdb and its log on to SSD.
And if/when your workload is write-heavy, having the data logs on SSD would also likely give a substantial boost.
And if you've been surviving with the current config, then I think you can be certain you won't see a downside to having all your database and log files on the same SSD array (though perhaps partitioned for space management).

  • Yes, this move is a test for some of the partitioning work I want to do later on, but I thought it could be helpful as a standalone. Why do you feel tempdb is a good candidate for an SSD? From what I've read a high number of rewrites is just going to burn out the disk. Also, when you say splitting up the data file will have no performance benefit, are you saying this because the data drive is an SSD, or in the general case? – Matthew Sontum Feb 15 '17 at 3:07
  • @MatthewSontum Bear in mind that my suggestions have a lot of "likely"s etc. in them, and are in the context of my understanding that you're trying to squeeze as much performance as possible from the current hardware. For tempdb on SSDs, yes the drive lifespan will likely be reduced, but it might be worth balancing that against the possible performance drag on your usage. This gives a fuller picture: sqlperformance.com/2014/11/io-subsystem/… . (Continues next). – T.H. Feb 15 '17 at 10:05
  • For splitting up the data file I'm suggesting you'd have to have two independent arrays to see performance benefit, whether SSD or not. And your usage and hardware subsystems would have to be pretty extreme to see benefit with SSDs. – T.H. Feb 15 '17 at 10:06
  • So when people put their tempdb on an SSD what do they do? Just swap drives as they burn out? I'm not really a hardware guy but I'm supposed to make recommendations to the IT group and I wouldn't feel right making recommendations that significantly impact their responsibilities. But thanks for commenting, I like your answer better now, but I want to see more answers so I am going to keep the bounty open. – Matthew Sontum Feb 15 '17 at 12:00
  • 3
    @MatthewSontum "Why do you feel tempdb is a good candidate for an SSD? From what I've read a high number of rewrites is just going to burn out the disk." Everything is a good candidate for SSD these days, get what you can afford with the endurance you need. – Jack Douglas Feb 16 '17 at 22:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.