Learn the basics of Foreign Keys in SQL Server: trust, join elimination, cascading deletes, and indexing child keys. Limited on time? Check out the Table of Contents below.
https://www.youtube.com/watch?v=BkNOQgV5Zv8
You can't do everything with filtered indexes in SQL Server. For instance, you can't create the following index:
[crayon-6a6370533a627956303143/]
If you try, you'll get the error message:
[crayon-6a6370533a630004622172/]
Instead, you can use 'IN' and create the index this way:
[crayon-6a6370533a633336896024/]
That works-- and good news, even queries written with 'OR' can use that filtered index, because SQL Server is clever like that. Here's an execution plan that shows it in action.
One of the blocking scenarios I find most interesting is related to online index rebuilds. Index rebuilds are only mostly online. In order to complete they need a very high level of lock: a schema modification lock (SCH-M).
Here's one way this can become a big problem:
If you're a production database administrator responsible for backups, corruption checking, and index maintenance on SQL Server, try Ola Hallengren's free database maintenance scripts. They're better than yours (trust me), and they give you more flexibility than built-in maintenance plans.
However, the index maintenance defaults aren't good for everyone. Here's how they ship:
[crayon-6a63705343a1d566305299/]
The defaults on some of these parameters are a little tricky:
Performance tuning of independent software vendor (ISV) databases is a little tricky.
To understand who does what, let's think through all of the work required with building and hosting a database application:
A while back, we posted about how SQL Server index fragmentation doesn't matter. That blog post generated a lot of heated discussion and has served to make people aware that fragmentation may not be the source of their woes. Sometimes, though, there are data access patterns where fragmentation really does matter. Perpetual Fragmentation Some data…
Making index changes in SQL Server is tricky: you immediately want to know if the new index helped your performance and if it's being used, but SQL Server execution plans and their related statistics can be are insanely confusing.
It can be useful to run sp_recompile after you create an index, but not necessarily for the reason you might think.
Let's say you've got a vendor application that creates tables, loads them, queries them, and drops them all the time. You can't change the code.
The tables involved desperately need an index in order to go faster, and they're your biggest performance bottleneck.
You know how to design indexes, but you’re not sure how good your skills really are. In this quiz-packed session you’ll get a chance to test your skills! Kendra Little will walk you through a set of index design challenges. You’ll have time to answer each problem on your own, then find out whether the…
Do you rely on index rebuilds to make queries run faster? Or do you always feel like statistics are "bad" and are the cause of your query problems? You're not alone-- it's easy to fall into the trap of always blaming fragmentation or statistics. Learn why these two tools aren't the answer to every problem…
SQL Server 2012 introduced nonclustered columnstore indexes, but I never saw them used in the wild simply because once created, they made the underlying table read-only. Not a lot of folks like read-only tables. (Bad news, by the way - that limitation hasn't disappeared in 2014.) SQL Server 2014 brings clustered columnstore indexes, and they're…
I first knew something was up when I looked at the job history for a simple maintenance plan. It had two steps:
Rebuild all the indexes in the database - this took 10 minutes each night.
Update statistics - this took 2-3 hours each night.
SQL Server has this fancy feature called constraints. Database constraints are used to restrict the domain (the range of allowed values) of a given attribute. That’s just a funny way of saying: through a set of carefully crafted rules, we control the shape of our universe.
Our Test Table
It was a dark and stormy… Oh, wrong story. It was actually a warm, sunny afternoon in Charlotte, NC. I was presenting “Index Methods You’re Not Using” at PASS Summit. In this talk, I discussed how indexed views, filtered indexes, and compressed indexes can improve your query performance by reducing I/O.
From stage right, an intrepid audience member raised his hand and asked, “Can you think of an example of when you would use filtered indexes instead of partitioning?”
Views are logical objects in SQL Server databases that present you with a “virtual table”. Views are typically created for one of three reasons: security, simplification, or aggregation. Security: we create views so that a user can read specific columns out of certain tables, but not all the data. Simplification: sometimes, it’s easier to write…
I’ve been told that an attendee at the PASS Summit pre-conference event asked about using dynamic SQL to get around some of the problems with filtered indexes. I’m not entirely sure what the question was, but it did give me the opportunity to play around with filtered indexes and write some simple demo code to illustrate just the kind of shenanigans that you can get up to.
When you write a query, you (usually) don’t want to return all the rows from the table(s) involved – so you add a WHERE clause to the statement. This ensures that fewer rows are returned to the client - but doesn't reduce the amount of I/O done to get the results. When you create a…
Let me ask you a question: do you want to boss the SQL Server query optimizer around?
If you answered no: good. You’re willing to let the query optimizer do its job, which is to find the least expensive way to run a query, as quickly as possible.
This question came up in the pre-show chat for our weekly webcast: when you rebuild a nonclustered index offline, does it impact only the nonclustered index? Or does it impact the entire table? Showing is more fun than telling! Let's take a look. First I restore AdventureWorks to slow storage Sometimes slower storage is handy.…
Are you still rebuilding all of your indexes every week - or heaven forbid, every night - and still having performance problems? Odds are, you're not actually making things better - you're making them worse! I explain why in this video:
https://www.youtube.com/watch?v=fD1CZVc6oUk
