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
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
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?
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.
18 People are following this question.
