Tagged Questions

7
votes
4answers
294 views

Index Seek vs Index Scan

Looking at an execution plan of a slow running query and I noticed that some of the nodes are index seek and some of them are index scan. What is the difference between and index seek and an index ...
2
votes
1answer
60 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 ...
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
177 views

Different results rebuilding an index online and offline

I have a non-clustered, non-unique index on a foreign key column of type bigint. When I rebuild the index online, the average fragmentation drops to 3%, with 2 fragments, and 30 pages. When I run ...
2
votes
1answer
121 views

Does rowlocks on index cause keylocks? : Transaction (Process ID xy) was deadlocked, example included

I am currently experiencing the error (logged via traceID 1222 ): keylock hobtid= 8205698989 objectname=mydb.dbo.Orders indexname = _dta_Index_Orders_5_120345_K1_K2_7_1 id=lock43fe181 mode=S ...
1
vote
1answer
63 views

Possible to query all SQL Server Indexes which have page locks enabled?

Is it possible to run a SQL Server Query in order to view all SQL Server Indexes which have page locks enabled?
2
votes
2answers
157 views

Defrag a HEAP by creating clustered index and immediately dropping it

I'm writing a script which intends to defrag a HEAP based table by creating a dummy col with a clustered index and then immediately dropping it. (It's someone else's app and I don't want to make any ...
2
votes
1answer
125 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
287 views

Query performance degrades with time and use

I have been fighting a problem for several weeks now where the performance of my SQL Server queries degrade over a few days of use. In addition every few days, a query will simply not return from my ...
4
votes
2answers
252 views

Why index REBUILD does not reduce index fragmentatation with a page count > 1000?

This is kind of a followup to the existing question on this site "Why index REBUILD does not reduce index fragmentatation?". The accepted answer to that question, and all other resources I have found ...
5
votes
3answers
389 views

Does having an index on a VARCHAR column with a lot of very similar starting values have bad performance

We seem to be having quite unusual bad performance on queries that use an index. for example the table looks like PK BIGINT ID VARCHAR(50) Col1 Col2 etc So we need to insert a row in the ...
4
votes
4answers
187 views

Can I reorganise index on a sql server 2005 database without performance hit on production server

I would have preferred to run online index rebuild instead but our production sql server (2005) is standard edition. Hence I think I am left with reorganising index and update statistics after that. ...
5
votes
2answers
125 views

Unique Contraint/Index Based On Values

I have a table that has the following column definitions: ID (INT, PK) Name (VarChar) Active (Bit) Bunch_of (Other_columns) Question: I want to have a Constraint on Name/Active such that we can ...
4
votes
1answer
278 views

Index defrag takes forever to run

We have an Agent job that runs every morning at 2a.m. to defrag the top 5 biggest tables. Usually it takes around 20 mins to complete. It has stayed this way for several years. However, recently (last ...
0
votes
2answers
119 views

How to move all non-unique and non-clustered indexes to a different filegroup?

I want to move all indexes to a separate hard drive. To that effect, I created a new filegroup, then added a new file to it (e.g. alter database foo add file(...) to FileGroup). Now I want to move ...
2
votes
1answer
522 views

Is Index causing timeouts?

Recently, while fine tuning the db for better perf, I changed a couple of indexes on tables which were hit very often. I am using the following query to keep tab on index stats SELECT DISTINCT ...
1
vote
1answer
451 views

Index Strategies on Text or NVARCHAR(MAX) Fields

I have the following query (simplified for question) I'm trying to speed up for a read only DB ... SELECT [sysid] ,[Date]=CONVERT(CHAR, DATEADD(D, [date], '1800-12-28'),101) ,[From]=[from_addr] ...
2
votes
1answer
296 views

Maintenance Plan Takes Too Long - Locks Tables - Indexing To Blame

System: I have a Maintenance Plan that Rebuilds Indexes for Several Tables (10 tables, 50M records total). Issue: During the Index Rebuild (~20 minutes), we fail to insert data into the DB ...
2
votes
2answers
156 views

Does the number of indexes effect the modification of table?

First of all Good morning ! Today I got one question regarding indexes and tables. Actually I am not facing problem regarding this but due to curiosity I want to post !! Now come to the point, ...
2
votes
2answers
112 views

Space Consumption for SQL Index Rebuild

I am attempting to rebuild the indexes for a single table on a SQL Server 2005 reporting database. The indexes are heavily fragmented as this has not been done for a long time. I am running the ...
3
votes
1answer
100 views

Do operations on indexed views happen in parallel?

When I'm inserting into a table that has a view with a clustered index, the estimated execution plan appears to do the table insert and the view's clustered index insert operations in parallel. Is ...
3
votes
2answers
236 views

Performance impact of having gaps in Identity Column in SQL Server 2005

We have a number of tables in our database with an identity column as the primary key & clustered index. Over the years records have been added and deleted from these tables and of course there ...
2
votes
1answer
224 views

Online Re-Indexing In SQL Server 2005/2008 Standard

The Story: Once a week, we run a special Maintenance Plan that involves re-indexing 2 critical tables that have ~6 Million records between the two of them. We use SQL Server 2005 Standard Edition. ...
3
votes
1answer
302 views

Index fragmentation increase as more row being deleted

I have a table that contains more than 18 million records. I have a process that clears data out of that table everyday. Index fragmentation was low. This table has high transaction throughput. ...
2
votes
0answers
77 views

MS-SQL index not using all the available columns as seek predicates

I have a sql query on a large table (>17 million rows) that some times does not populate all the predicates before performing index seek, which results in not filtering out enough rows and slowing the ...
9
votes
4answers
260 views

Determining that Indexes on a table are unused

I’ve been running this script to try to find extraneous indexes select o.name as TableName, i.name as IndexName, p.reserved_page_count * 8.0 / 1024 as SpaceInMB, s.* from ...
1
vote
1answer
583 views

What I can do to speed up Key Lookup in my Query

I am using SQL2005 and against them I calling query simila to this declare @art table (id int primary key, naziv varchar(35) null, sifra varchar(15) null, jm int null); insert ...
1
vote
3answers
44 views

SQL Server - Index Including PK

Lets assume we have a table as follows: PK -- nonclustered primary key Col1 -- unique clustered Col2 Col3 Is this index: nonclustered index on (Col2, Col3) any different from this index: ...
2
votes
2answers
309 views

Check Index Fragementation Query

I'm completely stumped. I'm a relatively new SQL Server DBA so I'm at a loss with the following situation; hoping maybe someone had an idea. I have a new production SQL Server (Windows Server ...
2
votes
2answers
84 views

Index issue on order by column while applying Union all

I have two tables with 6 columns in 1st table and 6 in the second one. I have to combine the result of these two tables in order to make them in one set. Below are the structures of my tables: CREATE ...
1
vote
3answers
563 views

Hard and Fast rule for include columns in index

Is there any hard and fast rule to decide what columns and in which order it should be put in Included in non clustered index. I was just reading this post Why use the INCLUDE clause when creating an ...
4
votes
1answer
4k views

SQL Server: index creation date

In SQL Server 2005 and above, how can I find when an index was created?
4
votes
2answers
2k views

SQL Server 2005 Index Rebuild/Reorganize

I have checked my huge db and run some shrink tasks for 5 or 7 times. I got about 11 GB freespace. This DB is used for offline reporting operations. Before, when we create a report it was growing ...
3
votes
1answer
558 views

Which fillfactor is used by ALTER INDEX REBUILD?

From technet I got the impression, that I either have to explicitly set the wanted fillfactor or when I do not specify it, the default fillfactor 0 is choosen. For tables I know to get rows added ...
11
votes
2answers
812 views

Is there any tangible difference between a unique clustered index and a clustered primary key?

I understand that there may be a difference in meaning or intent between the two, but are there any behavioral or performance differences between a clustered primary key and a clustered unique index?
8
votes
2answers
681 views

Possible to run two DBCC INDEXDEFRAG commands simultaneously, each on a different table?

I am currently running a script which performs a DBCC INDEXDEFRAG on every table in a SQL Server 2005 database, one table at a time. Using DBCC DBREINDEX instead of INDEXDEFRAG is not an option, due ...
8
votes
2answers
420 views

SQL 2005: Can we determine how much the rebuild index maintenance job can grow database log files?

On SQL server 2005, if all the databases are in Full mode (with hourly transaction log backups), is it possible to determine if rebuilding all indexes of a database can grow log file of a database? ...