SQL 2012 :: Minimal Logging Insert Statement On Non Clustered Index Table
Jul 9, 2014
I 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
I 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 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 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 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.
Hi All,I have a database that is serving a web site with reasonably hightraffiic.We're getting errors at certain points where processes are beinglocked. In particular, one of our people has suggested that an updatestatement contained within a stored procedure that uses a wherecondition that only touches on a column that has a clustered primaryindex on it will still cause a table lock.So, for example:UPDATE ORDERS SETprod = @product,val = @valWHERE ordid = @ordidIn this case ordid has a clustered primary index on it.Can anyone tell me if this would be the case, and if there's a way ofensuring that we are only doing a row lock on the record specified inthe where condition?Many, many thanks in advance!Much warmth,Murray
I 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,
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?
I have a table (named table1) with 20million rows. It takes around 11 minutes to apply the primary key to this table. There are some tables with over 100 million rows so based on the previous time if my calculations are correct it will take close to an hour apply this primary key for tables with around 100 million rows.
My current solution is to create another table (named table2) with no indexs or primary keys. Pump over only like 5 days worth of data, then apply the primary key. Then have a script that will eventually populate table2 with the rest of the data gradually. When I say gradually I mean like insert like every 100k per hour or something. Keep in mind this table2 is heavily updated with new records.
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?
Hi, I am working on performance issues. We have a database defined as the following. And I would like to know why I am getting the clustered index insert in showplan in profiler for a query?
I have a table with field a,b,c,d,e,f,g,h,,etc. The primary key consists of a combination of field a and field b, which is a clustered index and unique.
There is a computed column that is defined as field k001 that consists of field a and field b combined. This computed column is not persisted. It is not defined as unique even though it is unique.
There is another computed column that is defined as field k005 it consists of field d,e,a,b combined in that order. It is not persisted. It is also not defined as unique, even though it is unique.
There is a non-clustered index on field k001 called k001_c. There is a non-clustered index on field k005 called k005_C.
The following cursor is declared. EXEC SQL DECLARE C_RANGE CURSOR FOR SELECT "A" ,"B" ,"C" ,"D" ,"E" ,"F" ,"G" ,"H" ,"I" ,"J" FROM "TABLE_TEST" WITH (INDEX(K005_C)) WHERE "K005" >= :K1--AREA AND "K005" <= :K2--AREA ORDER BY "K005" ASC END-EXEC
My question is this. This statement when looked at in profiler does a clustered index insert when each row is fetched. It shows in the showplan.xml that this has 60% of the cost involved.
I thought maybe this was because the columns were not persisted. So I persisted these two columns, (by the way just for other information there are other computed columns in this table that are not persisted, and use some of the same original fields). I then defined a temp table using the same table defination for the original table. I persisted the two columns, then did a select into the temp table( with identity insert off). I then deleted the original table, and renamed the temp table to the original table.
I seemed to still get the clustered index insert. I do not remember if the cost showed up the same. My question is this. Why am I getting the clustered index insert on a query?
Hi everyone, When we create a clustered index firstly, and then is it advantageous to create another index which is nonclustered ?? In my opinion, yes it is. Because, since we use clustered index first, our rows are sorted and so while using nonclustered index on this data file, finding adress of the record on this sorted data is really easier than finding adress of the record on unsorted data, is not it ??
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.
- 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.
I 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.
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)
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'
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'.
I am having 2 tables one is staging temp and another is main import table.
In my staging table there are 3 column Col001,Id,Loaddate
in Col001 column data are present with '¯' delemeter.
I am having function which is used to load data from staging to import table using one function.
this function create a insert statement.
My Existing function
-- Description: To Split a Delimited field by a Delimiter ALTER FUNCTION [dbo].[ufn_SplitFieldByDelimiter] ( @fieldname varchar(max) ,@delimiter varchar(max) ,@delimiter_count int
[Code] ....
I am unable to get correct statement with above function.
I already posted this over on sqlteam so don't peek there if you haven't seen that post yet. :)
So now to the question:
Anyone care to guess how long it took me to build a clustered index on a table with 900 million rows? This is the largest amount of data in a single table I have had to work with thus far in my career! It's sorta fun to work with such large datasets. :)
Some details:
1. running sql 2005 on a dual proc 32bit server, 8gb ram, hyperthreaded, 3ghz clock. disk is a decent SAN, not sure of the specs though.
2. ddl for table:
CREATE TABLE [dbo].[fld]( [id] [bigint] NOT NULL, [id2] [tinyint] NOT NULL, [extid] [bigint] NOT NULL, [dd] [bit] NOT NULL, [mp] [tinyint] NOT NULL, [ss] [tinyint] NOT NULL, [cc] [datetime] NOT NULL, [ff] [tinyint] NOT NULL, [mm] [smallint] NOT NULL, [ds] [smallint] NOT NULL )
3. ddl for index (this is the only index on the table):
CREATE CLUSTERED INDEX [CIfld] ON [dbo].[fld] ( extid asc )WITH (FILLFACTOR=100, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
4. extid column was not sorted to begin with. ordering was completely random.
Note that I have changed the column names, etc, to protect the innocent. I can't go into details about what it's for or I'd be violating NDA type stuff.
We are trying to load flat text files with upwards of 7 million records into a table on SQL. The table has a clustered index on 3 fields. We setup the indexes prior to importing the data. We are sometimes able to complete smaller tables (500,000-750,000 records), however when we try the larger tables an error occurs :
Error at Destination for row number 6785496. Errors encountered so far in this task: 1
Location: somerge.c:1573 Expression: mrP->mrStatus!=MERGERUN::NONE SPID: 11 Process ID: 173
The destination row number is the same number as the total number of rows that we are trying to load.
None of the recods end up importing. The row number it gives is always the total number of records that was in the text file I was trying to import. I tried to import the text files first and then build the clustered indexes but a table with only 300,000 records ran for nearly 4 days without completing before we killed it. Be for we try to load the file we always delete whatever is there. Some of the files that we try to load are new and we have to set up the indexes from scratch. We are using a DTS wizard. Someone told me to find a way to get it to commit every 1000 or so but I can't find a way to do it. I looked and looked but can't find it !!!
Hi all as i remember i had read in Books Online that on each Table in Sql Server we can create only one Clustered index but today suddenly i create another clustered index on a table without any Error from SQl server !!! BUT my query Order changed to the order of this newly created index. could anyone elaborate on this issue?
I have a table "Client" that has two columns: "ClientID" and "ProductID". I created on clustered index on ClientID and when I opened the table in the management studio, I saw the table was in the order of ClientID.
Then I added another non-clustered index on ProductID. When I open the table again, it is in the order of ProductID. Shouldn't the table always be in the order of clustered index? Non-clustered index should be a structure outside of the table itself? Did I do anything wrong?
I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).
Each row will have the same item, but with a different task type.ie.
I am trying to create a temp table with a non-clustered index.
Originally I tried to create the index after I created the table.
This seemed to work fine, so I added my stored procedure to our Production environment.
However, when two users called the stored procedure at once I got the following error:
There is already an object named 'IX_tmpTableName' in the database. Could not create constraint. See previous errors.
I then found that SQL Server does generate unique names for the temp table but not all the objects associated with the temp table if they are explicitly named.
This is easy enough to solve for a PRIMAY KEY or UNIQUE constraint because the do not have to be named.
Is there a way to create an non-clustered index on a temp table without naming it?
