SQL Server 2012 RTM CU7 Released!
SQL Build: 11.0.2405 KB: 2823247
Quite a few fixes, similar to the ones in SQL 2008 R2 SP1 CU12 and SQL 2008 R2 SP2 CU6. Some issues specific to SQL 2012 are related to SSAS, fixing performance issues with systems with more then 32-logical processors, access violation issues with FILESTREAM-enabled but data is offline. FULL JOIN, DMV, Log Shipping, etc.
SQL Server 2008 R2 SP2 CU6 Released!
SQL Build: 10.50.4279 KB: 2830140
Many fixes are same as those released for SP1 CU12. But there are some additional fixes in this CU for PowerPivot, SSRS, Cursors, Encryption, one for Database Mirroring.
SQL Server 2008 R2 SP1 CU12 Released!
Build: 10.50.2874 KB: 2828727
Mostly engine fixes, few fixes for SSAS. Some interesting fixes around SQL Server freezing on shutdown, Assertion check failed/access volition for partitioning, index size issues with RCSI, data corruption.
“SQL Server is running slow!?!” Help!
How many times have we heard that SQL Server is running slow from end-user, but can’t track down why? Or what happened? In this post I hope to point out some of the things you can consider. I’ll approach this scenario two ways, 1) SQL Server is running slow due to processor is pinned 2) SQL Server is running slow but processor is not pinned.
SQL Server is running slow due to processor is pinned
What are some of the things that require CPU in SQL Server? When dealing with processor related issue, we need to know what might cause CPU utilization and what kind of utilization we have (privileged or user)? Before we start troubleshooting any kind of issue on the server, please ask the first obvious question? Is it really sqlservr.exe (SQL Engine) causing the issue? In CPUs case it is pretty easy, we can look at task manager. However if we wanted to look at the Windows Performance Monitor (perfmon), then we might want to look at least three counters; Processor\% Processor Time (_Total), Process\% User Time (sqlservr), and Process\% Privileged Time (sqlservr).
An interesting difference between Processor and Process counter is, Processor counter is an average of all processors visible to the operating system. Where as Process counter is a summation of all processor visible to the operating system.
Image 1: Processor Utilization
If we look at the utilization the Process\% User Time is much higher then Processor\% Processor Time, therefore to really see the utilization for SQL when looking at Process counters, we need to average the value to get true utilization. When looking at Processor\% Processor Time, I would like the values to ideally run around maximum of 70-80%, if these values are going above that for extended period of time, then we need to figure out what is causing CPU utilization. Maybe it is time to migrate some workloads off this server, maybe its time to purchase new hardware, or maybe we have runaway queries and need to do some performance tuning. All of this normally depends on having proper baseline of these values. However lets say we do have an issue so now what?
First thing to note, everything in SQL Server uses CPU. Like life, nothing comes free. Memory Reads (Logical Reads), Disk Read/Writes (Physical Reads/Writes), Hash Operations, Execution Plans, etc. all require CPU. Question is what is using up the CPU?
Maybe its physical operations?
If its physical operations I should see my Process\% Privileged Time higher then normal. Generally for SQL server we recommend this value should not exceed 25-30%. In addition to this we should see high disk usage or some kind of memory pressure. That is causing SQL Server to write dirty pages out or high amount of scans causing light number of physical reads. So we potentially need to look at the Logical Disk or Physical Disk counters, SQL Server: Access Methods, and SQL Server: Buffer Manager counters. In SQL Server we might see PAGEIOLATCH_* wait types in sys.dm_os_waiting_tasks DMV.
Maybe its logical operations?
If its logical operations, I should not see high number in Processor\% Privileged Time but should see high number in Process\% User Time. In SQL Server maybe I want to run a query to get the most expensive queries by Logical Reads from sys.dm_exec_query_stats to see who is causing excessive logical reads. You can look at Get Top 50 SQL Statements/Query Plans post on how to get this information.
Maybe its hash operations?
Again this should be user time utilization on CPU and if I have lots of Hash Operations, that means I have potential for high TempDB usage. So I might look for counters SQLServer:Access Methods\Workfiles Created/sec in relation to SQLServer:SQL Statistics\Batch Requests/sec. These values we like to see this ratio to be around 1:5 ratio. In SQL Server we might want to look for queries with high amount of worker time. In addition we might have SOS_SCHEDULER_YEILD wait_type in sys.dm_os_waiting_tasks if the box is being very busy.
Maybe its execution plans?
If its execution plans, then we might have high number of SQLServer:SQL Statistics\SQL Compilations/sec or SQLServer:SQL Statistics\SQL Re-Compilations/sec. If its re-compilations n the sys.dm_exec_query_stats we might find execution plans with high plan_generation_num. Alternatively we might have high compilations/sec because lots of Ad-Hoc T-SQL (any code that is not object with in SQL Server, such as stored procedures, triggers, etc.) code is being executed (greater then your baselines).
SQL Server is running slow but processor is not pinned
Well we looked at some of the scenarios where the CPU might be pinned, how about where CPU is not pinned? What might be issue then? In these cases, if your CPU is not pinned but things are running slow, the problem might be resource contention issue. Therefore you might have blocking in SQL Server. If you look at activity monitor you might see wait types such as LCK_M_*, or ASYNC_NETWORK_IO, etc.
We can look at DMV sys.dm_exec_waiting_tasks, sys.dm_exec_requests, and sys.dm_tran_locks to se where is the contention issue. From there we can further dig into the SQL statement using sys.dm_exec_sql_text and then further the execution plan using sys.dm_exec_query_plan.
In addition you might need to track blocking in SQL Server, please look at Blocked Process Report in SQL Profiler post.
Conclusion
I am sure everyone has run into their fair share of issues, unfortunately the answer is rarely simple, especially when it comes to performance issues. However I hope this post gave you some idea for your further troubleshooting. Another thing to consider is, what if the issue has happened few hours ago or last day and you are just finding about it? It is really outside scope of this post, but something’s that might help in this case is Management Data Warehouse, looking at the previous execution in the DMVs, or using other external monitoring tools such as Microsoft System Center Options Manager.
How to calculate Max Memory for SQL Server?
SQL Server is designed to manage the memory on a server dynamically. That is, it will acquire memory as needed and it will release it when asked to (by OS). Its the good kid on the play ground, making sure now other processes on OS don’t starve. Unlike 32-bit (x86), where the SQL Server process was capped to 1.7GB. On 64-bit (x64) we do not have this limitation, as the virtual address space (VAS) for user applications is 8TB. Therefore for SQL Servers running on x64, I often recommend to set Max Memory to control the amount of memory each instance can use. Until SQL Server 2008 R2, the Max Memory stated the amount of memory assigned to Buffer Pool only. However starting SQL 2012, it is almost all the memory SQL can consume. (How memory architecture changed in SQL 2012 is beyond this post, maybe a future post?). However even with new SQL 2012, we still should reserve some memory for SQL threads and external components. So I am still using similar formula for all versions until I have reason to change it.
* Please note all calculations are for x64 bit system.
Where …
Max Physical Memory = Total Physical Memory Available on Server. If you are calculating this for Clustered Environment, then I would use the Available Physical Memory from the Node with Least Amount of Memory.
Memory Reserved For OS = Really its not operating system I am referring to, any other applications running on server that is not SQL Server Engine. I have used the guidelines below for initial configuration. I will adjust this value for further monitoring and tuning. Namely I will monitor Memory\Available Mbytes and Page File\% Usage(_Total). On x64 I want Available Mbytes to be 1024MB or above and % Page File Usage <= 20%.
| Physical Ram | Ram Left for OS |
| 4096MB (4GB) | 1024MB (1GB) |
| 8192MB (8GB) | 2048MB (2GB) |
| 16384MB (16GB) | 3072MB (3GB) |
| 24576MB (24GB) | 4096MB (4GB) |
| 32768MB (32GB) | 5120MB (5GB) |
| 40960MB+ (40GB+) | 6144MB (6GB) |
Memory Reserved for SQL Server: This formula is borrowed from really SQL 2005/2008 days, but I am still using it in SQL 2012 with positive results.
Where ….
# of Max Threads: This is referring to maximum number of worker threads SQL Server can create. By default this value is “0″, meaning SQL calculates the maximum number based on number of CPUs as below. CPUs in this context referrers to all visible CPU to operating system. For example, if you have 2 Physical CPUs with 4 Physical Cores each and Hyper-Threading; for the purpose of this formula the # Of CPU = 16 (2 * 4 * 2).
This formula by all mean not absolute, please feel free to adjust it to your needs and environment. After you configure SQL Server with Max Memory as listed above monitor your Available Memory (Memory\Available MBytes), Page File (Page File\% Usage (_Total), SQL Server Memory Pressure (SQLServer:Buffer Manager\Page Life Expectancy) and adjust the memory you reserve for the OS and SQLOS.
Lets work through an example …
- Operating System: Windows 2008 R2 (x64)
- # of Physical Sockets (CPUS): 4
- # of Physical Cores per CPU: 6
- Hyper-Threading Enabled: Yes
- Number of SQL Server Instances: 2
- Physical Ram Available: 16GB (16384MB)
First Step: Calculate the maximum number of worker threads…
# of Max Threads = 512 + {[(4*6*2)-4)*16} = 1216
Second Step: Memory Reserved for each SQL OS
SQLOS = 1216 Threads * 2MB = 2432MB
Third Step: Memory Reserved for Operating System
Memory Reserved for OS = 3072MB (Based on my reference table above)
Forth Step: Total Memory Reserved for all Instances
Total Memory Reserved for SQLOS = 2 * 2432 = 4864MB
Final Step: Putting it All together
Max Memory Per Instance = [16384MB (Physical) - 3072MB (Reserved for OS) - 4864MB (Reserved for SQLOS)] / 2 (Number of Instances)
Therefore Max Memory Per Instance = 4224MB
WOW? Even though server has 16GB, we are leaving SQL server with only 4.125GB per instance. Please note this is extreme value, that is you have a SQL Server that is going to use all 1216 threads. Therefore if you monitor your SQL Server and actually see your Available Memory is ALWAYS above 1024MB, maybe you want to give more to your SQL instances. In this example lets suppose we know Instance 2 only requires 2048MB. Then we can assign 6400MB to instance #1 without working about performance.
I hope I explained this topic a bit, generally troubleshooting performance issues with Memory is lots of work. But I usually start here for dealing with memory related issues.
PS Please note I only use MB in my calculations, because we set our max memory in SQL Server to MB not GB or KB.
SQL 2008 R2 SP1 CU10 Released!
Build: 10.50.2868 KB: 2783135
A few fixes have been made, however for some reason I think I have seem most of this in previous CUs. Still fixes for the memory leak with AUTO_UPDATE_STATISTICS_ASYNC, non-yeilding schedulers for LOB objects, etc. fixes are included. So if yo have seen similar issues might be worth looking it.
SQL Server 2008 R2 SP2 CU4 Released!
Build: 10.50.4270, KB: 2777358
Many fixes are rolled up in this KB, some of the interesting ones for me (Since I primary focus on the engine) are..
KB2708012, EventID 833 logged in error log when VSS snapshot backup is taking place and write operating occurs.
This is interesting, because Error 833 is ..
SQL Server has encountered <n> occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [<full path of file>] in database [<database name.>] (<dbid>). The OS file handle is <file handle>. The offset of the latest long I/O is: <offset in hexadecimal>
This is usually you have disk I/O issues, but in this case nope. It is a “internal members of the overlapped structure is not initialized correctly”.
KB2770258, You receive in incorrect result when you run a query against a partitioned table in SQL Server 2008 R2.
There are other issues that have been fixes in this CU, might be worth a quick gander to see if any of these apply to you.
