|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
strange BLOB beahaviour
Hi everyone.
I'm a junior DBA at my firm, and have recently noticed a very strange behaviour with a certain view in my database. I have a few tables with BLOB's that are combined in one view using two select's and a UNION containing about 1.5 million rows. Everything worked fine since recently. Recently everything slowed down quite noticeably, so i begun investigating. I found out that there was a lot of CXPACKET waits, so i disabled max degree of parellelism, after that i noticed that there was a lot of PAGEUILATCH_SH waits and BUFFER latch time was extreamly big. I've rebuilded index on that table (PK CLUSTERED), but notting changed. DBCC also did not return any errors. Then i reviewed code for that view and started playing with it a bit. After i removed "Pictures IS NOT NULL" (witch is the BLOB column and not important for the logic) from where clause everything returned to normal. I really do not understand what the problem was... Server is SQL Server 2005 (9.0.3077) running on Win server 2003 R2, Quad core xeon processor and 4GB of RAM. If anyone has any idea i woul greatly appreciate it. Does the Picture column have an index? How much NULL for that column do you
have? Show quoteHide quote "grbihno" <grbi***@gmail.com> wrote in message news:h2sm4p$nnn$1@ss408.t-com.hr... > Hi everyone. > > I'm a junior DBA at my firm, and have recently noticed a very strange > behaviour with a certain view in my database. > > I have a few tables with BLOB's that are combined in one view using two > select's and a UNION containing about 1.5 million rows. Everything worked > fine since recently. > Recently everything slowed down quite noticeably, so i begun > investigating. I found out that there was a lot of CXPACKET waits, so > i disabled max degree of parellelism, after that i noticed that there was > a lot of PAGEUILATCH_SH waits and BUFFER latch time was extreamly big. > I've rebuilded index on that table (PK CLUSTERED), but notting changed. > DBCC also did not return any errors. Then i reviewed code for that view > and started playing with it a bit. After i removed > "Pictures IS NOT NULL" (witch is the BLOB column and not important for > the logic) from where clause everything returned to normal. > > I really do not understand what the problem was... > > Server is SQL Server 2005 (9.0.3077) running on Win server 2003 R2, Quad > core xeon processor and 4GB of RAM. > > If anyone has any idea i woul greatly appreciate it. > > > > No. The Picture column does not have index on it, but other coulums that are used for primary key do (clustered).
There are no nulls on Picture column because it has not null constraint defined on it. Other columns that are used in where clause are indexed and performance increased soon as i removed "Picture is not null"from the clause. The question is why? Could it be posible that sql server checks for null values in the case when not null is defined on the column? This kind of behaviour would explain the slowdown, but it's very illogical to me. When i try very simple query like select count(*) on a view that is accessing this table and i have "Picture is not null" in where clause it takes about 6 min, other time when i dont have "Picture is not null" it takes about 8 s. Show quoteHide quote >Does the Picture column have an index? How much NULL for that column do you >have? > > > >"grbihno" <grbi***@gmail.com> wrote in message >news:h2sm4p$nnn$1@ss408.t-com.hr... >> Hi everyone. >> >> I'm a junior DBA at my firm, and have recently noticed a very strange >> behaviour with a certain view in my database. >> >> I have a few tables with BLOB's that are combined in one view using two >> select's and a UNION containing about 1.5 million rows. Everything worked >> fine since recently. >> Recently everything slowed down quite noticeably, so i begun >> investigating. I found out that there was a lot of CXPACKET waits, so >> i disabled max degree of parellelism, after that i noticed that there was >> a lot of PAGEUILATCH_SH waits and BUFFER latch time was extreamly big. >> I've rebuilded index on that table (PK CLUSTERED), but notting changed. >> DBCC also did not return any errors. Then i reviewed code for that view >> and started playing with it a bit. After i removed >> "Pictures IS NOT NULL" (witch is the BLOB column and not important for >> the logic) from where clause everything returned to normal. >> >> I really do not understand what the problem was... >> >> Server is SQL Server 2005 (9.0.3077) running on Win server 2003 R2, Quad >> core xeon processor and 4GB of RAM. >> >> If anyone has any idea i woul greatly appreciate it. >> >> >> >> > By PAGEUILATCH_SH, do you mean PAGEIOLATCH_SH?
I'd also check if these BLOB pages are simply being used more. Linchi Show quoteHide quote "grbihno" wrote: > Hi everyone. > > I'm a junior DBA at my firm, and have recently noticed a very strange behaviour with a certain view in my database. > > I have a few tables with BLOB's that are combined in one view using two select's and a UNION containing about 1.5 million rows. Everything worked fine since recently. > Recently everything slowed down quite noticeably, so i begun investigating. I found out that there was a lot of CXPACKET waits, so > i disabled max degree of parellelism, after that i noticed that there was a lot of PAGEUILATCH_SH waits and BUFFER latch time was extreamly big. > I've rebuilded index on that table (PK CLUSTERED), but notting changed. DBCC also did not return any errors. Then i reviewed code for that view and started playing with it a bit. After i removed > "Pictures IS NOT NULL" (witch is the BLOB column and not important for the logic) from where clause everything returned to normal. > > I really do not understand what the problem was... > > Server is SQL Server 2005 (9.0.3077) running on Win server 2003 R2, Quad core xeon processor and 4GB of RAM. > > If anyone has any idea i woul greatly appreciate it. > > > > > >By PAGEUILATCH_SH, do you mean PAGEIOLATCH_SH? Yes i did :) sorry about that.>I'd also check if these BLOB pages are simply being used more. I'm actually testing this on a test machine that is equal to production, so there is no other user, or query using this BLOB.Show quoteHide quote >Linchi > >"grbihno" wrote: > >> Hi everyone. >> >> I'm a junior DBA at my firm, and have recently noticed a very strange behaviour with a certain view in my database. >> >> I have a few tables with BLOB's that are combined in one view using two select's and a UNION containing about 1.5 million rows. Everything worked fine since recently. >> Recently everything slowed down quite noticeably, so i begun investigating. I found out that there was a lot of CXPACKET waits, so >> i disabled max degree of parellelism, after that i noticed that there was a lot of PAGEUILATCH_SH waits and BUFFER latch time was extreamly big. >> I've rebuilded index on that table (PK CLUSTERED), but notting changed. DBCC also did not return any errors. Then i reviewed code for that view and started playing with it a bit. After i removed >> "Pictures IS NOT NULL" (witch is the BLOB column and not important for the logic) from where clause everything returned to normal. >> >> I really do not understand what the problem was... >> >> Server is SQL Server 2005 (9.0.3077) running on Win server 2003 R2, Quad core xeon processor and 4GB of RAM. >> >> If anyone has any idea i woul greatly appreciate it. grbihno (grbi***@gmail.com) writes:
Show quoteHide quote > I have a few tables with BLOB's that are combined in one view using two To be able to say something useful, I would like to see the view> select's and a UNION containing about 1.5 million rows. Everything > worked fine since recently. > Recently everything slowed down quite noticeably, so i begun > investigating. I found out that there was a lot of CXPACKET waits, so > i disabled max degree of parellelism, after that i noticed that there > was a lot of PAGEUILATCH_SH waits and BUFFER latch time was extreamly > big. I've rebuilded index on that table (PK CLUSTERED), but notting > changed. DBCC also did not return any errors. Then i reviewed code for > that view and started playing with it a bit. After i removed "Pictures > IS NOT NULL" (witch is the BLOB column and not important for the logic) > from where clause everything returned to normal. > > I really do not understand what the problem was... definition and the CREATE TABLE and CREATE INDEX statements for the underlying tables. Even better would be to see the query plans for the two cases. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Show quote
Hide quote
>grbihno (grbi***@gmail.com) writes: CREATE TABLE Pictures (>> I have a few tables with BLOB's that are combined in one view using two >> select's and a UNION containing about 1.5 million rows. Everything >> worked fine since recently. >> Recently everything slowed down quite noticeably, so i begun >> investigating. I found out that there was a lot of CXPACKET waits, so >> i disabled max degree of parellelism, after that i noticed that there >> was a lot of PAGEUILATCH_SH waits and BUFFER latch time was extreamly >> big. I've rebuilded index on that table (PK CLUSTERED), but notting >> changed. DBCC also did not return any errors. Then i reviewed code for >> that view and started playing with it a bit. After i removed "Pictures >> IS NOT NULL" (witch is the BLOB column and not important for the logic) >> from where clause everything returned to normal. >> >> I really do not understand what the problem was... > >To be able to say something useful, I would like to see the view >definition and the CREATE TABLE and CREATE INDEX statements for the >underlying tables. > >Even better would be to see the query plans for the two cases. > > >-- >Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > >Links for SQL Server Books Online: >SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx >SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx >SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > > [DocType] [char](3) NOT NULL, [DocNo1] [varchar](5) NOT NULL, [DocNo2] [int] NOT NULL, [OrderNo] [smallint] NOT NULL, [Picture] [image] NOT NULL, [Printed] [bit] NULL, [_XXYY] [char](5) NULL, [_Date] [datetime] NULL, CONSTRAINT [PK_SLIKE] PRIMARY KEY CLUSTERED ( [DocType] ASC, [DocNo1] ASC, [DocNo2] ASC, [OrderNo] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] I've uploaded plant to http://www.filefactory.com/file/ahb00e2/n/plans_zip plan A is without "Picture is not null" and plan B is with that statement. (All coumns are in my native language in this plans. So Picture = Slika DokTip = DocType DokBroj1 = DocNo1 ) Tnx. grbihno (grbi***@gmail.com) writes:
Show quoteHide quote > The one difference is that the plan with WHERE Slika IS NOT NULL has a> CREATE TABLE Pictures ( > [DocType] [char](3) NOT NULL, > [DocNo1] [varchar](5) NOT NULL, > [DocNo2] [int] NOT NULL, > [OrderNo] [smallint] NOT NULL, > [Picture] [image] NOT NULL, > [Printed] [bit] NULL, > [_XXYY] [char](5) NULL, > [_Date] [datetime] NULL, > CONSTRAINT [PK_SLIKE] PRIMARY KEY CLUSTERED > ( > [DocType] ASC, > [DocNo1] ASC, > [DocNo2] ASC, > [OrderNo] ASC > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = > > I've uploaded plant to http://www.filefactory.com/file/ahb00e2/n/plans_zip > > plan A is without "Picture is not null" and plan B is with that statement. > (All coumns are in my native language in this plans. > So > Picture = Slika > DokTip = DocType > DokBroj1 = DocNo1 > ) filter operator. As you noted, this filter does not seem necessary, since the column is not nullable. But apparently this filter is costly. I notice that the result of the COUNT(*) query is over 600000, so that is 600000 rows to check. Now, the datatype of this column is "image". This data type is deprecated, and and the preferred data type in SQL 2005 and later is varbinary(MAX). You may have to rewrite some code to get there, but in the end you will be happy, as varbinary(MAX) is very much like a regular varbinary, and you don't have all the restrictions you have with image, nor do you need this textpointer business. I don't know, but maybe there is some anachronism with image that compels SQL Server to do perform the extra filter. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Show quote
Hide quote
>grbihno (grbi***@gmail.com) writes: Thank you very much for the answer. I was also heading in that direction. Will do some testing and most likely give that recomendation to our developers.>> >> CREATE TABLE Pictures ( >> [DocType] [char](3) NOT NULL, >> [DocNo1] [varchar](5) NOT NULL, >> [DocNo2] [int] NOT NULL, >> [OrderNo] [smallint] NOT NULL, >> [Picture] [image] NOT NULL, >> [Printed] [bit] NULL, >> [_XXYY] [char](5) NULL, >> [_Date] [datetime] NULL, >> CONSTRAINT [PK_SLIKE] PRIMARY KEY CLUSTERED >> ( >> [DocType] ASC, >> [DocNo1] ASC, >> [DocNo2] ASC, >> [OrderNo] ASC >> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = >> >> I've uploaded plant to http://www.filefactory.com/file/ahb00e2/n/plans_zip >> >> plan A is without "Picture is not null" and plan B is with that statement. >> (All coumns are in my native language in this plans. >> So >> Picture = Slika >> DokTip = DocType >> DokBroj1 = DocNo1 >> ) > >The one difference is that the plan with WHERE Slika IS NOT NULL has a >filter operator. As you noted, this filter does not seem necessary, >since the column is not nullable. But apparently this filter is costly. >I notice that the result of the COUNT(*) query is over 600000, so that >is 600000 rows to check. > >Now, the datatype of this column is "image". This data type is deprecated, >and and the preferred data type in SQL 2005 and later is varbinary(MAX). >You may have to rewrite some code to get there, but in the end you will >be happy, as varbinary(MAX) is very much like a regular varbinary, >and you don't have all the restrictions you have with image, nor do you >need this textpointer business. I don't know, but maybe there is some >anachronism with image that compels SQL Server to do perform the >extra filter. > > >-- >Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > >Links for SQL Server Books Online: >SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx >SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx >SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > > Thank's again. Ivan Grbavac  
Other interesting topics
AWE on SQL Server2005
ID large queries while they execute and kill 'Generate scripts' functionality in SQL 2005 not being consistent Problem with SUM - Help please problem with new login Saving images Column Limits Problem with triggers an ntext SQLhas encountered 2 occurrence(s) of IO requests taking longer th Cartesian with one table |
|||||||||||||||||||||||
