SQL 2012 :: Column For Clustered Index
Apr 16, 2015Is it always the best practice to have the partition column also as the column for clustered index?
View 2 RepliesIs it always the best practice to have the partition column also as the column for clustered index?
View 2 RepliesI have created NONCLUSTERED index on table but my report is taking more time that's why i created columnstore NONCLUSTERED index on the same table but i have one query, if any table have row and column level index(same columns in index) . Which index query will consider.
View 1 Replies View Related
Dear All.
We had Teradata 4700 SMP. We have moved data from TD to MS_SQL SERVER 2003. records are 19.65 Millions.
table is >> Order_Dtl
Columns are:-
Client_ID varchar 10
Order_ID varchar 50
Order_Sub_ID decimal
.....
...
..
.
Pk is (ClientID+OrderId+OrderSubID)
Web Base application or PDA devices use to initiate the order from all over the country. The issue is this table is not Partioned but good HP with 30 GB RAM is installed. this is main table that receive 18,0000 hits or more. All brokers and users are using this table to see the status of their order.
The always search by OrderID, or ClientID or order_SubNo, or enter any two like (Client_ID+Order_Sub_ID) or any combination.
Query takes to much time when ever server receive more querys. some orther indexes are also created on the same table like (OrderDate, OrdCreate Date and Status)
My Question are:-
Q1. IF Person "A" query to DB on Client_ID, then what Index will use ? (If any one do Query on any two combination like Client_ID+Order_ID, So what index will be uesd.? How does MS-SQL SERVER deal with these kind of issues.?
Q2. If i create 3 more indexes on ClientID, ORderID and OrdersubID. will this improve the performance of query.if person "A" search record on orderNo so what index will be used. (Mind it their would be 3 seprate indexes for Each PK columns) and composite-Clustered index is also available.?
Q3. I want to check what indexes has been used? on what search?
Q4. How can i check what table was populated when, or last date of update (DML)?
My Limitation is i Dont Create a Partioned table. I dont have permission to do it.
In Teradata we had more than 4 tb record of CRM data with no issue. i am not new baby in db line but not expert in sql server 2003.
I am thank u to all who read or reply.
Arshad
Manager Database
Esoulconsultancy.com
(Teradata Master)
10g OCP
I have a clustered index that consists of 3 int columns in this order: DateKey, LocationKey, ItemKey (there are many other columns in this data warehouse table such as quantities, prices, etc.).
Now I want to add a non-clustered index on just one of the other columns, say LocationKey, like this:
CREATE INDEX IX_test on TableName (LocationKey)
I understand that the clustered index keys will also be added as key columns to any NC indexes. So, in this case the NC index will also get the other two columns from the clustered index added as key columns. But, in what order will they be added?
Will the resulting index keys on this new NC index effectively be:
LocationKey, DateKey, ItemKey
OR
LocationKey, ItemKey, DateKey
Do the clustering keys get added to a NC index in the same order as they are defined in the clustered index?
the query:
SELECT a.AssetGuid, a.Name, a.LocationGuid
FROM Asset a WHERE a.AssociationGuid IN (
SELECT ada.DataAssociationGuid FROM AssociationDataAssociation ada
WHERE ada.AssociationGuid = '568B40AD-5133-4237-9F3C-F8EA9D472662')
takes 30-60 seconds to run on my machine, due to a clustered index scan on our an index on asset [about half a million rows]. For this particular association less than 50 rows are returned.
expanding the inner select into a list of guids the query runs instantly:
SELECT a.AssetGuid, a.Name, a.LocationGuid
FROM Asset a WHERE a.AssociationGuid IN (
'0F9C1654-9FAC-45FC-9997-5EBDAD21A4B4',
'52C616C0-C4C5-45F4-B691-7FA83462CA34',
'C95A6669-D6D1-460A-BC2F-C0F6756A234D')
It runs instantly because of doing a clustered index seek [on the same index as the previous query] instead of a scan. The index in question IX_Asset_AssociationGuid is a nonclustered index on Asset.AssociationGuid.
The tables involved:
Asset, represents an asset. Primary key is AssetGuid, there is an index/FK on Asset.AssociationGuid. The asset table has 28 columns or so...
Association, kind of like a place, associations exist in a tree where one association can contain any number of child associations. Each association has a ParentAssociationGuid pointing to its parent. Only leaf associations contain assets.
AssociationDataAssociation, a table consisting of two columns, AssociationGuid, DataAssociationGuid. This is a table used to quickly find leaf associations [DataAssociationGuid] beneath a particular association [AssociationGuid]. In the above case the inner select () returns 3 rows.
I'd include .sqlplan files or screenshots, but I don't see a way to attach them.
I understand I can specify to use the index manually [and this also runs instantly], but for such a simple query it is peculiar it is necesscary. This is the query with the index specified manually:
SELECT a.AssetGuid, a.Name, a.LocationGuid
FROM Asset a WITH (INDEX (IX_Asset_AssociationGuid)) WHERE
a.AssociationGuid IN (
SELECT ada.DataAssociationGuid FROM AssociationDataAssociation ada
WHERE ada.AssociationGuid = '568B40AD-5133-4237-9F3C-F8EA9D472662')
To repeat/clarify my question, why might this not be doing a clustered index seek with the first query?
I want to know more details about the Clustered Index Delete. Is that Clustered Index Delete in the execution plan is good or bad or we can neglect that cost. Is there any way to avoid that clustered Index delete operator from the execution plan.
View 4 Replies View Related- What are your thoughts on adding clustered index on datetime (createdDate , native GUID) column. The data will be be physically organized in the clustered index allowing range operations to perform its duties. But will the GUID column make any impact ( drawbacks) should it be made part of the clustered key ?
The GUID column will provide the lookup with the required indexes to support.
I created a NC index as suggested by missing index DMV(of course I don't create them blindly). This one seemed to be a useful index but I now see from index usage stats that it only got scanned 50 times in 4 days.No seeks, no lookups. So is it a good idea keeping such index.The table on which this index is created is used more for reads and less for writes.
View 6 Replies View RelatedI am trying to tune a process that is running slowly. I analyzed the process using the Database Engine Tuning Advisor, and it recommended the creation of 3 indexes, all non-clustered:
1) ColA, include ColB
2) ColA, include ColC
3) ColA, include ColD
So... I created a single non-clustered index on:
4) ColA, include ColB, ColC, ColD
That should do the same thing, right? A look at my execution plan shows that the index I created is being scanned -- 3 times. What is puzzling me, though, is that the Database Engine Tuning Advisor is still recommending I create these 3 separate indexes, even with the index (4) that I created in existence.
If it matters, ColA, ColB, ColC and ColD are all int FKs.
how to reduce the cost of clustered index insert ?
View 8 Replies View RelatedI have a query which is primarily a victim of blocking and a blocker itself for other queries. I studied the plan for this and it shows a 42% cost on CI insert operation. The insert is happening on a table (Table A) that has a PK. This PK is not a running number. It is also a business key (primary key) in another master table (Table B).
My understanding is that the cost is heavy because -
1, this PK is not an incremental number. It could be any number not in a sequence.
2. while inserting into CI, there must be a scan happening to find out the location where the index will be inserted.
How can I reduce the cost?
1. Should I go for partitioning of this table Table A? I am trying to do this but I am not able to find any suitable partition key looking at the JOINS and filter clauses where this table is being used in the applicaiton.
2. Should I introduce a surrogate key (running number) as a primary key so that CI is faster ?
I have a table that has a clustered index that is only the identity column on the table. The table is somewhere around 200K rows and has 3800 pages in the index. We run our index maintenance every other day on this database using Ola's scripts and this index is rebuilt because it is 40-60% fragmented after 2 days. Overall, this isn't really too much of a problem since the index rebuild doesn't take too long, but I am puzzled as to how this index is getting fragmented since the only column in it is the identity.
CREATE TABLE [dbo].[Example](
[ExampleID] [int] IDENTITY(1,1) NOT NULL,
[ExampleCode] [varchar](10) NOT NULL,
[ForeignID] [int] NOT NULL,
[AnotherID] [int] NOT NULL,
[code]...
) ON [PRIMARY]There is nothing strange like updates to the identity happening and while some records are deleted, there has only been about 20,000 in the life of the table (months). Not enough to account for the level of fragmentation that we're seeing on the index.About the only thing I can think of that would cause fragmentation on this index in this scenario are:
1. Page splits caused by starting with a small value in one of the VARCHARs and later inserting a larger value
2. Page splits caused by the NULLABLE column, ExampleDate, starting with NULLs and later updating them to a date.
For #1, I had development check the update scenarios for the varchar columns, especially the varchar(1000) one, and they didn't see it as a common thing where the values would go from small (or empty) to large.
For #2, I checked and found that the only value for that column in the table is NULL so while it always starts as NULL, it never gets updated to anything else.
I've tried looking at sys.dm_db_index_operational_stats and the leaf_update_count is around 300,000, but unless those updates are causing page splits, I don't see how they would contribute to fragmentation.
I have a clustered index which shows as having a fragmentation level of 66% according to sys.dm_db_index_ physical_ stats.avg_ fragmentation_ in_ percent.
But no matter what I try the fragmentation level doesn't budge. And yes I'm updating the statistics after each attempt.Its not a huge issue the table only has 348 records. I'm testing a fixing fragmentation maintenance script. In Ironing out the syntax of my script I've fixed the fragmentation of indexes of over 65 % anyway..I've come across and index that I can't defragment. I've tried
ALTER INDEX ALL ON [GRIDINFO] REBUILD WITH (FILLFACTOR = 90)
I've tried
ALTER INDEX ALL ON [GRIDINFO] REORGANIZE
I've tried
DBCC INDEXDEFRAG (MYDATABASE, 'GRIDINFO', PK__GRIDINFO__3214EC2721F5FC7F);
The command complete successfully yet the avg_fragmentation_in_percent doesn't change. The table also has a nonclustered index. I've gleaned through all the statements of 'this will have no effect if' but so far I've not spotted a reason why this index won't defrag.The script has been modified to remove schema and database names for the forum.
dbcc showcontig('GRIDINFO','IDX_GRIDINFO1') with tableresults , all_levels
go
DBCC SHOWCONTIG ('GRIDINFO')
go
sp_helpindex 'GRIDINFO'
[code]....
I have a table with clustered index on that. I have only 5 columns in that table. Execution plan is showing that Index scan occurred. What are the cause of the Index scan how can we change that to index seek?
I am giving that kind of similar query below
SELECT @ProductID= ProductID FROM Product WITH (NOLOCK) WHERE SalesID= '@salesId' and Product = 'Clothes '
I need to create a Clustered Index (CI) on a very large SQL Server 2012 database table. This table has about approximately 10 billion rows, 500 GB in size. The job ran for about 20 hours into it and then fails with error: "Out of disk space in tempdb". My tempDB size is 1.8TB, but yet it's still not enough.
Here is my script:
CREATE CLUSTERED INDEX CI_IndexName
ON TableName(Column1,Column2)
WITH (MAXDOP= 4, ONLINE=ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION=PAGE)
ON sh_WeekDT(Day_DT)
GO
I have Enterprise version of SQL Server 2012 & SQL server 2008. I understand that Image/Text/NText is obsoleted and should not be used. That being said I dont understand why I couldnt rebuild the following clustered index, while I could with nonclustered index, this happens on both SQL 2008 and 2012. Here are the DDL.
CREATE TABLE [dbo].[Demo](
[ID] [int] NOT NULL,
[FK_ID] [uniqueidentifier] NOT NULL,
[SomeColumn] [nvarchar](100) NOT NULL,
[Image] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
[code]...
An online operation cannot be performed for index 'IX1_Demo' because the index contains column 'Image' of data type text, ntext, image or FILESTREAM. For a non-clustered index, the column could be an include column of the index. For a clustered index, the column could be any column of the table. If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.
--Online rebuild works fine on non clustered index
ALTER INDEX IX2_Demo ON Demo REBUILD WITH(ONLINE = ON)--It seems to me that some how having the Image datatype column in the table is an issue. eventhough that column is not part of the index.
I have a view that joins a dozen tables with a million rows added per year by an application. I want to materialize it. The view is always filtered by date first on reports, then there are a few key transaction keys, but then many other fields required to make each row unique. I don't want to add these columns since they are large, many, not used for sorting or filtering, and may not define uniqueness in a future application design. I need a uniqueifier that is application agnostic. I prefer a bigint. So to store the materialized view ideally for reporting, I want to add the following clustered index to materialize the view:
CREATE unique CLUSTERED INDEX idx1
ON [dbo].[myview](myDate, key1, key2, key3, id bigint identity(1,1) NOT NULL)
And I get this error:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'bigint'.
Can I do what I want? If so, how?
Users can approach their userprofile on my site using: www.mysite.com/name=peterName is a unique value within my database (db type: nvarchar(50))Now, I have created a clustered index on the username column.However, IMHO its faster to create a clustered index on the (also unique) usercode column since that is of type int.BUT since a user can approach my site based on username I feel that I HAVE to live with this setback in performance....Is that true or is there a better way to solve this issue?
View 1 Replies View RelatedI was under impression that rebuilding index online largely means that the index will remain available for use during rebuild and my procs and query will be able to use it during rebuild. Also my understanding was that table will be locked very briefly while the schema change will be completing.But when I was rebuilding the clustered index online on a large table with some 3 million records, the table got locked and I was not able even to read the data from it for some 5 minutes. Then I cancelled the operation as it was production server and it was one of our main transaction table.
Is rebuilding index online supposed to work this way? The table has no other index.The parameteres I used are:
REBUILD WITH (PAD_INDEX = ON, SORT_IN_TEMPDB = ON, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95)
I have a database where records are Inserted by an external process.
There is no updating or deleting of the data once inserted. The table in
question has a Clustered Index on the Machine_ID (integer) (data is from
manufacturing processes). Each record bears a start and end time. Most
queries involve the Machine, a time span (start time between to points in
time), the Downtime Cause, and the Running Mode.
I want to add an index on the Start Time, the Downtime Cause, and the
Runtime Mode.
My question is: should this new index also contain the Machine_id column
or does the existence of the Clustered Index already on that column negate
its need in the new index?
RC - Dedicated to only creating original mistakes!
I have 5 million rows of table, and going to create Non Clustered Index for Datetime values column. Creating Non clustered Index on Datetime value column will affect performance or not.
View 4 Replies View RelatedWant to check my thinking with you folks...
I have a table with a clustered composite index, consisting of 3 columns, which together form a unique key. For illustration, the columns are C1, C2 & C3.
Counts of distinct values for columns are C1 425, C2 300,000 & C3 4,000,000
C3 is effectively number of seconds since 01/01/1970.
The usage of the table is typically, insert a row, do something else, then update it.
Currently, the index columns are ordered C3,C1,C2. Fill factor of 90%.
My thinking is that this composite index is better ordered C1,C2,C3.
My reasoning is that having C3 as the leading column, biases all the inserts towards one side of the indexes underlying B-tree, causing page splits. Also, there'll be a bunch of "wasted" space across the tree, as the values going into C3 only ever get bigger (like an identity), so the space due to the fill factor in lower values never gets used.
Welcome your thoughts.
I need to find out all the tables in database, which has FK columns and don’t have any Non-clustered index on them.
View 11 Replies View RelatedI understand that minimal logging can occur on a non clustered indexed heap as long as [URL] ...
*not replicated
*tablock is used
*table is empty
The following test seems to contradict this
In the test I create a non indexed heap, insert some record and check the log, then repeat the test on an indexed heap.
The results suggest that even though the conditions for minimal logging into a indexed heap are met, minimal logging is not happening although it does happen on an non indexed heap. What am I doing wrong?
CREATE DATABASE logtest
GO
USE logtest
GO
CREATE TABLE test (field varchar(100))
GO
CHECKPOINT
[Code] ....
I've been asked to look at using Clustered Columnstore indexes for one of my tables. The table contains about 5 million records with about 50 columns. The max field size is a NVarchar(MAX) with max field length currently of about 4k characters. It's only about a gigabyte's worth of data. The table is about 50% R/W operations. Currently, we have multiple indexes with no clustered index due to some performance issues that happened in the past. I've been attempting to determine if it's even really worth it to switch over. I feel that the table is still fairly small with minimal columns and don't believe there will be any noticeable improvement over traditional indexing.
View 3 Replies View RelatedI created composite clustered index key based on Gender and Salary column
The Query executed Successfully and <g class="gr_ gr_135 gr-alert gr_tiny gr_spell undefined ContextualSpelling multiReplace" data-gr-id="135" id="135">i</g>
got composite index key id Gender(-), Salary I <g class="gr_ gr_310 gr-alert gr_gramm undefined Grammar only-ins replaceWithoutSep" data-gr-id="310" id="310">want</g> know why Gender(-) display like this?
And Gender is <g class="gr_ gr_391 gr-alert gr_spell undefined ContextualSpelling ins-del multiReplace" data-gr-id="391" id="391">nvarchar</g> (20)
I would like to put a Clustered Index on a date column in a current heap, but one question/concern.This heap every month has thousands of rows deleted and even more added later. How much of an issue will this cause the Clustered Index as far as page splits? I was thinking Fill Factor of 70%.I would normally just test and still will on Dev box, but my Dev box is much smaller than production as far as power.
View 6 Replies View RelatedI am extremely new to database design, and I ran into a problem that I know comes up often, however has many opinions...
Basically I have a table that is going to have 50+ columns. The natural key on this table is actually 8 columns wide, 4 of them being Varchar columns by default. (varchar(50)'s).
I have added an identity column, (1,1) to the table, however I put the clustered index on the 8 natural keys... My plan is to rebuild the clustered index once nightly when the system isn't in use (after 7 pm).
I know others would say it would be better to have the clustered key on the 1,1 column and then add indexes on the other 8 fields... However I don't quite understand why honestly...
Every single query against this table will use the 8 columns, and will NOT use the Identity column (1,1) because they are calls from other systems that do not know the Identity column....
Therefore if your database is set up for query speed, and every single query has to have a value for 8 columns to get a valid result, does it make sense to put a clustered index over the 8 columns?
If not why? Why is putting a clustered index on an identity column (that will literally never be used in a query) a better solution?
How can we get the list of clustered columnstore index in a database in sql server 2014
View 3 Replies View RelatedI am trying to use an indexed view to allow for aggregations to be generated more quickly in my test data warehouse. The Fact Table I am creating the indexed view on is a partitioned clustered columnstore index.
I have created a view with the following code:
ALTER view dbo.FactView
with schemabinding
as
select local_date_key, meter_key, unit_key, read_type_key, sum(isnull(read_value,0)) as [s_read_value], sum(isnull(cost,0)) as [s_cost]
, sum(isnull(easy_target_value,0)) as [s_easy_target_value], sum(isnull(hard_target_value,0)) as [s_hard_target_value]
, sum(isnull(read_value,0)) as [a_read_value], sum(isnull(temperature,0)) as [a_temp], sum(isnull(co2,0)) as [s_co2]
, sum(isnull(easy_target_co2,0)) as [s_easy_target_co2]
, sum(isnull(hard_target_co2,0)) as [s_hard_target_co2], sum(isnull(temp1,0)) as [a_temp1], sum(isnull(temp2,0)) as [a_temp2]
, sum(isnull(volume,0)) as [s_volume], count_big(*) as [freq]
from dbo.FactConsumptionPart
group by local_date_key, read_type_key, meter_key, unit_key
I then created an index on the view as follows:
create unique clustered index IDX_FV on factview (local_date_key, read_type_key, meter_key, unit_key)
I then followed this up by running some large calculations that required use of the aggregation functionality on the main fact table, grouping by the clustered index columns and only returning averages and sums that are available in the view, but it still uses the underlying table to perform the aggregations, rather than the view I have created. Running an equivalent query on the view, then it takes 75% less time to query the indexed view directly, to using the fact table. I think the expected behaviour was that in SQL Server Enterprise or Developer edition (I am using developer edition), then the fact table should have used the indexed view. what I might be missing, for the query not to be using the indexed view?
We are going to use SQL Sever change tracking. The problem is that some of our tables, which are to be tracked, have no primary keys. There are only unique clustered indexes. The question is what is the best way to turn on change tracking for these tables in our circumstances.
View 4 Replies View RelatedI desire to have a clustered index on a column other than the Primary Key. I have a few junction tables that I may want to alter, create table, or ...
I have practiced with an example table that is not really a junction table. It is just a table I decided to use for practice. When I execute the script, it seems to do everything I expect. For instance, there are not any constraints but there are indexes. The PK is the correct column.
CREATE TABLE [dbo].[tblNotificationMgr](
[NotificationMgrKey] [int] IDENTITY(1,1) NOT NULL,
[ContactKey] [int] NOT NULL,
[EventTypeEnum] [tinyint] NOT NULL,
[code]....
I have created two tables. table one has the following fields,
Id -> unique clustered index.
table two has the following fields,
Tid -> unique clustered index
Id -> foreign key of table one(id).
Now I have created primary key for the table one column 'id'. It's created as "nonclustered, unique, primary key located on PRIMARY". Primary key create clustered index default. since unique clustered index existed in table one, it has created "Nonclustered primary key".
My Question is, What is the difference between "clustered, unique, primary key" and "nonclustered, unique, primary key"? Is there any performance impact between these?