SQL Server 2019 Transact-SQL optimization tips (Part 2) Try to avoid using the DISTINCT clause, whenever possible. The DISTINCT clause should only be used if you know that duplicate returned rows are a possibility. Because using the DISTINCT clause will result in some performance degradation, you should use this clause only when it is necessary. Consider using a MAXDOP option...
Tag: Transact-SQL
SQL Server 2019 Transact-SQL optimization tips (Part 1)
SQL Server 2019 Transact-SQL optimization tips (Part 1) Use table variables instead of temporary tables. Table variables require less locking and logging resources than temporary tables, so table variables should be used whenever possible. Try to avoid using SQL Server cursors, whenever possible. SQL Server cursors can result in some performance degradation in comparison with select statements. Try to use...
Some useful SQL Server 2017 Transact-SQL optimization tips (Part 2)
Some useful SQL Server 2017 Transact-SQL optimization tips (Part 2) Try to avoid using the DISTINCT clause, whenever possible. The DISTINCT clause should only be used if you know that duplicate returned rows are a possibility. Because using the DISTINCT clause will result in some performance degradation, you should use this clause only when it is necessary. Use table variables...
Some useful SQL Server 2017 Transact-SQL optimization tips (Part 1)
Some useful SQL Server 2017 Transact-SQL optimization tips (Part 1) Try to avoid using SQL Server cursors, whenever possible. SQL Server cursors can result in some performance degradation in comparison with select statements. Try to use correlated subquery or derived tables, if you need to perform row-by-row operations. Encapsulate your Transact-SQL code inside a transaction when you modify database, not...
SQL Server 2016 Transact-SQL Optimization Tips (Part 2)
SQL Server 2016 Transact-SQL Optimization Tips (Part 2) Try to restrict the queries result set by returning only the particular columns from the table, not all table’s columns. This can results in good performance benefits, because SQL Server will return to client only particular columns, not all table’s columns. This can reduce network traffic and boost the overall performance of...
SQL Server 2016 Transact-SQL Optimization Tips (Part 1)
SQL Server 2016 Transact-SQL Optimization Tips (Part 1) Consider using a new query hint NO_PERFORMANCE_SPOOL. This hint was first introduced in SQL Server 2016 and can prevent a spool operator from being added to query plans. This can improve performance when many concurrent queries are running with spool operations. Try to restrict the queries result set by using the WHERE...
Troubleshooting problems with SQL Server 2014 Management Studio
Troubleshooting problems with SQL Server 2014 Management Studio If you have problems with SQL Server 2014 Management Studio (SSMS 2014), review this troubleshooting checklist to find potential solutions. 1. Install the latest SQL Server 2014 service pack. Because many SQL Server 2014 Management Studio bugs were fixed in SQL Server service packs, you should install the latest SQL Server service […]
SQL Server 2014 Transact-SQL Optimization Tips (Part 2)
SQL Server 2014 Transact-SQL Optimization Tips (Part 2) Try to restrict the queries result set by using the WHERE clause. This can results in good performance benefits, because SQL Server will return to client only particular rows, not all rows from the table(s). This can reduce network traffic and boost the overall performance of the query. Consider using the CONTAINS...
SQL Server 2014 Transact-SQL Optimization Tips (Part 2)
SQL Server 2014 Transact-SQL Optimization Tips (Part 2) Try to restrict the queries result set by using the WHERE clause. This can results in good performance benefits, because SQL Server will return to client only particular rows, not all rows from the table(s). This can reduce network traffic and boost the overall performance of the query. Consider using the CONTAINS […]
SQL Server 2014 Transact-SQL Optimization Tips (Part 1)
SQL Server 2014 Transact-SQL Optimization Tips (Part 1) Use views and stored procedures instead of heavy-duty queries. This can reduce network traffic, because your client will send to server only stored procedure or view name (perhaps with some parameters) instead of large heavy-duty queries text. This can be used to facilitate permission management also, because you can restrict user access...