Grid control in SSMS

Question

I notice in SSMS (SQL Server Management Studio 2016), the query results return within a blink of a second (above 10k+ rows). The result table/grid scroll perfectly smooth, and have an extremely low memory footprint (~80MB) on SSMS. This grid/view-like control way out perform either ListView (~200MB, 2-3 seconds) and DataGrid (~600MB, 8-10 seconds). Even if I turn off all visualization or tweak cancententscroll or fix its height to optimize the speed, they still perform far behind the grid in SSMS, still with sluggish scrolling and GUI operation.

What is behind the grid control used in SSMS that make it so smooth?

Good coding practices, available since the VB6 days. Not loading *more* data than you can show, scrolling and data virtualization. There's nothing wrong with ListView or DataGrid - most likely SSMS is using one of them — Panagiotis Kanavos, Jul 06 '17 at 10:11
Notice that your screenshot shows only *16* rows, not 10K. There's no reason to load all 10K rows in memory when you'll show only 16. You can load 100 and use virtualization. By the time the user scrolls down the first 10 records, the application has alread loaded the next 100 without the user noticing — Panagiotis Kanavos, Jul 06 '17 at 10:15
I can change the image. even with virtualization it would not get that speed. refer to my other grid/virtualization question.. or else I wont start a bounty. anyone who has work with large data on wpf control should experience this — KMC, Jul 06 '17 at 10:28
No they don't - they use data binding and virtualization so they *avoid* this. The memory sizes you mention are huge - it means that you loaded all results in memory and probably added all of them as rows to the grid as well. Virtualization means that the data *isn't* loaded until it's needed. There's nothing sluggish about a grid that's loaded only 100 rows — Panagiotis Kanavos, Jul 06 '17 at 10:48
I am guessing -- it is likely to be a custom component developed in C (or less likely C++ / MFC). We are ask Microsoft to open source it ! (https://opensource.microsoft.com/) — Subbu, Jul 06 '17 at 14:24
Is it really a `DataGrid`? `DataGridView` in VirtualMode is *really fast* — ASh, Jul 06 '17 at 17:23
I believe it's a 100% custom control, written in .NET. It's Microsoft.SqlServer.Management.UI.Grid.GridControl (and derivates), located (on my SQL 2014 setup) in C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.GridControl\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.GridControl.dll Since this is not a redistribuable, you're not supposed to reuse it. — Simon Mourier, Jul 06 '17 at 22:38
@PanagiotisKanavos, with or without virtualization - I cannot get both fast loading and smooth scrolling. I ran into similar situation like [this SO post](https://stackoverflow.com/questions/6680879/wpf-datagrid-is-very-slow-to-render) so I start looking into other software's grid, specifically the SSMS's grid. — KMC, Jul 12 '17 at 16:12

Simon Mourier · Accepted Answer · 2021-08-02T14:59:13.300

SSMS grid is not C++, it's not a ListView nor a DataGrid, it does not uses Windows native controls, it's "just" a custom .NET control named GridControl (in a Microsoft.SqlServer.Management.UI.Grid namespace) that belongs to an assembly named Microsoft.SqlServer.GridControl.dll.

You can find it in various places: in the GAC, in %ProgramFiles(x86)%\Common Files\Microsoft Shared\SQL Server Developer Tools, in %ProgramFiles(x86)%\Microsoft SQL Server Management Studio 18\Common7\IDE, in Visual Studio files, etc.

It's not a redistributable binary AFAIK, so you're not supposed to ship it, it's not documented, and it's not a full-featured grid like others. However, as you found out, it's lightweight and it can be fast, as fast as your underlying data access.

If you want to play with it, here's a small Winforms C# sample (a 10000 x 256 grid, which is 2,5 million cells that opens instantly) that demonstrates how to use it:

using System;
using System.Drawing;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.UI.Grid;

namespace WindowsFormsApp1
{
    public partial class Form1 : Form
    {
        private GridControl _control = new GridControl();

        public Form1()
        {
            InitializeComponent();

            for (int i = 0; i < 256; i++)
            {
                _control.AddColumn(new GridColumnInfo { HeaderType = GridColumnHeaderType.Text, IsUserResizable = true });
                _control.SetHeaderInfo(i, "Column " + i, null);
            }

            _control.Dock = DockStyle.Fill;
            _control.GridStorage = new GridStorage();
            Controls.Add(_control);
        }
    }

    // represents a datasource
    public class GridStorage : IGridStorage
    {
        public long EnsureRowsInBuf(long FirstRowIndex, long LastRowIndex)
        {
            return NumRows(); // pagination, dynamic load, virtualization, could happen here
        }

        public void FillControlWithData(long nRowIndex, int nColIndex, IGridEmbeddedControl control)
        {
            // for cell edition
            control.SetCurSelectionAsString(GetCellDataAsString(nRowIndex, nColIndex));
        }

        public string GetCellDataAsString(long nRowIndex, int nColIndex)
        {
            // get cell data
            return nRowIndex + " x " + nColIndex;
        }

        public int IsCellEditable(long nRowIndex, int nColIndex)
        {
            return 1; // 1 means yes, 0 means false
        }

        public long NumRows()
        {
            return 10000;
        }

        public bool SetCellDataFromControl(long nRowIndex, int nColIndex, IGridEmbeddedControl control)
        {
            // when a cell has changed, you're supposed to change your data here
            return true;
        }

        public Bitmap GetCellDataAsBitmap(long nRowIndex, int nColIndex) => throw new NotImplementedException();
        public void GetCellDataForButton(long nRowIndex, int nColIndex, out ButtonCellState state, out Bitmap image, out string buttonLabel) => throw new NotImplementedException();
        public GridCheckBoxState GetCellDataForCheckBox(long nRowIndex, int nColIndex) => throw new NotImplementedException();
    }
}

Here is what it looks like. You can scroll without any slowdown, on a decent computer.

what is the lightest way to distribute this grid in applications that does not depend on SQL Server? — denfromufa, Oct 28 '18 at 04:28
Technically, I think you just have to copy it somewhere. Legally, I don't think you have the right to do it as it belongs to Microsoft, but I'm no expert at all in that area. — Simon Mourier, Oct 28 '18 at 07:39

Grid control in SSMS

1 Answers1

Linked