November Happy Hour will be moved to Thursday December 5th.
AI OnAI Off
High SQL-server load
Hi!
This is an ImageVault 3 issue and the three queries that you refers to comes from the following calls.
The first one comes from a search for images (winds up in DataFactory.GetImages)
The other two comes from ImprovedUrlBuilder.CreateUrl() when a url is requested for the image for a specific format (here 1661).
Do you get requests for other formats or is it only the 1661? What is that format in your db?
ImageVault3 has some scheduled jobs but I don't recognize any one that should create formats for you.
Do you have some own scheduled job that could be causing this?
/Dan
Sep 18, 2013 13:35
Hi, and I am glad getting hold of an expert.
Bear in mind I am the DBA of server, so I cant help much with your specific questions, but I 'll try.
It looks like it is 5 formats after tracing the SQL-calls for a few minutes.
They looks like this in IS_ConversionFormats:
ConversionFormatId Name Width Height DPI Format KeepAspectRatio UnsharpMask SystemFormat ICCProfile CompressionQuality AspectRatio
36 SYSTEM [NULL] [NULL] [NULL] Original 1 0 1 [NULL] [NULL] [NULL]
601 SYSTEM 723 [NULL] [NULL] Jpeg 1 0 1 [NULL] [NULL] [NULL]
1247 SYSTEM 519 [NULL] [NULL] WebSafe 1 0 1 [NULL] [NULL] [NULL]
1661 SYSTEM 120 [NULL] [NULL] Original 1 0 1 [NULL] 90 [NULL]
5788 Objektbildlista 570 [NULL] [NULL] Jpeg 1 0 0 100 0.000000
They all got NULL in Height (is that a problem?)
Our EPI-guy showed me that there is no scheduled jobs.
I noticed a setting that was something like "cascades changes in Original-pictures".
Our 2 environments (Production/Test) shows the same behaviour, and it looks like it is the same formats that generation the traffic.
/Patrik
Sep 18, 2013 14:38
I guess that the traffic originates from the website then if there are no scheduled jobs. Looks like some kind of gallery page that searches for images and then requests the url:s for them. You might try to cross reference the sql requests with the iis logs to see if you are requesting images that matches the id and conversion formats.
If that is the case, maybe you should look into the implementation of the gallery page and see if you can add cache the page in the output page cache to remove load from the dbserver.
The formats looks ok. That height is null is not an issue. It only means to keep the proportions and resize the image according to the width setting.
I don't recognize the "cascade changes in Original-pictures" setting.
Sep 18, 2013 15:05
Hi Forum
I have an issue with our ImageVault, that probably is caused by a setting we dont realize the effects of.
ImageVault is constantly sending SQL-requests even though no users are logged in, it looks like it is going through all pictures and does some stuff - picture by picture.
My guess is that it is going through all pictures to sort out which pictures has convertedformats.
This is the 3 SQL-commands that is repeatedly in DataObject_id order.
SELECT DISTINCT IS_DataObjects.DataObject_id, IS_DataObjects.Album_id, IS_DataObjects.Username, IS_DataObjects.DateAdded, IS_DataObjects.Path, IS_DataObjects.Type_id,
IS_DataObjects.Name, IS_DataObjects.Flag,
IS_FormatInformations.Format,
IS_FormatInformations.Width,
IS_FormatInformations.Height,
IS_FormatInformations.FileSize,
IS_FormatInformations.Depth,
IS_FormatInformations.Resolution,
IS_FormatInformations.ColorSpace,
IS_FormatInformations.Extension,
IS_FormatInformations.Drawable,
IS_FormatInformations.ContainsColorProfile,
ISNULL(MetaDataUserLanguage.[MetaDataValue], MetaDataDefault.[MetaDataValue]) as MetaDataDefaultValue
FROM IS_DataObjects
LEFT JOIN (SELECT IS_MetaDataLinkDataObjects.[DataObject_id], IS_MetaDataLinkDataObjects.[MetaDataValue]
FROM IS_MetaDataLinkDataObjects INNER JOIN IS_MetaData ON IS_MetaDataLinkDataObjects.[MetaData_id] = IS_MetaData.[MetaData_id]
WHERE IS_MetaData.[DefaultShow] = 1
AND IS_MetaDataLinkDataObjects.[Language] = '')
AS MetaDataDefault ON MetaDataDefault.[DataObject_id] = IS_DataObjects.[DataObject_id]
LEFT JOIN (SELECT IS_MetaDataLinkDataObjects.[DataObject_id], IS_MetaDataLinkDataObjects.[MetaDataValue]
FROM IS_MetaDataLinkDataObjects INNER JOIN IS_MetaData ON IS_MetaDataLinkDataObjects.[MetaData_id] = IS_MetaData.[MetaData_id]
WHERE IS_MetaData.[DefaultShow] = 1
AND IS_MetaDataLinkDataObjects.[Language] = 'sv')
AS MetaDataUserLanguage ON MetaDataUserLanguage.[DataObject_id] = IS_DataObjects.[DataObject_id]
LEFT JOIN IS_FormatInformations ON IS_FormatInformations.DataObject_id = IS_DataObjects.DataObject_id
WHERE 1 = 1 AND IS_DataObjects.album_id = 1837 AND (
CONVERT(DATETIME,'2013-09-17',121) < ISNULL( IS_DataObjects.ArchivedAfterDate, CONVERT(DATETIME,'9999-12-31',121))
AND
CONVERT(DATETIME,'2013-09-17',121) > ISNULL( IS_DataObjects.ArchivedBeforeDate, CONVERT(DATETIME,'1753-01-01',121))
)
go
if not exists (select * from IS_ConvertedDataObjects where DataObject_id = 1725 and ConversionFormatId = 1661 ) begin
insert into IS_ConvertedDataObjects values (1725,1661,'','',0)
end
go
SELECT IS_DataObjects.DataObject_ID, IS_DataObjects.Album_id, IS_DataObjects.Username, IS_DataObjects.DateAdded, IS_DataObjects.Path, IS_DataObjects.Type_id, IS_DataObjects.Name, IS_DataObjects.Flag, IS_FormatInformations.Format, IS_FormatInformations.Width, IS_FormatInformations.Height, IS_FormatInformations.FileSize, IS_FormatInformations.Depth, IS_FormatInformations.Resolution, IS_FormatInformations.ColorSpace, IS_FormatInformations.Extension, IS_FormatInformations.Drawable, IS_FormatInformations.ContainsColorProfile, (SELECT TOP 1 IS_MetaDataLinkDataObjects.[MetaDataValue] FROM IS_MetaDataLinkDataObjects INNER JOIN IS_MetaData ON IS_MetaDataLinkDataObjects.[MetaData_id] = IS_MetaData.[MetaData_id] WHERE IS_MetaData.[DefaultShow] = 1 AND IS_MetaDataLinkDataObjects.[DataObject_id] = IS_DataObjects.[DataObject_id]
AND (IS_MetaDataLinkDataObjects.[Language] = 'sv' OR IS_MetaDataLinkDataObjects.[Language] = '')
order by IS_MetaDataLinkDataObjects.[Language] desc
) AS MetaDataDefaultValue FROM IS_DataObjects LEFT JOIN IS_FormatInformations ON IS_FormatInformations.DataObject_id = IS_DataObjects.DataObject_id WHERE IS_DataObjects.DataObject_id = 1725
go