Tagged Questions
39
votes
6answers
8k views
How to determine if an Index is required or necessary
I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now ...
8
votes
2answers
2k views
How to know when/if I have too many indexes?
Running Microsoft SQL Server Profiler every now and then, it suggests me with a bunch of new indexes and statistics to create ("...97% estimated improvement...").
From my understanding every added ...
16
votes
3answers
4k views
When should I use a unique constraint instead of a unique index?
When I want a column to have distinct values, I can either use a constraint
create table t1(
id int primary key,
code varchar(10) unique NULL
);
go
or I can use a unique index
create table t2(
id ...
8
votes
1answer
292 views
Behavior of data in indexes based on fill factor
Let's say you have a database where the default fill factor is 20. Whenever data is inserted, does it only create pages filled up to 20%?
From my understanding, when the data is inserted there ...
7
votes
2answers
2k views
SQL Server 2008 - Partitioning and Clustered Indexes
So let me preface by saying I do not have total control over my db design, so a lot of the aspects of the current system cannot be changed for the purposes of this scenario.
Comments about how we ...
9
votes
1answer
687 views
What's the anatomy of a columnstore index?
One of the new features in SQL Server 2012 codenamed Denali is the Columnstore index.
I know a good bit about regular old row-store indexes, like the b-tree structure, differences in storage between ...
5
votes
2answers
746 views
Why index REBUILD does not reduce index fragmentatation?
I have used ALTER INDEX REBUILD to remove index fragmentation. In some cases REBUILD does not seem to remove this fragmentation. What are the reasons why REBUILD does not remove fragmentation? It ...
3
votes
2answers
124 views
SQl server indexes file damaged, was on ramdisk!
I have put some of my indexes into a file-group that contains one file, that file is on the ramdisk, the performance goes fire!! but the problem is that the file has deleted by wrong (The file ...
2
votes
2answers
483 views
IN Clause causes Execution plan to change from Nested Loops to Hash Match
I'm tuning a query and have discovered some behaviour I'm not clear about.
If I remove the WHERE IN clause the query runs in 3 seconds instead of 3 minutes.
There only 7 rows returned in the ...
8
votes
5answers
2k views
When should nonclustered indexes be stored on separate filegroups?
I have heard that storing indexes on a different filegroup and drive increases performance in a database because the drive doesn't have to go back and forth between the index and the data to which the ...
6
votes
2answers
547 views
Reindex Task Failing Randomly
We use the built-in maintenance plan to reindex (and backup, etc.) our production SQL Server. The problem we are currently having is the reindex task started failing randomly this week after months ...
4
votes
1answer
400 views
SQL Server 2008 query planner failing after index drop & recreate
Recently we ran a script to our production DB that would dynamically drop and recreate hundreds of indexes as filtered indexes. While this script had run perfectly in all other previous tests, now ...
4
votes
1answer
501 views
What is Sql Server Clustered Index in Oracle terminology
A clustered index in sql server like a dictionary or telephone directory. A,B,C,D it goes on.
If you look up with a name Ozgur you start O then z etc.
I would like a simple explaination for this ...
2
votes
3answers
82 views
Programmatically find indexes that cannot be rebuilt online
I am automating rebuild and reorganise indexes using T-SQL. I run into problems with indexes that cannot be rebuilt online. Primarily this happens because ntext/nvarchar columns are included.
Is ...
