Infrastructure at your Service
In many blogs or forums, you can read that the answer is to use the option: WITH ESTIMATEONLY. With this option, you can easily have the space estimation needed to check the database in tempdb. But be careful, only since SQL Server 2014, this estimation has been good! See the PS from Paul Randal’s blog for this information, here But between SQL server 2008, 2012 and 2014…and 2016, the result of this query changed! 😕
A lot of people ask me about the different versions of SQL Server Integration Services and packages. I create rapidly this little table to summarize it: With SQL Server 2008 and 2008 R2, the SSIS version is the same than SQL Server Integration Services 10.0. With SQL Server 2005, the SSIS version is not contained in the service name. To get this information, you must check directly in the MsDtsSrvr.exe file. How to know your…
A couple of days ago, I had the opportunity to work with AlwaysOn 2014 infrastructure that included a report server (SSRS) in native and scale-out mode. As you know, dealing with SSRS and availability groups is not an easy task depending on the used features. This is particularly true when scheduling and subscriptions are used by customers. Here’s a brief description of the concerned architecture: 2 SSRS servers in scale-out mode and an availability group…
Few weeks ago, I had the opportunity to give a session about the clustered columnstore index feature (CCI) at our In-Memory event dedicated to In-Memory technologies for Microsoft SQL Server, Oracle and SAP HANA. During our session, I explained the improvement made by Microsoft on SQL Server 2014 with the introduction of new clustered columnstore index (CCI). The CCI includes a new structure that allows update operations: the delta store. Indeed, insert operations go directly…
May 14th, Microsoft has released the first Service Pack (SP1) for SQL Server 2014. It is more than thirteen months after the RTM version. SQL Server 2014 Service Pack 1 includes all of the CU from 1 to 5. Which issues are fixed in this SP1 There are 29 hotfixes: 19 for the Engine 6 for SSRS 3 for SSAS 1 for SSIS Some improvements are: Performance improvement of Column store with batch mode operators…
In this blog post, I will try to do the same than my colleagues about Oracle and for PostgreSQL. As a reminder, we’ve seen in my previous blog post that SQL Server is designed to commit transactions implicitly by default and inserting 1M rows in this case may have a huge impact on the transaction log throughput. Each transaction is synchronously committed to the transaction log. In this blog post, we’ll see a variation of…
Since SQL Server 2014 CTP2, Microsoft has introduced a new kind of index which is the Range index. We have now two ways for indexing an In-Memory table: using either an Hash index or a Range index. These two indexes are slightly different. In fact, a Hash index is a set of buckets, 8-bytes memory pointers, which points to the actual row data or row chain data. SQL Server 2014 uses a hash function to…
A customer asks me, how to know if In-Memory Feature is supported by my SQL Server server? An easy way is to check the edition, version etc. but now, you have directly a property for that. On msdn here, you find all property that you can search with the T-SQL Command: SERVERPROPERTY But if you try to run through all your servers with CMS (Central Management Server), for all SQL Server below than SQL Server…
Few weeks ago, I presented the session on security via Policies for “Les journées SQL Server 2014”, organized by the French SQL Server User Group (GUSS) in Paris. I promised to post our policies script on a blog. Security Policies are split into 4 categories: Server Instance Database Data All policies follow this naming convention: dbi_”Level number”_”Level name”_”Policy name” I give you the policy name, the condition and the query or facet associated. To understand…
Last week I gave a workshop about SQL Server 2014 and the new features. The first day we worked on new In-memory OLTP and different topics such as the new internal storage, the new transaction processing behavior or the new checkpointing process. During this day, one of the attendees asked me about the memory management with In-Memory OLTP feature. It was a very interesting question but unfortunately I didn’t have the time to discuss about…
