THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kevin Kline

  • Now Playing on SSWUG TV!

    Hi friends,

    I know a lot of you regularly read my blog either on my home page at, on, or one of the locations where it's syndicated. And you may also regularly take in my content on SQLSentry.TV or my monthly column on Database Trends & Applications magazine.


    Click the image to see my new weekly video series on!

    Now I'm adding a new video format to the mix, in partnership with my friend Steven Wynkoop and  I've been a frequent speaker in the SSWUG virtual conferences over the past several years and, we thought, why not make this a more frequent occurrence than twice per year?

    For the next several episodes, I'll be discussing new features that will be released in SQL Server 2014.  However, I'd love to make it interactive.  So if you have any topics that are keenly interesting to you which are part of my regular repertoire (SQL Server, SQL, other database platforms like Oracle, database design, enterprise architecture, cloud computing, professional development, and leading IT teams), then please drop me a note. I'd love to hear from you.


    -Read more content at

    -Follow me on Twitter!

    -Google Author

  • Windows Azure and SQL Database News

    This morning Microsoft released some great updates to Windows Azure.  These new capabilities include:

    • SQL Server AlwaysOn Support: General Availability support with Windows Azure Virtual Machines for High Availability and Disaster Recovery
    • Notification Hubs: General Availability Release of Windows Azure Notification Hubs (broadcast push notifications for Windows 8, Windows Phone, iOS and Android)
    • AutoScale: Schedule-based AutoScale rules and richer logging support
    • Virtual Machines: Load Balancer Configuration and Management
    • Management Services: New Portal Extension for Operation logs + Alerts

    Scott Guthrie has a blog post with more details about updates and new features here:

    On a related note, if you use Windows Azure VMs running SQL Server, take a look at this MSDN topic:  Backup and Restore for SQL Server in Windows Azure Virtual Machines.  Finally, Windows Azure offers backup capabilities not only in the cloud but also on-premises, as described here (when the SQL Server 2012 SP1 CU2 or later versions), enabling users to backup directly to a blob using Windows Azure Storage Services:


    -Follow me on Twitter!
    -Google Author




  • Database Trends & Applications column: Database Benchmarking from A to Z


    Have you heard of the monthly print and web magazine Database Trends & Applications (DBTA)? 

    Did you know I'm the regular columnist covering SQL Server?  For the past six months, I've been writing a series of articles about database benchmarking culminating in the latest article discussing my three favorite database benchmarking tools:  the free, open-source HammerDB, the native SQL Server Distributed Replay Utility, and the commercial Benchmark Factory from Dell / Quest Software.

    Wondering what else I wrote about in this series of six articles on database benchmarking? Pop on over to my latest database benchmarking article ( the series to get a recap of all of the articles.

    Pictured (L-R): Scott Klein (Blog), me, and Tobiasz Koprowski (Twitter | Blog) at the SQL Relay event in Glasgow, Scotland. 

    Have you ever run your own database benchmarks? If so, what tools did you use?  Tell me what you think!

    Best regards,


    -Follow me on Twitter!

    -Google Author 

  • The New PASS Executive Committee of 2014

    I'm sure you can understand the high level of interest I have in the internal affairs and governance of PASS.  After all, I was one of the founders of the organization way back in 1999 and was president for four years from 2003 through 2006 (fondly remembered as "the hardest fricken years of my life").

    So I was keenly interested when the latest press release from PASS named the incoming PASS Executive Committee to take their seats on January 1st, 2014. The executive committee is composed of the officers of the organization, that is, directors who are empowered to sign contracts and to speak on behalf of PASS in public settings.  As anyone who's worked on a committee can tell you, they are s...l...o...w.  With that in mind, the PASS board of directors long ago established the executive committee (ExeCo, pronounced "Exec - Coe") to act on behalf of the board when speed and expediency are required, for example, in negotiations where going back and forth with the board and an external partner would simply take way too long and would make the partner throw up their hands in exasperation.

    Another bit of backstory to the creation of the ExeCo.  It was also put in place as a check and balance against a president who might be arbitrary, capricious, or venal.  At that time, the bylaws gave much greater powers to the president and although all the previous presidents had been faultless, the board thought it'd be a good idea to make sure that future boards wouldn't have to deal with a power grab by an ambitious or crony-coddling president decades in the future.  Hence the requirement that even the ExeCo must vote on and come to consensus on the activities that they execute on their own.

    Here are a few thoughts from me about the incoming ExeCo.  Keep in mind that I've known them all for years and call all of them friends:

    • President: Thomas LaRock
      • I recall first meeting Tom way back in the early 2000's when he was a volunteer for PASS and a lead DBA at a major financial house in New England.  Tom was a hardworking and committed volunteer, as well as an up and coming blogger.  Some leaders are conservative and unlikely to change much, if anything, that a big organization does.  As president, I believe that Tom is likely to inject innovation into PASS by making some big bets on growing the organization and better serving the community.
    • Executive Vice President, Finance: Adam Jorgensen
      • The first time I got to sit down and talk with Adam was in Tucson, several years ago, while we were both recording some video for Steve Wynkoops  I was immediately impressed by his business and financial acumen, over and above his technical skills. There are few skills more sorely needed on a board of directors than business and financial skills. Like Tom, Adam is a doer with a lot of energy and the will to get things done, and a history of executing successfully on his projects.  I think that Adam will help deliver on the new, big bets that are in PASS' future as well to help transform the internal governance of the organization to be more effective and efficient.
    • Vice President, Marketing: Denise McInerney
      • It seems like Denise has been an informal leader and influencer in PASS almost since the beginning, when she launched our Women In Technology (WIT) initiative way back in the early 2000's.  Because of her hard work, PASS was one of the very first IT professional organizations to have a dedicated WIT community.  (The Sybase community did it just a year or two before we did).  She's one of the smartest people I know with amazing people skills and a very high EQ. That combination equals wisdom and, if you asked me, I'd take a wise person over a smart one any day of the week.  I'm especially looking forward to what she's going to do around growth and marketing in our global regional structure.

    Welcome to the ExeCo of 2014, y'all!

    One thing I'd like to see from the three of you (plus, Bill Graziano, who will step into the role of Immediate Past President) is a statement of direction or a vision statement.  It'd be very informative to see where you want the organization to go in the years ahead.

    What would I like to see in 2014 and beyond?  Here are a few quick thoughts:

    1. Implement a streamlined and cohesive approach to enabling volunteers to sign up to help the organization.  Many potential volunteers walk away from PASS disappointed because there's no one to talk with about implementing a cool idea or building out an interesting capability for the organization.  I'd like to see a means by which people in our community can successfully offer to do things such as IT projects for PASS (build a webpage, analyze some data, etc).  With a few exceptions like the Summit Program Committee, PASS could stand to do a complete rebuild of its volunteer strategy and processes.
    2. Finalize its structure around global growth.  PASS has done extremely well growing eastward and southward into Eastern Europe, Africa, and South America especially through the SQL Saturday events.  It needs to take that growth in once per year events into the Pacific rim and then leapfrog into fully formed chapters, with regularly scheduled meetings, in a well-defined worldwide organizational structure.
    3. Devise and execute a better and more cohesive partnerships with their sponsor community. PASS still approaches sponsors, like my employer, from the standpoint of exhibitors at the big annual conference.  But PASS could do so much more to make the relationship a solid partnership, offering back to their sponsors a global community with year-round contact 100's of thousands of members of PASS.
    4. Build a social media strategy. PASS exists on Facebook, LinkedIn, and Twitter.  But I wouldn't say that they exploit it.
    5. Continue the effort started at the PASS Business Analytics Conference to define and coalesce the market for business analytics. Nobody else is doing a good job in this space, so PASS should step in and take that goal to the next level.

    I'm sure there's a lot more you'd probably like to see from PASS and many other good ideas.  But this is where my head is at for the moment.  How about you?  Tell me what you think!

    Best regards,


    -Follow me on Twitter!
    -Google Author


  • My Advice About Growing as an IT and Data Professional

    IMG_6089First off, I should apologize for not blogging in quite a while. I've been traveling extensively and, because traveling can be so exhausting, I usually have to queue up blog posts to hit while I'm traveling in order to remain active while on the road.  I didn't do that for these most recent trips.  And, as you can see, there's quite a gap between this post and my last few posts.

    On the other hand, I've experienced an unexpected surge in requests for mentoring and advice from friends and colleagues about career growth.  I'm always happy to help out a friend and, in fact, there's little in my professional experience which I enjoy more than seeing friends grow and advance.  Because I get a lot of questions about professional development, I try to distill these thoughts down into usable nuggets of wisdom. Since I've had quite a few interview requests in the vein of career and professional development in the last several weeks, I thought what better way to get back on track with blogging than to put them all together in one place!

    Infusive Solutions: Mindful Career Development

    Ben Weiss, the digital marketing strategist at Infusive Solutions in NYC (facebook | blog | twitter),  reached out to me a few weeks ago to discuss career development for his own team members around personal branding and career growth.  This guy is really good at making hay while the sun is shining!  We'd initially done just a simple phone discussion, but he has managed to expand these discussions in a variety of ways.  Here are all the links, at present, to the content that Ben created from our discussions:

    1. How to Become an IT Executive:

    2. Landing page with the full interview:

    3. The recent NYC SQL User Group meeting  on personal branding:

    One of the key points in the interviews is that the IT professionals who learn the most about what their business does, rather than just the IT that they work with, are frequently the most successful.  It's a mindful process of understanding your strengths and weaknesses, improving those areas where you are genuinely weak, amplifying those areas where you excel, develop your communication skills, and then getting outside of your comfort zone to become a genuine business problem solver.  I've said it 100 times - There are NO information technology problems. There are business problems which are solved with information technology.  The IT pros who learn that lesson are mighty indeed.  Grab the interviews for more insight.

    Louis Davidson: Why We Write

    Louis "DrSQL" Davidson (blog | twitter) is a long-time good friend. We live in the same fantastic town of Nashville, TN and both are active supporting the local SQL Server user group.  In one of those ironic twists of 21st-century life, we might see each other more in a given year in other cities than in our own home town, simply because we both speak and travel a lot.  If you're considering growing your professional credentials by writing, and it certainly is one of the best ways to grow your personal brand, then I recommend you read all of the "Why We Write" posts in Louis' blog series.  In my interview, I tried to give Louis really thoughtful and insightful answers.  The core of my advice to potential writers in the answer to question number 5.  Please read it and tell me what you think.  The full blog post and interview:

    Why We Write, #6 by Louis Davidson with Kevin Kline:

    Richard Douglas: Speaking and Presentation Skills

    My friend and former coworker at Quest Software, Richard Douglas, has also put together a very nice interview series which is predominantly IT experts from the UK.  Richard has some very insightful questions and a rather different strategy.  His questions are much more focused on developing skills as a good speaker.  It's been very gratifying to see Richard grow in stature as a database expert over in the UK and I'm looking forward to even more great community work from him.  An example of the interesting sort of questions he came up with for the interview include how to make a presentation not only useful, but how can the speaker project it with authority and charisma?  That's not the sort of question I get every day.

    Read my answer to this question and many more here:

    Tim Ford: Interpersonal and Communication Skills

    Tim Ford (blog |twitter) is a long-time SQL Server pro, who's also a very energetic volunteer and user group leader.  One of his very cool side projects is the SQL Cruise.  Tim has been running the SQL Cruise for many years now and it's one of my favorite ways to conduct training.  We have a full day of training every day that we're at sea.  We do excursions together.  We do "office hours" every evening with lots of time to discuss individual problems, career development, and focus on individual mentoring.  A while back, Tim conducted an audio interview and, for some reason, I'm only now getting around to post it.

    Please give it a listen and let me know what you think: (This is a Dropbox file. I'm not really sure how it'll behave if you do not have Dropbox installed. Let me know if it doesn't work as expected).

    Rodney Landrum and SQLBeats: Looking Over the Horizon

    Rodney Landrum (Twitter |Blog) has been putting out great content for Simple-Talk for quite a long time now.  In fall of 2012, we got together and recorded a podcast which was really fun and, at times, funny. Here is the full podcast. Rodney told me that he laughed at several points while editing.  That makes me happy!


    Most of all, I'd like to hear your feedback.  Let me know what you think by posting a comment here, mentioning this on Twitter, or social media like Facebook or LinkedIn.  Many thanks,


    -Follow me on Twitter!
    -Google Author


  • MySQL, NoSQL, and NuoDB

    I've been keen on MySQL ever since I first started using it heavily more than a decade ago.  In fact, I liked it well enough to include it as one of the four main database platforms, over and above the ANSI-ISO SQL standard, in my popular book SQL in a Nutshell. However, with the advent of NoSQL data platforms in the last few years, the waters have been muddied.  It's no longer a quick easy decision as to which database platform you should use, both because there are many new platforms to choose from and because the old, easy choices aren't as cut and dried as they used to be.  MySQL, for example, is now owned by Oracle which definitely complicates the decision, at least in terms of the mainstream commercial versions of that product, while the brain-trust that started MySQL has gone on to the alternative database platform called MariaDB.

    Rather than dive straight into the alternative next-gen of MySQL, I decided to investigate more powerful alternatives that offer more of the benefits of NoSQL and cloud-centric databases.  That's when I landed on NuoDB.  It’s has been generally available for a few months now as a straight database platform.  It didn’t have any real Microsoft-oriented features until recently.  Then last month the company announced a bunch of Microsoft enhancements, many which are great for developers, so I downloaded it.  (You might have seen my tweets from that time when I first started to check it out).

    From their website:

    • 64 bit support for Windows Server, Windows 7 and 8 for high performance
    • Full support for Visual Studio 2012, LINQ and Entity Framework for a more integrated developer experience
    • Azure compatibility for running/deploying NuoDB easily in the cloud

    Now it’s possible to build and deploy .NET applications using standard Microsoft tools and frameworks against a back-end that has built-in scale up elasticity.  In other words, it gives you some of the best features of some of the NoSQL platforms while also giving you some of the best features of Azure.  If you are interested in trying it out, there are 2 free version available for download here.   And definitely let me know what you think. As you probably know, I'm not a hard-code .NET developer.  So I'm interested in hearing from professional developers about its capabilities.

    I’ll write up some observations in future blogs.

    And, as always, thanks!

    -Follow me on Twitter!
    -Google Author

  • What's the Data Modeling Standard for Business Intelligence Systems?

    I had the good fortune to overhead some of my good friends and fellow MVPs discussing the process of data modeling for business intelligence systems.  So what are the industry standard approaches for modeling dimensional data modelling?
    The short answer is that Kimball's dimensional modeling is the most widely adopted standard for any sort of data warehouse. Inmon is also well respected.

    But the consensus is to stick with Kimball, especially if you're looking for something an enterprise standards team is going to recognize as "standard". The book on my shelf and which I most frequently recommend is The Data Warehouse Toolkit: Complete Guide to Dimensional Modeling

    There's a version of this book specifically for the Microsoft platform, focusing on SQL Server Analysis Services (SSAS). But I recommend the generic one because the platform-specific version compresses some of the general concept sections to make room for the Microsoft-specific content. The Kimball book does a good job describing how important it is to get the conformed dimensions and the precise measures most needed for the BI application. Otoh, the platform-specific version does point out some nice tools available for download from Microsoft's website.  Fortunately, you can just go the "Tools & Utilities" tab from this page:

    Of course, if you're using a data modeling tool, the specific notation and design patterns might vary a little bit because the tool offers only a given set of workflows or symbols.  Also, some industries have already mapped out specific pattern data models along with six or seven industry standard figure data models, many of which are free if you know where to look.  [Note: I don't know where to look. So if you do, please post a comment with this important insight!] You might, for example, apply the set of common patterns made popular in financial BI apps, in which you have a staging/ETL area, data marts, and a data warehouse and then carefully measure how quickly and reliably data reaches the user. After all, BI is much more than just the cubes, reporting, dashboards, and event subscriptions of an SSAS/SSRS/SSIS implementation.  It must be useful for and usable by the end-users.

    So, I'm curious - what standards patterns and notations are you using? What data modeling tools are influencing your design?  Have you taken advantage of the various free industry patterns out there?  Inquiring minds want to know!  Post your comment here.  And, as always, thanks!



    -Follow me on Twitter!
    -Google Author

    P.S. A special thanks to Bob Duffy (Blog), Davide Mauri (Blog), Robert Pearl (Twitter Blog), Audrey Hammonds (Twitter Blog), Karen Lopez (Twitter | Blog), Thomas Ivarsson (Twitter Blog), Chistian Cote (Twitter Blog), and Dr. Greg Low (Twitter | Blog) for letting me eavesdrop on their very informative conversation! Be sure to read their blogs and follow them on Twitter.

  • Hear the SQL Server 2012 story on DotNetRocks


    I was privileged to have a chat with my buddies over at, Carl Franklin and Richard Campbell, episode number 876 (876!). Listen to the most popular internet audio talk show for .NET developers!  Here's the abstract:

    Carl and Richard talk to Kevin Kline about the latest features in SQL Server 2012. The conversation starts out talking about the new features that developers will love, like windowing - no need for cursors anymore, you can request a window of records from a set and move easily window-to-window. Kevin also talks about the new column store index that is especially useful with repeating data. There's also a discussion on the role of SQL Server in an increasingly NoSQL world, along with cool new technologies like Hadoop, Cassandra and Hekaton. Kevin closes with an offer of some free tools at SQL Sentry, including Plan Explorer, a tool to help you understand the query plans that SQL Server makes from your queries. Check it out!

    Let me know what you think.  Thanks,


    - Follow me on Twitter!

    - Google Author

  • New On-Line Resources for Windows, Virtualization, and Cloud!

    Ever since returning from the UK for the SQLBits conference, I've been snowed under a mountain of action items.  I've got such a backlog of things to get done, emails to answer, and family to not ignore that I'm starting to feel really guilty.  

    So with that in mind, I wanted to whip out a quick blog post to let you know I'm still alive and thinking of y'all.  What could be quicker and easier than some cool new resources you might not have seen?  For your perusal:  

    Let me know what you think.  Thanks,


    - Follow me on Twitter!

    - Google Author

  • Cheaters Never Win, Even in TPC Benchmarks

    In this column, I want to tell you about one of my favorite aspects of the TPC benchmarks – CHEATING. Keep in mind that I use the term “cheating” in a joking manner with my tongue planted firmly in my cheek.  But I’m also half-serious.  One of the of the things that is great about the TPC benchmarks is that each of the vendors are required to fully describe all of the shortcuts, tweaks, and special operating configurations they use in order to achieve their spectacular performance numbers.  In a sense, the Transaction Processing Counsel requires that all benchmarked platforms declare all of the ways that they cheat in order to achieve peak performance. more...

    Many thanks,


    - Follow me on Twitter!

    - Google Author

  • Use TPC Database Benchmarks to Save Money

    Last month, I began a series of articles describing database application benchmarking. In the first article, I told you about different ways that you can construct your own database application benchmark. However, you don’t have to reinvent the wheel. The Transaction Processing Council ( has already created a large number of database benchmarks that are extremely useful and informative.

    I also described last month how the TPC provides several different types of benchmark tests. For example the TPC-C and TPC-E benchmarks are extremely useful for measuring transaction throughput. On the other hand, the TPC – H benchmark is  useful for measuring business intelligence workloads.

    Today, I would like to give you a primer on how to read the benchmark reports that are published by the major database and hardware vendors.  You never know when a vendor will publish a new benchmark. There’s no set schedule for them to publish their test findings. Of course, you can always look for new advertisements from many of the vendors. But that’s very imprecise. I prefer to find out if there are new results on my own and so I typically start at There, I’ll check to see if my favorite hardware or database vendors have published any new test results.... read more ...

    Many thanks,


    - Follow me on Twitter!

    - Google Author

  • Read the New TPC Database Benchmarking Series

    Let's talk about database application benchmarking.

    This is a skill set which, in my opinion, is one of the major differentiators between a journeyman-level DBA and a true master of the trade. In this article published in my monthly column at Database Trends & Applications magazine, I'll give you a brief introduction to TPC benchmarks and, in future articles, I'll be telling you how to extract specific pieces of valuable information from the published benchmark results.

    But let's get started with an overview … read more.

    Many thanks,


    - Follow me on Twitter!
    - Google Author

  • PASS Business Analytics Conference (BAC) Recap

    The PASS Business Analytics Conference (PASS BAC) is PASS' first foray into an event that is dedicated to business intelligence, big data, data visualization, and business analytics.  And it totally makes sense for PASS to move in this direction, over and above the flagship community work centered on database management and application development.  Why?  Because business analytics is all about how to apply the data being collected and managed by all of those developers and DBAs.  And, at the end of the day, how we use and apply our data is really the nexus of its value.  That's what matters to business.  You can read the speech from the standing president, Bill Graziano (Twitter | Blog), or watch it online at the PASS website.

    The day one highlight, introduced by the SQL Server team's best presenter - Amir Netz (Twitter), is the release of a new BI data visualization tool called Project “GeoFlow” for Excel.  GeoFlow is a 3D visualization and storytelling tool that helps you map, explore and interact with both geographic and chronological data for visualizing data which is difficult to identify in traditional 2D tables and charts. With GeoFlow, you can plot up to a million rows of data in 3D on Bing Maps, see data changes over time and share findings through appealing screenshots and cinematic, guided video tours of the data. It's really something you have to see to understand – check out the video demo and screenshots below. You can also download and try it out firsthand today. It’s an entirely new way to experience and share insights – one you’ll probably enjoy.  For more information on GeoFlow, check out the Excel team’s blog and visit the BI website.

    The highlight for me, aside from connecting with so many friends and colleagues in the exhibit hall at the SQL Sentry booth, was the day 2 keynote address by Dr. Steve Levitt of Freakonomics fame.  Freakonomics is both a brilliant blog and the number one business book in America.  His insights are well documented in a variety of places, not just in his own channels, but also in places such as TEDtalks.  I'm also really enjoying his new website,

    Steve presented an outstanding keynote, full of funny anecdotes and insights into the world of data analytics and interpretation. A couple of his comments really resonated with me which are worth repeating. In one story, he pointed out how some of the greatest insights came from corporate data which was collected incidentally or coincidentally. The data that help provide the greatest and most valuable revelations were from data that was basically a corporate afterthought.  Another revelation - he's only now starting to make much use of relational databases.  He primarily uses spreadsheets, flat files, and the Stata statistical analysis tool.  Another insight, which I've known and proselytized as "the Fresh Pair of Eyes" approach, is that it really helps him to gain insights in a problem by knowing as little about the problem as possible.  As it turns out, if you know the industry or the challenge at the core of the problem, you make a lot of assumptions that limit your means of interpreting data.  By knowing nothing or next to nothing about a particular problem, you can ask the questions that insiders never ask.  Here's an example (not from the keynote though) - let's say you're an energy company CEO.  You might spend a lot of time thinking about how to accommodate the expected huge increase in energy consumption due to lots of people driving electric cars.  You might tell your data analysts to figure out when and how to ensure peak electrical usage is available at the times when consumers are recharging their electric vehicles.  But a fresh pair of eyes would point out that electric cars, in their present form, are a huge energy boondoggle compared to hybrid and plain ol' cheap, high-mileage models like the Honda Civic.  Consumers will never recoup their investment in a high-priced, all-electric car compared to a cheap, gas sipping model.

    IMG_0287 - CopyAt the heart of his presentation is the fact that data is meaningless when it doesn't answer important questions!  Many times, data professionals spend so much time devising elegant SQL statements and clever user-interfaces that they forget about using a fresh pair of eyes when they look at business questions.  Our session, Operational Excellence for the BI Pro, focused on the trails and travails of successfully implementing and growing the footprint of a business intelligence project.

    In addition, we had a fun and very informative panel discussion breakfast on Thursday of the PASS BAC. At right is a picture of Nick Harshbarger, Justin Randal, and me prior to the session.  The audience was very engaged and, despite having no slides, there was a whole lot of wisdom goin' on.  The panel included Chris Webb (Twitter | Blog), Craig Utley, Jen Stirrup (Twitter | Blog), Paul Turley (Blog),  and Stacia Misner (Twitter | Blog). I served as the moderator and facilitator of the session.  We recorded the session, with a little HD Flip camera, and although I haven't checked out the file yet, we're hopeful we can post it or at least a transcript soon.

    Do you have a "fresh eyes" story? I'd love to hear it!  Post a comment here!

    Many thanks,


    -Follow me on Twitter!
    - Google Author

  • The All-New 'Database Lifecycle Management" is available on MSDN

    The initial release of Database Lifecycle Management is now available on MSDN.

    The site is something called "curated content". This means it's a single consolidated location to look up lots of disparate articles and content, all in one easy to search location. This “curated content view” contains the best content, video, and community-centric information from Microsoft, including topics like:

    SQL Server Data Tools

       · Get started with sample projects, code samples

       · Video demos by Gert Drapers (blog)

       · Script common data portability tasks using Sqlpackage.exe

       · Link to the SSDT team blog

    SQL Server Management Studio

       · Manage SQL Database using SSMS

       · Backup and restore w/ SQL Azure

       · Migrate local databases to Azure

       · Video demo of hybrid scenarios by Gert Drapers (blog)

    Windows Azure SQL Database

       · SQL Database backup and restore

       · Import/export SQL Database

       · Windows Azure training kit

       · Connection management and troubleshooting connections



    -Follow me on Twitter!
    - Google Author

  • Squishy Limits in SQL Server Express Edition

    It's an old story you've probably heard before.  Provide a free version of your software product with strict limitations on performance or other specific capabilities so that folks can give it a try without risk, while you minimize the chance of cannibalizing sales of your commercial products.  Microsoft has take this strategy with SQL Server Express Edition, not only to increase adoption in the student market but also to counter the threat of open-source (i.e. free) relational databases like MySQL for entry-level applications.

    One such limitation of SQL Server Express Edition is that it supports no more than 1GB of RAM for the instance.  Of course, you could have many Express Edition instances on a single Windows server, each with its own 1GB of RAM.

    But what does that metric of 1GB of RAM actually mean?  The key thing to remember is that the restriction is for buffer cache.  Since SQL Server has many other caches, even when not counting the plan cache, there are plenty of other caches within SQL Server.  (Run a query against sys.dm_os_memory_clerks if you'd like to see some of the others).  Because only the buffer cache has the strict 1GB limitation, you can actually watch SQL Server Express Edition's memory working set size grow to around 1.4-1.5GB due to the other memory caches at play.

    Pawel Potasinski, a SQL Server MVP from Poland (Twitter | Blog), once posted an interesting repro for this behavior:

    -- Assess amount of databases resident in buffer cache

     WHEN database_id = 32767 THEN 'mssqlsystemresource'
     ELSE DB_NAME(database_id)
     END AS [Database],
     CONVERT(numeric(38,2),(8.0 / 1024) * COUNT(*)) AS [MB in buffer cache] 
    FROM sys.dm_os_buffer_descriptors 
    GROUP BY database_id 
    -- Assess amount of tables resident in buffer cache
     QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + '.' +
     QUOTENAME(OBJECT_NAME(p.object_id)) AS [Object],
     CONVERT(numeric(38,2),(8.0 / 1024) * COUNT(*)) AS [MB In buffer cache] 
    FROM sys.dm_os_buffer_descriptors AS d 
     INNER JOIN sys.allocation_units AS u ON d.allocation_unit_id = u.allocation_unit_id 
     INNER JOIN sys.partitions AS p ON (u.type IN (1,3) AND u.container_id = p.hobt_id) OR (u.type = 2 AND u.container_id = p.partition_id) 
    WHERE d.database_id = DB_ID() 
    ORDER BY [Object] DESC;
    -- Fill up Express Edition's buffer allocation
    IF OBJECT_ID(N'dbo.test', N'U') IS NOT NULL
     DROP TABLE dbo.test;
    CREATE TABLE dbo.test (col_a char(8000));
    INSERT INTO dbo.test (col_a)
     SELECT REPLICATE('col_a', 8000)
     FROM sys.all_objects 
     WHERE is_ms_shipped = 1;
    GO 100

     The bottom line for the hard memory limit of SQL Server Express Edition is "Yes, it's limited.  But it's a squishy limit. Not a hard limit."

    Although your mileage may vary, I'd bet a dollar that you'll find more than 1GB in the active working set for your instance of SQL Server Express Edition.  I am curious, however, if you're seeing much variation between versions and even service packs of SQL Server?  Let me know if you try this out on more than one version and/or service pack level of SQL Server.  Did it change much between versions?  Let me know!



    -Follow me on Twitter!

    Google Author


This Blog



Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement