Tagged Questions
0
votes
1answer
48 views
Can I use the database and query it while full-text indexing?
I need to index my database, but I need to query it while it is indexing, it may take a long time, can I do that?
1
vote
1answer
64 views
SQL Server clustered index high fragmentation
I've got a Table with an Integer (4bytes) as primary key. it's defined as an identity. it is also the clustered index.
Inserts are working perfectly fine. After inserting 2000 rows the fragmentation ...
5
votes
2answers
141 views
Why disabling a clustered index makes the table inaccessible?
When an index is disabled, the definition remains in the system catalog but is no longer
used.
SQL Server does not maintain the index (as data in the table changes), and the index
cannot be used to ...
0
votes
1answer
73 views
SQL Server: Sync Indexes between two tables within Same database
I need to automate and Sync only indexes between two tables ( Primary and Stage_Table) within same database.
Tried using SSIS SQL Server Objects Task, but looks like it works only when we sync between ...
3
votes
2answers
125 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
0answers
89 views
Why does CREATE INDEX … WITH ONLINE=ON block access to the table over a period of minutes?
I have an existing table:
CREATE TABLE dbo.ProofDetails
(
ProofDetailsID int NOT NULL
CONSTRAINT PK_ProofDetails
PRIMARY KEY CLUSTERED IDENTITY(1,1),
ProofID int NULL,
...
2
votes
1answer
33 views
Rebuilding Unique Index with uniqueidentifier in SQL Azure never succeeds
We have a number of tables (~1M records) that have a column on them defined as: [GlobalID] [uniqueidentifier] NOT NULL that gets auto-populated with newid(). We use this ID for synchronizing data ...
1
vote
2answers
117 views
Clustered vs Nonclustered Index
My database currently has a primary Key/Clustered index on the ID column for each table. However, the application that connects to the database is always looking at views defined as:
SELECT * FROM ...
3
votes
2answers
87 views
Reducing Log Impact During Re-Indexing
We use Ola's maintenance solution and its great.
Regardless of method for re-indexing a major friction point with IT is the amount of log generated during the weekly re-indexing process. For a 1TB ...
2
votes
1answer
57 views
Validate Primary Key and Index Selection
I have a table that will store transaction data from store sales registers, I have read quite a bit on index and key choice and below is what I have concluded is the best option but I am new to this ...
2
votes
2answers
74 views
Suspended in index rebuild
In SQL Server 2012, an Index Rebuild job is taking a very long time (up to 8 hours). However, not even one Index rebuild completed, so I stopped the index job.
In monitoring SQL task:
state = ...
5
votes
1answer
61 views
When REBUILDing indexes on SQL Server, what bearing does tempdb & LOG disk speed have?
Say I have the a data disk that is 50x faster than the LOG and tempdb (measured by Random Write speed) disk. (Don't ask why that's something we'll be fixing if needed)
I have a table that's got 19 ...
3
votes
1answer
72 views
How can I alter the fill factor of an index outside of an ALTER INDEX command?
I am using a COTS package to perform index rebuilds across my SQL Server portfolio. The package unfortunately doesn't have an option to set index fill factors globally for an individual instance or DB ...
3
votes
1answer
102 views
Why is a hash match operator in this very basic query
I'm beginning to learn some about looking at execution plans and making queries more efficient
Consider these two basic queries
select distinct pat_id, drug_class, drug_name from rx
select pat_id, ...
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 ...
2
votes
2answers
71 views
Differences Between Two Different Create Index Commands
Are there differences between these two scripts? Or would all of the extra tokens/attributes (ie: NONCLUSTERED, WITH..., etc...) for the 1st script be defaults in SQL Server 2008 for the 2nd script?
...
4
votes
2answers
184 views
What are the differences between leaf and non-leaf pages?
I've been running some index usage reports, and I'm trying to get a definition of Leaf and Non-leaf. There seem to be both Leaf and Non-leaf Inserts, Updates, Deletes, Page Merges, and Page ...
2
votes
1answer
88 views
Indexed view not being used - SQL Server [duplicate]
I've a query with multiple joins which takes long time to return data for one date. I've created an indexed view on the same. I set all the required options appropriately, while creating view and ...
4
votes
1answer
407 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 ...
1
vote
1answer
77 views
SQL Azure: Drop Constraint and Index
This may be a stupid question, but I'm not a DBA and just wanna make sure of what I'm doing before I make a mistake.
Here is my question:
When dropping Constraints, are their indexes dropped as well?
...
1
vote
1answer
209 views
Creating indexes with t-sql scrips vs rebuild indexes in maintenance plan
I’m using SQL Server 2008 and I am running several (15) scripts each day to bulk insert to tables. Each script drops the table at the beginning. I create indexes in the scripts. Some of the scripts ...
1
vote
1answer
74 views
Consolidating indexes
I have one big table that is used to generate business intelligence cube. Currently it has around 40M rows and 55 columns. A lot of the cube dimensions are generated by running 'select distinct' on a ...
-7
votes
2answers
74 views
I have table with 17 Indexes if i add 1 more index for search in two columns [closed]
I have table with 17 Indexes if i add 1 more index for search in two columns insert , update and delete queries will be slow.I dont want them to slow...what to do
4
votes
1answer
191 views
Indexing strategy when using the between operator SQL Server 2008
I have a large table ~25 million rows with the structure
CREATE TABLE [dbo].[rx](
[pat_id] [int] NOT NULL,
[fill_Date] [date] NOT NULL,
[script_End_Date] AS ...
6
votes
3answers
246 views
Clustered index in SQL Server vs index organized tables in Oracle
I am making the transition as a database developer from SQL Server to Oracle and found some fantastic resources here already (How to make a transition from SQL Server DBA to Oracle? and As a DBA, how ...
5
votes
1answer
77 views
Efficiently Filter Large Set With Disjunctions
Let's say I have a single table
CREATE TABLE Ticket (
TicketId int NOT NULL,
InsertDateTime datetime NOT NULL,
SiteId int NOT NULL,
StatusId tinyint NOT NULL,
AssignedId int NULL,
...
3
votes
1answer
124 views
Index Key Column Order
I have created a joining table for many-to-many relationship.
The table only has 2 cols in it, ticketid and groupid
typical data would be
groupid ticketid
20 56
20 87
20 ...
2
votes
0answers
70 views
What is the effect of replacing indexes with filtered (non-null value) indexes?
Our project runs a very large, very complicated database. So about a month ago, we noticed that the space used by indexed columns containing null values was getting too large. As a response to that, I ...
2
votes
1answer
237 views
SQL Server manage indexes
Is it possible to automate the managing of indexes on my database? I'm looking to see if there are any scripts or programs that can automate this process. Would it be possible to use stats generated ...
7
votes
2answers
494 views
At What Point Does Having an Index Become Efficient
I've found a lot of resources that mention that adding an index to a table makes searches faster and inserts slower, but only if the table is large. This creates a tradeoff, which is a design ...
1
vote
1answer
52 views
Is it a good idea to have an index when the column value changes several times over record lifetime?
I have a table where one of the columns represents an object state - let it be State of type int. The State value starts with 1 and changes to 7 with values in between. Over a row lifetime there're ...
1
vote
0answers
87 views
Best Practice to update columnstore indexed tables
I have a table with aprox. 22 Bn rows, for each month I have made one partition that and each partition has two data files. I created the columnstore index on this table and the performance ...
0
votes
1answer
135 views
Find fulltext indexes in SQL Server 2008
How do I find a list of tables that have a FULLTEXT INDEX applied to them on a given database?
For example, you can enable and disable a FULLTEXT INDEX like this:
ALTER FULLTEXT INDEX ON ...
3
votes
2answers
130 views
Proper Index for WHERE X <> 1 AND Y <> 1 ORDER BY Z
I have a relatively large table that is accessed in exactly one way, by exactly one query (on the read side).
The query filters the table by two columns (both bit), and sorts by a third column (an ...
-1
votes
1answer
82 views
Covering Index isn't being used and I don't know why
We have this complicated query that I'm trying to make "better" until we move it to pull from a data warehouse. I need a solution that's "good enough" for now and I think I'm about 2-3 indexes away ...
1
vote
1answer
138 views
Non-clustered Index Update not displayed in execution plan [duplicate]
Possible Duplicate:
Nonclustered Index Insert
I'm trying to work out the cost of having a nonclustered index on a particular table, but I don't see any maintenance cost in the execution ...
3
votes
1answer
599 views
Should I rebuild index after truncate/large insert?
I have a stored procedure that truncates some tables with around 1.75M rows in each, before inserting new data (based on data in other tables, calculations etc.)
Basic outline is very simple:
...
3
votes
2answers
96 views
Why is this key lookup required?
I have an admittedly-complex query that I'm optimizing and I'm stuck on the next step. I have pinpointed the bulk of my problem to one part of my query that's doing the following Key Lookup but I'm ...
3
votes
1answer
58 views
Large inserts breaking indexed queries
Very similar to Large insert/update in table causes timeout while SQL Server re-arrange indexes only I am not using SSIS with only occassional large (predominently annual) inserts from another staging ...
1
vote
1answer
309 views
SQL Server Full-Text Indexer with stoplists/stopwords
As I'm working on a graduate project (Textmining with SQL Server 2012 Semantic Search) I run into a situation where I need to post a question on this website, hoping someone can help me.
This ...
7
votes
1answer
181 views
SQL Server Index vs Statistic
What are the differences between CREATE INDEX and CREATE STATISTICS and when should I use each?
4
votes
2answers
87 views
We are running database maintenace with indexing the system, indexing fails due to locking?
We have been trying to do re-indexing in our system, there are users still using the database during the indexing. While doing index for certain tables there is failure of indexing this seems to be ...
3
votes
1answer
97 views
How to update views outside of update/insert transaction in SQL Server
Looking at SQL Server indexed views I see that these views are updated within a transaction that updates dependent tables. I wonder if there is a way around it?
There will be about 10 views with 20 ...
3
votes
1answer
279 views
Is a clustered index locked while a (clustered index) scan is in progress?
The question is pretty simple, as the title says. However, I cannot find any references to confirm or contradict this.
The problem leading me to this question is that I have a view joining several ...
2
votes
1answer
160 views
Why 2 allocation units during online index rebuild to different file group?
I'm in the process of rebuilding the clustered index of a big table to a different file group. I'm monitoring the rebuild process using sys.system_internals_allocation_units. I'm 3h in but not done ...
3
votes
1answer
208 views
Move Primary Key to Filegroup (SQL Server 2012)
How can I move a clustered primary key to a new filegroup? I already have found a possible "algorithm" but it is horribly inefficient:
Drop non-clustered indexed (requires them to be resorted and ...
2
votes
1answer
124 views
clustered and covering index ignored on delete statement. Table scan occurs
Why would SQL Server 2005 find it more efficient to perform a table scan instead of using the available clustered index on the primary key (and only the primary key)?
DISCLAIMER:
There is also a ...
5
votes
3answers
162 views
Database Indexing - Maintenance Jobs
I have created a script that runs every night to rebuild & re-organize indexes based on the fragmentation, Indexes with Fragmentation > 30% are rebuilt, Indexes with Fragmentation 10% - 30% are ...
1
vote
1answer
63 views
Partitions and Indexes, in which cases should be used
I know some knowledges:
Partitions used to achieve better performance (like described in this article)
And indexes used to increase performance in selection operations.
And as result my question: ...
2
votes
1answer
207 views
Index with multiple leaf levels
I have a three-column (int, smallint, smallint) composite clustered index with three leaf levels. My question is how and when does SQL Server create multiple leaf levels (index_level 0) for the same ...
