0
votes
1answer
47 views

Which should I use on my DB? - Clustered Index or Non Clustered Index or Both [duplicate]

I just need advice on how I am going to optimize my database. | Date | Time | Area | Block | Data1 | Data2 | Data3 | DataN | I have that format of tables on my database. Each tables has 30-days of ...
2
votes
1answer
61 views

Non-Clustered Index key columns to cover a variable where clause

To whoever reads this, sorry for the long question and thanks so much for your time. I'm needing some help on understanding what columns should be key/non key in the following situation, and what ...
3
votes
1answer
163 views

Clustered Index - Query Performing Index Scan

Why does the below query perform an Index Scan rather than an Index Seek? I would have thought the Index would act as a covering index? CREATE NONCLUSTERED INDEX [IX_CustomerFileDate] ON ...
0
votes
0answers
98 views

Table Valued Function Very Slow

I have a TVF that when called takes 10 secs to run. However if I take the query inside that TVF, it takes only 1 sec. I have added correct indexes on this underlying query tables. In the Execution ...
9
votes
3answers
171 views

One index or two?

I have the following index created on a table in my database: CREATE INDEX [idx_index1] on [table1] (col1, col2, col3) The server is suggesting the following 'missing' index: CREATE INDEX ...
-1
votes
1answer
107 views

How can I detect which indexes are being rebuilt offline and how long?

The rebuild of indexes takes appr. the same 55 min. with both "Do not rebuild indexes and "Rebuild indexes offline" "For index types that do not support index rebuilds" with "Keep index online while ...
0
votes
1answer
109 views

What is blocking 'SELECT… WITH(NOLOCK)'?

While following BrentOzar's "How Much is Offline During an Index Rebuild?" I was curious to run: SELECT Quantity FROM [Production].[TransactionHistory] WITH(NOLOCK) while executing a rebuild ...
7
votes
1answer
329 views

Poor query performance

We have a large (10,000+ lines) procedure that typically runs in 0.5-6.0 seconds depending on how much data it has to work with. Over the past month or so it has started taking 30+ seconds after we do ...
4
votes
3answers
617 views

Indexing a PK GUID in SQL Server 2012

My developers have setup their application to use GUID's as PK for pretty much all of their tables and by default SQL Server has setup the clustered index on these PK's. The system is relatively ...
5
votes
2answers
285 views

How to optimize NOT IN statement

Why does NOT IN performs INDEX SCAN instead of INDEX SEEK? Here's my current index on the table, CREATE NONCLUSTERED INDEX [idx_dmcasarms_CourseList_cDesc] ON [dbo].[courselist] ( [c_desc] ...
1
vote
1answer
101 views

Database Tuning Advisor Index Recommendations

How does DTA make an index recommendation? What is the technology behind that? If we can use same on our side then we can shorten the whole process of Tuning recommendation: creating trace file ...
1
vote
2answers
138 views

How to improve performance if index is not Most SELECTIVE?

As per suggestion index must be most selective to improve the performance. As a general guideline, we should create indexes on tables that are often queried for less than 15% of the table's rows The ...
2
votes
1answer
143 views

Creating indexes on AutoId and time column

My table structure is as follows Events( AutoId int identity(1,1) //Primay Key, clustered index EventTime datetime // non unique, non clustered index FileName nvarchar(1000) ...
7
votes
2answers
1k views

Does a re-index update statistics?

I've been doing the MS10775A course this past week and one question that came up that the trainer couldn't answer reliably is: Does a re-index update the statistics? We found discussions online ...
2
votes
2answers
163 views

SQL Server 2008 R2 : A Possible indexing strategy for the given schema?

We have these four tables: Store ( row bigint, id uniqueidentifier, name varchar ) Products ( row bigint, id uniqueidentifier, storeID uniqueidentifier, productname ...
5
votes
2answers
379 views

How to design index for faster deletes

I want to speed up following piece of code delete from ssn_sdo where (art_id=@art_id and skl_id=@skl_id and @level=0 ) or (art_id=@art_id and skl_id=@skl_id and @level=1 and ...
4
votes
1answer
108 views

What is the difference between these two index declarations and do I need both?

I was examining a script with 'Display Estimated Execution Plan' and it made several recommendations for indexes to add to the database. I added them and the script did indeed run much faster. Now ...
5
votes
2answers
133 views

Indexing strategy for dynamic predicate

Assume SQL Server 2012 Standard edition. My database has a table with 500 million rows. The table has about a dozen columns, none of which are very wide (some varchar(100)'s and some ints). The ...
3
votes
1answer
128 views

How can I determine what is using a particular resource (table, view, or function, etc) on my SQL Server?

I've been looking for tables that could use better indexing. I've created a stored procedure that provides a list of tables that have no indexes other than a primary key: CREATE PROCEDURE ...
2
votes
1answer
352 views

Best practice around sys.dm_db_missing_index_group_stats in SQL Server

I have a fairly simple query using sys.dm_db_missing_index_group_stats that identifies missing indexes in my SQL Server database. This is pretty commonly used. My question is how do I identify which ...
0
votes
1answer
556 views

Is Reorganizing Indexes required after ShrinkDatabase or can I just reogranize index with fragmentation more than 10%?

I am a new DBA to a SQL 2005 production Report server. My predecessor created a Maintenance Plan with 4 tasks: CHECKDB; SHRINKDATABASE (N'DB1',10,TRUNCATEONLY); it seems to be running a REORGANIZE on ...
5
votes
2answers
467 views

Finding stored procedures with missing indexes

How to scan for stored proc that need indexes to be created. Anyone have any idea? Basically we have stored procedures that run very slowly. I'm wondering if there is some kind of automated script ...
1
vote
1answer
402 views

Optimizing an audit table for inserts and deletes

I'm trying to identify the best way to design a table for audit purposes. Basically I log several last events for many users in one table. There's a limit on the number of records for each user. The ...
1
vote
2answers
367 views

What am I doing wrong with the Database Tuning Advisor?

My background: I'm a dev/architect, not a DBA. Sorry! So we have a ~400 table 75GB database. I ran Profiler for ~24 hours ("Tuning" template minus LoginName) and have ~7GB of usage recorded in a ...
2
votes
3answers
159 views

Determining if indexes are redundant

I have a table with the following structure: Id (int identity PK) FooId (int FK) BarId (int FK) QuxId (int FK) Other fields The following indexes have been defined: PK (Id), clustered Index1 ...
3
votes
1answer
290 views

Can indexing views in SQL Server 2005+ speed up SELECT count(*) from a View?

My initial question was How does someone determine if it is a good idea to index a view?. Update: My underlying assumption was that indexing views speeds them up. This is apparently not sensible, ...
6
votes
2answers
506 views

Adding a Clustered index to a HEAP table on someone else's app

We're using a proprietary application based on SQL Server 2005, which has many HEAP based tables (that is, no Clustered index). Over the years, these tables have grown badly fragmented (e.g. 99% ...
7
votes
1answer
771 views

What factors go into an Indexed View's Clustered Index being selected?

Breifly What factors go into they query optimizer's selection of an indexed view's index? For me, indexed views seem to defy what I understand about how the Optimizer picks indexes. I've seen this ...
3
votes
2answers
127 views

index maintenance strategy if few insertion points relative to # of rows

In SQL Server 2008 R2, I have a nonclustered covering index on multiple tables with 100M+ rows. The table has a few thousand "insertion points" where all new inserts happen. This means that regardless ...
1
vote
0answers
403 views

SQL Server Database Engine Tuning Advisor vs DMV queries

What is the best way to tune the indexes on my production database? I normally use the following query : SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage], ...
3
votes
3answers
155 views

What is a good guideline for sampling for statistics updates

Scope: A billion record table that is indexed in 10 ways. Rate of change per day is 1/2 a percent. Some of the indexes are monotonously increasing (datetime/timestamp), yet the common queries most ...
5
votes
2answers
1k views

Viable strategies for rebuilding indexes online in SQL Server 2008 R2 Standard edition?

We use SQL Server 2008 R2 Standard edition (we can't afford Enterprise edition on all our production database servers) and have to periodically maintain the critical indexes (rebuilding/reorganizing) ...
7
votes
2answers
517 views

Filtering data ordered by rowversion

I have a SQL table of data with the following structure : CREATE TABLE Data( Id uniqueidentifier NOT NULL, Date datetime NOT NULL, Value decimal(20, 10) NULL, RV timestamp NOT NULL, ...
2
votes
1answer
115 views

Where to place non clustered index

Execution plan clearly showing that one of my table used in query is useing Clustered Index Scan. From this node, how can i guess that which columns should be part of my non clustered index key and ...
5
votes
3answers
1k views

SQL server indexing foreign keys, covering indexes included columns

I have pulled a list of FK's that do not have indexes on them. If a FK does not have a dedicated index on them but are part of wider indexes used for covering queries, Should they have a dedicated ...
4
votes
2answers
3k views

Primary key vs. unique index: performance difference with foreign keys?

Will there be any difference in performance if you compare a query fetching data via a join between two tables where there's a relationship against either of the following: A primary key A unique ...
2
votes
1answer
164 views

Indexing or changing one-to-many relationship

I have these two Microsoft SQL Server 2005 tables: ObjectLicenses (ObjectLicenseID [PK]; ObjectID; LicenseType; LicenseNumber) 1|1|A|000001 2|1|A|000002 3|1|B|000003 4|2|C|000004 Objects (ObjectID ...
1
vote
1answer
776 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] ...
6
votes
4answers
1k views

How will large index INCLUDE fields effect system performance?

This question is about SQL Server index performance with a varchar(2000) as an INCLUDE in a covering index. I’m trying to improve performance in an slow and unstable database application. In some ...
9
votes
1answer
2k views

Should I use many single field indexes, instead of specific multi column indexes?

This question is about the effectiveness of a SQL Server indexing technique. I think it is known as "index intersection". I'm working with an existing SQL Server (2008) application that has a number ...
5
votes
2answers
3k views

What is the difference between ALTER INDEX and DBCC DBREINDEX?

Is the only difference between ALTER INDEX [index_name] on [object_name] REBUILD with (ONLINE=OFF, FILLFACTOR=90) and DBCC DBREINDEX([dbname], 90) just that the DBCC command will reindex all ...
2
votes
1answer
280 views

Is staggering reindexing jobs a good strategy?

I have inherited a server with 35 databases and a job that reindexes all tables on all databases (using sp_MSForEachTable). It's taking over 14 hours to complete and is now blocking other processes. ...
2
votes
2answers
705 views

How can an identity primary key index become fragmented?

From what I understand about index fragmentation, this should not be possible. The cases I have found in my databases are non-clustered. Example: ALTER TABLE [dbo].[ClaimLineInstitutional] ADD ...
10
votes
2answers
3k 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 ...