SQL Centre of Excellence

Last year one of the most popular sessions for SQL Saturday were the lightning talks. No need to register in advance, just register at the event for a FIVE minute slot. one hour and maybe ten speakers.

The rules are simple:

- Topic can be ANYTHING loosely related to SQL Server
- Strict five minute time, buzzer provided for cut off

We will have some mystery prizes. Last year prize categories were:

  • Best “New” speaker (no MVPs and product team folk)
  • Most entertaining
  • Best speaker

This years promised to be a mixture of short nuggets form industry experts with some light hearted content thrown in.

If you missed Matt Massons hilarious “How to use SSIS and slipstream to manage facebook” he is presenting a sequel “How to use SSIS to manage online dating”

@CarmelGunn penned “Paradise Lost? Advise found” with the catch tag of “When your data fits on a USB stick, more hardware’s unlikely to do the trick” http://blogs.prodata.ie/post/Paradise-Lost-Advise-Found.aspx

To register and for more information on this years SQL Saturday Dublin go to
http://www.sqlsaturday.com/229/

or catch up at #sqlsatdublin on twitter

These questions are focused on availability, more next week on performance and tuning. I helped write some of the Mirror questions on the v1.0 MCM exam which must be well retired by now, and I must admit they were pretty obscure.  Learn those failure conditions and resources/threads required on the MSDN articles and MCM videos!

HA - Availability

1. Define the difference between RPO and RTO ?

2. What are the key components of an SLA ?

3. What are the key characteristics of an SLO (or Service Level Target) (eight possible)

4. Name five different types of disaster, what they affect and recommended mitigation steps

5. Articulate the key feature differences between Log shipping and Replication

6. When and why would you use Failover clustering instead of Mirroring

7. What would be a blocker to using failover clustering (name three blockers)

8. A major web site wants “zero down time” and “zero data loss” and “zero user impact”. What SQL Server technology will achieve this by itself?

9. How can developers ensure that mirroring and always on do not affect the user experience when they failover WRT uncommitted transactions?

10. Now that you have answered (9) do you want to change your answer to (8) ;-)

11. What is the max failover time with a mirror with default settings?

12. What types of failures can cause this?

13. Explain the difference between “Looks Alive” and “is Alive” in WFC?

14. How does this change in SQL 2012?

15. Windows 2008 cluster components no longer need the HCL, but what “certification” do they need?

16. A customer has added a new disk to the cluster. Do they need to rerun the validation tool to maintain supported status?

17. Should a cluster use the vendor MPIO driver or the MS MPIO ?

18. Any special anti-virus considerations for clustering? name them

19. There is a three node cluster with a single instance. How many nodes should be removed as possible owners before patching?

20. Why might log shipping offer better performance for a data warehouse workload than mirroring?

21. What’s the maximum number of databases that it is recommended to Mirror on a single instance?

22. How many additional threads does a mirror consume on the principal per database mirrored?

23. At what level of hardware does enterprise edition offer more redo threads with mirroring ?

24. What OS and SQL editions can be supported with the SVVP ?

25. Is VMWARE or Hyper-V “Snapshot” technology supported by MS ?

26. What does the RHS.exe do in Windows 2008 clustering ?

Few people are naturally “wide” enough to cover the whole of the SQL MCM with ease, and no I don't mean in terms of eating burgers, ice cream, beer and carbs.

When I went to Redmond, I knew that I was weak in clustering, replication and partitioning. I may have ahem “architected” some clusters while working for Microsoft Consultancy Services and even reviewed some design documents, best practices, etc but I had not actually got my hands dirty. It was getting embarrassing as sales guys were even saying “You have a SQL cluster issue, you need to speak to our guy Bob. Bob – show them what's wrong will you”. A friend who runs the dell training centre took me through a series of labs and we made clusters, broke them, player with quorum models, looked into logs, etc. After that one evening some weight was lifted off my shoulders. I could now start to merge the theory and practice a bit better with some affirmation from someone who has set up maybe hundreds of environments. I also stopped avoiding clusters like the plague.

Some advice. If you don’t have and know your weak areas, either your already already an MCM, MCA and BI Maestro (and your name is Dirk Gubbels). OR maybe you haven’t looked hard enough.

Find those weak areas and embrace them. If you are going for MCA don’t be afraid to make it clear on your skills, where they blur and where they end. In my MCA review board I was asked a truck load of questions on ITIL, operations and data centre manageability, I recommended that we bring in a team including an Ops consultant (for which I had some contact), and explained that while I could help them navigate through some of the (obvious) high level issues to engage deeper we needed a specialist rather than just myself. I was more a solution/technical design and infrastructure dude than hard core data centre operations. Expecting a ding, I actually got a comment from the review board of “solid – knows what he knows and what he doesn’t know”. So don’t expect to be an expert in everything. In the MCM you may have to guess some questions as its a Microsoft Learning exam but in MCA – don’t bullshit so much. In the real world you don’t need to personally be an expert in everything to get to the correct result.

If you have read the "http://blogs.prodata.ie/post/So-you-think-your-a-SQL-MCM-Test-Yourself-Part-I.aspx” here some more questions to test where your at. If you read these questions and say “that was fun, must check out 1-2 things” you are ready. If you read them and go “holy crap”, maybe you need to watch the videos some more and attend some of the level 400-500 training events that are going around.

Health Warning: These are not actual MCM questions. Just some questions to demonstrate the type depth I personally would expect to see. If you pass these questions and fail the MCM – sorry, I’ll buy you a beer…

Locking and Blocking

1. How can you see the locks for a specific SPID (two common methods)

2. Someone has a shared lock on a resource. Can an update lock also be serviced?

3. Name two types of locks that can be made if an IX lock is in place ?

4. What can cause a row lock to escalate to a page lock?

5. How can you implement partition level locking?

6. When would you consider the “UPDLOCK” instead of the default shared lock??

7. How can you control lock timeout

8. Name two ways to deal with lock timeout in procedural code?

9. How can you avoid blocking (up to five methods)?

10. What is the relevance of the “'blocked process threshold”

11. How can you avoid deadlocks (six techniques)

Snapshot Isolation

1. What is ACID?

2. Someone issues a begin tran and then two identical select statement. Under what isolation levels will the same results return if updates are ongoing?

3. When RCSI is enabled what is increase in row length and why?

4. What is the difference between RCSI and SNAPSHOT?

Partitioning

1. What are the pros and cons of PV’s versus PT’s?

2. Name two improvements in query plans on partitioned tables in 2008+ over 2005

3. Are there any queries that will run slower on partitioned tables v non partitioned. Name some examples?

4. How do you enable partition level lock escalation?

5. How and why would you disable lock escalation completely on a partitioned table?

6. A customer with a complex OLTP is having performance problems. The developers have read about partitioning and want to apply this. Is this a good idea?

Index Fragmentation

1. What is the largest IO size with read ahead in enterprise and std edition ?

2. What is the largest amount of read ahead that Enterprise and standard edition will do ?

3. Do you want to change your answer to question (1) after doing question 2 ;-)

4 Explain the difference between logical, extent and physical fragmentation. Which ones prevent SQL Server read ahead from using larger IO size?

5. Why is the perfmon counter “Page/Splits per second” not very useful for detecting harmful page splits?

6. How can you detect harmful page splits versus natural ones?

7. List three ways to fix fragmentation

8. What are the pros and cons of using MAXDOP of 1 on an index rebuild for a data warehouse ?

9. Does a clustered index rebuild rebuild all the Non clustered Indexes ?

10. What can prevent online index rebuild operations (name three things)

11. How can you monitor the progress of an index rebuild?

HA - Backup

1. A log backup is started while a full backup is being taken. Will the log record by cleared ?

2. A crash happens during a backup. Will transactions started before the backup be written to the data file on disk? why ?

3. Does a backup also include any log details ? why ?

4. What does BACKUP LOG WITH TRUNCATE ONLY do?

5. How do you backup the “tail” of the log following disk failure ?

6. Will taking a full backup break the log chain ?

7. Name two common ways the log chain is broken on log backups ?

8. What is the largest IO size you can get with backups and how can you get it ?

HA - Restore

1. How do you set instant file initialization?

2. How much faster does instant file initialization make the log file creation?

3. Apart from a faster disk (and instance file initialization) how can you make restore form a full back up faster?

4. You have a customer that needs to create a 100GB log file on restore. How can they make this faster without additional hardware?

5. How can you determine how long the “undo” phase of restore will take ?

6. If you restore with “NORECOVERY” is the redo phase applied

7. What affects how long the “redo” phase takes ?

8. Why should you not use SSMS when recovering from a disaster ?

9. When and why would you use RESTORE..WITH STANDBY ?

10. How does this DMV help with a restore strategy ? “dm_db_persisted_sku_features”

HA – Consistency and Corruption

1. What is the number one cause of corruption ?

2. What is the difference between a hard and soft IO issues ?

3. What is a read-retry error ? does it result in data loss ?

4. Name two places 823, 824 and 825 I/O errors are logged ?

5. What is the recommended tool to check a disk subsystem for IO errors for an OLTP SQL workload?

6. Can you whiteboard the difference between torn page detection and checksum ?

7. What does checksum protect from that torn pages does not ?

8. You switch a database from torn-page detection to checksum. You run a query which will read a page from disk that is corrupted. At what point will the error be detected.

9. What types of errors does automatic page repair work with ?

10. Name some improvements in DBCC CHECKDB introduced in 2005 ?

11. Name some improvements in DBCC CHECKDB introduced in 2008 ?

12. Does DBCC CHECKDB check constraints?

13. How do you estimate how much tempdb may be required for a DBCC CHECKDB ?

14. DBCC checkDB always takes 20 minutes to run, Today it is 60 minutes and still going.

a. What is the most likely cause?

b. How can we help answer how long it will take?

Post any comments on questions that bug you. Would love to get an existing MCM to help provide answers but I’m too lazy ;-)

Just found out that I’ve been selected to speak at SQL Saturday Edinburgh on the 7th/8th of June http://sqlsaturdayedinburgh.com

I’m super excited as Edinburgh is one of my favourite cities, having been through my student years there and sampled many a pint in the Last Drop on Grassmarket. I’ve been back a few time for new year but never during the summer.

So what topic am I speaking on ? Well a topic fitting of a SQL Server MCA, SQL MCM, SQL Server MVP and Analysis services Maestro. Its a tool that leaves SQL professionals, IT professionals and dot.net developers shaking in their boots (or pulling their hair out).

Yes, my “Microsoft Access Power Hour” is finally coming to a SQL Saturday near you.

Come along and jeer, see what the enemy is wooing the business with;  throw some rotten tomatoes as we show how fantastic Microsoft Access is . If you can’t beat the enemy, join them: find out how to get SQL Server and Access to co-exist, even maybe work together, and actually mention the world “performance” without giggling.

If you have not seen what access can do I guarantee that you will change your opinion – or at least know your enemy better so you can fight your battles.

Yes, with Microsoft Access you do common tasks ridiculously faster than with the “enterprise” equivalents.

Some popular demos:

- Using Access to build your windows front ends in 30 seconds, while dot.net developers are still getting their coffee before white boarding class diagrams.

- Developing reports in 15 seconds. Make reporting services and even PowerView look cumbersome.

- Building data driven web sites in 60 seconds. Make Access do the hard work and leave web designers to work on the nice logos and arty stuff.

- Transferring data for ETL solutions in five seconds. Leave SSIS and BCP for when you need to take more time to justify your bill.

- Developing SharePoint solutions with Microsoft Access in 90 seconds, or optionally employ a SharePoint “workflow” developer for six weeks.

- Using Microsoft Access to bulk load and transfer data.

- How to bypass common access security to prove access is very insecure.

- How to make Access databases secure to annoy people who are trying to prove it is insecure.

- How to secretly scale Microsoft Access front ends to many hundreds of concurrent users (people have been killed for less)

- Why Microsoft Access can teach SQL Server a thing or two about meta data, extended properties, data management and being RAD.

We have  few places left for the three day agile data warehouse design course with Lawrence Corr. This is the first time we have run a dimensional modelling course in Ireland and we picked Lawrence Corr to present the course. Numbers are limited to only 25, so do book as soon as you can – we can’t accommodate a lot of people on this course.

If you are working with data warehouse’s or star schemas in any way shape or form, then  this will be a really good course. While I speak at a lot of event in the year I only take about one 2-3 day course a year and this is the one I am attending this year.

Good data modelling skills are the foundation of any star schema design and Lawrence has lots of experience to help guide us through the topic, having been an associate of Ralph Kimball himself and worked for Ralph Kimball University.

  • Day 1 – Modelstorming/ Agile BI requirements gathering
  • Day 2 – Agile Star Schema Design
  • Day 3 – Dimension Design Patterns

For more information and to book, follow the link below:

http://www.prodata.ie/events/2013/Agile_Data_Warehouse_Design.aspx

Indexing “Strategy”

by Stephen Archbold 16. March 2013 20:47

Hi,

Welcome to my first Prodata blog! First, allow me to introduce myself, my name is Stephen Archbold and I joined the Prodata team around 6 months ago. Having spent 5+ years in the same role as a SQL DBA, I was lucky enough to nab a role working as a SQL consultant with Prodata in order to expand my horizons. While some of the expansion has been terrifying, I’m delighted I made the move.

The Prodata blog is known for its excellent technical content and as a reliable source of information thanks to the hard work of Bob and Fintan (And sometimes Carmel when we make her!), and I hope I can keep up that tradition.

My previous blog http://simplesql.blogspot.com was geared towards “Reluctant DBA’s” and beginners, and trying to get people starting out more comfortable with the SQL product. I still feel that the gap between “Beginner” and “Comfortable” can be quite hard to bridge, so I want to introduce some of the more advanced concepts, in what I hope is an easy to follow and structured approach.

“Indexing” Versus “Indexing Strategy”

With that, I wanted to talk about a subject which has been cropping up more and more in the field. I will assume that most people know what indexes are, and why they’re useful (If not, hop over here, then hop back! http://simplesql.blogspot.ie/2011/11/why-are-indexes-good-for-your-server.html).

A pretty common indexing methodology is to play “Fix the broken query”. Find a query which is dying, give it a good index, watch it revive. And there’s not necessarily anything wrong with that. But what happens when over a 6 month period, you’ve address two poor queries per month, and they all happen to use the same table? You’ve applied the best index for your queries individually, but what impact is that having across the board. Is it better to have 12 individual “Best” indexes for 12 individual queries, or have 2 “Good enough” indexes for a workload of 12 queries?

This post is not intended to be a scare tactic against applying indexes, they are super, super important in performance tuning, and in most cases are your best friend for a poor performing query. This is just to give you an idea of some of the follow on steps you should consider after applying indexes to make sure they are effective enough to warrant the overhead they carry.

A phrase I’ve been hearing a lot when discussing indexes is “I know they can add some over head, but is it that much?”. Aside from the obvious additional overhead on maintenance (Rebuilds, Re-orgs), what about the day to day work of getting new data into your table? Well, let’s find out.

Overhead on DML

Let’s take a 3 column table, with a clustered index, and do a very simple insert.

(Please note this doesn’t include any of the Allocation Unit work (GAM, SGAM, IAM etc.), and won’t, that’s another topic for another day!)

clip_image001

2 Logical reads performed when updating a clustered index. Not so bad.

Now let’s add a non-clustered index on two of the columns and try that insert again.

clip_image002

6 logical reads, a 3X increase on the clustered index alone. Traversing the non-clustered index to perform the update added some additional IO’s.

Now let’s add a second Non Clustered Index on only one column this time and try the insert again :

clip_image003

It had a smaller index to traverse, but still incurred two additional reads to perform the insert, on top of the other Non Clustered Index we added previously.

This is a very simple demonstration, but I hope it gets the point across, that the “strategy” part of “Index strategy” is very important, as you need to make sure that what you’re gaining in read performance, you’re not losing on write performance.

And also, just because the index you add is to service one query, doesn’t stop it impacting on every insert, update and delete which occurs on the table.

Identify indexes who don’t justify their existence

This is very much “it depends” as to what quantifies justification, but the result of the below script should be a good guide. It calculates number of reads vs number of writes on the index, and if the ratio is write heavy, the index may not be the most efficient. I would advise looking for a ratio of at least 1:1 to justify keeping it, as anything below probably means you’re incurring write over head for little return on reads.

Important Note: This only tracks reads/writes since the last restart of the instance. If this doesn’t include a full business cycle, you may throw away an important month end index

This script is a slightly modified version of Brent Ozar’s (t|b) unused index script (Found over at http://sqlserverpedia.com/wiki/Find_Indexes_Not_In_Use). I’ve only dropped the “Drop Index” create statement so you have to think about what you’re doing :)

SELECT
o.name
, indexname=i.name
, i.index_id
, reads=user_seeks + user_scans + user_lookups
, writes = user_updates
, rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)
, CASE
WHEN s.user_updates < 1 THEN 100
ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
END AS reads_per_write
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id
INNER JOIN sys.objects o on s.object_id = o.object_id
INNER JOIN sys.schemas c on o.schema_id = c.schema_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
AND s.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000
ORDER BY reads

Identify potentially redundant indexes

Kimberly Tripp has already done extensive work in this area, and I would struggle to do it justice in the way she does, so I’ll give a link to her blog, and specifically sp_helpindex at the end.

What makes an index potentially redundant (Apart from never being used)? Take the following scenario:

You have two queries

· Select Col1 from MyTable

· Select Col1, Col2 from MyTable

You create two indexes :

· One on Col1

· One on Col1 and Col2

The first index is optimal for the first query, the second index is optimal for the second query. But both indexes share the “Col1” column. This comes back to “Best” vs “Good enough”, the second index cover both queries, and only requires one index update per write. If they are used equally, the first index could be considered redundant, as the second index will also service the first query.

Summarising

Try the sp_helpindex query, and do some experimenting with your queries (on a test system!) and indexes, and try to strike the right balance between read performance and write performance.

Indexes are absolutely a good thing, and can drag a system/process back from the brink of falling over, to completing in milliseconds. But there’s no such thing as free money, so make sure you keep the “Strategy” portion in your mind when considering your Indexing Strategy.

Further reading

http://www.sqlskills.com/blogs/kimberly/removing-duplicate-indexes/ - Kimberly Tripp sp_helpindex

http://www.sqlskills.com/blogs/kimberly/category/sp_helpindex-rewrites/ - sp_helpindex blog archives

I use the term “Checkpoint IO storm” to refer to issues caused by the checkpoint process “flooding” the disk subsystem. This is often diagnosed on OLTP systems as periodic high latency writes which occur at very regular intervals.

The basic problem is that SQL Server does not write data changes write to disk immediately (the MDF file), it will continue to only write to the log file and write dirty pages to the in memory buffer pool. Every now and then it will then flush the dirty pages to disk by the checkpoint process based on how long it thinks it would take to recover if the server crashed (the recovery interval).

For many customers this process goes un-noticed for other customers they suffer a very noticeable drop in service quality.

How do you Know you have a Checkpoint Issue ?

Well high latency periodically is a warning sign but the sure sign is when you can correlate the high latency to the checkpoint process using these performance counters:

- Buffer Manager\CheckPoint Pages/Sec

- Disk Secs / Write

- Disk Writes/Sec

As a test I ran a load test on a sample OLTP doing ecommerce transactions on the C: drive of my laptop and this is what they look like. You can see that my workload does about 105 IOPS and disk latency is usually pretty great (under 1ms as its SSD), but when the Checkpoint pages/sec kicks in the IOPS go up to 5,000 or so and disk latency goes up to 20ms. My critical inserts will now get 20ms of WRITELOG waits !!

image

Paul Randal goes into some detail in the checkpoint process on his Immersion course aimed at MCM candidates and also has a blog article on monitoring checkpoints further than the perfmon counters

http://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/how-to-monitor-checkpoints-137148

How do you solve checkpoint issues

There are a few tools in your arsenal.

a) Reconfigure the disk subsystem to handle the burst smoother (if you can get past the SAN mafia).

b) Set the recovery interval on your instance This is roughly the number of minutes of data SQL Server will keep dirty before flushing. If you lower this the checkpoints will be more frequent.   http://msdn.microsoft.com/en-us/library/ms191154.aspx

c) Make the checkpoint flush over a longer time to regulate the IO using the checkpoint command and the [checkpoint duration] flag. See http://msdn.microsoft.com/en-us/library/ms188748.aspx 

Lets See this [checkpoint duration] thing in action

You need to be careful as if you specify a checkpoint duration too low, this will cause spikes and if you specify a period too long, SQL server will not be writing as fast as the pages are getting dirty so will need to do another unregulated checkpoint after the manual one!

As an example I ran the script below while a OLTP load was applied.

image

image

A bit better but not perfect ;-( you can see that the 5,10 and 20 second checkpoints for this particular workload still get a small latency spike (the red line). Not as bad as the 20ms, but 4-10ms all the same, not great for log writes.

I then tried the following script

image

image

Much better we can see that peak IOPS have gone down from 9,000 during the “IO storm” to under 3,000 and latency barely kisses 1ms. The SAN guys can buy us a Guinness…

So why don’t I go away and mess with this right now?

Two reasons. Firstly you need to know the workload super well to start messing with the checkpoint. The default settings are kind of self regulating, so can handle when the workload changes.

Secondly, this issue can more often be fixed by basic disk best practices such as not placing the data and log on the same spindles or LUN, or if the server has two HBA’s that are not muti-path, placing the data on one and the log on another,or if a big data load considering minimal logging.

One issue I have seen a few times is if the SAN runs out of write cache half way through the checkpoint. You can be in a world of pain as SQL Server will be pushing at potentially tens of thousands of IOPS to the cache and suddenly boom – the SAN can’t handle it as it runs out of cache and all hell breaks loose.

we finally have a date for this years SQL Saturday Dublin It is June 22nd, 2013. The registration site is below:

http://www.sqlsaturday.com/229/

Without question this will be the biggest SQL Server event in Ireland for 2013.

Last  year we had about 400 attendees, with many of the worlds top SQL speakers present, excellent prizes, barbeque, beer and an after event party with a real Irish theme. The event was sold out and the Hilton Hotel was packed with everything to do with SQL Server. Precon wise we had Matt Masson from the SSIS product team delivering a deep dive on SSIS design patterns for performance and new features in SQL 2012.

This year we are looking to top that. Speaker submissions are now active on the site and we will be looking to run TWO full day pre-conference seminars on the Friday. On the Saturday we will have FOUR tracks covering core database, BI, Developer and cloud/special interest.

For those of you who know Marco and Sandra, Inga, Niall and Carmel who are helping run the event , you’ll know that this will be a special/unique event and if you can stay for the evening you wont be disappointed.

Here is a clip of Jack Wise in action from last year

Jack wise at the last SQL Saturday

 

Hope to see you there!!

Whops I accidently deleted the original list of questions. Like a true data professional I don’t backup my  blog. Here’s of the original questions.

Internals

1. What’s an example of a legacy LOB type v new LOB Type ?

2. Are Legacy LOB Types stored on/Off row by default

3. Are New LOB Types stored on/off row by default ?

4. How can you change this behaviour for legacy LOB Types?

5. How can you change this behaviour for New LOB Types?

6. If you change row storage for a LOB from “on row“ to “off row” on a 100GB database how long will it take ?

7. How big in bytes is the “version tag” for versioned rows and what does it store ?

8. How often to PFS pages appear in a data file?

9. How often to IAM pages appear in data file?

Index Strategies

1. A table has 1,000 pages with 100 rows per page. A query on an (select * from blah where MyColumn=1) column returns 500 rows and there is an index on MyColumn. Will it use the index if the index is non-clustered?

2. Can an index include LOB data types?

3. How big can the clustered Index Keys on a Materialised View be?

4. Recommend TWO Ways to get rid of unused indexes if you have a representative trace file

Log Internals

1. Is there such a thing as a non-logged operation ? If so, examples?

2. How Can you get minimally logged inserts on a clustered table with rows in it ? (specify two things to do)

3. A Developer issues a begin tran followed by an update and then a checkpoint. Are the changes written to disk in the checkpoint ?

4. How do you resolve a “checkpoint IO storm”

5. Someone creates a 1GB log file. How many VLF’s are there ?

6. Is a log backup smaller or larger with BULK_LOGGED than with FULL logging mode ?

7. Is rebuilding of an index in simple mode minimally logged?

8. Is re-org of an index in simple mode minimally logged?

9. You need to update a 100GB BLOB when in bulk logged mode. Can this be done minimally logged? If so, how?

10. If you switch from full to bulk and then MDF corrupts. Can you backup the tail log ?

11. How can you make the log file initialisation faster ?

Statistics

1. How many steps are in a stats histogram

2. Are histogram values left or right based?

3. What can cause a recompile (there are 14 events). List as many as you can.

Here are the slide decks and sample source code from the SQL/Dot.Net performance tuning MasterClass. I hope that there was some food for thought there.

The password is the name of the company who wrote the DVD Store application.

Decks Decks

Page List

Page List