Missing Indexes

  • Comments posted to this topic are about the item Missing Indexes

  • This was removed by the editor as SPAM

  • I was sure it returns error that there's no possibilty to prepare the plan. :ermm:

    Thx

  • I was pretty sure it would just think there weren't any indexes.

  • Is there any difference when using clustered indexes instead of heaps?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Will the database be accessible if one of the files go offline?

    Thanks.

  • With the clustered index, the data is the index. You can't have all indexes in a separate filegroup. However you can still run a plan on a clustered index if the non clustered ones are offline.

  • SQL-DBA-01 (9/10/2015)


    Will the database be accessible if one of the files go offline?

    The filegroup is offline if any files are. If there is the primary filegroup available and online, the database is online.

  • Luis Cazares (9/10/2015)


    Is there any difference when using clustered indexes instead of heaps?

    Both a heap and a clustered index hold all the data.

    A heap in unorganized. There are just two ways to access it: scanning all rows (unordered), or looking up single rows after selecting the correct rows from a nonclustered index.

    A clustered index does have a logical order imposed. In addition to unordered scans or inividual lookups, it also supports ordered scans, or directly acccessing individual rows (or ranges of rows) based on the values in the indexed columns.

    Under very heavy insert workloads, a clustered index can become a performance bottleneck due to either page splits (if inserts are scattered) or hot spot (if inserts are all at the end).

    Under very heavy insert workloads, a heap can become a performance bottleneck due to a hot spot. Page splits will not occur in a heap.

    After even moderate update activity, a heap will start to suffer from a specific type of fragmentation due to "forwarding pointers". See my blog post "The table scan from hell" to get an idea of just jow bad this can be.

    Heaps can only be defragmented by rebuilding the table. A clustered index can be rebuilt if the fragmentation is very high, but for lower fragmentation a reorganize often suffices. (Note that reorganize is always an online action; a rebuild can optionally be an online operation on enterprise edition only, and at the cost of higher resource usage and slower execution)

    There are a few cases where a heap gives you overall better performance than a clustered index. They are the exception, and you should ensure to manage the heap very well. In other words, my rule of thumb is to always have a clustered index on every table, and to only stray from this rule if I have very good reasons.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Interesting question. I was sure it would give an error.

  • Ken Wymore (9/10/2015)


    Interesting question. I was sure it would give an error.

    Queries that already have a plan cached that uses the nonclustered index will throw an error.

    The bit of the question that specifies that the query is "new" is important for that reason πŸ™‚

  • Jacob Wilkins (9/10/2015)


    Ken Wymore (9/10/2015)


    Interesting question. I was sure it would give an error.

    Queries that already have a plan cached that uses the nonclustered index will throw an error.

    The bit of the question that specifies that the query is "new" is important for that reason πŸ™‚

    I am pretty sure that this is incorrect.

    Queries that are running while the filegroup goes belly up will fail. Queries submitted later will be recompiled because an index they use no longer is accessible.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hugo Kornelis (9/10/2015)


    Jacob Wilkins (9/10/2015)


    Ken Wymore (9/10/2015)


    Interesting question. I was sure it would give an error.

    Queries that already have a plan cached that uses the nonclustered index will throw an error.

    The bit of the question that specifies that the query is "new" is important for that reason πŸ™‚

    I am pretty sure that this is incorrect.

    Queries that are running while the filegroup goes belly up will fail. Queries submitted later will be recompiled because an index they use no longer is accessible.

    Well, I tested it multiple times in 2008 R2 and that was the behavior I saw. Perhaps that behavior is different than in other versions, but at least on my instance of 2008 R2 what I said is correct.

    I'll run the tests in other versions later to see if there was some change.

    Cheers!

  • Jacob Wilkins (9/10/2015)


    Hugo Kornelis (9/10/2015)


    Jacob Wilkins (9/10/2015)


    Ken Wymore (9/10/2015)


    Interesting question. I was sure it would give an error.

    Queries that already have a plan cached that uses the nonclustered index will throw an error.

    The bit of the question that specifies that the query is "new" is important for that reason πŸ™‚

    I am pretty sure that this is incorrect.

    Queries that are running while the filegroup goes belly up will fail. Queries submitted later will be recompiled because an index they use no longer is accessible.

    Well, I tested it multiple times in 2008 R2 and that was the behavior I saw. Perhaps that behavior is different than in other versions, but at least on my instance of 2008 R2 what I said is correct.

    I'll run the tests in other versions later to see if there was some change.

    Cheers!

    I reproduced the same behavior in 2008 R2, 2012, and 2014.

    Below is the script I used to test:

    --This script creates a database with a filegroup for the nonclustered indexes,

    --creates a heap on the primary filegroup, and adds a nonclustered index onΒ 

    --the NC_Indexes filegroup.

    --It populates the table, and then runs a SELECT 1 query that will use the nonclustered index.

    --Then the file for the NC_Indexes filegroup is taken offline. When the SELECT 1 query is run again,

    --it still tries to use the nonclustered index, and an error is raised.

    --A new SELECT query (SELECT 3 instead of SELECT 1) is run, and for it a plan scanning the heap is compiled.

    --I use EXECUTE for the SELECTs just to make sure that the exact same SELECT is run the first two times, andΒ 

    --no whitespaces or other characters are included that might lead to a new plan the second time around.

    --To recreate on your system, the paths may well have to be changed, of course.

    CREATE DATABASE OfflineFG

    ON PRIMARY

    (

    NAME=PrimaryData,

    FILENAME='E:\MSSQL\Data\OfflineFG.mdf',

    SIZE=10MB

    ),

    FILEGROUP NC_Indexes

    (

    NAME=NonClustIndexes,

    FILENAME='E:\MSSQL\Data\OfflineFGNCIndexes.ndf',

    SIZE=10MB

    )

    LOG ON

    (

    NAME=OfflineFGLog,

    FILENAME='F:\MSSQL\Logs\OfflineFGLog.ldf',

    SIZE=10MB

    );

    GO

    USE OfflineFG;

    CREATE TABLE OfflineFGTest (ID int) ON [PRIMARY];

    CREATE NONCLUSTERED INDEX NC_ID ON OfflineFGTest (ID) ON NC_Indexes;

    INSERT INTO OfflineFGTest VALUES (1),(2),(3);

    PRINT 'First execution of SELECT 1. No errors!'

    EXECUTE('SELECT 1 FROM OfflineFGTest WHERE ID=2');

    ALTER DATABASE OfflineFG MODIFY FILE (NAME=NonClustIndexes, OFFLINE)

    PRINT 'Second execution of SELECT 1, after the file is taken offline. There''s an error below this!';

    EXECUTE('SELECT 1 FROM OfflineFGTest WHERE ID=2');

    GO --This lets us continue after the error.

    PRINT 'First execution of SELECT 3, with the file offline. There''s no error here, because a plan scanning the heap is used.';

    EXECUTE('SELECT 3 FROM OfflineFGTest WHERE ID=2');

    USE master;

    DROP DATABASE OfflineFG

    --At this point, we still need to clean up the offline .ndf file from the file system, since it won't be cleaned up by the

    --DROP DATABASE.

    Below is the error all three versions throw when re-running the SELECT 1 after the file is taken offline:

    Msg 667, Level 16, State 1, Line 1

    The index "NC_ID" for table "dbo.OfflineFGTest" (RowsetId 72057594040614912) resides on a filegroup ("NC_Indexes") that cannot be accessed because it is offline, is being restored, or is defunct.

    I suppose it's possible this code isn't recreating the exact scenario you had in mind, but at any rate, this should clarify things a bit.

    Cheers!

  • Jacob Wilkins (9/10/2015)


    Well, I tested it multiple times in 2008 R2 and that was the behavior I saw. Perhaps that behavior is different than in other versions, but at least on my instance of 2008 R2 what I said is correct.

    (...)

    I reproduced the same behavior in 2008 R2, 2012, and 2014.

    Below is the script I used to test:

    Thanks for the effort, and most of all thanks for correcting me. I replied from memory, and my memory was clearly wrong. My bad!


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply