Sql-server – Identify performance bottleneck in SQL Server, checked the usual suspects

performancesql serversql-server-2012

I'm trying to identify what users are describing as slow application performance. I have a hunch it may be related to the application itself and not an issue with the SQL server. I've went over many of the usual suspects to identify common bottlenecks.

Is there anything i interpreted incorrectly?
Is there anything i neglected to review?

The MS SQL server (11.0.5058) is running on Hyper-V, 2012r2 with the following assigned to the guest: 72GB ram, 16 processors, 4 disk controllers with tempDB, OS, DB, Program Files all on separate VHDs.

Ran perfmon over the course of a day

% Processor Time < 40% Max

Avg Disk Q Length 0.003 Avg – 0.41 Max

% Disk Time Avg 0.0038

Avg Avail MBytes 5010

Buffer Cache Hit Ratio 99.996 Avg 98.614 Min

I don't see any issues with disk, mem, or processor. Where should i look next?

Best Answer

I'm going to be brutally honest here: those are not the right metrics to look at, and wherever you're looking that gives you those metrics is wildly outdated. Those are not the usual suspects.

In the year 2018 (and indeed, for the last several years), the way you diagnose a slow SQL Server is by using wait stats. SQL Server is constantly tracking what queries are waiting on, and you can see it in the DMV sys.dm_os_wait_stats.

My favorite way to visualize that is with sp_BlitzFirst @SinceStartup = 1. (Disclaimer: it's my favorite because I wrote it, and it's free & open source.)

Try editing your answer to include a screenshot of your wait stats from sp_BlitzFirst, and we may be able to get you a lot closer.

Related Solutions

Sql-server – SQL Server bulk insert performance

Below are some good ways to improve BULK INSERT operations :

Using TABLOCK as query hint.
Dropping Indexes during Bulk Load operation and then once it is completed then recreating them.
Changing the Recovery model of database to be BULK_LOGGED during the load operation.
If the target has Clustered Index then specifying ORDER BY clause in the bulk insert operation will increase the speed of BULK loading.
Using Trace Flag 610 at the beginning of BULK INSERT operation.

The max degree of parallelism should be configured on the server rather than the default. You can refer to my answer on how it configure it here.

Some good reference with interesting stats are given in The Data Loading Performance Guide here . Have a Look at BATCHSIZE and ROWS_PER_BATCH as well in the guide.

Sql-server – How to identify the number of times a View is called in SQL Server

Here is one not-overly-scientific way to do it, it is quite possible that it will miss references due to, say, indexed view expansion, and get you false positives if the batch text contains your view name in comments or as part of another entity name or string. Also won't work on earlier versions of SQL Server (which is why it is important to tag your question with the minimum version of SQL Server you need to support).

SELECT t.text,s.execution_count,s.last_execution_time
FROM sys.dm_exec_query_stats AS s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
WHERE t.text LIKE '%your-view-name%';

Also note this only contains metrics going back to the last service restart, RECONFIGURE, etc.

Best Answer

Related Solutions

Sql-server – SQL Server bulk insert performance

Sql-server – How to identify the number of times a View is called in SQL Server

Related Question