{"id":89706,"date":"2021-01-28T17:19:26","date_gmt":"2021-01-28T17:19:26","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=89706"},"modified":"2022-04-24T20:58:09","modified_gmt":"2022-04-24T20:58:09","slug":"dba-training-sql-server-hood","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/dba-training-sql-server-hood\/","title":{"rendered":"DBA in training: SQL Server under the hood"},"content":{"rendered":"

The series so far:<\/strong><\/p>\n

    \n
  1. DBA in training: So, you want to be a DBA...<\/a><\/li>\n
  2. DBA in training: Preparing for interviews<\/a><\/li>\n
  3. DBA in training: Know your environment(s)<\/a><\/li>\n
  4. DBA in training: Security<\/a><\/li>\n
  5. DBA in training: Backups, SLAs, and restore strategies<\/a><\/li>\n
  6. DBA in training: Know your server\u2019s limits<\/a>\u00a0<\/li>\n
  7. DBA in training: SQL Server under the hood<\/a><\/li>\n
  8. DBA in training: SQL Server high availability options<\/a><\/li>\n<\/ol>\n\n

    You are now more familiar with your environment, how to secure it, and how to keep it from breaking. It\u2019s time to begin delving into the weeds and learn more about SQL Server under the hood. (Well, sort of. You can spend a lifetime learning internals). This article will deal with basic internals, indexing, tuning, and sins against SQL Server, which should be avoided.<\/p>\n

    SQL Server \u2013 the house that Microsoft built<\/h2>\n

    To get an idea of how SQL Server works, let\u2019s start at the bottom and work our way up. Here is a 10,000-mile view to help us get started:<\/p>\n

    \"SQL<\/p>\n

    The basic anatomy of a SQL Server instance<\/h2>\n

    <\/p>\n

    The more rows of data that can fit on a page, the better performance will be for everything up the tree. This is the reason that you need to look at what kind of data types are making up your tables \u2013 even a small difference might not end up scaling. We\u2019ll go more into that point when we start talking about tables.<\/p>\n

    So, a query walks into the parser\u2026.<\/h2>\n

    \u2026.and SQL Server will see if it parses or not. If it does parse, it is sent off to the Query Optimizer. The Query Optimizer sees if it already has a plan that will work for the query. If it does, it uses it. If not, it will look through the options until it finds a good enough plan (not necessarily the best<\/em> one!). To do that, it looks through table structures and indexes; it combs through statistics to figure out what (if any) indexes can be used, and if they can, will it be a quick seek or a row-by-agonizing-row proposition. It coordinates with the Query Execution layer. Meanwhile, waaaaay down the stack, the Engine is reading pages into memory (if they aren\u2019t already there), locking tables or rows of data (if needed), and generally carrying out the nuts and bolts that make the query happen. The protocol layer takes the results and translates them back into a language that the end-user can understand.<\/p>\n

    This, of course, is the 10,000 mile-high view of it, but you get the idea.<\/p>\n

    The DMVs and system objects<\/h2>\n

    There are many built-in ways to find out what\u2019s going on under-the-hood: DMVs (dynamic management views) and other system objects.<\/p>\n

    Dynamic Management Objects<\/h3>\n

    The Dynamic Management Objects are a set of views and functions supplied by Microsoft to help you look inside SQL Server. They need a bit of manipulation to present the data in an immediately useable way. Glenn Berry has done the work for you. Unless you just love a challenge, don\u2019t reinvent the wheel. You can find his diagnostic queries here<\/a>, or if you like PowerShell, DBATools<\/a> will run his queries and even export them to Excel for you. They are the source of invaluable information on your servers, and I have used them countless times when trying to troubleshoot issues or to baseline metrics.<\/p>\n

    INFORMATION_SCHEMA views<\/h3>\n

    Microsoft also gives you something called the INFORMATION_SCHEMA<\/a> views. These are a fast and easy way to get the answer when you have questions such as what table has what column, or how to find occurrences of a string<\/a> anywhere on a server instance. It can also give you information on column-level permissions, constraint column usage, and more.<\/p>\n

    System functions<\/h3>\n

    SQL Server comes equipped with a set of useful functions<\/a> that are good to know. Some examples include the invaluable GETDATE()<\/code>, which returns the current date as a datetime:<\/p>\n

    <\/p>\n

    They may also include @@ROWCOUNT<\/code>, which does what you would expect: it returns the number of rows affected by your last code statement. In an admittedly contrived example, say you work for AdventureWorks and want to liquidate some inventory. You\u2019re considering the possibility of having a sale in a particular area, but it won\u2019t make sense to do so for a population under 200 people. You could use @@ROWCOUNT<\/code> in your decision logic:<\/p>\n

    <\/p>\n

    If you think your statement will return a huge number of rows (over two billion), ROWCOUNT_BIG()<\/code> will be the function to use instead. I have used all of these system function examples more often than I can count. You likely will as well, or you will find others more useful to you.<\/p>\n

    Note: You may have noticed the system functions are not consistently written: some of them are prefaced with \u201c@@\u201d, and some are appended with \u201c()\u201d, so be careful to check which is which when you are using them.<\/em><\/p>\n

    System stored procedures<\/h3>\n

    Like the system functions, the system stored procedures<\/a> are Microsoft\u2019s internal stored procedures. There are a metric ton of them \u2013 so many that Microsoft lists them by category rather than listing them separately. In SSMS, you can find them by going to the Programmability tab, then expanding the Stored Procedures tab, then expanding the System Stored Procedures tab. Most are for internal processes and shouldn\u2019t be used unless you know what you are doing. Some examples that you can use include sp_helpdb<\/code>, which will give you basic information about all your databases:<\/p>\n

    \"image.png\"<\/p>\n

    Or, just more detailed information on one database in particular:<\/p>\n

    \"image.png\"<\/p>\n

    Another example would be sp_helpserver<\/code>, which will give you info on your server or linked servers on your instance. If you are a new DBA, stick to the sp_help<\/code> procedures for now.<\/p>\n

    Databases in SQL Server<\/h2>\n

    So, we\u2019ve taken the server-level, 10,000 mile-high view of SQL Server. Let\u2019s begin talking about databases.<\/p>\n

    There are two types of databases in SQL Server: system databases and user databases.<\/p>\n

    There are five system databases that you will typically see in a SQL Server instance:<\/p>\n

      \n
    1. master \u2013 this is where you\u2019ll find all the basic information of your SQL Server instance, including the logins, databases, settings \u2013 everything. SQL Server doesn\u2019t start without this database, so it is paramount that this (as well as all of the other system databases except resource and tempdb) are regularly backed up.<\/li>\n
    2. model \u2013 this is, yes, the \u201cmodel\u201d for what all your other databases will look like. Whatever you do here happens to any new database made afterward.<\/li>\n
    3. msdb \u2013 this is where all the SQL Agent job information is located. It\u2019s also the home of database mail and service broker and the backup history. Jeremiah Peschka suggests limiting the amount of backup history you keep<\/a>, but be very careful with this. Know what the business expectations are for backup history retention, and never assume that is your decision to make \u2013 it isn\u2019t.<\/li>\n
    4. tempdb \u2013 this is SQL Server\u2019s scratchpad, where temp tables are made and discarded, where versioning is used for some of the isolation levels, and where query processing goes to do its work (and to spill data from badly performing queries). It is remade every time SQL Server reboots. You want to give tempdb plenty of room to work, and of course, it is the only database (along with the Resource database) you do not back up.<\/li>\n
    5. Resource \u2013 this database will be invisible to you unless you know some special queries to look at it. This is where the system objects are kept. It is best to let it do its job and leave it alone.<\/li>\n
    6. Distribution \u2013 you will see this special system database on the distribution server instance if you use replication.<\/li>\n<\/ol>\n

       <\/p>\n

      User databases are just what they sound like \u2013 databases that were created by SQL Server users.<\/p>\n

      Filegroups<\/h2>\n

      Data files are grouped into filegroups on databases. This allows for better organization and efficiency. There is always one filegroup \u2013 PRIMARY<\/code> \u2013 that houses the main data file, or .mdf. But you can have as many as are needed. Jess Borland does a great job of discussing filegroups here<\/a>.<\/p>\n

      Why would you have more than one filegroup? Aside from the obvious organizational benefits, if you have multiple filegroups, you can leverage piecemeal or partial restores in an emergency. Paul Randal goes into this in his \u201cAdvanced Corruption Recovery Techniques\u201d course on Pluralsight. It\u2019s highly recommended that you listen to his course \u201cSQL Server: Detecting and Correcting Database Corruption\u201d first.<\/p>\n

      Tables, data types, and referential integrity<\/h2>\n

      Just like there are system and user databases, there are system and user tables.<\/p>\n