All Questions

Tagged with
Filter by
Sorted by
Tagged with
1
vote
3answers
654 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 ...
0
votes
1answer
45 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 ...
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)...
0
votes
1answer
349 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
1answer
112 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
2answers
473 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?
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 ...
0
votes
1answer
75 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) ...
0
votes
2answers
89 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
500 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 [...
1
vote
1answer
511 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
499 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
163 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
54 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
886 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?
7
votes
1answer
1k 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
113 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 ...
-1
votes
1answer
280 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 ...
1
vote
0answers
26 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
436 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, ...
11
votes
1answer
963 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 ...
1
vote
0answers
137 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; ...
1
vote
1answer
311 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 ...
2
votes
0answers
80 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 ...
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
42 views

What can cause an Index “total writer” to be the same in every index?

I have a table, with some Indexes ( created by me, others by SQL Server Profiler (SqlServer 2008)). I use this query to analyze unused Indexes: --Possible bad Indexes (writes > reads) SELECT ...
3
votes
2answers
475 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 ...
3
votes
1answer
6k 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, @...
1
vote
1answer
101 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 ...
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 ...
1
vote
0answers
71 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 ...
10
votes
2answers
5k views

Finding the query causing the missing index DMVs

How can I find the query causing the missing index DMVs suggesting to create a particular index? Thank you.
1
vote
1answer
81 views

What happens if an index build is cut half way through?

Today I ran a create NC index but stopped the execution after running for 1 hour 46 minutes. The main reason being I started to get worried about the disk space getting lower. It consumed roughly 10 ...
1
vote
1answer
2k views

Removing some English token words (such as “are” or “or” etc.) from the system stoplist of full text search?

One of our table has full text catalog on it. It contains some data in a language other than English which makes some words such as "are", "or" useful. There is no stop list defined but these words ...
5
votes
1answer
2k views

What is a non-clustered index scan

I know what table scan, clustered index scan and index seek is but my google skills let me down to find a precise explanation into non clustered index scans. Why and when a query uses a non clustered ...
1
vote
1answer
79 views

Choice of clustered key when almost all columns would be part of a unique index anyway

I am creating the following table CREATE TABLE dbo.COMM_SHELF_BLOCK_ACCESS ( KeyId uniqueidentifier NOT NULL ROWGUIDCOL CONSTRAINT DF_COMM_SHELF_BLOCK_ACCESS_KeyId default NEWSEQUENTIALID(), ...
0
votes
0answers
480 views

Measure fragmentation of a single index?

We are measuring the impact of different defrag approaches that we do one index at a time. How do we query to see fragmentation of a single index, by index name (or table + index). This is on SQL ...
2
votes
1answer
106 views

Index against a subset of rows?

Is it possible to create an index against a subset of rows fulfilling a certain condition? If it is possible how does one ensure the index is used?
0
votes
0answers
42 views

I need advise on if I should rebuild my index [duplicate]

I have a SQL Server 2008 Standard server on full mirror (safety). I reorganize all indexes weekly. I am looking into rebuilding a few indexes to improve performance. I ran DBCC SHOWCONTIG on my ...
2
votes
0answers
161 views

SQL Server Full-Text Index with Stoplist

I've searched this site and the internet in general and can't find any information which answers my question, so I thought I'd post it here. I have Full Text search operating on a database in SQL ...
7
votes
1answer
17k views

Difference between Clustered Index and Unique Clustered Index? [closed]

I got difference between clustered index and unique index here. But what is the exact difference between clustered index and unique-clustered index? AFAIK creating clustered index can be done for a ...
1
vote
1answer
63 views

Creating an index on table A but with reference to table B

Our company uses a 3rd party application for numerous aspects of the business. One aspect is dealing with customer payments and when selecting a window within the app to list all the payments ever ...
2
votes
1answer
381 views

Find statements related to sys.dm_db_missing_index… tables (SQL Server)

I have read up different articles related to SQL Server 2008 on how to find out missing indexes using the tables sys.dm_db_missing_index_groups, sys.dm_db_missing_index_group_stats etc. One thing I'...
0
votes
0answers
547 views

Error creating spatial index sqlserver

I am creating spatial index on sqlserver table. CREATE SPATIAL INDEX [SIndex_udt_cell_SP_GEOMETRY] ON [udt_cell] ( [SP_GEOMETRY] )USING GEOMETRY_GRID WITH ( BOUNDING_BOX =(80.0713, 26.389581, ...
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: ...
1
vote
2answers
1k views

Find missing indexes

tool available to find missing indexes in mssql 2008 ? can we find the missing index using Database Engine Tuning Advisor
0
votes
1answer
345 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 ...
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 ...
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 ...