THE SQL Server Blog Spot on the Web

Welcome to - 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

  • Introducing Microsoft SQL Server 2014

    The release of the latest two versions of SQL Server (2008 R2 and 2012) has been accompanied by the publication of their respective eBooks. On April 2nd, a post on Microsoft Press Blog has announced the publication of the eBook Introducing Microsoft SQL Server 2014!


    This eBook is divided in two parts; the first one is dedicated to Database Administration, here the authors describe the editions of SQL Server 2014, the characteristics of the new Engine In-Memory and how the transition from on-premises solutions to the Cloud is easy with added support for Hybrid environments. In the second part, the authors describe the components that support data analysis and what improvements have been done in related technologies such as Microsoft Excel 2013, Power BI for Office 365, HDInsight, and PolyBase.

    The PDF version of the eBook is available, for free, here. In addiction you will find this book into the eBook section on Microsoft Virtual Academy home site.

    Enjoy the book and thanks to authors Ross Mistry (@RossMistry) and Stacia Misner (@StaciaMisner).

  • Unexpected SPID change

    A few time ago I had the opportunity to investigate about an unexpected SPID (Session Process ID) change. The inquiry began when a customer of ours starts to complain for locks, and lock time out error. These locks sometime were more frequent and sometimes not.

    To investigate about this issue, I have taken two SQL Profiler trace files related of two execution of the Application functionality on which the customer has complained about the problem, with the same execution criteria. The only difference is that the first trace file was taken when the user complained locks and lock timeout error, while the second one was taken when no locks were occurred.

    Comparing the two SQL Profiler trace files I have noticed an "unexpected" SPID change happened exactly when the Application has been locked; I have written "unexpected" because the Application uses always one connection to perform the queries captured by SQL Profiler. I have checked the piece of code that execute the queries shown in the following picture and I have verified no new connection was been opened explicitly.

    The following picture shows the first execution in which you can see the unexpected SPID change.

    Picture 1 – Execution with unexpected SPID change

    Let me focus on the trace captured during the first execution. As you can see in the Picture 1, at certain point for the ClientProcessID number 192, there was been a SPID change from SPID number 111 to SPID number 110. The last query executed with SPID 110 has only the SP:StmtStarting event without SP:StmtCompleted event because this query was blocked from the previous SPID number 111 and for this reason the Application has been blocked.

    The following picture shows the second execution.

    Picture 2 – Execution without SPID change

    Let me focus on the second execution of the same Application functionality, on the same client and with the same execution criteria, of course. As you can see in the picture 2 the SPID number is always the 68 for all queries performed in the same piece of code (as I expect).

    Now the questions are: Which is the reason for this unexpected SPID change? Which are the conditions that force SQL Server to take the decision of changing the SPID number between two queries execution?

    Talking about this issue with Erland Sommarskog he asked me "What API does the application use?" and my answer: "OLE DB", so he replied me "We have the answer!".

    When you use OLE DB or something based on OLE DB and you perform a query on a connection which has not fully consumed the entire result-set of the previous, the default behavior is to open a new connection behind the scenes. This new connection will have a different SPID number, it is an attempt to be helpful, but it is not be ever helpful.

    In particular, the result-set not fully consumed was in the last query executed by the SPID number 111, the same tables were been accessed from the last query with SPID 110 and then there was been the lock.

    Thanks for the help Erland!

  • Hurry up! Buy one book and get one for free on Packt Publishing!

    Packt Publishing wants to celebrate the release of their 2000th title with the great offer "Buy One, Get One Free" applied into their comprehensive catalog.

    If you buy a book, of your choice, you will get another one for free. In addition, you will exploit this offer unlimitedly during the offer period.

    I suggest you to take a look to available titles, you can risk to bring home two books at the ones price :) in particular, I suggest you to look this categories:

    Enjoy the books!

  • Performance Tuning with SQL Server Dynamic Management Views

    From Red-Gate website you can download (for free) the ebook Performance Tuning with SQL Server Dynamic Management Views written by Louis Davidson (@drsql) and Tim Ford (@sqlagentman).

    DMVs are objects that publish information about the health state of a SQL Server instance. They allow you to monitor the heart of the system to diagnose problems and measure performance. They represent a very good tool for DBAs and database Developer as well. Reading this book you will learn how to obtain information from the most frequently used DMVs in investigating query execution, index usage, session and transaction activity, disk I/O, and how SQL Server is using or abusing the operating system.

    The editorial review is available here.

    This book cannot miss in your digital library! And obviously you can buy the paperback on Amazon.

  • Troubleshooting Clusters

    The session Troubleshooting Clusters by Allan Hirt (@SQLHA) has been published on channel SQLPASS TV.


    Whether you are new to clusters or have years of experience, you may still cross your fingers when implementing a failover cluster instance (FCI) of SQL Server or an availability group (AG). Both require an underlying Windows Server failover cluster (WSFC), but how many DBAs are involved in the planning and deployment of it? What happens if you try to install an FCI or configure an AG and things don't work? Or worse, what if the implementation fails after going live in production? This session will cover how to diagnose what went wrong and the free tools available to you, as well as demonstrate how to fix common issues that every DBA should know about.



  • Skewed Data - Poor Cardinality Estimates... and Plans Gone Bad

    The session Skewed Data, Poor Cardinality Estimates, and Plans Gone Bad by Kimberly Tripp (@KimberlyLTripp) has been published on channel SQLPASS TV.
    When data distribution is heavily skewed, cardinality estimation (how many rows the query optimizer expects each operator to process) can be wildly incorrect, resulting in poor quality query plans and degraded performance. You've probably seen the advice to update all statistics if a query plan looks wrong – but is that the right advice? In many cases, no! These are "sledgehammer" approaches, and while they might solve some problems (usually parameter sniffing problems), they don't solve the actual problem. In this session, you'll learn a generalized yet tailored-to-the-table way to solve query plan quality problems for very large tables (VLTs). Topics will include creating, using, and updating filtered statistics; using forced parameterization and templatized plan guides; and understanding stored procedures and how they can leverage filtered statistics.
  • Review of the book SQL Server 2012 Reporting Services Blueprints

    Recently I had the opportunity to read the book SQL Server 2012 Reporting Services Blueprints written by Marlon Ribunal and Mickey Stuewe and published by Packt Publishing.

    First, I have to tell you that I am not a Reporting Services guy, I have much more knowledge on other topics such as Database Development, Performance Tuning, and so on. When I have heard about the opportunity to read this book I have thought it was a great opportunity to increase my knowledge on SQL Server Reporting Services, but the time is always short, so I needed a practical book that goes to the point quickly.

    If I would describe you the book with only one adjective, absolutely I could tell you that this book is concrete and easy to read. There are many pictures in the book and the authors guide the reader, step-by-step, from the Definition of the Data Source for an SSRS Project up to the Reporting Services Best Practices and Transactional Replication topics.

    The book covers all feature of SQL Server Reporting Services and provides you all information you need to start develop database reports with SSRS in a week. If you want to look inside the book you can download a sample chapter here and this is the table of contents:

    • Chapter 1: Let's Breakdown the Numbers
    • Chapter 2: Making a Good Impression
    • Chapter 3: Getting Some Actions
    • Chapter 4: Everything Looks Better with Charts
    • Chapter 5: Location, Location, Locations!
    • Chapter 6: Let's Get Analytical!
    • Chapter 7: Sir, We're Ready to Deploy!
    • Chapter 8: Beyond the Horizon!
    • Appendix A: SSRS Best Practices
    • Appendix B: Transactional Replication for Reporting Services

    My preferred chapters are the 2, 4 and 5.

    This book cannot missing in your digital or physical library!

  • Building Your T-SQL Tool Kit: Window Function Fundamentals

    The session Building Your T-SQL Tool Kit: Window Function Fundamentals by Christina E. Leo (@christinaleo) has been published on channel SQLPASS TV.


    Have you pulled a script to identify duplicates from a blog post but couldn't quite get it to work because you weren't sure what that ROW_NUMBER() function was doing? Maybe you heard talk about creating running totals without using subqueries, but you got frustrated when the groups weren't totaling correctly. Or maybe, you've never even heard of window functions. All are good reasons to attend this all-demo session, which demystifies this versatile T-SQL tool. First, we'll break apart the OVER clause, the key to understanding how window functions work. Then we'll expand on each group of functions that can use the OVER clause: ranking, aggregate, and analytic functions. Finally, we'll look at real scenarios where this tool works and talk about performance considerations. When you leave, you'll have the fundamentals you need to fully develop your mastery of window functions.


  • Automate Your ETL Infrastructure with SSIS and PowerShell

    SQLPASS TV channel has been published the session Automate Your ETL Infrastructure with SSIS and PowerShell that Allen White (@SQLRunr) has done during the PASS Summit 2013. 


    Much of your ETL process flow consists of packages that are very similar in structure, capturing data from a single source and transferring that to a single destination. Creating the individual packages can be tedious, and it’s easy to miss something in the process of generating the same basic package over and over again. BI Markup Language makes it easy to build new packages, and PowerShell makes creating the BIML scripts easy. In this session, we’ll see how you can use PowerShell to generate dozens of SSIS packages doing similar tasks from a defined set of ETL sources.


    Enjoy the session and thanks to Allen White!

  • PASS DW/BI Virtual Chapter Upcoming Sessions (December 2013)

    Let me point out the upcoming live events scheduled for December 2013 organized by PASS Business Intelligence Virtual Chapter.


    Create and Load a Staging Environment from Scratch in an Hour with Biml

    Date: Thursday 12 December Noon PST / 3 PM EST / 8 PM GMT
    Speaker: Scott Currie

    Business Intelligence Markup Language (Biml) automates your BI patterns and eliminates the manual repetition that consumes most of your SSIS development time. During this hour long presentation, Scott Currie from Varigence will use the free BIDSHelper add-in for BIDS and SSDT to introduce Biml and use to automatically generate large quantities of custom SSIS packages. The session will be largely demonstration driven, and reusable sample code will be distributed for you to use in your own projects. Using a live-typing approach, Scott will start from scratch and by the end of the session create a full-blown staging environment. This will include the creation of *hundreds* of target table creation scripts, data load packages, data scrubbing rules, logging, and more. The best part is that you can freely reuse the code in your own environment just by changing the connection strings - or make small changes to implement your own data load patterns.


    Inferred Dimension Members within MDS and SSIS

    Date: Monday 16 December 3 PM PST / 6 PM EST / 11 PM GMT
    Speaker: Reza Rad

    Combining Master Data Services with Data Warehouses, will cause some challenges in ETL Scenarios. In this session we will go through a demo of Inferred Dimension Members implementation with SSIS considering the fact that MDS keeps the single version of truth for the dimension record. In this session you will learn how we will write back new record's data into MDS entity as an Inferred member. The staging structure of Master Data Services and Batch Processing will be used for this. Then you will learn what is the best practice to add the inferred record into Data Warehouse dimension. Updating the existing dimension member also would consider the Inferred member and apply SCD types only if this is not an inferred Member.


    Guerrilla MDS/MDM The Road To Data Governance

    Date: Thursday 19 December Noon PST / 3 PM EST / 8 PM GMT
    Speakers: Ira Whiteside and Victoria Stasiewicz

    Ira and Vic's session "Guerrilla MDS" will be a walk-through of a real-world implementation for a master data model (MDM) and metadata mart utilizing SSIS, MDS and POWER BI EXCEL add-ins as well as applying proper data quality techniques. We will walk through in detail the processes necessary for utilizing the complete MDS functionality as follows: creating entities attribute, relating entities the domain based attributes, staging leave table, updated entity content, apply business rules, create subscription view and set up security. Source code for all samples and PowerPoint will be made available. 

  • Fundamentals of SQL Server 2012 Replication

    From Red-Gate web site you can download, for free (plus a trial of SQL Monitor), the eBook Fundamentals of SQL Server 2012 Replication written by Sebastian Meine (@sqlity).

    Data replication is traditionally considered a complex feature and generally we get discouraged easily, already during the preparation of the test environment. Why the implementation of a SQL Server Replication is considered a complex activity? Which problems could you meet? The most common problems that you could encounter are related to the database schema that it has to be thought for replication scenarios and there could be problems related to the replication agents' permissions. Finally, you may encounter problems related to the architecture of the network and the Internet bandwidth especially if you want to implement a web replication or if you have to use an ftp area for data exchange. To do a good job, we need different skills.

    This book cannot miss in your digital library! And obviously you can buy the paperback on Amazon.

    Editorial Review

    Many of my clients need to make data that lives on one server available on another server. There are many reasons for such a requirement. You might want to speed up cross-server queries by providing a local copy of the data. Or you might want to make the data available to resource intensive reporting queries without impacting the OLTP load, maybe even with an intentional delay so you're always reporting against complete days only. Finally, you might be looking to implement high availability. In all these situations, SQL Server Replication is a viable option to look at when planning for the implemen­tation of such a requirement.

    With this book, I want to introduce you into the vast world of SQL Server Replication and show you its most important strength and weaknesses. After working through the exercises, you will be able to make an informed decision whether replication is the right feature to use and which type of replication is the most advantageous in your situation. You will also know when to stay away from replication and use other features such as simple log shipping or the new "Always On" feature set.

    Fundamentals of SQL Server 2012 Replication provides a hands-on introduction to SQL Server replication. The book begins with a short overview that introduces you to the technologies that make up replication. In the following chapters, the book will walk you through setting up different replication scenarios. All hands-on exercises are designed with security best practices in mind. When you're finished working through the exercises, you will be able to implement your own multi-server replication setup while following the principle of least privilege.

    Enjoy the book!

  • PASS Business Intelligence Virtual Chapter Upcoming Sessions (November 2013)

    Let me point out the upcoming live events, dedicated to Business Intelligence with SQL Server, that PASS Business Intelligence Virtual Chapter has scheduled for November 2013.


    The "Accidental Business Intelligence Project Manager"

    Date: Thursday 7th November - 8:00 PM GMT / 3:00 PM EST / Noon PST
    Speaker: Jen Stirrup

    • You've watched the Apprentice with Donald Trump and Lord Alan Sugar. You know that the Project Manager is usually the one gets fired
    • You've heard that Business Intelligence projects are prone to failure
    • You know that a quick Bing search for "why do Business Intelligence projects fail?" produces a search result of 25 million hits!
    • Despite all this… you're now Business Intelligence Project Manager – now what do you do?

    In this session, Jen will provide a "sparks from the anvil" series of steps and working practices in Business Intelligence Project Management. What about waterfall vs agile? What is a Gantt chart anyway? Is Microsoft Project your friend or a problematic aspect of being a BI PM? Jen will give you some ideas and insights that will help you set your BI project right: assess priorities, avoid conflict, empower the BI team and generally deliver the Business Intelligence project successfully!


    Dimensional Modelling Design Patterns: Beyond Basics

    Date: Tuesday 12th November - Noon AEDT / 1:00 AM GMT / Monday 11th November 5:00 PM PST
    Speaker: Jason Horner, Josh Fennessy and friends

    This session will provide a deeper dive into the art of dimensional modeling. We will look at the different types of fact tables and dimension tables, how and when to use them. We will also some approaches to creating rich hierarchies that make reporting a snap. This session promises to be very interactive and engaging, bring your toughest Dimensional Modeling quandaries.


    Data Vault Data Warehouse Architecture

    Date: Tuesday 19th November - 4:00 PM PST / 7 PM EST / Wednesday 20th November 11:00 PM AEDT
    Speaker: Jeff Renz and Leslie Weed

    Data vault is a compelling architecture for an enterprise data warehouse using SQL Server 2012. A well designed data vault data warehouse facilitates fast, efficient and maintainable data integration across business systems. In this session Leslie and I will review the basics about enterprise data warehouse design, introduce you to the data vault architecture and discuss how you can leverage new features of SQL Server 2012 help make your data warehouse solution provide maximum value to your users. 

  • Microsoft MVP Award Renewal 2013

    There is a day of the year in which an MVP is "expiring" and in that day he hopes to receive the renewal notification email for this important professional recognition. The MVP Award Program is the way used by Microsoft to say "Thanks for your commitment"... thanks for sharing your technical knowledge with others, thanks for helping others in the worldwide technical communities.

    Today is the expiration day of my MVP Award, but is also the renewal day because I have received the long awaited email "Congratulations 2013 Microsoft MVP!" I begin in this way my fourth year in the MVP Award Program for SQL Server. It is an honor for me to be part of this magic group of people that every day share their technical knowledge with others, with passion!

    In the past, someone asked me "How can I becoming an MVP?" or "What did you do for becoming an MVP?" My answer was: "Learn a technology very well and keep the pace because it will change quickly; help others about using this technology, not for money but only for a Thanks and put passion in all of this!". This is my point of view of the page Becoming an MVP.

    Thanks very much to all of you; I will do my best to bring in a responsible way this great recognition!

    Congrats to new MVPs and to all MVPs renewed:

  • One, Two, Three: SQL Saturday in Italy!

    As a friend of mine tells "The first time that something happens is a fortuity, the second time that it happens again is a probability and the third one is a certainty!"

    Therefore, this year, SQL Saturday in Italy is a certainty! From October to December in Italy there will be three SQL Saturday events!

    The first one: October 8th 2013, Milan

    The second one: November 9th 2013, Verona

    The third: 2013 December 13th, Ancona

    All the CFPs are still open, if there are one or more arguments related to SQL Server, that you take care or that you are interested in, send your session proposals! It could be a great opportunity to visit Italy.

    See you there!

  • SQL Server Backup and Restore

    From Red-Gate web site you can download, for free, the eBook SQL Server Backup and Restore written by Shawn McGehee.

    One of the most important activities under DBA responsibility is to check backups and maintain an efficient recovery plan. In this book, Shawn McGehee explains how to build an efficient recovery plan; because after you have known that your database is in "Suspect Mode" or is corrupt and some data were lost, there is no worst news of that tells you that no valid backups are available for database recovery. It is not enough to take a backup, you must ensure that it is also possible to perform the restore!

    This book cannot miss in your digital library! And obviously you can buy the paperback on Amazon.

    Editorial Review

    The duties and responsibilities of a Database Administrator (DBA) make for a long and dynamically changing list, ranging from offering query tuning advice, to cutting stored procedures, all the way through to system process design and implementation for high availability. A DBA's tasks, from day-to-day, are rarely constant; with one exception: the need to ensure each and every day that any database in their charge can be restored and recovered, in the event of error or disaster. This means that if a database, for whatever reason, gets corrupted, dropped, or otherwise becomes unusable, then it is the DBA's responsibility to restore that database to the state it was in before the problem occurred, or as close as is possible.

    Of course, this doesn't mean that a DBA is required to restore a database each and every day, just that if disaster does strike the DBA must be prepared to deal with it, regardless of when or why it occurs. If a DBA isn't prepared, and significant data is lost, or databases become unavailable to end users for long periods of time, then that DBA probably won't be in their job for too long. This is why a good, and tested, SQL Server backup and restore plan must be on the top of every administrative DBA's list of tasks.

    In this book, you'll discover how to perform each of these backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Red Gate's SQL Backup tool. Capturing backups using SSMS or simple scripts is perfectly fine for one-off backup operations, but any backups that form part of the recovery strategy for any given database must be automated and you'll also want to build in some checks that, for example, alert the responsible DBA immediately if a problem arises. The tool of choice in this book for backup automation is Red Gate's SQL Backup Pro. Building your own automated solution will take a lot of work, but we do offer some advice on possible options, such as PowerShell scripting, T-SQL scripts and SQL Server Agent jobs.

    Enjoy the book!

More Posts Next page »
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement