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.
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
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.
adrshen 