Tips for using full-text search in SQL Server 2014 Reduce the full-text unique key size. To create a full-text index, the table to be indexed must have a unique index. Try to select a numeric column as the full-text unique key to increase the speed of full-text population. If the table to be indexed does not have numeric unique index,...
Tag: SQL Server 2014
Tips for using indexed views in SQL Server 2014
Tips for using indexed views in SQL Server 2014 Keep the indexes on the views as narrow as possible. Because each index takes up disk space try to minimize the index key’s size to avoid using superfluous disk space. This reduces the number of reads required to read the index and boost overall index performance. Avoid creating indexes on a...
Tips for using Join Hints in SQL Server 2014
Tips for using Join Hints in SQL Server 2014 Use join hints very carefully. Because SQL Server 2014 query optimizer usually proposes the best execution plan, it is very unlikely that you can optimize your query by using join hints, more often, this will hurt performance. Try to rewrite the query to provide better execution plan. If your query is...
Memory management for In-Memory OLTP Server – Part 1
Memory management for In-Memory OLTP Server Part-1 Microsoft introduced a new functionality in SQL 2014 namely memory optimized objects. As the name implies, this technology is about having tables reside in memory. This means that the memory usage on your database will be much higher. In this series of articles we will see how we can manage the memory in...
Memory management for In-Memory OLTP Server – Part 2
Memory management for In-Memory OLTP Server – Part II In my previous article, we saw how to calculate the memory needed for memory based objects when using In-Memory technology that was introduced in SQL Server 2014 version. We also saw how we can monitor the memory usage by these objects. In this article we will see how we can make...
SQL Server 2014 Reporting Services Optimization Tips
SQL Server 2014 Reporting Services Optimization Tips Consider using the Google Chrome browser to run the Reporting Services native mode Report Manager. SQL Server 2014 release of Reporting Services adds support for the Google Chrome browser. So, you can use the Google Chrome browser to run the Reporting Services native mode Report Manager to manage reports and the report server....
Tips for using bulk copy in SQL Server 2014
Tips for using bulk copy in SQL Server 2014 Use native mode bulk copy whenever possible. Because native mode bulk copies are generally faster than character mode, you should use native mode bulk copy whenever possible. Note. Now the bcp utility supports native data files compatible with SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2,...
Tips for using constraints in SQL Server 2014
Tips for using constraints in SQL Server 2014 Use CHECK constraints instead of rules. Rules are provided for backward compatibility and have been replaced by CHECK constraints. Constraints are much more efficient than rules and can boost performance. Rules have some restrictions. For example, only one rule can be applied to a column, but multiple CHECK constraints can be applied....
Tips for using temporary tables in SQL Server 2014
Tips for using temporary tables in SQL Server 2014 Consider using memory-optimized table variables instead of a traditional table variable to reduce tempdb use. SQL Server 2014 introduced In-Memory OLTP (In-Memory Optimization). In-Memory OLTP introduces the memory-optimized tables and the memory-optimized table type. A table variable created using a memory-optimized table type is a memory-optimized table variable. Memory-optimized table variables...
Tips for using User-Defined Functions in SQL Server 2014
Tips for using User-Defined Functions in SQL Server 2014 Try to avoid using nested user-defined functions. Because using nested user-defined functions can result in some performance degradation, try to break down a nested function into simpler functions. Consider using CLR user-defined functions. The CLR user-defined functions were first introduced in SQL Server 2005. You can write the user-defined functions in...