{"id":658,"date":"2005-09-23T04:40:00","date_gmt":"2005-09-23T04:40:00","guid":{"rendered":"\/blogs\/kimberly\/post\/MSDN-Webcast-QA-A-Primer-to-Proper-SQL-Server-Development-Part-6-of-10.aspx"},"modified":"2013-01-11T23:12:44","modified_gmt":"2013-01-12T07:12:44","slug":"msdn-webcast-qa-a-primer-to-proper-sql-server-development-part-6-of-10","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/msdn-webcast-qa-a-primer-to-proper-sql-server-development-part-6-of-10\/","title":{"rendered":"MSDN Webcast Q&A: A Primer to Proper SQL Server Development, Part 6 of 10"},"content":{"rendered":"

\nEffectively Designing a Scalable and Reliable Database<\/strong><\/span>\n<\/p>\n

\nA Primer to Proper SQL Server Development<\/span><\/em><\/strong><\/span>\n<\/p>\n

\nSQL Server Mixed Workloads, Secondary Databases, Locking and Isolation, Part 6 of 10<\/span><\/strong><\/span>\n<\/p>\n

\n<\/span><\/strong>Presented by<\/span><\/em> Kimberly L. Tripp, SQLskills.com<\/span><\/strong><\/span>\n<\/p>\n

<\/span><\/strong>Q: Can I view a recording of this webcast?<\/strong><\/span>Part 6 can be replayed by clicking here<\/span><\/strong><\/a>.<\/span><\/span><\/span> <\/p>\n

\nQ: Where can we get the demo scripts AND the sample database: Credit? <\/strong><\/span>The demo scripts are in this zip (20050916MSDNDemoScripts.zip (6.11 KB)<\/a>); here in this blog entry. However, at the series completion, I will also create an entry under Past Event Resources<\/span><\/a> for the entire webcast series. <\/span> <\/span>To download the ZIP of the Credit Database Backup click here<\/a>. Once unzipped, restore this backup to SQL Server 2000 or SQL Server 2005. The backup is a SQL Server 2000 backup and can be restored to either version! If restoring to SQL Server 2005, you might want to change the destination for the data and log file as the path will probably be different.<\/span>\n<\/p>\n

<\/span><\/span><\/p>\n

\n<\/span>Q: Where are the links to all prior Webcast Q&As from this series?<\/strong><\/span>\n<\/p>\n

\n

\n\tPart 1: Creating a Recoverable Database
\n\t<\/strong><\/span>For the MSDN Download for Part 1, click here<\/strong><\/span><\/a>.
\n\t<\/span>For the SQLskills Blog Entry for Part 1, click here<\/strong><\/span><\/a>.<\/span>\n\t<\/p>\n

\n\t<\/span>Part 2: Creating a Reliable and Automated Backup Strategy<\/strong>
\n\t<\/span>For the MSDN Download for Part 2, click here<\/strong><\/span><\/a>.
\n\t<\/span>For the SQLskills Blog Entry for Part 2, click here<\/strong><\/span><\/a>.<\/span>\n\t<\/p>\n

\n\t<\/span>Part 3: Designing Tables that Scale, Best Practices in Data Types and Initial Table Structures<\/strong>
\n\t<\/span>For the MSDN Download for Part 3, click here<\/strong><\/span><\/a>.
\n\t<\/span>For the SQLskills Blog Entry for Part 3, click here<\/strong><\/span><\/a>.<\/span>\n\t<\/p>\n

\n\t<\/span>Part 4: SQL Server Indexing Best Practices
\n\t<\/strong><\/span>For the MSDN Download for Part 4, click here<\/strong><\/span><\/a>.
\n\t<\/span>For the SQLskills Blog Entries for Part 4
\n\t<\/span>Resource links blog entry, click here<\/strong><\/span><\/a>.
\n\t<\/span>Q&A blog entry, click here<\/strong><\/span><\/a>.<\/span><\/span><\/span>\n\t<\/p>\n

\n\t<\/span><\/span><\/span>Part 5: SQL Server Index Defrag Best Practices
\n\t<\/strong><\/span><\/span>For the MSDN Download for Part 5, click here<\/span><\/strong><\/a>.
\n\t<\/span><\/span>For the SQLskills Blog <\/span>entry, click here<\/span><\/strong><\/a>.<\/span><\/span><\/span> <\/span>\n\t<\/p>\n<\/blockquote>\n

\nQ: How can I replay previous sessions? I thought we were going to get emails for replaying — but I haven't received any replay emails.<\/strong><\/span> You will receive replay emails ONLY when you register for these sessions through MSDN. We’ve come to find out that there are other ways to register but it’s only through MSDN that we know for sure you will receive the replay information. <\/span>\n<\/p>\n

\n<\/span>Technical Questions<\/strong><\/span>\n<\/p>\n

\nQ: I know you have covered indexes and backups in other webcasts, but here is my question I use heavily temporary tables. My TempDB grows up to 5 GIG. Should I backup or truncate the transaction log in order to bring it back to its normal size? <\/span><\/strong>No, there is no need to specifically maintain the transaction log of the TempDB database. If the transaction log (and subsequently, the database) grows large – there could be multiple reasons for that and instead of thinking in terms of trying to manage the log, I’d look at long running transactions and\/or large transaction. You can use Profiler to help you see long running and\/or large transactions.<\/span><\/span>\n<\/p>\n

\n<\/span>Q: Can I perform a database snapshot to another server? <\/span><\/strong>No, database snapshots must be created on the same server as the database on which the snapshot is being based.<\/span><\/span>\n<\/p>\n

\n<\/span>Q: Can I snapshot by filegroup? <\/span><\/strong>No, however if what you want to do is create a snapshot which does NOT include certain files – you can take those filegroups offline and then create the snapshot. In the snapshot the only file\/filegroups available will be those which were online when the snapshot was created…even if those files\/filegroups are brought online after the snapshot was created.<\/span><\/span>\n<\/p>\n

\n<\/span>Q: Could a reader be blocked on the snapshot DB while SQL updates the changed page? <\/span><\/strong>No. The copy on write mechanism is really a copy before write mechanism and the pages will be copied before the write and essentially before the locks, etc. The only possible “blocking” could be caused by the excess I\/Os that need to be performed. However, the I\/Os are performed only on the FIRST change to the page after the snapshot is created – so it’s minimal!!<\/span><\/span>\n<\/p>\n

\n<\/span>Q: Are DMVs in SQL Server 2005 only?<\/span><\/strong>Yes, DMVs = Dynamic Management Views and these are a feature of SQL Server 2005.<\/span><\/span>\n<\/p>\n

\n<\/span>Q: Is read uncommitted the lowest\/least in terms of data consistency? <\/span><\/strong>Yes, read uncommitted is also known as “dirty read.” A dirty read is a read against an “in-flight” transaction; this transaction could be rolled back. As a result, the query that read that data would be inaccurate.<\/span><\/span>\n<\/p>\n

\n<\/span>Q: What is the effect of versioning on fragmentation and performance (I'm assuming I create a split of the page is full)? <\/span><\/strong>Actually, I’m not sure I’m following this one… But – I think I can answer it by just giving you some insight into how things work. Versioning – in terms of the data overhead added to the data row – does add a 14 byte value to help store the offset. This overhead is added ONLY once, to each row, after one of the snapshot isolation options is turned on (either or both – the READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION). When this 14 byte value is added to each of the rows, the additional 14-bytes might cause the page to split. Again, this is only a one-time addition. The trick to optimizing this structural change is to change the database option and then rebuild your indexes. This will make the data contiguous and versioning will have no additional affects on the data row.<\/span> <\/span>Now, if what you were thinking is that the versions were stored in the data row – then this is NOT the case. The version store comes from the TempDB and as a result, there is no additional overhead (over the 14-bytes) needed within the data row.<\/span><\/span>\n<\/p>\n

\n<\/span>Q: What if we're not using transactions? Will repeatable read still lock the table during the read (particularly if the select is long)? <\/span><\/strong>Repeatable reads locks – and holds – the resources as they are read. So, YES, in the case of a select statement, you will acquire and hold the read locks for the life of the transaction.<\/span><\/span>\n<\/p>\n

\n<\/span>Q: How much additional overhead does versioning require from the SQL Server engine? <\/span><\/strong>Most of the overhead comes from TempDB but there’s also a bit of overhead in looking up the version. There are no direct numbers associated with the overhead but in a lot of cases you should think in terms of roughly 10% additional costs for your transaction… So, I guess the best point is that you will have slower overall performance when implementing row version; however, you might solve a lot of your blocking problems. Slower but not blocked is better than not running at all – even when it does run at all quickly. <\/span>J<\/span><\/span>  <\/span>In all seriousness though, if blocking is NOT your primary problem, you will add overhead without a possible benefit.<\/span><\/span>\n<\/p>\n

\n<\/span>Q: So, is it the new<\/em> transaction data or the old<\/em> transaction data held in the snapshot store (seems like it might be different for statement vs. transaction level snapshotting)? <\/span><\/strong>It’s always the BEFORE image. The general process of the write is called “copy on write” but I think of it better as copy before<\/u> write.<\/span><\/span>\n<\/p>\n

\n<\/span>Q: Can we optimize the snapshot store (different physical device, file group(s), etc.)? <\/span><\/strong>No. However, you should look at optimizing TempDB. There are multiple things that you might want to consider. I discuss those thing in this blog entry here<\/a>.<\/span><\/span>\n<\/p>\n

\n<\/span>Q: If I don’t need locking why shouldn’t I use read-uncommitted? <\/span><\/strong>Hmm, you can… you just need to be aware of the fact that the data is “dirty” and is not guaranteed to persist.<\/span><\/span>\n<\/p>\n

\n<\/span>Q: Where does SQL store all the row versions (with snapshot isolation turned on)? <\/span><\/strong>The version store is in TempDB.<\/span><\/span>\n<\/p>\n

\n<\/span>Q: How do I view all of the Report options from the summary page? I am looking at Adventureworks (compatibility level = 90), but all that I see is the General report. <\/span><\/strong>Ah, ha! The new summary windows were added to after the beta II April CTP. So, what this tells me is that you’re running a build lower than 9.00.1187.07. At this point, I’d go for the September CTP which is build 9.001314.06.<\/span><\/span>\n<\/p>\n

\n<\/span>Q: This question is from previous webcast… Is there anything new with SQL 2005 that does datetime support data types? Time datatype or Date datatype only? <\/span><\/strong>No, SQL Server 2005 only includes the datetime datatype for date\/time data. However, by using “custom types” you can create your own types which are date only or time only (just for one example). There were separate SQLCLR types of date only and time only in SQL Server 2005; however these were non-native types and subsequently removed. Instead, they will be shipped as examples in a resource kit which ships after RTM.<\/span><\/span>\n<\/p>\n

\n<\/span>Q: Kimberly, the downloadable zip file from your blog for at least the first session will only unzip to a "C:" drive (which my system doesn't have ;^) Could you please re-zip it to allow election of the drive to which it should unzip? <\/span><\/strong><\/span>This one still perplexes me. I didn’t set any options that would restrict this… <\/span><\/span><\/span> <\/span>\n<\/p>\n

For the next session, we’re going to cover how SQL Server keeps plans, where you can look to see what's in cache AND how you can know better if the stored procedure's plan should be kept…or not? If you’re interested in hearing more – here’s the registration link:<\/span> <\/p>\n

\nMSDN Webcast: A Primer to Proper SQL Server Development (Part 7 of 10): Understanding Plan Caching and Optimizing Procedure Performance<\/a><\/strong><\/span>\n<\/p>\n

 <\/span>See you on Friday!<\/span> <\/p>\n

\nkt<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

Effectively Designing a Scalable and Reliable Database A Primer to Proper SQL Server Development SQL Server Mixed Workloads, Secondary Databases, Locking and Isolation, Part 6 of 10 Presented by Kimberly L. Tripp, SQLskills.com Q: Can I view a recording of this webcast?Part 6 can be replayed by clicking here. Q: Where can we get the demo scripts […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27,47,58,65],"tags":[],"class_list":["post-658","post","type-post","status-publish","format-standard","hentry","category-events","category-msdn-webcasts","category-resources","category-sql-server-2005"],"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/658","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/comments?post=658"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/658\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/media?parent=658"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/categories?post=658"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/tags?post=658"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}