Voice of the DBA

Tag: sql server

  • Common SQL Server Mistakes – Indexing Every Column

    If one index helps speed up queries, than more indexes should help more, right? They do, but they also come at a price. Both in performance during data modifications (insert/update/delete), and in terms of space since each index must be stored somewhere.

    I have never bothered to index every column of a table. Actually I’m not sure if I’ve indexed every column of any table. Perhaps that’s because I originally came from a dBase/Clipper/Foxpro environment and I had to manage every index manually. Adding too many indexes resulted in a lot of coding.

    In SQL Server, each index is useful in two ways. When someone puts a filter in the WHERE clause, like this:

    select
      CustomerID, TerritoryID, AccountNumber
    from Sales.Customer
    where CustomerID = 10

    then an index on CustomerID will speed up this query. Instead of having to scan all rows of the table, the index on CustomerID can be searched, the correct row found. If this is a clustered index, then the data can be read. If it’s a non-clustered index, the server can retrieve the rowID and then go get the data from the clustered index without reading all the rows in the table.

    In more recent versions of SQL Server, multiple indexes can be used. For example,

    select
      CustomerID, TerritoryID, AccountNumber
    from Sales.Customer
    where CustomerID = 10
      and TerritoryID = 23

    In AdventureWorks, there are indexes on both CustomerID and TerritoryID for this table. It is possible (with lots of data), that the optimizer might choose to scan the CustomerID index for all matching rows and then the TerritoryID index for matching rows, join those results together to get a set of rows for the overall query and read the clustered index for those specific rows. However that’s not something you can count on in SQL Server. Typically one index is used in many queries.

    So why not index every column?

    First, if the columns are large, like varchar(max), text, or varbinary(max) columns, then it doesn’t make sense to build large indexes unless you often query these fields. Even then, a full-text index is likely a better choice.

    Second, each time you change data (insert/update/delete), then all indexes must be updated at the same time. This means that your write performance suffers, and that impacts the server read performance as well since resources are being used to perform those updates. The more indexes, the more work that has to be done in support of any DML statement.

    Lastly, you typically find that most of the time you query a table based on 3-5 fields, and those are the best candidates for indexes. For transactional tables, this is typically the number of indexes that you want to put on each table. Reporting tables, or OLAP type tables, might have more indexes, but these are tables that typically receive mostly read activity, and rare write activity.

    Which columns do you index? Pick those columns that often appear in your queries, and that are fairly selective. You can always query the missing index DMVs for help in choosing which indexes the optimizer things it might use.

  • Community Direction

    When Microsoft implemented Connect, I thought it was a great idea. It was a way for real users to submit bugs, and others to see those bugs, voting on them if they thought they were important. It would help Microsoft determine what features and bugs are important and perhaps allocate resources accordingly. However there was a fundamental problem with the system. People would see individual items, and could vote for them, but wouldn’t have an idea of what other items might be listed.

    The work on SQL 11 is underway, and recently I got a note from Itzik Ben-Gan asking people to vote for windowing enhancements to the T-SQL language. I’m not sure exactly of all the places that these are useful, but Itzik is one of the smartest people I know and I tend to believe that if he finds these enhancements useful, they are likely going to make T-SQL easier to work with.

    But are these items a priority? I am sure they are valuable, but are they more valuable than CREATE or REPLACE? IS it more of a priority than allowing SSMS add-ins? There are any number of enhancements that are listed, but most of us don’t have the time to dig through them all, or even try to determine how important they might be when weighed against other items.

    Microsoft can do what they want, and they need to keep one eye on the sales generated from new features. However I wish that they’d reserve a slice of their development efforts for older features and get some community help in choosing which items to work on. I’d love to see a list of the items they are considering, maybe the top 20 features, and let us add votes to pick the 10 they can work on.

    We may not sign the purchase orders, but us DBAs really like SQL Server and would appreciate improvements that make our jobs easier.

    Steve Jones

    PS: Here are Itzik’s items:

  • What Do We Need in SQL Server?

    What should be included in the next release of SQL Server? SQL Server 11 is being worked on now, and there are a number of features that I’m sure will be new, as well as some improvements to old features.

    But what should be changed/added/improved upon? Brad McGehee asked that question in his September Blog question. There are some really interesting ideas in the comments, and I hope that some of these get submitted to Connect for inclusion in SQL 11, or even SQL 12. The product will always need work, and we should all be looking forward to future versions.

    I think the product will evolve in the future, and include more Cloud-like features since SQL Azure is getting a lot of attention and it seems many vendors are looking to push for a more service oriented, cloud-based architecture. However for the foreseeable future, I’d think that the next few versions will continue with the same type of install-an-instance-on-a-server that we’ve been used to.

    If you have ideas, or want to lobby for new features, please feel free to include them in the discussion along with your reasons why they are a priority. And if they’re very important to you, please feel free to submit them on Connect and let us know the URL.

    Steve Jones

  • NoSQL Is Not Everywhere

    How many places really use NoSQL? Facebook, one of the largest sites on the Internet, has had their Cassandra database service receive  a lot of press over the last year. In pointing to them as an example, many technologists use the argument that if Facebook uses Cassandra to run their extremely data intensive business, then it ought to be good enough for the rest of us.

    Cassandra might be great for your business, but is it the best choice? How does that argument apply when Microsoft.com, also one of the largest web sites in the world, serves millions of users a day with SQL Server as the back end? Twitter also uses an RDBMS to store it’s tweets. It uses the MySQL database, an RDBMS, though they are investigating and integrating Cassandra in places.

    The point is that NoSQL databases, like .NET assemblies or J2EE applets are just technologies. They’re just tools used to build an application, and they can be built well, or built poorly. Is Cassandra bad because it didn’t work well for Digg? No, just like those companies that have switched off SQL Server to some other platform aren’t necessarily doing so because SQL Server can’t meet their needs.

    It’s up to the architect to put together a great platform for an application, using whatever tools they choose. Hopefully they are choosing those tools because the fit the problem, or the skills of the developers. I’d be disappointed in an architect that chooses something because it’s new, or discards another technology because of some personal bias.

    Steve Jones