Starting with SQL Server 2014 a new troubleshooting capability is to monitors real time query progress with the DMV sys.dm_exec_query_profiles which is the base for Live Query Statistics new functionality for SQL Server 2016, there are two ways to enable real time query monitoring: Session scope: By enabling SET STATISTICS XML ON; or SET STATISTICS PROFILE ON;... » read more
Execution Plan in SSMS Run Stored Procedure in SSMS with “Execution Plan” enabled. SSMS will recommend index to be created. Improve Query Statement Convert subqueries to main queries. Note: This may not always increase performance. Have to run the both queries side by side to test.
Note: Make sure the user you are about to delete does not own anything in the database, else you will get an error message saying unable to delete user. Make sure the user does not own any of the SQL jobs.
Look for duplicate records. Remove duplicate records
Now the common misconception with this option is probably because of the following screen within SQL Server Management Studio (Server Properties -> Connections) where we see the following. So the first checkbox allows remote connections to the server so we could assume that the remote query timeout value underneath (here set to 600, the default)... » read more
If need to include indexes during SQL compare, make sure the following boxes are unchecked in the “options” section… Ignore indexes Ignore performances indexes Adding Missing Index
Error: Got the following error after upgrading SSRS. Failed to load expression host assembly. Details: Could not load file xxxxxxx or assembly or one of its dependencies. The system cannot find the file specified. (rsErrorLoadingExprHostAssembly) Fix: For SQL Server 2014 to 2016, copy the missing dll to the new folder. From… C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting... » read more
You can give your users rights to check if reports have run or jobs are complete without granting full admin rights. Here’s how you can do so by using SQLAgentReaderRole in SQL Server. If you have several SQL Agent jobs on your system, it is inevitable that at some point in some of your in-house... » read more
Semantic Modeling Language (SMDL) SQL Server 2016+ SMDL models were deprecated as of the release of SQL Server 2014, the ability for them to function was still there. SSRS 2016 or later doesn’t support the Report Models feature. Uploading and managing report models is a discontinued feature on SQL Server 2016 (is no longer a... » read more