A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.

All Questions

Tagged with
Filter by
Sorted by
Tagged with
41
votes
3answers
47k views

Why is my database still fragmented after I rebuilt and reindexed everything?

I have a database which I tried to defragment all the tables at once by running this T-SQL: SELECT 'ALTER INDEX all ON ' + name + ' REORGANIZE;' + CHAR(10) + 'ALTER INDEX all ON ' + ...
7
votes
2answers
7k views

Best value for fill factor in index

I use SQL Server 2008 R2 and want to set value for fill factor property in each index. I look for recipe to calculate best value for this property (have better performance when insert record) . Also ...
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)...
38
votes
4answers
42k 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/...
21
votes
3answers
4k views

Is it OK to blindly add missing indexes?

I often use SSMS to test my slow stored procedures for missing indexes. Whenever I see a "Missing Index (Impact xxx)" my kneejerk reaction is to just create the new index. This results in a faster ...
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 ...
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: ...
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 ...
22
votes
3answers
11k views

Painless way to create a clustered index on a huge table?

So we have a customer site that is complaining about some seriously slow performance. I took one look and it's obvious that the problem is because Somebody Else (grrrr) designed a table holding some ...
10
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 ...
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
444 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, ...
4
votes
3answers
7k views

SQL Server Timeout: Drop and Recreate Index Solves the Issue

After having sql timeout issues with one of our queries we added a few indices that then made the query run very quickly and got rid of any timeout out issues we were having. The strange thing is ...
4
votes
1answer
1k 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 ...
3
votes
2answers
491 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
222 views

Minimum rowcount for filtered index?

Creating an index always has trade-offs. Whenever you insert, update or delete, it has to perform those operations on the index as well. This is all pretty commonly known. But with the advent of ...
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