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

Posted by NeilHambly | 1 comment(s)

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.

 

Posted by NeilHambly | 3 comment(s)
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

 

Posted by NeilHambly | with no comments
Filed under: ,

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..