Designing, evaluating, maintaining, and troubleshooting indexes and statistics.
568 associated posts270 primary posts
In order to improve your applications and your databases, they will need to change over time. The structure of the database changes, the structure of the tables change, the data in the tables change, the application changes, the queries against the data change. Indexes that once helped performance now just bloat your database and cause extra work for inserts, updates, and deletes.
One of the many best practices for SQL Server's table partitioning feature is to create "extra" empty partitions around your data. This is explained in SQL Server Books Online in the page on altering a Partition Function: Always keep empty partitions at both ends of the partition range to guarantee that the partition split (before…
They are elusive and hard to spot in the wild, but they exist: rare index options that can enhance the performance of your queries. If you have a solid knowledge of index basics and want to add to your collection, this is the session for you! In this 30-minute video, Jes will show you how filtered indexes, compressed indexes, and indexed views work and can improve performance.
There's a secret to learning about SQL Server's table partitioning feature: you need to get your hands on some code and really play with it in a test environment.
In this tutorial, Kendra will walk you through test scripts that set up a sample table partitioning environment. These scripts demonstrate gotchas to look out for and the cool features that can dramatically change your database's performance.
I have been in love with books and libraries most of my life.
I remember some of my earliest favorites: Little House on the Prairie, Anne of Green Gables, Bridge to Terebithia, The Chronicles of Narnia. I remember feeling grown up when my sixth grade teacher handed me Gone With the Wind.
Scaling up is hard: big hardware gets expensive fast. Scaling out is equally difficult; interesting design problems creep in to scale out solutions. One of the more troublesome issues architects face when scaling out is the issue of distributing identity. It's often advantageous for object identity to be unique and portable across all database instances…
Getting started with table partitioning is difficult; there are many decisions to make after you've decided to partition data. Correctly configuring partitioning is critical to the long term performance and stability of the database from both a querying and data modification standpoint. There are three key considerations that database architects should devote time to before embarking on a partitioning implementation: the design of the partitioning key, a thorough study of querying practices, and an examination of data modification practices.
I bet you're worried about your index fragmentation.
And I bet you're wasting your time, missing the real point.
What is Index Fragmentation and How It Happens
Let's take a step back for a second and pretend that your database is a phone book organized by last name, first name.
Let’s have a chat about your indexes in SQL Server. Indexes can be confusing. They are the easiest way to boost performance of queries, but there are so many options and terms around indexes that it can be hard to keep track of them. Let’s chat about the differences between clustered and nonclustered indexes, key…
At first glance, SQL Server’s partitioning seems like it should be an easy way to solve problems inserting data into busy tables. If the writes are spread across many partitions it only makes sense that we can avoid write hot spots in SQL Server, right? The truth is much more complicated than it appears. Asking…
Let's oversimplify the bejeezus out of this complex problem. Suspend your disbelief for a second and work with me:
We have a database server hosting just one 100GB table. Sure, in reality, we've got lots of databases and lots of tables, but we're going to keep this simple. We've got a simple sales table that stores a row for each sale we've ever had. We don't have any indexes: this is just 100GB of raw data in our clustered index.
Great volumes have been written about table partitioning. It’s a complex feature and you can read for days to just understand how you might apply it. But will it improve performance for you? Table partitioning produces great benefits for some applications, but causes giant headaches for others.
How do you know if you should invest your time in table partitioning?
Eventually we all run into a situation that makes us cringe: the users want to design their own reports. If this were something as simple as giving users a color picker and some visual layout tools, that would be one thing. Most of the time it’s more complex. Business users know their problem domain very…
Here's what I've been reading lately on the SQL Server front. I'd like to thank all three authors and publishers for providing these books for review. Securing SQL Server by Denny Cherry Paperback - Kindle Denny's a jack of all trades, and a Microsoft Certified Master of SQL Server to boot. I've had the pleasure…
How do you know which indexes to create to improve your database performance? Microsoft Certified Master Kendra Little shows you how to find a missing index, decide on the best index definition, and measure the usefulness of your new index. If you know the basics of what a database index does and want to learn the smart way to design indexes, this talk is for you.
If you're only indexing to make reads faster, you need to think again. Indexes can make every operation in the database faster, even deletes. The Problem: Deletes are Very Slow I was lazily researching new development techniques one day when I received an email from a client asking why deletes could be slow. I rattled…
Building an execution plan is a lot like going shopping. Before I leave the house, I need to think about a few things: How many items do I need? I take a glance at my grocery list to see roughly how many things I'm shopping for. What stores am I going to visit? I can't…
People love to search. Google has us addicted to fast, easy search functions. Users expect every application to have a built-in blazing-fast search functionality. To pull that off, developers build search queries that let users enter a string, and we ask SQL Server to find matches. For example, say our users need to find some…
Paul Randal (Blog - @PaulRandal) nailed a blog post to the church wall entitled, "What 5 things should SQL Server get rid of?" His choices included auto-shrink, putting databases in full recovery by default, GUIDs as clustered indexes, and more. He tagged me, and I knew right away what I'd say. 5. IntelliSense restrictions on…
Crystal meth helps you lose weight and feel better about yourself. It's true. Wikipedia says so. Sure, the side effects of methamphetamine are more than just anorexia and euphoria, but if you're looking to be an upbeat, skinny person, meth gets the job done. SQL Server has a couple of similar features that might make…
