This session's all about getting your database off to a right start and what I mean by that is what you should do up front, so that when your database needs to scale and perform later on down the road. It's ready for that. You don't want to do those things later that are necessary to make sure your database.
Are those things were going to go through it? But when it comes to scaling that's important my name? Is Andrew Kelly. I'm excuse me data platform MVP been one for quite awhile. I specialize in performance tuning scalability that I kind of got into that by fire, I worked for uh.
Years ago that we had at the time was considered a very large database and he needed to scale and performed so I had to kind of jump in and learn all this stuff on the fly and today I deal with large databases well. Large relative OK large to some people is one thing I have for instance, I have a table.
16 terabytes of compressed data for a single table tables with 20,000,000,000 rows lot of people think that's large some people think it's not so large, but I'll tell you without a doubt, you won't scale to those levels and get the performance that you need or want if you don't follow some of these best practices and.
Hand because that's what I do every single day and I've seen it. I've seen people try to try to do this and not not get there, so we're going to start out with consistency. Actually, I just realized there's a thing in there, I pulled out recovery intervals. When I found out this was 50 minutes, I had.
Database file in file groups are actually going to talk quite a bit about that because that's 1 thing that a lot of people are confused about we're going to talk about partitioning, but not I'm not going to make you guys experts in partitioning. I'm going to tell you why partitioning is important for this schemas and then we're going to take a look at some of the database configurations on it.
And then hopefully they'll be time for questions and answers but we'll see OK so consistency. Consistency is definitely the key with this because most of the time if you were going to scale you're probably going to have a lot of databases as well, and even if you're not getting to the you know 10 terabyte.
You if you are going to manage lots of databases. You have to be consistent with what you're doing otherwise. You're going to have a hard time managing that as you grow used everyone starts out with one database or a few databases and then they go on things like when they design their all.
Very small concepts a lot of people do their dev and test with 100 rose in their tables OK. That's great. But it's not going to scale and if you don't think about that up front consistency is the same way things like object naming.
Object placement we're going to talk a little bit about object placement as well. Configuration certainly on that, etc etc. But the point is that you got to come up with these concepts upfront decide what these are and apply them right from the beginning, you can for instance, decide after your database has 10.
That you're going to change some things about it. Changing the table name. That's pretty easy. I mean right. It's a metadata operation. But how many stored procedures? How many views. How many lines of code have that in it or ready, so if you decide down the road that you're now all over going to all of a sudden going to come up with a set of best practices.
And start applying them You're going to have a lot of work ahead of you then you would have had at the beginning problem is, is that most people won't take that time at the beginning, I mean, a big thing. These days is agile development and things like that. Well, a lot of people. Forget about this side of it. They just think we're going to go in and start building these databases and will.
Will go on from there? Well, when it comes to scaling the databases and managing large amounts of data base. You can't do that. You have to put some thought in upfront things like like I said, like naming conventions and I'll show you when we get and look at one of the databases. I'll show you like for instance, the file names and such in one of the Main.
Horton.
Now you can utilize the model DB for this uh the model database is actually way underutilized if you put something in the model database. Then, when you create a new database that inherits whatever you put in there, so if you have. Let's just say you have a stored procedure that you need to manage all of your databases. If you put it in the model date.
Meet the new database, it will be there, I mean, there's a lot of other things. On that, but properties settings. You know things like that. That's that's 1 thing that can help you with that policy based management. Now I say this but policy based management hasn't gotten any love recently you probably know that I mean, it's a great concept.
Only in a concept, it's there and it works, and it works very well. But it's underutilized people don't utilized at now? What is policy based management. It's essentially a way to let you manage your instances or your objects within SQL server and you can control certain things like naming conventions.
Can or cannot do something so if if you say that my databases? Are all going to be named with ABC underscore and then some number or some set of characters, you can create a policy that says don't allow that so it will actually rollback that creation in that database. If somebody tries to create one.
More likely you're just going to do a notify so when it gets created. It let some created but then it sends you an email or whatever and says. Hey somebody violated this thing, so policy based management can go a long way towards making sure your environments. Consistent is how many people don't have more than one person working in their environment and those people that have more.
But how many of you are actually all on the same page. All the time specially if you're a larger company and scattered across division policy based management will help you manage that a lot better.
A data types this, I laugh at that because this is something that I find people pay the least attention, too. And I think you should be paying the most attention to it. When you're talking about performance and scalability. You need to, you need to make sure you're using the correct data type.
But what do I mean for a given column you need to look at things like size accuracy think about what type of data? Are you going to put in there and then what are you going to do with that data. I can't tell you being a consultant I can't tell you? How many times, I've gone into a client and.
Databases are created with varchar 8000 or varchar, Max on all columns or everything's a big into are you know, whatever the case. I'm sure you've all seen it and and for small databases. That might be fine. You might get away with that. But we're not talking about what you're starting with, we're talking about what you're ending with if you go.
The base and you have a data type that's the wrong data type and you've now loaded 10,000,000,000 rows into that and you need to change the data type that's not an easy thing to do it say my team being impossible thing to do. I have a database that was created before I got on this project and that that database now is?
16 terabytes of compressed data in that and they didn't have an index partition aligned index that they should have and will talk about that in a little bit. So now that table is so big that I actually can't do what I want to do with it in production like that, so we're going to move that tay.
Column store compression when it when we move it over we're going to fix that, but that's that's a perfect example of why if you don't do something. The the right way, at the beginning. You're going to be a load of trouble down the road because yeah, maybe you can fix it. Maybe you can take your production system down for 12 hours while you rebuild the indexes.
But probably not, and in my case, it is just so big. We don't have the capacity to do it on that server in that fashion in that I'm on there.
So one of the things about the data types is size does matter. Now you'll get arguments for this all the time and people will say you know if you need an int just make it a big ints and just just always use that or if you don't know what the character size is just make it something don't worry about it, but it does match.
On disk.
Because of the space is going to take up on disk. It matters in memory, which is actually in. I think even more important because when you bring that data when you read that data you have to bring it from disk into the buffer pool into the SQL server buffer pool. It's taking up memory if that table or that column is large.
You're taking up more memory you can fit less data into their so what's that mean? What is that going to mean for you, you're going to do more physical. IO then you should had you design that properly now again. People say, these days. It doesn't matter memories. Cheap disk is cheap things like that. But just think about it if I used in big integer column and I.
Is an ant or even a small into small N is? What 2 bites ebig ENT? Is 8 bytes multiply that 6 bytes times 10,000,000,000 for the number of rows and if there were multiple columns multiplied by the number of columns. That's a lot of data that is a lot of extra data that you're storing on disk.
You're putting into memory, but you're also what else does that does it do when when you've got that extra data on disk and now you get it in a memory? What's another problem with that.
It's well, yes, it is wasted space as well, but you to get that data from disk into memory. You've gotta do. IO operations you're now doing in this case. You've got what should have been 2 bites. It's now 8. Bytes so that's 4 times the size so you're doing 4 times as much I owe.
Physical IO to get the data from disk into memory and network is another thing you're going to pull that data if you're going to read data and you're going to send it somewhere that data is now going across the network and that's more data across the network it all adds up and when you're talking about the large amounts of data that we see today, it is.
Have to be considerate of right off the bat because you again. You can't go back and change those things that easily. Yes, you can make some changes. But if you just spend a few few minutes up front. You would you could avoid that whole aspect of it now one thing I just well before I before I talk on that other thing.
You know, I might get some arguments on this for some of the people here, but I hate user defined data types. I could define a user defined data type and say this is my big integer or my integer and you define it as an int and then you go put it in 100 tables or in 200 columns and you use it that's great because it's consistent so now every.
So, just be be very careful if you're going to use user defined data types, you will ultimately run into problems scaling with that.
This is slide. I just added will talk about it because its role of into the data size. I just said. If I have a small end versus a big in you know that 6 bytes difference. But if you're using one of those types of compression that might minimize that issue so a lot of people say, well, I don't need.
Worry about that, so if I'm using either row or page. Compression that big integer will be well. If I have a big end 8 bytes, but I'm only storing the number one in it. It doesn't need a bytes to store that so the compression will actually store it as only take one bite to store.
So it's on disk is one bite. It comes across it's only one bite and it goes in a memory is one bite. But when you read that data and you're pulling it back out and you're going to send it to the client. It gets expanded again and it's now 8, bytes again on there, so you have to be very careful. If you if you take that and you dump in attemped? What happens when you dump.
See you got a temporary table or whatever it expands it when it goes in attempts are using more tempdb space so don't let compression don't let people give you the argument that we're using compression, so we don't have to worry about that. It's still relevant use the right data type for the for the task and then there's CCI columns.
Column store has excellent compression and if you're talking about these large tables. Hopefully you're using columnstore compression. So it will compress it down, but again that is while it's on discord in memory. Once you read it out and it's going to Tempdb. It's going to your client. It's going across the network. It's
Actual size so keep that in mind don't let the compression fool you on that.
Any questions on that.
Alright instant file initialisation. I talk about this because it is very important, it's.
It used to be very important because it was something you had to go in and set yourself. And if you didn't have the right installation routine or the right setup policies. Then a lot of times. This got missed and the instant file initialisation. Just means for your data files when they grow or when you initially create them.
And have to go and set each bit back to zero and that's what the instant filing or that's what the initial ization is the instant file initialisation feature defers that until you're actually going to go and use that block of data and then it will initialize it at that point but it can make a huge difference in your for instance.
Your files and things like that, it does not work for the log file. Unfortunately, but uh we're going to talk a little bit about the log file and some things you can get around that, but as of I think it's 2016 the installation. Wizard will set it for you in most cases prior to that you had to make sure you.
Yourself so depending on what version you're on on there.
I'm sorry.
You have to check a box in the initial I think it's checked automatically, though right oh. It is unchecked by default, OK, so they're telling me that instant file. Initial is unchecked by default. It's there. You gotta remember to check it. So that's that's a good point on there. We do a lot of our installations unattended so it's a config.
Action script so it's set in the script. But if you're doing, it manually. You gotta make sure that you check that box and you'll be. You won't like it if you don't alright so files and Filegroups. This is something that I find a lot of people are confused about on you know what they are what you really should be used.
By etc so we're going to spend a little bit of time on that.
Excuse me So what is a filegroup a filegroup is nothing more than a logical container there's nothing physical about a fire group. It really is just within SQL server. It's a logical container that says, I have this one or more data files contained within this logical file group. It's a way for me to manage.
Usually more than one file but not necessarily could be one file but it's a way for me to manage that one or more files kind of as one and do certain things with it. The Filegroups cannot spend databases. They're all contained within it within a given database and a single file that you put in a file.
Cannot span filegroups OK, it has to live in that one filegroup on there. That's just that's just the way that they work.
SQL server by default when you create a database if you don't do anything other than say create database. It will create 2 files. It will create a primary data file with the extension dot MDF and then it will create a log file with the extension dot. LDF and again. That's how most people create their databases. They create the database just say.
And then they go from there well. You don't want to do that. The primary filegroup and that file that single MDF file holds all of the system objects now? What do I mean by system objects things like all the metadata to manage your objects within within that database but also your OBGYN.
It's really not just system. It's user objects as well. Like stored procedures views. UDF things like that when you create that they live in that primary filegroup and by default. There's only one file in there and that's fine to have one file in your primary file group.
And just hold the system objects or the user metadata things on there, you should not put user table user objects in there, you should not put data in there and will talk will talk about that in the next slide.
When you do create a file group and add files more than one file always ensure that the files are the same size. Not only the same size but the same auto growth settings. Anybody want tell me why that's important.
There's a card steak.
No.
Now, maybe it rain. I don't know what's what's valuable to Britons here a bicycle go ahead darling.
Yes, so, So what Earl instead is that when you have multiple data files in a file group. They the object. It would let's say it's a table customer stable when you start writing data that customers table. It's going to spread it across all the files that are in that filegroup so if I have 4 files. It's going to spread it across them? Well, why.
Is it spread it it uses? What's called a an algorithm that is based on the amount of free space in each file so it'll look at all 4 files before it starts loading any data and says how much free space is in each and then it says OK if the amount of free space was the same in all 4 it will put one quarter of the day.
Record of the data and then access so on, and so forth. Now it may not be exactly one quarter but it tries to spread the data as evenly as possible across that if you created 4 files and they were different sizes chances are the amount of free space is going to be different. So it's going to favor one or more files over the other ones, and one of the advantages of having multiple data files.
Your load across those files and maybe even across disks, which we'll talk about in a minute. But if you don't have the same amount of free space in each of the files then then that if you want to call it that load balancing kind of feature in there doesn't work anymore. You've defeated the purpose of that feature.
And we'll talk a little bit more about the file sizes in the settings and things like that.
You might need to spread that across multiple physical arrays and you can't do that with a single file a file can only live on 11 drive one logical drive so if I create a file group and I put 4 files in it, and they're all the same size. I can take one file and put it on drive L another.
Put it on MNO whatever and they can be on separate sand separate drives. They could be on the same one, it. It's really up to you, but that gives you the ability to do that versus just having a single file when you fill up that drive? What do you do? Well, you have to create another file and?
But what is that? What is that what problem does represent when you do that.
You no longer have that ability to spread the data load between the 2 anymore because they're different sizes. They're going to have different given this one is full anyway, so or maybe it's mostly full so SQL server is going to put a little bit of data in this one in a lot of data over there, so again, you're kind of defeating that purpose, but why am I.
Because this is the type of decision that you need to make up front. You don't want to wait until your file is full or that the drive that that file is honest full. You want to think about. Where is this going to be and you have to be creative. You have to think about you know years down the road? What is my table going to look like?
And or tables in that database going to look like and think about how many files that you're going to need and split up now. You might originally you might create 4 files and have them all on the same drive.
And then as you grow you can then take one or more of those files and just move on to another drive and you've not changed anything about your database. None of your user code is changed and you've now scaled very easily because you thought about it up front, but if you only created one file to begin with you're going to have a much harder time deal.
That makes sense.
It they can file groups can let you separate objects as well. It used to be separating your data in your index is used to be a common thing that you would put data on OneDrive in indexes on another. The idea being that you have 2 physical drives and you can be reading the indexes and pulling the data at the same time, but from different physical.
Getting performance advantage of it well, the drive situation is gone. As has changed. A lot over the years and that's not necessarily as important as it used to be specially with solid state architecture. But the concepts are still relevant so it may not just be data index.
With your sales tables on this file group and you're you know, whatever tables on this file group and now you can split them out and put them different places, if you want to again if you don't do that up front, then that's going to be a lot harder to do you're going to actually physically have to move data later on another?
Filegroups give you is finer level of backup and recovery. You can actually back up at the Filegroup Well, you can back up at the file level or the fire group level or the database level and you can see you can restore so again if you have a database that is very large 20 terabyte database and you cannot.
La you cannot do a full backup every night and you might want to make sure that you get a full backup every night, but you might be able to split it up and do filegroup back up so if you have 4 file groups. You can backup one this night, the next one. The next night. The next one. The next night so on. And so forth and you can split that up there's again without the different filegroups.
You're stuck this gives you the option if you need it.
Are the the other thing that I touched on before and said I was going to come back to is your now separating the user objects from the system objects, meaning the data from the metadata on that. So when I create a stored procedure. Sure, the stored procedure definition itself goes in the primary file group in.
But when I load data into a table that data. I'm going to point it to a secondary fire group, so the best practice is always created database with a primary filegroup and generally just one data file that's relatively small in the primary file group and then immediately create a sex.
Just one secondary file group with one or more data files in it, and now you make that your default fire group, so whenever you create an object that object that table that index. Whatever lives in the other one. So it keeps your data separate from your other one why well again, it, it makes it easier to kind of spread this.
But there's also another important aspect of that is anyone anyone know why you would need to do that.
You might know, but
Yes, exactly so Hugo says for Filegroup restore when you're restoring at the file or fire group level. Your primary file group has to be online. So you need to, you want to separate your user objects. 'cause you're probably going to restore like we have a problem. It's probably going to be with your user objects.
Sure, that your system data that metadata is intact and it needs to be online for you to do. The other far group, so that alone is a reason why you want to split these out. So my best practices always is create your primary filegroup with one data file set it to a size and we're going to talk about the size.
A few minutes and then create a one or more secondary filegroups with one or more secondary data files and that's where you put your actual data and leave the system stuff and the other one separate. Now they've added a few things recently that throw, a little bit of a kink into that.
Hey, I typically made my file my primary filegroup relatively small because user objects usually don't take it unless you've got like 5000 stored procedures. You can usually do that in a couple couple 100 megabytes or less, but can't think of a feature that they've added recently that throws a wrench in that.
Query store absolutely Corey store query store. You don't have a choice when you turn on or enable query store for a given database. It creates a table or a set of tables really in the primary filegroup and you don't have a choice to change that, so if your settings for the query store were to say let.
100 gigabytes of query store data, which is a lot of data for the query store. But if you did that. That's going to go in your primary file group, so if you have query store enabled you have. You might need to make that? Well, you will have to make it larger as it goes so think about again ahead of time? Are we going to use query store and if so.
Cure when you create your database plan accordingly because wherever that primary data file is it needs to be big enough to handle the amount of data. We're going to put into it. So don't start it out and put him on put all your primary data. Files on the L drive because you have 100 gigabytes of space and they're all 200 megabytes in size, so that everything is great.
On query store later and all of a sudden that drive is filled up in like 30 minutes think about what you're going to be doing with that database and plan ahead for that specially something like that 'cause That's something that gets a lot of people they don't think about it, you turn it on and all of a sudden the San admin is yelling at you? Why did you Why?
Wives etc, etc just keep that in mind.
Alright so how many how many filegroups should you have anyone wanna venture.
Or there's a perfect answer for this.
More than one, but yeah, it depends that's the perfect answer for anything in SQL server on there. But at least 2. Like I just said your primary filegroup just have that one data file and leave it alone. You know make it whatever size it needs to be leaving alone, then create a secondary.
With one or more data files that you're going to put your object now. If you're looking long-term you're going to probably have more, but you can more easily grow and scale. If you have more files or filegroups. Then you actually need versus having too few.
On there, but what's probably going to drive your decision more than anything is partitioning. A lot of and we're going to talk a little bit more about partitioning. But when you partition. You're going to put a given partition in a given file group and depending on how you're going to utilize that data you may have.
Partition or I'm sorry, you might create a file group with one or more files and put a single partition in that and if you had 50 partitions. You might end up with 55 groups that may be a little bit of extreme, but again, it depends on what you're going to do with it if your scheme is to constantly evolve you only want to keep 3 years worth today.
Then you're going to use partitions to do that. You might have that kind of scenario, so that once you're done with that oldest partition that old, a set of data you just use metadata operations to switch out that partition from your partitioning scheme and then it's all the metadata operation. You don't have.
Worry about taking your system down you know things like that, and then that table and then you essentially what you get is a table and you can do what you want. If you don't need the data. You can delete it. But more likely. You're going to archive it or you're going to back it up or do something. But if you didn't partition.
And you were trying to do that? What's your only option. If everything was all in one table or 1 by the way every table in SQL server is partitioned when you create a table or an index. It is partitioned behind the scenes. But it only has one partition. You would have to create the other ones.
Did have that one partition and I had 10,000,000,000 rows of data and now? I had to delete 500000000 rows from 3 years ago? What's my option.
Delete it my only option is to delete the data, an what's wrong with the delete.
It's a fully logged operation and it's expensive. It's going to make it's probably going to fill up your log file blah blah blah if you did, you're planning out front and you did, the partitioning. It would be a metadata operation it would be almost instantaneous and it would not touch your log file at all for all practical purpose.
A little bit more on partition in a second, but the partitioning will often drive your file screw filegroup scheme, so right off the bat when you're thinking about your database in the objects. You have to be thinking about how you're going to partition because that's going to go hand in hand with how many files and filegroups you're going to.
To live all that other stuff.
OK, so now we talked about the filegroups now we're going to talk about the files within the file file groups? How many files should you have.
You all know the answer.
You're going to what you're going to do, if it really is always going to be a small to mid sized database and what is small to midsize why you know if you're talking a couple 100 gigabytes or less. There's almost no reason you can't get by with a single secondary data file.
Again, exceptions, there's always exceptions to these rules on there, but how many people know when you create your table? How many rows are going to be in that Table 23, even 4 years from now.
You may have a you know you may know, but more likely you're taking a guess or you're relying on somebody else, making that guess ahead of time and he told you a number that's nowhere clear or nowhere near what it is so even though you think the database is going to be small. I would still plan for.
In just in case this thing grew instead of 200 gigabytes. It was 2. Terabytes OK and you treat the you would definitely treated database differently. That's 2200 gigabytes versus 2 terabytes you would utilize partitioning more you would utilize more data files, etc, etc. So think about that now there is really no.
You know this was kind of hard to hard to say, because there's so many factors that influence and depend on make this depend on this and I just gotta make sure so we go to.
Turn up.
When do we go when we end?
550. OK. That's what I thought.
If you had more data files or fire groups. Then you needed you are probably OK your penalty for having too many data files is far less than having not enough data files when you when you really need him now that doesn't mean go off and create 100 data files just in case again. You gotta put some thought into it and you have to be realistic.
You want you don't want to be in a situation where you have to change your whole structure. An add things down the road so way your way your stuff there temp. DB is is a different matter. You've probably all heard things about you have to have multiple data files for tempdb.
Bob Ward spoke about that in several things even in the keynote. There's still contention for things like what's called the PFS gam S cam and that's that's going to be there, so temp. DB is a different story. You always going to want to have multiple data files for that. And there's plenty of good info out there, so I'm not going to touch on that, but the data files are really more.
We use some how much data you're going to put in how fast are you going to put the data in and then what are you going to do with that data that's in there and one like I say how fast you're going to put it in if you're going to load.
A million try to load a million transactions a second into a table. Let's say you have a stock app and you're going to be loading as much data as you can you're never going to load that much data into a single data file you're going to have contention you're going to have certain things like that, so you have to think about how you're going to use.
Not just how much data. I had a customer that actually was US stock type customer and they were trying to load 100 gigabytes into an empty it every morning when the Bell rang they would start throwing data in this table and at the end of the day. It would be 100 gigabytes in size, so they were loading lots of data very fast and they had.
Because they were trying to do it all on a single file and just by adding multiple files. We got rid of a lot of the contention. We also did some other things, but it's the kind of thing that you need to think about again up front on there.
II did touch on this before definitely iterated all files in the file group should be the same size and they need to have the same auto grow and it should be affixed setting never if you have a database that has grow into your data your log files grow by 10% slap yourself.
Change it OK don't ever use percentage on your that because it's not consistent anymore. There's no way for you to predict or control what is going to happen on that so put a fixed size when you do that and that's especially true for this here, so you want when.
Files get full you want them to auto grow, but you want to all to grow at the same time, and there's a prior to 2016, there was a trace flag T 117. I think 1117. If you turn that on it would grow all files in a file group at the same time, so if I had.
And let's say they were all the same size and they all had the same auto growth. When one of them started get full. It would grow all for the same amount at the same time, so they would all keep that equal amount of free space going on the problem is, is that with trace flag 1117 it's an instance level flag. It's a global flag and that affect.
Basis and then there was another one.
In 2016, they added another option that says auto grow all files and it's a database level option, much, much better, so you never turn the trace flag on you would just say what database do. I want this on and turn it on now. It should be on all your databases because you again when you create.
You should make them all the same size from the beginning, so that should work. But if you have an existing environment where you've upgraded databases chances are the files are at different levels. So you might want to defer setting at the database level to begin with until you've gone and cleaned that up so now you can do that at the database level using the altered database op.
Don't don't count on Agro be proactive. Instead, you well. I mean again that depends on an environment where we have thousands and thousands of databases and we can't go and look at all those databases and proactively grow them plus. We don't we're not the owners of those databases.
The users so we don't really know their usage patterns. So it doesn't kind of makes sense. So we do rely on auto grow but we have set all the things that we need to a front, but I'm really talking about on this is that if you have a database that you're planning think about again down the road. Where are you going to be and you might want to just make?
Larger than you think they need to be at the beginning just so that you don't have to worry about that. Now I know that not everybody has all this extra space sitting around but think about that. That's why I want to do that because if initially I create these data files that I make him wonder.
And then 6 months from now. I need 500 gigabytes and I'm going to grow their what happens if 6 months from now. I go to do that and they've added so many other databases. There's no more space on that disk for me, so if I make that database or the data files as large as I think I need him to be within a reasonable amount of time. I know that when I'm growing. It's going to be there.
You may not always have that luxury but it's something to certainly consider. Now your first thought is probably well isn't that going to be a penalty. Having your database, too big so I made my database. One terabyte instead of 200 gigabytes is that a problem.
Well, for the most part, no because when you're backing up a database. You're only backing up the actual extent sore pages that are actually used so if I have a terabyte database. But I'm only using 200 gigabytes. I'm only backing up 200 gigabytes worth of data so my backup is no longer with a terabyte database that.
Database as long as that's only the data now on the restore part. It may take a little longer. But if you have instant file initialisation turned on. It's a few seconds longer. Maybe on that there might be some other kind of Gotchas, but it's not a lot of people think, because the database is this big.
Is going to be harder to manage in heart? You know the backups going take longer? That's not the case at all, so don't worry about that aspect of it.
Transaction log certainly a different a different story there, it should always be a single file there's no.
Viable reason to have a transit more than one transaction log for a well planned out database now. You might have run into a situation where your transaction log was on a physical drive that you ran out of space and the temporary thing might be to have to create a second file on another drive to get you by until you can move.
On where you get a loan, but there's no performance advantage for having more than one file and there's actually lots of disadvantages. So always think about a single having a single logfile tempdb contention. I'm not going to spend a lot of time on that, but there was another trace flag 1118 that said, Don't use.
Extents but as of 2016 that's built into the product and you don't have to worry about that anymore. Prior to 2016. You would use that but there is that option that was telling you about now the auto grow all files. That's for the user databases. Tempdb will automatically do that. But the user database. You have to you have to change that.
All right, so this is what a database might look like you have your primary.
Is that? Show me there we go? You have your primary filegroup with a single primary file in it and that again is where your metadata goes when you create a table. Sure, the definition of the table goes in there, but the data is going to go in one of these probably your data file group is where the data is going to go and then the indexes.
Going to go in this one NB spread out, however you need them to be and again a single log file.
Alright I'm not going to touch, too much on this There's a lot of good information. I've got a slide coming up. That was supposed to be a demo, but because of the time things were not going to do it, but I have links to things that will tell you a lot more about it, but the thing with the log files is they have something called a virtual log file VLF it's a logical.
In the log file that the log manager operates on if you will so when in this picture here. Each of those blocks is a virtual log file and then the lighter color information. Those VL FS are active, meaning that I have an open active transaction or that transaction has.
This guy is not been committed. We didn't do a checkpoint yet and the way that the log file works essentially is it starts at the beginning, I don't know what's going on with my pointer, but it starts at the beginning and it goes to the end and kind of wraps around and hopefully by the time it wraps around. It gets there. You've already committed that data and you can reuse that portion.
But as far as this goes you want to keep an eye on the number of log files that you have OK 2 virtual log files. Sorry too many year are bad things you've got databases with 203, 104, 100 or more virtual log files think about going back reorganizing those.
Down and then grow them back in larger increments, so that you get less log files. You want to stay under I like to stay under about 150 virtual log files and these are log files for databases that are very large terabytes in size, so that's not an unreasonable request there.
And again there's some things on that there's a couple links here that go into great detail about how to manage the VL FS and what they what they are too much too much too few so I'm not going to go into that.
Will go back to the partitioning. I've already talked a lot about this? I'm not going to whole lot. But this is what I was talking about before and my pointer is not working every table or index has a partition by default. So it will have one partition if you don't do anything else and then all.
The index pages living there, it's either going to be a data page. Large object page or what's called a row overflow page. That's in there, but they all are partitioned.
And first and foremost partitioning as a data management tool lot of people think partitioning is that all about performance and it can be about performance, but it's about management first if you have a table with 20,000,000,000 rows and it's not partition and you need to rebuild the index you're rebuilding 20,000,000,000 rows if.
That table and you had 10 partitions. You might be rebuilding 2,000,000,000 rows at a time. If you had 100 partitions. It might be 200 Gigabyte 200000000 rows, etc, etc. So managing that things like index rebuild. Statistics updates, even though it's all one histogram, but you can still update statistics.
Mission but especially switching partitions in-n-out you can take that large amount of data as long as all your indexes on that partition table are aligned with the partition. It's a metadata operation again, something something you need to keep in mind up front yes.
Yes, as our own, said performance could be a side effect it could be a positive or negative on that. It depends on what you're going to do with it on their partitioning, especially if your quarry is in your where clause is filtering on that partition. Key then it only needs to read that partition or partitions.
Change on there and that in and of itself can be a huge part performance benefit. But it's really think about it as a data management aspect first.
All right and again, these things should be decided long before you start loading data. I can't emphasize that enough doing it. After the fact is hard work and sometimes not even possible in most environments. These days think about it up front and implement it before you start loading a single row of data in there.
Uh let's see schemas. I gotta couple just a few things here. We're almost at a time. The thing I just want to say about schemas. It's another thing that people don't take advantage of enough of front. Schemas Hill, Oregon allow you to separate objects into different groups. If you will and you can that helps with things like security helps with managing.
Again, if you don't do this upfront. It's an almost impossible task to go back and rework a production database, and split it out the way that you need it's not impossible, but it's certainly a lot harder so think about that up front, all right. So the configuration and that was the end of the slides by the way so I just very brief.
Is going to show a database and I'm going to zoom in on this once we get in there?
And just show a couple aspects here.
And let's see.
All right, so this is a database. I've I've got a primary filegroup with my primary. I thought I had the box. Sorry about that, so my primary filegroup only has a single file and it is set to 100 megabytes. You might need a little bit more this for the demo purposes.
Fixed auto grow and it has a fixed size on that the.
The secondary files.
Are in a another file group called DEFG? My default filegroup again have a fixed size fixed auto growth that are all the same and something else. You notice on here is the naming convention. That's something I touched on before, but I want to emphasize here since we're looking at the files this is very simple and.
You go too much beyond that, but if you name, your files say starting with the database name underscore and then even just 12 and 3 for the data files. The primary files. The same with the logical name, etc. You're not going to get into trouble if you have if you call all your data files.
On my data file too. But you have customers in orders if you place them into the same physical directory on disk. You're going to have a clash of the file names so if you start your naming convention off with the database name, underscoring that you'll never have a class so you can move stuff around down the road as need.
I have to worry about contention if you didn't think about your naming convention and you had just a single file that was the wrong name. You might have problems down the road question.
I'm sorry.
You take it that convention is because the file group.
Oh, yes, OK, I see what you're saying the file group is a logical container within the database. So one database can't see the name of a file group from the other one. There's no physical thing on disk that has the name of the file group. But the but the files are different story on that and that's why I want to point this out and we are.
At the time one thing.
Well, actually, yeah, I've gone through I've gone through all the slides and I went through all the properties and want to get too. So I just want to end this by saying This is something that you need to think about upfront put some thought into it. It's not a It's not in agile type thing, you need to.
Guys make sure you fill out your eval cards, please before you leave but the point is you need to figure out what it is, you're going to do up front and enforce that policy make the document. It may be used policy based management like I said, but make sure everybody is adhering to that and put in those principles in.
Are really going to save your butt down the road and that's not just to know that might happen. I'm telling you it is going to happen if you don't do some of those things up front, you're going to be bit by it down the road. So you might as well put that time up front, but once you've decided what those are you don't have to revisit that effort. All the time you've documented it and it's.
Now you when you create a database. You just follow those practices and life is good on there so that's that was the main point. I want to get across and I know we're outta time here. So thanks everyone for coming and don't forget to turn in your evals.