1 / 29

TOP 10 Thinks you shouldn’t do with/in your database

30 March 2010. TOP 10 Thinks you shouldn’t do with/in your database. Margarita Naumova Microsoft. Top 10 … on focus today. Memory Configuration Worst Practices TempDB configuration worst practices TempDB Usage worst practices. MaxServerMemory Configuration Mistakes.

ipo
Download Presentation

TOP 10 Thinks you shouldn’t do with/in your database

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 30 March 2010 TOP 10 Thinks you shouldn’t do with/in your database Margarita Naumova Microsoft

  2. Top 10 … on focus today • Memory Configuration Worst Practices • TempDB configuration worst practices • TempDB Usage worst practices

  3. MaxServerMemory Configuration Mistakes • Set MaxServerMemory to physical Maximum • Do not set MaxServerMemory At All Results: All threads/processes outside SQL Server BufferPool are slow or cannot start at all: Not enough buffers for SSIS Packages, releasing buffer waits Linked Server and distributed query issues performance or execution errors Connection/SQL Server Login Time-Outs (Errors 70x) Error "WARNING: Failed to reserve contiguous memory of Size= 65536."

  4. 0xFFFFFFFF Operating System Operating System SQL Server or OS (/3GB switch) 0xC0000000 0x80000000 Buffer Pool (8KB buffers) SQL Server Thread stacks MemToLeave area 0x00000000 The way it works

  5. 0xFFFFFFFF Operating System SQL Server or OS (/3GB switch) 0xC0000000 0x80000000 Other Locks Query Workspace. Plan Cache DB Page Cache SQL Server Thread stacks MemToLeave area 0x00000000 The way it works MaxServerMemory

  6. Recommendations • Always define MaxServerMemory! • Take into account MemToLeave area • MemToLeave= MemReserved + (NumThreads* StackSize) • MemReserved = 256 MB • NumThreads = the total number of worker threads configured (table below) • StackSize = (0.5MB x86, 2MB x64, 4MB IA64) Example 8Cores, 64Bit: MemToLeave=256+576*2=1,408MB

  7. Recommendations • Calculate worst case scenario • Scenario Examle: 64bit, 8 cores, 32GB RAM • 2GB for the OS • 2 GB for MemToLeave (LinkedSrvs, MultiPageAllocatorsAND worker threads) • 1GB for backup program • = 5GB • Set Max Server Memory to 27GB • Special Attention to MultiInstance Cluster Configuration!

  8. -3GB/AWE configuration mistakes • Set /3G when there are other Applications on the server • /AWE and large buffer flushes Results: The SQL Server needs more memory because it takes all and still works slowly. I set /3G and I set AWE switch but: The OS is hanging, the overall Server is hanging I still do not get performance benefit

  9. 0xFFFFFFFF Operating System SQL Server or OS (/3GB switch) 0xC0000000 0x80000000 Other Locks Query Workspace. Plan Cache DB Page Cache SQL Server Thread stacks MemToLeave area 0x00000000 The way it works - /3GB

  10. 0xFFFFFFFF Operating System SQL Server or OS (/3GB switch) 0xC0000000 0x80000000 Other Locks Query Workspace. Plan Cache DB Page Cache SQL Server Thread stacks MemToLeave area 0x00000000 The way it works - AWE AWE Memory

  11. Recommendations • Use /3GB only in 32bit platforms and only when you have not database page buffer related memory issues • Use AWE on 32-bit to increase DB Pages BufferPoolSize if you have intensive BPoolPage Usage • Watch out Memory\Free System Page Table Entries counter (should be >10 000) • /USERVA switch in Windows Server 2003 • Gives less than 3GB to user mode • Extra space kept in reserve for System PTEs • Recommended values between 2900 and 3030 • Do not use /PAE with /3GB (it increases PTE from 4K to 8K)

  12. The LockPagesInMemory Mistakes • LockPagesInMemory Not set • The performance of SQL Server decreases suddenly. • A computer that is running SQL Server stops responding for a short time. • A time-out occurs for applications that connect to SQL Server. • Problems occur when you run even simple commands or use applications on the system • New message from 2005 SP2: • “A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 646 seconds. Working set (KB): 901904, committed (KB): 2215752, memory utilization: 40%.”

  13. The way it works - LockPagesInMemory • Prevent OS paging of Buffer Pool by Locking Pages in Memory • Automatic if SQL Server Service Account has Lock Pages in Memory privilege • Enterprise and Standard Edition Feature • Set using gpedit.msc: http://msdn2.microsoft.com/en-us/library/aa198229(SQL.80).aspx • Note: Other parts of SQL may still be paged, along with other applications

  14. Recommendations • On 64bit configuration always set LockPagesInMemory • Use with MaxServerMemory • SQL Server 2008 SP1 CU2 and SQL Server 2005 SP3 CU 4 for Standard Editions • For the LocalSystem account it is set automatically

  15. DEMO: Get the Memory Statistics

  16. The TempDB configuration mistakes Issues: Slow Execution of procedures Slow execution on large queries Disruption/Prevent procedure from completion 1101- insufficient space for session 1105 – insufficient space for session 3959 – version store is full 3967 – version store forced to shrink 3958 – transaction cannot find required version record … Contention  Wrong Number of DB Files Usage  Wrong DB File Sizing Placement  Wrong Drive • Too much or too less (1) TempDB Database File • Confuse TempDB Performance and contention • Extreme TempDB usage for large temp objects, and object generating volumes • TempDB Size left default • Temp Tables v/s temp variables v/s … huge user temp objects

  17. latching PFS SGAM tempdb Contention defined • On TempTables Creation SGAM 4 GB

  18. DEMO: TempDB Contention

  19. User Objects • User-defined tables and indexes • System tables and indexes • Global temporary tables and indexes • Local temporary tables and indexes • Table variables • Tables returned in table-valued functions • Scoped to session or stored procedure

  20. Temp Tables v/s temp Variables • Common myth that table variables are memory-based only • Both end up hitting tempdb • Temp Tables • Can be indexed • Can have statistics • Colmodctr can cause recompiles • Table Variables • No indexes or statistics • No recompile threshold

  21. Internal Objects • Not logged • Work tables for cursor operations • Work tables for spool operations • Temporary large object storage • Work files for hash join or hash aggregate operations • Intermediate sort results for creating/rebuilding indexes (if SORT_IN_TEMPDB) or some GROUP BY, ORDER BY or UNION queries

  22. Sort Warnings • Reason: large sort operation within a query (ORDER BY) • Sort Operation cannot be done into memory and has to be divided into more steps • Sort takes multiple passes to TempDB to get the sorted output

  23. Hash Warnings • Hash Occursion/hash bailout has occurred during hash operation • Hash Join or Hash aggregate has run out of memory and been force to split to disk during execution • Slower query perf and space consumtion increase in TempDB

  24. DEMO: TempTables/TempVariables

  25. Recommendations • Minimize use of temporary tables • Avoid Static Cursors usage • Watch out usage of LOB, • Index to avoid worktables/workfiles • Set the size appropriately for production • Enable autogrow • Use Instant File Initialization • Set Number of files to ½ to ¼ CPUs but <=8 • Set equal file sizes • Place separately • Separate drive for Files and Log • Fast IOPS drive

  26. Recommendations (cont) • Minimize large sort operations trough: • Reducing the sort inputs • Reducing the fields in ORDER BY clause • Creating appropriate index • Minimize Hash Joins • Make sure that statistics exist on the columns that are involved in the hashing operation • Try updating them • Try using different type of JOIN (nested loop, merge)

  27. Top 10 thinks you should do with/in your database (server)Session Summary • Don’t miss MaxServerMemory • Don’t use /3GB and /AWE for 32bit platforms just because you guess that SQL server needs more Memory • Don’t miss the LockPagesInMemory on 64bit platform • Don’t confuse TempDB performance with contention • Don’t use too much/few TempDB Data Files • Don’t leave TempDB File Size default. Size properly • Don’t just use TempTables or TempVariables. Choose properly • Don’t use Static Cursors • Don’t do large ORDER BY • Avoid Large Inputs, groupings causing hash operations

  28. Thank you! Contact me further: 31 March 2010, 16:15 – 17:15, SQL Server Unplugged Or any time at: e-mail: Margarita.Naumova@microsoft.com Magi_Naumova@hotmail.com Please Complete The Evaluations! Your Feedback and comments COUNT!

  29. Community Booth • Become part of our technological communities • Network with colleagues and experts • Ask the Experts sessions Exhibition AreaLevel -1 Photo (cc-by-sa) Chris Radcliff @ Flickr

More Related