- 3 Key Numbers
- How busy is your server
- How hard is it working
- Ho wmuch data do you have
- Perfmon
SQLServer:SQL Statistics - Batch Requests/sec. Trend this on an hourly basis and break it out by Weekday/Weekend- Do NOT look at transactions per/sec, since not all statements are transactions
- 0 - 1,000 easy to handle with commodity hardware. MAY be possible to run this load on a VM
- 1,0000 - 5,000 be careful, because a table lock or bad query can knock the server over
- 5,0000 - 25,0000 You should have several full time DBAs performance tuning. Every index matters crticially
- 25,000 + requires lots of attention, but you need to have always on availability groups.
- Record is 150,000
- Hours of wait time per hour
- brentozar.com/go/getwaits - trend this on an hourly baiss
- The more the server works the more it waits
- 0 - Server isn't doing anything
- 1 hour of waits - still not doing much
- 1 hour of waits X # of cores - working hard, look at tuning
- All numbers below assumce 'commodity' hardware which is 2CPU and 250GB + of RAM
- 1 - 150GB Std Edition
- 150 - 500 GB Enterprise
- 500GB + OLTP vs. Analytical?
- 1TB OLTP data - very challenging
- http://cpuid.com can be used to look at exactly what the CPU is doing
- Get a faster CPU. Pay attention to the number of cores
- 7k per core with Enterpise. 2k per core with standard
- PAGEIOLATCH - make storage faster. Add more memory
- Perfmon
Phsical Disk: Avg Sec/Read. 20 - 100 ms is good. > 100ms means you have a storage issue - Don't ever change storage in an existing server. Buy new hardware and test/tune there
- WRITELOG
- Commit is only completed when the transaction log is written to.
- Perfmon
Physical Disk: Avg Sec/Write. 3-20ms is good - Perfmon
Physical Disk: Reads/Sec, Writes/Sec
- You need more RAM than data. If you have 128GB of data then you should have roughly 192GB of RAM
- If you can fit data in memory, then most of your tuning problems go away
- By default, SQL Server will allow one query to allocate up to 25% of available memory. 4 users with aweful reports could swamp a server
- Can use filtered indexes to get best performance for recent history
- Indexes views are a view with a where clause - has a clustered index
DBCC TRACEON(610)minimal logging - useful for DW transfers to significantly drop logging- Almost impossible to run if you are running FULL recovery. Need to be running in SIMPLE recovery mode
- Use SIMPLE recovery model for datawarehouse databases. Pair this with
DBCC TRACEON(610)to get super minimal logging and better speed for writes WITH INDEX(1)will force the clustered index to be used
- The 'Version Store' uses tempdb
- Temp tables and temp variables live here as well
- SQL server can round robin and distribute amongst the available temp db files
- For best results, have equally sized files
- For lots of small allocations, like you have with tempdb, having multiple files helps because you get multiple PFS (page free space) and SGAM (shared global allocation map)
- PAGELATCH_UP - tempdb waits. means the page is in memory
- PAGE_IO* - means that page is pulling from disk
- You can see this with the
sp_WhoIsActivescript - Look at wait stats to see if adding tempdb files can help distribute workload
- SQL 2014 has greatly reduced IO in tempdb
- Latency thresholds for concern
- Read latency - 30ms for data files, 5ms for log files
- Write latency - 30ms for data files, 2ms for log files
- Microsoft's guidelines - start with the # of tempdb files as you have physical cores, up to 8. From there you need to measure waits and test
- They like to pre-grow out tempdb and fill up the drive
- Simple approach - start with 4 equally sized tempdb data files and then watch
- Don't need to necessarily put tempdb on a separate physical drive unless you're seeing phpyiscal IO waits. Kendra recommended creating a separate logical volume to contain tempdb so that you can control it's growth. Also allows you to move it around easily
- PREEMPTIVE_OS_* means that it's a wait on something outside of SQL Server. Could also be related to encryption.
- Content Addressable Storage as a replacement for storing images/blobs in a SQL Server. http://www.emc.com/data-protection/centera.htm
- ElasticSearch - full text search solution in use by Stack Overflow
- Redis for an open source caching layer
- Not for us
- ostress.exe - simple tool for scripting queries to hit a dev SQL Server. Similar to hammerdb
- Set autoshrink to off in sys.databases
- Stats updates happen synchronously when the next read comes through after writes cross the registered threshold
- THREADPOOL waits are an example of a poison wait. Shouldn't ever really have these.
- SQL Server memory ools:
- Execution Plan Cache
- Buffer Pool Cache (aka Data Cache)
- Query Workspace Memory (aka Memory Grant)
- RESOURCE_SEMAPHORE - Queries are asking for workspace memory and SQL server is unable to grant
- Scanning large tables can lead to big memory grants
- For 2012/2014 you need to set memory higher than max memory of 128 on Standard.
- Make sure that SQL Server is under external memory pressure
- sp_Blitz checks for the poison wait types
- The memory limits for SQL Server apply to the Data Cache only. So, add 15% of so to the max edition limit and set the max sql server memory to this level.
- Leave 10% of 4GB (whichever is higher) for the OS