All Questions
Tagged with sql-server-2008-r2 index-tuning
50 questions
1
vote
0
answers
38
views
Execution plan showing high hash match [closed]
i have a sql query running from database 1 that joins table company on to a view
SELECT DISTINCT RTRIM([K].[cmpCompanyCode]) AS [cmpCompanyCode]
FROM [dbo].[view] AS [K]
JOIN [database2].[dbo].[...
- 11
0
votes
1
answer
99
views
Speeding up "duplicate" checks
We have an events table that is reasonably large (~35 million rows)
The original table is like this,
CREATE TABLE [dbo].[Events](
[EventID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[...
- 255
2
votes
2
answers
395
views
Enlighten me about INCLUDE on indexes
Well, After reading Erik Darling's post Filtered Indexes: Just Add Includes I'm really curious and sad about how I use INCLUDES:
He gives some examples and etc. about INCLUDE, but I'm confused about ...
- 7,484
1
vote
1
answer
452
views
Query Tuning and Index Tuning - SQL Server
I've been asked to improve the performance of the following query.
I can't amend any existing Indexes but I can create new ones.
The table structures can be found in this SQL Fiddle
The row sizes of ...
- 813
6
votes
2
answers
3k
views
NonClustered Index not being Used
I have a table (created below) that contains 321 rows.
I expect the last query below to use the NonClustered Index and then a Key Lookup. However, instead it uses a Clustered Index scan. Only a ...
- 533
2
votes
1
answer
2k
views
Does IMAGE column affect query performance even if it's not included in the query?
Given the following table storing multiple milions of rows:
CREATE TABLE [dbo].[files]
(
[ID] UNIQUEIDENTIFIER CONSTRAINT [a] DEFAULT(NEWID()) NOT NULL,
[OOUID] UNIQUEIDENTIFIER ...
- 4,807
4
votes
4
answers
5k
views
Find and Remove Duplicate Indexes
I have more then 1000 tables in my database. On few tables I found duplicate Indexes. Now I want to find duplicate indexes in the database and remove them. Means, if a table have 2 or more indexes on ...
user98113
9
votes
1
answer
2k
views
Is the WHERE-JOIN-ORDER-(SELECT) rule for index column order wrong?
I am trying to improve this (sub-) query being part of a larger query:
select SUM(isnull(IP.Q, 0)) as Q,
IP.OPID
from IP
inner join I
on I.ID = IP.IID
where
...
- 4,807
7
votes
1
answer
1k
views
Will a nonclustered index with a unique column always address all queries filtering that column first?
I have found large heap tables that are over-indexed and for example have multiple different nonclustered indexes with different columns, but some of those indexes have the (only one present) primary ...
- 4,807
2
votes
2
answers
103
views
Why does the first query scan and the second seek?
Please could somebody explain why the first query below scans on every join to the REFERENCE_VALUES table but the second query seeks on the same joins?
The first query does have other joins, but I ...
- 423
11
votes
2
answers
16k
views
Good reasons to use SELECT... WITH XLOCK?
I am facing some re-occurring deadlocks, one of which is a Keylock and contains a SELECT query with XLOCK hint that becomes the deadlock victim. The other statement is an INSERT into one of the tables ...
- 4,807
2
votes
1
answer
95
views
SQL Server 2008: Unused Clustered Index
I have an MS SQL Server 2008 R2 database that contains a table that has just under 100 million rows & is both read and write heavy.
It contains Tweets, and the id of each Tweet is one of the ...
- 21
5
votes
2
answers
136
views
Does a covering nonclustered index help to answer Select * queries?
Our application developers run a lot of SELECT * queries against our database system, unfortunately.
The missing index suggestions of sys.dm_db_missing_index contain a lot of cases that indicate to ...
- 4,807
1
vote
0
answers
119
views
Reasons why plans of sys.dm_exec_cached_plans do not match actual plans?
I re-assess index / query optimization and actually focus on key/bookmark lookpus that could be eliminated.
Using a script of Jonathan Kehayas I go through the list of query plans that used those ...
- 4,807
2
votes
1
answer
317
views
Why does SQL Server use this index?
I have two indexes on a table with 786602 rows:
CREATE NONCLUSTERED INDEX [IX1]
ON [dbo].[O] ([A] ASC, [B] ASC)
INCLUDE ([c1], [c2], [c3])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
...
- 4,807
2
votes
0
answers
225
views
Combine several missing index suggestions with one index?
I investigate on missing indexes recommendations of my sql server engine.
Looking at the most useful ones (for a specific table) that are candidates based on their potential usage and potential ...
- 4,807
3
votes
1
answer
183
views
Missing indexes dmv count varies every day
I have a question to the missig index dmv.
BOL says:
Information returned by sys.dm_db_missing_index_details is updated when a query is optimized by the query optimizer, and is not persisted. ...
- 4,807
7
votes
1
answer
251
views
Clustered Index Help, where did i go wrong?
I was asked to review a very busy table and identify any areas of improvement.
I have very limited power to change the table just the indexes.
Table information
240 columns
~5 million rows
Read and ...
- 256
3
votes
1
answer
443
views
Ways to Reduce Database Size and possibly improve performace. Untouched enviroment
I am new to an environment. The databases are virtually untouched by a seasoned DBA. I am looking for ways to reduce DB size and possibly improve performance.
Unfortunately, I only have Web edition ...
- 171
4
votes
1
answer
952
views
Sudden poor SELECT performance on a large table with existing indexes
My company uses a software suite in which the only thing I can truly modify is the database. We've always had performance issues (mostly hardware problems that we hope to resolve soon), but lately it'...
- 273
2
votes
1
answer
171
views
Index to filter a view before joining the underlying tables [closed]
Using SQL Server 2008 R2
Suppose you have a query like the following
select top 1 *
from view_cms_user
where UserId = 1234 or UserPasswordRequestHash = 'anxazno121o3n'
view_cms_user is basically:
...
1
vote
1
answer
134
views
Order of Columns in Index
I have following query which has ON clause on different column, Where clause on different column and Selection on different columns so my question is what should be Order of Columns in Index as ...
- 4,073
2
votes
0
answers
191
views
Why would a default constraint be missing an index?
I have a query that looks at the sys.dm_db_missing_index_group_stats system view to determine what missing indexes there are for maintenance purposes. If I remove the filter from the query that ...
- 3,068
1
vote
1
answer
2k
views
Index file growing endlessly - how to fix it?
I need to do some maintenance on a database on our client's site. Both log and index files were huge (Don't know how they took their backups to end this way) anyway I fixed the log file (.ldf) issue, ...
- 13
3
votes
2
answers
207
views
When does SQL Server determine to use a new index?
I have been performing some maintenance on a server that runs some software the company bought, so I can't really change how the queries have been written. I have used some tools to determine some of ...
- 3,068
4
votes
1
answer
752
views
Table with clustered Index consuming max memory causing low PLE alerts
For one of my server with below configuration i am continuously receiving alerts for low PLE which ranges between 20- 400 and an average of 90-100 in a day:
Server: SQL server 2008R2
Databases : ...
- 7,177
4
votes
2
answers
1k
views
Uniquifier on Non-Unique Clustered Index vs Unique Clustered Index
Lets say for example that I have a table structured this way:
ID1 int
ID2 int
ID3 int
DTM datetime
I'm only able to create a unique clustered index on the table if I include ID1, ID2, and ID3 (a ...
- 145
4
votes
2
answers
3k
views
Clear missing index DMVs
How can I clear missing index DMVs such as dm_db_missing_index_group_stats, dm_db_missing_index_groups and dm_db_missing_index_details?
I don't have the option to restart the server or taking it ...
- 1,550
1
vote
1
answer
280
views
Rebuild index in table with many values
I'm junior DBA ,have table with 11 million values ,one of the indexes is like :
CREATE NONCLUSTERED INDEX [ix_mytbl_mycolumn] ON [dbo].[myTbl]
(
[my_id] DESC
)WITH (PAD_INDEX = OFF, ...
- 263
4
votes
2
answers
4k
views
SQL Server 2008R2 XML Column Index strategy for large tables?
I am working with SQL Server 2008 R2 and some XML columns in a very large table. I understand that I cannot use Selective XML Indexes due to our SQL Server version being < 2012. I'm brand spanking ...
- 427
2
votes
1
answer
3k
views
Create nonclustered index on uniqueidentifier type
CREATE TABLE [dbo].[session]
(
[id] [int] IDENTITY(1,1) NOT NULL,
[user_id] [int] NOT NULL,
[session_id] [uniqueidentifier] NOT NULL,
[supervisor_id] [int] NULL,
some columns ..
...
- 263
5
votes
2
answers
29k
views
Find out when your index was last rebuilt / reorganized
I've recently come across a database, which has very high fragmentation on large tables.
Page count > 1'000'000 with fragmentation 99,...
I would like to know how long this table has gone without a ...
- 2,631
2
votes
0
answers
93
views
work/cache table issues, heavy inserts with heavy reads
we have a work table that stores availability of products. This table is updated with millions of inserts every night and medium amounts of inserts throughout the day.
during the updates, we delete ...
- 289
0
votes
0
answers
80
views
Rebuilding an index not working? [duplicate]
I noticed that when I right click manually on an index and choose "Rebuild" or "Reorganize", its not giving any indication that its doing so, then I click on properties again for the same index, I see ...
- 655
2
votes
1
answer
612
views
Does Index Intersection work with Included Columns?
I've been asked by a report designer whether it is acceptable to add a new index to a table to speed up a particular report. The index would be on a single column, CreatedDate, but would have ...
- 295
10
votes
3
answers
284
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,620
3
votes
1
answer
1k
views
NEWSEQUENTIALID() resets sequence on server restart
MSDN learns me that NEWSEQUENTIALID():
Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows,...
- 191
10
votes
1
answer
701
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 ...
- 1,245
0
votes
1
answer
168
views
Index Maintenance on SQL Server 2008 R2 Web edition [duplicate]
I've inherited of a couple of databases which indexes have never been maintained (rebuilt, reorganized).
I'm using Ola Hallengren's Index maintenance script, I've used the following script to record ...
- 229
1
vote
1
answer
356
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 using ...
- 401
2
votes
2
answers
1k
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 ...
- 401
2
votes
2
answers
801
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 ...
- 23
9
votes
1
answer
6k
views
How can a fill factor of 0 or 100 be the same?
As of my understanding, Fill factor of 80 implies that 20 percentage of each leaf-level page will be empty to enable future growth.
I can't correlate how fill factor of 0 and 100 can be the same!
Am I ...
- 97
5
votes
2
answers
6k
views
Can I trust the Database Engine Tuning Advisor recommendations?
Our database has become large, and we are trying to optimize performance.
Can we follow the recommendations provided by the Database Engine Tuning Advisor by supplying it a .trc file generated with ...
- 51
0
votes
1
answer
56
views
Can I rely on the order of columns on an index generated by database tuning advisor
I got the following recommendation on a table:
CREATE NONCLUSTERED INDEX
[...
- 3
3
votes
2
answers
7k
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 ...
- 472
4
votes
2
answers
941
views
Execution plan results for sp_executesql suggests adding missing index for already existing index
I'm analyzing a query being run inside our system via sp_executesql. I am able to execute the query on the production database and include the actual execution plan. In the execution plan results ...
- 785
4
votes
2
answers
161
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 ...
- 751
4
votes
1
answer
295
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 ...
- 2,965
1
vote
1
answer
3k
views
Reindexing Partitioned Table Indexes
Historic Reporting Server running SQL 2008 R2.
I'm trying to tweak the fill factor. I have a job that records the fragmentation when the jobs runs in the early hours.
For every table that had high ...
- 2,591