The developer of the EZManage SQL software, DBA Services, announced the acquisition of DBSophic. The company notes that the acquisition will expand its solutions on the applicative environment of Microsoft SQL.

DBSophic is an innovative developer of performance management products, named Qure Optimizer and Cure Analyzer, which are dedicated to systems based on MS SQL platform and suffer from significant performance problems. The Qure Optimizer analyzes server workloads, provides particularized recommendations (indexing scheme, SQL rewrites, schema changes and more) and may also apply the implementation.

Qure Optimizer, the leading workload tuning solution for SQL Server, provides a performance improvement in a short time and it is a complementary product for a wide range of solutions, DBA Services offers under the brand EZManage SQL. Both company's client's operations are located mostly in the US or Europe and the combined company will offer a wider range of solutions.

DBSophic's customers include Easy Forex, BMW, Nestl?, Siemens and the US air force. Aviv Madmon, CEO of DBA Services, stated that "the acquisition is expected to triple the combined company's sales during the near future and also to provide more innovative solutions in the future".

Although the “Join-a-day” series focuses mostly on the logical aspects of joins, under the covers there is another aspect which is the physical operators that SQL Server chooses to implement your ‘logical’ joins. As part of the optimization process, the SQL Server Optimizer uses statistical information to try and evaluate which physical operator will be the optimal one to use in regards to your join and the rest of the query plan. If you are unfamiliar with the physical join operators, I recommend you read our free E-book which explains in detail how each operator works and what are the main considerations of the SQL Server optimizer when choosing one operator over another. For you convenience, here is the summary table that sums the main characteristics of each physical join operator: 

When I started to study database design, this was one of the first hot controversies I encountered. If you Google the phrase “natural vs. artificial keys,” you’ll come up with more than 150 million results, including endless debates, numerous articles, blog posts with passionate replies, long theoretical and practical arguments, and even the occasional profanity."

The above paragraph opened my chapter in the MVP Deep Dives 2 book. If you don't own this book yet, don't wait... But if you are interested in hearing some of my thoughts on the issue, you are welcome to watch a recording of a session I delivered last week on the subject as part of PASS Data Architecture Virtual Chapter. You can find the recording and links to the materials here.

Note that the Live Meeting recording works on IE only (at least on my PC...).

Who can tell? Perhaps things won’t look as stark afterward. Let me know what you think...

For a full list of features, see the previous blog post or have fun reading the very detailed user guide.

If reading a 60 page guide is not your cup of tea, and you would rather sit back, relax and sip your tea, then let me do the talking. I invite you to watch our in-depth video tutorial of using Qure Analyzer. Check out the two 15-minute videos I've recorded for you. You can find these videos and other resources on the Qure Analyzer resources page.

When I bought my desktop PC last year, I purchased an SSD drive to be used as the system drive (C:\). At the time, the prices of SSD devices was pretty steep so although I had some doubts, I convinced myself that a 40GB drive should be more than enough to accommodate everything I need. Big mistake #1. As it turned out, only the I-tunes backups of my iPhone which (for some reason) can be placed only on C:\ took 16GB... Add to that my SQL Server installation, office and other essentials. Most of these applications, even when installed to a different drive, still place a lot of stuff on the system drive so soon I found myself struggling for space and using every trick in the book to free up as much space as possible on C:\ just to be able to work.

When I decided it's time to install SQL 2012 on the host O/S and stop using it in a VM, I realized that no tricks will help me now - I need a larger drive. Happily I went to my local hardware dealer and purchased a shiny new 128GB SSD. Since I didn't want to install the O/S from scratch, I looked around for disk imaging software to clone my original disk onto the new drive. Should be simple and straight forward... right? well, I was about to find out the harsh reality soon enough.

A quick search on the internet revealed quite a few disk cloning utilities, some of them free, that claim to do the job. Some even had great reviews so I quickly downloaded one, installed it and was extremely happy to receive the message "Disk Clone Completed Successfully". I changed the BIOS boot sequence, restarted the PC and received the cheerful message "BOOTMGR is missing". I said OK, no harm done and quickly reverted the BIOS settings back, restarted the PC, and guess what - the same message again. No worries. I booted into windows recovery console which confirmed there is an issue with windows start-up and suggested to fix it for me, which it actually did successfully. Booting back from my original drive succeeded and a quick look into windows logs revealed that windows blocked the cloning utility from gaining access to the boot sector. So much for "Successful cloning" within Windows.

I didn't give up. If I can't clone from within windows, I surely can clone from outside windows, right? I downloaded a great boot disk (UBCD) pre-installed with many disk utilities, booted into its windows XP PE (Pre-Installation) environment and it happily cloned the disk for me, this time successfully (or so I thought). I removed the old disk and plugged in the cloned copy in it's place. Restarted the PC and now it simply hung after the BIOS messages with a small blinking cursor at the top left corner of the screen. No worries, no harm done. I have the magical windows recovery console which worked great last time! I placed the windows DVD back in the drive, rebooted and indeed it again found a problem with windows start-up. I clicked "Repair" and got the message "Successfully repaired your windows installation". Happily I rebooted again, and my old friend, the top left small blinking cursor appeared again and the PC just hung. No worries! I quickly plugged in the old disk back and moved the new disk to another slot, and rebooted again. This time, windows boot menu appeared and now I had not one, but 2 "Windows 7 Ultimate" installations which I was asked to choose from. However, this time both of them actually worked. One booted from the old disk and one from the new one. But now I encountered a new issues. No matter which one I booted, the new drive was recognized as E:\ and not C:\ but worse than that, the 128GB disk was identified by disk manager as having just 40GB although its hardware properties showed the correct size. Trying to extend the partition was strictly refused by windows with the very informative error message "Parameter is incorrect". After a few futile attempts to get this configuration working using some really scary utilities modifying internal disk properties, and as I was about to give up about 4 hours into the job, I gathered my strength again and decided to try a different approach. I realized that windows 7 has a built in imaging utility for disaster recovery that should be able to do the trick. On we go.

I deleted the partition from the new disk, booted again only to find out windows still thinks I have 2 operating systems. In the old days we had boot.ini which could be manually edited but it's no longer there in windows 7. Sigh, take a deep breath - I can live with that. Windows was more than happy to perform an image for me (note that you must have a 3rd drive to store the image on). I removed the old disk, plugged in the new one, booted again into windows recovery console. When I asked to restore an image, it searched the disks for me, found my recent image and suggested to restore it. Delighted, I clicked "YES!" please restore my system on the new drive, only to receive a new error message "No suitable disk found to restore the image on". I checked again, the new disk was identified correctly by the BIOS, and even by windows itself in the "Exclude disks" list. So why won't it restore the image on it??? At this point I was really pulling my hair out (and I don't have much of it...) but I didn't give up. I remembered my wonderful boot disk with all its low level disk utilities, it must find if there is something wrong with it. Again I booted into windows PE, and tried ALL available diagnostics on it. Nothing was found wrong. I used everything I could think of - removing MBR, wiping the disk clean with every possible utility but to no avail. Windows simply decided it doesn't like my new disk and won't restore the image on it.

I was just about ready to bite the bullet and re-install windows from scratch, when I remembered that windows recovery console comes with a partition utility of its own called "Diskpart". As a last resort, I tried it. SELECT disk 0 (yes - that's diskpart language) followed by a CLEAN command finally did the trick. I rebooted, windows agreed to recognize the new disk, restore the image on it and even agreed to extend the partition to the full 128GB. At last, I have my old O/S, running on the new disk and utilizing its full capacity. Yes, there still were some minor "leftover" issues and errors but nothing I can't live with. 

All in all, it took more than 7 hours of work and a lot of frustration to make this work. I was also very happy that I bought a heavy duty PC case which only suffered minor "frustration dents" in the process :-)

BTW - I also managed to fix the boot menu issues with this really cool boot manager (free for personal use) from http://neosmart.net/EasyBCD/

So to sum up my lessons from this experience:

• Don't convince yourself that the cheaper hardware will do when you suspect it might not. It will eventually cost you much more.
• Don't use cloning utilities to move operating systems - use the native windows imaging.
• Don't use 3rd party disk partitioning utilities as windows doesn't seem to like them - use diskpart instead.
• If you ever need to move your operating system from one disk to another, take a day off :-)

Before SQL Server 2012, the only management / development tool that shipped with SQL Server as part of the installation was SSMS. SSMS actually serves 2 main purposes - Management and development. You can manage all your SQL Server instances using graphical tools, wizards and using T-SQL. SSMS also included the visual schema designers (A separate blog topic...), Query designers, scripting features and many others that are used by both DBAs and developers.

What a lot of DBAs didn't know, that since 2007, Visual Studio (in some versions) offered a complementary database development tool called "Visual Studio Team System Edition for Database Professionals". A short and friendly name if you abbreviate it to VSTSEDBP. This project was the baby of Gert E. Drapers that gave it its more common name, "Data Dude". Data dude was built to help developers use their familiar and comfortable VS environments while allowing integration of the database development into the VS ALM (Application Lifecycle Management). Data Dude offered schema and object creation using database projects inside VS, off line 'virtualized' development without an actual SQL Server instance, unit testing, data generation capabilities, schema and data compare features, code review, smart debugging and of course the ability to deploy these projects along with the application while maintaining strict version control - a term most production DBAs only heard of theoretically.

SSDT is actually an evolution of data dude. Microsoft seized the opportunity to kill 3 birds with one stone here. The 'less than expected' adoption of data dude and VSTE in general, the every tightening integration of VS infrastructure into the SQL Server tools and the highly accelerated investment in SQL Azure all led to the decision to provide SSDT as a free tool for all SQL Server users with SQL Server 2012. The FAQ page says "SSDT provides a modern database development experience for the SQL Server and SQL Azure Database Developer. As the supported SQL Azure development platform, SSDT will be regularly updated online to ensure that it keeps pace with the latest SQL Azure features.". With VS 11 (still in beta), "SSDT will both replace and provide conversion capability for existing Visual Studio database projects".

Microsoft provides a clear and concise explanation of the use cases of SSDT for developers and DBAs:

"SSDT is for SQL Server database developers, who often develop database schemas, views, stored procedures, and other database objects while developing their application logic.

• Tooling for both SQL Server and SQL Azure Development:

SSDT offers new capabilities in a single cohesive environment to compile, refactor, and deploy databases to specific editions of SQL Server and SQL Azure. The toolset makes it easy, for example, to migrate on-premise SQL Server schemas to the cloud on SQL Azure, and develop and maintain databases across both on premise and cloud deployments. SSDT can target SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, and SQL Azure databases, including all editions of these database servers.

• For SQL Server DBAs:

SSDT provides a central and unified toolset targeted to the specific needs of DBAs to develop and maintain databases, with visual tools for developing tables, schema compare, and rich T-SQL support for refactoring databases, building views, stored procedures, functions and triggers. The toolset provides both a live development mode, and an offline project mode that tracks and manages all artifacts associated with a database. This mode optionally fully integrates with Visual Studio 2010 for team development, source control and change tracking. All change operations are automatically transformed into optimized T-SQL alter scripts, and can optionally be applied immediately to the online database or saved for later execution."

It also answers the natural question - "Is SSDT a replacement for SQL Server Management Studio (SSMS)"?

Well - not really... "SSMS is targeted at core administration of connected databases, and will continue to be a part of SQL Server, and a core component of SQL Server 2012. As such, SSMS will continue to be the graphical tool for such tasks as creating and managing database backups, creating and viewing extended events, and other such central DBA administration tasks for production databases."

So, although currently there is definitely some significant overlap between SSMS and SSDT which was left intentionally to allow users to gradually become familiar with the new SSDT, we can expect future releases to remove some of this overlap until some day, not too far ahead, we will have 2 distinct tools - SSMS for online instance management, and SSDT for development.

If you are into database development, I highly recommend you to install and learn how to use SSDT. It will make your life easier in many ways.

Now, we are happy to offer you early access to the Qure Analyzer V1.5 Beta which offers the following advantages (partial list):

• Full SQL Server 2012 support. 
  • Allows analysis of SQL Server 2012 workloads.
  • Allows use of SQL Server 2012 databases as the workload analysis repository.
• Improved performance of workload analysis phase.
• Ability to handle very large workloads.
• Significantly reduced memory consumption.
• For SQL Sentry users, support for analyzing workloads from the SQL Sentry Performance Advisor repository.
• Default trace template provided as part of the installation for all SQL Server versions.
• New workload analysis management feature for database repositories provides the option to delete a workload analysis or add traces to an existing workload.

• New "Custom Settings" feature allows you to save your current grouping and sorting for quick load later.

• Resource filters now support fine grained value settings in addition to the graphical sliders.
• Comparison filters allow "locking" of baseline and evaluated workloads for easy setting of multiple filters.

• Opening of the same workload twice for filtered comparisons is now supported.
• Many bug fixes, including: 
  • Free text batch template filter not filtering correctly.
  • Excessive memory usage may cause workload analysis cutoff.
  • Wrong naming of trace event classes.
  • Comparison of workloads opened using different aliases is not allowed.

Without your help and feedback, we couldn't make Qure Analyzer better.

I urge you to register and try out the new beta version by sending an Email to support@dbsophic.com or use the signup form. We'll do our best to implement the top feedback suggestions before the GA release of this version, so that you can have an active role in the actual making of this free offering and contribute to the SQL Server community.

And... check out the new cool logo for Qure Analyzer: 

Ami

Although physically, storage will be exactly the same if you define your VARCHAR as 300 or 3,000, it can still make a huge impact on performance. SQL Server needs to estimate the work space that is required for each query and allocate it before the query begins to execute. For variable type columns, the estimation is that the data size will be 1/2 of the max size defined for the column. So if you have a column that is defined as 300 bytes, the allocation estimation will be 150 but for 3,000 - the estimate will be 1,500. On systems that experience memory pressure, this can make a huge difference in performance as your queries will wait for memory allocations, potentially significantly hurting performance in addition to the fact that needless memory will be allocated that could serve SQL Server for much more useful purposes.

The mechanism responsible for handling memory grants is called "Resource Semaphore". You can read more about it in this excellent post by the SQL Server Query Processing team blog: http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx

When you rebuild an index, a full scan of all index keys is performed. SQL Server uses this opportunity not only to rebuild the index, but also to update the related statistics. What is unique about this statistics update is the fact that it is equivalent to an update with the full-scan option. The default statistics sampling uses only a small fraction of the keys in order not to load your production server. A statistics based on a full scan of the data has the potential to be more accurate, therefore provide the query optimizer with better information, allowing it in turn to produce better performing execution plans for your workload queries. Also remember that a statistics update flushes all plans that use it from the procedure cache forcing a recompilation of the related queries.

Therefore, note the following tips:

• After rebuilding an index, there is no need to updates its statistics explicitly. Moreover, doing so might actually get you less accurate statistics if you fail to use the FULL SCAN option.
• After rebuilding an index, there is no need to explicitly flush the relevant plans from cache. They will be flushed automatically. Be aware that using more 'aggressive' means like executing sp_recompile on the underlying table or flushing the cache altogether might cause unexpected results as additional plans, ones that had nothing to do with your rebuilt index, will be recompiled again.
• If you have the time window during off-peak hours, try updating statistics of your larger tables using the FULL SCAN option. In some cases, it can make a huge difference.

Have a great weekend!

The first thing you will notice when you download the product, is the change in available editions. SQL Server 2008 offered the following editions: PDW, Data center, Enterprise, Standard, Web, Workgroup, Developer, Express, Compact, Core and Azure. With SQL 2012, the main editions will be Standard, BI and Enterprise. Licensing has significantly changed for these editions. Developer express and compact editions will be distributed without any change. For more information, see the editions datasheet.

If you are installing SQL 2012 on windows 7 or windows server 2008R2, make sure you install SP1 or higher first. The installer also includes a new ‘on-the-fly’ update feature for integration of updates into the main installation. A great time saver! SQL Server Data Tools (SSDT) and Data Quality services can be installed as part of the main installation. If you haven’t checked out SSDT yet – I highly recommend you give it a try. For more information, see the data developer center.

SQL Server 2012 also removed some major limitations of previous releases and now allows you to install and configure a cluster node across subnets (geo-cluster), use a local, non-shared disk for TempDB in a cluster and use SMB network file shares as a valid, fully supported NAS storage option.

Security wise, default accounts are offered for all SQL Services and keeping in tune with the minimal exposure, highly secure by default installation, the BUILTIN\administrators and the LocalSystem accounts are not provisioned by default to the server sysadmin role.

SQL Server 2012 Books On line now uses the help viewer released with Microsoft® Visual Studio 2010 Service Pack 1 (SP1) and is no longer included in the installation media. You can view the documentation online or download as a local help collection.

Last, remember to read the backward compatibility section for a list of deprecated features, discontinued functionality, Behavior changes and most importantly the breaking changes to the database engine. Some of these breaking changes are quite interesting and surprising.

And the winners are:

The New iPad goes to Peter Tran from PROS Revenue Management. Peter's Qure Analyzer story can be found in this blog post.

The $100 Amazon gift card goes to Steven Jones. Steven's raffle entry, along with several others can be found on our customer testimonials page.

We would like to take this opportunity to thank all Qure Analyzer users – we appreciate your support!

Partners and customers can already download the evaluation version, GA to begin ~ April 1^st.

For more info see http://blogs.technet.com/b/dataplatforminsider/archive/2012/03/06/sql-server-2012-released-to-manufacturing.aspx

Exciting times !

Qure workload tuning product suite will of course support SQL 2012 in their next release.

So tell us how Qure Analyzer has helped you, and you’ll be entered into the March 31st raffle.

To enter: just email your story to intune@dbsophic.com, or post it on your our own blog (with a link to this blog) and let us know.

First prize: An iPad3*

Second prize: $100 Amazon Gift Card

The winners will be announced during the first week of April on this blog.

Don't have a Qure Analyzer story to tell us yet? It's not too late: Download the free Qure Analyzer today!

Some basic rules:

- The stories should be emailed to intune@dbsophic.com.

- Each entry must include valid contact details in order to be eligible to enter the raffle.

- If the story was posted on your blog, we need to know by March 31st (via email, comment or trackback).

- Re-posting old blog posts (with the addition of a link to this blog post) is definitely allowed.

- DBSophic reserves the right to republish stories submitted.

*Assumes iPad3 will be announced in March, as is widely expected. Otherwise, a similarly cool prize will be awarded!

I've been fortunate to attend two summits in the past, but this year I won't be going. I've been doing too much travelling recently and I'm coming the US at the end of March to speak at SQL Connections! I will deliver a session about "Physical Join Operators" and on the way back to Israel, I'll stop in London to deliver a session on "Clusters vs. Heaps" in SQL Bits X. Both events are official SQL Server 2012 Launch events in the US and UK respectively.

If you are attending one of these conferences, come and say Hi... :-)

Go get your copy from http://www.microsoft.com/download/en/details.aspx?id=28145

@SQLServer tweeted the following links for some really neat images of SQL Server 2012 graphics for use as a wallpaper, presentation backgrounds etc.

SQL 2012 Wallpaper

Get’em here before they are gone:

• 1024×768      http://twitpic.com/7d7y08/full
• 1280×800     http://twitpic.com/7d7x8d/full
• 1400×900     http://twitpic.com/7d7wiy/full
• 1680×1024   http://twitpic.com/7d7vwh/full