Solved

SQL Server 2008 R2 Best Practices - Configuration

Posted on 2014-12-30
24
159 Views
Last Modified: 2015-01-07
Hi, we have an ECM web-based application that has a SQL database backend.  I'm not a dba and my customer does not have a dba on staff.  We believe there are performance problems stemming from the db, I'm not sure if it's server resources or the SQL configuration.

Does anyone have a good reference for SQL configuration so I can ensure it's configured in the most appropriate manner?  

This is a single SQL server, no cluster.  Server 2008 R2 with SQL 2008 R2.  It's virtual (vmware) with 18 gb mem, 12 cpu cores.

Also, is using the Best Practice Analyzer a worthwhile exercise?

Thanks in advance.
0
Comment
Question by:bmsande
  • 8
  • 8
  • 7
  • +1
24 Comments
 
LVL 24

Expert Comment

by:Mohammed Khawaja
ID: 40524406
Using Best Practice Analyzer is a good start as it can point you to the direction which could identify as to what the issue is.  For starters, what is the SQL server configuration and have you looked at the performance in Task Manager?  It would be ideal to get a DBA to look at the server.  Just curious, is the SQL server getting backed up and if so, how is it getting backed up?  Is the ECM solution installed on the SQL server or is it on a dedicated server of its own?
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 40524414
Probably; for most things, it's very good.  For a few, you need the expertise to know when to override it ... which you don't have :-).

First, run command "EXEC sp_configure" and make sure the "max server memory (MB)" setting is set to, say, 14336 (14 GB), or at most 16GB.  If it's set to 0, you need to change it.

Run this command to verify that SQL "sees" the OS resources the same as you expect it to:
SELECT * FROM sys.dm_os_sys_info

Also, look at Windows memory manager for that VM and see if "free memory" is at least 100MB.

We need to rule out memory pressure first, because if it is memory pressure, you basically have to add more RAM, there's nothing else you can "fix" for that.

After that, you can run the "wait" stats query below.  I rather doubt nowadays it's cpu, particularly with 12(!) available, but you can check to be sure.  Often you'll see "CXPACKET" near the top, and you almost always want to increase the "cost threshold for parallelism" configuration setting from 5 or 20 to between 40 and 50.

Also, separately look at the top "top 10 average i/o" and "top 10 average cpu" reports available from SQL itself.  you can also look at the "total" i/o and cpu reports if you want a more thorough check.

--waits query
WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitSecs],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceSecs],
        [signal_wait_time_ms] / 1000.0 AS [SignalSecs],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',
        N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
        N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
        N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
        N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
        N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
        N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
        N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
        N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
        N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'DIRTY_PAGE_POLL',  N'SP_SERVER_DIAGNOSTICS_SLEEP')
    )
SELECT
    [W1].[wait_type] AS [WaitType],
    CAST ([W1].[WaitSecs] AS DECIMAL(14, 2)) AS [Wait_Secs],
    CAST ([W1].[ResourceSecs] AS DECIMAL(14, 2)) AS [Resource_Secs],
    CAST ([W1].[SignalSecs] AS DECIMAL(14, 2)) AS [Signal_Secs],
    [W1].[WaitCount] AS [WaitCount],
    CAST ([W1].[Percentage] AS DECIMAL(4, 2)) AS [Percentage],
    CAST (([W1].[WaitSecs] / CASE WHEN [W1].[WaitCount] = 0 THEN 1 ELSE [W1].[WaitCount] END) AS DECIMAL (14, 4)) AS [AvgWait_Secs],
    CAST (([W1].[ResourceSecs] / CASE WHEN [W1].[WaitCount] = 0 THEN 1 ELSE [W1].[WaitCount] END) AS DECIMAL (14, 4)) AS [AvgRes_Secs],
    CAST (([W1].[SignalSecs] / CASE WHEN [W1].[WaitCount] = 0 THEN 1 ELSE [W1].[WaitCount] END) AS DECIMAL (14, 4)) AS [AvgSig_Secs]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitSecs],
    [W1].[ResourceSecs], [W1].[SignalSecs], [W1].[WaitCount], [W1].[Percentage]
--HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95; -- percentage threshold
ORDER BY [AvgWait_Secs] DESC
0
 

Author Comment

by:bmsande
ID: 40525981
@Mohammed,
When looking at Task Manager, memory consumption is typically >85% at all times.  CPU is usually stable but during peak hours we see constant 50% utilization.  There's no chance a dba will review the server.  It's up to me with the guidance from you Experts.  The server is being backed up nightly with VMware snapshots.  There are no SQL Maintenance Plans for scheduled backups.  The ECM app is not installed on the SQL server.  This SQL server is dedicated to the ECM app.  Only ONE db is alive on the SQL server.

@Scott,
I'm not seeing the expected max memory result of "EXEC sp_configure":
sp_configure
The server actually has 16 gb mem and 6 cpu cores.  (I was mistaken when I opened the question).

Results from SELECT * FROM sys.dm_os_sys_info:
sys_info
Here's our paging file config:
paging config
Stats from the business day, but it's New Years Eve so the office is light:
task manager
processes

I'm going to run the BPA tool now and will return with my results.  Then I'll review the 'wait' stats query that was mentioned.  I wanted to provide this in the meantime.  Thanks everyone.
0
 

Author Comment

by:bmsande
ID: 40526068
Here are the results of our BPA scan.  Is there anything performance related that should be addressed?  I only scanned the SQL_Server_Engine and SQL_Server_Setup because reporting/analysis/integration are not important to our application.

bpa results
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 40526363
I believe your company needs a DBA.  

If your company can't' afford one, I would suggest that you spare a few box to have an performance audit done then take action suggested by that audit.  Performance Tuning is one of the most difficult task in a DBA's job. Yu can start doing an audit, adjust your parameters, collecting tons of information then inform your management you don't currently have the skill set to insure the problem will be solved.  You are doing your best that is the best you can do for the moment.

Good luck..We know the pain as DBA's. Happy New Year.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 40526367
<<It's up to me with the guidance from you Experts>>
I wish I could help you more but the best I can propose to say anything of value on such broad scope and based on the information you provided is to suggest taking a look and tell you what I see and make a few recommendations you can take.  If you can open up a VPN canal, I can take a look.  Note: I do not expect any payment for this.

You can also try to narrow down your search to establish time slots on when the problems occur specifically.  the process will get you closer to a specific problem.  This is the issue for the moment, you don't know exactly what you are looking for and you don't know how to look for it and how to solve it.  Let me know if the VPN is possible.
0
 

Author Comment

by:bmsande
ID: 40526689
@Racimo, happy new year to you as well!   I appreciate your feedback and offer to take a look but that's not possible.  Yes we need a dba, we know that.  Trust me..... but it's not gonna happen.

I can get around pretty good with IT infrastructure components.  I don't expect to tune this like a dba.  I want to make sure there are no obvious configuration issues.  And I'm looking for tips/tricks on what should be checked.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 40526715
<<I want to make sure there are no obvious configuration issues.  And I'm looking for tips/tricks on what should be checked.>>
Fair enough.

Unfortunately, performance tuning is often like looking for needle in the middle of a stack.  There are no real tips and tricks in performance tuning, more of a craftsmanship-like strict methodology to effectively narrow down potential causes elimination when problems occur: without this methodology, one sometime has to literally eliminate hundreds of potential causes to solve a problem or be lucky.  Such methodologies take years to develop, often in utter pain and sleepless nights.   If this is the first time you are facing SQL performance tuning problem, you are at the beginning stage of having to do broad range search.   I do not mean I say this to discourage you but to help you weight more realistically the task ahead.  Since I was in your shoes once, I proposed my help for free through VPN since I am probably on the other side of the world for that reason.

I wish you good luck into being effective with broad range search and I sincerely hope you will convince your managers to grant you some manpower.  You should not be put in such situation because thrust me, if you don't find a solution, they will be the first to blame you.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 40526719
<<I want to make sure there are no obvious configuration issues.>>
Please start with this:
> CPU Sizing : make sure you are always below 50% except for spikes
> IO Sizing: please make sure you have no Read Write drive contention.  --> Performance Monitor
> Make sure you don't have to many table scans.--> Profiler
> Make sure you do not have connection bottlenecks
> Make sure your clients boxes have sufficient memory and CPU to process data received from the server.  I see so many performance problems occurring because the client could not handle the data sent back by SQL.

With some luck that can help a bit.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40527324
>> I'm not seeing the expected max memory result of "EXEC sp_configure":<<

Sorry, you'll need to "tell" SQL to show you the "advanced" options:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE WITH OVERRIDE;
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40527327
I am a DBA (and have been for more than 25 years).  There's no memory pressure indicated on what you've posted.  The next steps of what to check are what I posted above.
"
We need to rule out memory pressure first, because if it is memory pressure, you basically have to add more RAM, there's nothing else you can "fix" for that.

After that, you can run the "wait" stats query below.  I rather doubt nowadays it's cpu, particularly with 12(!) available, but you can check to be sure.  Often you'll see "CXPACKET" near the top, and you almost always want to increase the "cost threshold for parallelism" configuration setting from 5 or 20 to between 40 and 50.

Also, separately look at the top "top 10 average i/o" and "top 10 average cpu" reports available from SQL itself.  you can also look at the "total" i/o and cpu reports if you want a more thorough check.
"
0
 

Author Comment

by:bmsande
ID: 40528086
Here is the result of our max memory config.  If I'm reading this correctly, this should be a different value considering the server has 16 gb mem...... Thoughts?

max memory
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:bmsande
ID: 40528103
Here are the CXPACKET results from the wait stats query
cxpacket
What, if anything, needs modified?  I read the default value is typically '5'.  But I don't understand what column this value should have been listed within.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 40528134
<<Thoughts?>>
Nothing.
Memory pressure is not detected by randomly simply looking at max memory setting.  Your config simply says that 6GB are left for the OS to run which seems all right.  Simply move on to the next points listed.
0
 

Author Comment

by:bmsande
ID: 40528237
Where can I find the top "top 10 average i/o" and "top 10 average cpu" reports in SQL?
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 40529156
Download the latest SSMS client.  They should be in the menu bar.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40531638
>> @Racimo
Nothing.
 Memory pressure is not detected by randomly simply looking at max memory setting.  Your config simply says that 6GB are left for the OS to run which seems all right.
<<

This isn't "random".  Afaik there is no other way to determine how much RAM SQL Server is allowed to use.  You've allocated SQL just under 10GB for buffer space.  With only 16GB on the server, that's probably about right.

Next let's see if SQL itself is indicating any memory pressure recently:

;WITH    RingBuffer
          AS (SELECT    CAST(dorb.record AS XML) AS xRecord,
                        dorb.TIMESTAMP
              FROM      sys.dm_os_ring_buffers AS dorb
              WHERE     dorb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
             ),
        RingBuffer2 AS (
    SELECT  xr.value('(ResourceMonitor/Notification)[1]', 'varchar(75)') AS RmNotification,
            xr.value('(ResourceMonitor/IndicatorsProcess)[1]', 'tinyint') AS IndicatorsProcess,
            xr.value('(ResourceMonitor/IndicatorsSystem)[1]', 'tinyint') AS IndicatorsSystem,
            xr.value('(//Record/ResourceMonitor/Indicators)[1]', 'bigint') AS [Indicators_SQL2005],
            xr.value('(//Record/@time)[1]', 'bigint') AS [RecordTime],
            xr.value('(MemoryNode/TargetMemory)[1]', 'bigint') AS TargetMemory,
            xr.value('(MemoryNode/ReserveMemory)[1]', 'bigint') AS ReserveMemory,
            xr.value('(MemoryNode/CommittedMemory)[1]', 'bigint') AS CommitedMemory,
            xr.value('(MemoryNode/SharedMemory)[1]', 'bigint') AS SharedMemory,
            xr.value('(MemoryNode/PagesMemory)[1]', 'bigint') AS PagesMemory,
            xr.value('(MemoryRecord/MemoryUtilization)[1]', 'bigint') AS MemoryUtilization,
            xr.value('(MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS TotalPhysicalMemory,
            xr.value('(MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS AvailablePhysicalMemory,
            xr.value('(MemoryRecord/TotalPageFile)[1]', 'bigint') AS TotalPageFile,
            xr.value('(MemoryRecord/AvailablePageFile)[1]', 'bigint') AS AvailablePageFile,
            xr.value('(MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS TotalVirtualAddressSpace,
            xr.value('(MemoryRecord/AvailableVirtualAddressSpace)[1]',
                     'bigint') AS AvailableVirtualAddressSpace,
            xr.value('(MemoryRecord/AvailableExtendedVirtualAddressSpace)[1]',
                     'bigint') AS AvailableExtendedVirtualAddressSpace,
            dosi.ms_ticks
    FROM    RingBuffer AS rb
            CROSS JOIN sys.dm_os_sys_info AS dosi
            CROSS APPLY rb.xRecord.nodes('Record') record (xr)
        )
    SELECT
        DATEADD (ms, -1 * (rb2.ms_ticks - rb2.RecordTime), GETDATE()) AS NotificationTime,
        rb2.*
    FROM RingBuffer2 rb2
    --WHERE
        --RmNotification LIKE '%MEM%_LOW%' AND (IndicatorsProcess > 0 OR IndicatorsSystem > 0)
    --WHERE --Indicator Values: 1=high(ok) phys mem; 2=low(potential issue of not enough ram) phys mem;4=low virt mem.
        --( IndicatorsSystem >= 2 OR Indicators_SQL2005 >=2 )
    ORDER BY [NotificationTime]

The first time, don't check just for more pressure -- indicator >= 2 -- list it all to make sure you get results from the query.  Then uncomment the WHERE condition to check specifically for low memory condition(s).
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40531656
>> Where can I find the top "top 10 average i/o" and "top 10 average cpu" reports in SQL? <<

In SSMS, right-click on the instance ("server") name.  You should see an entry "Reports" with an arrow after it.  Cursor over that, then pick "Standard Reports", and you should see reports starting with "Performance - Top Queries" that (roughly) match the names/categories above.

Often the top 1 or 2 queries will be vastly more I/O and/or cpu than any other query.  Then we tune those queries, if at all possible, since they are the bad dogs :-).
0
 

Author Comment

by:bmsande
ID: 40531843
@Scott, thanks so much for your assistance.  So far you've taught me several helpful tips.  I ran the query for recent memory pressure and the following results were returned:
sql ss1
Not sure what I'm looking at.  Next I uncommented the WHERE condition to check specifically for low memory condition(s) but I must be doing something wrong.  I uncommented WHERE one-by-one, see results here:
ss2
ss3
ss4
ss5
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 40531854
<< Afaik there is no other way to determine how much RAM SQL Server is allowed to use.  >>
I don't believe we are in real disagreement here. We are simply not looking on the same angle which is fine.

My point is that the amount of RAM allowed for SQL Server does not say much about memory pressure and further in-depth analysis is required to conclude memory pressure occurs.  Ex: too many adhoc queries occurring will bloat your procedure cache even if the amount of allowed memory seems enough.  To detect this kind of memory pressure one has to evaluate plan reuse, recompilation, total space used etc....

I agree allowed memory can and should be verified quickly, but if there is nothing wrong , it is better to move on to other potential issues on broad range search.  I have seen much more IO issues and CPU issues on SQL than memory issues on broad range searches.  

Happy New Year...
0
 

Author Comment

by:bmsande
ID: 40531937
The performance reports have been extremely helpful.  I'm opening a ticket with the vendor of the ECM application to analyze some of these queries.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40532193
SQL is seeing internal memory pressure, but not system wide (process indicator is 2 but system indicator is 0).  This can be difficult to pinpoint, but most often it is caused by SQL Server not having enough RAM overall, i.e., the "10000" for "max server memory" is very likely not enough.

You've got nearly 2GB of ram free.  First, I'd try upping SQL's allocation to 11264.0 (11GB, or 11776 11.5GB if you're feeling lucky) from 10000 (9.77GB).

If you want to post the top one or two I/O hogs, I can take a look at those two for you.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40532202
>>My point is that the amount of RAM allowed for SQL Server does not say much about memory pressure and further in-depth analysis is required to conclude memory pressure occurs. <<

How on earth can you do any analysis at all -- let alone "in-depth" -- if you don't know how much RAM SQL is allowed to use?  If the server has 128GB, but you're giving SQL only 3GB (some oversight obviously), the problem is not RAM itself but the amount of RAM allowed for SQL Server.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 40532239
<<How on earth can you do any analysis at all -- let alone "in-depth" -- if you don't know how much RAM SQL is allowed to use?  I>>
Which is probably why I also wrote.   allowed memory can and should be verified quickly :)

Please do not take my comment as a contradiction but as as constructive complementary comment.

Is not EE all about that ?  :)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now