varbinary column making select longer
-
Have a table with varbinary(8000) column.
When carrying out a select and including this column the query takes twice as long, when I remove it the query takes half time.
Any suggestions? The column isn't included in the join or where clause
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
Maybe this hints at what's going on?
https://blog.greglow.com/2019/02/26/sql-text-vs-varcharmax-in-row-vs-out-of-row-storage/
It might be that the varbinary(8000) is stored outside the row of your table, so when you do not include that column in your query, it executes quickly, because the engine can just scan a segment of memory. When you request the varbinary column, the engine has to find where that is stored, which is not in the contiguous block of data. ... I think that's what the article is saying.
-
The varbinary column may contain large data such as BLOBs or images, which can take a long time to read. Try using data compression -it might help!
-
Sorry forgot to mentioned the table has columnstore index with partitions enabled.
-
My suspicion is that it's not so much that the query is taking longer to execute, as it is taking more time to send the results to (I'm presuming) SSMS and get them displayed.
One thing you could look to try to see if this is potentially the issue, check your wait stats for ASYNC_NETWORK_IO, once when you query the data without the varbinary, and once with. Ideally, do this during a quiet time for the app, and if you really want to make it easier to see the numbers, run "DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);" before each test.
Or, if you're handy with Extended Events you can capture the info that way.
But, and again presuming I'm on the right track here, your columnstore index and partitioning aren't going to make a difference.
-
I don't see ASYNC_NETWORK_IO waits when running the query including the column that is varbinary. There was just PAGEIOLATCH_SH wait
Just some stats
--4.33min with varbinary column
--28 sec without varbinary column
-
So presuming it's a spike in PAGEIOLATCH_SH when the query runs with the varbinary, that's an indicator that the problem *could* be the storage on the server.
Can you give some info on the specs of the server? Especially the type of disk (hard disk with spinning platters, SSD, virtual server, NVMe) and the RAM in the server? Not going to say "throw hardware at it," but that might be the road to go down.
And one more question, is this a recent problem, or has it always been this way? If it's recent, has something changed, either in the database or the server itself?
-
Its on AWS.
8 CPU and 64 RAM
I need to include the varbinary column in the query.
It is a new problem, today the query is taking 59 seconds
-
So I will note, storage could still be a problem, we're in Azure and a few of our apps had unacceptable database performance until we bumped up the storage tier the database files were on.
And to be sure I'm understanding your last post right, yesterday the time for the query that was taking 4.5 minutes took just shy of 1 minute?
Something else to look into, is there any sort of anti-virus / anti-malware on the server that might be scanning the database files? MS does recommend configuring any such AV to not "live" scan mdf / ndf / ldf (Configure antivirus software to work with SQL Server) although I would expect this to impact any use of the database, not just querying and retrieving a varbinary.
-
Yes was taking less time but today it back up around the same time. For me it seems like a storage issue, I don't think there is anything additional that can be done on the query side.
-
I notice you didnt post the query. Are you doing any SORT operations anywhere ?
----------------------------------------------------
-
To be clear... your query without including the VARBINARY might be returning 20 bytes per row and then you can't understand why it takes about twice as long to return about 400 times that amount of data for each row?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply