|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Why the update process take so much time??I Created a table with the following structure ----------------------------------------- CREATE TABLE [offUserConfig] ( [UserCnfgId] [udIDGen] NOT NULL , [CrsPndRef] [udIDGen] NOT NULL , [ItemAttribut] [char] (10) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL , [ItemValue] [udInteger] NULL , [StreamStr] [udHugeStr] NULL , [FormName] [udShortStr] NULL , [ChDate] [udLongDate] NOT NULL CONSTRAINT [DF_offUserConfig_ChDate] DEFAULT (getdate()), [RowState] [udInteger] NULL CONSTRAINT [DF_offUserConfig_RowState] DEFAULT (8), CONSTRAINT [PK_UserConfig] PRIMARY KEY NONCLUSTERED ( [UserCnfgId] ) ON [PRIMARY] ) ON [PRIMARY] GO and the following indexes IX_CrsPndRef nonclustered located on PRIMARY CrsPndRef PK_UserConfig nonclustered, unique, primary key located on PRIMARY UserCnfgId ----------------------------------------- This table contains approximately 500 rows. When i issue an update command agianst the DB like update ofc.offUserConfig set UserCnfgId = 1000430, CrsPndRef = 2134, ItemValue = 117, StreamStr = 'Rotation=0,Zoom=0', ChDate = '20050430 00:00:00.000' where UserCnfgId = 1000430 it sometimes takes 580 units of CPU, 48433 units of read and a duration of 6076 ms while it takes only 2 units of write. I don't understand why so many reads happen and the write process is so slow, despite this table has only one foreign key. Also pay attention that the Database Size is about 30GB. Regards, Sassan > I don't understand why so many reads happen and the write process is so Do you have an index on the foreign key column of the referenced table? The > slow, despite this table has only one foreign key. DDL script you posted has no foreign key constraints. -- Show quoteHide quoteHope this helps. Dan Guzman SQL Server MVP "Sassan Komeili Zadeh" <SassanKomeiliZa***@discussions.microsoft.com> wrote in message news:26FB1498-7737-47A4-A438-5753740A25A3@microsoft.com... > Hi, > > I Created a table with the following structure > ----------------------------------------- > > CREATE TABLE [offUserConfig] ( > [UserCnfgId] [udIDGen] NOT NULL , > [CrsPndRef] [udIDGen] NOT NULL , > [ItemAttribut] [char] (10) COLLATE > SQL_Latin1_General_CP1256_CI_AS NULL , > [ItemValue] [udInteger] NULL , > [StreamStr] [udHugeStr] NULL , > [FormName] [udShortStr] NULL , > [ChDate] [udLongDate] NOT NULL CONSTRAINT > [DF_offUserConfig_ChDate] DEFAULT (getdate()), > [RowState] [udInteger] NULL CONSTRAINT > [DF_offUserConfig_RowState] DEFAULT (8), > CONSTRAINT [PK_UserConfig] PRIMARY KEY NONCLUSTERED > ( > [UserCnfgId] > ) ON [PRIMARY] > ) ON [PRIMARY] > GO > > and the following indexes > > IX_CrsPndRef nonclustered located on PRIMARY CrsPndRef > PK_UserConfig nonclustered, unique, primary key located on PRIMARY > UserCnfgId > ----------------------------------------- > > This table contains approximately 500 rows. When i issue an update command > agianst the DB like > > update ofc.offUserConfig > set > UserCnfgId = 1000430, > CrsPndRef = 2134, > ItemValue = 117, > StreamStr = 'Rotation=0,Zoom=0', > ChDate = '20050430 00:00:00.000' > where > UserCnfgId = 1000430 > > it sometimes takes 580 units of CPU, 48433 units of read and a duration of > 6076 ms while it takes only 2 units of write. > I don't understand why so many reads happen and the write process is so > slow, despite this table has only one foreign key. > > Also pay attention that the Database Size is about 30GB. > > Regards, > Sassan > Dear Dan,
Thanks for the reply. I dropped the foreign key just because of this problem. And also I have an index on the foreign key column of the referenced table. Please help, Sassan Show quoteHide quote "Dan Guzman" wrote: > > I don't understand why so many reads happen and the write process is so > > slow, despite this table has only one foreign key. > > Do you have an index on the foreign key column of the referenced table? The > DDL script you posted has no foreign key constraints. > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "Sassan Komeili Zadeh" <SassanKomeiliZa***@discussions.microsoft.com> wrote > in message news:26FB1498-7737-47A4-A438-5753740A25A3@microsoft.com... > > Hi, > > > > I Created a table with the following structure > > ----------------------------------------- > > > > CREATE TABLE [offUserConfig] ( > > [UserCnfgId] [udIDGen] NOT NULL , > > [CrsPndRef] [udIDGen] NOT NULL , > > [ItemAttribut] [char] (10) COLLATE > > SQL_Latin1_General_CP1256_CI_AS NULL , > > [ItemValue] [udInteger] NULL , > > [StreamStr] [udHugeStr] NULL , > > [FormName] [udShortStr] NULL , > > [ChDate] [udLongDate] NOT NULL CONSTRAINT > > [DF_offUserConfig_ChDate] DEFAULT (getdate()), > > [RowState] [udInteger] NULL CONSTRAINT > > [DF_offUserConfig_RowState] DEFAULT (8), > > CONSTRAINT [PK_UserConfig] PRIMARY KEY NONCLUSTERED > > ( > > [UserCnfgId] > > ) ON [PRIMARY] > > ) ON [PRIMARY] > > GO > > > > and the following indexes > > > > IX_CrsPndRef nonclustered located on PRIMARY CrsPndRef > > PK_UserConfig nonclustered, unique, primary key located on PRIMARY > > UserCnfgId > > ----------------------------------------- > > > > This table contains approximately 500 rows. When i issue an update command > > agianst the DB like > > > > update ofc.offUserConfig > > set > > UserCnfgId = 1000430, > > CrsPndRef = 2134, > > ItemValue = 117, > > StreamStr = 'Rotation=0,Zoom=0', > > ChDate = '20050430 00:00:00.000' > > where > > UserCnfgId = 1000430 > > > > it sometimes takes 580 units of CPU, 48433 units of read and a duration of > > 6076 ms while it takes only 2 units of write. > > I don't understand why so many reads happen and the write process is so > > slow, despite this table has only one foreign key. > > > > Also pay attention that the Database Size is about 30GB. > > > > Regards, > > Sassan > > > > > Please post the referenced table DDL along with a script for your
user-defined data type definitions. Also, if you have any update triggers on the offUserConfig table, include that code too. -- Show quoteHide quoteHope this helps. Dan Guzman SQL Server MVP "Sassan Komeili Zadeh" <SassanKomeiliZa***@discussions.microsoft.com> wrote in message news:3EBFA032-CD66-4610-A471-6BACD1814B55@microsoft.com... > Dear Dan, > > Thanks for the reply. > I dropped the foreign key just because of this problem. > And also I have an index on the foreign key column of the referenced > table. > > Please help, > Sassan > > "Dan Guzman" wrote: > >> > I don't understand why so many reads happen and the write process is so >> > slow, despite this table has only one foreign key. >> >> Do you have an index on the foreign key column of the referenced table? >> The >> DDL script you posted has no foreign key constraints. >> >> -- >> Hope this helps. >> >> Dan Guzman >> SQL Server MVP >> >> "Sassan Komeili Zadeh" <SassanKomeiliZa***@discussions.microsoft.com> >> wrote >> in message news:26FB1498-7737-47A4-A438-5753740A25A3@microsoft.com... >> > Hi, >> > >> > I Created a table with the following structure >> > ----------------------------------------- >> > >> > CREATE TABLE [offUserConfig] ( >> > [UserCnfgId] [udIDGen] NOT NULL , >> > [CrsPndRef] [udIDGen] NOT NULL , >> > [ItemAttribut] [char] (10) COLLATE >> > SQL_Latin1_General_CP1256_CI_AS NULL , >> > [ItemValue] [udInteger] NULL , >> > [StreamStr] [udHugeStr] NULL , >> > [FormName] [udShortStr] NULL , >> > [ChDate] [udLongDate] NOT NULL CONSTRAINT >> > [DF_offUserConfig_ChDate] DEFAULT (getdate()), >> > [RowState] [udInteger] NULL CONSTRAINT >> > [DF_offUserConfig_RowState] DEFAULT (8), >> > CONSTRAINT [PK_UserConfig] PRIMARY KEY NONCLUSTERED >> > ( >> > [UserCnfgId] >> > ) ON [PRIMARY] >> > ) ON [PRIMARY] >> > GO >> > >> > and the following indexes >> > >> > IX_CrsPndRef nonclustered located on PRIMARY CrsPndRef >> > PK_UserConfig nonclustered, unique, primary key located on PRIMARY >> > UserCnfgId >> > ----------------------------------------- >> > >> > This table contains approximately 500 rows. When i issue an update >> > command >> > agianst the DB like >> > >> > update ofc.offUserConfig >> > set >> > UserCnfgId = 1000430, >> > CrsPndRef = 2134, >> > ItemValue = 117, >> > StreamStr = 'Rotation=0,Zoom=0', >> > ChDate = '20050430 00:00:00.000' >> > where >> > UserCnfgId = 1000430 >> > >> > it sometimes takes 580 units of CPU, 48433 units of read and a duration >> > of >> > 6076 ms while it takes only 2 units of write. >> > I don't understand why so many reads happen and the write process is so >> > slow, despite this table has only one foreign key. >> > >> > Also pay attention that the Database Size is about 30GB. >> > >> > Regards, >> > Sassan >> > >> >> >> Hi
Thanks for the response. As I indicated there is no foreign key reference from the ofc.offUserConfig to any other table and the ofc.offUserConfig DDL script is as the previous script I had sent. The customer script profile is attached to this message and I drew an elipse on places when the read and write process happens. As it is depicted in the image the write process has more than 50000 unit of read while the table has only 500 rows. It may be helpfull to know that nearly 300 concurrent users are working on the table but the table updates is done by means of primary key and no conflict happens because each user is working on his/her own data. The "two much read" problem doesn't happen when the database size is smaller (e.g. 10GB). This problem exists in other tables in the database but as this table is the smallest one in size with no FK to any other table it is very strange to me. Please post me your email in order I send you the attached image (sassa***@hotmail.com) Look forward to hearing from you ASAP. Regards, Sassan Show quoteHide quote "Dan Guzman" wrote: > Please post the referenced table DDL along with a script for your > user-defined data type definitions. Also, if you have any update triggers > on the offUserConfig table, include that code too. > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "Sassan Komeili Zadeh" <SassanKomeiliZa***@discussions.microsoft.com> wrote > in message news:3EBFA032-CD66-4610-A471-6BACD1814B55@microsoft.com... > > Dear Dan, > > > > Thanks for the reply. > > I dropped the foreign key just because of this problem. > > And also I have an index on the foreign key column of the referenced > > table. > > > > Please help, > > Sassan > > > > "Dan Guzman" wrote: > > > >> > I don't understand why so many reads happen and the write process is so > >> > slow, despite this table has only one foreign key. > >> > >> Do you have an index on the foreign key column of the referenced table? > >> The > >> DDL script you posted has no foreign key constraints. > >> > >> -- > >> Hope this helps. > >> > >> Dan Guzman > >> SQL Server MVP > >> > >> "Sassan Komeili Zadeh" <SassanKomeiliZa***@discussions.microsoft.com> > >> wrote > >> in message news:26FB1498-7737-47A4-A438-5753740A25A3@microsoft.com... > >> > Hi, > >> > > >> > I Created a table with the following structure > >> > ----------------------------------------- > >> > > >> > CREATE TABLE [offUserConfig] ( > >> > [UserCnfgId] [udIDGen] NOT NULL , > >> > [CrsPndRef] [udIDGen] NOT NULL , > >> > [ItemAttribut] [char] (10) COLLATE > >> > SQL_Latin1_General_CP1256_CI_AS NULL , > >> > [ItemValue] [udInteger] NULL , > >> > [StreamStr] [udHugeStr] NULL , > >> > [FormName] [udShortStr] NULL , > >> > [ChDate] [udLongDate] NOT NULL CONSTRAINT > >> > [DF_offUserConfig_ChDate] DEFAULT (getdate()), > >> > [RowState] [udInteger] NULL CONSTRAINT > >> > [DF_offUserConfig_RowState] DEFAULT (8), > >> > CONSTRAINT [PK_UserConfig] PRIMARY KEY NONCLUSTERED > >> > ( > >> > [UserCnfgId] > >> > ) ON [PRIMARY] > >> > ) ON [PRIMARY] > >> > GO > >> > > >> > and the following indexes > >> > > >> > IX_CrsPndRef nonclustered located on PRIMARY CrsPndRef > >> > PK_UserConfig nonclustered, unique, primary key located on PRIMARY > >> > UserCnfgId > >> > ----------------------------------------- > >> > > >> > This table contains approximately 500 rows. When i issue an update > >> > command > >> > agianst the DB like > >> > > >> > update ofc.offUserConfig > >> > set > >> > UserCnfgId = 1000430, > >> > CrsPndRef = 2134, > >> > ItemValue = 117, > >> > StreamStr = 'Rotation=0,Zoom=0', > >> > ChDate = '20050430 00:00:00.000' > >> > where > >> > UserCnfgId = 1000430 > >> > > >> > it sometimes takes 580 units of CPU, 48433 units of read and a duration > >> > of > >> > 6076 ms while it takes only 2 units of write. > >> > I don't understand why so many reads happen and the write process is so > >> > slow, despite this table has only one foreign key. > >> > > >> > Also pay attention that the Database Size is about 30GB. > >> > > >> > Regards, > >> > Sassan > >> > > >> > >> > >> > > > I understood from your previous message that the performance problem occurs
only with the foreign key in place. So the update is slow even without the foreign key? Check the query plan to ensure an index seek on the PK is used. If a table scan is used, reorging the table may help but won't address the underlying cause. It's usually best to have a clustered index on tables. As I previously mentioned, the provided DDL is incomplete because the UDT definitions are not included. Do you have any update triggers on the table? -- Show quoteHide quoteHope this helps. Dan Guzman SQL Server MVP "Sassan Komeili Zadeh" <SassanKomeiliZa***@discussions.microsoft.com> wrote in message news:A8CA5929-48ED-4678-9AD6-5CDF852E5A61@microsoft.com... > Hi > > Thanks for the response. As I indicated there is no foreign key reference > from the ofc.offUserConfig to any other table and the ofc.offUserConfig > DDL > script is as the previous script I had sent. > > The customer script profile is attached to this message and I drew an > elipse > on places when the read and write process happens. As it is depicted in > the > image the write process has more than 50000 unit of read while the table > has > only 500 rows. > > It may be helpfull to know that nearly 300 concurrent users are working on > the table but the table updates is done by means of primary key and no > conflict happens because each user is working on his/her own data. > > The "two much read" problem doesn't happen when the database size is > smaller > (e.g. 10GB). > > This problem exists in other tables in the database but as this table is > the > smallest one in size with no FK to any other table it is very strange to > me. > > Please post me your email in order I send you the attached image > (sassa***@hotmail.com) > > Look forward to hearing from you ASAP. > > Regards, > Sassan > > "Dan Guzman" wrote: > >> Please post the referenced table DDL along with a script for your >> user-defined data type definitions. Also, if you have any update >> triggers >> on the offUserConfig table, include that code too. >> >> -- >> Hope this helps. >> >> Dan Guzman >> SQL Server MVP >> >> "Sassan Komeili Zadeh" <SassanKomeiliZa***@discussions.microsoft.com> >> wrote >> in message news:3EBFA032-CD66-4610-A471-6BACD1814B55@microsoft.com... >> > Dear Dan, >> > >> > Thanks for the reply. >> > I dropped the foreign key just because of this problem. >> > And also I have an index on the foreign key column of the referenced >> > table. >> > >> > Please help, >> > Sassan >> > >> > "Dan Guzman" wrote: >> > >> >> > I don't understand why so many reads happen and the write process is >> >> > so >> >> > slow, despite this table has only one foreign key. >> >> >> >> Do you have an index on the foreign key column of the referenced >> >> table? >> >> The >> >> DDL script you posted has no foreign key constraints. >> >> >> >> -- >> >> Hope this helps. >> >> >> >> Dan Guzman >> >> SQL Server MVP >> >> >> >> "Sassan Komeili Zadeh" <SassanKomeiliZa***@discussions.microsoft.com> >> >> wrote >> >> in message news:26FB1498-7737-47A4-A438-5753740A25A3@microsoft.com... >> >> > Hi, >> >> > >> >> > I Created a table with the following structure >> >> > ----------------------------------------- >> >> > >> >> > CREATE TABLE [offUserConfig] ( >> >> > [UserCnfgId] [udIDGen] NOT NULL , >> >> > [CrsPndRef] [udIDGen] NOT NULL , >> >> > [ItemAttribut] [char] (10) COLLATE >> >> > SQL_Latin1_General_CP1256_CI_AS NULL , >> >> > [ItemValue] [udInteger] NULL , >> >> > [StreamStr] [udHugeStr] NULL , >> >> > [FormName] [udShortStr] NULL , >> >> > [ChDate] [udLongDate] NOT NULL CONSTRAINT >> >> > [DF_offUserConfig_ChDate] DEFAULT (getdate()), >> >> > [RowState] [udInteger] NULL CONSTRAINT >> >> > [DF_offUserConfig_RowState] DEFAULT (8), >> >> > CONSTRAINT [PK_UserConfig] PRIMARY KEY NONCLUSTERED >> >> > ( >> >> > [UserCnfgId] >> >> > ) ON [PRIMARY] >> >> > ) ON [PRIMARY] >> >> > GO >> >> > >> >> > and the following indexes >> >> > >> >> > IX_CrsPndRef nonclustered located on PRIMARY CrsPndRef >> >> > PK_UserConfig nonclustered, unique, primary key located on PRIMARY >> >> > UserCnfgId >> >> > ----------------------------------------- >> >> > >> >> > This table contains approximately 500 rows. When i issue an update >> >> > command >> >> > agianst the DB like >> >> > >> >> > update ofc.offUserConfig >> >> > set >> >> > UserCnfgId = 1000430, >> >> > CrsPndRef = 2134, >> >> > ItemValue = 117, >> >> > StreamStr = 'Rotation=0,Zoom=0', >> >> > ChDate = '20050430 00:00:00.000' >> >> > where >> >> > UserCnfgId = 1000430 >> >> > >> >> > it sometimes takes 580 units of CPU, 48433 units of read and a >> >> > duration >> >> > of >> >> > 6076 ms while it takes only 2 units of write. >> >> > I don't understand why so many reads happen and the write process is >> >> > so >> >> > slow, despite this table has only one foreign key. >> >> > >> >> > Also pay attention that the Database Size is about 30GB. >> >> > >> >> > Regards, >> >> > Sassan >> >> > >> >> >> >> >> >> >> >> >> Nope. The problem is that you are updating the very attribute that you are
using in the search criteria...BIG NO NO! Why do you need to update a value you already know? Try this instead: UPDATE tbl SET CrsPndRef = 2134 ,ItemValue = 117 ,StreamStr = 'Rotation=0,Zoom=0' ,ChDate = '20050430 00:00:00.000' FROM ofc.offUserConfig AS updtbl WHERE updtbl.UserCnfgId = 1000430 However, I would agree with Dan that EVERY TABLE should have a Clustered Index defined. It looks like you are using some sort of surrogate key as the PRIMARY KEY, offUserConfig. What is the Business Key for the Entity? That typically is the best Cluster Index candidate. It looks like maybe the ItemAttribute or ItemAttribute and FormName are the Business Key. That would be a good place to start. However, if the offUserCnfgId is your Business Key—I would question this choice—then I would choose that as the Clustered Index. Good luck. Sincerely, Anthony Thomas -- "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message I understood from your previous message that the performance problem occursnews:%23FXW5g%23TFHA.3392@TK2MSFTNGP12.phx.gbl... only with the foreign key in place. So the update is slow even without the foreign key? Check the query plan to ensure an index seek on the PK is used. If a table scan is used, reorging the table may help but won't address the underlying cause. It's usually best to have a clustered index on tables. As I previously mentioned, the provided DDL is incomplete because the UDT definitions are not included. Do you have any update triggers on the table? -- Show quoteHide quoteHope this helps. Dan Guzman SQL Server MVP "Sassan Komeili Zadeh" <SassanKomeiliZa***@discussions.microsoft.com> wrote in message news:A8CA5929-48ED-4678-9AD6-5CDF852E5A61@microsoft.com... > Hi > > Thanks for the response. As I indicated there is no foreign key reference > from the ofc.offUserConfig to any other table and the ofc.offUserConfig > DDL > script is as the previous script I had sent. > > The customer script profile is attached to this message and I drew an > elipse > on places when the read and write process happens. As it is depicted in > the > image the write process has more than 50000 unit of read while the table > has > only 500 rows. > > It may be helpfull to know that nearly 300 concurrent users are working on > the table but the table updates is done by means of primary key and no > conflict happens because each user is working on his/her own data. > > The "two much read" problem doesn't happen when the database size is > smaller > (e.g. 10GB). > > This problem exists in other tables in the database but as this table is > the > smallest one in size with no FK to any other table it is very strange to > me. > > Please post me your email in order I send you the attached image > (sassa***@hotmail.com) > > Look forward to hearing from you ASAP. > > Regards, > Sassan > > "Dan Guzman" wrote: > >> Please post the referenced table DDL along with a script for your >> user-defined data type definitions. Also, if you have any update >> triggers >> on the offUserConfig table, include that code too. >> >> -- >> Hope this helps. >> >> Dan Guzman >> SQL Server MVP >> >> "Sassan Komeili Zadeh" <SassanKomeiliZa***@discussions.microsoft.com> >> wrote >> in message news:3EBFA032-CD66-4610-A471-6BACD1814B55@microsoft.com... >> > Dear Dan, >> > >> > Thanks for the reply. >> > I dropped the foreign key just because of this problem. >> > And also I have an index on the foreign key column of the referenced >> > table. >> > >> > Please help, >> > Sassan >> > >> > "Dan Guzman" wrote: >> > >> >> > I don't understand why so many reads happen and the write process is >> >> > so >> >> > slow, despite this table has only one foreign key. >> >> >> >> Do you have an index on the foreign key column of the referenced >> >> table? >> >> The >> >> DDL script you posted has no foreign key constraints. >> >> >> >> -- >> >> Hope this helps. >> >> >> >> Dan Guzman >> >> SQL Server MVP >> >> >> >> "Sassan Komeili Zadeh" <SassanKomeiliZa***@discussions.microsoft.com> >> >> wrote >> >> in message news:26FB1498-7737-47A4-A438-5753740A25A3@microsoft.com... >> >> > Hi, >> >> > >> >> > I Created a table with the following structure >> >> > ----------------------------------------- >> >> > >> >> > CREATE TABLE [offUserConfig] ( >> >> > [UserCnfgId] [udIDGen] NOT NULL , >> >> > [CrsPndRef] [udIDGen] NOT NULL , >> >> > [ItemAttribut] [char] (10) COLLATE >> >> > SQL_Latin1_General_CP1256_CI_AS NULL , >> >> > [ItemValue] [udInteger] NULL , >> >> > [StreamStr] [udHugeStr] NULL , >> >> > [FormName] [udShortStr] NULL , >> >> > [ChDate] [udLongDate] NOT NULL CONSTRAINT >> >> > [DF_offUserConfig_ChDate] DEFAULT (getdate()), >> >> > [RowState] [udInteger] NULL CONSTRAINT >> >> > [DF_offUserConfig_RowState] DEFAULT (8), >> >> > CONSTRAINT [PK_UserConfig] PRIMARY KEY NONCLUSTERED >> >> > ( >> >> > [UserCnfgId] >> >> > ) ON [PRIMARY] >> >> > ) ON [PRIMARY] >> >> > GO >> >> > >> >> > and the following indexes >> >> > >> >> > IX_CrsPndRef nonclustered located on PRIMARY CrsPndRef >> >> > PK_UserConfig nonclustered, unique, primary key located on PRIMARY >> >> > UserCnfgId >> >> > ----------------------------------------- >> >> > >> >> > This table contains approximately 500 rows. When i issue an update >> >> > command >> >> > agianst the DB like >> >> > >> >> > update ofc.offUserConfig >> >> > set >> >> > UserCnfgId = 1000430, >> >> > CrsPndRef = 2134, >> >> > ItemValue = 117, >> >> > StreamStr = 'Rotation=0,Zoom=0', >> >> > ChDate = '20050430 00:00:00.000' >> >> > where >> >> > UserCnfgId = 1000430 >> >> > >> >> > it sometimes takes 580 units of CPU, 48433 units of read and a >> >> > duration >> >> > of >> >> > 6076 ms while it takes only 2 units of write. >> >> > I don't understand why so many reads happen and the write process is >> >> > so >> >> > slow, despite this table has only one foreign key. >> >> > >> >> > Also pay attention that the Database Size is about 30GB. >> >> > >> >> > Regards, >> >> > Sassan >> >> > >> >> >> >> >> >> >> >> >>  
Thread options
Other interesting topics
StoredProc Help...
Database Maintenance Plan Grief Combine 3 Queries into One stored procedure Commenets on Running Citrix on SQL Server? Anyone doing this? open table Question on Hardware failover of the MS SQL Server 2000 DB synonyms in SQL server 2000 stupid optimizer tricks Alert & Cache Hit Ratio |
|||||||||||||||||||||||
