Archive for December, 2012

Year 2012–a quick recap December 31st, 2012

Vinod Kumar

Looking back

It has been an exciting long year with loads of learning and sharing. I thought to share what is in my mind in general as we close this year. In this blog post I plan to share the various things we have done in this blog. I am amazed of the fact that we took some new initiatives and I am glad to see some great responses from each readers like you. This is a moment to say thanks to each and every one out here reading this post. I get inspired because of your positive comments. So please keep showing the love you have given me till date in the years to come too.

SQL Saturday 116–Thanks ALL

We started the year with the first ever SQL Saturday in India at our Bangalore UG. This was something we planned for more than 3+ months and was glad to execute this first thing in Year 2012. I am sure you are about to ask me when is the next one. We surely are thinking about this with various stakeholders. We don’t have concrete plans yet, but something is on the cards and minds of people – trust me :).

Management topics

I started writing regularly on Management related topics and this year I have started sharing much more openly on some of the views I carry strongly. This was a bold but a cautious move personally because I was not sure how my readers will react. I am happy with the results and I am glad you folks liked these topics and you have supported me much more openly via your constructive comments. I think this trend will continue even in year 2013 and I plan to write many more topics that I feel are worth a thought. I have consolidated the some of these posts in my Popular Posts section.

SQL Server 2012 Release

This was a the simplest thing and the easiest thing when it comes to writing blogs on a technical topic. This release meant I had loads of fun exploring new things and then sharing with readers. I think we shared some really good content on some really cool new features this year. As I explore more of SQL 2012, they will surely get featured here in this blog. Trust me :). Some of the posts for reference are:

Upgrading to SQL Server 2012

Contained Databases inside SQL Server 2012

Page Restore Dialog in SQL Server 2012

SQL Server 2012: THROW your errors

SQL Server 2012: ColumnStore Characteristics

SQL Server 2012: Offline Logs viewing

Monitor High CPU with Extended Events in SQL Server 2012

SQL Server 2012: Using SEQUENCE

SQL Server 2012: Finding Sequence of backup taken

SQL Server 2012: Offline Documentation

SQL Server: Database Recovery Advisor

SQL Server 2012 : SSMS Tips

SQL Server 2012 – Too many VLF and Performance

TechEd India 2012

It has been now 9 years that I have been part of TechEd in India and I eagerly look forward to TechEd 2013 as it would mark my 10 years association with this brand. Over the years I have been very happy that deep technical content have been discussed and we have been able to excite the crowd year-after-year with new content. So here are the posts around TechEd 2012.

TechEd India 2012: Online Indexing Enhancements

TechEd India 2012: SQL Content Extravaganza

TechEd 2012: MTC India @ TechEd

TechEd India 2012: SQL Server Internals

TechEd India 2012: Architecture Track

Picture Posts

This was a humble try in doing something different. I didn’t know I will be able to take it longer than I expected. But the fact remains, I have loads of ideas that you have provided that are worth mentioning via a picture. These are mini-posts in my opinion but driving some powerful message. It is tough to create a crisp compelling concept via pictures. If you haven’t seen these posts, well feel free to check my Picture Post section for past posts. Would love to hear your feedbacks and suggestions and I will try to incorporate the same. I already have more than 15+ posts done on various topics.

SQL Server Basics Topics

Most of the times I love to explore to what SQL Server has to give me. And many of my posts in the past 1 year have been on explaining some core concept in an detailed way when it comes to SQL Server 2012. Having said that, I have been quizzed by many about the basics and this triggered the concept of “Basics series”. In the last one quarter, I have seen the most viewed topics have been my Basics series. I am happy to write many more in the coming year and look forward for your support.

SQL Server: Database Basics

SQL Server: Database Page Basics

Concurrency basics

SQL Server: Locking basics

SQL Server: Storage Engine Basics

SQL Server: Transaction Logs and Recovery

SQL Server: Basics of Database Snapshot

SQL Server: TempDB Basics

Bangalore UG started

One of the biggest announcement which is worth mentioning over this blog is that, we started the Bangalore SQL Server UG officially and we have had two UG meets so far with great turn over. If you are interested in knowing about our group – Join SQLBangalore over facebook. There is so much to share and learn and this group has some finest of people from GTSC, PFE, MCS and community. Thanks again for supporting, contributing, sharing and engaging in the groups page. Join the group, I can assure you loads of learning for sure.

Personal SLA

What has been my SLA? Well, it was not a new year resolution but more of a self control to see if I can write 2 blog post every week in a consistent way. Must thank Pinal for the continuous source of inspiration even till date. I think since April atleast 90% of the time I have written 8 or more blog posts every month till date. In order to keep myself busy with topics, I have used various techniques as mentioned above. If you have any specific series that you want me to take a stab – well, drop your comments and I will try to write about them.

Finally, thanks for reading this far !!! Feel free to subscribe to my blog via email, and thanks to the 200+ people who have already subscribed. Thanks to all my followers and readers via RSS and Twitter. Last but not the least – I take this opportunity to wish each and every one a fun filled safe happy New Year 2013.

Continue reading...


 

Work Smart Guides: Getting Started Series December 24th, 2012

Vinod Kumar

As part of my twitter stream (@vinodk_sql), I post a lot of links and resources that I come across. These are mostly from MSDN or from other sources that I find as interesting content. Having said that, there is no one place we can go and find all the information over the internet for getting started series. I found the Work Smart series has a number of interesting documents that are worth a look if you are a beginner or want to start learning a technology. Whenever there is a new release, I look forward for these papers because they are quick to read and give me a great idea to start learning the concepts in detail later. In my experience of more than a decade of IT, I have come to a conclusion that there is no substitute for self learning and exploration. Sky is your limit and your imagination is your limitation to how far you can learn.

Desktop

Exploring Windows 8: This guide provides a high-level overview of the exciting new features in the Windows 8 user interface.

Windows 8 Shortcut Keys : This guide provides a quick reference to the shortcut keys in the Windows 8 Start screen.

Windows 7 New Features :This guide describes some of the new feature available now in Windows 7.

Windows 7 on a Slate Device : Learn how to use Windows 7 features on a slate device, using touch-friendly features in IE9, the tablet input panel, speech inputs and more.

Everyday Work with Windows Vista Get Started : Windows Vista offers improved search capabilities, easier ways to sort and organize files, and interface enhancements that help manage applications and information.

Windows Vista and Microsoft Office Work Smarter Checklist : Streamline your work with powerful new features in Windows Vista, the 2007 Microsoft Office system, and Exchange Server 2007.

Using Windows Internet Explorer 10 : Internet Explorer 10, included in Windows 8, offers two browser experiences, a touch-optimized, modern-style experience and a classic desktop, web-browsing experience. This guide provides detailed instructions for using the Internet Explorer 10 modern experience, provides useful tips for working across both experiences, and discusses security and privacy.

Office

Microsoft Office 2010 User Interface Get Started : Use this guide to learn more about Office 2010 user interface new features.

Groove 2007 Get Started : Groove enables teams of people to work together securely over the Internet or a corporate network as if they were in the same physical location. Groove is good for teams that don’t work in the same location, time zone, or company. Groove manages changes to shared files even when you are offline.

Groove 2007 Learn More : With Groove 2007, small teams can work together securely over the Internet or corporate network without sharing the same location, time zone, or company.

Microsoft Excel 2010 Get Started : Use this guide to learn about some of the new and improved features which are available in Microsoft Excel 2010.

Microsoft PowerPoint 2010 Get Started : Use this guide to learn about the new features of Microsoft PowerPoint 2010.

Microsoft Word 2010 Get Started : Use this guide to learn about some of the new and improved features which are available in Microsoft Word 2010.

OneNote Overview : You can use this guide to learn about the features in Microsoft OneNote 2010, and how to use them.

Outlook 2010 Calendar Scenarios Get Started : Microsoft Outlook 2010 makes setting appointments, sharing your calendar availability, and managing your schedule even easier.

Outlook 2010 Get Started : Use Microsoft Outlook 2010 to manage your e-mail messages. You can use this guide to learn about some of the new features in Outlook 2010.

Outlook and SharePoint Integration Get Started : Understand how you can use Outlook to work directly on SharePoint resources.

Outlook and SharePoint Integration Overview : Understand how you can use Outlook to work directly on SharePoint resources.

Outlook E-Mail Signatures Get Started : Office Outlook 2007 e-mail signatures enable you to create a brief description of yourself for the benefit of those you correspond with.

Outlook Web App Get Started : Microsoft Outlook Web App (OWA) provides secure access to your Outlook email, calendar, contacts, tasks, and Global Address List – via the Internet. This guide explains how to log on to OWA and use OWA features.

Microsoft Office Tasks Get Started : Use Outlook Tasks to organize your time and your work. Work across applications with Tasks integrated across Outlook, SharePoint, and OneNote.

Sharepoint

Collaborating with SharePoint Overview : Collaborate effectively with SharePoint sites and features.

Collaborating with SharePoint Server 2010 Get Started : Get started on collaborating effectively with SharePoint sites and features.

Customizing SharePoint Sites Get Started : Customize SharePoint sites with Web parts. Display libraries, documents, RSS feeds, and customize the left navigation menu (Quick Launch).

Office SharePoint Workspace Get Started : Microsoft® SharePoint® Workspace 2010 is a client application that offers a seamless experience when you work online and offline with document libraries and lists on Microsoft SharePoint Server 2010 and Microsoft SharePoint Foundation 2010. Additionally, it conducts real-time, automatic, transparent synchronization of that content when you connect to the SharePoint server.

Messaging / Communication

E-Mail Basics: Managing E-Mail Get Started : This guide describes several tips and tools that will help you take charge of your email.

Successful Meetings with Lync Get Started : Traditional meetings, large and small, can be a challenge when people are spread out over long distances and include internal and external attendees. Communication technology can overcome the physical boundaries by allowing people to attend from any location with Internet or phone access. But that alone is not enough to make a meeting successful. By understanding the best practices for conducting meetings and using the right communication technology from Microsoft Lync 2010, you are on your way to conducting successful meetings.

Security

Protect Data with BitLocker Get Started : Microsoft BitLocker Drive Encryption technology uses the strongest publicly available encryption to protect your computer’s data, and prevents others from accessing your disk drives without authorization. Additionally, the BitLocker To Go feature prevents unauthorized data access on your portable storage devices, including Universal Serial Bus (USB) flash drives, also known as “thumb drives.”

Protecting Data with Windows 8 BitLocker : This guide discusses how to use Microsoft BitLocker Drive Encryption technology to protect your computer’s data, and prevents others from accessing your disk drives without authorization.

Securing Business Information : At work, all forms of information, including ideas and concepts, have potential business value. It is everyone’s responsibility to protect this information from unauthorized disclosure. The Securing Business Information Work Smart Guide details how to classify business information and how to choose the correct technology to protect this information before you transmit, share, store, or destroy it.

Securing Your Computer
Every computer at Microsoft is a gateway to the corporate network. Employees need to guard these gateways against intruders who might try to gain access to corporate resources. By adhering to Information Security Standards and following the best practices in this guide, you can help protect Microsoft against potential threats.

Securing Your Windows Phone Get Started : Windows Phones are powerful tools that enable you to work remotely by synchronizing to your corporate email and the corporate network. More and more employees work remotely and use Windows Phones and other mobile devices. However, protecting them is a challenge, because they are small and easily lost or stolen. This guide provides information on securing your Windows Phone.

Others

DirectAccess is Key to Microsoft IT Remote Access Strategy : Microsoft IT has leveraged DirectAccess as its future in preferred remote access technology. DirectAccess is a new feature in Windows 7 and Window Server 2008 R2. With DirectAccess, users are always connected to the corporate network whenever they are connected to the Internet, and without having to use a VPN. This seamless and ubiquitous access method has resulted both significant end user and business benefits for Microsoft.

Environmental Sustainability Get Started : Environmentally responsible IT business strategies continue to gain momentum across the industry. Microsoft IT has been addressing Environmental Sustainability issues for years but now has a single point of collaboration around these efforts in order to increase awareness and reduce the company’s overall carbon footprint.

System Center 2012 Configuration Manager Get Started : System Center 2012 Configuration Manager puts the management of your computer in your hands. You can use Configuration Manager to quickly and easily identify and install applications that are relevant to you based on your role, language, and location. You can also use Configuration Manager to manage the timing of installations and upgrades to avoid disruptions to your work.

Transfer Files and Settings Get Started : Use this guide for instructions on how you can transfer your files and settings from your old computer that is running Windows XP or Windows Vista using the Windows Easy Transfer tool. Topics include: Install Windows Easy Transfer for Windows XP, Transfer Files and Settings Between Computers Over a Network, Transfer Files and Settings Using a Location on a Network, Transfer Files and Settings Using Removable Media, and Recycle Your Old Computer.

Using Microsoft Forefront Identity Manager Get Started : Microsoft Forefront Identity Manager (FIM) 2010 provides an integrated and comprehensive self-service group management and password reset solution. FIM 2010 simplifies the identity management landscape and enables group management through a Microsoft Office SharePoint Server 2007 portal and a Microsoft Office Outlook 2007 add-in.

Using Windows Phone 7.5 Get Started : Microsoft IT deployed Windows Phone 7 companywide to over 50,000 employees. Cross-company, Cross-team collaboration, planning, and focused marketing was the key to deploying and supporting Windows Phones successfully to employees around the world.

Backing Up Your Data and Settings : This guide provides information on how to set up and use the new File History feature in Windows 8 or the IntelliMirror Redirection Service to back up your data.

Connect a Microsoft Account (Windows Live ID) to Your Domain Account : This guide discusses how, with Windows 8, you can connect your Microsoft account to your corporate domain account. By connecting the accounts you can continue to sign in to your office PC with your domain (network) credentials, sync your Windows personalization settings across multiple PCs, and use a single sign-on for Windows Live services.

Connecting Remotely Using Windows 8 DirectAccess : This guide discusses how to connect remotely using Windows 8 DirectAccess, which enables you to seamlessly connect to the corporate network from any Internet-equipped remote location without having to establish a Virtual Private Network (VPN) connection.

Hope you found the links and resources useful. I am sure I might have missed couple of links for sure but keep an eye for these series on the Microsoft site.

Continue reading...


 

Picture Post: Changing Jobs Lifecycle December 20th, 2012

Vinod Kumar

It is not the strongest of the species that survive, nor the most intelligent, but the one most responsive to change. – Charles Darwin

This is an interesting topic and above all a challenging one to write. There are various patterns to how we look at our jobs and this is one of the most common patterns in the ITES industry. I am sure many will be able to relate this to your real life or to someone you know. Change is inevitable, how often one changes and how one is resistible to change is different between different people. What is your reasons for change and what some of the motivations when it comes to change in your jobs? Do leave a comment.

Change is hard because people overestimate the value of what they have and underestimate the value of what they may gain by giving that up – James Belasco and Ralph Stayer

Work harder on yourself than you do on your job. – Jim Rohn quotes

I am surely not of the school of thought that one has to change for the heck of changing. But there has to be some resistance that has reached the high pressure that one needs to release this pressure by doing something completely different.

The math can be very simple. If your # of hops are more than half the total # of years in service – well you surely need to learn to be patient and you need to learn “how to pick the right job”. The reason to have a different ratio can mean totally different other things and maybe it will be a picture post for the future.

The best way to appreciate your job is to imagine yourself without one – Oscar Wilde

Continue reading...


 

SQL Server: Database Recovery Advisor December 19th, 2012

Vinod Kumar

We have talked about two concepts earlier in this blog when it comes to backup in the recent past. First is around Page Restore Dialog and other is around how to find the Sequence of backup taken. Both these are very interesting and very important concept in my opinion and a frequently asked question. In this blog post, we will discuss the concept of Database Recovery Advisor (DRA) of SQL Server 2012. DRA is a new SQL Server 2012 feature to make it easier for SQL Server Management Studio (SSMS) users to do point-in-time database restore and Database Page restores. Although this feature is called Database Recovery Advisor, the end-user will only see these improvements via the Restore Database dialog and new Page Restore dialog. In DRA the users will be able to figure out which data pages are corrupt directly from the dialog and will be able to do Page Restores from the backups and this has been explained in our Page Restore Dialog post mentioned earlier.

What has DRA given us in the long term with SQL Server 2012.

  1. Redesigned the Database Restore Dialog in SSMS to support consistent restore plan creation and verification.
  2. Backup History Time-line dialog, this visually shows the backups on a time-line and lets user select a possible point-in-time to restore.
  3. Page Restore dialog in SSMS.
  4. Have support for these restore plan creation and verification even in SMO.
  5. Enabling the SSMS restore dialog to generate automated restore plan for all the scenarios.
  6. Show the Database backup history when exists in the msdb in the restore.
  7. When the msdb does not have the backup history of the Database, it needs to be constructed from the backup header info when pointed.
  8. Feature allows users to check the integrity of the backup files before restoring directly from the Restore Dialog.
  9. Allowing the user to restore to the point-in-time, even if it in the tail of the log, by automatically taking a Tail-Log backup.
  10. Page Restore Dialog allows the user to find out the corrupt pages in the Database.
  11. Page Restore Dialog automatically generate Restore Plan for Page-Restore from the backup history of the database.

Restore Correctness

Correctness of the Restore Plan is checked every time it is modified. If the sequence of the backups is not correct. Error message will be shown in the top message bar. For example:

The page header will give some interesting warnings making the dialog really complete and makes sure the DBA doesn’t miss a step.

  1. No Full backup Selected.
  2. Differential backup not compatible with the full backup.
  3. Some of the T-Log is not compatible with the Full or Diff backup set selected.
  4. The T-Log backup sequence is broken.

These some of the most common warnings that come up !!!

The checkboxes in the grid lets users exclude some backup sets from the Restore Plan as required. The Dialog handles various scenarios intelligently: for example, if a backup set is excluded from the plan, all the backup sets that are dependent on it will be unselected. The selection of the backup sets in the grid is always consistent to be restored.

Verify Backup Media: Checks the integrity of the selected backup sets. This will call RESTORE VERIFYONLY on the selected backup sets. This can be a long running operation, and its progress can be tracked and cancelled by the Progress Monitor on the Dialog Framework. The top information bar also shows the progress of the operation. We can Stop it too from the top bar.

image

RESTORE VERIFYONLY performs the following checks:

  1. Makes sure we are able to read all the backup set and all volumes.
  2. Some header fields of database pages, such as the page ID are accessible.
  3. Checks for Checksum correctness (if present on the media).
  4. Checks for sufficient space on destination devices.

Recovery time Range – Timeline Dialog

In the above figure we can see the timeline button and that opens up a nice dialog like below. This is really a powerful dialog that cannot be missed. This backup/restore functionality provides us the ability to restore back the state of the database to a previous point-in-time.

image

Last Backup taken: This selects the time of the last backup taken for that database that can be restored. (Selecting this disables the slider in the timeline.) This is the default setting.

Specific date and time: Allows the user to select a point-in-time in the timeline to restore the database to. (Selecting this enables the slider in the timeline.)

Interval: This combo box lets you select the span of the view in the timeline.
  1. Hour
  2. 6 Hour
  3. Day (Default)
  4. Week
Graphical Timeline: This timeline visually represents the backups of the database.
  1. The red line represents the selected point-in-time, this red-line will not go outside the feasible time span in which the database can be restored. Default location is the end of the last feasible restore point.
  2. The slider lets the user select a point-in-time restore location by moving the red line.
  3. The left-right button lets the user navigate on the timeline.
  4. Markers:
  1. The Markers represent the Full (bigger) and the Diff (smaller) backups.
  2. The green bar represents the T-Log backup coverage, while the light green bar represents the tail of the log, which has not been backed up.

Scripting Options

The best strategy to learn is always using the “Script to” option. There are four scripting options:

  1. Send RESTORE DATABASE script to a "New Query Editor Windows".
  2. Send to a T-SQL File.
  3. Copy text to Clipboard
  4. Create an Agent Job that can be run later.

We will highly recommend to get it to a T-SQL file because this can we can learn from what script SQL spits out and we can use the same script for later use if needbe.

Limitation of DRA

  1. Support for File and FileGroup Restore is not supported and there is a separate dialog to do that, but that will not have Recovery Advisor features.
  2. DRA will not support the recovery of system databases.
  3. DRA will not support DBCC Repairs.
  4. DRA does not support piecemeal restores: Files and Filegroups Restore dialog should be used for that.
  5. DRA will not support Page restores of Database Boot pages, PSF, GAM, SGAM etc.

As we described in the previous post, the Page Restore Dialog can be read from that post. I highly recommend you to read that post too.

Continue reading...


 

SQL Server: Storage Engine Basics December 12th, 2012

Vinod Kumar

In by day-job I have a need to guide customers with specific deployment scenario’s on SQL Server. Often I end up talking about the storage, transaction engine and deployment options from SAN, DAS etc. This conversation can get really complex and many times we have a need to educate customers on using one over the other. It is a tough call because there are specific features to discuss in each scenario, there is a budget constraint of customer and the environment SQL Server is running and this involves multiple teams. From SQL Developers, SQL DBAs, Network Admins, Performance testing teams and System Admins all these can be actually different functions inside an given organization.

Storage is typical and sometimes tough given the number of parameters to work with. Let me give some of the high-level considerations when on this point.

  1. When we are working with databases a special eye needs to be put for input/output (I/O) load of the application. The I/O characteristics is a function of business requirements and and components enabled for SQL Server. Typical questions to ask for your I/O characteristics are: 
    1. How are the read versus write ratio for the database?
    2. Has performance counters monitored for the typical I/O volume (I/O per second) on the system for a window of usage? 
    3. Based on the usage patterns, can we quantify the I/O done sequential and how much is random?
  2. Has the business called out the SLA and RTO requirement to narrow down the availability and performance needs for the database system.
  3. Based on 1 and 2, has a capacity planning done to determine the hardware required to support the business. 
  4. Has the SQL Server been configured to make best use of the hardware in step 3.
  5. Is proper monitoring in place to track the performance of the database while the workload changes over a period of time.

The whitepapers on database architecture and storage is long and beyond the scope of this blog. See Microsoft SQL Server Storage Top 10 Best Practices and Physical Database Storage Design for more detailed recommendations from the SQL Server team.

Since this is in continuation to the other topics on basics, a lot has been omitted because they are covered in other locations. Also make sure to read SQL Server: Database Page Basics, SQL Server: Database Basics, SQL Server: Locking basics, SQL Server: TempDB Basics and SQL Server: Transaction Logs and Recovery.

SQL Server – Access Methods

  1. SQL doesn’t directly retrieve from disk, it makes requests to buffer manager which serves up the page in cache before rendering out.
  2. When scan starts, SQL Server can use look-ahead mechanisms to qualify rows and index entries on a page. The retrieving of rows that meet specified criteria is known as a qualified retrieval.
  3. Basics of Row and Index Operations:
    1. Each component maintains its respective on-disk data structures – rows of data or B-Tree indexes.
    2. Understand and manipulate information on data and index pages
    3. Row operation code retrieves, modifies and performs operations on individual rows
    4. Special operations are needed to handle LOB data (Large Object)
    5. Index operation code maintains and supports searches on B-Tree which are used for SQL Server indexes.
  4. Basics of Page Allocation Operations:
    1. The allocation operations code manages a collection of pages for each database and keeps track of which pages in the db have already been used, for what purpose and how much space is available on each page.
    2. Each database is a collection of 8 KB pages spread across one more physical files.
    3. 13 types of disk pages, specifics are explained in the Page basics blog:
      1. Data is stored on data or LOB pages
      2. Index rows are stored on index pages
      3. Page Free Space (PFS) pages keep track of which pages in a db are available to hold new data
      4. Global Allocation Map (GAM), Shared Global Allocation Map (SGAM), Index Allocation Map (IAM) keep track of other pages
      5. Bulk Changed Map (BCM) and Differential Changed Maps (DCM) are used to make backup and recovery more efficient
      6. Rest of the page types are used for logging and recovery
  5. Versioning Operations, also look at SQL Server: TempDB Basics for few more details on versioning:
    1. Manages access to the version store
    2. Row versioning allows SQL Server to maintain older versions of changed rows

Database Transaction Services

  1. Provides support for Atomicity, Consistency, Isolation and Durability
  2. Write-ahead logging ensures that the record of each transaction’s changes is captured on disk in the transaction log before a transaction is acknowledged as committed.
  3. Log records are always written to disk before the data pages where changes were made are actually written.
  4. Writes to the transaction log are synchronous while writes to data pages can be asynchronous.
  5. Allows for transaction that cross databases within the same SQL Server Instance.
  6. For cross-instance transactions, it coordinates with Microsoft Distributed Transaction Coordinator (MS DTC). Use it with utmost caution and understand the overheads of the two phased commit here.
  7. Also transaction services coordinates locking to provide Isolation.
  8. SQL Server supports two concurrency models
    1. Optimistic Concurrency – provides consistent data by keeping older versions of rows with committed values in version store. Readers do not block writers and writers do not block readers. Writers do block writers
    2. Pessimistic Concurrency – guarantees correctness and consistency by locking data so it cannot be changed
  9. SQL Server has five isolation levels:
    1. Read Uncommitted [pessimistic]
    2. Repeatable Read [pessimistic]
    3. Serializable [pessimistic]
    4. Snapshot [optimistic]
    5. Read Committed [optimistic/pessimistic – depends on database setting]
  10. Behavior of transactions depends on the isolation level and concurrency model one is working with

Database Locking Operations

  1. Acquires and releases various types of locks
    1. Share locks – reading
    2. Exclusive locks – writing
    3. Intent locks – taken at a higher granularity to signal a potential "plan" to perform some operation
    4. Extent locks – for space allocation
  2. Manages compatibility between the lock types, resolves deadlocks and escalates locks.
  3. Controls table, page, and row locks as well as system data locks.
  4. Locking is the best way SQL Server can bring a balance to consistency and concurrency and it is SQL Server’s way of implementing Isolation Levels discussed above. Read the other post around “SQL Server: Locking basics” discussed in detail.

Database File Sizes

  1. An ideal recommendation would be to have data files of equal size – SQL Server uses a proportional fill algorithm that favors allocations in files with more free space.
  2. Pre-size data and log files well ahead of time and plan for the growth.
  3. Do not rely on AUTOGROW, instead manage the growth of these files manually. You may leave AUTOGROW ON for safety reasons, but you should proactively manage the growth of the data files.
  4. Configure an alerting mechanism to send emails or log information of available free space. Use other monitoring mechanism to send emails if required.
  5. Also have alert mechanism to look at AUTOGROWTH happening in the system for both Application databases and TempDB.

Disk Topology and SAN interfaces

Small Computer System Interface (SCSI)

  1. Supports forcing data to be written to disk, improving recoverability.
  2. SCSI with Tagged Command Queuing (TCQ) supports multiple I/O requests.
  3. Supports hot-swapping.
  4. SCSI can have up to 15 drives per channel.
  5. Less restrictive on physical cable length.
  6. Overloading the channels increases the chance of reaching the transfer rate limit.

Integrated Device Electronics (IDE)

  1. Supports hot-swapping.
  2. IDE has high transfer rates only if there is one drive attached per channel.
  3. Typically greater capacity than SCSI.
  4. Typically cheaper per GB than SCSI drives.
  5. Can only handle one outstanding I/O request per channel.

Serial Advanced Technology Attachment (SATA)

  1. SCSI with TCQ supports multiple I/O requests.
  2. Supports hot-swapping.
  3. Most are explicitly designed to support only one drive per channel; however, multiple SATA channels of 2 to 12+ on interface cards are also available.
  4. Typically greater capacity than SCSI.
  5. Typically cheaper per GB than SCSI drives.

Serial-attached SCSI (SAS)

  1. Very fast. Supports SCSI protocol.
  2. Allows for a larger number of disks than SCSI.
  3. Applicable to direct-attached storage (DAS) only.
  4. Replacement technology for parallel SCSI. Backward compatible with SATA drives.

SAN

  1. Can serve multiple servers.
  2. No limitations on the number of disks that can be accessible.
  3. Easier to install additional servers. Easier to manage many servers.
  4. Easier to reallocate disk storage between servers.
  5. Maintenance costs tend to be lower than DAS.

DAS

  1. Greater maximum bandwidth.
  2. Easier to manage for a smaller number of servers.
  3. Initial overhead costs are lower than SAN.
  4. Deployed per server directly attached.
  5. The number of disks is limited by the number of slots in the server and the type of interface used.

For both DAS and SAN, the following categories of performance should be measured:

  1. I/O per second
  2. Megabytes per second
  3. Latency

Performance of both DAS and SAN environments is affected by so many variables that simple recommendations are not possible. Examples of variables include drivers, configuration, underlying and supporting foundational technologies and host bus adapters (HBAs).

Fiber-Channel-switched fabric may be beneficial for SAN environments, because Fiber Channels can provide multiple links through the fabric, and can thereby enable I/O path parallelism so that the SAN can process I/O requests more efficiently.

Where to start?

Generally, when prioritizing data among faster disks for SQL Server databases, use the following ranking, these are generic and need evaluation on a case-to-case basis:

  1. Start with Tempdb data and the tempdb transaction logs
  2. Database transaction log files
  3. Search database
  4. Database data files
  5. In a heavily read-oriented portal site, prioritize data over logs.

Don’t overlook HBA configuration while deployment

  1. Use up-to-date HBA drivers as recommended by the vendor.
  2. Utilize storage vendor specific drivers from the HBA manufactures website.
  3. Tune your HBA driver settings as needed by the databases and access patterns.
  4. Ensure that the storage array firmware is up to the latest recommended level.
  5. Use multipath software to achieve balancing across HBA’s and LUN’s and ensure this is functioning properly.

Well, some of the conversations I have with customers go as long as a day on these topic and I am sure this is not exhaustive. So please feel free to drop in comments of areas that I have missed out here for the benefit of others. Thanks again for reading this far !!!

Continue reading...