Infrastructure at your Service

Tag

SQL Server 2014 Archives - Page 2 of 7 - Blog dbi services

Stéphane Haby

How to be sure that tempdb size is good to run a DBCC CHECKDB?

By | Database Administration & Monitoring, Database management | No Comments

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! 😕

Read More
Stéphane Haby

SQL Server, Integration Services and Package Versions

By | Database Administration & Monitoring | No Comments

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…

Read More
Microsoft Team

Dealing with SSRS subscription schedules in AlwaysOn environment

By | Database management | 13 Comments

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…

Read More
Microsoft Team

Clustered columnstore index and memory management

By | Database management | 2 Comments

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…

Read More
Stéphane Savorgnano

SQL Server 2014: First Service pack (SP1) is available

By | Technology Survey | No Comments

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…

Read More
Microsoft Team

Variations on 1M insert (2): write commit

By | Development & Performance | No Comments

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…

Read More
Stéphane Savorgnano

In-Memory OLTP: Hash and Range indexes

By | Hardware & Storage | No Comments

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…

Read More
Stéphane Haby

SQL Server Tips: How to know if In-Memory Feature is supported by your server?

By | Database management | No Comments

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…

Read More
Stéphane Haby

Security via policies

By | Database management | No Comments

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…

Read More
Microsoft Team

Memory management, OOM issues with SQL Server 2014 In-memory OLTP

By | Application integration & Middleware | 2 Comments

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…

Read More