SQL Server Community Blogs

How your Standard can become AWEsome

NeilHambly — Sun, 21 Mar 2010 15:41:00 GMT

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...(read more)

Product Naming Conventions - Does it make sense

NeilHambly — Sun, 21 Mar 2010 12:41:00 GMT

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"...(read more)

Are you a SQLBits attendee? get a discount for PASS Europe SQL 2008 R2 Launch

simonsabin — Thu, 18 Mar 2010 23:22:13 GMT

PASS have given use a number of prizes for SQLBits. We have registrations for PASS Europe and PASS North America as well as DVD sets of the sessions from the North America summit last year to give away.

Not only that, if you want to go to PASS Europe and you are a SQLBits user then you can get a promotion code that not only gives you the best price it also raises money for SQLBits.

To get the promotion code login and then visit the community page http://www.sqlbits.com/about/Community.aspx

Are you a SQLBits attendee? get a discount for PASS Europe SQL 2008 R2 Launch

simonsabin — Thu, 18 Mar 2010 23:22:13 GMT

Not only that, if you want to go to PASS Europe and you are a SQLBits user then you can get a promotion code that not only gives you the best price it also raises money for SQLBits.

To get the promotion code login and then visit the community page http://www.sqlbits.com/about/Community.aspx

Scottish Area SQL Server User Group Meeting, Edinburgh - Thursday 25th March

MartinBell — Thu, 18 Mar 2010 20:58:00 GMT

User Group Meeting for the Scottish Area SQL Server User Group Meeting on 25th March 2010...(read more)

DMV {dm_os_ring_buffers} - Queries to help pinpoint current Issues / usual usage patterns

NeilHambly — Thu, 18 Mar 2010 13:47:00 GMT

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

Performance Gains using Indexed Views and Computed Columns

NeilHambly — Thu, 18 Mar 2010 10:41:00 GMT

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

Information regarding Collection 6233 - Implementing and Maintaining Business Intelligence in Microsoft® SQL Server® 2008: Integration Services, Reporting Services and Analysis Services

Testas — Thu, 18 Mar 2010 10:02:00 GMT

At the London SQL Server User Group I was asked a number of questions regarding the release of Collection 6233 - Implementing and Maintaining Business Intelligence in Microsoft® SQL Server® 2008: Integration Services, Reporting Services and Analysis Services, which has been authored by myself. Particularly regarding the SSIS component of the collection.

Elearning is an interactive training experience that enables you to learn at your own pace. With a variety of learning tools including demonstrations, animations as well as written materials and the addition of labs that enables you to reinforce your learning. Microsoft Elearning can provide a valuable learning tool when you may not have the time to take out of the office to attend a course

This 24-hour collection provides you with the skills and knowledge required to implement and maintain business intelligence solutions on SQL Server 2008 and also helps students to prepare for Exam 70-448 and you can buy each part individually see:

http://www.microsoft.com/learning/elearning/course/6233.mspx

However, you will create a simple data warehouse in this collection and use SSIS to create packages to populate the data warehouse with data, exploring key concepts and tools to faciliatate this. This was a decision thart I took when writing this course based on feedback from hundreds of students who attended Microsoft Official Courses on SSIS. They wanted a course that allowed them to use SSIS to work with a data warehouse. This collection will certainly enable you to explore the options available in SSIS to meet this requirement while at the same time meeting the certification requirements

I hope this answers the questions regarding this collection and hope you enjoy this collection

Chris

Have you really fixed that problem?

DavidWimbush — Thu, 18 Mar 2010 07:46:00 GMT

The day before yesterday I saw our main live server's CPU go up to constantly 100% with just the occasional short drop to a lower level. The exact opposite of what you'd want to see.

We're log shipping every 15 minutes and part of that involves calling WinRAR to compress the log backups before copying them over. (We're on SQL2005 so there's no native compression and we have bandwidth issues with the connection to our remote site.) I realised the log shipping jobs were taking about 10 minutes and that most of that was spent shipping a 'live' reporting database that is completely rebuilt every 20 minutes. (I'm just trying to keep this stuff alive until I can improve it.) We can rebuild this database in minutes if we have to fail over so I disabled log shipping of that database. The log shipping went down to less than 2 minutes and I went off to the SQL Social evening in London feeling quite pleased with myself. It was a great evening - fun, educational and thought-provoking. Thanks to Simon Sabin & co for laying that on, and thanks too to the guests for making the effort when they must have been pretty worn out after doing DevWeek all day first.

The next morning I came down to earth with a bump: CPU still at 100%. WTF? I looked in the activity monitor but it was confusing because some sessions have been running for a long time so it's not a good guide what's using the CPU now. I tried the standard reports showing queries by CPU (average and total) but they only show the top 10 so they just show my big overnight archiving and data cleaning stuff. But the Profiler showed it was four queries used by our new website usage tracking system. Four simple indexes later the CPU was back where it should be: about 20% with occasional short spikes.

So the moral is: even when you're convinced you've found the cause and fixed the problem, you HAVE to go back and confirm that the problem has gone.

And, yes, I have checked the CPU again today and it's still looking sweet.

Support for Windows 2000 Professional and Server to be dropped

Testas — Wed, 17 Mar 2010 16:11:00 GMT

Windows 2000 Professional and Windows 2000 Server are approaching 10 years since their launch and both products will go out of support on July 13, 2010.

Windows XP was launched back in 2001. While support for the product will continue, Service Pack 2 will go out of support on July 13, 2010. From that date onwards, Microsoft will no longer support or provide free security updates for Windows XP SP2. Please install the free Service Pack 3 for Windows XP to have the most secure and supported Windows XP platform.

Finally, Windows Vista with no Service Packs installed will end support on April 13 2010. Please install the free Service Pack 2 for Windows Vista to have the most secure and supported Windows Vista platform.

This may have implications for older version of SQL Server that are installed on these products and you should assess whether it is time to upgrade

Chris

SQLbits 6 registration is open

simonsabin — Wed, 17 Mar 2010 12:28:41 GMT

The title says it all

Registration for SQLBits is now open.

We expect this to be very very popular so make sure you register quickly.

http://www.sqlbits.com/

SQLbits 6 registration is open

simonsabin — Wed, 17 Mar 2010 12:28:41 GMT

The title says it all

Registration for SQLBits is now open.

We expect this to be very very popular so make sure you register quickly.

http://www.sqlbits.com/

SQLBits 6 Registration Now Open

MartinBell — Wed, 17 Mar 2010 12:01:00 GMT

SQLBits VI registations opened on 17 March 2010 at 12:00 ...(read more)

SQL Social

SteveP — Wed, 17 Mar 2010 11:29:00 GMT

Wanted to thanks Simon for putting together a great event last night. It was a real pleasure to be in the company of some of the greats(Itzik, Greg, Davide, Bill and not forgetting Mr Sabin) in the SQL server space. The venue was superb and the knowledge of the panel covered pretty much every corner of the SQL Server platform.

I'm very much looking forward to seeing how the social evenings progress. It's going to be hard to follow this one.

Acommodation deal annoucced for SQLBits 6

simonsabin — Wed, 17 Mar 2010 01:27:22 GMT

The details of our acommodation deal have been announced. We are going to be using the Park Paza on Westminster Bridge.

http://www.parkplaza.com/hotels/gbwestmi

The speakers are going to be based there so why not join us, its a short walk from the venue.

We have a promotion code SQL6 which gives you a greatly reduced rate of £139 + VAT.

If you want cheaper then consider using Laterooms

Acommodation deal annoucced for SQLBits 6

simonsabin — Wed, 17 Mar 2010 01:27:22 GMT

The details of our acommodation deal have been announced. We are going to be using the Park Paza on Westminster Bridge.

http://www.parkplaza.com/hotels/gbwestmi

The speakers are going to be based there so why not join us, its a short walk from the venue.

We have a promotion code SQL6 which gives you a greatly reduced rate of £139 + VAT.

If you want cheaper then consider using Laterooms

Be the first in the UK to leanr about Windows Mobile 7

simonsabin — Tue, 16 Mar 2010 09:21:52 GMT

Register Now for UK Tech Days: Windows Phone 7 Series

https://msevents.microsoft.com/cui/EventDetail.aspx?culture=en-GB&eventid=1032442961

Come and join us to learn how to build applications and games for Windows Phone 7 Series.

Be amongst the first in the UK to learn how to build applications and games for Windows Phone 7 Series. We’ll introduce you to the development platform and show you how to work with the Windows Phone 7 Series development tools. Each session will ramp up your knowledge and help you become skilled in developing games and apps for Windows Phone 7.

This will be a fun and practical day of detailed Windows Phone 7 Series development sessions covering the new Windows Phone 7 Series specification, applications technologies and services.

VBUG Manchester March 3rd 2010 - Slides and examples

MartinBell — Mon, 15 Mar 2010 22:23:00 GMT

At the VBUG meeting in Manchester on 3rd March, I was scheduled to talk about Table Valued Parameters, but when I got there the guys wanted something more general so I talked about some of the new features of SQL 2008. The presentation is here and the TVP demo project is here . My blog postings on TVPs are listed at http://sqlblogcasts.com/blogs/martinbell/archive/tags/TVP/default.aspx . Information about the new date and time data types http://sqlblogcasts.com/blogs/martinbell/archive/2009/05/15...(read more)

SSMS - Please get keyboard shortcuts working for schemas

simonsabin — Mon, 15 Mar 2010 11:08:08 GMT

My current client is using schemas which is good as it provides nice seperation. However it causes me pain on a daily basis.

The reason. I can't use the built in keyboard shortcuts in SQL Server management studio.

I can't believe how painfully annoying this is. It's just madness that SQL Server's own tool doesn't support a best practice feature.

You can vote on the connect item here to get this sorted https://connect.microsoft.com/SQLServer/feedback/details/349116/keyboard-shortcut-alt-f1-sp-help-doesnt-work-for-tables-belonging-to-non-default-schemas

I've blogged about this before, but this just annoys me so much I'm posting about it again.

Surely it can't be difficult to change. The other option is to open up SSMS so we can use add-Ins. I've blogged that before and you can vote on that suggestion here https://connect.microsoft.com/SQLServer/feedback/details/265567

I've also raised a connect item to give other improvements to keyboard shortcuts https://connect.microsoft.com/SQLServer/feedback/details/390612/improvements-to-keyboard-shortcuts-in-ssms

Relational Database pioneer Chris Date is giving a seminar 13th/14th May Edinburgh on "SQL and Relational Theory - How to Write Accurate SQL Code"

tonyrogerson — Mon, 15 Mar 2010 07:21:00 GMT

One of the pioneers of the Relational Database, Chris Date is giving a 2 day seminar in Edinburgh (13th and 14th May 2010) based around his new book "SQL and Relational Theory - How to Write Accurate SQL Code" which if you don't already have I'd say is a must buy. When I first saw this and what he will cover I thought, oh yer - this is going to cost the earth, well it doesn't - its £750 for the two days and there are discounts available for multiple bookings, being a member...(read more)

Database Design - Surrogate keys: Part 1 of many (Rules for Surrogate Keys, E. F. Codd and C J Date research and problems they solve)

tonyrogerson — Sat, 13 Mar 2010 13:42:00 GMT

I started writing an article for my blog on surrogate keys drawing in the original research by E F Codd and C J Date, its getting a bit big :) so I'm going to chop it up into a number of posts over the coming weeks depending on my time. I'm interested in your thoughts and if you disagree please let me know but more importantly give me references so I can go back and read what you've read and either modify my position or not :), either way it will improve the article. Hope it makes sense...(read more)

Visual Studio Professional 2010 upgrades

MartinBell — Sat, 13 Mar 2010 12:33:00 GMT

Discounted Visual Studio 2010 upgrade...(read more)

ORMs - Should DBAs just lighten up?

simonsabin — Fri, 12 Mar 2010 23:27:12 GMT

I did a presentation at DDD8 on the entity framework and how to stop your DBA from having a heart attack. You can find my demos and slide deck here http://sqlblogcasts.com/blogs/simons/archive/2010/01/30/Entity-Framework-how-to-stop-your-DBA-having-a-heart-attack.aspx

Whilst at DDD Mike Ormond interviewed me about my view on ORMs and the battel between DBAs and Devs. To see what I said go tohttp://bit.ly/bnf1By

Learning SQL Server

MartinBell — Fri, 12 Mar 2010 07:21:00 GMT

Everyone is different when it comes to learning, here are a few options to help you find your own preference....(read more)

Coding standards

Piotr Rodak — Thu, 11 Mar 2010 22:07:00 GMT

This post will be about coding standards. There are countless articles and blog posts related to this topic, so I know this post will not be too revealing. Yet I would like to mention a few things I came across during my work with the T-SQL code. Naming convention - there are many of them obviously. Too bad if all of them are used in the same database, and sometimes even in the same stored procedure. It is not uncommon to see something like create procedure dbo . Proc1 ( @ParamId int ) as begin declare...(read more)