November 8, 2017

SQL Performance Troubleshooting (Part 1)

SQL Performance Troubleshooting (Part 1)

At our current client, we have been experiencing a few performance issues, mainly around SQL Server. As a result, I looked around and found various snippets of SQL that are quite helpful in diagnosing various issues, so I thought I would create a set of blog posts to share them with, and my future self ;)

Statistics

Statistics can provide you with information as to what impact the query you are executing is having on the SQL Server. In order to enable statistics on the query you need to include the following statements as part of your query:


SET STATISTICS IO ON
SET STATISTICS TIME ON

Now when you run your script you will see some output looking similar to this:

SQL Server Execution Times:
CPU time = 5ms, Elapsed time = 5ms.

Table 'ServiceCall'. Scan count 1, logical reads 695, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

On a high level, what this tells you the duration of the query as well as the number of logical reads, physical reads and how many times a table was scanned. In general, the lower the logical reads the faster the query will be be. With this information we can see if we have an optimal query, so lets go ahead and dig deeper into what some of the results mean.

Scan Count

This tells you if a plan caused an object to be read repeatedly. You can see identify what object this was from the query plan. It will always remain constant unless you change the actual query itself.

Logical Reads

This value tells you how many pages were read from the data cache. This is the most important number to focus on and you should try to reduce this by changing indexes and/or your actual query structure as well as joins and WHERE clauses.

Physical Reads

This represents the actual pages read from disk. You will see this number reduce to zero every time you run the query as more and more data is being cached. Typically, you can ignore this value.

That's it for statistics, now we will briefly look at the connections currently made to the database.

SQL Server Connection and Session Info

In certain instances it may be useful to see who is connected to the server and what resources they are using. In order to do this, you can use the sys.dm_exec_sessions view as follows:


SELECT
    database_id,
    session_id,
    status,
    login_time,
    cpu_time, --In Milliseconds
    memory_usage, --In 8kb pages
    reads,
    writes,
    logical_reads,
    host_name,
    program_name,
    host_process_id,
    client_interface_name,
    login_name as database_login_name,
    last_request_start_time
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
ORDER BY cpu_time DESC;

What this will tell you is the current connections to the server, as well as the CPU time, reads/logical reads as well as writes. Using this information, you can see if a session is consuming a lot of a particular type of resource.

One thing to note that is if you have multiple applications running against the same database, you can add the ;Application Name=XYZ command to the connection string. This will then be set for the program_name result in the query above.

Well that wraps up this post. In the following post we will look at various other queries we can use to identify other issues in your database.

Until next time...keep learning!