Docstoc

3-Snapshot Isolation Demo Script

Document Sample
3-Snapshot Isolation Demo Script Powered By Docstoc
					Microsoft Confidential – Microsoft Internal Use Only


SQL Server 2005 Launch Content

Data Platform Track, Session 01
Building Highly Available Systems
with SQL Server 2005

Level 200
Demo Script 3
Snapshot Isolation
Microsoft Confidential – Microsoft Internal Use Only

CONTENTS

BEFORE YOU BEGIN ................................................................................................................................. 1
TIPS AND TRICKS................................................................................... ERROR! BOOKMARK NOT DEFINED.1
DEMO 3: SNAPSHOT ISOLATION ............................................................................................................. 2
  USING THE “DUAL DATABASE MONITOR (DDM)” APPLICATION TO SHOW APPLICATION WAITTIME WHEN BLOCKING OCCURS DUE TO LONG RUNNING AND POORLY
  WRITTEN TRANSACTIONS – IN BOTH SQL SERVER 2000 AND SQL SERVER 2005. THEN ENABLE READ-COMMITTED WITH STATEMENT-LEVEL SNAPSHOT TO SEE
  HOW ALL OF THE BLOCKING PROBLEMS ARE EASILY RESOLVED WITH NO ADDITIONAL APPLICATION CHANGE! ................................................................ 2




SQL Server 2005 Launch Event Content                                     i                                                 Data Platform Track Session 01
Microsoft Confidential – Microsoft Internal Use Only

Before you begin
1. Logon as Administrator.
2. The password for the VPC image login is Pass@word1

We’re using Administrator because we’re going to be stopping and starting a lot of services in these demos. Please start your VPC
and be logged in already before you do any demos and you won’t need to highlight this fact.


Tips and Tricks
Make sure you test this script THOROUGHLY on your hardware and with your network, etc.

Also, get a good feel for the entire configuration of the VPC. This will help you to better explain configuration settings, where you feel
it might be appropriate. The more you know about this configuration the more smoothly these demos will go.

Demos 1, 2 and 3 – build on one another. Ideally, you want to leave the VPC running and even leave the required applications
running. The two applications you will use are: SSMS and the DDM (Dual Database Monitor). The DDM was created specifically for
launch demos… Be sure to get a feel for that within these demos.

If you need to leave Management Studio, be sure that you have SQLCMD mode turned on – otherwise, some scripts may fail.

    Bug: It looks like the Tools/Option setting under – By default, open new queries in SQLCMD mode – option does NOT stay
    after you close SSMS. Because of this you’re OK when you set it for this demo. However, if you leave SSMS and go back in,
    this option will no longer be in effect. You will need to set this each time you enter SSMS – which is ONLY once (and this demo
    takes that into account, by design).




SQL Server 2005 Launch Event Content                           1                                          Data Platform Track Session 01
Microsoft Confidential – Microsoft Internal Use Only

Demo 3: Snapshot Isolation
Make sure you are VERY comfortable with the talking points.
Also, this demo requires that you return to previous scripts to run code that’s currently commented out. Be sure to feel comfortable
with the movement between the different scripts here.



Using the “Dual Database Monitor (DDM)” Application to show application waittime when blocking occurs due to long
running and poorly written transactions – in both SQL Server 2000 and SQL Server 2005. Then enable Read-Committed
with Statement-level Snapshot to see how all of the blocking problems are easily resolved with no additional application
change!
                            Speaker Script                                                                     Steps
Now, we’re going to return the Dual Database Monitor (DDM). You can           Perform these steps on SQLLaunchVPC.
see that it’s still adding rows – even while we were discussing snapshot        1. Return to the DDM. If you’re comfortable with it you’ve probably
isolation …                                                                       left it running (which looks great if you can leave it running) and
(Again, spend a moment showing the counters for both 2000 and 2005)                now you’ll just return to see the counters are higher and rows are
                                                                                   STILL being added…


For SQL Server 2000                                                             2. From the Demo03_SnapshotIsolation project, open and execute
We’re going to simulate a poorly written transaction. This transaction             the “Step 1” Query file.
begins a tran with a BEGIN TRAN statement, it then executes an INSERT           3. While it runs, return to the DDM and watch how the 2000 server
but then the batch completes…                                                      essentially stops loading data… then the “ONLINE” part will change
This is a transaction that spans batches. When a transaction spans                 to “BLOCKED.”
batches, this lengthens the time of the transaction. Because of the
default isolation in SQL Server, this also creates blocking as only the
active transactional data is used – and isolated.
For SQL Server 2005                                                             4. From the Demo03_SnapshotIsolation project, open and execute
We’re going to run the exact same script to create the exact same                  the “Step 2” Query file.
blocking environment by default. And – there is NO DIFFERENCE in the            While it runs, return to the DDM and watch how the 2005 server
environments by default – the environment is exactly the same... notice         essentially stops loading data… then the “ONLINE” part will change to
the “blocking” again.                                                           “BLOCKED.”

Now, just to make sure that it’s clear… the inserts are NOT blocked, only       5. From the Demo03_SnapshotIsolation project, open and execute
the SELECT which is getting the count, is blocked.                                 the “Step 3” Query file.
                                                                                Stay ONLY in this window and switch to the messages window. You
                                                                                will see that the two inserts will complete but then the script will be
SQL Server 2005 Launch Event Content                                      2                                             Data Platform Track Session 01
Microsoft Confidential – Microsoft Internal Use Only

I know it seems like DDM is blocked for INSERTs but it’s NOT. DDM not         “waiting” to complete. It’s the “count” that is blocked.
only inserts rows but it also does a SELECT count(*) to return the current    TIP: If you feel comfortable, execute *just* the two INSERTs first and
rowcount. It is the SELECT that’s waiting NOT the INSERT and this script      show how they complete. Then, execute the SELECT and show how it
can help prove it. It is the SELECT that’s waiting NOT the INSERT and         must wait.
this script can help prove it.
                                                                              IMPORTANT: Make sure you select the proper batches – from
                                                                              :CONNECT to go for each group:
                                                                                 :CONNECT…INSERT…go
                                                                                 :CONNECT…INSERT…go
                                                                                 :CONNECT…SELECT…go
We’ll eliminate all of the blocking now by committing these two open and      6. Return to the window that has the query for “Step 1” open.
poorly written transactions.                                                  NOTE: If you’re not sure how to easily switch back to an already open
Explain (again)…                                                              window, double-click the script from the Project List in the Solution
The real culprit here is the application that opens a transaction and does    Explorer Window.
not complete it. You should always avoid long running transactions that       7. In the “Step 1” window, highlight the word COMMIT (and only
wait for user input!                                                             that single word) and execute the commit.
                                                                              8. Do the same for the SQL Server 2005 transaction. Return to the
                                                                                 window that has the query for “Step 2” open.
                                                                              9. In the “Step 2” window, highlight the word COMMIT and execute
                                                                                 the commit.
                                                                              10. Point out that the “Step 3” script is no longer waiting!
                                                                              11. Close the “Step 3” Query file.
Let’s take a quick look at the DDM and see if the rows are coming back in     12. Return to the DDM to drive home the point that everyone is back
(and being counted). Remember, it was the SELECT that was blocked not             in and no one is blocked.
the INSERT. OK, see how everything is running again… All of the blocking
has been resolved by completing those transactions. However, if you are
running a mixed workload environment, you might want to allow queries
– without creating blocking…
Now we’re going to turn on Read Committed with Statement-level                13. From the Demo03_SnapshotIsolation project, open and execute
Snapshot for SQL Server 2005. While this option might look like it’s the          the “Step 4” Query file.
ultimate way to save the day, it’s important to realize that there are        14. Switch to the DDM to show that it’s still adding data.
applications which require – and benefit from – locking. There are
                                                                              IMPORTANT NOTE: The DDM might show BLOCKED for a moment as
benefits to isolation and locking – if (for example) what you want is
                                                                              the Snapshot statement takes effect. No users are allowed to use the
always the latest price. If the price is currently changing – either up or
                                                                              database while versioning is put into effect.
down – locking would require that the purchaser WAIT for the price
change to complete before selling any more of that item. Only locking
gives you this kind of “isolation.” However, if what you want is the sum of
sales – and what you want is accuracy at a given point in time – without
waiting, then Snapshot Isolation can help you achieve that!

SQL Server 2005 Launch Event Content                                    3                                             Data Platform Track Session 01
Microsoft Confidential – Microsoft Internal Use Only


Now we’re going to RE-EXECUTE the two poorly written transactions. One          15. From the Demo03_SnapshotIsolation project, open and execute
in SQL Server 2000 will cause blocking and the other – in SQL Server                the “Step 1” Query file. (Make sure you don’t have just the
2005 with the Read-Committed with Statement-level Snapshot database                 COMMIT highlights.)
option enabled will not.                                                        16. Switch to the DDM and you can see it’s blocked.
It’s important to realize that if blocking if your problem this can be very     17. From the Demo03_SnapshotIsolation project, open and execute
helpful. However, if blocking is not your problem then adding row-level             the “Step 2” Query file. (Make sure you don’t have just the
versioning may add overhead with no immediate benefit.                              COMMIT highlights.)
                                                                                18. Switch to the DDM and you can see that there’s been no impact
                                                                                    whatsoever!
                                                                                << Pause for applause! >>
OK, a bit of quick cleanup before we return to the slides. First, we’ll close   19. For SQL Server 2000: In the “Step 1” window, highlight the word
out those pending transactions in both SQL Server 2000 and SQL Server               COMMIT (and only that single word) and execute the commit.
2005 and exit out of our scripts…but we’ll stay within SSMS.                    20. Close the “Step 1” Query file.
Finally, we’ll close the DDM…                                                   21. For SQL Server 2000: In the “Step 2” window, highlight the word
                                                                                    COMMIT and execute the commit.
                                                                                22. Close the “Step 2” Query file.
                                                                                23. Close the “Step 4” Query file.
                                                                                24. We have finished using the DDM for these thee demos. Ideally you
                                                                                    should:
                                                                                       a.   Stop the adding of rows to both databases.
                                                                                       b.   Exit the DDM




SQL Server 2005 Launch Event Content                                      4                                           Data Platform Track Session 01

				
DOCUMENT INFO