September 9, 2015 at 10:20 pm
Comments posted to this topic are about the item Missing Indexes
September 10, 2015 at 12:42 am
This was removed by the editor as SPAM
September 10, 2015 at 5:01 am
I was sure it returns error that there's no possibilty to prepare the plan. :ermm:
Thx
September 10, 2015 at 7:16 am
I was pretty sure it would just think there weren't any indexes.
September 10, 2015 at 7:57 am
Is there any difference when using clustered indexes instead of heaps?
September 10, 2015 at 8:26 am
Will the database be accessible if one of the files go offline?
Thanks.
September 10, 2015 at 10:56 am
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.
September 10, 2015 at 10:57 am
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.
September 10, 2015 at 11:12 am
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.
September 10, 2015 at 12:09 pm
Interesting question. I was sure it would give an error.
September 10, 2015 at 1:00 pm
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 🙂
September 10, 2015 at 1:07 pm
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.
September 10, 2015 at 1:11 pm
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!
September 10, 2015 at 3:30 pm
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!
September 11, 2015 at 1:33 am
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!
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply