Bob Duffy's Blobby Blog

In case anyone missed the acquisition: Microsoft acquired DATAllegro back in July. Press is here http://www.microsoft.com/Presspass/press/2008/jul08/07-24DataWarehousingPR.mspx

This means that we potentially have a customer with a 400+ TB database, and our roadmap will include scale out to a size four times bigger than oracle has ever achieved.

And how will we load a database this big?

well, the world ETL record held by SQL Server is 1TB in 25 minutes, which comes to about 450 million rows per second.

http://blogs.msdn.com/sqlperf/archive/2008/02/27/etl-world-record.aspx

Anyone on the database scene would have noticed a big surge in SQL Injection attacks around April this year. Developers coming cap in hand to the DBA to blame the database for a security breach and all that...

Hopefully everyone knows what SQL injection is - for those of you who don't, her's a good example.

http://imgs.xkcd.com/comics/exploits_of_a_mom.png

Technically SQL injection has nothing to do with security of the database - its really the application tier that needs to ensure that all user input is validated and where possible parameterized.

Following some boring/basic best practice like minimum rights for the application and use of stored Procs won't fully prevent SQL injection but it will certainly help mitigate any damage that it can do.

Older "classic" ASP applications seem to be the worst hit - not because the language itself is insecure, but more because people just didn't worry about SQL injection back in them there good old days when string concatenation was how things were done. Ye Hah!

To help detect SQL injection vulnerabilities in ASP applications, Microsoft has released the following FREE toolkit

http://www.microsoft.com/downloads/details.aspx?FamilyId=58A7C46E-A599-4FCB-9AB4-A4334146B6BA&displaylang=en

If you are a TechNet or MSDN customer (and who isn't!), you can download the final RTM of SQL Server 2008 here

http://technet.microsoft.com/en-ie/subscriptions/default(en-us).aspx

Personally I am really stoked about this release - I'm going to be involved helping enterprise customer in Ireland with upgrades, migrations, data warehousing, BI and training on the new platform.

This is what makes my job great ;-)

Paul Randall and Kimberly Tripp are probably the foremost speakers on SQL Server and they are coming to Dublin.

On Thursday 4th September, 2008 they are coming to Dublin to present at the SQL Users Group on Indexing and Fragmentation (Level 400).

If you go to ANY SQL Server sessions or work in proximity to SQL Server this is the talk to go to this year. Well if you could squeeze in seeing me at the SQL 2008 Academy that would be cool too.

Paul and Kimberly rock! They are currently the Subject Matter Experts for a lot of the Certified Master and Database Architect Programs. I was with Paul for a week on the MCA course and my head still hurts.

I'll personally buy a bottle of champagne for the first person who can get Paul or Kimberly to say "I don't know" on Indexing or low level storage questions ;-)

The questions below probably wont event make them pause for breath:

- How can null bit maps make query execution faster?

- At what % of pages read is a bookmark lookup too expensive compared to a scan?

- What is the most efficient index to support count(*) on a clustered table?

- Does a leaf in Non Clustered Index or data page in heap  have more physical IO's for a bookmark lookup?

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/