*** Skip all and go to last 2nd para for main question ****
We have our custom ETL tool that loads data in SS 2005 Enterprise Vesion using BCP. We use '-h "TABLOCK"' table hint and '-b 10000' batch size options.
Prior to 2005 Enterprise version (2K and 2K5 Standard Versions), we use to maintain around 10 DBs with same schema/sps and views in each of the dbs.
Now, in Enterprise version we are maintaining one DB where all tables are partitioned by one column.
Multiple instances might run from different workstations. Here, I want to clarify you that no instance will load data pertaining to another partition.
But at the same time there will be other components running that will query the tables in a SELECT sql and all tables have NOLOCK table hint.
There can be multiple instances of this component running from different workstations. Again, none of the instance will not access data pertaining to another instance (including the component that is loading data)
I have no clue why we some times get deadlock issues when we run multiple instances of custom ETL tool for loading data and other component that just has select only sql.
More over I get deadlocks many times only in BCP, this is a puzzle to me because i use NOLOCK table hint. This is the only place where table is populated by data and data in table neither updates or deleted.
Can some one tell which of these below concurrent transactions will get conflicts and leads to dead lock?
bcping data into table of partition A
bcping data into table of partition B
deleting few rows of data from table of partition E
updating few rows of data from table of partition F
querying table of partition C
querying table of partition D
During a load test , we found a procedure that was causing a deadlock. In the profiler deadlock graph both the process that blocked as well as the victim where having the same object id of the procedure.
I used WITH (TABLOCK) hint and now I'm not finding the deadlocks.
Is this a right solution ? Will this affect the functionality ?
Code piece inside the procedure when it caused deadlocks
INSERT INTO Table1 SELECT @Col1, @Col2, @Col3, @Col4
UPDATE Table1 SET Col5 = @Col5 WHERE @Col1 = @Var
UPDATE Table2 SET Col = @ColVal
Code piece changed now with WITH (TABLOCK)
INSERT INTO Table1 SELECT @Col1, @Col2, @Col3, @Col4
UPDATE Table1 WITH (TABLOCK) SET Col5 = @Col5 WHERE @Col1 = @Var
We currently have a large ETL import each night. There are lots of tables and some are quite large. If there is a problem with the nightly import, it is rerun during the day while users are running reports. The only updates to the tables is the nightly import.Currently the import does a BULK Insert and several INSERT INTOs. We are going to try to improve performance by adding a WITH (TABLOCK). But wonder if the TABLOCK will cause more locking, less locking, or be about the same. We do not have transactions batched. We insert all the records for a table in a single transaction.Â
Have found these references regarding TABLOCK, but I am confused what they mean, and how they differ from doing a BULK INSERT or INSERT INTO without TABLOCK.
TABLOCK - Specifies that a shared lock is taken on the table held until the end-of-statement. [URL] .... Using INSERT INTO…SELECT to Bulk Load Data with Minimal Logging [URL] .... Lock Modes [URL] ....
I've been reading about the "table lock on bulk load" option and TABLOCK hint.
So my understanding is by default only row locks are taken out and other queries can read/write data while the bulk load is going on. However if you were doing parallel bulk loads with overlapping keys from a clustered index then they may block each other.
But if the option is enabled, you can do the parallel bulk loads without blocking because a table lock is taken out, however, other processes couldn't read/write the data until they're all done.
Is that the gist of it? I think I got confused by some misinformation. Don't all those row locks eventually likely escalate to a table lock anyway though?
Is possible to use like hint with subselect? , i mean i want to find all rows in table A that contains a word in a field(CALLED CONTENT) in table B, concretely in a field called content too, i show you the idea although the syntax is incorrect.
select ' + char (39) + @country + char (39) + ' as PAIS, A.ID, A.IDUSUARIO MSISDN, NULL AS MSISDN_COD, convert(char(19),A.FECHA_ALVENTO, 121) AS FECHA_MO_LOCAL, NULL AS FECHA_MO_LOCAL_D,
Background:I am currently working on a mission critical web based applicationthat is accessed 24 hours a day by users from just about every timezone. We use MS SQL Server as our database and we have lots ofproblems with time-outs. We used to have lots of problems with locksuntil my management decided that we would use the WITH (NOLOCK) hinton EVERY select statement and WITH (ROWLOCK) on EVERY updatestatement. I have argued since the beginning that the NOLOCK hintshould be the exception and not the rule. Meanwhile we continue tohave problems related to time-outs.Problem:I'm the one that they call when there are time-out errors.I am a programmer first and a DBA when I have to be. I'd really liketo hear from some of you who are the opposite. I realize that thereare many factors that contribute to slow response from a databaseserver (indexes, RAM, disk speed, etc.), but what I really need tohear from an expert is whether or not using NOLOCK on **EVERY** queryin a 30GB database that has 344 tables is a bad idea.Thanks in advance,Stephen McMahonJoin Bytes!
Hi all If i have a view: CREATE VIEW vw_Users AS SELECT * FROM Users WITH(NOLOCK)
Is it suggested to use nolock in views? And if i needed to use this view in stored procs is it then suggested to apply the nolock hint? CREATE PROC [dbo] .[usp_GetCompanyUsers] AS SELECT * FROM Companies WITH(NOLOCK) JOIN vw_Users WITH(NOLOCK) --<< ---is this suggested?
Is possible to use like hint with subselect? , i mean i want to find all rows in table A that contains a word in a field(CALLED CONTENT) in table B, concretely in a field called content too, i show you the idea although the syntax is incorrect.
select ' + char (39) + @country + char (39) + ' as PAIS, A.ID, A.IDUSUARIO MSISDN, NULL AS MSISDN_COD, convert(char(19),A.FECHA_ALVENTO, 121) AS FECHA_MO_LOCAL, NULL AS FECHA_MO_LOCAL_D,
Hi All,I have a question about lock hint for you :If the first user currently run a select commandwith share lock and hold it. What kind oflock (lock hint) should be used by the second userin the select command (from the same table) so thatthis command will wait until the first user releasesthe lock ?I have tried using tablock, tablockx, xlock andupdlock hint in the select command for the seconduser, but it is not successful. Below is myunsuccessful test :The first user :Begin Transactionselect fprefix from ut1 where fprefix = '000'The second user :select * from ut1 with (tablockx)Please help meThanks in advanceAnita Hery*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Can I use the NO Lock hint when running a SELECT against a view that points to another server? Will the no lock "travel" to the other server through the view?
I need to get back the actual characters (without trailing blanks) contained in a char(43) field. So i tried: - substring(fieldname,1,len(fieldname)) which yields a 43 char column - using a variable that contains the actual length in substring(fieldname,1,@nchar)) the same. - tricking by concatenating the resulting string with a dummy like "" didn't work either. So apparently I am at a dead end. Anybody a clue, hint or solution???? Thanks to all contributors
Is it possible to place an exclusive row lock when running a SELECT query by using a lock hint (or otherwise).
Basically, when a select statement is run against a table I don't won't any other users to read that row until it has been updated - at some later stage.
Any suggestions on whether this is possible would be welcome.
I want to implement the following pattern (pseudo-code follows):
[for index in x different databases, where x is rather big]
create database MyDatabase_#{index}_Temp launch a dedicated ssis package on this database if I detect no error, proudly rename MyDatabase_#{index}_Temp to MyDatabase_#{index}_Last_Known_Good (drop the last_known_good first if it exist)
Later on, a consolidation process collect the data in Last_Known_Good versions of the databases.
My question: what is the simpliest way to achieve the rename operation (step 3) ?
I have tried an "alter database #{old_database_name} modify name = #{new_database_name}", but it works only once: once renamed, the database keeps the old mdf and ldf filenames, and the next create database will choke on this.
I have also tried to rename the logical filenames with ALTER DATABASE XXX MODIFY FILE (NAME = YYY, NEWNAME = ZZZ) but it doesn't work either.
I'd like to avoid specifying absolute mdf and ldf filenames myself, is it possible ?
An interesting discussion yesterday. One of the programmers asked about the use of the NOLOCK optimizer hint with an iterator table aka table of numbers. His comment was that this optimizer hint was not efficient. Rather than give a knee-jerk response I thought it would be better to ask. The main circumstance is that the iterator table is completely static with a fill factor of 100%. My purpose is to eliminate lock contention if I can.
Are there reasons to not use the NOLOCK hint in this case to potentially improve performance?
My God! What happens if I miss a step, or put it in a different order??? I know I can specify ORDER BY StepID, but what about a missing step? I do have "missing ranges" script, but posting questions filtered through the script process may become a full-time job in itself...What to do, what to do...
Hi All,I want to lock 2 tables on 2 servers using TABLOCKXhint. These tables function as semaphores in myapplication. It means when the tables are lockedthen other users will not be able to access themand automatically they can not continue their works.I have tried using the following code, but itdoes not work. I always got the error :"Cannot specify an index or locking hint fora remote data source" on the select command.begin transelect * from server1.accounting.dbo.semtabwith (tablockx) where fprefix = '000'select * from server2.accounting.dbo.semtabwith (tablockx) where fprefix = '000'--commit tranThe error will disappear if I remove the servername, like this :select * from accounting.dbo.semtabwith (tablockx) where fprefix = '000'But, it does not meet my requirement.It only locks one table on one server(default).Please help meThanks in advanceJohn S.*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
Hello! I would like when I compare query plans to be able to compare 2 queries where the 1. is the "normal" version and the 2. the version where I forced compiler not to use a specific index (i don't want to force at table scan, so hint index(0) can't be used).
The only way I see how I could achieve something similar is to to drop the index and compare response time before and after. But building index could be time consuming...
Is it possible?
select col42 from tbl; select col42 from tbl with...
I've got a dilemma which I hope someone has a solution to.
Let's say we're building a data mining model to predict aircraft reliability. In the training table we've got a column (among many others) with a unique aircraft ID, and then a column for the type (737,747) and then a column for the series (100,200,300). I.E. A 737-800 series would be "737" and "800".
There is in essence a parent-child relationship between these 2 columns. 737's should share a common set of reliability factors, and then those factors might be further defined by the series number (for instance, the 737 might have very reliable radar except for the 500 series). The series is analogous to what model year a car is. What I want to make sure doesn't happen is for the system to correlate a 747-400 and a 737-400 because they are the same series. They are totally independent if the model number is different.
My only idea was to merge the columns and have a single value "737-100". But it would seem then that the model won't have any idea that a "737-100" and "737-200" should have a lot more in common than a "737-100" because the values will be completely different.
I was hoping to find some sort of parent-child hint in the column properties but found none.
What solutions have other people tried? It sure seems that there should be an elegant solution for something like, but I'm missing it.
we have two SqlServer 2005 in cluster. The machines act very slow (although the CPU load is low) as soon as we achieve one of these operations: - drop / create database - restore database
We achieve those operations through the SqlServer Browser or via sqlcmd. Everything else is running smoothly.
Is there any known reason on why it can be so slow ?
best regards
Thibaut
(hope this is not too OT, but could not find any cluster-specific forum).
I am setting up filtered replication(tran repl) for a table that is 2+ TB in size. The filtered rows are less than 1% of total size.When I run the snapshot, sometimes it complete in 27 mins(I have already optimized the snapshot agent profile) and sometimes it runs for ever. Mostly it runs forever, so the chances of it finishing soon are really very less.I have infact created filtered index just to cater to the query in the filter i.e. on the filter predicates. The reason its flipping flopping is due to the incorrect plan that it chooses. I can force the right index and the filtered query runs just fine but. I cannot use the query hints when I am adding the filter in replication as its not allowed.
I then went ahead and created the plan guides to force the best plan. But the plan guide was not used because even if there is a single space in the actual query that replication runs in the background the plan guide will not be used(testing on plan guides has proved this). The query needs to be ditto same including whitespaces and carriage returns. I then ran profiler and found that there is a view that replication creates and executes to extract the filtered rows and BCP it. Got the text of the view and created the plan guide on it but it was still not used because sp_helptext output seems to be different than what is actually being fired by replication internally.
Further, I collected the verbose log with level 2 and tried to find out if the main query shows up there with no success.
(a) I can't use the plan guide because I do not know the exact syntax(I mean I have the query but I do not know the white spaces, carriage returns, etc) and
(b) I cannot use query hint while filtering the article.
1. I cannot afford to have downtime. 2. I cannot use backup and restore option either.
I am stuck with that error when running my ASP.NET application on my production server for many weeks now and still don't have a complete solution to that problem.
I formerly posted that thread and read that other thread without success, so as I have made a few progress in delimiting the problem I am posting this new thread with the hope somebody will finally be able to help me.
To summarize:
1) I have developed an ASP.NET application, - on a Windows XP Pro SP2 computer - with VS studio 2005 Dev and included SQL 2005 engine - this application works fine on the dev computer
2) I have deployed the application on a production server which is: - a Windows 2000 SP 4 computer - with SQL Express SP1 with Advance services installed
When I launch the prod application I have this error: Login failed for user xxxxASPNET
I have found that I can bypass this error by doing the following: 1) manually attach my .mdf database with SQL Server Management Studio Express and specifying the field Attached as with myDatabaseName (in place of the default fullPhysicalPath/myDatabaseName.MDF) then detach it 2) change the auto generated connection string in web.config file from <add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|myDatabaseName.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/> To <add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|myDatabaseName.mdf;User ID=sa;Password=mySAPassword;database=myDatabaseName" providerName="System.Data.SqlClient"/>
That would have been an acceptable substitution solution but unfortunately my application also uses the new Login and Webparts features of VS 2005 which implies the automatic creation of an ASPNETDB.MDF database by VS. As I have no mean (or at least don't know how) of modifying the associated Connection String for that database (it does not appear in web.config) I cannot bypass the problem for it and thus the error still appears when the application try to use this database.
Please I really need help on this matter, I can provide any other useful information as needed (logs, sources ...)
Our system is reasonably complex with a lot of non-trivial stored procedures. As the load on our DB increased we're now getting more and more deadlocks (10 per day or so from about a million stored proc executions).
We try to avoid transactions where we can, and we do attempt to optimse stored procs to steer clear of deadlock conditions, but with the sheer number of stored procedures we can't possibly avoid all deadlock conditions.
One solution I'm considering is to re-run stored procs that failed because of a deadlock. In the .net code we'll run the stored proc, check for a deadlock error and if one happened, wait 100ms and try again.
we have a production inviremont that is running for about 10 months. Since a couple of weeks we are having problems with "Deadlocks".
This cant be due to an increase in data size on the tables that are having the issues because these are cleaned in the same transaction that populates them.
These tables are used to store temporary data that the production system needs to calculate the correct price for any given order. This transaction takes between 0.5 to 1 second to commit.
We are running on a dual processor machine with 1 Gb of RAM with SQL Server 7 - sp 3, Windows NT 4 sp 6, Microsoft Transaction Server.
In all our queries and stored procedures we use the optimizer hints (nolock) for select statements and (rowlock) for updates or deletes.
Is there any way to totally avoid deadlocks. In some critical applications we have removed transactions entirely, counting on other means to maintain database consistency. We still get deadlocks in this area. These are mainly inserts, and the only thing I can think is that updates to the indexes are causing multiple page locks which result in deadlocks. Is this true?
Will deadlocks be eliminated in 7.0 with row level locking for this situation? Or will index page splits still cause a possibility of deadlock contention?
I have a problem with a SP in 6.5. When i try to run a Stored Proc which is a simple select statement dumped into a temp table in a particular database, I lock other users who are tring to log into other databases some in tempdb database. When i try to kill the process the rollback takes almost 45 mins or so..till then no one can log on to the server.
The SP works fine when no one is logged into the Great Plains server. One more thing i observed is that, the SP when run results on a deadlock only when the owner is a user. If the owner is DBO it works fine.
I've got a deadlock problem. The log below has been generated. The problem is that during one day, I have more than 300 deadlocks like it. Before, the were not so many deadlocks. During past year, the number of users has grow (from 100 before to 500 or 700 now)
*** Deadlock Detected *** - Requested by: SPID 360 ECID 0 Mode "S" - Held by: SPID 113 ECID 0 Mode "S" Index: aaaaa_PK Table: TABLE_1 Database: MYDB == Lock: KEY: 22:325576198:1 (ff009ae5078d) - Requested by: SPID 113 ECID 0 Mode "S" - Held by: SPID 374 ECID 0 Mode "X" Index: aaaaa_PK Table: TABLE_1 Database: MYDB == Lock: KEY: 22:325576198:1 (ff009ae5078d) - Requested by: SPID 374 ECID 0 Mode "IX" - Held by: SPID 360 ECID 0 Mode "S" Table: TABLE_2 Database: MYDB == Lock: PAG: 22:1:2428 == Deadlock Lock participant information: Input Buf: S E L E C T the_rest_of_the_query SPID: 360 ECID: 0 Statement Type: UNKNOWN TOKEN Line #: 1 Input Buf: s p _ e x e c u t e 8 Input Buf: s p _ c u r s o r 8À B 8 8f ç @ Table I Input Buf: S E L E C T the_rest_of_the_query SPID: 360 ECID: 0 Statement Type: SELECT Line #: 1 == Session participant information: == Deadlock Detected at: ==> Process 360 chosen as deadlock victim
I have done : - rebuild indexes on all tables (fillfactor 90) - analysed memory activity
Could a lack of memory be at the origin of the problem ? Which counters in perfmon are significant for memory lack ?
Could the index fill factor could be at the origin of the problem ? At time, it is at 90 percent.
Config : Winnt4 Server, MS-SQL 7 SP4 , 2 GB of RAM , 2 x Xeon 700
I have an application built on top of a questionable DB design which requires overcomplicated selects. The application is experiencing deadlocks regularly, in some cases with only one concurrent user.
I set the trace flag 1204 but am not seeing anything in the Error.log and I initiated a trace in profiler which does not seem to show any deadlock. Despite having recreated the problem which show my browser hanging indefinitely. When I run the following queries:
SELECT spid, waittime, lastwaittype, waitresource FROM master..sysprocesses WHERE waittime > 10000 AND spid > 50
SELECT spid, cmd, status, loginame, open_tran, datediff(s, last_batch, getdate ()) AS [WaitTime(s)] FROM master..sysprocesses p WHERE open_tran > 0 AND spid > 50 AND datediff (s, last_batch, getdate ()) > 30 ANd EXISTS (SELECT * FROM master..syslockinfo l WHERE req_spid = p.spid AND rsc_type <> 2)
I get:
55860978LCK_M_XPAG: 13:1:2573
54AWAITING COMMANDsleeping sa 11499 55UPDATE sleeping sa 21499
We have a SQL 2005 transaction database server that suddenly started to issue deadlock errors last week on most of the databases on that server and a lot of timeout errors. Before that, that database server performed very well and timeouts were minimal to zero. I am not sure what changed for it to have these performance problems.
The only major change we did was to convert several varchar columns to nvarchar in several tables (as part of internationalization initiatives). We did not modify the procs from varchar to nvarchar though but would be doing that phase by phase.
There is also one proc in which we used the snapshot isolation level of sql server 2005. These are only 2 major changes done within the past 2 weeks. Would these be the cause for these deadlocks and timeouts on our web-based application?
