Tag: sql server

A session I have wanted to see. Marciej Pilecki has done this before with high ratings and I have missed until today

He starts with a description of how memory is allocated in the 32 and 64 bit systems. One interesting thin he noted is that the lock Pages in Memory setting on 64 bit still uses the AWE API though the awe setting doesn’t make sense and isn’t used.

SQL server 2005+ is NUMA aware. Worth noting if you upgrade hardware for those old SQL 2000 instances

One lazy writer thread per NUMA node.

TCP NUMA affinity. Hadn’t heard of that but it is something to look up and check on. Marciej calls this “poor man’s Resource Governor”

The buffer pool is not just for data? Also does single page allocations for other caches. Interesting

An interesting demo to show the allocation of memory as the server starts and you run queries. dbcc freeproccache
Might clear buffers but the memory remains allocated.

Changing the Max memory live shown in Perfmon. Both target and total server memory adjust in real time.

Memory pressure can be virtual or physical and either internal or external. Inside SQL server, your physical memory pressure could be global across the instance or local to some smaller cache.

External pressure is a notification from the OS. Clock hands in SQL will sweep cache stores (need to read about this) and asks lazy writer to sweep buffer pool. This external pressure can lead to internal pressure.

Every memory cache has two clock hands: internal and external. Externals move together. Internal caches move separately from each other

Demo showing this by querying sys.dm_os_memory_cache_clock_hands

LRU algorithm used to clear caches

Procedure cache aging is based on cost of compilation, not cost of execution. This cache can also steal memory from the buffer pool.

Check on your caches with dbcc Memorystatus. Never have used that before but it shows nicely the “stealing” of buffer pool by the procedure cache

Good session and very interesting to get a little more insight into the memory of a server.

Brad presented a session that tries to help you identify and fix tempdb problems. Performance monitor is your friend. the local disks, avg read and write counters should help you determine if I/O is an issue. Watch for systems where you have >20ms for the averages, this is a gross number, and not an exact measure, but something to be aware of.

Wait states can help you to identify contention for tempdb on allocation structures. Using sys.dm_OS_waiting_tasks, you can look for pagelatches on the pfs, gam, and sgam pages. If you query and find that you have lots of waits for these allocations, you might want to add more tempdb files to help alleviate contention.

There is no reason a DBA should allow a database to run out of space. A quote from Brad and for the most part i agree. You ought to have alerting setup, monitor space, and grow files as needed. There are cases where a runaway event might cause issues, but for the most part, you should be managing space actively.

Optimizing: a variety of suggestions, with the idea that you ought to assume tempdb will be an issue over time. So pre-plan for performance.
– minimize usage. Dont return more rows than you need. Don’t sort data you don’t need to, don’t use order by or distinct if not needed, keep transactions short, index well, avoid temp tables.
– Avoid cursors, especially static or keyset driven cursors.
– avoid LOB columns if you can, consider vertical parittioning.
– avoid table variables
– avoid triggers where you can
– avoid aggregating large sets of data
– avoid snapshot isolation or read committed isolation levels
– if you use sort in tempdb for index rebuilds, schedule it during off hours

You can use these features if you need them, but understand they impact tempdb. Be smart and minimize the usage where you can.

Isolating tempd on separate physical disks can help speed it up, adding RAM might help if sql server can avoid spilling to tempdb, but that depends if you have memory pressure. If you do not have any memory pressure, then you may not get any benefit from adding ram.

SSDs are used for tempdb, but the problem is that tempdb has lots of read.write and can “wear out” an SSd drive.

“The DBA life is about compromise”. I would agree with that. We can’t always do what we want and dont have the money so we need to make tradeoffs.

Preallocate tempdb space, monitor, and then resize as needed. Use IFI for data growth if needed. Note this does not apply to log growth.

Multiple files can help prevent tempdb contention, recommendation is 1/4 to 1/2 the number of cores, up to 8. Note that you want to make all your files the same size.

If you enable TDE, which is a good feature, be aware that tempdb is encrypted. Also be aware that if you remove TDE from all databases, tempdb remains encrypted. Be careful of “testing” TDE on servers.