3. caite.info Get Certified as a SQL Server DBA: 1. a. Microsoft Certified Technology Specialist. becoming a DBA, specializing in Microsoft SQL Server. One of the deciding .. SQL Servers instances from SQL Server to SQL Server , by the end of may prefer paper, an e-mail, a Word file, a PDF file, HTML, or they may have a. Microsoft. This product is built for the basic function of storing retrieving data as This tutorial explains some basic and advanced concepts of SQL Server such.
|Language:||English, Spanish, Dutch|
|ePub File Size:||17.33 MB|
|PDF File Size:||8.66 MB|
|Distribution:||Free* [*Regsitration Required]|
SQL Server DBA Training. Andrew Fraser, September , http:// caite.info This course is a reduced version of Microsoft‟s 5 day System. as a SQL Server DBA, but you are familiar with SQL. Server basics. . Requires SQL Server /, and Windows Server / (or higher version). A SQL Server /R2 DBA training course from caite.info · Skill level: ; · Download a PDF of the videos in this course Download PDF video.
Top tip: Throw in businesses, and let's say our table has , records in it. This book is intended for developers, DBAs, and casual users who hope to administer or may already be administering a SQL Server system and its business intelligence features, such as Integration Services. Then look at the contents of those tables - is the data being stored unencrypted? You should learn what exceptions could happen, how to trap them, and how to fail elegantly.
This chapter also describes some of the hidden tools you may not even know you have. Once you know how to manage your SQL Server, you can learn in Chapter 5 how to automate many of the redundant monitoring and maintenance tasks. Chapters 6 and 7 cover how to properly administer and automate many tasks inside of the Microsoft business intelligence products, such as Integration Services and Analysis Services. Chapter 9 explains how to secure your SQL Server from many common threats and how to create logins and users.
Chapter 10 covers how to create a SQL Server project and do proper change management in promoting your scripts through the various environments. Chapters 11 through 15 make up the performance tuning part of the book. Chapter 11 discusses how to choose the right hardware configuration for your SQL Server in order to achieve optimal performance.
After the hardware and operating system is configured, Chapter 12 shows you how to optimize your SQL Server instance for the best performance. Chapter 13 describes how to monitor your SQL Server instance for problematic issues such as blocking and locking.
Chapters 14 and 15 discuss how to optimize the T-SQL that accesses your tables and then how to index your tables appropriately. The treatment of normalization and denormalization is no more than a good introduction to the topic, but it suffices to give a new DBA an appreciation of what's happening inside the database.
The same point applies to the section on indexing. For more information on these topics, you need a book that focuses on performance and tuning. With an understanding of the internal workings of SQL Server, a DBA can step in to assist a theory-oriented developer in query optimization. The authors drop hints about updating the statistical information the query optimizer uses, an important task in a dynamic, changing database. The appendixes are a useful reference source and include a list of SQL Server's built-in stored procedures, a function reference for building stored procedures, and a list of SQL system tables.
Developers who have to install, maintain, back up, and tune database applications in the development process will also benefit. The book scores a direct hit on its target readership--strongly recommended. SQL Server. Hide comments. More information about text formats.
Text format Comments Plain text. Web page addresses and e-mail addresses turn into links automatically. Lines and paragraphs break automatically. And yes, if you've only got your spare time at work, your monitoring tool is going to be crappy. If you've got so much time that you can build a great monitoring Just when you thought it was safe to let your guard down, we throw a trick chapter at you.
If the server runs out of drive space, if the service stops, if the event log starts flling up with errors, or zombies atack, these tools will let you know. Performance Diagnostics - these tools try to derive insight from SQL Server's dynamic management views DMVs to help performance tuners wring the most throughput out of their hardware. If so, get them to start monitoring your servers.
I'm being selfsh here - my real reason is that I want to save my monitoring budget for tools in the Performance Diagnostics category.
Instead, no mater which category of tool you're buying, make a list of the last things that have caused your phone to ring afer hours. Here's some of my personal favorites: All of them provide free day trials. Reproduce the problem, and watch how the monitoring sofware reacts. Does it lead you to the root cause quickly, or does it just have a bunch of fashing red lights on the console?
The best ones will save you time by geting you right to the solution. Afer you've done your evaluation and picked a favorite, get a quote from them - and get a quote from the other vendors as well. Share the quotes with the competing salespeople. Your Homework I don't expect you to magically get budget approval for a tool this week, but I want you to start making a list of day to day problems as they strike. If you waste four hours troubleshooting a deadlock problem, make a note of the date, the time required, and a few words about the root cause.
In a month or two, you'll be able to present this list to your boss as proof of why the right monitoring tool could save you money. Down the road, I'll also link you to a video showing you how to get the budget approval for that tool. I'm not selling you vendor tools, by the way. I ofen link to vendor tools, but I don't receive any compensation. I'm just a huge believer in using the right tool to look like a hero fast - instead of building your own hammer every time you change jobs.
The lead DBA asked me, "If you were going to monitor your servers, how would you do it? He wanted me to use an open source monitoring framework. Today, several years down the road, now that I'm a Microsof Certifed Master, my answer is still the same. I don't reinvent the wheel, and neither should you. Focus on things that provide the business tremendous value, and your career will take care of itself. If your business would fnd tremendous value in a killer monitoring system, then become a developer and give it to them, or consider contributing to the huge number of open source monitoring products.
Don't build a new one from scratch, and defnitely know which metrics to monitor. What other statistical lies are lurking out there for you to fnd in Perfmon? Learn why. Rotational drives can tell you how hot the drive is, provide bad sector counts, and a host of other information about drive health. Read more in this post. Before you build or buy, check out the best free SQL Server downloads: That's kilobytes - not megabytes, not gigabytes, but just mere kilobytes.
Say that we create a table: Now see what you did? It's long again. Doggone it, it's hard teaching this stuf in an email.
Anyhoo, in this table, each record takes up just a litle bit of space. It's the same 4 bytes whether that number is 1 or 1,, If all of our employees average about 10 characters in their name, that means we could ft about records per 8KB database page.
In reality, there's some overhead because SQL Server also needs to use some parts of the page to store metadata, and we'll talk about that later in the training. Brent Ozar Unlimited is a small company, so we can keep all of our employees on a single 8KB page. As we insert, update, and delete employees, SQL Server fetches that 8KB page of disk, brings it into memory, makes the necessary changes, and then writes that data page back to disk.
Tables are cool, but as we start to dig into what SQL Server's doing under the hood, we want to start thinking about these three object types: Heap - a table with no clustered index. In my dbo. Employees table, I didn't specify in what order SQL Server should store my data, so it's just going to slap the data down on my 8KB page in any old order. Clustered Index - what we normally think of as a table.
If I'd have created my table like this: That way, when I search for EmployeeID 42, it can go directly to that number without scanning through all of my employees. The clustered index is sorted in the order of the EmployeeID feld, but it's actually the full copy of our table, including all of our felds - in this case, just EmployeeName.
This copy of my table will only include the felds specifed in my index EmployeeName , plus whatever felds it needs to get back to the clustered index in this case, my clustering key, EmployeeID. All three of these objects - heaps, clustered indexes, and nonclustered indexes - will be stored on separate sets of pages.
We won't have the clustered index and nonclustered index for the same table on the same page - they're split. That's why when we're doing space analysis, we have to think in terms of indexes, not tables. Scroll down to the examples link in that Books Online page, and you'll get queries to examine a single table - I'd strongly recommend starting with a small table, because some of the parameters for this DMF will cause SQL Server to actually look at every page in the object.
That means if all of the pages for that object aren't cached in memory, SQL Server will go pull those pages of disk, and that can slow down your running SQL Server. This DMF also includes average record size and max record size. This makes for fun spelunking: Now, don't go changing your database structure just yet - you can easily break applications when datatypes change.
Let's leave that for later. I use real database pages from the StackOverfow. Your Homework Let's start thinking about what objects are taking up space in our databases.
Is your database a hoarder, clutching on to a bunch of nonclustered indexes that aren't geting used, and are just taking up space?
The details columns in the results will show how big each index is, and whether it's geting used or not. This is important because the more indexes you have: As we start to move up the Hierarchy of Needs from capacity into performance, you'll start to see how these foundational items are so important. You pass in a database name, fle number, and page number, and SQL Server will return the nearly-raw contents of that page along with diagnostic information.
It's a fun way to get a peek under the hood. This topic is a good example of how knowing the basics of database internals can come in handy when you step back and think about database server performance. What Pages are In Memory? SQL Server uses memory for lots of things: Last section, we looked at the 8KB pages in our database.
Those pages are the same whether they're on disk or in memory - they include the database ID and the object ID, so if we looked at all of the pages in memory, we could fgure out which tables are being cached in memory right now.
The below query gives us the magic answers, but be aware that the more memory you have, the longer this will take. That might be completely okay - if you only regularly query just that amount of data - but what if we constantly need all GB, and we're constantly pulling it from disk?
I get so excited by these concepts. How fast are the cached pages changing? From the moment we read an 8KB page of disk, how long does it stay in memory before we have to fush it out of the cache to make room for something else we're reading of disk? The longer, the beter, as I explain in my Perfmon tutorial.
Do the results change based on time of day? This is a one-time snapshot of what's in memory at the moment, but it can change in You might be surprised at how litle memory is used for caching data. If you have automated processes that run a bunch of reports in a single database at 2AM, then the memory picture will look completely diferent then. Are we caching low-value data? If you mix vendor apps and in-house-writen apps on the server, you'll ofen fnd that the worst- writen application will use the most memory.
Thing is, that might not be the most important application. Unfortunately, we don't have a way of capping how much memory gets used by each database. This is why most shops prefer to run vendor applications on separate virtual machines or servers - this way, they don't hog all the memory on a SQL Server that needs to serve other applications.
Do we have enough memory? It's the safest, easiest performance tuning change you can make. If you're in a VM or running Enterprise Edition, the memory question gets a lot tougher.
Are we using memory for anything other than SQL Server? If we've got Integration Services, Analysis Services, Reporting Services, or any other applications installed on our server, these are robbing us of precious memory that we might need to cache data. Put your management tools on a virtual machine in the data center, and remote desktop into that instead. Can we reduce memory needs with indexes? If we've got a really wide table lots of felds or a really wide index, and we're not actively querying most of those felds, then we're caching a whole bunch of data we don't need.
Remember, SQL Server is caching at the page level, not at the feld level. The less felds, the more data we can pack in per page.
The more we can pack in, the more data we're caching, and the less we need to hit disk. When I tune indexes on a server I've never seen before, sys. The database with the most stuf cached here is likely to be the one that needs the most index help. I kept complaining to my SAN administrators because my storage didn't respond fast enough - my drives were taking 50ms, ms, or even ms in order to deliver data for my queries. The SAN admin kept saying, "It's okay. The SAN has a cache.
That's way less than what the SQL Server has in memory. Not gonna happen. We need to track: We will never have two records in here with the same phone number.
We have to tell our database about that by making the phone number our primary key. When we make the phone number the primary key, we're telling SQL Server that there can be no duplicate phone numbers. That means every time a record is inserted or updated in this table, SQL Server has to check to make sure nobody exists with that same phone number.
As of the year , there were about , people in Miami. Throw in businesses, and let's say our table has , records in it. That means by default, every time we insert one eensy litle record, SQL Server has to read half a million records just to make sure nobody else has the same phone number! Well, that won't work, will it? So let's organize our table in the order of phone number.
That way, when SQL Server inserts or updates records, it can quickly jump to the exact area of that phone number and determine whether or not there's any existing duplicates. It's called clustered because - well, I have no idea why it's called clustered, but the botom line is that if you could look at the actual hard drive the data was stored on, it would be stored in order of phone number.
Now we have the table organized by phone number, and if we want to fnd people by phone number, it'll be very fast. While our computer systems will usually need to grab people's data by phone number, our customers and end users ofen need to get numbers by other ways. That's where non- clustered indexes come in. In which we pretend the phone company, but instead of giving everybody unlimited calling, we just organize the data.
What Goes First? A Non-Clustered Index Our customers constantly need to fnd people's phone numbers by their name. They don't know the phone number, but they know the last name and frst name. We would create an index called the White Pages: Think about how you use the white pages: You scan through pages looking at just the leters at the top until you get close 2. When you get close, you open up the full book and jump to the right leters 3. You can quickly fnd the right single one record Now think about how you would do it without the White Pages.
Think if you only had a book with , records in it, organized by phone number. You would have to scan through all , records and check the last name and frst name felds. The database works the same way, except it's even worse! If a developer wrote a SQL query looking for the phone number, it would look like this: When you, as a human being, go through that list of , phone numbers, you would stop when you thought you found the right John Smith. The database server can't do that - if it fnds John Smith at row 15, it doesn't mater, because there might be a few John Smiths.
Whenever you do a table scan and you don't specify how many records you need, it absolutely, positively has to scan all , records no mater what. If the database has an index by last name and frst name, though, the database server can quickly jump to Smith, John and start reading.
The instant it hits Smith, Johnathan, it knows it can stop, because there's no more John Smiths. Covering Fields: Helping Indexes Help You But that's not always enough. Sometimes we have more than one John Smith, and the customer needs to know which John Smith to call. Afer all, if your name was John Smith, and the phone book didn't include your address, you'd get prety tired of answering the phone and saying, "No, you want the John Smith on Red Road. He's No, but we include it for convenience because when we DO need it, we need it bad.
And if we DON'T need it, it doesn't really hurt us much. This is called a covering index because it covers other felds that are useful. Adding the address feld to our index does make it larger. A phone book without addresses would be a litle thinner, and we could pack more on a page. We probably don't want to include the Address 2 feld, because the Address 1 feld is enough to get what we need.
When building covering indexes, the covering felds go at the end of the index. Obviously, this index would suck: That wouldn't be as fast and easy to use.
That's why the covering felds go at the end, and the names go frst - because we use those. This is more efcient than organizing the phone book by frst name then last name because there are more unique last names than frst names. There are probably more Brents in Miami than Ozars.
This is called selectivity. The last name feld is more selective than the frst name feld because it has more unique values. For lookup tables - meaning, when users need to look up a specifc record - when you've narrowed down the list of felds that you're going to use in an index, generally you put the most selective feld frst.
Indexes should almost never be set up with a non-selective feld frst, like Gender. Not that that's a bad thing - but no mater how much of a suave guy you think you are, you don't really need ALL of the women in Miami.
This is why non- selective indexes aren't all that useful on lookup tables. This rule is really important for lookup tables, but what if you aren't trying to look up a single specifc record? What if you're interested in a range of records? Well, let's look at I prefer ordering trucks of food.
Another Index When we need to fnd a dog groomer, we don't want to go shufing through the white pages looking for anything that sounds like a dog groomer. We want a list of organized by business category: We'll work with several of the records. Here, we're searching for a range of records, not just a single one. Notice that we didn't put the most selective feld frst in the index.
The feld "Business Name" is more selective than "Business Category". But we put Business Category frst because we need to work with a range of records. When you're building indexes, you not only need to know what felds are important, but you have to know how the user is fetching records.
If they need several records in a row next to each other, then it may be more helpful to arrange the records like that by carefully choosing the order of the felds in the index. Learning More About Indexes Indexes are really important, so we'll be covering these in more depth in the next two emails.
In the meantime, here's a few great resources on geting started with indexes: Our index resources page - where we've got posts and videos about heaps, indexing for deletes, partitioning, and more. Expert Performance Indexing by Jason Strate and Ted Krueger book - covers how indexes work and how to pick the right one.
Also available on Kindle. In this week's episode, we're going to spend just a paragraph or two covering the other types of indexes, starting with covering indexes. Get it? We're covering covering indexes. Oh, I kill me. Covering Indexes Covering indexes aren't actually a diferent kind of index - it's a term that is used in combination with a query and an index.
If I have this query: SQL Server doesn't have to go back to the clustered index in order to get the results I need. This means faster queries, plus less contention - it leaves the clustered index and the other nonclustered indexes free for other queries to use.
Covering indexes are most efective when you have very frequent queries that constantly read data, and they're causing blocking problems or heavy IO. Filtered Indexes Say we're a big huge online store named afer a river, and we constantly add records to our dbo. Orders table as customers place orders.
We need to query orders that haven't been processed yet, like this: Full Text Indexes Let's say we have a table called dbo. MoviePlots, and it had a Description feld where we put each movie's plots.
We know we liked this one movie where a guy was afraid of snakes, but we couldn't remember the exact table. We could write a query that says: SQL Server would have to look at every movie's description, and scroll through all of the words one character at a time looking for snakes.
Even if we index the Description feld, we're still going to have to scan every row. Full text indexes break up a text feld like Description into each word, and then stores the list of words in a separate index.