All Questions

Tagged with
Filter by
Sorted by
Tagged with
8
votes
3answers
17k views

Tools for Identifying Needed Indexes

I want to create the best indexes for each table in my database. Is there a query or tool in SQL Server to help with this process?
0
votes
1answer
83 views

SQL Server - moved index behind PK constraint to different filegroup, but space not freed

I have a large table Foo. There are 4 filegroups (FG1, FG2, FG3, FG4) FG2 is on a disk that is nearly full. It has 2 indexes: a) clustered index (on Date) b) a unique non-clustered index (on ID) ...
6
votes
1answer
418 views

Landed as BI, but databases are a big WTF, what to do?

Maybe a duplicate, but I believe my case is a bit different. From one of the answers I got to this post on SQL Server Central that also comes handy too but is not quite the same scenario: 9 Things to ...
0
votes
2answers
1k views

Sync Indexes between two tables

I need to automate and Sync only indexes between two tables ( Primary and Stage_Table) within the same database. I tried using SSIS SQL Server Objects Task, but looks like it works only when we sync ...
1
vote
1answer
168 views

SQl code performance

What measurement do you know if this sql code is efficient based on execution plans? How do you know if this sql code is efficient based on execution plans? SELECT [CustomerID], [CompanyName], [City],...
1
vote
3answers
3k views

Query to see Index Fragmentation on all databases without using SP or temp tables

I am trying to compile a index fragmentation report on all databases, in a multi-server environment. The query will be executed via an openquery command, so I would like to make it as simple as ...
11
votes
2answers
5k views

Is there a way to force an index to stay in memory with SQL Server 2008?

I've got a table with several millions rows, from which I need to run some queries from time to time. First query will usually be quite slow (around 10s), and subsequent queries are usually way faster ...
6
votes
1answer
2k views

Convert clustered primary key to non-clustered and use another column for the clustered index

I have a large table with a primary key (GUID) that is also the clustered index. There is already a integer sequence based field. So I want to leave the GUID as the PK and make the integer column the ...
3
votes
4answers
1k views

How does the database decide which Index to use

DDL create table t ( id int, id1 int ) create index Example_Index On t(id,id1) create index Example1_Index On t(id1,id) DML insert into t(id, id1)values(1, 100) insert into t(id, id1)...
6
votes
1answer
3k views

How much of a view is persisted when you create an index?

Say I have a view defined by the following SQL: SELECT t1.id, t2.name, t3.address FROM Table1 as t1 INNER JOIN Table2 t2 ON t1.id = t2.tID INNER JOIN Table3 t3 ON t1.id = t3.tID From what I ...
4
votes
1answer
7k views

Ola Hallengren Index Optimise Script not Rebuilding Indexes [closed]

I have downloaded Ola Hallengren's script and deployed to master database. I run it using the below... EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @...
0
votes
1answer
105 views

Questions on database table and index partitioning in SQL Server

I have developed Transact-SQL script to partition the table. To apply the partition, I am creating one non-clustered index for my partition key column and associating it to partition scheme. I have ...
4
votes
2answers
11k 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 [dbo].[...
0
votes
1answer
62 views

How can I get SQL to do the sensible thing without resorting to a query hint?

I have two tables, Items and Grouping. Items has about 7 million rows whereas Grouping only has 300,000. Of the two conditions on my query, the 'human obvious' thing is to evaluate G.StatusID IN (1,2)...
9
votes
3answers
3k views

Is there an advantage to not partition aligning an index?

I have the privilege of managing a large partitioned OLAP table. While reviewing this table I noticed one of the indexes is not aligned with the partitioning scheme. Since the author is not ...
0
votes
1answer
298 views

Index Reorganize During Database Full Backup

Index maintenance usually starts 3 hours before the database full backup and ends before the full backup start. The database is using the Simple Recovery model. Last weekend the index maintenance ...
0
votes
1answer
539 views

Adding Time Limit parameter to Ola hallengren Index Maintenance

I would like to limit the duration of the index maintenance procedure of Ola Hallengren to be 7200 seconds (2 Hours) I found parameter TimeLimit defined as: Set the time, in seconds, after which no ...
0
votes
2answers
751 views

Reorganize and Rebuild Indexes with active connections

I want to rebuild or reorganize indexes in a table. What happens if there are active connections to that table at the moment?
3
votes
2answers
4k views

Update performance: clustered versus covering index

I have a simple, 3-column table with about 50 million rows in it. This table gets about 5,000 inserts/updates per second, and perhaps 20 queries per second are executed against the table. The table ...
8
votes
3answers
1k views

How to create clustered index on 100 GB table

I have a heap table that takes about 104 GB of disk space with almost 3 billion rows. I am trying to create a clustered index on this table on the [WeekEndingDate] column. I have about 200 gb's free ...
6
votes
4answers
447 views

SELECT * WHERE VarCharColumn IN (…) Optimization

I have a list of 3000 strings, and I am passing them (twenty at a time) into a parametrized IN clause. It definitely isn't getting the results I would like to see ~ 500ms per execution. The column is ...
17
votes
2answers
10k views

Central stored procedure to execute in calling database context

I am working on a customized maintenance solution using the sys.dm_db_index_physical_stats view. I currently have it being referenced from a stored procedure. Now when that stored procedure runs on ...
0
votes
2answers
91 views

Performance issue in Sql Server

I have a master product table as like below : CREATE TABLE dbo.[products]( [id] INT NOT NULL IDENTITY(1, 1), [product_code] VARCHAR(100) NOT NULL UNIQUE, [price] FLOAT NOT NULL, [...
4
votes
1answer
587 views

Reindexing / rebuild Indexes on Linked Server tables

I am trying to run the below query to rebuild the index on a table on the Linked server, but it is not working. Can any one please let me know what I am doing wrong? ALTER INDEX ALL ON [...
9
votes
2answers
1k views

How much “Padding” do I put on my indexes?

How do you know how much "Padding" an index should have?
1
vote
1answer
551 views

Index with high fragmentation percentage

I have a very large table with enormous percentage of index fragmentation( almost 98 % !). Obviously, it needs index rebuild. Main problem is my SQL Server is in Standard edition, thats why I cannot ...
1
vote
1answer
500 views

'Locking' will occur when I create more than 5-6 non-clustered indexes?

When I create more than 5 to 6 non-clustered indexes on a single table in SQL Server it will cause a locking problem. Is this right or wrong? And if your answer is "right" and my actual requirement ...
2
votes
0answers
207 views

Is the average row size listed in Management Studio's index properties accurate?

I just inherited some SQL Server 2008 R2 databases and have reason to be reviewing the indexes on some tables. For a table with two columns such as: CREATE TABLE My_Table ( Obj_Id int NOT NULL, ...
0
votes
2answers
55 views

Which indices should I create?

Given this table: CREATE TABLE [dbo].[visit]( [visit_id] [uniqueidentifier] NOT NULL, [group_id] [uniqueidentifier] NOT NULL, [visitor_company] [nvarchar](50) NOT NULL, -- Other columns ...
0
votes
1answer
1k views

Effect of Reorganizing and Rebuilding an index [closed]

What are the effects of Index Rebuilding and Reorganizing on clustered and non-clustered indexes. How does these 2 operations affect the logical storage and physical storage of data in Hard disk?
10
votes
1answer
2k views

MSG 666 when running an insert query on 80M-row indexed table

Strangely, my stored procedure started to receive Msg 666 for some input data. Stored procedure fails on the last step when it tries to insert a row into a table with the following structure: ...
7
votes
1answer
2k views

Two columns with and without index in where clause - How index works

I am executing a query on SQL Server 2008: SELECT col1 FROM table1 WHERE col2=val2 AND col3=val3 Here col2 has a non-clustered index, col1 is the PRIMARY KEY, and col3 does not have any index. Query ...
3
votes
1answer
114 views

Aggregation GROUP BY bottleneck

I have a performance bottleneck with a SELECT GROUP BY operation. Schema I have a table like this: CREATE TABLE [InverterData]( [InverterID] [bigint] NOT NULL, [TimeStamp] [datetime] NOT NULL, ...
-1
votes
1answer
320 views

Verify this TSQL to find the indexes fragmented over 50%

select OBJECT_SCHEMA_NAME(IPS.object_id) 'schema', OBJECT_NAME(IPS.object_id) as table_name,index_type_desc, round(avg_fragmentation_in_percent,2) 'avg_fragmentation_in_percent', si.name ...
2
votes
2answers
85 views

Best index for this scenario

There is a table in our database tblCustomText( ParentID int NOT NULL, CustomFieldID int NOT NULL, Value char(50) NOT NULL, Log_CreateDate datetime NOT NULL ) (ParentID+CustomFieldID) is ...
2
votes
1answer
188 views

Will a primary key be added as a clustered index?

I've inherited a database where no primary keys were defined on the tables. There are also no clustered indexes assigned to the tables. If I perform an alter table to assign a primary key will this ...
1
vote
0answers
27 views

Index comparison

On my Production server (SQL2008) I have a query that returns suggestions of indexes. SELECT * FROM sys.dm_db_missing_index_groups g INNER JOIN sys.dm_db_missing_index_group_stats gs on gs....
1
vote
1answer
497 views

dm_db_index_physical_stats results inconsistent with actual object properties - what may be the reasons?

SQL Server 2008 RTM (10.0.1600.22) Running a typical index fragmentation report on a table: SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName, i.name AS IndexName, s.index_type_desc, ...
3
votes
2answers
622 views

What to do with a table with too many indexes?

I'm the new DBA for this company. I'm seeing some of their tables have a LOT of indexes; for instance some have over 50 or 60. Is this a good thing? From my research, it seems this is not good for ...
11
votes
1answer
1k views

How to set up indexed view when SELECTing TOP 1 with ORDER BY from different tables

I am struggling to setup an indexed view in the following scenario so that the following query performs without two clustered index scans. Whenever I create an index view for this query and then use ...
5
votes
2answers
882 views

If I update a column record in a table, will indexes that do NOT have this column in it be affected?

In terms of performance if I have a table like so: CREATE TABLE [TESTDATA].[TableA]( [Col1] [nchar](5) NOT NULL, [Col2] [nchar](2) NULL, [Col3] [float] NULL CONSTRAINT [TableA_PK] PRIMARY ...
1
vote
0answers
145 views

Database fragmentation and heap SQL Server

We have critical performance issues with our production database (its around 300 gb size) We have run the following script for a few huge tables : DECLARE @db_id SMALLINT; DECLARE @object_id INT; ...
7
votes
1answer
13k views

How do I alter index rebuild an entire database?

I want to reorganize the indexes of all tables in my SQL Server 2008 database, named 'db'. I try the following command: ALTER INDEX all ON db REBUILD And I get the following error: Cannot find the ...
1
vote
0answers
74 views

Defragmentation of SQL Server 2008 indexes

I am having issues defragmenting indexes. I keep using the various defragmentation methods for defragmenting a SQL Server index such as DBCC INDEXDEFRAG and DBCC DBReIndex, but none seem to be ...
0
votes
1answer
347 views

SQL Server 2008 - Question about index behaviour

I have a general question about advanced issues regarding index behaviour. In short, roughly one year ago, we dynamically dropped and recreated a number of indexes with different filters, but using ...
1
vote
1answer
321 views

DBCC DBREINDEX in MS SQL Server 2008

We have done DBCC DBREINDEX for our production database which size is about 300 gb, it was running for 12+ hours, but i found that DBCC DBREINDEX is obsolete and recommended approach is using ALTER ...
1
vote
1answer
35 views

Sql Server 2008 : What is the cost of accessing an index?

I've just read this interesting blog post about selectivity notion. In the section 'When is better to not use a database index?', the author states : Accessing the index takes time, and consumes ...
38
votes
4answers
43k 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 https://stackoverflow.com/questions/1307990/...
2
votes
0answers
85 views

How long does the final phase of an online index creation take?

In reading the whitepaper: Online Indexing Operations in SQL Server 2005 it says: The online index build process can be roughly divided into three phases: Preparation Build Final ...
1
vote
1answer
109 views

Full text index does not honour deleted words - They are still indexed

I have a full text index defined as 'Automatic' radio button checked on in its Catalog. When I modify a word in the full text indexed column, the new word appears in the sys.dm_fts_index_keywords but ...