Tips for using Integration Services in SQL Server 2014 (Part 1) Try to avoid SSIS lookups. Because SSIS lookups slow down performance, try to use the Transact-SQL statements to perform the same functions. If you need to combine data rows from multiple upstream data flow components, you can use the Union All, Merge, and Merge Join transformations. These transformations make...
Author: Alexander Chigrik
SQL Server 2014 Clustering Optimization Tips
SQL Server 2014 Clustering Optimization Tips Create a cluster resource group before installing SQL Server 2014 clustering. Avoid installing SQL Server 2014 into the default cluster group. To create cluster resource group dedicated for SQL Server 2014, you can use the Cluster Administrator tool. You should also place all the shared array drives that will be used by SQL Server...
Tips for using linked servers in SQL Server 2014
Tips for using linked servers in SQL Server 2014 Try to avoid using distribution transaction or minimize it using. Because distribution transactions incur more overhead than general transactions, avoid using distribution transactions, whenever possible. One of the first steps to optimize remote queries against a SQL Server 2014 linked server is rewrite queries so, that the most work will be...
Some tips for using views in SQL Server 2014
Some tips for using views in SQL Server 2014 Consider using indexed views. Indexed views can significantly improve the performance of an application because the result set of the indexed view is stored and indexed in the database. You do not need to modify your existing applications to take advantage of the performance improvements with indexed views. The indexed view...
Some tips for using ASP.NET with SQL Server 2014
Some tips for using ASP.NET with SQL Server 2014 If you use ADO to access SQL Server data in your ASP.NET, avoid creating transactions using ADO methods. Try to create transactions inside a stored procedure on the SQL Server. By doing so, you can reduce network traffic and boost overall ASP.NET performance. When building a web page based on SQL...
Some tips for using jobs in SQL Server 2014
Some tips for using jobs in SQL Server 2014 Try to separate very large job into several small jobs. This can improve performance, and can be useful to maintenance the jobs and locate any problems. Specify the understandable job name. Try to specify the job’s name so, that the name describes what the job makes. By using so, you can...
Tips for using query hints in SQL Server 2014
Tips for using query hints in SQL Server 2014 If you want to set any hints for your query, do not remember to test the query with hint and without the hint and compare results. Because SQL Server 2014 query optimizer usually proposes the best execution plan, you should test the query with hint and use this hint only when...
Tips for using table hints in SQL Server 2014
Tips for using table hints in SQL Server 2014 One of the ways to increase the queries performance is using the SQL Server 2014 table hints. The table hints specify a locking method to be used by the query optimizer with this table, specify the locking granularity for a table, or one or more indexes to be used by the...
Some tips to work with SQL Server 2014 alerts
Some tips to work with SQL Server 2014 alerts SQL Server writes the events to the Windows Application log. SQL Server Agent checks the Windows application log for SQL Server events. An alert is an automated response to events raised by SQL Server. When an event occurs, the SQL Server Agent checks if the appropriate alert exists and if so...
Tips for using stored procedures in SQL Server 2014
Tips for using stored procedures in SQL Server 2014 Try to avoid using temporary tables and DDL (Data Definition Language) statements inside your stored procedure. Using temporary tables or DDL statements inside stored procedure reduces the chance to reuse the execution plan. Call stored procedure using its fully qualified name. The complete name of an object consists of four identifiers:...