UBGP008: Maximizing SQL Server Performance with Microsoft Dynamics GP. John Lowther

(1)

UBGP008: Maximizing SQL Server

Performance with Microsoft

Dynamics GP

(2)

#GPUG

John Lowther

Chief Data Architect, Sta-Home Health Agency

Owner of Lowther Software

 Microsoft Dynamics GP Most Valuable Professional (MVP) & GPUG All Star  Chairman on the Board of Advisors for the Great Plains User Group (GPUG)

– Member of the GPUG Planning Committee – Member of the GPUG Programming Committee

– GPUG Special Interest Group (SIG) Leader (BI & Reporting, DBA, and Sys Admin) – Published Author in the GPUG Magazines

 GPUG Academy Board Member

– Member of the GPUG Academy Certification Committee

 Other Info

– 10+ years with Dynamics-GP - 15+ years as a Database Administrator - 20+ years as a Developer – Blog: Microsoft Dynamics GP DBA

– Blog: The Dynamics GP Administrator – Post: Microsoft Dynamics GP Product Forum – Post: GPUG Collaborate

(3)

#GPUG

Session Format & Audience

 Format:

– User Group Breakout

 Audience:

(4)

#GPUG

Agenda

 Increase your confidence and knowledge as we explore

how to maximize the performance of a Microsoft SQL Server running Microsoft Dynamics GP databases.

 We'll also review the settings and maintenance items you

should be using, along with other important things you can do to increase performance, including off-load

(5)

#GPUG

Topic List

 Updates  BIOS  Hardware  RAM

(6)

#GPUG

Topic List

 SQL Database Settings!  Off Loading Reports!  Data Warehousing

 How do we locate the real problem!

(7)

#GPUG

Updates

 Always check to make sure you have the latest software

updates.

 Not just software!

– BIOS, Chipset, Drivers – Everything!

(8)

#GPUG

BIOS: Hyper-threading?

 You better believe it! Well most of the time.

 Hyper-threading allows improved parallelization meaning

the processor can do multiple task at once.

 However there are some rare cases where it can cause

(9)

#GPUG

BIOS: Power Save Options

 Turn off all power save options like putting network cards

to sleep, powering down hard drives, or lower CPU performance.

 Note: This also applies to Power settings in Windows as

(10)

#GPUG

Hardware: To x64 or not to x64?

(11)

#GPUG

Hardware: Disk Configuration

DISK are cheap! But what about your employees time?

– OS – Raid 10

– System Databases (Master, Model, & MSDB) – RAID 10 – Master Data Files (MDF files) – Raid 10

– Log Data Files (LDF Files) – Raid 10 – TEMPDB (MDF and LDF) – Raid 10 – Backup location – RAID 10

(12)

#GPUG

RAM

 SQL Server loves RAM

– If you decrease RAM by 25% you decrease performance by 50%. – In other words double your RAM you quadruple your

performance!

(13)

#GPUG

(14)

(15)

(16)

(17)

(18)

(19)

(20)

(21)

(22)

(23)

#GPUG Notice: No Domain Users Use AD Group for GP Users

(24)

#GPUG

(25)

(26)

(27)

(28)

(29)

(30)

(31)

(32)

(33)

(34)

(35)

#GPUG

Off Loading Reports

 My definition: (totally In My Opinion) (IMO)

– Off loading Reports is the concept of running specific types of Reports on a different SQL Server than your Application

Production SQL Server.

– Log Shipping is a great way to “Off Load” Reports.

– This only works for the Excel Refreshable Reports and the SQL Server Reporting Services Reports (SSRS).

– This does not work for the internal Microsoft Dynamics GP Reports!

(36)

#GPUG

Existing Setup

Existing SQL Server for Microsoft Dynamics GP One or more Workstations SQL Backups Excel and SSRS Reports

(37)

#GPUG

Restore a Full Backup to the new Server

(38)

#GPUG

Temporarily connect one workstation

(39)

#GPUG

Redeploy SSRS and Excel Reports

Existing SQL Server for Microsoft Dynamics GP One or more Workstations SQL Backups Excel and SSRS Reports Note:

(40)

#GPUG

Remove the temporary connection

Existing SQL Server for Microsoft Dynamics GP One or more Workstations SQL Backups Excel and SSRS Reports Note:

(41)

#GPUG

This is what you have now

Existing SQL Server for Microsoft Dynamics GP One or more Workstations SQL Backups Excel and SSRS Reports Note: Other Workstations

(42)

#GPUG

Final Touches

Existing SQL Server for Microsoft Dynamics GP One or more Workstations SQL Backups Excel and SSRS Reports Note: Other Workstations

(43)

#GPUG

(44)

#GPUG

How do we find out about a Problem?

 A user calls with that dreaded message, GP is running slow.

Or, GP is not working at all.

 My first thought, is it really a SQL problem?

 There are times with SQL has nothing to do with the

problem. Bad network cables, etc.

(45)

#GPUG

How do we locate the real Problem

 Stop and Think! (Base line performance before hand!)  OS – Task Manager – Resource Monitor – Performance Monitor – Event Viewer  SQL

– Job Activity Monitor – Is there a SQL Job Running ??? – Activity Monitor

– Database Engine Tuning Advisor (treat with care) – SQL Server Profiler (treat with extreme care)

(46)

#GPUG

How do we locate the real Problem

 Dynamics GP Add-Ons

– Support Debugger Tool

 Other Tools outside the Dynamics GP World

– Idera Software – SQL Check

(47)

#GPUG

Where do we find Information

 NETWORKING WITH EACH OTHER!

 The GPUG Collaborate site, and GPUG

 Microsoft Dynamics GP Community

– Forum

– Blogs – Videos

(48)

#GPUG

Where do we find Information

 Microsoft Dynamics GP MVP’s Blogs

 Customer Source

– White Papers, Knowledgebase,

– Even a white paper on Performance

(49)

#GPUG

Maintenance Plan

 The copy only problem

 The Update Statistics problem  Let’s Demo

(50)

#GPUG

Take Away #1

 Bios and OS Settings

(51)

#GPUG

Take Away #2

Watch those Database Files

 Grow the MDF file yourself at off peak times, try to avoid

autogrows during peak times.

 LDF files should not actually autogrow.

– Transaction log backup should clear it before the physical file needs to grow.

(52)

#GPUG

Take Away #3

(53)

#GPUG

Take Away #4

(54)

#GPUG

Questions?

John Lowther

Sta-Home Health & Hospice Lowther Software

Email: [email protected] Blog: Microsoft Dynamics GP DBA