Enjoy the Podcast?
This session is for DBAs and DEVs that have little experience on SQL Server and want to improve their code and performance with small changes.
Are you faced with complaints from users, poor performing code from developers, and regular requests to build reports? Do you uncover installation and configuration issues on your SQL Server instances? Have you ever thought that in dire times avoiding Worst Practices could be a good starting point? If the answer is “yes”, then this session is for you: together we will discover how not to torture a SQL Server instance and we will see how to avoid making choices that turn out to be not so smart in the long run.
You are probably thinking: “Hey, wait, what about Best Practices?”. Sometimes Best Practices are not enough, especially for beginners, and it is not always clear what happens if we fail to follow them. Worst Practices can show the mistakes to avoid. I have made lots of mistakes throughout my career: come and learn from my mistakes!
Circles in the SQL Server Hell:
- Shaky Typers
- Anarchic Designers
- Inconsistent Baptists
- Environment Pollutors
- Overly Optimistic Testers
- Indolent Developers
- Stingy Buyers
- Next next finish installers
- Careless caretakers
- Performance killers
Why I Want to Present This Session:
Beginners need beginner topics. Many attendees get excited about super advanced topics and wish that conferences offered only brain-melting sessions. On the other hand, we all have been beginners at something at some point – and there is nothing wrong about it. Let’s make room for the uber-geeks of tomorrow, starting from the simple things!
Brent Ozar: In this session at GroupBy, Gianluca is going to be talking about not internals, but infernals, the worst things that you can do to your SQL Server. So Gianluca, take it away.
Gianluca Sartori: Okay, cool. Thank you. In this session, as you said, we won’t talk about internals but the topic is infernals. So, all the things that will put you in the SQL Server hell.
Before we go, a few words about me. I’m a SQL Server consultant data platform MVP. I’ve been working with SQL Server since version 7, so a long time. This way I had the chance to make a lot of mistakes and that’s a good thing because I can show you all my mistakes. Currently, I’m working as a DBA at Formula One company, the one with the red cars and the prancing horses. If you want to read my blog, you can find it at spaghettidba.com. If you want to follow me on Twitter, my handle is @spaghettidba.
The agenda for the next 60 mins or so. First of all, we will see why worst practices because we also have best practices, why those are not enough. Also, then we’ll start seeing what could go wrong in different areas of SQL Server. So, design, development, installation, and administration. As you said, it’s a lot of topics. I will cover lots of things. I won’t go into deep details of these because we have lots of things to see.
Before we go, a little word of warning. Not everything is black or white. There’s lots of grey in this. Most of the time, “it depends” is right answer. If you see listed here as a worst practice something that you are doing, don’t get mad, because maybe it’s the right thing to do in that particular situation. It’s not a thing of black or white. Chances are that if you see something here, it is really a bad idea but let’s pretend that you’re doing that in the right situation.
Okay, so, let’s go. Why we need worst practices? Because you know we have best practices, why those are not enough. Well, let’s face it, there are too many. It’s impossible to know them all in all the deep details that are needed to master each and every topic. Also, there is no time because we have a lot of things to do and there isn’t always the time to go and start the topics into deep details.
Also, when we don’t have experience on particular topics, we need to know the bear minimum to get running. Also, it’s not always clear what happens if we fail to follow best practices without lots of white papers to tell you do this and this and this. But they don’t tell you exactly what happens if you do the exact opposite. So it’s good to know what to avoid.
Here, worst practices can help us. They show the mistakes to avoid. They give us the chance to learn from somebody else’s mistakes without having to do all the mistakes by ourselves. If we go and take all the worst practices and [inaudible 00:08:13] them in areas, we could have things that have [00:08:16]. So, the schema design itself [inaudible 00:08:20] the data types. Things that have to do with the names that we give to the objects inside the database.
Then we have development worst practices. Things that have to do with the way we set up the environment with the code itself. So the way we code our queries and objects and the way we test applications and the database. Moving on, we have worst practices in the installation area, so the way we choose and validate the hardware. The way we install the operating system and SQL Server itself.
Then, moving on, we have worst practices regarding the way we administer SQL Server, so backup and recovery, security, capacity, performance, and monitoring of SQL Server. So if you think of things areas as circles in the SQL Server hell, we have a complete hell where the SQL Server guards will put you when you do sins against them.
I will be your Virgil and I will guide you through the SQL Server hell as Virgil did with Dante. So in the first circle, we have the sinners that go under the name of undernormalizers. What does that mean? We have rules to design a database properly and these rules are called normal forms. The sin that undernormalizers make is not normalizing the schema properly. So, let’s see these normal forms. Well, there’s a lot of normal forms but to have a database that is properly normalized, it’s enough to have the three first normal forms.
The first one says that a table should have a primary key and only atomic attributes. What does that mean? A primary key? It’s easy to understand what that means. If we have a table without a primary key, it’s a problem. We are violating the first normal form. It’s interesting to note that a table is the physical implementation of a relation in the relational model. So if it doesn’t have a primary key, it’s not really the physical implementation of a relation because a relation needs to have a primary key. So maybe it’s a physical implementation of a pen, of a bag, of something else, but not a relation. Also, attributes must be atomic. That means that they have to have just a single value. So values with commas, multiple values in a column, that doesn’t work. It violates the first normal form.
Second normal form says that every attribute depends on the whole key. What does it mean? It means that if we have a table with a multicolumn primary key, its attributes has to depend on the whole key. It can’t depend on a single attribute or a sub key of the primary key.
The third normal form says that every attribute depends only on the key. That means that if we have attributes that depend on something different from the key, we have a problem. We are violating the third normal form. To sum it up in a single statement, “The key, the whole key, nothing but the key, so help me Codd.” This is an old one.
So, clues that we’re doing things wrong. Data repeating across rows. We have redundancies in this case. Or, inconsistent data between tables. We have the same information in multiple places and we have different information in different places. That’s called an anomaly. Another clue that we didn’t do things probably, data separated by commas. So example, we have an email column and we have something like firstname.lastname@example.org and email@example.com. This is a clue that the cardinality relationship between the entities in the model is not okay.
Another clue of denormalization. We have structured data in notes column. Why do I say notes column? Because notes are usually huge columns that can contain lots of text, so smart developers start putting lots of information inside notes and separate that with vertical bars and commas and try to parse the data inside notes to use it improperly. Other things. Columns with a numeric suffix. Example, Zone1, Zone2, Zone3. That’s a huge clue that things are not okay.
In the next circle, we will find those who generalize too much, the generalizers. You may be thinking, “But generalizing is a good thing,” especially if you’re coming from object-oriented programs. You’re probably thinking of complex hierarchies of classes and subclasses and specializations and the more you go deep in the hierarchy, will find more specialized classes. That’s a good thing in object-oriented. It’s not a good thing in a database design. In a database design, things have to be specific. Why? Let’s see an example with lookup tables.
Imagine that you have a schema like this. You have some main entities, like customers and orders. Then you have some small entities around here. So countries, states, and order status, and order priorities. These are simply lookup attributes for things that you find, main entities. So example, a country here has a country ID. It’s a foreign key here to the countries table, which only has ID and the description. Same thing here for the state. So we have an ID and a description. Even here or here, we have some attributes that are coded on simple tables with ID and description. So, order status, status ID, status description. Again, priorities, ID, description. What we have here is one lookup table for each attribute.
So, looking at this, there’s a button, I see ID description. Wouldn’t it be cool if I could take this and generalize the design to have a single table to lookup all my attributes? Look at that. Isn’t that really smart? What we have here, it’s a code and a description. We just have to put a table name as an additional key here and we solve all these stupid, little tables. Isn’t it cool? No. It’s isn’t. Because it leads to lots of problems.
So what do we have here? It’s one lookup table for all the attributes. One true lookup table is a name by Phil Factor, if you know the guy, he came up with this name. So what’s the problem with that? You cannot define foreign keys anymore. You have an additional attribute that you don’t have in your entities, which is a table name. You don’t have it, so you cannot create a foreign key.
You have to use a generic data type for the ID, which will be probably something along the lines of nvarchar(some high number) so that it can store as much as possible varieties of data types. This leads to implicit conversions and huge memory grants because it’s a big column. Also, incorrect data because if you want to store here something like dates or numbers, there’s nothing enforcing the correctness of the data that you are storing here. You could use some check constraints to help to a point identifiers which data type we should be using, but really it doesn’t work. That’s not the way things should be designed. Also, everybody has hidden a single table, so locking is a disaster here.
If you generalize even more, there’s one of the biggest sin in database design, which is called entity, attribute, value. This will give you the instant damnation badge. What do we have here? We have again customers and orders. Then we have something that helps us define some dynamic attributes. What does that mean? We don’t store attributes inside the main entities, but we use this complex structure of attribute values to create some dynamic attributes. Not in columns, but in rows.
So we have an entities table to insert each of the values we have in the schema. So one row for customers and one row for orders. Then we finish the attributes we could add to this dynamic schema. Let’s add here another table to take all the values of the attributes. We have attribute ID and entity taken from attribute names. ID is the ID of the referencing entity. So the customer ID and order ID, then you have the value, which again, is nvarchar(4000).
Okay, so what’s wrong with this? Isn’t it smart? Yes, but what we have again is generic data types. So again, varchar(4000) or nvarchar(4000). Again, no foreign keys. There’s no way to define foreign keys here. No check constraints because values are by rows. So there’s no way to access other attributes for the same ID. There’s no way to create multi-column check constraints.
Every time we need to read a value, an attribute from that schema, we have to access the same table, the attribute values table, multiple times. So there’s one access per attribute. Well, there’s some advantages to this design. It’s a dynamic schema, so there’s no need to alter the database and that’s a huge plus in some situations. Imagine replication or some kind of distributed environments where changing the schema is really problematic.
So for instance, in replication, you have some disconnected subscribers if you change the schema there’s some changes to the schema that trigger reinitializing the subscribers, which is not always feasible. So, it solves a legitimate problem in a terrible way. What else could I do? On entity, attribute, value reporting is insanely hard because you have to join two of these dynamic attributes. So it’s multiple joins to the attribute values table multiple times. It’s hard to write. Also, writing to the table is a mess. The application has to…
Brent Ozar: Gianluca, you’re breaking up pretty bad over there. You may try turning off your webcam. If you turn off your webcam, the audio might be better.
Gianluca Sartori: I closed the webcam.
Brent Ozar: Perfect. Okay.
Gianluca Sartori: Isn’t that enough?
Brent Ozar: Yeah, I think that’s perfect. Your sound sounds better.
Gianluca Sartori: Yeah. You said it’s beautiful here.
Brent Ozar: Yes.
Gianluca Sartori: I live in a vineyard in the countryside and the best I can get here is internet by radio.
Brent Ozar: Wow.
Gianluca Sartori: This is not really stable.
Brent Ozar: Wow.
Gianluca Sartori: Let’s try to move on and see if the internet gods help us. Okay, so, what I have here is I’m creating the schema. First of all, I will create the schema in a proper way. So countries and states and order status and order priorities. So all the small tables with just ID and description. Then I insert the values here. So priorities for instance I have high, normal, low, all the confirmed, inserted, cancel, and so on.
Then I have the customers table. So what I have here is all these attributes. They are looked up in the small ID description table with a foreign key. Same here in orders. So I have a reference to the order status and order priorities. This is how a proper schema looks like. So if I want to do the same thing with a one true lookup table, we’ll have here is I get rid of all these small tables and the schema I create here is with our lookup table, which has table name, lookup code, and description, nvarchar(4000). I have my check constraints to try to use the correct data types.
Then, what I have here, I’m inserting all the lookup values inside the lookup table. So everything goes here. I have countries and Italy, Denmark, and Slovenia, which maps to what I have here. So you see the difference. I have here this additional attribute which is the table name. This doesn’t let me define foreign keys. So when I’m creating my customers table, I cannot create a foreign key on this column. Same thing here with orders. It’s looked up on that lookup table and I cannot create foreign keys here. But again, I can insert my data and it looks exactly the same as it looked on the proper design.
If we take this a step further, what I have here is an EVA design. So let’s have a look at what it does. First of all, I have to rerun this setup. Let’s go back here. Now I need attributes, entities, and values. Let’s have a look. Attributes just has an attribute ID and attribute name. Entities has an ID and a name. Here, values has a reference to the attribute ID, the entity ID, and here, ID is the ID from the referencing table. So, orders and customers.
Okay. Let’s create this and insert some data. Inside entities, I will have customers and orders. Attributes, I have some dynamic attributes that are not columns in my entities. So, example: deliver date, cancel date, email, telephone number, and window width.
Then to insert the values, I will have to enter to things like this. So, attribute ID 1. It is the delivery date. It is here. Delivery date is number one. Then I have the entity ID 2, which is orders. And ID, this is the ID from orders. So it will be order ID 1, order ID 2. This is the column where the values go. So here I have to insert the dates in some way. Because it’s a varchar column, I have to decide a format to store the dates. This looks like a reasonable way to store dates. But again, there’s nothing preventing me from inserting data like this. So, February 30th works. It goes in. Nothing stops me from doing that.
Let’s do this. Let’s insert this data. What I have here is attributes. So in this case, what I was talking about this attribute ID entity ID 2, so orders, and ID order 1, 2, 3, 4, 7… This is an invalid date. Good. So, let’s see what happens when we read this example. For customers I have to join to the values table the first time to get the email. So I will force the attribute ID 3 for email. I have to join again to get the telephone, so attribute ID 4. Again, I have to join two values with attribute ID 5 to get the window width.
This is what I end up with, just to query three attributes in my schema. So it kind of works, but it’s really clunky. It’s not a good design. Again, same thing for orders. I won’t repeat here, but you see for each attribute I add here, so delivery date, it’s a first join, second join, for cancel it. Here, interesting to note if I do something like this on the date values, so I have a predicate on the value, it doesn’t work because there’s an invalid date in my data.
If I want to read this data in another way, to have a single access to the values, so no multiple joins to get the data from there, what I can do is I can beef up the data, which is in rows. I can change that to columns. PIVOT is a way to do that. In this case, what I have is that all the information that I had in rows, so one row for each ID in this case, has been turned into columns.
What I can do here is I combine that pivoted data with my customers so I just have one access to the values table, which is more efficient and it yields the same results. And if I want to write to this schema, I can create a view with this definition and instead of trigger as Adam did in his presentation a few minutes ago. If you don’t like pivot, there’s a crosstab, which is an old school pivot. You have a case expression here to extract the data and change rows to columns, but the concept is pretty much the same. Okay, so, are there questions so far?
Brent Ozar: Nope, you’re good. You’re good.
Gianluca Sartori: Good. Let’s go back to the slides. Moving to the next circle, we’ll find the shaky typers. As you may imagine, it’s those people who fail to choose the right data types for their columns. Let’s see why it’s a bad thing. Examples of bad data type choices. Numeric data types for non-numeric data. Example: a VAT number looks like a number but it’s not a number. You don’t multiply VAT numbers or postal codes look like numbers but they’re not really numbers.
Another thing that will get you the instant damnation badge is storing data as their human readable presentation, which is what we just did with the dates and that EAV schema. So don’t try to save dates in varchar columns. Again, the use of deprecated data types will get you instant damnation as well. So think of text and image for instance as being deprecated since forever. Don’t use that in new development.
Well, this is an interesting one. Using larger datatypes just in case, because who knows? This column could contain some insane amount of data someday. This is not how design works. The size of a column is a constraint on the data itself. So use the bare minimum because not just of efficiency but also for constraining the data in that column.
Also, the same applies to the use of variable length data types for fixed size data. Example: VAT numbers are exactly eleven characters in Italy. I have no idea how it is in the U.S. but in Italy it’s eleven characters. So if it’s twelve characters, it’s wrong. It can’t be ten either. So it’s exactly eleven. This is another one that I have seen a couple times, storing durations in date/datetime columns. No. Durations are plain numbers.
Another thing that I see wrong all the time is Unicode. This has two faces. The first one is using Unicode when it’s not needed. Example: URLs can’t be Unicode so don’t store URLs as Unicode. There is really no need to do that. On the opposite side, there is storing things that should be Unicode as ANSI. In today’s globalized world, even if your application will not be used in foreign countries, there’s people moving around the globe. So what if you have to store some Japanese name in your application and you’re using an ANSI column? That won’t work. If you have the same information stored in different tables, please be consistent. Don’t use different data types in different places. That won’t help you.
Moving on to the next circle, we will find the anarchic designers, those who fail to use rules in their database design. So what we will have here, we’ll have no user primary keys or use of only surrogate keys. Well, no primary key, we already have seen that it’s a bad thing because it violates the first normal form. So your table really is something different from a relational table. It’s a bag or a pen, but not definitely a table.
But the user surrogate keys only, what does that mean? Well, sometimes I see tables that only have an ID column and that ID is an identity column or unique identifier column. That ID is the only key on the table. It’s only surrogate key. So, remember that an identity is not the only possible key. A table has to have [inaudible 00:35:00] key. You’re not forced to use that key as the primary key. You can use a surrogate, but remember to always create a unique index on your natural key. If you can’t find one, chances are that your design is wrong.
Another thing that I see all the time, no foreign keys. Well, foreign keys, they’re awkward. They force you to do things in a set way, in a forced way. So they’re not good. I want to be free to insert the data in the order I want. Okay. Go on, do that. No check constraints because the application will guarantee consistency. Yeah. Okay. That never happens. Also because often the database is a hub for multiple applications so it’s not one application. You have multiple applications hitting the database and enforcing their constraints inside all the application can be costly and not always consistent. So you better do that in the database as much as possible.
Wrong data types, we already have seen that. Remember that the data type is the first constraint on the data. So try not to get that wrong. Well, this is another one that I see all the time. The use of null where not appropriate. If you have all columns that are nullable, it’s probably wrong because some information has to be mandatory on some tables. So everything null is probably wrong.
The other side, making everything non-nullable will force people to start inserting dummy data. So you will have empty strings or zeros or this kind of dummy data. How can you tell a proper zero from a dummy zero that really means null? There’s no way to do that. So use nullable where appropriate and not nullable where appropriate.
Moving to the next circle, we will find the inconsistent baptists. Here is those people that failed to choose the appropriate names for their objects in the database. What we will find here is lots of damned people from Hungary. Nothing wrong with Hungary, it’s a nice str_country. The Hungarian notation is also known as tibbing. Why? Because with tables is where you find tables name as tbl_something. It’s also known as Volkswagen because of the vw_something for views.
So all this came from Hungarian notation which was a notation for programming languages which didn’t have proper data types. So the only way to identify data types was to put a prefix in the variable names describing the type of the information stored in the variable. So you had str_something, i_something, and so on. In databases, this becomes tbl_ for tables, vw_ for views, and sp_something for stored procedures.
This is a bad thing because we have the metadata to tell the type of the objects we have in the database. So we really don’t need to know what’s happening by looking at the name of the object. Also, chances are that at some point you will have to split a table into tables and expose the original table as a view. In that case, you will have a view named tbl_something, which really is confusing. So another reason to avoid Hungarian notation.
Also, don’t use insanely short names. If you’re old enough to remember AS/400 databases, you had only eight characters for table names. It’s not that way anymore. So if your tables are named something like ak16of, well, get out of the 80s. It’s 2016. Also, don’t get over enthusiastic with long names. Don’t name your objects like Java methods. We will have to type that in your queries all the time, so keep it as short as possible.
This is one thing that happens all the time in Italy. There’s schemas with half of the objects in English and half of the objects in Italian. That happens all the time. You don’t have that chance in U.S. or U.K. but we have that all the time. It’s really annoying.
Well, the use of the sp_ prefix. This is probably the most well renowned worst practice regarding naming. Sp_, it’s Hungarian notation as we said. Also, it’s a wrong Hungarian notation because sp doesn’t stand for stored procedure but stands for special. In fact, it refers to system objects and it has a small performance hit because objects are first looked up in the master database and then in the user database. It’s not a huge performance hit but keep that in mind if you are running that very often.
Don’t use reserved words or illegal characters in your names otherwise your query will be still possible because you have square brackets, but really, really tough. Don’t use system-generated constraint names. If you create a constraint without explicitly naming it, SQL Server will choose a name for you. It will be a random name. So if you have the same database in multiple places and you have to deploy a script to change something in the database, the constraint will have different names in each different installation of your database. So choose your name upfront and stick to that. The worst of all is having no naming convention or multiple naming conventions which is pretty much the same as having no naming convention.
Okay, let’s move on to the next circle where we will find the environment polluters. Here are those who failed to set up their environment properly. So, instant damnation for those who develop in production. When I present this, I see people laughing at this point but really it happens. It happens all the time, more often than you think. Also, the test environment is not for development. The test environment, as the name suggests, is for testing.
Another thing that I see all the time is people developing on a shared database. No, really. There’s no reason to do that. Use your own laptop or desktop for development. Nobody else should use the same development environment as you for development. This way, if you do something that breaks the schema, that changes something that you are taking care of and somebody else is not, you’re not breaking somebody else’s work. Know this, machines, desktops and laptops, are beefy enough to do that, so don’t be worried of local database development.
Another thing that I see all the time is no source control for the database. Nowadays, it’s easy to put the database on source control. It used to be hard. Nowadays we have third party applications [inaudible 00:44:12] inside Visual Studio. So choose one and stick with that.
Another thing I see all the time is developing with sysadmin privileges. Imagine this, you start your development on your machine, so your sysadmin. You use Windows Authentication from your Visual Studio. So you’ll hit the database impersonating yourself, so as a sysadmin. You do all the development work, so the application is ready. You go to the DBA and you say, “Okay, run this to create my database in production so we can use the application.”
“Good” says the DBA, “and which permissions are required by this application?”
“Oh, well, I have no idea.” That’s because you used sysadmin and you didn’t have to take note of all the permissions needed by the application. So develop your application using a separate login which has no permission and add all the permissions to a row so when you go in production, you just have to add users to the row.
Well, this is another thing. If you develop for targeting a specific version of SQL Server, use that in the development phase. Same thing applies to the edition. So if you’re targeting Enterprise Edition, don’t develop on Standard, and the other way around. Starting from 2016 SP1, this aspect is less problematic, but keep that in mind anyway.
Okay, moving on. We will find in the next circle the overly optimistic testers. These are those who fail to test things properly. So, not testing all the code will get you damned. Also, if you test your application, use meaningful data volumes. Otherwise, you’re just testing correctness and not the performance of the application. You will get instant damnation again for testing in production. This sounds obviously but I’ve seen that happening multiple times. So maybe it’s not so obvious. Not only it can alter production data, but also it interferes with production users. So really don’t do that.
Another thing that I see all the time is testing in development environment. Usually the development environment is a small database with a few rows, with few fake rows, so it’s not good for testing with meaningful data volumes. So it’s useful for unit tests at most.
Going on, I’m afraid I will have to put some developers to hell. Don’t get me wrong, I’m not into the DBA/developer wars but, hey, you know, sometimes developers are lazy. So, what do we have here? No transactions. It might sound ridiculous but I’ve seen this happening. A few years ago, I was hired by a famous shoe company here in Italy to fix their warehousing system. It was a huge application in DB 6, yes, I’m that old, so what it really was DB 6. The first day on that job, I opened up the DB 6 project and I searched for BEGIN TRANSATION and I had zero matches in the project. So it’s been really really tough.
Okay, no error handling. This happens all the time. If you decide to handle your errors in T-SQL, remember that @@ERROR is a thing of the past. Today we have TRY CATCH blocks so you use that instead. Getting wrong isolation levels. Again, it’s a thing of laziness. Just because read committed is the default, it’s not granted that it’s the appropriate isolation level for the whole application. So you will have to go up or down in some situations. Remember that NOLOCK means no consistency. So using NOLOCK will not honor locks put by other sessions but will also not honor locks from your session, which means that you will end up reading rows multiple times or not at all. So NOLOCK means no consistency.
SELECT *. This is a popular one. SELECT * means that you’re pulling for no reason all the columns from your tables. Which means that all this data has to travel across the wire, which is typically slow and means that SQL Server will easily give up on using indexes because at some point it will have to look up all the columns that are not included in indexes. So it’s really a bad thing.
One of the worst ones is using Dynamic SQL with concatenated parameter values. This happens all the time because a lazy developer doesn’t want to add up a properly parameterized query. So what you end up with is code vulnerable to SQL injection. So because of the two minutes you saved by skipping using proper parameters, your code, your whole project, your whole database, is now vulnerable to SQL injection. Well done.
Another thing that I see all the time is no abstraction layer. Really, do that. It’s one second. Create another schema with views that map one to one to your tables. So when you have to change something in your schema, you will still have a contract with the application. This will make things much easier for you in the future. So use views and functions and stored procedures, don’t give direct access to the tables in the application.
Let’s move on. At this point, there’s no more putting developers to hell. Now it’s the DBAs turn. Regarding hardware worst practices, using unbalanced or inadequate hardware for SQL Server. Why is that a worst practice? Because SQL Server licenses cost a lot of money. Putting them into a low hardware is really a capital sin. So you will get instant damnation for reusing the commission server for new installations. Why? Because you will have typically slower CPUs and licenses cost the same on fast CPUs. You will have less RAM supported and RAM is one of the cheapest ways to enhance performance without spending lots of money.
Regarding storage, one of the worst practices is planning storage with capacity in mind. Generally speaking, big storage is slow storage. So plan your storage with speed in mind, not capacity. This is typically the choice of wrong RAID level. Well things are changing a lot nowadays on the storage area because we have all of this solid state hardware but keep that in mind if you’re using old style storage, the choice of RAID level. Typically, what you want for SQL Server is a RAID 10.
In the next circles, we will find the next next finish installers. Well this is easy to guess what that means. If you have an installation with accepting all the defaults, you will get terrible defaults for SQL Server. Which means the data files on the system drive, which is not really what you want to have. You will have MAXDOP = 0. So use all the CPUs and in today’s multicore CPUs, that is a time bomb. And max server memory, all the memory available, which again, is a time bomb for systems that are not 100 percent dedicated to SQL Server. SQL Server will end up using all the RAM, even stealing it from Windows or competing with Windows for the RAM. So really put a limit to that.
Another thing that I see is installing unused components. Really just install the bare minimum that you need. Otherwise, you will have to patch everything. Installing just the components that you need will reduce the surface. If you can, don’t install multiple services on the same machine. Example: the relational engine and analysis services don’t go well together. Same thing for reporting services. It’s really completely different types of workloads. So don’t try to put everything on a single machine.
There’s also some easy wins on I/O that you really shouldn’t be giving up. It’s so easy that it’s a capital sin to give up on that. Example: partition misalignment, it’s pretty much a thing of the past with modern operating systems it doesn’t really matter that much because we have a default one meg offset for partitions nowadays. But it used to be an easy win on 2003. Again, using the default allocation unit. It’s 4 kilobytes in Windows, the default. SQL Server usually performs better with 64 kilobytes allocation units.
In the next circle we’ll have the careless caretakers. This is a pyramid that Brent will probably recognize because I stole it from his website. It describes what a database needs. At the foundations of your pyramid, you will find backups and then going up, security, capacity, performance, and future-proofing. So everybody obsesses about performance but really is important is what stands at the foundation of the pyramid. Basically, backups and security. So let’s start and go through these levels together.
Backup and recovery worst practices. Well, having no backup at all. That means that when disaster strikes, you have nothing to restore from. Also, it doesn’t hurt only recoverability but it also hurts availability because with full recovery it’s a time bomb. If nobody is truncating the logs, the disk where the logs are sitting will fill up and the database will get halted.
Another thing that is really a worst practice is ignoring RPO and RTO. RPO stands for Recovery Point Objective and RTO stands for Recovery Time Objective. These two numbers mean the time of work, the amount of work that you are allowed to lose. RPO and RTO means the time that it takes to restore back to normal. These two numbers are not your decision. It’s a business decision. Don’t try to apply standard policy to all the databases, so it’s nightly full backup and lock backups every 15 minutes. No. It doesn’t work like that. You have to go to your business owner and ask what they need and have them signing that with their blood.
Okay. Not testing your restores will get you instant damnation. It’s really, there’s no use in having boxes full of tapes with your backups if you cannot restore from those tapes. This is coupled with consistency checks. If you don’t check consistency of your database and you find out that you have corruption and you restore a corrupted database, you’re back to the same situation as before. So really, test consistency on your database.
When disaster strikes and you run DBCC REPAIR_ALLOW_DATA_LOSS, what you do is exactly what is on the [inaudible 00:58:29]. So you allow data loss, which is instant damnation for a DBA. So what you want to do is restore for a non-corrupt backup instead. Remember that the responsibility of the DBA is to perform restores, not backups. There’s no medal for taking backups. There’s medals, or no damnation, for performing restores.
Okay, so, moving on. Security worst practices. Things that I see all the time. Too many sysadmins. I once went to a customer who had domain\authenticated users in the sysadmin role. Which was really really bad. So that means that the guy answering the phone was a sysadmin on the database. Not really what you want to have. Or everybody who knows the password for SA. This will get you instant damnation.
Another thing that I don’t like to see is the use of SQL Authentication because it’s not as strong as Windows Authentication. Why? Because basically because of passwords. SQL authentication doesn’t enforce you to have a password policy. You can apply a password policy, but you can skip password policies, which will let you have weak passwords such as “123” or “password” or same as user name. This one, I find it all the time. So Windows Authentication is better in that regard because it forces you to apply a password policy the same as the one you have in your domain. If you have sensitive data in your database, really set up some auditing. Otherwise, you will get damnation.
Regarding capacity, worst practices in this area. Not checking for disk space. That means if you have no space left, the database stops working. Really, test it. Example: you have full recovery and no log backups. That will fill the disk. If you rely 100 percent on autogrowth, that’s not ideal. What you really should be doing is sizing your database files upfront and never touch those two things, autoshrink and autoclose. Well, autoclose is a different thing. It closes the database when nobody is using it.
But autoshrink in particular, what does this do? It tries to free space in the database by moving pages from the bottom of the file to the first hole in the data file. This creates massive fragmentation. This, coupled with autogrowth will have a database that grows and then shrinks and then grows again and then shrinks. The effect of this is internal fragmentation for the shrink operation and operating system level fragmentation for the growth operations. So really, it’s super bad. Avoid that at all costs.
This is particularly important for tempdb and also database files for tempdb should be pre-sized and you should have multiple files in order to avoid incurring a latching and striping penalty on your tempdb. It’s so easy and also nowadays with 2016, the installation wizard lets you do this during the installation, during the setup process. So it’s really easy to avoid making this mistake.
Moving on. Regarding maintenance, the worst practices here. Not maintaining indexes and statistics. In particular, indexes require maintenance but really don’t get obsessed about reindexing index fragmentation. Index fragmentation is more important for internal fragmentation. So the space that is wasted inside the index, so you should avoid reading too many pages for the information that you want to read. Nowadays, disks, especially on SANs are fragmented by definition. So really there’s no point in maintaining indexes to avoid external fragmentation.
Another thing that I see all the time is the use of catch-all maintenance plans. So what we have is something like this. First of all, we rebuild all the indexes. Then we reorganize all the indexes. Then, why not? We update all the statistics because that’s what I’m supposed to do, right? Well, not exactly. First of all, rebuild index, okay, all tables, why? When you clean your house, you don’t take all the furniture out of the house, clean the floors, and then put the furniture back in. You want to clean just when it needs to be cleaned.
The same applies to database tables. So you should rebuild just the indexes that you want to rebuild. Then, when you rebuild the indexes what you end up with is an index completely rebuilt, so a perfect index with full scan statistic on the index. So there’s no point in updating it again statistics using a sample because this [inaudible 00:1:05:03] creates a sample statistics and analyze you specify the scan type, full scan. But you already have done this, well there’s no point in doing that. Obviously, there’s no point in reorganizing indexes when you have just rebuilt them. Okay, moving on. In the next and final circle, we find the worst…[inaudible starting at 00:1:05:37]
Brent Ozar: You broke up there for a second, Gianluca. Your audio is gone again. All right. Go ahead and give it another shot there. Keep talking and we’ll see what we get.
Gianluca Sartori: … the best way to perform [inaudible starting 01:07:38] usually doesn’t help much. So let’s go and have a look at all the different levels. Query optimization worst practices. Worst of all is row by agonizing row strategies. So RBAR, thanks to Jeff Moden for coming up with the term. That means cursors and wild loops and upside cursors. So if you’re doing your loops in .NET, it doesn’t get any better than doing that in T-SQL.
There’s also hidden RBAR, which is scalar and multi-statement functions. That’s really bad for performance. Other things that are really bad for query optimization, views on views on views. Why is that a bad idea? Because it looks like a brilliant idea at first because you reuse all the code. Yay. But there’s a catch. You can end up losing control if you have too many views nested. So at some point, you will keep accessing the base tables again because you don’t have that particular column that you need in one of the levels of your views. You will have again unnecessary joins to the base table.
Okay, so, what you end up is that you lose control at some point. So you remember this movie, Inception, so you had a dream inside a dream inside a dream, well, things didn’t turn out very well for the characters in this movie and the same applies to views on views on views. So try to avoid that as much as possible.
I have a quick demo on views on views on views. Let’s have this. The concept is pretty simple. You have a view, you create a view on open orders. So this is based on AdventureWorks. You take these columns and again, you create another view here and again you need to take another person ID from customers, so you have to join again to customers. What you end up here is that you query the same tables over and over directly or through the view for no reason whatsoever. So get rid of that and query the tables directly.
Query optimization worst practices. One query to rule them all. So I once had to tune some queries which was to max of SQL, that’s not fun at all. The optimizer is good, it’s not perfect. So when you submit a 2 meg query, it will simply give up and give you whatever plan it can come up with. In many situations, a divide and conquer approach is much better.
Another thing that I see all the time, DISTINCT, because why not? Who wants stinking duplicates? Yes. So DISTINCT. No really, DISTINCT means that you will have to use a SORT to take away the duplicates and it’s really really bad performance-wise. Or, using query hints because query hints solved the problem once. So query hints all over the place. Don’t use hints, especially index hints, because the optimizer knows better. Let the optimizer do its work. Also, if you use index hints, if you rename or move the index, the queries will stop working, which is not exactly what you want.
Regarding indexing, one of the things that I see all the time, DTA indexes all over the place, a suggestion from Tuning Advisor. Tuning advisor is a nice way to know that things are wrong and there is something to fix, but don’t take all the suggestions as gospel. It’s an indication that something is wrong that has to be fixed, but not necessarily in the way the Tuning Advisor suggests because it suggests very often duplicate indexes.
Another thing that I see all the time, an index for each column. Yay. So indexes all over the place. Indexes are not for free. They’re copies of the data in the column, so really, just the ones that we need. Another thing that I see very often is suboptimal clustered index. A good clustered index for OLTP is usually unique, small, unchanging, and ever increasing or decreasing, even if that creates a hotspot effect, but it’s a totally different topic. Okay, this means if you’re using a unique identifier, use new sequential ID instead of new ID.
Regarding server tuning worst practices. Throwing hardware at a problem is one of the things that gets you instant damnation. Why? Because a twice as fast machine might make RBAR code twice as fast. It really doesn’t happen. It could, it might do that, but what it can’t do is having 500 percent improvements that you can have when you change your RBAR code to set-based code. So do that instead.
One of the things that really gets you damned is the use of advanced options without testing. There is a couple of settings that really shouldn’t be so easy to change. Example: NT fibers, lightweight pooling or priority boost. Those really shouldn’t be in the instance properties window. So Microsoft, please take them away. Nobody should touch that.
There is no time to go into deep details. I have some resources for you that has detailed blogpost for each of the circles on spaghettidba.com, or really there’s not at this time, there will be at some point. There’s a few, but not all the circles up on my blog. I promise I will fill the gaps. If you want to analyze your best practices instead of worst practices, there’s a tool for you, it’s Best Practices Analyzer. This is the link to download it. If you want to contact me, you can send me an email firstname.lastname@example.org. This is the link to the session which contains also the link to the demos. Here’s a link to my series on SQL Server Infernals. So, are there any questions?
Brent Ozar: Well, you know, it was interesting, you didn’t get a lot of questions but you got so many people saying that they loved this talk, that they recognized so many of the levels of hell inside their own companies. Several people were keeping track of how many circles of hell their companies have been in.
Gianluca Sartori: So, there’s a hell Bingo.
Brent Ozar: Yes, yes. We should have had a high score tracking. Several people say they have all kinds of good things to say about your sessions. One person said their company met all of the circles. James asks, “What’s the best tool in your opinion for source control?”
Gianluca Sartori: Well, I tried a couple of them. I think the best at the moment is what we have in Visual Studio in SSDT. Because it’s a declarative definition of the database and it’s not just imperative scripts as you have in other source control tools. So I love that for that reason.
Brent Ozar: Then J.D. says, “If you’re forced to use SQL Authentication, what’s a good way to minimize the side effects? Or is there anything you can do to make it easier or better?”
Gianluca Sartori: The first thing to do is to make sure that you are using the password policies on your SQL Server logins. Another thing to do is to test your logins for obvious weak passwords such as one-on-ones or the ones such as same as user name. There’s tools that can help you do that.
Brent Ozar: Great. Well thank you so much, Gianluca, for presenting today. This is a huge hit. People loved this. Folks, if you want to look at the slide deck, you can go to groupby.org/go/session5 where you can go revisit this slide deck and pass it around with your coworkers because you’re all going to laugh at how many of the levels of hell you’re meeting right now. So, thanks, Gianluca.
Gianluca Sartori: Thanks for having me.
Brent Ozar: No, thanks for submitting. This is such a good slide deck. I knew when I saw it I was going to love it.
Latest posts by Gianluca Sartori (see all)
- SQL Server Infernals – A Beginner’s Guide to SQL Server Worst Practices - January 13, 2017