Boosting SQL Server throughput - Part I

Recently, I came across nifty things, like DBCC PINTABLE ( putting sql server table's data pages in memory (RAM), they are stored in Physical disk (HDD) ) , excellent DMV goodies like db_missing_index_details, and TABLESAMPLE

All three are worthy of a detailed explanation here, but however, I am not a story freak, so my story telling about all three, will go short...

DBCC PINTABLE

Whenever, I get into any sort of SQL crisis, I usually find a DBCC command to probe it, or to fix it. My story begins like this .... My Project leader, Mr. Arun Patel, approached me, he had a damn slow report dashboard, ( fully loaded with 4 big charts, and couple of divs with nested datagrids), showing data in condensed ( everywhere count and group by aggregations ) form. :(

The worktables were 5 in number, and data rows were in millions.

My acumen, directed me to tear down stored procedures. I skimmed through queries, execution plan, did little tweaks like changing 'in' clause , setting nocount on, and several more ..... But, unfortunately could not get any luck, in terms of faster throughput. Now I moved to perfmon.exe to inquire into, what was getting cooked in server, when dashboard was getting loaded. I was taken aback, the 'average disk queue lengh' was surging like anything. Clearly showing hardware bottleneck of throughput, I asked my Project leader to get a high RPM, and high cache HDD, he returned 'We cannot raise our costs, we have already PUT more RAM and CPU strength' ----------------------||

Now, I had only thing in my mind, use bit of RAM, to get rid of Physical disk I/O bottleneck. I again suggested for putting Database files (mdf, ldf) in RAM DRIVE. My boss reverted, "We can not install third party RAM DRIVE software, they do no comply our data privacy policies". Again, moment of despair, :( I had suggest this because, we had 8GB of RAM, we were using only 3GB of it.  After spending a day of intense research and good luck ;), I was able to google out, DBCC PINTABLE, it keeps the data pages of tables in RAM, ( faster I/O).  

Syntax of pinning up the table in RAM is

DECLARE @db_id int, @tbl_id int
USE dbEmailFramework
SET @db_id = DB_ID('dbEmailFramework')
SET @tbl_id = OBJECT_ID('dbEmailFramework..tblfinalRecordbyDateRange')
DBCC PINTABLE (@db_id, @tbl_id)

 reversal of unpinning up the table in RAM is

DECLARE @db_id int, @tbl_id int
USE dbEmailFramework
SET @db_id = DB_ID('dbEmailFramework')
SET @tbl_id = OBJECT_ID('dbEmailFramework..tblfinalRecordbyDateRange')
DBCC UNPINTABLE (@db_id, @tbl_id)

and Kudos!!!!!!!!!!!!!!!!! It worked very interestingly. The page now started loading in 20 seconds, as compared with 3 minutes of earlier load time.  Other two will be covered in next two posts  

Caution : Never Use, DBCC PINTABLE unnecessarily. it requires a lot of Memory, check for table size also, with sp_spaceused 'tablename'. if it is too large, don't pin table