Bob Duffy's Blobby Blog

One of the changes in SQL 20005 is that the default value of Max Worker Threads has changed from 255 to zero, meaning that SQL Server now automatically determines the optimum number of threads. You can determine the maximum number of threads SQL Server has actually configured and how many it is currently using by the following DMVs:

select max_workers_count From sys.dm_os_sys_info

select count(*) from sys.dm_os_threads

The formula to determine the max worker processes is as follows:

For x86 systems where total number of logical processors <=4

# max worker threads = 256

Otherwise:

# max worker threads = 256 + ((# Procs – 4) * 8)

For x64 systems where total number of logical processors <= 4

# max worker threads = 512

Otherwise

# max worker threads = 512 + ((# Procs – 4) * 16)

One reason why Hyper-threading causes SQL Server a lot of hassle is that it appears as two physical cores, so may result in SQL Server allocating too many threads.

In a previous blog Indicated the relationship between how to configure the max server memory base don the platform and max number of worker threads.

I've taken the plunge and installed Windows 2008 64-bit on my laptop after a re-imaging "opportunity" caused by IpSec and dogfooding a pre-release of some software that will remain nameless.

So far my trusty Dell 820 is working pretty well. Disk to disk copy was a respectable 45 MB/Sec, and I can use Hyper-V and VMM 2008 to play with my virtual machines.

Some common issues to watch out for that got me:

A) Bluetooth - I gave up
B) The Essential Windows Search in Outlook not installed by default. I used this blog to help http://www.win2008workstation.com/wordpress/2008/03/17/enable-windows-search-service/
D) Windows LiveWriter didn't work. Hard coding of o/s checks into setup - naughty naughty. I used this blog to help  http://weblogs.asp.net/cazzu/archive/2008/03/25/installing-windows-live-writer-on-windows-2008.aspx 

e) a few things are not installed by default, but can be selected from Roles and Features - like Desktop experience, Wireless LAN Management and dot.net 3.0.

f) Could not find a video driver for love nor money - I tries the NVidia Vista x64 bit driver available from dell and it worked!! no blue screens yet ;-)

Out last written exam tomorrow!!! There was definitely a sense of the end of a long journey when we finished the last class today (VLDB scenario). I don't think people will be jumping for the pub on Friday as everyone will be flopping down in bed ;-)

The SQL ranger team and centre of Excellence folk really put in some hard work here in Redmond. I doubt there was a single person on the course, organiser or student who has got a good nights sleep in 4 weeks.

Now all we have is the Qualification Lab and the Review Board next week.

Ahh I can almost smell home: the kiddies demanding wrestles, playing chase and some quality chill time with my wife who has probably sacrificed more than me these past few weeks...

I thought I would post some clarification on what the famous "Lock Pages in Memory" policy setting does and does not do.

1. It prevents the operating system from stealing the buffer pool (see previous blog on buffer pool), thus shrinking the working set.

2. It is NOT available on Standard Edition.

3. It ALSO enables an enterprise feature called "Large Page Extensions" on 64 bit systems. This allows SQL Server to allocate memory in 4-16MB chunks. This is order of magnitudes faster than the piddly default 4k size and highly recommended for processing huge queries.

3. You can tell if Lock Pages in Memory is enabled by looking in the sql server log for the entry "Large page extensions enabled" in the log.

4. It does not stop the operating system from shrinking the working set. The Thread and MemToLeave areas can still be bashed causing pressure. (see previous blog).

5. It is automatically enabled on 32 bit systems if AWE is used. As we said earlier. AWE sucks, so no big gain here ;-)

After the third exam, we only had a few sessions today and then carting for the first down time some people have had in a while. Ken the Instructor and veteran SQL ranger from rotation 01 kicked some butt on the track. I was in his slipstream (err if 1/4 a lap behind counts as slipstream) for most of the race about to get some payback, but alas a few too many pies to make that last bend.

The flag dudes took offence to me flattening a few fences, driving straight into someone else and hit me with the dreaded Penalty Button that cripples your cart down to 5mph and I was out of there ;-)

Ernest (Hong Kong), Bob (Ireland) and Dirk (Belgium)

Ken, not content with kicking our butts for three weeks with mind blowing questions and scenarios on every aspect of SQL Server, lays the pain down at carting...

OK I'm on a roll here blogging as my brain is full up on the MCA course. Here's a good tip on how to calculate the MAX_SERVER_MEMORY

a) calculate worker threads (assuming set correctly, as default max 255 may be too low for huge beasts)

select max_workers_count from sys.dm_os_sys_info

b) Figure out thread stack size

or

select max( stack_bytes_committed )
from sys.dm_os_threads

c) guess how much ram to leave for OS/apps and MemToLeave

2-4Gb guess

Formula:

MAX_SERVER_MEMORY= TOTAL_SERVER_MEMORY - [ Os/Apps Memory] - [Threads * Thread Size]

so an x64 with 1024 threads and 64 GB ram could have MAX_SERVER_MEMORY of 58 GB.

On the IA64 platform this would be 56 GB as ThreadSize is bigger.

I have a little picture below to show how memory is broken up in the SQL Server Working set.

Some key Myths to dispel:

a) The Max Memory Setting only sets the target memory for the buffer pool not for the whole of SQL Server working set. So do not set it as high as the MEM you think SQL has to play with, there is formula for determining amount of space to leave for Thread Stacks, but it eludes me ;-)

b) The "Lock pages in Memory" ONLY stops the buffer pool from being paged. Other areas of SQL are fair game so memory pressure may still kill you. Bottom line is Lock Pages in RAM will help EE run smoother but severe external memory pressure is a bad sign. If you lock pages in RAM and set the max server memory too high - I dread to think what could happen.. a nice big buffer pool and no space for threads maybe - who knows...

c)  in 32 bit AWE ONLY can be used for the DB Page Cache. There is a lot of confusion here as sometimes the DB Page Cache is called the "Buffer Pool" (even internally at MS), when it is just one of many components inside the buffer pool.

In fact AWE can actually make matters worse as the non AWE space needs to store a MAP to the AWE memory space as a reference pointer. On a 64 GB system I think this would come to about 512 MB all from the measly 1 GB or less the DB Page Cache has to play with out of non AWE RAM. Now that gotta hurt somewhere..

To make matters worse one place you can really need RAM is the the Query Workspace. This is targeted as 25%-75% of the buffer pool size. All the RAM in the world above 4GB on 32 bit platform isn't going to help here.

Exam #3 at the SQL Ranger course is tomorrow and a few of the candidates and Instructors have spoken about the huge pressure on Candidates.

Most people come from a community where they ARE the SQL experts and highest authority. Their managers, co-workers and love ones think it inconceivable that they won't pass the full programme and Dreaded Review Board. Boy does that create the peer pressure from hell.

On the plus side if you pass the exams, Qual lab and then fail the review board (which is quite common) you are still granted the "Ranger Apprentice of MCA Apprentice" title so you can resit the review board and have credit for passing the toughest SQL Server exams on the planet.

Like a lot of technical Candidates and Consultants I suffer from problems talking far too much about technology and solutions, when the board is looking for Leadership, Organisational Dynamics, Strategy and tactics and Communication.... time to brush up on those soft skills and stop being so nerdy folks  ;-)

I have been doing a great course with Paul Randal on disaster recovery for SQL Server. He has a great blog on just about everything to do with DBCC and disk structures and recovery. See http://www.sqlskills.com/blogs/paul/default.aspx

Here is a little flow chart I made up for some fun:

The art of the disaster game is to land on as many green squares as possible.

URLT stands for "Update Resume Leave Town", thanks Paul ;-)

I've just survived week 3 of the SQL Ranger Rotation 04 here in Redmond. Whats its been like: a break neck sprint through level 300-500 content on SQL nearly every aspect of SQL. Man I thought I was good on SQL Server until Paul Randal started explaining low level on Disk Structures - or Gert Drapers explained the finer points of UMS and and worker scheduling - My head hurts!!!

This must be as HARD CORE as training gets in SQL Server. We have class room sessions 8:30 till 6pm with members of the product team and heavy hitting speakers, and then go back to a hotel room ro study each night till 1am, get up at 7am, back to school, Study Group at Weekends, and Sunday is catchup. Then we sit the toughest exams I have ever seen in my life. Dont event think of getting 50% if you don't know intimately how PFS, GAM, SGAM, and IAM work togethor is, what PMM stands for, or actually get excited by learning hidden trace files, and undocumented DBCC commands.Coming here is as a so called SQL expert is like being a big fish in a small Pond and swimming into the sea. Everyones knows something you don't about SQL Server, and you begin to question what the hell you've  been doing for your life - working as a SQL professional or reading the dummys guide to SQL...This might be basic stuff for a Dev on the SQL team but for an external consultant its a stretch sometimes.

 My advice for anyone doing the programme. See those pre-requisites on the MCA:Database site? READ THEM. No don't skim through them on the weekend before the course READ THEM. A Week in and you'll be really regretting not putting the extra effort in...

There is a new white paper available on managing SQL Server for MOSS environments. http://go.microsoft.com/fwlink/?LinkId=111531&clcid=0x409

Great tips on Index defragging and use of DBCC.

One thing that I'm not sure I entirely agree with is the adding a proc to the user database to support index defrag. I prefer to add administrative procs to the master database so all users databases can enjoy them.

Another common questions is "can I change my content database schema" by adding a view or proc. The answer is pretty much NO - more information is contained in this article.

http://support.microsoft.com/kb/841057/

I'll be off to the states for the four week MCA:Database training. http://www.microsoft.com/learning/mcp/architect/database/default.mspx

Speaking to people who have been on the course before this is really exciting. We get to spend four weeks in training with folks from the product team, and some pretty heavy hitting SQL folk. It sounds a bit like a four week boot camp so internally at Microsoft it's know as the "SQL Ranger" course.

Sounds much cooler than "architect" which is currently among not so favourite words as its so overused. Everyone's an architect we have technical architects, solution architects, business architects, design architects, infrastructure architects, consulting architects, associate architects, data architects, exchange architects and sanitation architects. No longer do we "design" solutions we ahem "architect" them.

OK I know where the architect roles lie, and as software and infrastructure is getting more complex  its a key role - but the term/role is still wayyy overused.

I had an interesting conversation with a friend who said they knew someone who worked as an architect after just leaving college on a course involving computers and design. I was like "err surely that's not possible!" before realizing that the course was on building and interior design using CAD and other tools. Whoops.

Sorry I've been off the radar a while. I've been working as a Solution Architect on a large telco solution.

I'm very happy with the results, We managed to make good use of lots of SQL Server programmability features such as XML, service broker, SQL CLR, new error handling, UDF's and also integrate nicely with IBMMQ. We also build a nice test harness in Visual Studio Test edition and have a continuous build and regression testing with Visual Studio Team Build. A single change in a stored procedure, rules table or dot.net code is now automatically regression tested using hundreds of use cases. We extended Team Foundation Server a bit to allow for automatic production of key test documentation like a coverage matrix comparing requirements to test cases.

My number one gripe was de-queuing messages from IBM MQ 6.0 using dot.net 2.0. The API is a great, but I had hoped to use an event driven architecture. Unfortunately the current release (v6) of MQ Server only integrates with dot.net 1.1 for event driven de-queuing.

Anyone got a "simple" way to de-queue messages from IBM MQ using and event/trigger design pattern and plain old dot.net and SQL Server  ?

we ended up using a thin dot.net wrapper to the API called from the SQL Job every five minutes. Works well and as a bonus we get great operational monitoring via the SQL management tools used.

Of course as a mainly database dude, XML and messaging is the evil enemy you keep close to know your enemy. Seeing messages/rows come into SQL Server one at a time and locks/sec go mad is frustrating. Give me ETL, bulk data and set based queries any day ;-)

When moving to SQL 2005 from 2000, some customers are asking what real benefit 64 bit brings. The short answer is :

- 64 bit is the future - rumours are abound that future releases of SQL Server might not even have a 32 bit sku (except developer editions, etc).

- Virtually No OEM is making servers which are not 64 bit capable.

- 64 bit gives performance and headroom benefits, especially with RAM.

- 32 bit is soooo nineties, it just doesn't look nice in your server room.

Here is a great blog diving into some of the technical benefits:

http://blogs.msdn.com/mssqlisv/archive/2007/04/30/will-64-bit-increase-the-performance-of-my-sql-server-application.aspx

I always recommend 64 bit, unless a SQL Server is a "jack of all trades" box with lots of other applications or components installed that might not play nice.

There are some urban legends around the Windows stripe size that you should set for SQL Server. I have seen recommendations of 64k for both data and log internally at Microsoft by some heavy hitting data Dudes, so that is good enough for me ;-)

Generally a Storage Area Network will have a 64k block size anyway - so I like to match the size of the windows stripe to the block size of the underlying disk subsystem.

How to Change the Windows Strip Size

This can only be selected when you format the disk. Also know as "Allocation Unit Size". beware as the default is only 4k.

One thing to be aware of is that NTFS compression is not supported with a block size of 64. But no one uses that with SQL Server - right !!

How to Query the Stripe Size?

fsutil fsinfo ntfsinfo F:

Sample output shows 4k windows format stripe size

NTFS Volume Serial Number : 0xe660d46a60d442cb
Number Sectors : 0x00000000010ea04f
Total Clusters : 0x000000000021d409
Free Clusters : 0x00000000000f222a
Total Reserved : 0x0000000000003550
Bytes Per Sector : 512
Bytes Per Cluster : 4096
Bytes Per FileRecord Segment : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length : 0x0000000003eab000
Mft Start Lcn : 0x0000000000000004
Mft2 Start Lcn : 0x000000000010ea04
Mft Zone Start : 0x0000000000003ea0
Mft Zone End : 0x0000000000004700