Sql-server – Elapsed time in SSMS is consistently higher than SQL Server Execution Time – connection issue

connectionsperformancesql serversql-server-2012

I have a fresh install of SQL Server 2012 Developer Edition on a pretty snappy laptop. I'm using it as a backend for an ASP.NET webapp. I noticed query timings that were consistently higher than I'd expect coming from a MySQL background.

For example, MiniProfiler shows select * from Comments is taking 30-50ms, when Comments has only one row in it. Simple things like that.

In investigating, I opened SQL Server Management Studio and issued a simple query:

set statistics io on
set statistics time on
go
select 1 as one;
go
set statistics io off
set statistics time off
go

This outputs what I'd expect:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

But in the right sidebar of SSMS, 'Aggregate Status > Elapsed Time' shows 77ms. Running it multiple times shows timings varying between 50ms and 80ms.

I removed the timing commands and just ran select 1 as one; (again from SSMS). Profiler again shows 0ms execution times, but SSMS shows 55ms. My first question: Is it unreasonable to expect (exponentially) better performance? If SQL Server is just slow, I don't want to waste time on this. Have a hard time believing that, though.

At this point, I believe this is a connection issue, but I'm not sure where to start. I did disable McAfee's on-access protection, to no avail. I've also tried using SQL Server auth instead of Windows auth, also with no effect.

What are your thoughts? These 50ms hits for every query really add up on pageloads, and I believe we're also seeing this in production (I'm light on the details there).

Best Answer

If you're after a system that takes 0 ms to return and render results, good luck.

This doesn't mean that SQL Server is 'just slow', though I'm not sure what your expectations for grid rendering are.

The only way to see what your server is waiting on is to look at its wait stats. If you'd like a free script that does that, head over here and check out sp_BlitzFirst (full disclosure, I contribute to this open source project).

You can run it this way to look at your server's wait stats since it started up:

EXEC sp_BlitzFirst @SinceStartup = 1

Or this way to get a sampling during a window length of your choosing:

EXEC sp_BlitzFirst @Seconds = 30, @ExpertMode = 1

And if you find a system that returns and renders results in 0 ms, drop me a line! I'd love to hear about it.

Related Question