Really?

Yes, if you have fat varchar columns defined in a table and using columns in your frequent/daily queries it can have some potential performance impact.

Most of the geeks say that we are creating large varchar columns because it will only use number of bytes it needs to store data, which is, totally “True” nothing wrong with it.

However, another ignored aspect of it, when this column is queried, SQL Server estimation/actual plan will look into statistics to estimate data size and request for memory grants. It will simple multiply # of rows x datatype which will take in account to request memory for execution.  Production system are usually under pressure with other legit big queries and  looking for memory request/grants is not on everyone’s list of things when doing performance optimization.

Let’s dig in to a simple example, we are going to create a simple “MemoryGrant” sad table  with three column int, date and varchar(8000) column.

USE AdventureWorks2014
GO
IF OBJECTPROPERTY(object_id('dbo.[MemoryGrant:(]'), 'IsUserTable') = 1
DROP TABLE dbo.[MemoryGrant:(]
GO
CREATE TABLE dbo.[MemoryGrant:(]
(intCol        INT           IDENTITY(100,1),
 date2Col      DATE          CONSTRAINT I_am_date_no_table_is_without_me
                             DEFAULT GETDATE(),
devcol        VARCHAR(8000)  CONSTRAINT Iamdev_with20yrsexperience
                             DEFAULT 'small column but declared big'
CONSTRAINT [WeNeed_PK_Right?] PRIMARY KEY CLUSTERED
(
 IntCol ASC
)
) ON [PRIMARY]
GO
--Here it is that we are going to need synthetic data

INSERT dbo.[MemoryGrant:(] (date2Col,devcolumn)
SELECT ModifiedDate,CarrierTrackingNumber FROM sales.SalesOrderDetail
GO
--total row count should be 121317

If you run the following select and turn on the execution plan and the hoover to SELECT and note Requested  & MaxUsed granted memory (highlight down)

SELECT Top 100000 IntCol,date2Col FROM dbo.[MemoryGrant:(] 
order by date2Col.

MemoryGrant1

Now if you run the following select which is the same select (mentioned above) with additional one column  “devcol”. By simply adding “devcol” ,the requested memory bump up to 187768 but Max Used memory went a little bit high and then there is “Excessive Grant” yellow bang.

SELECT Top 100000 IntCol,date2Col,devcol FROM dbo.[MemoryGrant:(] 
order by date2Col

MemoryGrant2

 

What is memory grant?

Memory grant is memory used to store row data while sorting and joining rows for query. We call it “grant” because the server requires those queries to “reserve” before actually using memory.  To improve query reliability. It is also if server under server load,a query with reserved memory is less likely to hit out-of-memory while execution is in progress, and the SQL Server prevents one query from dominating entire server memory.  More on this, here a really informative and good article.

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s