HELP: Strange Blocking Performance Problem With Simultaneous Queries
Dec 1, 2005
Hello everyone, I am hoping someone can help me with this problem. I
will say up front that I am not a SQL Server DBA, I am a developer. I
have an application that sends about 25 simultaneous queries to a SQL
Server 2000 Standard Edition SP4 running on Windows 2000 Server with
2.5 GB of memory. About 11 of these queries are over views (all over
the same table) and these queries are all done from JDBC but I am not
sure that matters. Anyway, initially I had no problem with these
queries on the tables and the views with about 4 years of information
(I don't know how many rows off hand). Then we changed the tables to
replicated tables from another server and that increased the amount of
data to 15 years worth and also required a simple inner join on 2
columns to another table for those views.
Now here is the issue. After times of inactivity or other times during
the day with enough time between my test query run I get what looks
like blocking behavior on the queries to the views (remember these all
go to the same tables). I run my 25 queries and the 11 view queries
all take about 120 seconds each to return (they all are within
milliseconds of each other like they all sat there and then were
released for processing at the same time). The rest of the queries are
fine. Now if I turn around and immediately run the 25 queries again,
they all come back in a few seconds which is the normal amount of time.
Also, if I run a query on one of views first (just one) and then run
the 25 queries they all come back in a few seconds as well.
This tells me that some caching must be involved since the times are so
different between identical queries but I would expect that one of the
queries would cache and thus take longer but the other 10 would be
fast, not all block for 2 minutes. What is more puzzling is that this
behavior didn't occur before where now the only differences are:
1) 3 times more data (but that shouldn't cause a difference from 3
seconds to 120 and all tables have been through the index wizard with a
SQL trace file to recommend indexes)
2) There is now a join between 2 tables where there wasn't before
3) The tables are replicated throughout the day.
I would appreciate any insight into this problem as 120 seconds is way
too long to wait. Thanks in Advance.
Chris
View 1 Replies
ADVERTISEMENT
Oct 22, 2007
Hi,
I have an application resource that is accessible from multiple clients applications that all have access to a common SQL Server db. By this, I mean, multiple clients can potentially connect to this resource at any given time. I want to limit the usage of this resource to one client at a time and was thinking about simply using SQL to maintain the name of the client that currently has "control" of the resource.
My question pertains to the case where two clients are executing a SQL query, simultaneously, to check whether someone has control of the resource or not. If two users were to simultaneously execute this query, do i have to lock the table in order to ensure that both users are not viewing data in an incorrect state? Or, does SQL Server ultimately execute the queries in a specific order? Meaning, the requests are simultaneous from a human standpoint but one request may have actually come in a millisecond before the other one and SQL server executes that query first, and then the second query. Is this how it works?
Thanks,
Paul
View 2 Replies
View Related
Jan 26, 2008
I am running MS SQL 2000 server. The table involved is only about10,000 records. But this is the behavior I am seeing.The local machine is querying the table looking for a particularrecord. Everything works fine.The remote amchine goes to clear the table and then insert all 10,000records, into the table the following happens.1) the local machines queries do not compilete until the remotemachine is done.2) the remote machine can take up to 6 minutes to do these 10,000insert operations. So nothing on the local machine works right forthese 6 minutes.I do not have access to the remote machines source to see what isrunning but I am told it is simply a for loop with a insert query init. Nothing of a locking natture.Any idea the types of things I should look for to track this down? Ifound this by doing SQL profiler profiling and finding the remoteoperations. Turn these operatiiosn off and the local machine worksfine again, with no other action.Thanks,David
View 4 Replies
View Related
Feb 3, 2015
At one of your client sides we have configured Always on with synchronous mode.Also we have schedule rebuild index and update statistics job which runs in night every alternate day. the issue is there are more then 100 sleeping queries which is blocking update statistics job.
I have to stop update statistics job manually once i come to office manually.
Once I have killed blocking sleeping query but then other sleeping query blocked it and so on.
View 4 Replies
View Related
Jul 20, 2005
I have a strange performance question hopefully someone can clarifyfor me. I take a production database and make a copy of it, calledtest, on the same instance on the same server both running at the sametime. All the same things are running for each database and no one isusing each database but me. From Query Analyzer I run a SQL againstproduction three times in a row and it takes 1 minute 40 seconds onthe last/best run. I then run the same SQL against the test copy andrun it three times in a row and the last/best time is 12 seconds. Cananyone explain this behavior? If so, I hope this points to something Ican do to my production database to get the same performance.Thanks,MGB
View 5 Replies
View Related
May 12, 2006
Hi,
I have a strange performance issue. I have the following query which takes 40+ seconds to run. SELECT Count(x)
FROM view WHERE x = 347
AND y = 10056
AND z = 2
AND w = '01'
But if i switch it to below, the query returns the one result quickly (1 second).SELECT x
FROM view WHERE x = 347
AND y = 10056
AND z = 2
AND w = '01'
If the view is returning results quickly, why is it so much trouble for SQL to run the aggregate function on the results?Any help is appreciated. -Brian
View 3 Replies
View Related
Nov 28, 2005
Hi,I have a really interesting one for you guys...SQL Server 2000 sp3 on Windows Server 2003If I run this query:declare@find varchar(50)SET @find = 'TTLD0006423203'SELECT TOP 250ConsignmentID,c.CreatedFROM tblConsignment c WITH (NOLOCK)WHERE c.ConNoteNum LIKE @find + '%'ORDER BY c.Created DESCIt takes 5 - 7 seconds with an Index Scan on the Consignment TableHOWEVER, if I run either of the next two queries below they are instant(under 1 second) with no scan only an Index Seek ..declare@find2 varchar(50),@SQL nvarchar(4000)SET @find2 = 'TTLD0006423203'SET @SQL = 'SELECT TOP 250ConsignmentID,c.CreatedFROM Tranzbranch_archive.dbo.tblConsignment c WITH (NOLOCK)LEFT JOIN tblCustomer cu WITH (NOLOCK) ON c.FreightPayerCode =cu.CustCodeWHERE c.ConNoteNum LIKE ''' + @find2 + '%''ORDER BY c.Created DESC'execute sp_executesql @stmt = @SQLORSELECT TOP 250ConsignmentID,c.CreatedFROM tranzbranch_archive.dbo.tblConsignment c WITH (NOLOCK)LEFT JOIN tblCustomer cu WITH (NOLOCK) ON c.FreightPayerCode =cu.CustCodeWHERE c.ConNoteNum LIKE 'ttld0006423203%'ORDER BY c.Created DESCCan you please help me as this is causing Huge issues in our Live systemand I really don't want to rewrite 400+ stored procedures!!!!Thank you thank you thank you in advance....:-)Auday*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies
View Related
Apr 21, 2008
Dear All,
I got a strange performance issue with my existing application. The application run fine for a period of time. However, it got timeout error every time now when the number of records have been grown to a certain size.
The program uses Typed DataSet to access SQL Server 2005 database. The connection is made over a VPN. The same program and SQL run in a LAN environment performs not too bad. However, when it is run over the VPN, the CPU and I/O usage jumped to very high numbers. In the SQL Profiler, I found that the duration of execution is less than 20ms in LAN environment while the VPN will give me a figure around 30000ms (This means timeout).
I don't want to simply increase the timeout of my connection and command in order to solve this problem temporary. What's the actual cause of such huge difference in the performance?
Please give me some hints! Thanks a lot!
Regards,
Alex
View 9 Replies
View Related
Apr 12, 2007
Hi,
I've got a strange performance issue:
I'm using a SQL statement with a CTE to recursively get all node-ids from a tree beginning with a root node. In a select statement I'm using this CTE to get in a SELECT ... WHERE nodeID IN (SELECT ID FROM CTE_Nodes) statement data that is according to the nodes related to the root-node.
In our ASP.NET 2.0 application these statements are very slow or time out with an Exception. When I'm executing the same statement in a Management Studio the statement executes in less than one second.
BTW, we're using SQL Server 2005 Standard Ed. (9.0.3050 + 9.0.3054) in SQL Server 2000 compatibility mode with SQL Authentication.
I'm a bit frustrated, because the statements are the same.
Thanks in advance,
Klaus
Update:
BTW, the SQL Server is on a server machine and the Management Studio and the ASP.NET application on my developer machine. For data access we're using Enterprise Library 2.0 with System.Data.SqlClient.
Which possibilities do we have to trace this issue? Please help.
View 9 Replies
View Related
Jun 14, 2006
Hi,
I have a SQL Server stored procedure that gets called in the ASP.NET application. For a while it will return results quickly. After an unknown amount of time the stored procedure starts taking forever to execute. If I go into Query Analyzer and execute the same stored procedure using the exact same input parameters the results return quickly. If I then go back into the application and run the code that executes the stored procedure, the results return quickly again.
So basically every time the application call to the stored procedure begins to slow, I run that stored procedure in query analyzer and then it runs fine in the application again.
Has anyone else experienced anything like this or have any ideas as to why this would happen?
-Brian
View 1 Replies
View Related
Nov 23, 2006
Jezemine,
No, the number of reads is approximately the same. I can also confirm the disk read speed is the same on the test vs. production server. Update stats is run regularly on the production server - as I test, I ran sp_updatestats and then immediately ran the query a few times but it didn't affect the duration. Apart from the durations in the profiler traces, I can't see any differences. Clearly, something is causing the increased duration on the prod server but I don't know where to look to find it. It's definitely within SQL Server 2000.
Clive
View 8 Replies
View Related
Jun 26, 2007
Hello!I have this piece of SQL code:UPDATE aSET Field1 = c.Field1FROM aINNER JOIN b ON a.GUID1 = b.GUID1INNER JOIN c ON b.GUID2 = c.GUID2WHERE c.Type = 1AND @date BETWEEN b.DateFrom AND b.DateToThis query takes hours to complete.Now while trying to find out what's causing the poor performance (itsurely looks simple enough!) I've rewritten it to use temp tables:SELECT a.GUID1, a.Field1, c.Type, b.DateFrom, b.DateTo INTO #temptableFROM aINNER JOIN b ON a.GUID1 = b.GUID1INNER JOIN c ON b.GUID2 = c.GUID2WHERE c.Type = 1AND @date BETWEEN b.DateFrom AND b.DateToUPDATE a SET Field1 = subsel.Field1FROM (SELECT * FROM #temptable) AS subselWHERE subsel.GUID1 = a.GUID1Now it completes in 10 seconds.My question is why? Am I wrong in saying that the two batches aboveproduce same results? Is there something I've missed about the UPDATEFROM syntax? Why would the first query perform THAT poorly?Table sizes:a: 24k rowsb: 268k rowsc: 260k rowsGUIDs are of type uniqueidentifier.Any answers appreciated!Regards,// Richard
View 8 Replies
View Related
Apr 23, 2008
Hi All,
Not sure if this question belongs in this area or the server area but I'll start here. Here is my problem. We have an C/C++ app that was originally written for SQL 2000 and uses DBLibrary. We have converted it to SQL 2005 and are using ODBC/Native client to access the SQL 2005 database. This all works great. So we were doing some performance testing and we noticed that our update performance seems slower in the SQL 2005/ODBC case than it did in the SQL 2000/DBLibrary case. Inserts and queries all perform great, in fact the inserts are significantly faster in the SQL 2005/ODBC case which is good. We are using Array inserts/updates/queries wherever possible as this is faster obviously. In our update case, it takes 1.14 seconds to update 2000 rows in table in the SQL 2005/ODBC case, while SQL 2000/DBLibrary case takes .39 seconds to the exact same thing. The table in question is a 12 column table with all integer columns, with an index on the first three columns.
So my question is this. Is there something different about Array Updates in SQL 2005 ? I've looked thru the list of hot fixes available since SQL 2005 SP2 and haven't seen anything that directly mentions Updates so I'm hesitant to go off and start applying the hot fixes to our server to see if the behavior changes. It seems strange to me that Array Inserts would be very fast, but Updates wouldn't be. I've checked the ODBC Data Source and we aren't doing anything fancy there. I'm not actually even sure if this problem is client side or server side as I said earlier.
If anyone has any ideas or thoughts that would be great since this is really bugging me.
Here is a sample of what our C code is doing. This is simplied and I've removed a lot of our own code but these are the SQL calls that we are making in order so maybe can see something wrong I'm doing.
//----Sample Code -------------------------------------------------------------------------------------
pSQL = "Update bob set VV=? where VI=?" // not done this way actually in our code but just to show the update text
status = SQLPrepare ( hS, (UCHAR *)pSQL, SQL_NTS );
// Called twice with nCol = 1 and then with nCol = 2
status = SQLSetParam (
hS,
abs(nCol),
cType, // cType = 5 = SQL_C_LONG
sqlType, // sqlType = 4 = SQL_INTEGER
38, // size needed in case the column is numeric or decimal
0,
p16Data, // Pointer to my array of data
NULL
);
SQLSetStmtAttr(hS, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER)numrows, 0); // numrows = 2000
SQLSetStmtAttr(hS, SQL_ATTR_PARAMS_PROCESSED_PTR, &p16cRows, 0);
status = SQLExecute (hS);
SQLParamOptions(hS, 1, NULL);
SQLFreeStmt ( hS, SQL_RESET_PARAMS );
//----Sample Code -------------------------------------------------------------------------------------
Thanks,
Nick
View 5 Replies
View Related
Feb 7, 2008
I have a simple stored procedure that send a message on a conversation:
Code Snippet
CREATE PROCEDURE [Vxml].[sp_SendMessageToWarehouse]
-- Add the parameters for the stored procedure here
@Message XML,
@EntityId uniqueidentifier,
@ConversationHandle uniqueidentifier OUTPUT
AS
BEGIN
BEGIN TRY
;SEND ON CONVERSATION @ConversationHandle MESSAGE TYPE VxmlEngineXMLMessage(@Message);
END TRY
BEGIN CATCH
INSERT INTO [Vxml].SBError (ErrorMsg) VALUES (N'Error <' + ERROR_MESSAGE() + N'> for entity <' + cast(@EntityId as NVARCHAR(max)) + N'> on conversation <)' + cast(@ConversationHandle as NVARCHAR(max)) + N'> with body ' + cast(@Message as NVARCHAR(max)))
END CATCH;
END
This completes in less than 100ms (avg 30ms) except once in about 10000 executes (only one simultaneous execute), then this takes about 3 seconds to complete.
When performing the same test and calling the stored procedure simultaneously from 5 to 20 threads, we see up to 3 such spikes (in 10000 executes) taking 2 to 4 seconds.
Conversations are created before the test and closed after. No ODBC errors occur and the SBError table is empty.This is run on an SQLEXPRESS the conversation target is a SQL Server 2005 (standard edition)
Can anyone explain these strange performance spikes ?
View 1 Replies
View Related
Feb 22, 2008
Hello,
I have the following queries that run on a view called EntrySummary:
1)
exec sp_executesql N'SELECT COUNT (1) FROM [dbo].[EntrySummary] WHERE [EntrySummary].[SubmissionStatusID] = @SubmissionStatusID0 AND [EntrySummary].[CreatedBy] = @CreatedBy1',N'@SubmissionStatusID0 int,@CreatedBy1 nvarchar(20)',@SubmissionStatusID0=4,@CreatedBy1=N'domainaperson'
2)
exec sp_executesql N'SELECT COUNT (1) FROM [dbo].[EntrySummary] WHERE [EntrySummary].[CreatedBy] = @CreatedBy1 AND [EntrySummary].[SubmissionStatusID] = @SubmissionStatusID0',N'@SubmissionStatusID0 int,@CreatedBy1 nvarchar(20)',@SubmissionStatusID0=4,@CreatedBy1=N'domainaperson'
(The only difference between the two queries being the order of the where clauses)
Both return the correct answer (4144), but Query 2 takes 10-15 seconds whereas Query 1 takes < second.
If the same query is resubmitted several times, this doesn't affect the times.
(The vast majority of the records have a status of 4, but only about 3% will be created by the person).
Replacing Count(1) with count(*) makes both queries return quickly.
Is sp_executesql creating poor execution plans ? Can anyone explain this behaviour?
Regards,
AndyM
View 1 Replies
View Related
Sep 13, 2004
I am updating a db with data from a file, in this data we have new info, info that has been updated and info that is to be removed from the db.
Now I was wondering which approach results in better performance/shorter executin time:
1. first update excisting values, then insert new ones, and last delete cancelled data
or
2. delete cancelled data and data that will be updated, then insert new and updated info
I get all this data from a file, in that file all rows are similar and there is one column that defines if the data is new, updated or to be deleted (thus all the updates also include the information for the enty that has not been altered).
// Pati
View 4 Replies
View Related
Mar 5, 2001
Does anyone know how to improve performance on insert statements. I have to run a query of several thousand insert statements, but it just takes too long. Does anyone know of any good tips to improve performance?
joe
View 3 Replies
View Related
Nov 22, 2006
Hey there :)Sorry if I'm asking a dumb question here, but I'm still quite new to MS SQL,so this problem might appear larger to me than it really is.I'm trying to create a performance test environment for a Ruby on Rails andMongrel setup with an MS SQL Server 2000.The adapter, mssqlclient, uses some kind of "conversion" for unicode, here'sa quote from the homepage:"Automatically translate from proper UTF-16LE nvarchar fields in thedatabase to UTF-8 Ruby Strings you can display in your application"As far as the local DB designer knows, we're not using UTF16-LE nvarcharfields, unless it's something that happens implicitly.Either way, this is how a query from the mssqlclient adapter might look:SELECT TOP 1 * FROM Item WHERE (Item.Itemnumber = N'45783745')Response time the first couple of times was upwards of 20+ seconds, afterthe sql server has "awaken from its slumber", it's roughly 4 seconds.Omitting the "N" from the WHERE clause, response time is in milliseconds (asone would expect, regardless of the fact that there's currently >2.5million items in the table).Any tips on how to resolve this? Is the SQL statement bad, or is it aquestion of configuring SQL Server correctly?Thanks in advance for any help,Daniel Buus :)--http://www.rhesusb.dk
View 2 Replies
View Related
Feb 27, 2008
Hi All
I struck up with Slow perfornace query,Please some body help me how to analyze Slow perforamnce queris.
View 6 Replies
View Related
Dec 5, 2007
I have a number of complex search stored procedures that use the following syntax to try to simplify the code.
WHERE @SearchParam IS NULL OR SearchCol = @SearchParam
unfortunately it appears that this is really inefficient as far as the database is concerned.
If I run the following query on the AdventureWorks database (SQL Server 2005 with SP2 and fixes up to v3054)
Code Block
SET STATISTICS IO ON
Declare @CustomerID int
SET @CustomerID = 1
SELECT SalesOrderID
FROM Sales.SalesOrderHeader
WHERE @CustomerID IS NULL OR CustomerID = @CustomerID
SELECT SalesOrderID
FROM Sales.SalesOrderHeader
WHERE CustomerID = @CustomerID
Is see that the first select results in 45 logical reads, whereas the second results in only 2 logical reads.
Does anyone have any idea how I can get the benefit of a search procedure that does not have loads of IF blocks, or dynamic SQL without this major performance issue?
Cheers,
Justin
View 3 Replies
View Related
Jan 15, 2008
We have an issue where a cube hasn't been designed properly - when someone queries it with Excel, it is doing a mega-crossjoin. When anyone else tries to do *anything* on the AS server (connect with management studio, etc.) it just hangs. We have to either track down the person running the query (via the flight recorder), or restart the service. Obviously the correct fix is to change the design of the cube - I plan on doing it asap. But it brings up this important question - is there a setting I can change to allow others to use the box while this is going on? Maybe some thread isolation, or parallelism? I'm just throwing out ideas, as I haven't experienced this part of AS administration yet.
Thanks in advance,
John
View 7 Replies
View Related
Jul 20, 2005
I changed from Access97 to AccessXP and I have immense performanceproblems.Details:- Access XP MDB with Jet 4.0 ( no ADP-Project )- Linked Tables to SQL-Server 2000 over ODBCI used the SQL Profile to watch the T-SQL-Command which Access ( whocreates the commands?) creates and noticed:1) some Jet-SQL commands with JOINS and Where-Statements aretranslated very well, using sp_prepexe and sp_execute, including thesimilar SQL-Statement as in JET.2) other Jet-SQL commands with JOINS and Where-Statements aretranslated very bad, because the Join wasn´t sent as a join, Accesscollects the data of the individual tables seperately.Access sends much to much data over the network, it is a disaster!3) in Access97 the same command was interpreted wellCould it be possible the Access uses a wrong protocol-stack, perhapsJet to OLEDB, OLEDB to ODBC, ODBC to SQL-Server orJet to ODBC, ODBC to OLEDB and OLEDB to SQL-Server instead ofJet to ODBC and ODBC direct to SQL-ServerDoes anyone knows anything about:- Command-Interpreter of JetODBC, Parameters, how to influence thecommand-interpreter- Protocol-Stack of a Jet4.0 / ODBC / SQL-Server applicationThanks , Andreas
View 6 Replies
View Related
May 2, 2006
If multiple snapshots for the same report attempt to generate at the same second, is there something in place to prevent them from conflicting with each other?
Our SSRS 2005 application will use a console app that we are writing to change the default parameters on each report and call the CreateReportHistorySnapshot method. This application will be multi-threaded, so there is a possibility that multiple versions of the same report with different parameters will attempt to run at the same time. We need to be sure that these snapshots will not conflict and return the appropriate SnapshotHistoryID for that report run.
From looking at the History table in the ReportServer database it looks like there are uniqueIDs and other keys that would prevent duplicates, but what if the requests come in at the same time? Is SSRS 2005 smart enough to delay for a second so that there is not a duplicate? Since the way you reference these snapshots in your URL is with the Snapshot Time in UTC format, that only goes out to full seconds (2006-05-02T00:00:00).
Sorry for the length, but I could not think of how to condense this.
Thanks,
Steve
View 4 Replies
View Related
Mar 12, 2006
I have C# Express and the SQL Express Management Studio CTP installed. So far, I've been forced to disconnect from the server in one app to connect to it in the other. I understand there is something around a 4 connection limit in SQLServer Express, so I don't see the problem.
During one of these episodes, SQL Mgmt Studio hung while opening DB properties, then the SQLServer service couldn't be stopped. Now the DB can't be opened even after a reboot.
Help?
View 10 Replies
View Related
Mar 29, 2000
How can I simulate 1,000 simultaneous users in my database?
View 3 Replies
View Related
Mar 22, 1999
Is there a limit to the # of tasks ( Dumps) to a HD device ,which SQL 6.5 can perform simultaneously?
View 1 Replies
View Related
Feb 14, 2008
Hi All,
I have a Windows 2003 Server. Planning to use SQL Server to act as the main Database Server which needs to cater to over 100's of simultaneous connections.
Can anyone share with me the default maximum simultaneous connections and the maximum allowed simultaneous connections for the following versions:
- MSDE
- 2000
- 2005 Express
- 2005
Hope I can get more professional advice from you guys.
Thanks.
View 1 Replies
View Related
Jul 11, 2006
Using the Import/Export Wizard, it is easy to set up SSIS jobs to import multiple tables in one task. However, SSIS appears to grab a connection to the source database and the destination database for each table indicated in the task sometime prior to doing the actual data transfer. Even though the transfer seems to be done a few tables at a time, all the connections are held throughout the execution of the task.
This has two effects:
1. The task may fail if the number of connections exceeds the limit of user connections set for the database
2. There is a severe impact on the other users of the databases (in my case, the source is a production system)
Is there any way to control the number of simultaneous connections that SSIS data transfer packages initiate?
I can, of course, define the package to have a small number of tables (thus limiting the connections), but in real life I need to transfer almost 700 tables, so limiting the number of tables per task to 20 or 25 produces an awkwardly large, and error prone, number of tasks. And since this definition must be done for multiple (not quite identical) databases, the extra effort of defining small collections of tables is signifcant.
View 4 Replies
View Related
Aug 14, 2007
Greetings!
Are there any limit of simultaneous users and/or connections when running SQL Express? The free license of SQL Express, does that include use by any number of users/devices?
Regards
Harald Hedlund
View 5 Replies
View Related
Apr 7, 2008
Hi, can anyone help me? I want to know whether is it possible to insert records simultaneously in both master & detail tables?
For elaboration, say there is a master table contains (orderid,orderdate,amount) and details table contains (orderid, productid,qty,price). 1 record of master table associated with n records of details table. Can it be possible to insert both the 1 record at master table with n records in details table in a single sql statement?
View 3 Replies
View Related
Jul 20, 2005
hello to all,there are two databases named A and B.One database contains employee id as primaryI have another project database which includes that employee id asforeign key. How can I reflect my updations,deletionsthank you in advance,vishnu
View 2 Replies
View Related
Oct 15, 2007
Is there a way to get the Report Manager to allow a single user to
execute more than 1 report at a time? Currently, each user can only
have a single report being executed at a time. It looks like any
other reports are being queued and executed serially.
View 1 Replies
View Related
Oct 23, 2007
How can I prevent a package, or a section of a package, from being run more than once simultaneously? The package makes use of a staging database table, and if two copies of this package are run at the same time, there will be a race condition and possible corruption of the data in the database table. I have also noticed that the SSIS logging gets messed up and starts overwriting itself if I have more than one of these packages run at the same time.
What I am thinking of should work the same way as a serializable transaction lock on a normal query, where one query has to wait if another query has a lock on the table. I don't want the package to throw an error, I want it to wait until the other one is done.
I have been trying to use transactions in a test SSIS package, but they are not quite doing what I want them to do. I put various SSIS steps in a sequence container and require a serializable transaction. I have also tried putting the transaction at the package level, but that is not preventing simultaneous package runs.
Is there a built-in way to do this that is easy to implement? Otherwise, what I could do is insert a flag into the database indicating that a package is running, then make sure to reset the flag on any error or on completion. It seems like this alternate method could be error-prone though.
I am using SP2.
View 8 Replies
View Related
