How to use Database Tuning Advisor via command line

Question: Is it possible to use SQL Server Database Tuning Advisor through the command line? Answer: On a server with SQL Server installed you can start the SQL Server Database Tuning Advisor In the command line type "dta".That will return something like : Microsoft (R) SQL Server Microsoft SQL Server Database Engine Tuning Advisor command line utility Version 14.0.800.90 ((SQL_Main).170711-1339) Copyright (c) 2015 Microsoft. All rights reserved. Additionally - you can use : dta -? and that will give you extra usage details along with descriptions per switch Usage: DTA.EXE [-S ServerName[\Instance]] [-U LoginId] [-P Password] [-E] [-d DatabaseName] [-D... Read more →

UPDATE STATISTICS GETTING SLOWER

Question: The Statistics tasks in the SQL Server Maintenance plan is getting slower? When I first implemented the task it used to take 1 hr – recently it’s built up 4-5 hrs. I’ve checked underlying resource issues – and everything looks OK. Could you advise on how I can progress with troubleshooting. I’ve included a screeshot of Update Statistics Task. Answer: It is not unusual to find UPDATE STATISTICS getting slower over time. Looking at the way you have this configured All existing statistics – this includes all index and table statistics Sample size of 25 % It is very... Read more →

SQL Indexed View limitations

SQL Server Indexed Views remain a powerful technique for improving sql query performance , if utilised in the right context. The most effective use is to provide a pre-aggregated or pre-calculated view of the base data. In other words, users are able to query an aggregation of data without having to process the complex JOINS and aggregates when executing a request. For example, in a CRM , a daily batch job may pump data into the warehouse. A workflow may run which pre-aggregates the data into an indexed view. The pre-aggregation process may take 15 minutes, but the user does... Read more →

Memory grants and execution plans

Question: When reviewing some executions plans for slow running queries, I’ve noticed Memory Grant appearing. What is the Memory grant message? Answer: As an example: When the SQL Optimizer predicts it will operate on 15 rows based on the latest statistics for those tables , a chunk of memory is granted to fulfil the operation. But, if the statistics are inaccurate or not up to date, and the operation returns 10000 rows , the operation doesn’t automatically grant the extra memory. The operation will spill to disk – in the tempdb. Spilling to disk will allow the sorting and joining... Read more →

How to fix : Operarator used tempdb to spill data during execution warning

Question: During a query analysis , the execution plan showed a warning on a Physical Sort operator with the warning message Operator used tempdb to spill data during execution with spill level 1 Why is this occurring? How can I fix it? Answer:The warning appears because SQL Server is granting insufficient memory to complete the operation. Understanding the root cause is the challenge. For ORDER BY and SORT IN TEMPDB read SQL Server – SORT IN TEMPDB and Sort Warnings Has the execution plan not granted enough memory? Is the SQL Server under memory pressure?... Read more →