SQL Server – how can a select be so slow [closed]

Posted on

Question :

I was wondering if it is normal to have a simple query like below taking 6-7 seconds on a table with ‘only’ 300 000 rows. I’m working on SQL server 2014 and a RDBMS taking so much time to return “not that much” data is quite surprising to me.

  ,[ac] FROM .[dbo].[MyTable]

Here is the table description :

Id [int] IDENTITY(1,1) NOT NULL,
b [nvarchar](50) NULL,
c [nvarchar](30) NULL,
d [int] NOT NULL,
e [nvarchar](350) NULL,
f [nvarchar](350) NULL,
g [int] NULL,
h [int] NULL,
i [int] NULL,
j [int] NOT NULL,
k [float] NOT NULL,
l [int] NOT NULL,
m [float] NOT NULL,
n [float] NOT NULL,
o [bit] NOT NULL,
p [float] NOT NULL,
q [int] NOT NULL,
r [nvarchar](max) NULL,
s [int] NULL CONSTRAINT [DF_s]  DEFAULT ((0)),
t [int] NULL,
u [int] NULL,
v [int] NULL,
w [bit] NULL,
x [int] NULL,
y [int] NULL,
z [int] NULL,
aa [nvarchar](30) NULL,
ab [bit] NOT NULL CONSTRAINT [DF_ab]  DEFAULT ((0)),

I would like to know :

  1. WHY is this taking that long?
  2. WHAT can be done to improve simple queries like that

Answer :

You are not testing SQL Server performance.

You are mostly testing your local PC and network performance, via SSMS.

If you run this in SSMS, then SSMS has to render 300,000 rows.
The default is grid mode which takes a lot of resources.
And those 300,000 rows have to come over the network.

Change SSMS to discard those 300,000 rows:

Change SSMS to discard those 300,000 rows

Yes its normal when you run on a local disk or local machine.
Here are the stats why it takes 5 to 6 seconds.

From the table schema you provided average row size is 1700 bytes so approx in a page it can store only 4 rows

= 8060 (a page size) /1500 = 5.3, rounding to lowest value which is 4 rows.

= 300000 / 5, 55832 pages is required to store 300K records in that table

= 55832 * 8 KB = 446650.1241 Kb required to store the data

= 446650.1241 / 1024 = 436 MB required to store the data

So when you retrieve you are extracting around 300 to 400 Mb data from the disk. Lets take 100Mbps disk, so it takes 3 to 4 seconds to retrieve it and 2 seconds to render and show it in SSMS.

Hope this makes sense.

Leave a Reply

Your email address will not be published. Required fields are marked *