Very Large Databases Lessons Learned

[Music] starting now the broadcast is now starting all attendees are in listen-only mode good morning this is Steve can't roll with the last deep recession for a DBA fundamentals today's session is going to be very large data bases lessons learned given by Bob Moosa Terry we're really looking forward to this one especially at our company and we have some very large data bases that are growing like crazy and it's it's becoming more and more of a challenge to deal with them I've got a few other things to talk about first we always have to give our thanks to our sponsor century one formally sequel century they help us with our giveaways and and other things that help support our chapter speaking of a giveaway we will be giving away for this quarter a $750 Amazon gift card that will be drawn from everybody that has attended session since September every person gets one entry per session we'll go in and kind of clean out any duplicates because some people think they're gonna get a little bit Vantage of by signing on with multiple sign ons but that's not going to happen anyway everybody gets one attempt per session and we'll draw that and we'll have that in the next couple of days anyway century 1 is a fantastic site to go to for freebies of course they sell stuff too that's there in business but you can get a lot of free ebooks we all were always talking about plan explore and they give away their professional version now if you have ever looked at execution plans do you ought to give this a try and see how you like it it's fantastic anyway Pass has a lot of different virtual chapters and if you go to sequel path org /vc you can sign up and this is for the people that get to us through YouTube you can attend live sessions and get free giveaways and at the same time you can ask all the questions in the world that you want and even if the speakers don't have time during the sessions they'll blog about it and they'll give you specific answers to specific problems not necessarily online but they try to look at questions and and give answers that will help more people but they will answer your questions afterwards so you really need to sign up there's tons of different different virtual chapters as well as local chapters on just about any topic and most of the major languages here's an example of some of our meetings coming up obviously this one when he had this one last night that recording will be up pretty soon in December we'll be doing well when this fell over clustering by Wyndham Edmonds Sarmiento performance tuning methodologies Kevin Kline sees it then we have a lot of sequel skills sessions coming up in January February and March and April so while you need to go check those out here's some selected sessions coming up the rest of the month i chained ers doing one on transactional replication deep dive practically every side out there still uses transactional replication and it's very useful in certain situations see Saturday night sequel coming up this Saturday is having indexing fundamentals by Kathy Kellenberger she's very good speaker she's with the Women in Technology chapter you need to check out their sessions and definitely go to these we record all of our sessions and we post them on our YouTube site and you can also get these scripts and slides if you from our meeting archives side okay today I'm gonna go ahead and introduce Bob Bob has is a Microsoft Certified Master he's been working with for sequel with sequel server for over nine years he's currently a database administrator now his BIOS at Northwestern University in Chicago where he maintains huge databases but he just told me a few minutes ago that he's at a new company and their databases are not quite the size that he was dealing with in the past but he has got a huge amount of experience with very large databases and he's getting ready to do a presentation on that let me swap over to Bob make you the presenter all right I think that's being broadcast now yes looks like it all right excellent well thank you very much Steve for the introduction um welcome to vld B's lessons learned as Steve said I'm Bob Cousy Terry I am now a senior database administrator at Insano which is a managed services company have offices in Chicago and that's where I work out of um by way of a very brief introduction about the rest of myself yes I'm a Microsoft Certified Master I'm a friend of red gate I blog on sequel server and related topics at opposite Ericom you can find me on twitter at sequel Bob and I'm also a co chapter leader of the downtown Chicago sequel server user group which is another past chapter so we're talking about in very large databases today and one of the points I think to make the very beginning is what is very large some people may see 500 gigabyte databases is becoming rather large others may say no it's a terabyte and still others may say you know well I got 5 terabytes or more it's very relative within an organization as to what's considered a very large database speaking of other things that are very large this is Oliver he's my very large orange kitty he weighs in it about 15 pounds and I'm gonna guess that most of that is not muscle he likes to eat and he likes to chew on things including people unfortunately so if you were to actually as a brief aside go search the internet for cat bites suck you could find a nice blog post of mine on how he decided to chew on my wife a couple years ago and things turned out very very badly going back to databases so what's a very large database my personal favorite definition of a very large database is larger than an Excel spreadsheet can hold which I know I get some laughs but in reality a very large database is probably anything that's large enough that your standard DBA tasks now need to chain as soon as your is large enough where your standard rules no longer apply well you probably have a very large database so why are we worrying about this anyway what why do we care about this anyway obviously we have a large number of attendees here today to learn about very large databases and for very good reason as DBAs generally the amount of data we have is is going to be constantly growing hopefully all your databases in your environment are getting larger if the size of your environment is shrinking that may be a good sign to start getting your resume ready and preparing to move elsewhere vldb skills are very generally in demand the more job postings I see lately will mention vldb or very large databases so clearly the demand is there and so this presentation is going to be a collection of not best practices but good ideas that I've gathered over five-and-a-half years of working with very large databases in a previous position I had I was dealing with sequel Server databases of up to 60 terabytes in size that's per database not the entire server so we had a lot of techniques we had to employ to keep things manageable and working well for all of our users every system is different every system has different constraints different environments have different requirements but what we'll talk do today are things that I found worked well for me and the environments that I've been exposed to so first of all let's talk about the instance layout one of the first vldb environments I ever walked into was just that it was a really large single database and that was the only user database on that server there was a whole bunch of systems that fit into that database but it was one database and to make things even more interesting the production or the development and the test environments and the production environment were all in that database they were just in different schemas I needless to say that didn't really work out very well so hopefully you'll find a very large database environment with multiple databases segregated by the applications or the use cases it can more work to maintain multiple databases but you get a lot more flexibility when you start doing that databases are by definition in sequel server units of consistency we have one transaction log per database everything in the database is transactionally consistent against that log and when you start having multiple applications which have nothing to do with each other other than the fact that they're referencing the same database you're now essentially enforcing consistency between systems that may not need to be consistent at all this can lead to complications because other things are per database level as well all right backups and restores are at the database level you can backup you can restore the entire database there's no native way to restore individual objects within a database you'd have to look to third-party tools to be able to do that in sequel server and a lot of those third-party tools seem to have been abandoned in the past few years from what I have noticed similarly security is at the database level for many things there's lots of different permissions create table show plan your database state things like that these are all database level permissions and then there's a lot of configuration settings that exist at the database level as well right your recovery model whether or not you're using checksums to detect corruption within pages plenty of different settings our configurations that apply to the entire database now backups this is where we're vldb start to show that they're just a little different because as your database to start getting larger well your backups get larger which means they're also going to get slower it takes more time to copy more data to a backup fortunately there's a bunch of things you can do here to help most people don't realize that backups are in fact every bit as tunable as a query methods you'd use are a little different but you can see rather dramatic results by by performing different different things on backups so first of all timing let's talk about timing the time your backups run can very heavily affect their performance if you are backing a database during a time period when there's lots of read/write or lots of right activity on that database lots of changes going on if you have users updating or inserting or deleting rows we have maintenance tasks that are working perhaps you know reorganizing or rebuilding indexes whatnot you're backing up a database while these changes are going on your backups going to take significantly longer part of the backup process involves copying the transaction log and if your transaction log is seeing lots of changes you have lots more that you have to add to your backup so I found that investigating what's going on when backups run can greatly improve their timing or the amount of time they take to run for example in my last environment when I started backups we're taking over 24 hours and that was because backups were running the exact same time as system maintenance tasks changing the backup times and actually having backups run during the day when users are querying instead of at night when maintenance was running cut the time from 24 hours to about 8 hours simply by altering the time that backups were running so timing can do a lot for you without having to do much more than change a schedule after timing probably the next easiest thing you can do is investigate use of backup compression now in backup compression came out in sequel Server 2008 I know it was initially seen as something that was suspect by many but it's been proven to be extremely reliable and at this point if you're not using backup compression I sincerely hope it's only because your server does not support it you can cut the time backups take to run significantly just by applying back of compression to your native backups all right after back impression instant file initialization now this is going to help your restores more than your backups but essentially when you restore a database the first thing sequel server has to do is create all of the database files that existed previously so that we can copy the data from the backup into those files with instant file in this is an initialization or I fi I as it's often known instead of having to create a data file and then zero out that file where sequel server will literally create a file and then write over the entire files with zeros you can skip that but only for data files not for transaction log files but making sure that your server has appropriate permissions to utilize instant file initialization can greatly cut the time of your restarts multiple backup files as many of you know may know but some probably don't when you backup a database using sequel server native backup by default this backup will be written out to one file however you can write a back about to multiple files up to 64 of them in fact when you backup a database across more files the backup process will automatically assign more resources to the process so essentially you'll be able to have more memory and more CPU available for backups when you're backing up across multiple files the downside of backing up across multiple files is now you need all of those files to be able to restore so if you were to backup across six files instead of one you'd need all six files to be able to successfully restore that database however when you only backup to one file you still need all the files to successfully restore the database not much difference there and then finally once you've done all these other things there's further buttons you can push to make your backups and restores go faster and these come in the the variables known this buffer count and max transfer sets adjusting these variables can easily double or triple the speed of your backup processes and we're not going to go into depth with these because there can be an entire session of its own on backup tuning but as a brief introduction these are values you may want to look at changing to increase the speed of your backups so we've spent a bunch of time and we've tuned our backups and we've gotten them performing incredibly well relative to how they were before and then our storage administrator comes along and says you know you can do these backups and you got them to run in a quarter of their previous time and that's awesome but I can just take a snapshot right and that snapshot may only take a few seconds generally storage snapshots are instantaneous they can be very useful for a lot of things especially if you need to to present a copy of data elsewhere however when it comes down to it storage snapshots are still not a backup they're a snapshot and they're great for a lot of things but they're not a way to backup data to give a brief example of how storage snapshots work if we have this this box here which represents our database we have a very simple database so we'll just have a single data file and MDF and our transaction look LDF improver to create a snapshot of that database the storage system would then go ahead and essentially create snapshot files which contain the state of those files at a specific time or contain nothing depending on how snapshots architected and then changes will be written out later either way you end up with a copy that you can reference which is a pseudo copy of the data however it's still not a backup it resides on the primary storage system on your sand on your whatever you have and one of the cardinal rules I think of backups are it's not a backup until it's on separate storage you need to get that data off of your primary storage and onto somewhere else to another storage system ideally onto tape maybe in the cloud depending on what your organization's requirements are but once you get it off your primary storage system to where if that San if that data center blows up you can restore from somewhere else at that point it becomes a backup and this can become very apparent when you start looking at i/o errors you may encounter in your environment io errors in sequel server there's what I refer to as the big three of Io these are numbers you'll you'll want to know when you're dealing with aisle first is error 823 error 823 is an operating system-level error so sequel server says two windows hey windows fetch me this page from disk and windows will go no if windows can't get the data for whatever the needs are sequel server will retry four times if after four times windows still can't get that data for sequel server sequel server will throw up error 823 823 is a high severity error which will actually kill the entire connection or return 823 and then you'll know right away because your connection will die that's 823 824 is a little different error 824 is where sequel server says hey windows get me this page and windows says here you go and then sequel server itself determines that the data is corrupt so maybe something's wrong with disk with something in storage whatever the case is the data deliver to sequel server is corrupt much like a 23 sequel server if it determines the data is corrupted will retry to get that data four times if it still cannot succeed it will throw error a 24 and a 24 will once again killed connection so those are 823 and a 24 now then comes err 825 and error 8:25 unfortunately is not an error it's actually a warning and this is where a lot of people don't realize that so 825 what happens there is from the first step is in 823 or in a 24 error occurred through either the operating system or sequel server determine the data is unusable but on one of those four retries it actually succeeded so it failed the first or the second time and maybe it succeeded on the third at that point sequel server says ok we got the data this is if this is a successful operation however it did fail the first time or up to the first three times at which point it will issue warning 825 so 825 is written out to the sequel server error log and hopefully if you're checking the error log you'd notice that but it's not going to interrupt your F okay Schnoor kill your connection or do anything like that so one of the easiest ways to to start noticing there 825 is to create an alert in sequel server agent and you can find how to do these and you can search the internet for that and find a bunch of people giving examples and scripts of how to do that but essentially you can set up sequel server agent to say hey watch for 8:25 fire off an email to myself or the DBA team or whoever if this gets raised a 25 is often known as the the canary in the coal mine with usually when you start seeing a 25 it's it's a sign of something's going to go wrong in that terribly distant future personally on my I was seeing in one of my environments 825 errors for a period of a couple of months and we had raised this with the storage team and they kept on coming back saying oh the sands all good all the lights are green we're fine nothing to worry about and then came the weekend of my wedding anniversary and my wife and I were out of town and that's when the entire storage system decided to go down in flames so I was spent most of that weekend on the phone and on my laptop doing restore Zin stead but what ended up happening after we had the post mortem is it turns out that the sand was seeing all kinds of errors and nobody was actually looking for them and the alerting system was disabled so all the lights were still green not a good situation so watch for those 825 errors that may be the only warnings you get hopefully before you get to the point where you're seeing 825 errors though you'd notice issues with integrity checks which is our next topic so integrity checks unfortunately as your databases are getting larger much like with backups integrity checks are going to become slower as well the more data we have to check the longer it's going to take to check now one of the more common solutions to integrity checks on very large databases is well we don't need to run the integrity check on our primary machine we'll run it on an upper machine will run on a replica or maybe we'll take a backup and restore it elsewhere and run the check there these are good solutions but they're partial solutions you'll still want to check for consistency on your primary machine because you need to test the i/o subsystem your primary server if you're taking a backup and moving a backup to another machine and then running an integrity check on it that's a great way to prove the backup works but you're not really proving that everything's working in the storage system of the primary machine that you took the backup from so what I would tend to do in my environments were on my primary server I would run a check DB but I'd run it with physical only so this is only going to do physical consistency checks this is going to check and make sure all the pages in the database can be read they're valid they're not corrupt they pass checksum tests however we're not going and performing logical tests we're not making sure that there's a row in every index for each net in every non-clustered index for a clustering index there's a bunch of logical checks that also go on where you can cut those off by using with physical only that's what I would do on my primary server or as if I had a replica server somewhere else I'd probably want a full check DB where it checks absolutely everything if your databases are getting really large to the point where even the physical only checks are starting to take too long and affect users there's another option you can throw on called no index where check DB will only check clustered and heaps that will not check non-clustered indexes the idea being if you suffer corruption and a non-clustered index well you can usually fairly quickly alleviate that problem at least on a temporary basis by dropping and rebuilding that non-clustered index but you didn't actually lose any data something to keep in mind now index maintenance since we've talked about integrity checks and backups now index maintenance the third of the big three surprise index Mason next maintenance will also take longer my guides for index maintenance are before you start maintaining everything in a very large database like you might a smaller one where issues our issues are quickly resolved and checks complete quickly and rebuilds complete quickly is determine what your true threshold for fragmentation is if you have your data segregated or partition or however you do it where you have you know less frequently accessed data in a different area maybe in a different table or a different partition of a table different file group different storage however you'll probably find that that data that's less frequently accessed can definitely tolerate higher levels of fragmentation may be the expectations for running a query on ten-years-old finance data aren't the same as what happens yesterday in your environment and if that's the case then I would adjust your fragmentation thresholds accordingly I'd also work on developing a schedule for maintenance jobs but it may not need to be as regular you know in a in a production environment in a sales environment where data is changing constantly and being read constantly daily or weekly maintenance may be normal but in a larger environment where you have archive data going back many many many years you may not need to run that maintenance quite as often something to investigate and that's that's something that needs to be worked out not only between the operations people but the business people as well and then finally for larger databases instead of blindly running index maintenance jobs doing rebuilds or reorganize I would definitely have switched to jobs that will test for fragmentation before actually running those tasks and others there's all kinds of scripts out there that can do this for you they will run usually you know on a regular basis maybe maybe weekly maybe monthly go through all the tables determine the tables level of fragmentation and then decide okay these are the tables that need to be defragmented rebuilt first let's go through those indexes and then we can go work on the others later who are less it will build a priority queue and run through there and I definitely recommend this especially for larger tables you just don't want to go rebuilding a you know most terabyte index because it's the week where you think you're supposed to if not much has changed for fragmentation hasn't gone up enough may not be worth it now talk about file groups as you approach very large databases you should probably have multiple file groups already but more than one file group is definitely a great idea you can give yourself and your environment a lot of flexibility by deploying multiple file groups you can tear your data by performance when you have multiple file groups you can have file groups that are on slower disk and as I said older or less frequently accessed data can reside on a slower disk maybe be cheaper as well you have to worry about having super super expensive fast disk for data that someone might look at once a year backup and restore performance can also go up by using multiple file groups simply because when you're using multiple file groups you need to have more files you need at least one file per file group and when you're backing up and restoring a database again sequel server will assign resources to the backup and restore processes one of the criteria it uses is how many files are involved so if you have a database with more files it will by default get more resources for its backup process than a database with less partial restore ZAR also available when you're using multiple file groups and we'll talk about those in a little bit but essentially if you don't need to bring your entire database online at once partial restores will make that happen for you and they can make your your uptime recovering from a disaster be significantly lower by bringing up the data that you need right now as quickly as possible and then working on data that would not be needed as quickly at a later time and then finally you also have the option for read-only file groups and these were if they don't serve as much of a purpose as they used to so back in the day before sequel Server 2008 when data compression as we currently know it came about at sequel 2005 you could utilize instead of sequel server compression you could utilize NTFS compression and actually have the compression occur on the file system this was a big pain but it worked but one of the requirements was the data could not change so you actually had to have your data in a read-only file group so you basically put the data you wanted in a file group that was read-only you'd then you deploy apply NTFS compression at the file system level to compress the data and sequel server would handle it from there probably not as useful today as it used to be but the option still remains when you have multiple file groups one of the issues you'll run into is you'll probably need to enforce correct file group usage so if you had a database that you know would by default just have a primary file group and now you add another one or many file groups you'll want to make sure people are putting or creating objects in the correct file group unfortunately sequel server has no security settings behind file groups there's no way to say you know these users cannot create objects in this file group so if you have users creating objects you may need to make sure they're creating data they're creating their objects in the correct file group and there's a couple different ways you can do this I've seen creative usage of triggers I've seen event notifications used to do this I've seen resizing if I'll grow up making it really small maybe just like 50 megabytes and the idea being yes someone can create a table in there but as soon as they start copying any decent amount of data in there they're gonna run the file group out of space and then they'll start asking questions but you'll need to find a way to make sure the correct file groups are being used because just because you make a file group the default file group yes by default all new objects will go to that file group however if someone's copying pasting a script and it says you know create object on primary then it's gonna go in the primary file group and there's nothing you can do about it similarly when you start reacting your file groups this is of course much easier when you're creating a new system but if you have an existing system you may have the problem of needing to move data between file groups this can vary in difficulty depending on the contents of your database so for example if you have clustered or non-clustered indexes or heaps that you're dealing with these are pretty easy to move you can rebuild a cluster door a non-clustered index and a new foul group you can do that online and people may or may not be affected by that heaps are a little more work simply because you cannot rebuild a heap you'd have to take and build a clustered index on a new file group and then drop the clustering index later and you'd have a heap again and a new location those are rather simple where things start to get a little more complicated as when you're dealing with with lob data with large object data at this point this is not easy there are ways I've seen to move large object data between file groups with the database online if you're going to take it down time this is much simpler you can just copy the data over copy the files over to a new location but if you're going to keep the database online then you're probably gonna have to do something like create a new table on a new file group copy the data to that new table copy all your indexes permissions triggers and everything else you had to those new tables and then place huapi with the names switch the names around and then drop the old table and while the whole time you're doing all this stuff you got to make sure that nobody else is updating anything this this can get to be a problem really quick so this is something you'd want to plan out well in advance that's file groups moving up the chain once more to storage and talking about actual Drive letters and mount points there is a common belief out there that more drives can mean better performance not necessarily adding drives and adding storage to systems especially enterprise quality storage not cheap and there's usually simple ways to fix most performance issues so I would definitely recommend investigating other problems before you go and say we need to solve this problem by adding storage now of course if you're if you're running out of storage and and that's that's the main issue is you don't have enough space for it anymore then you have to add storage but if you're saying our performance is bad we're going to fix this by adding some more disks you can probably achieve a more dramatic performance increase by using other tuning methods first would be my my advice on that higher availability and I like the term higher availability as opposed to high availability because you can still have failures in a high availability system just hopefully you've increased you've decreased the chances of that occurring as I just got done saying disk is not cheap and as you have larger and larger databases with more and more disk it's definitely getting more and more expensive as well now if you're using high available solutions such as mirroring or replicas or log shipping these are great these are wonderful but they all duplicate the data right and a mirroring setup you have a principal and a mirror these machines both have their own separate discs you have two copies of your data essentially whereas if you're looking at something a little more old-school like a failover cluster instance now you're dealing with is your disk you don't need to buy multiple copies of the disk at least not in your primary environment hopefully you have a disaster recovery environment somewhere else which will require more discs but you won't need one for a note like you would for for mirroring or log shipping or with replicas the downside with with only having one copy and your primary environment is of course not you you have a single point of failure with your storage however hopefully your storage system is robust enough and your backup protocols I'll robust enough to be able to survive issues should they arise talking about restore x' and we touched on this briefly before I said partial restores can definitely be your friend and I'll actually correct myself a lot of people will say partial restore officially according to books online the term it's a piecemeal restore I've been corrected on that one before but the question you need to ask yourself and your business users are do you need the entire database restored right away as I said if you're using multiple file groups you can do a peaceful funeral we used a piecemeal restore and only restore the primary file group and other data the other file groups as needed so you can bring you can bring this year's accounting data online right away and have that up in 20 minutes and then we can work on last years and that'll take another half hour but we have you know some we have two years of data up and an hour at which point the other 20 years well those can wait till tomorrow or tomorrow night or next week depending on what the situation is so peace Maori stores allow you this flexibility of bringing some of the data up right away worrying about the rest of it later when we have more time and more bandwidth and people are back at work after spending all night fixing whatever issue created this problem in the first place partitioning so is your objects are getting larger in your database is partitioning may start to sound like a better and better idea table partitioning of course is where you have a partition key you have a a column that you are splitting the values in your table on and then you are depending on that value writing the data to different partitions within that table and the real performance increase for partitioning is twofold when you are querying your data if your query involves a field for that partition key you can now know instead of having to scan an entire table I only need to scan a particular partition to find all the values that qualify that can dramatically cut your query times but also for loading and removing data partitions allow you to do what's called partition swapping where you can swap a partition in or out of a table and effectively move that data around however you don't have to move the data to do it all you're literally doing is swapping partition IDs so to give a very brief example of this if this is our partition table here and we have we have four partitions in our table as you can see we create two more tables with identical column and index structures and we load the data we want to load if we want we want to load more data into table a we're going to start doing that by actually loading the rows using a bulk load process or whatever into table C and then we're going to swap that partition from table C into table a so I'm going to run this animation now and this is my first attempt at animations in PowerPoint but you'll see here we'll have partition 1 from table a as being removed it's being moved to table and then the partition that we just loaded from table C is swapped out into table a these are very quick operations at this point when we're loading the data we're doing it in a different table so we wouldn't account a wouldn't be affecting users trying to query in the table at that point and you can you can swap a partition in and out very quickly that's an advantage of using sequel server partitioning now there's also another option to partitioning that can be rather attractive depending on your environment and depending on also what edition of sequel server you have now I should mention that it recently in the past few weeks was announced that sequel Server 2016 Service Pack 1 had many new features that were added into the standard edition which were previously only available Enterprise Edition table partitioning was one of those features so if you're using 2016 Service Pack 1 you know have you now have access to partitioning or as previously that was an Enterprise Edition only feature what I used to tell people for who are using Standard Edition and needed the the query advantages of partitioning but didn't have the money to purchase Enterprise Edition was you can use what's called a partition view instead and this is still still a perfectly valid method even in the enterprise edition environment essentially what you do is instead of partition creating partitions within a table you actually go and you create multiple tables and these tables are divided once again on a on a particular value on your partition key essentially and when you create these tables you create check constraints to enforce the values in that table fall within whatever your partition range is at that point you create a view and you simply Union all all the tables into the view so you have a you know create a view select table 1 Union all select able to etcetera at which point when users query the view the query optimizer will be smart enough to read the constraints on those tables and realize that if you're searching for a particular value and it's divided by that partition key then well I don't need to scan every table that makes up this view my only need to scan particular tables so you could do this and like I said you can do this of sequel server and you will get the query advantages of using partitioning you will not get the data load advantages that partition swapping brings in but you'd give the querying advantages at least in this this works in standard edition so I definitely recommend taking a look at that if you have a very large database that is not in Enterprise Edition or is not sequel server 2016 Service Pack 1 standard edition so to sum up partitioning and say the partitioning is great for loading data like I said that partition swapping can be extremely quick that's a metadata operation so it's going to take you know fractions of a second to complete but if you don't have the ability to use table partitioning partition views definitely something you'd want to test beforehand but you could see great results from that and partitioning ik said is really only good if you're always filtering on your partition key if you have data that does if you have to run a query that does not know the value of the partition key partitioning is not going to work to your advantage in fact it may it may cost you more performance because when you have to merge results together from scanning different partitions that can actually take more time than just scanning a single partition by itself so you definitely want to make sure that the majority of your queries or at least the queries that you really care about performing well are filtering on that partition key I used to work with medical data and we had some large tables with billions and billions of rows and we actually did not apply partitioning to these tables simply because there was no real good way to partition them we could we could partition by a date by the date an event occurred however most of our queries most of our users who were looking for for data in a research setting wanted weren't trying to search across a specific date range they wanted to know you know tell me tell me every person who came in with a fractured arm since the beginning of time well that's going to take some some time you're gonna have to scan the entire database or the entire table to find that that information there's no there's no date data associated with that so we can't filter on a partition there were lots of instances like that where partitioning just didn't work so you really need to make sure that the type of queries and that your environment would benefit from this before rolling out something like partitioning next let's talk about data compression now sequel server has had data compression since 2008 and you can get some great advantages in performance and and save yourself some disk space with data compression as well so data compression occurs and I'll have my very simple graph of a database system here so we have end-users connecting to the database and writing a query there is access methods which are determining what needs to be read what what what pages of the database actually need to be read from memory and if they're not already a memory we need to bring them in from disk now when you go ahead and you apply data compression data compression occurs in sequel server within the access methods so what that means is when we apply compression essentially the the data is compressed both in memory and on disk this is this is good for several reasons number one you're saving disk space but you can also fit more data in memory now in the same amount of memory because it's compressed and it's taking up less space of memory there's a lot of storage vendors out there that have various storage systems and appliances that that will do compression and those very often can do better compression than sequel server can you can achieve more savings on disks but the one thing they can't do is they can't compress the data in memory on sequel server so this is something you definitely want to try out on your own if you're testing a new appliance hopefully you get an on trial basis or something first see what the difference is because yes even if you have an incredibly fast flash storage array the one thing that's faster than reading from an incredibly fast storage array is not having to read from that storage array because your data is already in memory and when you're using sequel server compression your data is already compressed when it's sitting in memory one of the downsides of using data compression and sequel server is that data is never cashed in a decompressed state we need to decompress the data before returning it to our user that's why the user is on the decompressed side of the line but there's no caching of decompressed data and that occurs so essentially every time a user reads a row even never reads the same row a million times over you'd have to then decompress that data a million times over data compression as I just said compresses data in both disk and on memory there's two flavors of data compression and sequel server there's row and there's page compression they row is a simpler faster form of compression that works in a lot of cases in some cases page compression can get you even more savings depends on the the degree of which values are repeated in your database and within a page that's something you test out when you're applying compression moving on to statistics statistics are of course very important in a database sequel server will use values from statistics to determine a query plan before the query even starts updating so you'd like to generally on a small database especially you like to keep your statistics as up-to-date as possible however as your tables start getting larger in sequel server even if you have auto updating statistics enabled those updates will become less and less frequent and that's simply because of the formula sequel server is using to determine when to update statistics sequel server keeps track of how many rows are in each object in each index and then it will keep track of how many updates have occurred since the last statistics rebuild now the the formula that it will use and used to use by default was 20% of the rows plus 500 rows had to change to trigger an automatic statistics rebuild so if you have a table with a thousand rows well there'd be 20% of a thousand or 200 plus 500 so once you had 700 out of those thousand rows change then you'd have an automatic statistics update occur that may work fine for smaller tables but once you start getting up to tables with billions and billions of rows you start needing millions and millions of updates to trigger an automatic statistics update and oftentimes you won't see that much churn in tables at large so you can either run statistics manually more frequently or there's a trace flag you can apply and it's race flag 2371 that will change the threshold for Auto statistics recalculation so I will show you on the next slide a chart of exactly how that changes but another question to ask is our running statistics more often really improving your performance because if you start having really really really large tables where the data doesn't change very often you may not need to run statistics as often to begin with but again this varies heavily by environment so when you enable trace flag 2371 or when you're using sequel Server 2016 because this is the new default behavior in 2016 instead of the default threshold of 20% of the changes which is represented by the blue line there you'll see that the threshold now becomes dynamic and it now follows that red line instead so as your table starts getting larger and larger the percentage of rows that need to change become smaller and smaller this will cause more frequent auto updates of statistics but that may improve your performance for queries by having statistics be refreshed automatically more frequently as opposed to doing it manually yourself all depends what you're looking for in your environment another thing to think about is parallelism are you getting all of the CPU that you've paid for you've bought a box with 32 or 64 cores and you've paid a bunch of money for sequel server licensing for all of those cores and then you go ahead and you deploy a queries with skilar UDF's which just utterly decimated lism it's not even possible there's a bunch of other sequel server commands you can use that can affect the sequel server's ability to parallelize queries and you can actually see an excellent blog post by Paul White on those topics and those lists of functions and what they do so I would definitely recommend checking those out but make sure that the queries you're running can as often as possible take advantage of all that CPU you've paid for to purchase and to license ok we're getting towards the towards the end here so we'll talk about security real quick your databases are getting larger and you have more and more objects in your very large databases I definitely want to look into using security roles if you aren't already simply because you can you can package up a bunch of permissions in a role and then add users or logins to those roles this makes life a lot easier when someone needs needs you know access when you have someone new join the team or whatever and they need to need security provision for them rather than having to go out and assign all kinds of ridiculous permissions you can add them to a role and be done definitely can be cured life easier you'll also find that as you have more and more data and perhaps more and more sensitive data in your systems you may need to keep track of what your users are looking at more frequently so what I'd like to tell the story I'd like to tell with this is actually the story of this building so this is the last remaining part of a building it's actually just outside of Chicago Western Electric used to have a massive factory west of Chicago in a town called Cicero and it was called the Hawthorne works was the name of the factory and Western Electric made all kinds of things they made telephones for the Bell System for a long time they made appliances they had they employed thousands of people in their heyday and it was seen as a great place to work and because there were so many workers doing so many different tasks it was also seen as a great place to actually do academic research on the behaviors of workers so there was a lot of formal academic studies Commission on how workers work how can we get people to work better and more efficiently all kinds of things like that and one of the effects that was proven scientifically at the Hawthorne works was actually named for it it's called the Hawthorne effect and you may not have ever heard of the Hawthorne effect but when I tell you what they prove with the Hawthorne effect I'm guessing you will realize that it's it's pretty common knowledge but of course at some point someone had to scientifically prove it so the Hawthorne effect states that people generally tend to behave differently when they know that they're being watched okay they did a bunch of work and proved that you know when workers know they're being watched they tend to work better and harder faster or as if they don't think anybody's watching they may not care as much same can be said essentially for security so if you have a database with all kinds of active security with with things denied P may not you know try to do anything but if you have a more passively secured system maybe you have users in your environment you can't deny access to maybe maybe the big boss wants system administrator access in your database and you really don't trust them but you're not in a position to say no this is where passive security you can come in and really help there's a couple methods you can use to passively secure your databases and this works for databases of any size but if you're not familiar with sequel server event notifications this is essentially you can set up a notification and we notified as soon as someone does something that they shouldn't maybe selects from a table or creates a table in the wrong file group things like that you can actually go ahead and create an event notification and have have something happen generally you know an email or other other notification event go on when something occurs the second thing you can do is of course auditing depending on your environment you may already be required to do auditing if you deal with with financial data healthcare data personally identifiable information like you may need to know exactly what users are seeing in all times hopefully you have an audit system set up to capture that information for you something else to think about when you're thinking about security you're putting all this effort into auditing your database and what people are looking at in the database what about the actual Active Directory groups that people are in if you have an Active Directory group of system administrators and you're watching watching who's you know in the system administrator list that's great but if someone goes and add someone to the Active Directory group that be transparent to your database you would know that Active Directory notifications are something you may have to work outside your team with if you're not an Active Directory administrator yourself you may need to go consult with your ad team but I found more often than not your ad team can do things for you like set up notifications at the ad level so as soon as membership in a role or group changes an ad group that is they can actually send out an email to you know a person or a group and say hey someone in this super administrator group just got added to it is this what we want definitely a good good sanity check there and then finally the last thing I'll talk about with very large databases is read-only mode concurrency is the cause of many issues in a database when you have users reading and writing data at the same time and if you know for a fact that this data is not changing and will never be changing well then read-only mode may be for you you can place a database in read-only mode and essentially have no more concurrency issues because the only activity people can do is read since the database is in read-only at this point statistics can actually be stored in database so they go into temp data me tempting me at that point for statistics and the other the downside really of read-only mode is it's very disruptive to switch between read-only and readwrite loan in a database you would need to put the database in single user mode switch it to read on the mode and then bring it back in the multi user remote so that people can read from it but it can be done so with that that is all I have four very large databases today happy to take questions I see some have queued up here and can take those and then if you want to take questions offline you can also feel free to reach me via email I'm also on Twitter and I do have a resource page up on my blog you can check out with links to some of the things I cited so that's what I got okay Bob I'll start from the top this respect when you were talking about snapshots user asked we need do we need to the storage to freeze a checkpoint in order to create a working snapshot does the storage need to freeze an i/o checkpoint to create a working snapshot that that depends on your storage system I've seen storage systems where that is necessary and I have seen storage systems where that doesn't matter they've created other workaround so that that unfortunately is going to depend on your environment more often than not I've seen storage systems that do need at i/o freeze for that but it's usually on the order of seconds ok this user wants to know about some of the challenges associated with of always own availability groups because we're trying to deploy a 1.2 terabyte database we have mb/s present on two drives in F or disk i/o is different in reads and writes we're facing low query execution do you see that question it's actually was they put three together do you understand what they're trying to ask I will I see them here I'm going to say that's probably better suited to take offline so maybe we can address that via email instead of right now if that's all right with them that's that's probably all right then they'll get their answer the next question is is they just want some general ideas and I think you've given those general ideas of how a 24-7 shop would go through that's what the entire presentation was about I'm assuming at the resources you're gonna have this stuff listed out and plus they can look at the archive and see that again but do you have anything to add add to that part of the biggest challenge of a 24/7 environment is you've got hopefully you have a time in your 24 hours a day where there is an activity law or you can where you can work on things I've seen environments where there is no law and then they they start using other techniques like maybe we'll divide our users into different groups because you know maybe it's a 24/7 global shop but there is still hours a day when you know everyone in the United States generally is in bed and an activity is higher in other regions of the world in which case they'll actually split up their environment by by geographic location and perform maintenance that affects users when when that group of users is most likely to be not utilizing the system there's a bunch of different ways to do it those are some of the general ones I've seen as well as well as just trying you know most of the techniques I think I addressed in the presentation were methods to do this with with minimizing downtime or possible okay there's the next user is talking about database scoped configuration with 2016 let's see you see it's that real big question okay so okay hang on I'm looking at it yet so I don't know I actually have not either so I am NOT going to be able to answer that question at this time unfortunately okay is there any added value to move of a very large database own to it um if if you placed higher value in in paying more to Microsoft to have it there and then having it in your own environment then perhaps that's probably a case more for your business people there's also the ability you know there's as you're stretched GB where you could put part of your database and Azure and have part of it on Prem but now you're paying all kinds of money to have that data up there as well so that's kind of got to determine where you want your dollars to go and for what reason if you feel that Edgar is bringing you more advantages by putting it there well then it may be worth it for your environment but I'm guessing others would probably say maybe it's not worth it so it can't get pretty expensive I'm thinking that archive data would might be the own and you utilizing it with stretc databases might be the only way that it would be cost feasible for a lot of people now I mean that's gonna probably change in the future yeah I would like to think so we're also facing the option of unable to rebuild an index online in standard edition with database yeah you're right I don't I don't believe that was added to the newest out of 2016 I don't think it was either because that's one of the big things that people want with enterprise oh yeah yeah I think that's one of the things Microsoft kept with enterprise so this this is one of those situations where unfortunately you have you have constraints put on you in standard edition and you got to abide by those constraints and occasionally a lot of the a lot of the features that Microsoft kept in Enterprise Edition that it did not put in standard edition involve greater uptime so if you need more uptime well Enterprise Edition may be for you or you may have to take a scheduled downtime to perform maintenance tasks like this yeah they're not gonna give away everything so they're gonna keep some things so people I think is it as it stands its availability groups and and things like this that they care ok how do you define foreign key constraints on partition tables partitioning gives me a headache regarding unique constraints as they're evaluated just at the petition level as far as I call what do you do to ensure data consistency between partitions boy I've never run into that I have I have not run into this so I will have to address those questions offline okay that makes sense how quick is a partition swap data integrity is preserved correct correct partition swaps are metadata operations so they're there as quick as swapping you know one integer for another essentially they're they can be under a second easily data integrity is preserved during that process so that that's that's part of the allure of partition swapping is the fact that you can do this atomically okay what are the disadvantages for purging and removing data probably the biggest disadvantage for purging and removing your data is you don't have that data anymore it's one part of the Big Data movement is is clearly that you know we have more data we've saved we were able to save due to cheaper storage and whatnot and that we can do more analysis over greater periods of time so we have more data so yeah you know I've worked in environments where purging data has been encouraged to save space and more more and more often these days I find that the general attitude is let's not let's not delete anything we don't have to let's find a way to save it somewhere even if it's in on in an offline environment where we can't access it quickly we still have it for later when storage becomes cheaper or some new technology come out and we'll have this data we can we can analyze it later point okay the next question is if your own flash drives I understand it's better to keep stats up-to-date rather than run re-indexing is that correct um I'm gonna go again with it depends definitely statistics up to date are always going to help you I think there's still a lot of people out there that are saying you know re-indexing ria next thing in a flash drive is kind of kind of an interesting proposition because most things when you have data on a flash storage it's generally always going to be fragmented there's that that's going to be part of it so if you're if you're using a flash drive to prevent fragmentation that that's not that used for at all what makes flashdrive so appealing for one of many things at least is the fact that you know the searches are so fast through it that even when your data is highly fragmented you're not going to see performance hit from that your flash drive can can handle highly fragmented data incredibly easily that's not an issue for it at all however re-indexing can cause issues with sequel server simply because you'll have even though the fragmentation doesn't matter to your flash drive it will matter to sequel server you'll have pages with very low you know if you had a data page on a highly fragmented table maybe only maybe your data pages are only 10% full well your flash drive doesn't see a problem with this but now you're wasting all kinds of space storing empty data and pages because your your data is fragmented and every indexing would help that part so you can you can get away with running indexing less frequently RIA mixing less frequently in sequel server on a flash drive so long as you don't mind the fact that you're probably wasting space at which point you probably want to run the reindex in anyway a lot of people will make the argument that you know you'll you know where your flash drives out you'll you'll issue too many write commands and it'll go bad and this was definitely more true 5 10 years ago than it is today but most of the enterprise flash I've seen today is incredibly robust it can handle years and years of getting pounded with writes so I'm not sure that's as valid of an argument at least that facet of it as it used to be this might be an answer for the person earlier that had the 900 gigabyte database on standard that had virtually that have to try to balance the cost of an enterprise license against the cost of the flash drives but those are coming down all everyday so the next question is when you trunk had a table will the stats also drop I believe they will I don't remember off the top of my head for sure but I'm pretty sure they will because they'll be will be invalid at that point anyway ok this is sort of the same question as earlier how can see quash or be leveraged in the management of very large databases depends on your business case in cost I love this money path on fan's I'm gonna read anyway what's the airspeed of an unladen swallow oh boy well Greg that depends if it's an African or yes well nobody's ever asked that I love it though but anyway thanks a lot great presentation one person earlier since we've got a minute wanted to know if your wife was alright because they had web read the the follow up on the cat bite story so yes yes she's fine she has full use of her finger these days I got a little scar there cuz they had to do surgery but she's good otherwise and the next most common question we get from that is do we still have the cat and the answer is absolutely he's he's standing us at my office door looking at me right now actually he wanted to see how you answered that ok well we really appreciate it this was a great presentation we'll want to we'll have to have you back some time I really like the way you did it next week or next month we'll wait so get some more okay there's some more questions here all of a sudden okay got a few minutes sorry I didn't I didn't roll up the screen cannon data by snapshot dude I don't understand that one snapshot dude database become more so does a database snapshot cause the database to performs more slowly does the database snapshot so if you're using a database snapshot that's a snapshot within the sequel server does it cause the database to perform more slowly the appropriate answer is yes but the real answer is it may not be that apparent it will be it will be slower by virtue of your doing more but it probably won't be a parent to your users when you're when you have a database snapshot active on a database essentially every update you issue on a database whether it's an insert an update or a delete will actually go ahead and copy pages from the primary data files into the snapshot data file so you have data moving every time you do that but that only occurs the first time you do that so if you if you update a row the original version of that row will get written into the snapshot file but then any subsequent updates of that row it won't occur but you're you're essentially keeping track of more and you're doing more so you will see there will be a performance impact but again it may not be significant to the point where people would actually notice definitely something you'd want to test with before just going ahead and getting crazy deploying database snapshots now the snapshots I've talked about where storage level snapshots so that's that's on the storage system as opposed to within sequel server but essentially your storage system is going to be doing the same thing just behind the scenes that sequel server would be doing or an equivalent operation at least okay this question is it safe to add MDF based on VLF count or the size of MDF I don't quite understand that do you see that one down towards the bottom all right hang and I'm looking here I'm scrolling through about eight from the bottom from the bottom numbered these questions is it safe to add MDF based on VLF count or size of MDF well usually when we're talking about VLS we're talking about the transaction log and I'm not sure how adding MDF files or data files would affect the number of VLF so then you know a little right into the transaction like there's no file present but I don't think it would increase the number of VLS it's not adding the transaction log at that point I'm I'm not unfortunately I don't understand that question enough to be able to answer it for them okay the rest of the questions have to do with partitioning and this is not necessarily petition if you want to talk a little bit about partitioning that would be good but I think you've been doing that all along some of these other questions have already been answered so I think what I'm going to do is I'm going to send the rest of these questions to you and you can follow up in a blog we always offer that and speakers usually love that because they get new blogging materials they don't finish questions but we're gonna have to close it down right now all right I really appreciate it will will discuss about getting you back sometime in 2017 and thank you and we'll see you next month with the windows fell over clustering session the first of the year thank you and have Merry Christmas and Happy Holidays thank you thank you very much for having me appreciate it that was very good I like that we're gonna we're gonna definitely have to use some of those things coming up especially partitioning

Channel: DBAFundamentals

Views: 5,645

Rating: 4.6296296 out of 5

Keywords: sql, very large databases, dba fundamentals, how to manage large databases, bob pusateri, dba, microsoft

Id: cN30SRgPkTk

Length: 70min 25sec (4225 seconds)

Published: Wed Dec 14 2016