November 7, 2022 at 4:25 pm
Hi SQL Gurus,
One of our production database is running very slow. I checked blocking , I didn't see any blocking. Also, check for running processes and look for any waittype. I see null. How to get more insights on a running sql server for a specific databases?
What all can he checked in this scenario?
Also, How to check what are the most or top 3-5 databases which are currently active on the server? any query please share and on what columns do we need to aggregate to check to see the top 5 dbs active at this point of time and taking up resources.
This is a shared SQL instance and many dbs are hosted on it. each of 3TB in size. We have like 15-20 dbs hosted on that instance. Its a testing server. it has 16cpus and 256 gb RAM. SQL Server 2017 Enterprise Edition.
Kind Regards,
Bob
November 7, 2022 at 5:56 pm
There are any number of things to check, but I generally focus on wait statistics and query times. And honestly, mostly query times. Wait statistics tell you what things are slowing down on, but not why. Queries are all the what and the why. Assuming you don't have monitoring set up, you can use the DMVs like sys.dm_exec_query_stats to see which queries currently in cache are using the most resources, running the longest, or are called most frequently. That should let you know what's up. Go from there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 7, 2022 at 7:38 pm
Before I had access to monitoring tools I found (and still do) 'sp_WhoIsActive' to be immensely helpful to gain insight as to what is happening right now.
November 7, 2022 at 7:58 pm
Do you have 15-20 3TB databases on a test server and one of the databases is production?
How much storage do you have? It sounds like you have 30-60 TB in databases. Is it possible you are running out of space? Are all the databases accessing the same volume(s)?
We use EC2 instances in AWS and it is cheaper to create one large volume, but I think it leads to slow disk access, especially if tempdb is on the same volume.
November 8, 2022 at 10:07 am
Do you have 15-20 3TB databases on a test server and one of the databases is production?
How much storage do you have? It sounds like you have 30-60 TB in databases. Is it possible you are running out of space? Are all the databases accessing the same volume(s)?
We use EC2 instances in AWS and it is cheaper to create one large volume, but I think it leads to slow disk access, especially if tempdb is on the same volume.
On Prod we have 1 copy of the database.
All servers and storage is Microsoft Azure and Azure storage.
November 8, 2022 at 10:11 am
There are any number of things to check, but I generally focus on wait statistics and query times. And honestly, mostly query times. Wait statistics tell you what things are slowing down on, but not why. Queries are all the what and the why. Assuming you don't have monitoring set up, you can use the DMVs like sys.dm_exec_query_stats to see which queries currently in cache are using the most resources, running the longest, or are called most frequently. That should let you know what's up. Go from there.
Thanks Grant for the direction path.
One more thing, I would like to ask.
End user or testing team says , they ran the workload for 10 users it returns in 60 secs but when they run same workload 100 users, the queries are dead slow.. taking more than 15mins – 30 mins.
What needs to checked from SQL Server/db side and what information we need to get from the end user to get more clarity? do we need any kind traces ?
November 9, 2022 at 9:48 am
You could use sp_humanevents (Erik Darling) https://www.erikdarlingdata.com/sp_humanevents/
To capture the waits while it is slow or log it to a table
Tools like sp_Blitz, sp_Blitzfirst (Brent Ozar) to have a quick check on the server https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit Newsletter: https://www.brentozar.com/blitz/
There is also a tool that captures the sqlstatements
https://github.com/spaghettidba/XESmartTarget
Saw a demo of it on dataminds and looked promising for lightweight monitoring
Is the testserver up to date with patches ?
November 9, 2022 at 3:38 pm
Ed B wrote:Do you have 15-20 3TB databases on a test server and one of the databases is production?
How much storage do you have? It sounds like you have 30-60 TB in databases. Is it possible you are running out of space? Are all the databases accessing the same volume(s)?
We use EC2 instances in AWS and it is cheaper to create one large volume, but I think it leads to slow disk access, especially if tempdb is on the same volume.
On Prod we have 1 copy of the database.
All servers and storage is Microsoft Azure and Azure storage.
If the Sql Server and Databases are in Azure each database can be individually sized based on performance requirements.
November 10, 2022 at 11:54 am
Few best practices to optimize SQL DB performance
- Reduce Table Size
- Simplify Joins
- Use SELECT Fields FROM Instead of SELECT * FROM
- Use EXISTS() Instead of COUNT()
- Use WHERE Instead of HAVING
- Add EXPLAIN to the Beginning of a Query
- Create SQL Server Indexes
- Avoid Running Queries in a Loop
Best of luck
November 14, 2022 at 5:15 pm
Hello,
You can also run the query here for a general sense of what SQL Server is waiting on:
SQL Server Wait Statistics (or please tell me where it hurts…)
https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
HTH.
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 17, 2022 at 7:09 am
Since you are on 2017 pressumably , navigate to your database (in explorer) and drill down to the Query Store. There you will find the most expensive queries by cpu time, IO, and I would pay attention to any regressed queries as well. These are candidates for optimising.
----------------------------------------------------
November 17, 2022 at 7:18 am
Few best practices to optimize SQL DB performance
- Reduce Table Size - Simplify Joins - Use SELECT Fields FROM Instead of SELECT * FROM - Use EXISTS() Instead of COUNT() - Use WHERE Instead of HAVING - Add EXPLAIN to the Beginning of a Query - Create SQL Server Indexes - Avoid Running Queries in a Loop
Best of luck
Having applies to a group not individual rows like a Where clause, so two different operators. Exists instead of Count ? Plus, How do you reduce the table size? Business requirements drive this and you have to have things optimized despite the table size.
----------------------------------------------------
November 17, 2022 at 7:28 am
Hi SQL Gurus,
One of our production database is running very slow. I checked blocking , I didn't see any blocking. Also, check for running processes and look for any waittype. I see null. How to get more insights on a running sql server for a specific databases?
What all can he checked in this scenario?
Also, How to check what are the most or top 3-5 databases which are currently active on the server? any query please share and on what columns do we need to aggregate to check to see the top 5 dbs active at this point of time and taking up resources.
This is a shared SQL instance and many dbs are hosted on it. each of 3TB in size. We have like 15-20 dbs hosted on that instance. Its a testing server. it has 16cpus and 256 gb RAM. SQL Server 2017 Enterprise Edition.
Kind Regards,
Bob
Production servers should always be on their own box. Development boxes can be had for a proverbial song because you can use the Developers Edition on those.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply