THE SQL Server Blog Spot on the Web

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

Sergio Govoni

A place where share my experience on SQL Server and related technologies

  • The biggest table in your database

    You may need to know which is the biggest table (in terms of disk space usage) in the database that you are currently connected. Sometimes, this information is very useful to check the indexing strategy of these tables.
     
    There are several methods to know which is the biggest table of a database, one of these methods is to use the standard report "Disk Usage by Top Table" exposed by SQL Server Management Studio. Another method is through T-SQL language, you can perform the sp_spaceused system stored procedure for each table contained in your database, you could store all the partial results in a temporary table and ordering these results by the "data" column, so you can find the biggest table of the database.
     
    Using the sp_spaceused system stored procedure we have to accept a row-by-row solution, in fact, we will perform a call to sp_spaceused for each table contained into the database. Increasing the number of tables, the number of calls to the stored procedure will grow up.
     
    An alternative solution is represented by the following Common Table Expression based on the internal code of the sp_spaceused system stored procedure, it allows us to obtain the result with a single execution, in a set-based way.
     
    WITH spaceused AS
    (
      SELECT
        sys.dm_db_partition_stats.object_id
        ,reservedpages = SUM(reserved_page_count)
        ,it_reservedpages = SUM(ISNULL(its.it_reserved_page_count, 0))
        ,usedpages = SUM(used_page_count)
        ,it_usedpages = SUM(ISNULL(its.it_used_page_count, 0))
        ,pages = SUM(CASE
                       WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
                       ELSE lob_used_page_count + row_overflow_used_page_count
                     END
                    )
        ,row_Count = SUM(CASE WHEN (index_id < 2) THEN row_count ELSE 0 END)
      FROM
        sys.dm_db_partition_stats
      JOIN
        sys.objects ON sys.objects.object_id=sys.dm_db_partition_stats.object_id
      OUTER APPLY
        (SELECT
           reserved_page_count AS it_reserved_page_count
           ,used_page_count AS it_used_page_count
         FROM
           sys.internal_tables AS it
         WHERE
         it.parent_id = object_id
         AND it.internal_type IN (202,204,211,212,213,214,215,216)
         AND object_id = it.object_id
      ) AS its
      WHERE
        sys.objects.type IN ('U', 'V')
      GROUP BY
        sys.dm_db_partition_stats.object_id
    )
    SELECT
      name = OBJECT_NAME (object_id)
      ,rows = convert (char(11), row_Count)
      ,reserved = LTRIM (STR (reservedpages * 8, 15, 0) + ' KB')
      ,it_reserved = LTRIM (STR (it_reservedpages * 8, 15, 0) + ' KB')
      ,tot_reserved = LTRIM (STR ( (reservedpages + it_reservedpages) * 8, 15, 0) + ' KB')
      ,data = LTRIM (STR (pages * 8, 15, 0) + ' KB')
      ,data_MB = LTRIM (STR ((pages * 8) / 1000.0, 15, 0) + ' MB')
      ,index_size = LTRIM (STR ((CASE WHEN usedpages > pages THEN (usedpages - pages) ELSE 0 END) * 8, 15, 0) + ' KB')
      ,it_index_size = LTRIM (STR ((CASE WHEN it_usedpages > pages THEN (it_usedpages - pages) ELSE 0 END) * 8, 15, 0) + ' KB')
      ,tot_index_size = LTRIM (STR ((CASE WHEN (usedpages + it_usedpages) > pages THEN ((usedpages + it_usedpages) - pages) ELSE 0 END) * 8, 15, 0) + ' KB')
      ,unused = LTRIM (STR ((CASE WHEN reservedpages > usedpages THEN (reservedpages - usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')
    FROM
      spaceused
    ORDER BY
      pages DESC;
     
    Are you thinking how can we access to the internal code of the sp_spaceused? It is simple, we can do that using the sp_helptext system stored procedure!
     
    Have fun! 
  • Introducing Microsoft SQL Server 2016 (Preview 2)

    Microsoft Press (@MicrosoftPress) has recently announced the publication of the Second Preview of the eBook Introducing Microsoft SQL Server 2016!
     
     
    In this second preview, the authors: Stacia Varga (@_StaciaV_), Denny Cherry (@mrdenny), and Joseph D'Antoni (@jdanton) introduce three new security features: Always Encrypted, Row-Level Security, and Dynamic Data Masking; they discuss the enhancements that enable you to better manage performance and storage: TempDB configuration, Query Store, and Stretch Database. Finally, they discuss about the improvements to Reporting Services, AlwaysOn Availability Groups, Tabular enhancements and R integration.
     
    The PDF versions of the eBook are available here:
     
     
    Enjoy the book and SQL Server 2016!
  • SQL Server 2012 Cumulative Updates

    Microsoft has released the following Cumulative Updates for SQL Server 2012 SP2 and SP3.

    Cumulative Update 10 for SQL Server 2012 SP2

    Cumulative Update 1 for SQL Server 2012 SP3

  • Introducing Microsoft SQL Server 2016 (Preview Edition)

    The release of the latest three versions of SQL Server (2008 R2, 2012 and 2014) has been accompanied by the publication of their respective eBooks. Today, a post on Microsoft Press Blog has announced the publication of the Preview Edition of the eBook Introducing Microsoft SQL Server 2016!
     
     
    This preview eBook contains only three Chapters, the Chapter 2, 4 and 7.
     
    The Chapter 2 describes the new features on the Protection and Data Security areas: Always Encrypted, Row-Level Security, and Dynamic Data Masking. The Chapter 4 describes the new implementations (and some extensions to existing functionality) in the SQL Server Engine that allow you to better manage growing data volumes, query performance and the solutions known as the "Hybrid Cloud". The Chapter 7 describes the most important features implemented in SQL Server Reporting Services (SSRS); reading this chapter you will easily realize why SQL Server 2016 represents a milestone for both Reporting Services and Mobile BI.
     
    The PDF versions of the eBook is available here:
     


    Thanks to the authors: Stacia Varga, Denny Cherry, and Joseph D'Antoni.
     
    Enjoy the book!
  • SQL Server 2012 SP2 Cumulative Update 9 released!

    Microsoft has released the Cumulative Update 9 for SQL Server 2012 SP2, these are the related articles:

    After you have applied the CU9, the build number of SQL Server 2012 SP2 will be: 11.0.5641.0

  • The free #eBooks series #SQLServer and .NET Blog

    The series of free eBooks "The SQL Server and .NET Blog eBook Series" by Artemakis Artemiou (@artemakis) collects dozens of articles explaining how to solve practical problems in these areas:
     
     
    Developing SQL Server (The SQL Server and .NET eBook Series)

    This book is for database developers and architects who develop SQL Server databases and related database processes. The book features tens of articles that provide detailed information on how to develop in SQL Server. However, the book is not intended to be a step-by-step comprehensive guide. Additionally, it assumes at least intermediate-level experience with SQL Server development and knowledge of basic database principles.

     
     
    Administering SQL Server (The SQL Server and .NET eBook Series)

    This book is for database administrators and architects who monitor and administer SQL Server instances in order to keep them operating to the highest possible level of stability and performance. The book suggests several techniques that can be used for ensuring a healthy SQL Server instance.

     
     
    Tuning SQL Server (The SQL Server and .NET eBook Series)

    This book is for database administrators and architects who monitor and tune SQL Server instances in order to keep them operating to the maximum possible performance and stability. The book suggests several techniques that can be used for ensuring a performant SQL Server instance.
     
     
     
    These three ebooks can not miss in your digital library!
  • SQL Saturday #454 – A couple of days after #sqlsat454

    Slides and demo files of the sessions we have made at SQL Saturday #454 in Turin are available for download through the schedule page.

    This is a good occasion to say "Thanks" to Politecnico di Torino (that has hosted the event), Organizers, Sponsors and Speakers. A big Thanks also to who have attended this event, you have made of this event a great event!

    We has fun very much :) and the photos posted on Twitter prove it!

  • PASS Summit is back! Looks into the 2015 edition! #Summit15

    The PASS Summit is back! This year the most important event around the world for SQL Server Data Platform will be held again in Seattle (WA) from 27 to 30 October 2015 and it will be preceded from two pre-conference days from 25 to 26 October 2015.

    Why I wrote "The most important event around the world for SQL Server Data Platform"? Because numbers of the PASS Summit 2014 are self explanatory! Last year over 5,000 data-geeks have attended the PASS Summit which has become the landmark event for the entire Microsoft Data Platform. Take a look at numbers of the year 2014, that are shown in the following pictures, they are really impressive, aren't they?

     

     

    Every year more and more IT Professionals, Technicians, Analysts and Data Scientists consider this event as an unique opportunity to: Connect to other people that have the same passion you have, Share your experience and the problems you face for your job and Learn more and more about the Microsoft Data Platform for choose always the best technology to win the challenges of the market. Don't forget that you have also the opportunity to meet and talk to people like Conor Cunningham (one of the principal architect of the relational Engine of SQL Server and SQL Azure), Dr. David DeWitt (one of the biggest expert of Parallel Databases) and Mark Souza (General Manager in the Microsoft Data Platform Group). Are you thinking that people are too busy for a talk with you? Nobody of these big names walks away if you try to meet and talk them!

    If you are undecided about the quality of the content provided at the PASS Summit, you can watch the sessions of the previous year on PASS TV. You will have no doubts about the quality of the sessions :)

    Are you a beginner or a first timers at PASS Summit? Don't worry there are lots of sessions of level 100 and 200 and you can count on the "First Timers Guidebook" that it will arrive soon!

    What kind of sessions can you expect to find at the PASS Summit 2015? Find it out by the recording sessions of the last 24 Hours of PASS event (September 2015) named "Summit 2015 Preview Edition", each video is available on detail page of the related session.

    You can find all information you need on the PASS Summit 2015 website and for your convenience, here some useful links:

    See you there!

  • 24 Hours of PASS (September 2015): Recordings Available! #24HOP

    The Sessions of the event 24 Hours of PASS named "Summit 2015 Preview Edition" (which was held the last September 2015 on 17th and 18th) were recorded and now they are available for online streaming!

    If you have missed one session in particular or the entire event, you can view or review your preferred sessions. Each video is available on detail page of the related session.

    What could you aspect from the next PASS Summit 2015? Find it out on recorded sessions of this edition of 24 Hours of PASS!

  • The series Microsoft #Azure Essentials: Free #eBooks

    From the beginning of this year, Microsoft Press (@MicrosoftPress) has been published a series of free eBooks about the services available on the Microsoft Azure platform. The eBooks are listed below:
     
     
    Microsoft Azure Essentials: Fundamentals of Azure
     
     
     
    This ebook covers the fundamentals of Azure you need to start developing solutions right away. It concentrates on the features of the Azure platform that you are most likely to need to know rather than on every feature and service available on the platform. This ebook also provides several walkthroughs you can follow to learn how to create VMs and virtual networks, websites and storage accounts, and so on. In many cases, real-world tips are included to help you get the most out of your Azure experience… Read more and download.
     
     
    Microsoft Azure Essentials: Azure Automation

     
    This ebook introduces a fairly new feature of Microsoft Azure called Azure Automation. Using a highly scalable workflow execution environment, Azure Automation allows you to orchestrate frequent deployment and life cycle management tasks using runbooks based on Windows PowerShell Workflow functionality. These runbooks are stored in and backed up by Azure. By automating runbooks, you can greatly minimize the occurrence of errors when carrying out repeated tasks and process automation… Read more and download.
     
     
    Microsoft Azure Essentials: Azure Machine Learning
     
     
    Microsoft Azure Machine Learning (ML) is a service that a developer can use to build predictive analytics models (using training datasets from a variety of data sources) and then easily deploy those models for consumption as cloud web services. Azure ML Studio provides rich functionality to support many end-to-end workflow scenarios for constructing predictive models, from easy access to common data sources, rich data exploration and visualization tools, application of popular ML algorithms, and powerful model evaluation, experimentation, and web publication tooling… Read more and download.
     
     
    Microsoft Azure Essentials: Azure Web Apps for Developers

     
    Azure Web Apps is a fully managed platform that you can use to build mission-critical web applications that are highly available, secure, and scalable to global proportions. Combined with first-class tooling from Visual Studio and the Microsoft Azure Tools, the Azure Web Apps service is the fastest way to get your web application to production. Azure Web Apps is part of the Azure App Service that is designed to empower developers to build web and mobile applications for any device… Read more and download.
     
    Enjoy the eBooks on Microsoft Azure platform! 
  • 24 Hours of PASS (September 2015): Summit Preview Edition

    Which sessions can you expect to find at the next PASS Summit 2015? Find it out on September 17, 2015, 12:00 UTC at the Summit 2015 Preview Edition of the 24 Hours of PASS.
     
     
     
    Search your preferred sessions by track: 

    Register now at this link; thanks to the sponsors, the 24 Hours of PASS is presented at no cost!

    This edition of 24 Hours of PASS wants to be a sneak taste what you can expect from the next PASS Summit 2015 that this year will be in Seattle (WA) from 27 to 30 October 2015.
     
    No matter from what part of the world you will follow the event, the important thing is to know that they will be 24 hours of continuous training on SQL Server and Business Intelligence on your desk!
  • SQL Server 2016 Video Pills

    This is my first curation since Microsoft Curah! has been moving to a new Microsoft platform named docs.com.

    SQL Server 2016 Video Pills is a collection of videos about the most important features of SQL Server 2016; speakers are Engineers and Program Manager of the SQL Server Team!

    Would you like to discover the new features of SQL Server 2016? Watch these videos!

    Enjoy!

  • 24 Hours of PASS (June 2015): Session recordings now available!

    Sessions of the event 24 Hours of PASS: Growing Our Community (which was held on last June 24-25) were recorded and now they are available for online streaming!

    If you have missed one session in particular or the entire event, you can view it or review your preferred sessions.

    All details are available here.

    Remember, the next PASS Summit will be held in Seattle (WA) on October 27-30, 2015.. don't miss it!

    Enjoy!

  • 24 Hours of PASS (June 2015)

    The most important free on-line event on SQL Server and Business Intelligence is back!

    The 24 Hours of PASS is coming back with a great edition that will provide best practices, expert tips, and demos, from new and up-and-coming Speakers. Don't take commitments on 24 and 25 of June 2015.

    Register now at this link, it's free!

    No matter from what part of the world you will follow the event, the important thing is to know that it will be 24 hours of continuous training on SQL Server and Business Intelligence.

    Enjoy! 

  • SQL Server 2016 Public Preview

    It's official, the first public preview of SQL Server 2016 will come this summer. Details are available on the SQL Server Blog:

    If you want to know more about capabilities of SQL Server 2016 visit the SQL Server 2016 preview page.

This Blog

Syndication

Privacy Statement