|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Strange Timeout problem.this morning everyone in the company is getting timeouts when attempting to save image data (tif). It seems to work if the image data is only a few a KB. But anything over 30K seems to hang and timeout after a minute. And, it's only timing out on saving the image data. All other Select statements, reports, data saves are working fine. From time to time a few connections do timeout, but it's usually only a couple of systems and not for very long. This has been going on all day with this one application. I tested an older version of the app just to make sure there wasn't something in the newer version, but the older version timed out exactly the same way. We're using a stored procedure to save new records and update existing ones. We're only updating one record at a time within a single transaction. Normal image size is from 20 - 500K. Sometimes it goes as high as 1-2 megs, but that's never been an issue before. We checked the drive, it's got 84 gigs free. This is an enterprise system with a few hundred users from Boston to LA. Does anyone have any ideas were to look? TIA, -Steve- No, not yet. But I.T. is considering it. It's huge deal to do it in the
middle of the day. <jroo***@gmail.com> wrote in message Show quote news:1127411172.378346.322940@g14g2000cwa.googlegroups.com... > Reboot yet? ;) > Sounds like you need to restart SQL server as you've both suggested.
That does seem very strange. Yosh Show quote "Steve Zimmelman" <skz@charter.nospam.net> wrote in message news:O%23r7fu5vFHA.3452@TK2MSFTNGP14.phx.gbl... > We have an application that has been running fine for several months. > Then this morning everyone in the company is getting timeouts when > attempting to save image data (tif). > > It seems to work if the image data is only a few a KB. But anything over > 30K seems to hang and timeout after a minute. And, it's only timing out > on saving the image data. All other Select statements, reports, data > saves are working fine. > > From time to time a few connections do timeout, but it's usually only a > couple of systems and not for very long. This has been going on all day > with this one application. I tested an older version of the app just to > make sure there wasn't something in the newer version, but the older > version timed out exactly the same way. > > We're using a stored procedure to save new records and update existing > ones. We're only updating one record at a time within a single > transaction. Normal image size is from 20 - 500K. Sometimes it goes as > high as 1-2 megs, but that's never been an issue before. > > We checked the drive, it's got 84 gigs free. > > This is an enterprise system with a few hundred users from Boston to LA. > > Does anyone have any ideas were to look? > > TIA, > -Steve- > When was the last time you rebuilt your indexes?
Show quote "Steve Zimmelman" <skz@charter.nospam.net> wrote in message news:O%23r7fu5vFHA.3452@TK2MSFTNGP14.phx.gbl... > We have an application that has been running fine for several months. > Then this morning everyone in the company is getting timeouts when > attempting to save image data (tif). > > It seems to work if the image data is only a few a KB. But anything over > 30K seems to hang and timeout after a minute. And, it's only timing out > on saving the image data. All other Select statements, reports, data > saves are working fine. > > From time to time a few connections do timeout, but it's usually only a > couple of systems and not for very long. This has been going on all day > with this one application. I tested an older version of the app just to > make sure there wasn't something in the newer version, but the older > version timed out exactly the same way. > > We're using a stored procedure to save new records and update existing > ones. We're only updating one record at a time within a single > transaction. Normal image size is from 20 - 500K. Sometimes it goes as > high as 1-2 megs, but that's never been an issue before. > > We checked the drive, it's got 84 gigs free. > > This is an enterprise system with a few hundred users from Boston to LA. > > Does anyone have any ideas were to look? > > TIA, > -Steve- > Rebuild Indexes? I thought we left that necessity behind with xBase...
Show quote "Yosh" <yoshi@nospam.com> wrote in message news:elaCWO6vFHA.1028@TK2MSFTNGP12.phx.gbl... > When was the last time you rebuilt your indexes? Is there a lot of delete or update activity on the images?
Show quote "Steve Zimmelman" <skz@charter.nospam.net> wrote in message news:O%23r7fu5vFHA.3452@TK2MSFTNGP14.phx.gbl... > We have an application that has been running fine for several months. > Then this morning everyone in the company is getting timeouts when > attempting to save image data (tif). > > It seems to work if the image data is only a few a KB. But anything over > 30K seems to hang and timeout after a minute. And, it's only timing out > on saving the image data. All other Select statements, reports, data > saves are working fine. > > From time to time a few connections do timeout, but it's usually only a > couple of systems and not for very long. This has been going on all day > with this one application. I tested an older version of the app just to > make sure there wasn't something in the newer version, but the older > version timed out exactly the same way. > > We're using a stored procedure to save new records and update existing > ones. We're only updating one record at a time within a single > transaction. Normal image size is from 20 - 500K. Sometimes it goes as > high as 1-2 megs, but that's never been an issue before. > > We checked the drive, it's got 84 gigs free. > > This is an enterprise system with a few hundred users from Boston to LA. > > Does anyone have any ideas were to look? > > TIA, > -Steve- > Do you have a separate textimage filegroup, or is it embedded in the primary
filegroup of the database? Is "text in row" enabled? If most of the image modification activity is inserts, then you should probably put the textimage on its own storage subsystem (RAID-1 or RAID 0+1--same as a transaction log). There may be fragmentation--ether within the file or on the disk. In addition, you may want to check your anti-virus software and make sure to exclude the database, transaction log and textimage files. You should also exclude them from the Indexing Service. Show quote "Brian Selzer" <br***@selzer-software.com> wrote in message news:uBUlWP6vFHA.460@TK2MSFTNGP15.phx.gbl... > Is there a lot of delete or update activity on the images? > > "Steve Zimmelman" <skz@charter.nospam.net> wrote in message > news:O%23r7fu5vFHA.3452@TK2MSFTNGP14.phx.gbl... >> We have an application that has been running fine for several months. >> Then this morning everyone in the company is getting timeouts when >> attempting to save image data (tif). >> >> It seems to work if the image data is only a few a KB. But anything over >> 30K seems to hang and timeout after a minute. And, it's only timing out >> on saving the image data. All other Select statements, reports, data >> saves are working fine. >> >> From time to time a few connections do timeout, but it's usually only a >> couple of systems and not for very long. This has been going on all day >> with this one application. I tested an older version of the app just to >> make sure there wasn't something in the newer version, but the older >> version timed out exactly the same way. >> >> We're using a stored procedure to save new records and update existing >> ones. We're only updating one record at a time within a single >> transaction. Normal image size is from 20 - 500K. Sometimes it goes as >> high as 1-2 megs, but that's never been an issue before. >> >> We checked the drive, it's got 84 gigs free. >> >> This is an enterprise system with a few hundred users from Boston to LA. >> >> Does anyone have any ideas were to look? >> >> TIA, >> -Steve- >> > > "Brian Selzer" <br***@selzer-software.com> wrote in message It's a normal table in a database that is used by other smaller processes. > Do you have a separate textimage filegroup, or is it embedded in the > primary filegroup of the database? The current size of the database file is about 3.5 gigs. > Is "text in row" enabled? I don't know what this means. I'm not a SQL Admin. I manage the client software development. -Steve- You can put text images on their own filegroup, which can significantly
improve insert performance provided the activity is primarily inserts for the images. "Text In Row" is a table option in SQL Server 2000. It is a mechanism whereby the data from text, ntext or image columns is physically stored with the row in the clustered index (or heap if there's no clustered index), provided the aggregate size of all text, ntext or image columns for a row doesn't exceed a specified threshold. If "Text In Row" is turned off, then only a 16 byte pointer for each text, ntext or image column is stored in the clustered index (or heap), and the actual data is stored in a textimage page. Show quote "Steve Zimmelman" <skz@charter.nospam.net> wrote in message news:OnOC9J7vFHA.2312@TK2MSFTNGP14.phx.gbl... > "Brian Selzer" <br***@selzer-software.com> wrote in message >> Do you have a separate textimage filegroup, or is it embedded in the >> primary filegroup of the database? > > It's a normal table in a database that is used by other smaller processes. > The current size of the database file is about 3.5 gigs. > >> Is "text in row" enabled? > I don't know what this means. I'm not a SQL Admin. I manage the client > software development. > > -Steve- > > Hi Brian,
So is it advisable to use "Text In Row"? Is this faster? The SQL docs seem to suggest that In Row Text is for small image data. Some of the images being scanned are 10 or more pages, and all need to be saved in the same record. How can I determine if "Text In Row" has been turned on? When you say "filegroup", do you mean its own database file (.MDF)? The images are mostly inserted, but obviously there are select statements going on all day long for the retrieval and viewing of the images. Forgive my ignorance, I'm haven't spent that much time setting up SQL Server. Unfortunately my client doesn't have a SQL guy on staff. They have grown much faster than their IT dept. Thanks, -Steve- Show quote "Brian Selzer" <br***@selzer-software.com> wrote in message news:%23LbseB8vFHA.2728@TK2MSFTNGP14.phx.gbl... > You can put text images on their own filegroup, which can significantly > improve insert performance provided the activity is primarily inserts for > the images. "Text In Row" is a table option in SQL Server 2000. It is a > mechanism whereby the data from text, ntext or image columns is physically > stored with the row in the clustered index (or heap if there's no > clustered index), provided the aggregate size of all text, ntext or image > columns for a row doesn't exceed a specified threshold. If "Text In Row" > is turned off, then only a 16 byte pointer for each text, ntext or image > column is stored in the clustered index (or heap), and the actual data is > stored in a textimage page. > > "Steve Zimmelman" <skz@charter.nospam.net> wrote in message > news:OnOC9J7vFHA.2312@TK2MSFTNGP14.phx.gbl... >> "Brian Selzer" <br***@selzer-software.com> wrote in message >>> Do you have a separate textimage filegroup, or is it embedded in the >>> primary filegroup of the database? >> >> It's a normal table in a database that is used by other smaller >> processes. The current size of the database file is about 3.5 gigs. >> >>> Is "text in row" enabled? >> I don't know what this means. I'm not a SQL Admin. I manage the client >> software development. >> >> -Steve- >> >> > > SELECT OBJECTPROPERTY(OBJECT_ID(N'<tableName>'), N'TableTextInRowLimit')
If the result is zero then the text in row property is disabled, otherwise the returned value is the maximum number of bytes allowed in a row for all text, ntext or image columns in the table. Whether it's advisable to use "text in row" depends on the situation. Separating the image data requires an additional read to return it, but removing the image data means that you can store more rows per page in the clustered index, which can improve overall performance--especially if most queries don't return the image. Storing the image data on its own physical disk subsystem mitigates the performance penalty of the additional read, and will also considerably improve insert performance. Each database can have multiple filegroups. A filegroup is a grouping of physical data files. When you create a table or index (including implicit indexes created by primary key or unique constraints), you can specify the filegroup from which the table or index consumes physical pages. In this way you can move tables or indexes with high levels of activity onto a disk subsystem separate from the rest of the database, thus significantly improving overall performance. With the TEXTIMAGE_ON clause you can specify the filegroup from which physical pages are consumed for text, ntext or image columns. Show quote "Steve Zimmelman" <skz@charter.nospam.net> wrote in message news:%23pPqTb%23vFHA.596@TK2MSFTNGP12.phx.gbl... > Hi Brian, > > So is it advisable to use "Text In Row"? Is this faster? The SQL docs > seem to suggest that In Row Text is for small image data. Some of the > images being scanned are 10 or more pages, and all need to be saved in the > same record. > > How can I determine if "Text In Row" has been turned on? > > When you say "filegroup", do you mean its own database file (.MDF)? The > images are mostly inserted, but obviously there are select statements > going on all day long for the retrieval and viewing of the images. > > Forgive my ignorance, I'm haven't spent that much time setting up SQL > Server. Unfortunately my client doesn't have a SQL guy on staff. They > have grown much faster than their IT dept. > > Thanks, > -Steve- > > "Brian Selzer" <br***@selzer-software.com> wrote in message > news:%23LbseB8vFHA.2728@TK2MSFTNGP14.phx.gbl... >> You can put text images on their own filegroup, which can significantly >> improve insert performance provided the activity is primarily inserts for >> the images. "Text In Row" is a table option in SQL Server 2000. It is a >> mechanism whereby the data from text, ntext or image columns is >> physically stored with the row in the clustered index (or heap if there's >> no clustered index), provided the aggregate size of all text, ntext or >> image columns for a row doesn't exceed a specified threshold. If "Text >> In Row" is turned off, then only a 16 byte pointer for each text, ntext >> or image column is stored in the clustered index (or heap), and the >> actual data is stored in a textimage page. >> >> "Steve Zimmelman" <skz@charter.nospam.net> wrote in message >> news:OnOC9J7vFHA.2312@TK2MSFTNGP14.phx.gbl... >>> "Brian Selzer" <br***@selzer-software.com> wrote in message >>>> Do you have a separate textimage filegroup, or is it embedded in the >>>> primary filegroup of the database? >>> >>> It's a normal table in a database that is used by other smaller >>> processes. The current size of the database file is about 3.5 gigs. >>> >>>> Is "text in row" enabled? >>> I don't know what this means. I'm not a SQL Admin. I manage the client >>> software development. >>> >>> -Steve- >>> >>> >> >> > > Hi Brian.
I appreciate your time and information. It is very helpful. Thanks! -Steve- Show quote "Brian Selzer" <br***@selzer-software.com> wrote in message news:eJzevw%23vFHA.3312@TK2MSFTNGP09.phx.gbl... > SELECT OBJECTPROPERTY(OBJECT_ID(N'<tableName>'), N'TableTextInRowLimit') > > If the result is zero then the text in row property is disabled, otherwise > the returned value is the maximum number of bytes allowed in a row for all > text, ntext or image columns in the table. "Brian Selzer" <br***@selzer-software.com> wrote in message Not that I am aware of. Once the images are scanned and attached to patient news:uBUlWP6vFHA.460@TK2MSFTNGP15.phx.gbl... > Is there a lot of delete or update activity on the images? records, that's pretty much it. Unless they have to rotate a few pages within the image data. We are storing tiff images in an image data field. Here are the stored procs we use: /*** New Image Record ***/ Create Procedure [dbo].[NewImageRec_SP] @FamNo VarChar(15),@PerNo VarChar(2), @FolderID Int, @Descr VarChar(100), @ImgDat Image, @ImgCount Int, @CreateUser VarChar(6), @DatType VarChar(1), @@NewID Int OutPut As Insert Into Images (FamilyNo,PersonNo,Folder_ID,Descr,ImageCount,CreatedUser, ImageSize,CreatedDate,ImageData,DataType) Values (@FamNo,@PerNo,@FolderID,@Descr,@ImgCount,@CreateUser, DataLength(@ImgDat),GetDate(),@ImgDat,@DatType) Set @@NewID = SCOPE_IDENTITY() /*** Update the Image Record ***/ Create Procedure [dbo].[UpdateImageRec_SP] @ID int, @FolderID Int, @Descr VarChar(100), @ImgDat Image, @ImgCount Int, @LastUser VarChar(6) As /* Update the Image Data */ DECLARE @ptrval binary(16), @DL Int SELECT @ptrval = TEXTPTR(Images.ImageData), @DL = ISNULL(DataLength(Images.ImageData),0) From Images Where ID = @ID /* Make sure pointer is not null, otherwise it will raise an exception */ IF (@ptrval = NULL) Or (@DL = 0) Begin Update Images Set ImageData = '123456789' Where ID = @ID SELECT @ptrval = TEXTPTR(Images.ImageData) From Images Where ID = @ID End WRITETEXT Images.ImageData @ptrval @ImgDat /* Update the rest of the record */ Update Images Set Folder_ID = Case When (@FolderID > 0) Then @FolderID Else Folder_ID End, Descr = CASE WHEN (@Descr <> '') THEN @Descr ELSE Descr END, ImageCount = @ImgCount, ImageSize = DataLength(ImageData), LastUser = @LastUser, LastUpdate = GetDate() Where ID = @ID Thanks Everyone for all your advise.
After I.T. reboot the server last night, the problem still persisted. So I rebuilt all the indexes on the image table and the problem went away. Strange though, there was a maintenance plan in effect that was re-indexing every Sunday morning at 2am. Regards, -Steve-  |
|||||||||||||||||||||||
Other interesting topics