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.
Abdulrhman AlamoudiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.