March 2010 - Posts
London SQL Server UG Presentation, @ Microsoft Victoria (17th March 2010). As this was my First UG Presentation I picked a topic and dutifully researched and prepared the PowerPoint Slides & a brief introduction, @ the last minute we needed to change the order of presentations due to small technical hitch with one of the laptops for the first presentation.So having an earlier appearance, meant I conveniently forgot what I had planned (funny that!), so It was a more thinking-on-your-feet kind of presentation than I had planned.Overall I think it went reasonably well, although I do think l managed to skip discussing some areas of Importance however, and (for my sins) probably marginally overran my allotted time, while still only covering some of the presentation. So I thought it might be useful to follow-up with another supporting blog entry, in an attempt to cover the topic a little more concisely, so here is the PowerPoint presentation I presented, along with some further explanations of the slides, hopefully highlighting the KEY points I may have not been able to make or omitted during the presentation.
Attached Zip file ( Word documents & PowerPoint Presentation) these provide some further explantions of the UG Presentation.
I always welcome feedback, if you have any on this material or viewed my presentation please feel free to let me know your views
Having tried to make a fun play on words to illustrate that for Standard Editions of SQL Server 2005/2008 since the releases of these Cumulative Updates:
SQL 2005 SP3 & CU4 / SQL 2008 SP1 & CU2 we can make real use of AWE!
Since (Mid 2009) when these CU’s where released, the ability to make use of required privilege “locking-pages-in-memory” which previously was only available in Enterprise Edition, allowing us to make use of those AWE APIs for resolving working set trim issues that resulted in non-optimum performance.
So naturally this raises a few questions That I would like to find some answers for:
What impact +/- will this have on my databases performance? (Clearly that’s not an easy question to answer succinctly or with any great accuracy), I will need to find some discreet questions that will allow me to determine its impact +/- on the databases performance with this functionality.
Some questions I have are:
· What measure(s) would best indicate the impact?
· What Trace flag(s) are required?
I’m going to try to answer those and others during the next few days/weeks and will post my findings on this as I find them, It’s not that new (it’s been around for ~9 months now), but none the less it should be able to provide some decent improvements if its implemented correctly.
Maybe it’s just me, but with some of the MS Products being released in 2010 with "2010" in their product name, is the naming of the SQL Server product suite being released with product name that doesn’t make sense, our latest SQL Server Release which is now just about to be released is "SQL Server 2008 R2"My question is do you think this product name is ? Good, Bad or just plain confusing IMHO I think we could have been better placed if this was named "SQL Server 2010", rather then "SQL Server 2008 R2" I sometimes have to explain this next release is not just a service patched version of the 2008 version but actually a NEW release, with some GREAT features, generally the comments that follow are usually in the vain. So why isn't it named with a different product date in its description if it’s a new release.The various naming conventions in use don't help us to clarify this either when explaining it to others who are not familiar with these - Consider the following, it contains a mixture of naming conventions.Microsoft SQL Server 2008 R2 (CTP) - 10.50.1352.12 (Intel X86) Oct 30 2009 18:22:17 Copyright (c) Microsoft Corporation Enterprise Evaluation Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2) Interested to see how others perceive this naming conventions topic
I'm been running some queries (below) to help me identify when I have had time-sensitive performance issues around Memory/CPU, I didn't want to load up additional overhead to the system (unless absolutely neccessary) using traces or profiler - naturally we have various methods to do this Perfmon counters, DBCC, DMVs etc..
One quick way I like is to run a few DMV queries (normally back in seconds) to help me find those RECENT specific time periods when the system has been substantially changed in some way using, this is using the DMV dm_os_ring_buffers
This one helps me identify when I'm expericing Timeout Errors (1222).. modiy code to look for other error as highlight below
DECLARE @ts_now BIGINT,@dt_max BIGINT, @dt_min BIGINT SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, cpu_ticks_in_ms) FROM sys.dm_os_sys_info SELECT @dt_max = MAX(timestamp), @dt_min = MIN(timestamp) FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_EXCEPTION' SELECT record_id ,DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime ,y.Error ,UserDefined ,b.description as NormalizedText FROM ( SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, record.value('(./Record/Exception/Error)[1]', 'int') AS Error, record.value('(./Record/Exception/UserDefined)[1]', 'int') AS UserDefined, TIMESTAMP FROM ( SELECT TIMESTAMP, CONVERT(XML, record) AS record FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_EXCEPTION' AND record LIKE '% %' ) AS x ) AS y INNER JOIN sys.sysmessages b on y.Error = b.error WHERE b.msglangid = 1033 and y.Error = 1222 ORDER BY record_id DESC
Sample Output
| record_id |
EventTime |
Error |
UserDefined |
NormalizedText |
| 15199195 |
18/03/2010 14:00 |
1222 |
0 |
Lock request time out period exceeded. |
| 15199194 |
18/03/2010 14:00 |
1222 |
0 |
Lock request time out period exceeded. |
| 15199193 |
18/03/2010 14:00 |
1222 |
0 |
Lock request time out period exceeded. |
| 15199192 |
18/03/2010 14:00 |
1222 |
0 |
Lock request time out period exceeded. |
| 15199191 |
18/03/2010 14:00 |
1222 |
0 |
Lock request time out period exceeded. |
This one helps me identify when I have Unusally High Processing (> 50%) or # Page-Faults
SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/UserModeTime)[1]', 'bigint') AS UserModeTime,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/KernelModeTime)[1]', 'bigint') AS KernelModeTime,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/PageFaults)[1]', 'bigint') AS PageFaults,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/WorkingSetDelta)[1]', 'bigint') AS WorkingSetDelta,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/MemoryUtilization)[1]', 'int') AS MemoryUtilization,TIMESTAMPFROM ( SELECT TIMESTAMP, CONVERT(XML, record) AS record FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND record LIKE '% %'
) AS x
Example: Showing entries > 50% SQL CPU
| record_id |
SystemIdle |
SQLProcessUtilization |
UserModeTime |
KernelModeTime |
PageFaults |
WorkingSetDelta |
MemoryUtilization |
TIMESTAMP |
| 111916 |
66 |
29 |
36718750 |
1374843750 |
21333 |
-40960 |
100 |
7991061289 |
| 111917 |
54 |
41 |
50156250 |
1954062500 |
26914 |
-28672 |
100 |
7991121290 |
| 111918 |
57 |
39 |
42968750 |
1838437500 |
30096 |
20480 |
100 |
7991181290 |
| 111919 |
41 |
53 |
43906250 |
2530156250 |
22088 |
-4096 |
100 |
7991241307 |
| 111920 |
48 |
45 |
40937500 |
2124062500 |
26395 |
8192 |
100 |
7991301310 |
| 111921 |
52 |
43 |
35625000 |
2052812500 |
21996 |
155648 |
100 |
7991361311 |
| 111922 |
40 |
55 |
36875000 |
2637343750 |
33355 |
-262144 |
100 |
7991421311 |
| 111923 |
36 |
58 |
44843750 |
2786562500 |
47019 |
28672 |
100 |
7991481311 |
| 111924 |
31 |
64 |
53437500 |
3046562500 |
31027 |
61440 |
100 |
7991541314 |
| 111925 |
36 |
57 |
43906250 |
2711250000 |
37074 |
-8192 |
100 |
7991601317 |
| 111926 |
52 |
43 |
43437500 |
2060156250 |
29176 |
20480 |
100 |
7991661318 |
| 111927 |
71 |
24 |
33750000 |
1141250000 |
14478 |
16384 |
100 |
7991721320 |
| 111928 |
71 |
23 |
34531250 |
1116250000 |
12711 |
-20480 |
100 |
7991781320 |
| 111929 |
53 |
36 |
46562500 |
1714062500 |
26684 |
200704 |
100 |
7991841323 |
Finally one to provide some understanding of the level of memory state changes that are ocuring
SELECT record.value('(./Record/@id)[1]', 'int') AS 'record_id',record.value('(./Record/ResourceMonitor/Notification)[1]', 'VARCHAR(100)') AS 'ReservedMemory',record.value('(./Record/ResourceMonitor/Indicators)[1]', 'int') AS 'Indicators',record.value('(./Record/ResourceMonitor/Effect/@state)[1]', 'VARCHAR(100)') + ' - ' + record.value('(./Record/ResourceMonitor/Effect/@reversed)[1]', 'VARCHAR(100)') + ' - ' + record.value('(./Record/ResourceMonitor/Effect)[1]', 'VARCHAR(100)') AS 'APPLY-HIGHPM',record.value('(./Record/ResourceMonitor/Effect/@state)[2]', 'VARCHAR(100)') + ' - ' + record.value('(./Record/ResourceMonitor/Effect/@reversed)[2]', 'VARCHAR(100)') + ' - ' + record.value('(./Record/ResourceMonitor/Effect)[2]', 'VARCHAR(100)') AS 'APPLY-HIGHPM',record.value('(./Record/ResourceMonitor/Effect/@state)[3]', 'VARCHAR(100)') + ' - ' + record.value('(./Record/ResourceMonitor/Effect/@reversed)[3]', 'VARCHAR(100)') + ' - ' + record.value('(./Record/ResourceMonitor/Effect)[3]', 'VARCHAR(100)') AS 'REVERT_HIGHPM',record.value('(./Record/MemoryNode/ReservedMemory)[1]', 'int') AS 'ReservedMemory',record.value('(./Record/MemoryNode/CommittedMemory)[1]', 'int') AS 'CommittedMemory',record.value('(./Record/MemoryNode/SharedMemory)[1]', 'int') AS 'SharedMemory',record.value('(./Record/MemoryNode/AWEMemory)[1]', 'int') AS 'AWEMemory',record.value('(./Record/MemoryNode/SinglePagesMemory)[1]', 'int') AS 'SinglePagesMemory',record.value('(./Record/MemoryNode/CachedMemory)[1]', 'int') AS 'CachedMemory',record.value('(./Record/MemoryRecord/MemoryUtilization)[1]', 'int') AS 'MemoryUtilization',record.value('(./Record/MemoryRecord/TotalPhysicalMemory)[1]', 'int') AS 'TotalPhysicalMemory',record.value('(./Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'int') AS 'AvailablePhysicalMemory',record.value('(./Record/MemoryRecord/TotalPageFile)[1]', 'int') AS 'TotalPageFile',record.value('(./Record/MemoryRecord/AvailablePageFile)[1]', 'int') AS 'AvailablePageFile',record.value('(./Record/MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS 'TotalVirtualAddressSpace',record.value('(./Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS 'AvailableVirtualAddressSpace',record.value('(./Record/MemoryRecord/AvailableExtendedVirtualAddressSpace)[1]', 'bigint') AS 'AvailableExtendedVirtualAddressSpace',
TIMESTAMP
FROM ( SELECT TIMESTAMP, CONVERT(XML, record) AS record FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_RESOURCE_MONITOR' AND record LIKE '% %' ) AS x
Hello
This is a quick follow-up blog to the Presention I gave last night @ the London UG Meeting ( 17th March 2010 )
It was a great evening and we had a big full house (over 120 Registered for this event), due to time constraints we had I was unable to spend enough time on this topic to really give it justice or any the myriad of questions that arose form the session, I will be gathering all my material and putting a comprehensive BLOG entry on this topic in the next couple of days..
In the meantime here is the slides from last night if you wanted to again review it or if you where not @ the meeting
If you wish to contact me then please feel free to send me emails @ Neil.Hambly@hotmail.co.uk
Finally - a quick thanks to Tony Rogerson for allowing me to be a Presenter last night (so we know who we can blame !) and all the other presenters for thier support
Watch this space Folks more to follow soon..