Update Statement Performing Table Lock Even Though Where Condition On Clustered Primary Index?
Jul 20, 2005
Hi All,
I have a database that is serving a web site with reasonably high
traffiic.
We're getting errors at certain points where processes are being
locked. In particular, one of our people has suggested that an update
statement contained within a stored procedure that uses a where
condition that only touches on a column that has a clustered primary
index on it will still cause a table lock.
So, for example:
UPDATE ORDERS SET
prod = @product,
val = @val
WHERE ordid = @ordid
In 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 of
ensuring that we are only doing a row lock on the record specified in
the where condition?
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,
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.
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?
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 want to keep certain archive data in certain tables. One such table is currently about 190 GB in size. It has a primary key with clustered index and three non-clustered indexes. The type of queries fired are strictly selects (daily) and inserts (only monthly).
Question: Is it advisable to have a non-clustered index on the primary key column?.....I am finding that the insert performance is getting hurt due to presence of clustered index on such a large table (190 GB).
I have created a very simple table. Here is the script:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IndexTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[IndexTable]
GO
CREATE TABLE [dbo].[IndexTable] ( [Id] [int] NOT NULL , [Code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CusteredOnCode] ON [dbo].[IndexTable]([Id]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[IndexTable] ADD CONSTRAINT [PrimaryKeyOnId] PRIMARY KEY NONCLUSTERED ( [Id] ) ON [PRIMARY] GO
The records that i added are:
Id Code
1 a 2 b 3 aa 4 bb
Now when i query like
Select * from IndexTable
I expect the results as:
Id Code
1 a 3 aa 2 b 4 bb
as i have the clustered index on column Code.
But i m getting the results as:
Id Code
1 a 2 b 3 aa 4 bb
as per the primary key order that is a non clustered index.
I am trying to drop a primary key on column LID and then create a clustered index on a new identity column ID and then add the primary key back on the LID. I am not able to do so due the table being in replication. here is the error:
Cannot alter the table '' because it is being published for replication.
How do I get past the error and create the Clustered Index on ID column in both publisher and subscriber?
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?
We have a table, which has one clustered index and one non clustered index(primary key). I want to drop the existing clustered index and make the primary key as clustered. Is there any easy way to do that. Will Drop_Existing support on this matter?
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 need to search for such SPs in my database in which the queries for update a table contains where clause which uses non primary key while updating rows in table.
If employee table have empId as primary key and an Update query is using empName in where clause to update employee record then such SP should be listed. so there would be hundreds of tables with their primary key and thousands of SPs in a database. How can I find them where the "where" clause is using some other column than its primary key.
If there is any other hint or query to identify such queries that lock tables, I only found the above few queries that are not using primary key in where clause.
Update WACTS_Inventory_Part_Loc_Tbl SET WIPLT_Part_New_Qty = WIPLT.WIPLT_Part_New_Qty + tmp.MATIN_QTY FROM
WACTS_Inventory_Part_Loc_Tbl WIPLT
INNER JOIN
Temp_MatIn_Data tmp
ON
WIPLT.WIPLT_Part_Number_MSBA=tmp.PARTS_ShortID
WHERE
WIPLT.WIPLT_Location_Code='MF'
I have a Problem with this satment becoz my Temp_Matin_Data_Tmp table contains two records with the same PartNumber that is (ShortId) and the two records have two different quantities.
The WACTS_Inventory_Part_Loc_Tbl has only one Part NUmber Record and the key is the PartNUmber and Location_Code.
Now when the Update Satement runs it updates the qty only from the first record of the temp table and does not update the quantity of the second record from tne source table.
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.
I have a very large table with approximately 400 million records in it. Every 10 seconds approximately 150 insert are done on the table. I am attempting to rebuild one of the indexes (non-unique, non-clustered). But when I run a script to rebuild the index online (i have enterprise edition) the VB.NET service that is attempting to insert generates SQL timeout errors (timeout set to 30 seconds). From an article on msdn they state that long term table locks are not held for the duration of the index operation. So what am I missing because I am not close to being a DBA. I know SQL Server is not a SCADA but it is not my choice.
Here is the script for one of the rebuilds USE [DATABASENAME] GO ALTER INDEX [IX_REALLY_BIG_TABLE_DT] ON [dbo].[REALLY_BIG_TABLE] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON ) GO
Here is the article http://msdn.microsoft.com/en-us/library/ms188388.aspx
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 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?
My environment is SQL 2000. I have a table with 500 million rows. The table is consistently getting updated and inserted. I can not take the table offline. My clustered index needs to be rebuilt due to decreased performance. How do I accomplish this?
I have a really super slow stored proc that does something simple. it updates a table if certain values are received.
In looking at this the matching is done on the Primary Key, which is set as a Clustered index, looking further I have another constraint, that sets the same column to a Unique, Non-Clustered.
I am not sure why this was done, but it seems to be counter productive. I have read only references to Which one is better on a primary key, but not can their be both and if it is "Smart".
I want to change Set clause of Update Statement dynamically based on some condition.
Basically i have 2 Update statments having same FROM clause and same JOIN clause.
Only diff is SET clause and 1 Where condition.
So i am trying to combine 2 Update statements into 1 and trying to avoid visit to same table twice.
Update t Set CASE **WHEN Isnull(td.IsPosted, 0) = 0 THEN t.AODYD = td.ODYD** *ELSE t.DAODYD = td.ODYD* END From #ReportData As t Join @CIR AS tmp On t.RowId = tmp.Max_RowId
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 three sprocs and three tables. I was told to use a clustered index in the first table and a unique clustered index on the second table. I never asked about the third table and the person I need to ask is on vacation. Most of the contents of the first table will be joined with all of the contents of the second table into the third table. Do I need to have a unique clustered index on the third table too?
The clustered index in the first sproc is on a unique key that I had created using by concatenating several columns together.
CREATE CLUSTERED INDEX IX_UNIQUE_KEY ON MRP.Margin_Optimization_Data (UNIQUE_KEY); CREATE NONCLUSTERED INDEX IX_DATE ON MRP.Margin_Optimization_Data (PERIOD); CREATE NONCLUSTERED INDEX IX_ODS_ID ON MRP.Margin_Optimization_Data (GL_SEG1_COMPANY_ODS_ID, GL_SEG2_PROFIT_CTR_ODS_ID, GL_SEG3_LOB_ODS_ID, GL_SEG4_PRODUCT_DEPT_ODS_ID, GL_SEG5_ACCOUNT_ODS_ID);
The second sproc with the unique clustered index is on the unique key from the first table and a date attribute.
CREATE UNIQUE CLUSTERED INDEX IX_UNIQUE_KEY ON MRP.[MGN_OPT_KPI_SOURCE] (UNIQUE_KEY, PERIOD);
In the third sproc, I'll have a nonclusted index on the ODS_ID attributes, but I'm unsure of how to go about the clustered index situation.
CREATE NONCLUSTERED INDEX IX_ODS_ID ON MRP.MGN_OPT_KPI_VALUES (GL_SEG1_COMPANY_ODS_ID, GL_SEG2_PROFIT_CTR_ODS_ID, GL_SEG3_LOB_ODS_ID, GL_SEG4_PRODUCT_DEPT_ODS_ID, GL_SEG5_ACCOUNT_ODS_ID);
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)
