SQL server peformance issues in production

Hi All

I have Sql sever 2012. In production I am seeing slowness in database.
   How do I find slow running queries.
    How do we check the CPU usage.
    How do I see any deadlock.

Do we have queries to check this?

Any other metrics to check?

Also we have few SSIS packages. They load lot of new data from the files. The DB size is 1.5 TB.

Thank you!!
Who is Participating?

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

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.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
If you see slowness at the Instance or database level, please ensure you are performing index maintenance tasks regularly, if not, then you can start with Ola Hallengren script.

If performance haven't improved even after Index Maintenance mentioned above, then try to run a Profiler trace, capture all the queries that were taking more time or parallelism or IO and tune it with the help of DBA for better performance.
Since you have around 1.5 TB of database data, proper indexing is required for better performance..

> > How do I see any deadlock.

Deadlocks can be identified by enabling Deadlock trace flag, once enabled deadlock information will be captured in SQL Server error logs.
SET TRACEON (1222,-1)

>> Also we have few SSIS packages. They load lot of new data from the files.

Please make sure that SSIS packages are written in optimal manner and it performs proper index rebuilds after loading the tables. Loading lots of data can cause fragmentation and hence rebuild or reorganize might be required after loading new data.

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:
I wouldn't mind much with CPU usage when dealing with databases. Resources as Memory, Disk and Network are more important. CPU should be the last resource to watch to.
But first of all check your queries performance. If the database design is poor (not normalized, lack of indexes, ...) then is highly possible that your queries will be unperformed. You can start by using SQL Server Profiler to capture the long running queries and analyze them, one by one.
No need to reinvent the wheel.

Use Task Manager and Resource Monitor to check the overall computer functionality and bottle necks.

Scripts for SQL Server also exist - look e.g. here: https://www.brentozar.com/  and download and test all sp_Blitz....  scripts. They are perfect for people like you (and me).

Queries to check SQL Server blocking are e.g. here: https://www.mssqltips.com/sqlservertip/2927/identify-the-cause-of-sql-server-blocking/

Of course, you'll need to learn additional knowledge to understand Query Plans, SQL Profiler output etc. etc.
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

From novice to tech pro — start learning today.