Deadlock And High Cpu - Chicken Or The Egg
Dec 12, 2007
I was asked to look into a performance problem on a newly migrated DB
server.
The db server was moved from a local-physical-nt4-sybase to remote (10
mb wan link), virtual, Windows 2003, SQL 2005.
The client side application had to be modified to work with MS SQL.
This is all second hand information as I have just been thrown into
this. Most of the people who set this up ran.
The 20 clients do some data entry all day which culminates into all 20
stations running an end of day procedure at the same time. This
particular event creates 3 things :
- very high and constant CPU usage on the SQL server
- deadlock victim errors on some of the clients
- very slow "end of day" performance.
This use to work flawleessly on the former setup.
My question is about deadlocks. Can they be generated by the high CPU
usage/ slow response or can they be the actual source of the CPU
peak ?
I suspect I might be in front of multiple problems:
- underpowered vm (i have asked to increase Ram and cpu cycles to the
vm which will take a few days)
- badly tuned sql application
I'm not asking for a solution to this, just some conventional wizdom
on deadlock and high cpu.
Thanks in advance.
View 8 Replies
ADVERTISEMENT
Oct 18, 2005
Hello all...here's a question from a newbie ...
View 8 Replies
View Related
Jul 23, 2005
I'm trying to create a local copy of a popular CRM database calledSalesforce.com. Many of the tables in the DB have FOREIGN KEYreferences that I want to preserve, but I've run into a chicken and eggproblem. Table "A" has a reference to table "B," and table "B" has areference to table "A." So I can't CREATE one until the other exists.Is there a way to disable these checks until I've created all theschema?Here's what I see (error first, then SQL that caused it):Server: Msg 1767, Level 16, State 1, Line 1Foreign key 'FK__UserRole__LastMo__48CFD27E' references invalid table'User'.Server: Msg 1750, Level 16, State 1, Line 1Could not create constraint. See previous errors.CREATE TABLE salesforce3.dbo."UserRole" ("Id" varchar(18) PRIMARY KEY ,"Name" varchar(40), "ParentRoleId" varchar(18) REFERENCES"UserRole"(Id), "RollupDescription" varchar(80),"OpportunityAccessForAccountOwner" varchar(40),"CaseAccessForAccountOwner" varchar(40), "LastModifiedDate" datetime,"LastModifiedById" varchar(18) REFERENCES "User"(Id), "SystemModstamp"datetime);CREATE TABLE salesforce3.dbo."User" ("Id" varchar(18) PRIMARY KEY ,"Username" varchar(80), "LastName" varchar(80), "FirstName"varchar(40), "CompanyName" varchar(80), "Division" varchar(80),"Department" varchar(80), "Title" varchar(80), "Street" text, "City"varchar(40), "State" varchar(20), "PostalCode" varchar(20), "Country"varchar(40), "Email" varchar(80), "Phone" varchar(40), "Fax"varchar(40), "MobilePhone" varchar(40), "Alias" varchar(8), "IsActive"bit, "TimeZoneSidKey" varchar(40), "UserRoleId" varchar(18) REFERENCES"UserRole"(Id), "LocaleSidKey" varchar(40), "ReceivesInfoEmails" bit,"ReceivesAdminInfoEmails" bit, "EmailEncodingKey" varchar(40),"ProfileId" varchar(18) REFERENCES "Profile"(Id), "LanguageLocaleKey"varchar(40), "EmployeeNumber" varchar(20), "WirelessEmail" varchar(80),"LastLoginDate" datetime, "CreatedDate" datetime, "CreatedById"varchar(18) REFERENCES "User"(Id), "LastModifiedDate" datetime,"LastModifiedById" varchar(18) REFERENCES "User"(Id), "SystemModstamp"datetime, "UserPermissionsMarketingUser" bit,"UserPermissionsOfflineUser" bit, "UserPermissionsWirelessUser" bit,"UserPermissionsSuperCssUser" bit, "UserPermissionsAvantgoUser" bit);
View 2 Replies
View Related
Apr 23, 2007
Hi,
Is there a way to configure mirroring to go from High Availability to High Protection without having to reconfigure Database Mirroring? Using the interface in Management Studio, I can change the configuration option to High Performance, but not High Protection despite both of them being Synchronous.
If not, what are the recommended steps to configure the mirror once it already has been configured? Is just like initially setting up the mirror or would there be any shortcuts I could take? If I stop the mirroring and remove the witness, will the High Protection option be available?
Thanks,
J.
View 3 Replies
View Related
Mar 6, 2008
Hi There
I realise this is a stupid quesiton but i cannot really find any confirmation of this in BOL.
If you are running High Safety with automatic failover, when failover occurs does this automatically change to High Performance mode. SInce for failover to occur something has happen with the primary , it will be impossible to commit transactions on the new primary and mirror asyncronously since 1 of them is no longer available.
So am i correct in assuming that automatic failover also automatically changes the mode to High Performacne for that session?
Thanx
View 4 Replies
View Related
Nov 10, 2015
Is there a way to send out an email woth deadlock information (victim query, winner query, process id's and resources on which the deadlock occurred) as soon as a deadlock occurs in a database or at instance level?I currently has trace flag 1222 turned on. And also created an alert that send me an email whenever a deadlock occurs. but it just says that a deadlock occurred and I log into sql server error log and review the information.
View 5 Replies
View Related
Apr 26, 2008
Hello everyone,Can you please help me on the following problem that i have.I am using the microsoft web stress tools to simulate 100 concurrent users for one min, and the database will go into a deadlock for large part of the 100 concurrent users.Please help...i've been stuck for many days already. Thanks...Error 5: Transaction (Process ID 497) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction..Here is my code.My connection string:<connectionStrings><add name="Connection_String" connectionString="Data Source=123.123.123.123;Initial Catalog=DBName;MultipleActiveResultSets=true;User ID=uname;Password=upass;connection timeout=60" providerName="System.Data.SqlClient"/> </connectionStrings>My web apps will call the below function. And the deadlock always occurs at the update statement (error 5). I believe it's probably the select statement above locked up the row. I've put in (UPDLOCK) for the select statement, and it still didn't work.private void UpdateLogTables(string Station_ID, string ContentFileName, string FileSize, string IP){ContentFileName = Regex.Replace(ContentFileName, "'", "''");string AdFileName = "";string AdFileNameClause = "";string DownloadCount = "";string TotalFileSize = "";string DownloadDate = "";string currentDate = "";string tempDate = "";string MyString = "";SqlConnection MyConnection = new SqlConnection(System.Configuration.ConfigurationMa nager.ConnectionStrings["Connection_String"].ConnectionString);SqlCommand myCommand = null;SqlDataReader myReader = null;try{MyConnection.Open();MyString = "SELECT AdFileName FROM tblContentRef WHERE Station_ID = '" + Station_ID + "' AND ContentFileName = '" + ContentFileName + "'";myCommand = new SqlCommand(MyString, MyConnection);myCommand.CommandTimeout = 60;myReader = myCommand.ExecuteReader();if (myReader.Read()) {AdFileName = myReader["AdFileName"].ToString();} }catch (Exception e){Response.Write("Error 2: " + e.Message + ".<br><br>Please email abc@abc.com.<br><br>Thank you.");} finally{if (myReader != null){myReader.Close();}}if (AdFileName != ""){AdFileName = "'" + AdFileName.Replace("'", "''") + "'";AdFileNameClause = "AdFileName = " + AdFileName;}else{AdFileName = "NULL";AdFileNameClause = "AdFileName IS NULL";}InsertDetailsLogTable(Station_ID, ContentFileName, AdFileName, FileSize, IP, MyConnection);SqlDataReader myReader2 = null;SqlCommand myCommand2 = null;bool Readable = false;try{MyString = "SELECT DownloadCount, TotalFileSize, DownloadDate FROM tblSummaryLog (UPDLOCK) WHERE Station_ID = '" + Station_ID + "' AND ContentFileName = '" + ContentFileName + "' AND " + AdFileNameClause + " ORDER BY DownloadDate DESC";myCommand2 = new SqlCommand(MyString, MyConnection);myCommand2.CommandTimeout = 60;myReader2 = myCommand2.ExecuteReader();if (myReader2.Read()){DownloadCount = myReader2["DownloadCount"].ToString();TotalFileSize = myReader2["TotalFileSize"].ToString();DownloadDate = myReader2["DownloadDate"].ToString();currentDate = DateTime.Now.ToString("M/d/yyyy");string[] inDate = DownloadDate.Split(new char[] { ' ' });tempDate = inDate[0];Readable = true;}if (myReader2 != null){myReader2.Close();} if (Readable){if (tempDate == currentDate){DownloadCount = Convert.ToString(Convert.ToInt32(DownloadCount) + 1);TotalFileSize = Convert.ToString(Convert.ToInt32(TotalFileSize) + Convert.ToInt32(FileSize));UpdateSummaryLogTable(DownloadCount, TotalFileSize, Station_ID, ContentFileName, AdFileNameClause, DownloadDate, MyConnection);}else{InsertSummaryLogTable(Station_ID, ContentFileName, AdFileName, FileSize, MyConnection);}}else{InsertSummaryLogTable(Station_ID, ContentFileName, AdFileName, FileSize, MyConnection);} }catch (Exception e){Response.Write("Error 3: " + e.Message + ".<br><br>Please email abc@abc.com.<br><br>Thank you.");}finally{ if (MyConnection != null){ MyConnection.Close();} }}private void InsertDetailsLogTable(string Station_ID, string ContentFileName, string AdFileName, string FileSize, string IP, SqlConnection MyConnection){string MyString = @"INSERT INTO tblDetailsLog ( Station_ID, ContentFileName, AdFileName, FileSize, IP, DateTime ) VALUES ( '" + Station_ID + "', '" + ContentFileName + "', " + AdFileName + ", '" + FileSize + "', '" + IP + "', getdate() )";try{ SqlCommand MyCmd = new SqlCommand(MyString, MyConnection);MyCmd.CommandTimeout = 60;MyCmd.ExecuteNonQuery(); }catch (Exception e){Response.Write("Error 4: " + e.Message + ".<br><br>Please email abc@abc.com.<br><br>Thank you.");}finally{} }private void UpdateSummaryLogTable(string DownloadCount, string TotalFileSize , string Station_ID , string ContentFileName , string AdFileNameClause, string DownloadDate, SqlConnection MyConnection){string MyString = @"UPDATE tblSummaryLog SET DownloadCount = '" + DownloadCount + "', TotalFileSize = '" + TotalFileSize + "', DownloadDate = getdate() WHERE Station_ID = '" + Station_ID + "' AND ContentFileName = '" + ContentFileName + "' AND " + AdFileNameClause + " AND (CONVERT(CHAR(19), DownloadDate) = CONVERT(CHAR(19), CAST('" + DownloadDate + "' AS datetime)))";try{ SqlCommand MyCmd = new SqlCommand(MyString, MyConnection); MyCmd.CommandTimeout = 60;MyCmd.ExecuteNonQuery(); }catch (Exception e){Response.Write("Error 5: " + e.Message + ".<br><br>Please email abc@abc.com.<br><br>Thank you.");}finally{} }private void InsertSummaryLogTable(string Station_ID , string ContentFileName , string AdFileName , string TotalFileSize, SqlConnection MyConnection){string MyString = @"INSERT INTO tblSummaryLog ( Station_ID, ContentFileName, AdFileName, DownloadCount, TotalFileSize, DownloadDate ) VALUES ( '" + Station_ID + "', '" + ContentFileName + "', " + AdFileName + ", '1', '" + TotalFileSize + "', getdate() )";try{ SqlCommand MyCmd = new SqlCommand(MyString, MyConnection);MyCmd.CommandTimeout = 60;MyCmd.ExecuteNonQuery();}catch (Exception e){Response.Write("Error 6: " + e.Message + ".<br><br>Please email abc@abc.com.<br><br>Thank you.");}finally{} }
View 5 Replies
View Related
Jul 9, 2005
In a high traffic environment, deadlocks eventually occur as number of data processes increase. How can deadlocks be avoided, minimized and resolved. Please kindly provide scenario examples and samples of T-SQL code. Thanks much.
View 3 Replies
View Related
May 7, 2001
SQL 7.0
Hi All
Iam trying to bcp a table(residing on my prod server to my local machine from command prompt) .Actually the table iam trying to bcp has heavy updates and selects, from users (70 users).
The users complain that system becomes slow.Is it got anything to do with my trying to bcp the mentioned table(table has 170,000 records).Also whenever i try to bcp this table, only after being chosen as the deadlock victim by Sql server,for 3 or 4 times that iam able to bcp the table.
Any help regarding this will be very much appreciated
TIA
kinnu
View 2 Replies
View Related
Jul 27, 2000
I am unable to control the granularity of locks in our queries.
We are running queries through MTS and are getting deadlocks.
The batch includes two inserts and one select query - all are hitting on
only one table. This table has a unique clustered and a unique nonclustered index as well
as a primary key.
Within the batch, I have given a table hint to set transaction isolation level
to READCOMMITTED, ROWLOCK for the insert statements, like this
INSERT INTO ib_price with (READCOMMITTED,ROWLOCK)........
and the same for the Select statement.
SELECT retail_price, price_status_id FROM ib_price with (READCOMMITTED,ROWLOCK)
When I run sp_lock on the spid, I get output indicating that
SS7 is placing a IX lock on the table. I'm pretty sure this is
a big contributor to the deadlock.
I get the deadlock when I try to run more than one client with similar insert parameters.
How can I control the granularity to just rowlocks?
All help is appreciated.
Thanks
Brett
View 4 Replies
View Related
May 12, 2000
How can I reproduce a deadlock in 7.0?
View 1 Replies
View Related
Nov 4, 2002
Hi,
I am getting deadlock running a stored procedure from two machines. Looking at the error log (generated using trace flag 1204 and 3605), it seems the deadlock is on a key. But what I fail to understand is how come sql server granted exclusive lock on the key to both connections. The grant list shows that lock with Mode X is granted to both connection.
Deadlock encountered .... Printing deadlock information
2002-11-04 14:34:26.33 spid4
2002-11-04 14:34:26.33 spid4 Wait-for graph
2002-11-04 14:34:26.33 spid4
2002-11-04 14:34:26.33 spid4 Node:1
2002-11-04 14:34:26.33 spid4 KEY: 11:1205683443:11 (da00d1f77328) CleanCnt:1 Mode: X Flags: 0x0
2002-11-04 14:34:26.33 spid4 Grant List::
2002-11-04 14:34:26.33 spid4 Owner:0x2ffa89c0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:179 ECID:0
2002-11-04 14:34:26.33 spid4 SPID: 179 ECID: 0 Statement Type: SELECT INTO Line #: 44
2002-11-04 14:34:26.33 spid4 Input Buf: Language Event: EXEC dbo.PROC_RVM_CALCULATE 22567, 'centreAVashista'
2002-11-04 14:34:26.33 spid4 Requested By:
2002-11-04 14:34:26.33 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:166 ECID:0 Ec:(0x6f02568) Value:0x85146480 Cost:(0/14E0)
2002-11-04 14:34:26.33 spid4
2002-11-04 14:34:26.33 spid4 Node:2
2002-11-04 14:34:26.33 spid4 KEY: 11:1205683443:11 (df001773794a) CleanCnt:1 Mode: X Flags: 0x0
2002-11-04 14:34:26.33 spid4 Grant List::
2002-11-04 14:34:26.33 spid4 Owner:0x85147ee0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:166 ECID:0
2002-11-04 14:34:26.33 spid4 SPID: 166 ECID: 0 Statement Type: SELECT INTO Line #: 44
2002-11-04 14:34:26.33 spid4 Input Buf: Language Event: EXEC dbo.PROC_RVM_CALCULATE 22562, 'centreJTtest4'
2002-11-04 14:34:26.33 spid4 Requested By:
2002-11-04 14:34:26.33 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:179 ECID:0 Ec:(0x7dd0568) Value:0x3a24f920 Cost:(0/39BC)
2002-11-04 14:34:26.33 spid4 Victim Resource Owner:
2002-11-04 14:34:26.33 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:166 ECID:0 Ec:(0x6f02568) Value:0x85146480 Cost:(0/14E0)
Any pointer is much appreciated.
Thanks
Shailesh
View 2 Replies
View Related
Dec 10, 1999
Help !!
How would you handle the deadlocks that happen often? Since the victim users complained about the disconnection.
Thank you ahead of time
View 2 Replies
View Related
Apr 27, 2004
Can anyone help me resolve deadlock with following text
Parallel Query worker thread Involved in deadlock.
I am particularly interested in resolving details of above mentioned line,as I started getting dead lock
more frequently now and when I look
into query involved blocking and victim
I see nothing that can cause deadlock
they are update insert and select statement which were fine for long and all of sudden started giving problem.
Thanks in advance,
for any knowledge share
my mail id scraval@hotmail.com
View 5 Replies
View Related
Aug 4, 1999
Hi,
When many users run some stored procedures I 've got some deadlocks. How to avoid that?
We run large stored procedures code which are using sometime the same table.
What is the best way for using the transaction isolation level, fillfactor indexes, procedure cache configuration ...etc to avoid that.
In addition, I am using MTS and sometimes the Tempdb is also locked, is it a Microsoft bug (again) ?
Herve Meftah
View 1 Replies
View Related
Mar 30, 2001
We had a dead lock every night 9:00pm. I found out Server/Current Activity --Object Locks :
The error log showing error 17824, severity:10, state 0 DNCC TRACEON 208, SPID 28 DBCCTRACEOFF 208, SPID 28 In current activity --object locks and reapetedly showing "tempdb.dbo.sysobjects/sysindexes/syscolumns" 28:sa.master.dbo /INSERT /SQL_servername (MS SQLEW)
Any help will be appreciated.
View 2 Replies
View Related
Apr 1, 2004
Hi
Sorry for bombading the forum with all these questions, but i am relatively new to sql 2000.
I am getting dead lock on the following procedure.
important background information
1. this is a multi user web-based call centre application
2. this procedure loads up a new contact based on priority
I see no reason how a dead lock could occur.
does any one have any idea. could it be something else that is locking up resource used by this procedure?
CREATE PROCEDURE topcat.getNewContactInfo
(
@contact_id int
)
AS
BEGIN
begin transaction
declare @id int
set @id = (SELECT TOP 1 _id FROM class_contact WHERE (status IS NULL OR status='New Contact' OR status = 'No Connect' OR status='callback') AND (checked_in IS NULL OR checked_in <> 1) AND (callback_date >= (getdate() + 1) OR callback_date IS NULL ) ORDER BY priority DESC)
UPDATE class_contact SET checked_in = 1 WHERE _id = @id
SELECT TOP 1 * FROM class_contact
WHERE _id = @id
commit
END
GO
wat i dont' get is that, this procedure only has one update statement, this is the only statement that could possibly hold a lock on another resource (i think) , i can't see how a dead lock can happen in this case since this procedure doesn't hold up 2 resources at a time.
James :(
View 6 Replies
View Related
Jun 19, 2007
Can anyone help me to solve this deadlock?
2007-06-17 22:42:34.18 spid4 Wait-for graph
2007-06-17 22:42:34.18 spid4
2007-06-17 22:42:34.18 spid4 Node:1
2007-06-17 22:42:34.18 spid4 PAG: 66:1:26187 CleanCnt:1 Mode: IX Flags: 0x2
2007-06-17 22:42:34.18 spid4 Grant List 2::
2007-06-17 22:42:34.18 spid4 Owner:0x69f19520 Mode: IX Flg:0x0 Ref:2 Life:02000000 SPID:349 ECID:0
2007-06-17 22:42:34.18 spid4 SPID: 349 ECID: 0 Statement Type: UPDATE Line #: 1
2007-06-17 22:42:34.18 spid4 Input Buf: RPC Event: sp_prepexec;1
2007-06-17 22:42:34.18 spid4 Requested By:
2007-06-17 22:42:34.18 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:348 ECID:33 Ec:(0x5996C098) Value:0x2f8d2aa0 Cost:(0/0)
2007-06-17 22:42:34.18 spid4
2007-06-17 22:42:34.18 spid4 Node:2
2007-06-17 22:42:34.18 spid4 PAG: 66:1:3051 CleanCnt:1 Mode: SIU Flags: 0x2
2007-06-17 22:42:34.18 spid4 Grant List 1::
2007-06-17 22:42:34.18 spid4 Owner:0x65c57560 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:348 ECID:33
2007-06-17 22:42:34.18 spid4 SPID: 348 ECID: 33 Statement Type: UPDATE Line #: 22
2007-06-17 22:42:34.18 spid4 Input Buf: Language Event: exec [Extracts].dbo.asp_SPCUpdateDRPFieldsIR @Name = 'SPCExecStep B05-ASP'
2007-06-17 22:42:34.18 spid4 Grant List 2::
2007-06-17 22:42:34.18 spid4 Requested By:
2007-06-17 22:42:34.18 spid4 ResType:LockOwner Stype:'OR' Mode: IX SPID:349 ECID:0 Ec:(0x5425B500) Value:0x674a2160 Cost:(0/5FD8)
2007-06-17 22:42:34.18 spid4 Victim Resource Owner:
2007-06-17 22:42:34.18 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:348 ECID:33 Ec:(0x5996C098) Value:0x2f8d2aa0 Cost:(0/0)
=============================
http://www.sqlserverstudy.com
View 12 Replies
View Related
Jul 23, 2005
Using SQL Server 2000 SP3a, I run the following in 2 query analizerwindows on the Northwind database, the second one always gets thedeadlock Msg 1205:Window 1:declare @cnt intselect @cnt = 5while @cnt > 0beginbegin transactionselect * from orders (updlock) where employeeid = 1update orders set employeeid = 1 where employeeid = 1waitfor delay '00:00:03'commitselect @cnt = @cnt -1endWindow 2:declare @cnt intselect @cnt = 5while @cnt > 0beginbegin transactionselect * from orders (updlock) where employeeid = 1 and customerid ='ERNSH'waitfor delay '00:00:02'commitselect @cnt = @cnt -1endThe query in the first window gets 123 rows and places update locks onthem, then updates them and commits. The query in the second windowgets a subset (about 5) of the results that window 1 gets also tryingto place update locks on the same rows. Shouldn't the query in window 2just wait for the transaction in window 1 to finish? why would itdeadlock?you can also get rid of the delay in the second window and it willdeadlock faster.thanks in advance.Eugene
View 11 Replies
View Related
Feb 4, 2006
Hi.create table joe(c1 integer not null, c2 integer not null)Two sessions:Session 1:BEGIN TRANinsert into joe (c1,c2) values (1,2)Session 2:BEGIN TRANinsert into joe (c1,c2) values (3,4)Session 1:select * from joeSession 2:select * from joeOne of the sessions gets a deadlock victim message.thanks,Joe
View 8 Replies
View Related
Jun 6, 2007
Hi,How we can detect deadlock from a database.Rahul
View 1 Replies
View Related
Jun 15, 2007
I have a table that every 30 minutes needs to be repopulated fromanother table that is recreated from scratch just before.What I did was this:CREATE PROCEDURE BatchUpdProducts ASbegin transactiondelete productsinsert into productsselect * from productsTempcommit transactionGOThis takes about 30 seconds to run. I tried it doing it with a cursor,row by row, but it took like 30 minutes to run instead. The problemis with the fast approach is, once in a while I get a deadlock errorin different areas trying to access the products table. Using SQLServer 2000 by the way.Any ideas?
View 3 Replies
View Related
Jul 20, 2005
HiI have a deadlock situation and I am trying to debug my Trace Log. How do Ifind out what is the cause ?I can see from the trace I have an exclusive lock on a RID, but how can Ifind out what/where 'RID: 7:1:431830:13 ' is ?RegardsSteve:TraceLog2003-08-20 15:15:45.28 spid4Deadlock encountered .... Printing deadlock information2003-08-20 15:15:45.29 spid42003-08-20 15:15:45.29 spid4 Wait-for graph2003-08-20 15:15:45.29 spid42003-08-20 15:15:45.29 spid4 Node:12003-08-20 15:15:45.29 spid4 RID: 7:1:431830:13 CleanCnt:1Mode: X Flags: 0x22003-08-20 15:15:45.29 spid4 Grant List 0::2003-08-20 15:15:45.29 spid4 Owner:0x193e3400 Mode: X Flg:0x0Ref:0 Life:02000000 SPID:52 ECID:02003-08-20 15:15:45.31 spid4 SPID: 52 ECID: 0 Statement Type: UPDATELine #: 442003-08-20 15:15:45.31 spid4 Input Buf: RPC Event: ams_Load_Stock;12003-08-20 15:15:45.31 spid4 Requested By:2003-08-20 15:15:45.31 spid4 ResType:LockOwner Stype:'OR' Mode: USPID:55 ECID:0 Ec:(0x1A0DF5A0) Value:0x193e1800 Cost:(0/E58)2003-08-20 15:15:45.32 spid42003-08-20 15:15:45.32 spid4 Node:22003-08-20 15:15:45.32 spid4 RID: 7:1:431830:14 CleanCnt:1Mode: X Flags: 0x22003-08-20 15:15:45.32 spid4 Grant List 1::2003-08-20 15:15:45.32 spid4 Owner:0x193e3360 Mode: X Flg:0x0Ref:0 Life:02000000 SPID:55 ECID:02003-08-20 15:15:45.32 spid4 SPID: 55 ECID: 0 Statement Type: UPDATELine #: 522003-08-20 15:15:45.34 spid4 Input Buf: Language Event: DECLARE @RCintDECLARE @strResult varchar(8)DECLARE @strErrorDesc varchar(512)EXEC @RC = [msmprim].[msm].[ams_Populate_PSM_Stockrequest] @strResult OUTPUT, @strErrorDesc OUTPUTDECLARE @PrnLine nvarchar(4000)PRINT 'Stored Procedure: msmprim.msm2003-08-20 15:15:45.34 spid4 Requested By:2003-08-20 15:15:45.34 spid4 ResType:LockOwner Stype:'OR' Mode: USPID:52 ECID:0 Ec:(0x198A5558) Value:0x193e2e80 Cost:(0/94)2003-08-20 15:15:45.35 spid4 Victim Resource Owner:2003-08-20 15:15:45.35 spid4 ResType:LockOwner Stype:'OR' Mode: USPID:52 ECID:0 Ec:(0x198A5558) Value:0x193e2e80 Cost:(0/94)
View 1 Replies
View Related
Jul 21, 2000
I need to use a system-generated key and will have over 2 billion items.
I'm considering using a data type like the following.
Decimal(19,0) will give me the large numbers I need without making my index wider than 9 bytes.
Uniqueidentifier is 16 bytes.
Anyone know if this will cause me pain in the future? Specifically using @@identity after inserts to get the value (i tested it seems OK).
CREATETABLEIdentityTest
(
MyIDDECIMAL (19,0)IDENTITY (1,1),
MyCharchar(1) NOT NULL DEFAULT 'Y'
)
Any ideas welcome.
Dano
View 3 Replies
View Related
Mar 21, 2000
After a fresh install of SQL 6.5 with SP5a(or without), the cpu is running at anywhere from 50%-80%. It is loaded on a PDC, but when I stop the sql service cpu utilization drops to 0-2%. When I start the sql service it's right back up there, does anyone have a suggestion as to how to fix this or why the service would be doing this?
Thanks, Christel
View 1 Replies
View Related
Mar 2, 2004
Hello everybody,
We are starting new project
Customer validation with minimum wait time.
How to insure High-Availability?
We have 2 standard servers and in past used custom log shipping.
Log shipping still requre manual intervention ,while goal to switch
between servers automaticly.
Clustering is a last possible solution.
Could someone recomend any other soluion or products?
Thank you
Alex
View 1 Replies
View Related
Aug 14, 2002
Hello,
We are experiencing high disk i/o on one of our RAID disk systems. Can someone tell me how I can identify the query or user or process which is causing this high disk i/o?
Thanks,
Brent.
View 1 Replies
View Related
Sep 5, 2002
I am getting high CPU utilization on the SQL Server process (>90%).
However the overall utilization (NT -- entire box) always seems to be under 50%.
Can someone explain why this is happening. The server is a quad; the SQL server process seems to be using only two CPUs at a time (not the same ones all the time).
Lightweitht pooling has been turned on and the maximum worker thread size has been left at the default value (255).
How can I configure SQL options to spread the load across all four CPUs ??????
View 2 Replies
View Related
Oct 20, 2005
COLOR=Blue]Hi all,
What can be the reason there is a high CPU usage even when there's only 1 database being accessed. As a result the database is really really slow.
CPUperformance usage goes as high as 100% and I can't for the life of me figure out what's causing it. The indexes are being rebuilt 3/4 times a week and I've checked to make sure there aren't any locks or anything.
Any other ideas? [/COLOR]
Thanks
B.
View 1 Replies
View Related
Nov 14, 2007
We have a live server that has had very high CPU usage in the last few days, therefore the site is extremely slow. There are about 60,000 users per day on the average. It always had high CPU usage, but not as bad as in the last few days, in the 90s, sometimes even reaching a 100.
Any solutions? I've run SQl Profiler to check on queries that have high CPU usage. When I run the same queries on our staging server, they are very quick. For example, the same query can take 1 minute on stage, but 12 minutes on the live site. We do know that this is also related to traffic, during lower traffic times, the same query takes less time, but still never as fast as stage.
Oh yeah, it's SQL Server 2005 and asp code running IIS6 on windows 2003 server.
View 11 Replies
View Related
Feb 28, 2008
I have a summary table with a 9 field composite primary key. Every 10 minutes, my system generates 2 files of 500,000 to 750,000 rows to be summarized into this table. I first Bulk insert those into a temp table, and then trigger an inner-join update query to do the updates, followed by a left-outer join to do the inserts. As the day goes on, millions of rows in my summary table, this process is too slow. Any ideas about causes/solutions???
RLiss
View 2 Replies
View Related
May 17, 2006
i have high cpu problem. i don't know why many .net sqlclinet dataprovider and IIS hold so much cpu resource. by the way, memerycondition is normal.when i restart sqlserver, from windows task manager, i see the cpu timeof sqlserver.exe process is low, but it grows gradually, and in twodays, it can grow to as high as 2:xx:xx.any one can tell me why .net sqlclinet data provider and IIS hold somuch cpu resource? and why cpu time of sqlserver.exe grows gradually?thanks a lot.below is the part of a trace report:TextDataApplicationNameDurationStartTimeReadsWritesCPUNULL.Net SqlClient Data Provider189123357:46.529168411040076NULL.Net SqlClient Data Provider277798310:19.27923583730414NULL.Net SqlClient Data Provider189504657:42.722503411526282NULL.Net SqlClient Data Provider189957657:38.2161560740425570NULLInternet Information Services43009602:52.77042962721518NULL.Net SqlClient Data Provider277362310:19.16577581019828NULL.Net SqlClient Data Provider68673308:21.03144731014904NULLInternet Information Services33395630:11.666142618512188NULLInternet Information Services60609310:07.49384117111124NULL.Net SqlClient Data Provider37281323:04.9111621010686NULL.Net SqlClient Data Provider170658028:33.72410832077626NULLInternet Information Services64606024:35.3238936207390NULL.Net SqlClient Data Provider109062338:32.911946207109NULL.Net SqlClient Data Provider89465642:20.85394306778NULL.Net SqlClient Data Provider78986006:40.766466706483NULL.Net SqlClient Data Provider68856331:44.82884206420NULL.Net SqlClient Data Provider79023344:29.73802006077NULL.Net SqlClient Data Provider56975000:21.587254356049NULL.Net SqlClient Data Provider110423338:12.911020565453NULL.Net SqlClient Data Provider56978000:21.5111749415217NULL.Net SqlClient Data Provider149207631:42.65649825122NULL.Net SqlClient Data Provider142872027:40.27825734877NULL.Net SqlClient Data Provider173951628:27.111265694653NULL.Net SqlClient Data Provider169073328:26.59107804640NULL.Net SqlClient Data Provider40656306:52.74912304391NULL.Net SqlClient Data Provider56973601:17.73792804344NULL.Net SqlClient Data Provider87886028:25.46157204280NULL.Net SqlClient Data Provider78981306:40.7160337554279NULL.Net SqlClient Data Provider28615610:42.01521704172NULL.Net SqlClient Data Provider180126328:07.98573904138NULL.Net SqlClient Data Provider26623352:11.54391104048NULL.Net SqlClient Data Provider42412613:13.33311404046NULL.Net SqlClient Data Provider45828349:48.71569404014NULL.Net SqlClient Data Provider59893648:20.44212603983NULLInternet Information Services38987657:32.910983643923NULL.Net SqlClient Data Provider40825051:56.11565203922NULL.Net SqlClient Data Provider44153050:05.41727103906NULL.Net SqlClient Data Provider49392303:29.66312503890NULL.Net SqlClient Data Provider35761025:20.51493003797NULL.Net SqlClient Data Provider35456343:38.81570403782NULL.Net SqlClient Data Provider37890649:44.61523103689NULL.Net SqlClient Data Provider36323643:30.21596403673NULL.Net SqlClient Data Provider43392348:08.21566003672NULL.Net SqlClient Data Provider22059321:53.91434903645NULL.Net SqlClient Data Provider184018628:04.68771003637NULL.Net SqlClient Data Provider40776351:38.71646203595NULL.Net SqlClient Data Provider40970350:47.01393003389NULL.Net SqlClient Data Provider65773645:36.83863503376NULL.Net SqlClient Data Provider44501348:17.71387003342NULL.Net SqlClient Data Provider40331351:24.91290503266NULLInternet Information Services60418628:05.5146475413035NULLInternet Information Services60220337:13.79609252955NULL.Net SqlClient Data Provider34518623:32.612010402955NULL.Net SqlClient Data Provider30251314:52.01217902937NULL.Net SqlClient Data Provider37945357:47.331948102891NULL.Net SqlClient Data Provider35692347:15.61096402564NULL.Net SqlClient Data Provider37964019:33.51062602453NULL.Net SqlClient Data Provider161006329:47.711812422391NULL.Net SqlClient Data Provider75999648:30.68796232376NULL.Net SqlClient Data Provider76001348:30.65651302187NULL.Net SqlClient Data Provider158300030:14.79316502157NULLInternet Information Services55791032:46.851272111967NULLInternet Information Services25312653:29.67935541893NULL.Net SqlClient Data Provider95000027:14.33036801875NULL.Net SqlClient Data Provider56968607:22.5112528421842NULLInternet Information Services21875054:04.03855301811NULL.Net SqlClient Data Provider126831335:28.4162994581782NULL.Net SqlClient Data Provider141667332:47.97308401674NULLInternet Information Services55525047:41.07781181639NULL.Net SqlClient Data Provider88247028:21.84272901638NULLInternet Information Services25129653:31.553638181626NULL.Net SqlClient Data Provider88981328:14.44857501625NULLInternet Information Services19851600:16.159345501580NULL.Net SqlClient Data Provider56970601:17.77347301576NULLInternet Information Services52651647:51.24524581565NULL.Net SqlClient Data Provider37881326:26.96875301467NULL.Net SqlClient Data Provider81989043:15.75487301312NULLInternet Information Services37651632:48.275602141297NULL.Net SqlClient Data Provider30257614:51.91455351801266NULL.Net SqlClient Data Provider94970357:49.66191501238NULL.Net SqlClient Data Provider34781323:29.93622901203NULL.Net SqlClient Data Provider76679630:17.53368101170NULL.Net SqlClient Data Provider107081039:00.44675901109NULL.Net SqlClient Data Provider49532648:22.548276101095NULL.Net SqlClient Data Provider50011048:49.252299511078NULL.Net SqlClient Data Provider37954657:49.7448401062NULL.Net SqlClient Data Provider88336028:20.94006501049NULL.Net SqlClient Data Provider24511033:33.0489701032NULL.Net SqlClient Data Provider88495328:19.33754801031NULL.Net SqlClient Data Provider60478303:26.6451490968NULL.Net SqlClient Data Provider49395303:29.5640980955NULL.Net SqlClient Data Provider54786019:17.0377930938NULLInternet Information Services29589006:38.7446491907NULL.Net SqlClient Data Provider33442300:18.767960891NULL.Net SqlClient Data Provider86037628:43.9126870858NULLInternet Information Services40493639:47.8471172843NULL.Net SqlClient Data Provider68973331:53.8315881796NULL.Net SqlClient Data Provider50568649:43.33473121796NULL.Net SqlClient Data Provider50345348:13.1276641764NULL.Net SqlClient Data Provider72609348:20.5210820702NULL.Net SqlClient Data Provider24520333:32.9381320688NULL.Net SqlClient Data Provider28625010:41.9285548686NULL.Net SqlClient Data Provider173781330:13.5169100659NULL.Net SqlClient Data Provider34089023:36.953030625NULL.Net SqlClient Data Provider34144023:36.327820625NULL.Net SqlClient Data Provider41397050:40.9287224610NULL.Net SqlClient Data Provider34073323:37.0213300608NULL.Net SqlClient Data Provider53148348:02.7270060595NULL.Net SqlClient Data Provider35764025:20.5402862594NULL.Net SqlClient Data Provider56892033:54.6284070593declare @P1 int set @P1=1033 declare @P2 int set @P2=20644 execp_splitpage @sql = 'SelectUserName,Status,Qymc,Qylb,areaCode1,SiteHits,Zycp, CONVERT(varchar(300), Qyjj) AS Qyjj,QyjjStatus,compPhone,compFax From UserInfoWhere isValid=1 and Right(Status,1).Net SqlClient DataProvider481308:18.211296955578NULL.Net SqlClient Data Provider49670348:13.6268720563NULL.Net SqlClient Data Provider49137349:58.5275230562NULL.Net SqlClient Data Provider37967019:33.5404290562NULLInternet Information Services39212632:22.61511612543NULL.Net SqlClient Data Provider51453048:13.0228450532NULL.Net SqlClient Data Provider65514048:42.740750531declare @P1 int set @P1=5587 declare @P2 int set @P2=55867 execp_splitpage @sql = 'SelectInfo.picPath,Info.Info_ID,Info.postUser,Info.infoT ype,Info.infoLevel,Info.infoDirect,Info.showname,I nfo.postDateTime,Info.areaCode,CONVERT(varchar(300), Info.conten.Net SqlClient DataProvider94014:55.0139021180516exec p_splitpage 'SelectID,UserName,PassWord,Qymc,RegisterTime,name,Phone From UserInfo WhereisValid = 1 and UserName like ''%nick%'' andSUBSTRING(Status,2,3)=''019'' Order By ID DESC',0,30InternetInformation Services131335:39.814030516NULL.Net SqlClient Data Provider24104607:30.7216430514NULL.Net SqlClient Data Provider56918635:46.737300499NULL.Net SqlClient Data Provider36893650:51.7199720486NULL.Net SqlClient Data Provider102501628:10.9231642485NULL.Net SqlClient Data Provider54798619:16.9340860485NULL.Net SqlClient Data Provider42397013:13.4397300483NULL.Net SqlClient Data Provider37964012:11.1330690470NULLInternet Information Services18441037:30.3261545468NULL.Net SqlClient Data Provider43448606:16.9191610468NULL.Net SqlClient Data Provider36296652:15.4190060436NULLInternet Information Services11064001:43.984251422NULL.Net SqlClient Data Provider37975057:40.8210330419NULL.Net SqlClient Data Provider5279655:44.9159260406NULL.Net SqlClient Data Provider33442300:18.7325502392NULLInternet Information Services11436047:40.4148070390NULL.Net SqlClient Data Provider39812636:26.138510343NULL.Net SqlClient Data Provider155228331:21.2164340329NULL.Net SqlClient Data Provider4187655:55.8164293328NULL.Net SqlClient Data Provider36928023:08.5144192328Select Count(ID) From UserInfo Where isValid=1 and Right(Status,1)<>'0'and hy = '0019'.Net SqlClient Data Provider98308:31.9232480298SELECT TOP 5 L.title, L.siteUrl FROM Links L INNER JOIN LinksCategory CON C.PKID = L.CategoryID INNER JOIN LinksType T ON T.typeID = C.typeIDWHERE (T.typeID = 7).Net SqlClient DataProvider3057654:41.622720282
View 1 Replies
View Related
Oct 17, 2006
Could anyone help me in finding out why the cpu utilization is very high??
I have two servers say, Server A , server B. There is a transactional replication going on from server A to B
There is a table say Table A on server A, which is being replicated to server B.
I created a trigger insert and update trigger on Table A on server B (i.e. on subscriber). Since then, the CPU utilization for server B is very high 80-90%
when i used profiler, i could see .whenever replication stored proc for insert or update executes..cpu utilization goes up..
trigger just insert the updated/inserted rows into some other table.
Could anyone tell me why the cpu utilization has gone up so much?? i am using sql server 2005
thanx
View 1 Replies
View Related
