SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 High Availability (2008)
 SQL Server migration issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yak Posting Veteran

98 Posts

Posted - 12/23/2013 :  08:35:53  Show Profile  Reply with Quote

We migrated one of our sql server to a new server with more RAM space, included more core processors. But we are facing high performance issues in our server.

The jobs which used to take few seconds are now taking more than the normal time.

What could be the possible check points to identify where the issue is from?

Thanks in advance.

- Varalakshmi

Almighty SQL Goddess

38182 Posts

Posted - 12/23/2013 :  14:21:29  Show Profile  Visit tkizer's Homepage  Reply with Quote
Run PerfMon to see if you have a hardware bottleneck. If the jobs are queries, then run those queries in SSMS and see what performance you get plus check the execution plans and statistics io/time. Probably you'll need to update stats on the tables, but also verify you have auto update stats turned on.

You should likely LOWER your server-side MAXDOP setting (sp_configure). My baseline value for OLTP systems is 2, and then I adjust as necessary. You can also adjust it in queries so that it doesn't affect other sessions.

Is LPIM set for the SQL Server service account? Any messages in the error log such as slow I/O?

Tara Kizer
SQL Server MVP since 2007
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000