VIEWS: 18 PAGES: 6 POSTED ON: 7/4/2011
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
"3-Snapshot Isolation Demo Script"