Performance matters
Ensuring your applications run quickly is vital in an age where users expect immediate results. Most modern computers are high-speed – smoking fast – even compared to machines from 5-10 years ago. Unless the application is doing complex maths or rendering images in games, chances are your computer is only idling along. That is, unless it is accessing data from files or a database. Most of the applications I work with have a lot of data in SQL server. Hundreds of GB of data are common. Sometimes, a database has several TB of data. Accessing such a database requires care to ensure the correct data is accessed in a way that keeps applications feeling snappy, but that isn’t always an easy thing to do.
When using ORM tools like Entity Framework, there is quite a gap between what you write as a developer and the SQL generated by the ORM. Sometimes, the most straightforward C# commands create thousands of lines of SQL (with Entity Framework – not so much in EFCore).
There are many reasons why an SQL server may appear to be slow, and it is almost always because we, as developers, write code or SQL that is inefficient or we haven’t created the correct Indexes, Views or Stored Procedures.
Who is doing what?
Regardless of ‘who is to blame’, one thing you always need to do is to look to see what it is that is hitting the database and causing performance issues. The SQL below helps to show what is running: –
SELECT
r.session_id
,st.TEXT AS batch_text
,SUBSTRING(st.TEXT, statement_start_offset / 2 + 1, (
(
CASE
WHEN r.statement_end_offset = - 1
THEN (LEN(CONVERT(NVARCHAR(max), st.TEXT)) * 2)
ELSE r.statement_end_offset
END
) - r.statement_start_offset
) / 2 + 1) AS statement_text
,qp.query_plan AS 'XML Plan'
,r.cpu_time, r.total_elapsed_time
,r.logical_reads, r.writes, r.dop
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
ORDER BY cpu_time DESC
