question

vivekchandra09 avatar image
vivekchandra09 asked

SQl Server Performance problem

Need to fix performance problem ran some baseline queries found few thins like too many connections, too many adhoc queries etc where to start
sql-serverperformanceperformance-tuning
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DenisT avatar image DenisT commented ·
I don't think we can help you here! You need to be more specific, otherwise Grant Fritchey will send you his hourly rate :)
1 Like 1 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Where do I send that?
0 Likes 0 ·
vivekchandra09 avatar image vivekchandra09 commented ·
1. so the issue is tempdb has 3 available files.There is plenty of RAM available. Need to investigate why tempdb is utilized instead of RAM. 2. tempdb : need to know what processes are using it and trouble shoot it from there. 3. my email is my username@aol dot com
0 Likes 0 ·
DenisT avatar image DenisT vivekchandra09 commented ·
You should start with monitoring your Tempdb usage to figure out that. There are few DMOs that you can use -- sys.dm_db_session_space_usage and sys.dm_db_task_space_usage -- you can join with sys.dm_exec_requests on the session_id to get the sql_handle which could be passed to sys.dm_exec_sql_text() to get the T-SQL statement and sys.dm_exec_query_plan() to get the plan. Also you can run Xevent session or Server Side trace to understand what's using Tempdb. Look for Tempdb spills, if you have enough buffer pool (What is plenty?), perhaps you get wrong memory grant estimates that cause the spills. Then start looking at the plans of the queries that spill to Tempbdb, if there are any. Outdated stats, missing indexes causing more IOs then nec., query re-writes to get rid of Hash Match (Joins/Aggregates), unnec. Sorts. If you get more details after monitoring, come back and we'll help you further! I'd start with these first to understand which queries are using Tempdb. Why 3 files in Tempdb?
0 Likes 0 ·

1 Answer

·
Tim avatar image
Tim answered
The best place to start is by checking out Simple Talk, giving them your email address to get to download this book [Troubleshooting SQL Server: A Guide for the Accidental DBA]( https://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/), or you can purchase it on Amazon.This book provides step by step advice along with the queries to find your pain points and how to troubleshoot them. It would also be very helpful for you to describe what your actual pain points are. "Performance Problems" is that with disk, CPU, network, memory, slow queries, query timeouts, etc? We would certainly need more information to provide a detailed response. So with a vague question, you get my vague response, however the book recommendation is awesome.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.