0
votes
0answers
15 views

Does MySQL have a visual tool for suggesting indexes like SQL Server's “Include Actual Execution Plan”?

SQL Server has a remarkably useful feature called "Include Actual Execution Plan" which not only gives you a visual diagram of your query, but also automatically suggests indexes that can improve ...
1
vote
2answers
21 views

Compare Indexes/Constraints between two SQL Server databases

I have two databases on one SQL Server that I would like to compare Index/Constraints. The two databases should be identical but I have a suspicion that one database is missing a index or constraints. ...
2
votes
1answer
39 views

Adding Indexes To a Partitioned table in SQL Server 2012

I have recently converted our one large table to use table partitioning in SQL Server 2012. It's a table that stores logs based on a time. So I created a partition function to split the table on my ...
1
vote
2answers
96 views

How to add an “INCLUDE” part in an Index Created as a part of CONSTRAINT

I am going through the execution plan of a tsql query. There is a key LookUp (Clustered) in the plan. To avoid this Key LookUp, I tried to Include a column (Status_Ind) to The Index (The Index ...
0
votes
0answers
42 views

LIKE or CHARINDEX to check String Contains a Substring [duplicate]

To check a string contains a sub string or not we can use [LIKE] or [CHARINDEX]. I know that like uses indexing in case we have name%.(while %name% may not use index) .Also charindex does not use ...
2
votes
0answers
60 views

Can there be any case where it would require additional indexes while all columns compose primary key

Ok i don't know whether this question is asked before or not Here my table SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblProductPagesFeatures]( ...
4
votes
1answer
194 views

Sql Server - Select while insert on another transaction gives unexpected results

I stumbled at situation that changes my knowledge about transactions and locking fundamentally (I don't know much though), and I need help to understand it. Let's say I have a table like this: ...
4
votes
1answer
52 views

Combining full-text and scalar index

Let's say we have a database of 12 million names and addresses that need to be searchable using full-text, but each row contains an integer value too, let's say COMPANYID. The table contains around ...
3
votes
1answer
76 views

Does sp_indexoption cause index rebuilds?

I want to switch AllowRowLocks=ON on a number of indexes. Some of those indexes are on large tables, and I don't really want to rebuild or reorganize the index. It is not clear from the MSDN article ...
3
votes
1answer
55 views

Online index rebuild LOB types

I have read and heard that you cannot perform online clustered index rebuild for tables that have a varchar(max) type. This (as I have read) also should be true for nonclustered indexes that contain ...
1
vote
1answer
64 views

Why is my filtered index being ignored?

--Setup, actual queries after the space --create table fltrind (a integer,b integer) truncate table fltrind DROP INDEX fltrind.nf DROP INDEX fltrind.f DECLARE @i integer SET @i=0 while @i<100 BEGIN ...
2
votes
2answers
70 views

Database events that will cause deletion of index

In SQL server if we try to change a data type of a column having index ,then it deletes the indexes associated with that column and changes the data type . I was not aware of it and was faced problem ...
1
vote
2answers
78 views

Update on view over partitioned tables updating all indexes

We have a table, which is partitioned through a date field into separate years. There is a view over all of these tables (Call) Schema is as follows: CREATE TABLE [dbo].[Call_2015]( [calID] ...
3
votes
2answers
81 views

Reindexing Clustered Primary Key

We need to reindex a Clustered Primary Key on quite a large table. Here is the definition of the table: Data Size: 22GB Index Size: 34GB Total Non-Clustered Indexes: 3 Row Count: 54 Million Data ...
5
votes
2answers
133 views

SQL Server: Create New GUID Values for Existing Index

I have an existing table with about 6 million rows. The relevant columns are: ID int not null PK Key uniqueidentifier not null Read/write on this table by Key lookup is probably something like 100 ...
0
votes
1answer
45 views

Batch converting indexes to keys [closed]

I have a legacy MS SQL Server database where all the primary and foreign keys were created as indexes. I am developing the new version of our software with Entity Frameworks, which I have learned ...
0
votes
1answer
83 views

When to rebuild and when to reorganize indexes [duplicate]

I have been trying to gather information about rebuilding and reorganizing indexes. From stackoverflow page (How Often should the indexes be re-build in sql-server DB?) i got this query : SELECT ...
4
votes
2answers
115 views

Clustered Table Scan Because of “SELECT *”

I have a Records table with over 100 columns and very many rows, and a nonclustered index on 5 fields based on my access paths: CREATE NONCLUSTERED INDEX [IX_Records_CustomerID] ON [dbo].[Records] ( ...
2
votes
2answers
65 views

best possible index on this situation [on hold]

create table #testing(id int, name varchar(50)) go Insert Into #testing Values(231, 'fasd') Insert Into #testing Values(232, 'dsffd') Insert Into #testing Values(233, 'xas') Insert Into #testing ...
0
votes
1answer
62 views

Indexes and their uses [closed]

Index improve performance in select statement, but what is the difference between this create clustered index ix_index1 on table1(col1) create clustered index ix_index2 on table1(col2) and this ...
0
votes
0answers
31 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 ...
1
vote
0answers
32 views

Clarify unique constraint and index, cannot grasp the concepts to 100% [duplicate]

When reading about UNIQUE, I understand the concept but when it comes to the practical part of it, I get confused and I'm unsure of what it means. What I do understand, is that there are two ways to ...
1
vote
3answers
335 views

What's the most efficient UUID column type

For storing a 128 bits UUID there are multiple storage options: a byte[16] column two bigint/long(64 bits) columns a CHAR(36) column - 32 hex digits + 4 dashes. a UUID database specific column, if ...
6
votes
1answer
354 views

SQL Server: Are there any performance benefits from indexing a table <8kb in size?

If a table is <8kb in size in SQL Server then presumably it is all stored on a single page. Thus - aside from enforcing a unique constraint - is there any performance benefit that could be obtained ...
2
votes
1answer
124 views

Is there a negative effect if I place an index on every column in a wide table?

We have a wide dataset that isn't very tall (say 250K rows, 90 columns). We will be providing users with an interface that allows them to generate arbitrary queries against this dataset. They can ...
10
votes
1answer
189 views

Rejoining range seek on nullable composite index?

For the following schema and example data CREATE TABLE T ( A INT NULL, B INT NOT NULL IDENTITY, C CHAR(8000) NULL, UNIQUE CLUSTERED (A, B) ) INSERT INTO T (A) ...
4
votes
0answers
312 views

SQL Server Deadlock - Two SELECT Exclusive Locks on Exact Same Index and Key [closed]

How is the deadlock graph below possible? How did two SELECT statements simultaneously acquire an exclusive lock on the exact same resource? The exclusive locks were on the primary key index for ...
0
votes
1answer
49 views

NTEXT,Indexing, and lob logical reads

We have an query that would benefit from a covering index but one of the columns that would be returned is a NTEXT data type. I know this can't be added to the index and I also know it would be good ...
5
votes
2answers
158 views

Any advantage of having a second index with included columns on a primary key?

I'm reviewing our database and noticed that on a particular table we have the following indexes: Table: Col1 INT IDENTITY(1,1) Primary Key Col2 INT ... about 15 more columns .... ColN ...
2
votes
1answer
77 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 ...
1
vote
4answers
100 views

Profling for Index Maintenance

I am wanting to perform some index maintenance, in particular we have a poor performing query against a particular table. Is it a sensible strategy to profile on RPC:Completed, SP:StmtCompleted and ...
1
vote
0answers
47 views

Index Maintenance

I have a query thats performing badly and has a RID lookup. There are already quite a few indexes on the table and in the query stats I can see they are used although some less than others and all ...
4
votes
1answer
131 views

How to disable or delete an index

I created a non-clustered index on a live server (SQL Server 2008 R2) and it turned out not to give the performance benefits that I was hoping for. So I want to delete the index again. Unfortunately ...
-2
votes
2answers
67 views

Index is not used with same Query on two Servers [closed]

I have two notebooks with same specs (same model, only ram is 24GB/32GB, same ssd) and exact same database and same sql server version. SQLIO is 3300 and 3600. Now I run a query on one notebook and ...
2
votes
3answers
159 views

Listing indexes and constraints

I am looking at a SQL Server database for an application I have inherited. I have not looked into SQL Server for approximately 10 years, so please bear with me. The database table I am looking at has ...
5
votes
1answer
89 views

SMS Suggesting covering all columns in an index?

I'm still a bit newer to indexes and database works, so I may very well be missing something obvious. SMS suggested adding a covered index to one of my tables that Indexes on the column I believe ...
8
votes
1answer
454 views

Deadlock when update different rows with non-clustered index

I'm solving a deadlocking issue while I noticed the lock behavior is different when I use clustered and non-clustered index on the id field. The deadlock issue seems to be solved if clusted index or ...
1
vote
1answer
41 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
2answers
75 views

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

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 ...
1
vote
1answer
124 views

GETDATE() uniqueness when used on INSERT

Recently I was reading this blog post: http://blogs.msdn.com/b/sqlazure/archive/2010/05/05/10007304.aspx which contains this section: Picking a Clustered Index There are a couple of ...
2
votes
1answer
61 views

Clustered index additional storage

Reading some books I found that clustered index not require additional disk space for storing index data, because leaf level of clustered index are data itself. But, as clustered index is organized ...
1
vote
0answers
65 views

How to take advantage of multiple spatial indices?

All, I'm building a view which uses a spatial intersect (STIntersect in MSSQL 2012) between points (student addresses with address information) and polygons (school boundary polygons with school ...
2
votes
2answers
114 views

SQL Server 2008 R2 Missing Index

I am performing index maintenance on a database. I have run SQL script that uses DMVs to provides a list of indexes SQL Server believes should be created. I am not creating indexes just because the ...
3
votes
2answers
66 views

SQL Server NULL Index

I am new here and have a little question. In a customer environment after checking his index fragmentation we see some "NULL" named index that we couldn't rebuilt or reorganize. This was a SQL ...
1
vote
1answer
127 views

Is indexing binary?

The scenario: When a user updates some information - the correct row must be found and updated. I'm debating between two options: Let the server search through all of the rows (-slow). But be ...
2
votes
1answer
80 views

indexes with unspecific columns

I have a third party database in which a large number of the tables have a 'companyID' column, and most of the queries against those tables include companyID = @companyID in the where clause. [EDIT: ...
4
votes
2answers
75 views

Spatial index not used

I have a table with a geometry column. For one record, there is only one Point stored. A spatial index has been created but queries searching for the nearest location do not use this index resulting ...
3
votes
4answers
628 views

indexing on multiple nvarchar(max) columns

I have multiple nvarchar(max) columns in my DB named ShipperName,ConsigneeName, ProdDesc,BillOFLading. What I need to do is to have UNIQUE INDEX for the combination of these columns. As I am failed ...
5
votes
2answers
130 views

Why is an entire index that is infrequently used stored in the buffer pool?

I have been looking at the memory usage of a server and noticed something odd when drilling into the details. One index is eating up ~15GB of the buffer pool. That happens to be the size of the entire ...
0
votes
0answers
37 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 ...