SQL Server Locks and Performance (Large Lock Requests)
-
We have a database on one of our SQL Servers which is killing performance. I'm trying to get to the bottom of this as we have additional plans for this SQL Server. The database is a mission critical DB which, we are adding to an AG. I've attached a screen snip of statistics for the SQL Server. Keep in mind, there's only 1 database on this server currently. Any advice would be greatly appreciated.
-
you have an awfull lot of plans in the cache for just one database.
are you using stored procs/parameterised queries? - have you tried turning on the setting for ad-hoc workloads?
looking at the number of transactions it looks like this is an entity framework gone mad.. especially since you are getting 0 compiles/recompiles per second - although i'm guessing here
MVDBA
-
That you for your response. I'm not too sure what ad-hoc workloads are. I believe this database is using stored procedures. This database is not utilizing EF. I'm trying to become a DBA, however, it's an undertaking. This database is also part of an AG, I forgot to mention that, or relayed incorrectly that in the original post.
-
The AG shouldn't matter (assuming you are talking about availability groups)
optimise for ad hoc workloads is a setting for the server (right click on the server in management studio and look at advanced settings)
there are a few resources you can look at
https://blog.sqlauthority.com/2017/10/20/sql-server-turn-optimize-ad-hoc-workloads/
there is also a script that advises you to turn it on or off, but i can't seem to find it at the minute
MVDBA
-
can you give us an idea of what the application does - it might help give us some context on what areas to look at
I did notice that you don't have a lot of tempdb free.... is your tempdb set up correctly for the number of CPU cores? (number of files should be number of cores -1 up to 8 as a maximum) - that would give you lots of issues with latches.
MVDBA
-
The database is a call logging database that's used for medical translation services. For instance, when a hospital needs translation services, they'd call a number and get connected to a person who speaks a particular language. Call tracking and other bits of information are logged to the database.
There are 8 TempDB files, however, they are on the C drive as well as theValetProd database. For goodness sakes, whomever did this I'd like to punch them LOL. This sucks because I cannot take the database offline. The database is being synchronized, however, isn't using the listener connection as I have to try and get a maintenance window to change the connection string.
-
You may want to look at the wait stats and see where things are taking time. The query in this posts includes links to the waits library on SQL Skills to get more information related to waits you see:
SQL Server Wait Statistics (or please tell me where it hurts…)
Sue
-
Thanks Sue.
-
so if it's a call logging system.... you probably read more than you write... can you go through the code and find "appropriate" places to use NOLOCK
NOLOCK is not a fix-all , but if you have a screen where readers are blocking writers then i'd look at the options
MVDBA
-
A quick reference for your wait states, is the Performance Dashboard Report, quickly you can find what is doing your sql server, or historical information about your waits, you can find Performance Dashboard, right click on your instance go to reports, standard reports, Performance dashboard, it comes with a recent update of your SQL Server Management Studio. Administative tasks of Always On must be another thread.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply