• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 53
  • Last Modified:

SQL Server 2014

I have an issue with My SQL Server, its getting very slow (such Freezing ) and cannot did anything when TempDB size is reach 8 to 10 GB.
once the process is killed or finished server start in normal status.
0
Abdulrhman Alamoudi
Asked:
Abdulrhman Alamoudi
1 Solution
 
Nitin SontakkeDeveloperCommented:
8 - 10 GB of tempdb? Is it possible to restart the SQL Server? If yes, please try doing it - hope it is not a production server. Note tempdb size afterwards.

The underlying issue is obviously somewhere else. Tempdb is used by SQL Server for very many purposes, such as sorting of result set (order by clause in your query), index creation, etc.

How many databases do you have?

Unfortunately scenarios such as these need to be investigated from so many directions...please give more details if you can.
0
 
Nitin SontakkeDeveloperCommented:
Which process are you killing? Need to look into what that process doing so as to address the real issue.
0
 
Abdulrhman AlamoudiAuthor Commented:
Dear Mr. Nitin

This is a production server and what should i do next step.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Nitin SontakkeDeveloperCommented:
You can something as follows, by changing the spid"

SELECT t.*
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE session_id = 59 -- modify this value with your actual spid

I have taken the above query from : https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sql-text-transact-sql

Even in SQL Server Management Studio, you can find top slowest running / top io queries, etc. Just right click on Server node in SSMS and you will see following options:

Built in performance reports in SSMS
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Well, it looks like your tempdb isn't properly sized. Tempdb is heavily used by the SQL Server engine so provide the correct size is always a good policy to avoid performance issues.
Hope that you have your tempdb in a dedicated disk and that you have at least a tempdb data file per processor until a maximum of 8 data files. And they should also be all of the same size.
0
 
65tdRetiredCommented:
Have a look at this tool should help with sizing and more.
https://www.brentozar.com/first-aid/
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Abdulrhman, a feedback will be appreciated.
Cheers.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now