Voice of the DBA

Tag: indexing

  • The DBA Team #1–Code and Slides

    Our first DBA Team event, in Richmond, VA just before SQL Saturday #187 went well. Overall I think our experiment was a success and we’re already talking about where and when we might do this again.

    In the meantime, we didn’t make a separate site for this series of events, being an experiment and all. I’m adding this post as a placeholder for the various slide decks and code.

    Core Monitoring for SQL Server (Steve Jones)

    Good DBAs ensure that they are always aware of the state of their instances. All systems should have monitoring in place, not just so you know when things go wrong, but so you understand what a normal workload looks like and can plan for the future. This session will cover the basics of monitoring a SQL Server system and the various metrics you should be tracking.

    Getting Started with SQL Server Backup (Grant Fritchey)

    Backups are fundamental to protecting the investment your business has in its data and they’re the foundation of disaster recovery planning. We’ll go over best practices for database backups, to ensure you’re establishing that foundation correctly within your systems. This introductory level session covers full, log, and differential backups, as well as restores and restores to a point in time. Come along to be sure you’ve got the right protection in place for your systems.

    Understanding Database Corruption (Grant Fritchey)

    A DBA’s primary purpose is to ensure that the information in their charge is accessible by the correct people within their organization. Despite everything you do to make sure you’ve got your servers configured, monitored, and tuned, with well-tested backups in place, you can still lose data through corruption. But what is corruption in a database? This session lays out exactly where database corruption can come from, how to find out exactly where the corruption is within a database, and  the methods you have available to recover from database corruption.

    Indexing for SQL Server (Steve Jones)

    Indexes are important for improving the performance of your queries, but they add overhead to your server and require maintenance. This session examines how indexes work and the basic maintenance that you should perform to ensure your system is running at its peak level.

  • Dropping Indexes

    While working on some demos recently, I needed to drop an index for a test. I executed this generic statement for an index I’d just created.

       1: DROP INDEX ix_IndexName

    Needless to say I was surprised when I got this error:

    Msg 159, Level 15, State 1, Line 1

    Must specify the table name and index name for the DROP INDEX statement.

     

    I haven’t done much index maintenance in the last few years, but since I had specified the name, and I expected names to be unique, I was surprised. That’s not the case, however, since indexes aren’t seen as objects.

    I created the index in AdventureWorks with this code:

       1: -- paste in create index statement

       2: CREATE NONCLUSTERED INDEX ix_IndexName

       3: ON Sales.SalesOrderHeader ( [TerritoryID],[ShipMethodID], [SubTotal], [Freight] )

       4: INCLUDE ([SalesOrderNumber], [CustomerID]);

    As a test, I then added this index:

       1: CREATE NONCLUSTERED INDEX ix_IndexName

       2: ON Production.Product ( [Name],[ListPrice]);

    Same name, different table.

    A quick check in sys.objects surprised me.

       1: select *

       2:  from sys.objects

       3:  where name = 'ix_Indexname'

    This returned no results. Hmmm, let’s investigate further. I next decided to check sys.indexes.

       1: select *

       2:  from sys.indexes where name = 'ix_Indexname'

    This returned two results:

    indexes

    Two entries, with two object_ids. I wondered what those objects were, so I ran more code:

       1: select *

       2:   from sys.objects

       3:   where object_id in (1010102639, 1717581157)

    I received the two tables back as the objects.

    indexes2

    This surprised me, though I’m sure I’ve read the details in a book at some point, or even seen the documentation in sys.indexes. The entry for name says it is unique only within the space of the object, which would be the parent table.

    I had assumed that indexes were objects, but they aren’t. They are an attribute of an object, and as such, I needed this code to remove my index:

       1: -- cleanup

       2: DROP INDEX ix_IndexName

       3:  ON Sales.SalesOrderHeader

       4: ;

       5: GO

    Update: As noted in a few comments, you can also drop the index as:

       2: DROP INDEX Sales.SalesOrderHeader.ix_IndexName

    And, of course, I needed to drop my test object.

       1: Drop INDEX ix_IndexName

       2: ON Production.Product

       3: ;

  • Review Your Indexing

    index cards
    How often do you re-examine your indexes?

    In the latest versions of SQL Server, there are some amazing new features. Many of them allow us to expand the capabilities of SQL Server, but some are added to allow us to dive more deeply into how the system works. A couple of the newer DMVs are fantastic tools to allow us to find indexes that are unused, duplicate, or unneeded. If you’re not using sys.dm_db_index_usage_stats or sys.dm_db_missing_index_details, you should dig into a little and learn how these work. However running a diagnostic query to find unused indexes and then dropping those indexes is a bad idea. You need to ensure that those indexes aren’t rarely, or lightly used.

    I thought about this recently while giving a talk on maintenance. Indexes require routine maintenance, and many of us schedule rebuilds or reorganizes in our databases to ensure that fragmentation doesn’t become an issue. That’s a good start, but there’s more you can do.

    Every month or two you should schedule time to analyze your indexes. Capture a workload from a Trace and analyze it with the Database Tuning Advisor. Take the results and compare them to your current indexing schema. Make a judgement or two on which indexes are used by different queries and spend a few hours testing changes to your systems. You might need new indexes, you might want to remove old indexes that aren’t being used, or you might decide to add a column or include to an existing index.

    There are lots of articles on SQLServerCentral and blogs on indexing that can help you learn more about what changes might improve performance, but ultimately you will really need to test any changes on your own systems. With a little practice, you can build a short routine that allows you to take a few hours every month and analyze a few indexing changes, perform a little testing, and perhaps greatly improve the performance of your applications.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Trace Flag 2371 and Statistics

    One of the issues that I see published often on forums like SQLServerCentral is the advice to update statistics on your tables if you have strange performance issues, or sudden changes in performance. Statistics are important for the query optimizer, and you should understand the basics of how they work.

    However there’s a problem with statistics. They get out of date. SQL Server will automatically update statistics, but it doesn’t do this constantly. It does it after 20% of the table changes (by default). If you have 1000 rows, that means 200 rows changed (or added) can trigger the update. If your table has 50 changes every couple days, you’ll get statistics updated every week.

    If you have 1mm rows (think large, historical data here), then those same 50 changes won’t trigger statistics updates for a long time. 200,000 changes will be needed then.

    There’s a trace flag, 2371, that can help with the minimum needed to trigger a stats update (or you can do this with your own jobs). By choosing this, you can lower the minimum for triggering an update.

    What you do really depends on your issues. If you find poor performance in queries, look for wildly incorrect estimates of rows in your query plans. If you find that your statistics aren’t being updated, or not updated enough, you might enable the trace flag, or create your own job to update statistics manually.

    Note that if you are rebuilding indexes, you don’t need to also update statistics on the columns in the index. They are done as part of the index rebuild.