I recently came across an issue where tSQLt tests were taking a long time to run. The procedure under test was doing a 38 table (!) join (with 37 faked tables and a table valued parameter). Only two of the faked tables and the TVP had any rows inserted Compilation times were extremely slow. Trace flag 8675 showed End of simplification, time: 0.002 net: 0.002 total: 0 net: 0.002 end exploration, tasks: 549 no total cost time: 0.013 net: 0.013 total: 0 net: 0.015 end search(0), cost: 13372.9 tasks: 3517 time: 0.012 net: 0.012 total: 0 net: 0.028 end exploration, tasks: 3983 Cost = 13372.9 time: 0 net: 0 total: 0 net: 0.028 end search(1), cost: 6706.79 tasks: 10187 time: 0.024 net: 0.024 total: 0 net: 0.052 end exploration, tasks: 10188 Cost = 6706.79 time: 0 net: 0 total: 0 net: 0.052 end search(1), cost: 6706.79 tasks: 61768 time: 0.165 net: 0.165 total: 0 net: 0.218 *** Optimizer time out abort at task 614400 *** end search(2), cost: 6706.79 tasks: 614400 time: 12.539 net: 12.539 total: 12 net: 12.758 *** Optimizer time out abort at task 614400 *** End of post optimization rewrite, time: 0.001 net: 0.001 total: 12 net: 12.759 End of query plan compilation, time: 0.003 net: 0.003 total: 12 net: 12.762 SQL Server parse and compile time: CPU time = 12735 ms, elapsed time = 12770 ms. It looks like the estimated rows grow exponentially for each join between empty tables until at the end the estimated row count was 135,601,000 and the query had a huge estimated cost justifying longer compile time. There was one specific table involved in a lot of these joins and inserting a single row to that table was enough to stop this explosion for the joins that table was involved in (cardinality estimator output indicates that it is now using the stats histogram from that table) The original behaviour seems weird to me. SQL Server knows that the table it is joining onto is empty and the plan caching white paper indicates that inserting any row to an empty table will cause the recompilation threshold to be reached so is there any good reason for it? Repro showing the estimated row count growth (though without long compile times) CREATE TABLE T1(C1 INT); INSERT INTO T1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9); CREATE TABLE T2(C1 INT, C2 VARCHAR(MAX)); SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.C1 = T2.C1 LEFT OUTER JOIN T2 T3 ON T3.C1 = T2.C1 LEFT OUTER JOIN T2 T4 ON T4.C1 = T2.C1 LEFT OUTER JOIN T2 T5 ON T5.C1 = T2.C1 LEFT OUTER JOIN T2 T6 ON T6.C1 = T2.C1 LEFT OUTER JOIN T2 T7 ON T7.C1 = T2.C1 LEFT OUTER JOIN T2 T8 ON T8.C1 = T2.C1 LEFT OUTER JOIN T2 T9 ON T9.C1 = T2.C1 LEFT OUTER JOIN T2 T10 ON T10.C1 = T2.C1 LEFT OUTER JOIN T2 T11 ON T11.C1 = T2.C1 LEFT OUTER JOIN T2 T12 ON T12.C1 = T2.C1 LEFT OUTER JOIN T2 T13 ON T13.C1 = T2.C1 LEFT OUTER JOIN T2 T14 ON T14.C1 = T2.C1 LEFT OUTER JOIN T2 T15 ON T15.C1 = T2.C1 LEFT OUTER JOIN T2 T16 ON T16.C1 = T2.C1 LEFT OUTER JOIN T2 T17 ON T17.C1 = T2.C1 LEFT OUTER JOIN T2 T18 ON T18.C1 = T2.C1 LEFT OUTER JOIN T2 T19 ON T19.C1 = T2.C1 LEFT OUTER JOIN T2 T20 ON T20.C1 = T2.C1 LEFT OUTER JOIN T2 T21 ON T21.C1 = T2.C1 LEFT OUTER JOIN T2 T22 ON T22.C1 = T2.C1 LEFT OUTER JOIN T2 T23 ON T23.C1 = T2.C1 LEFT OUTER JOIN T2 T24 ON T24.C1 = T2.C1 LEFT OUTER JOIN T2 T25 ON T25.C1 = T2.C1 LEFT OUTER JOIN T2 T26 ON T26.C1 = T2.C1 LEFT OUTER JOIN T2 T27 ON T27.C1 = T2.C1 LEFT OUTER JOIN T2 T28 ON T28.C1 = T2.C1 LEFT OUTER JOIN T2 T29 ON T29.C1 = T2.C1 LEFT OUTER JOIN T2 T30 ON T30.C1 = T2.C1 LEFT OUTER JOIN T2 T31 ON T31.C1 = T2.C1 LEFT OUTER JOIN T2 T32 ON T32.C1 = T2.C1 LEFT OUTER JOIN T2 T33 ON T33.C1 = T2.C1 LEFT OUTER JOIN T2 T34 ON T34.C1 = T2.C1 LEFT OUTER JOIN T2 T35 ON T35.C1 = T2.C1 LEFT OUTER JOIN T2 T36 ON T36.C1 = T2.C1 LEFT OUTER JOIN T2 T37 ON T37.C1 = T2.C1 LEFT OUTER JOIN T2 T38 ON T38.C1 = T2.C1 LEFT OUTER JOIN T2 T39 ON T39.C1 = T2.C1

I want to use tSQLt framework for unit testing my T-SQL queries. However [documentation](https://tsqlt.org/user-guide/quick-start/) warns: > Note: tSQLt should never be installed in production. In my case I have single instance of SQL Server, containing both production databases and dev databases. Will installing tSQLt give me some problems in itself, or is the warning just because of all the locks that come with using it? I obviously plan to use one of dev databases for my work, but I am not sure there are server-wide implications that come with the framework.

I am a SQL Server DBA. To automate some of my daily tasks, I need to write stored procedures. Creating functionality without testing does not make sense to me. For creating and running my tests, I used the tSQLt Framework. I had to create a database during the test. "CREATE DATABASE" is not permitted inside a multi-statement transaction, but all tests are automatically run inside a transaction within tSQLt Framework. Naturally, I can set up a test database manually before running the test, but the test cannot depend on the environment in which it will be executed. How should this be approached?

I have found below CPU consuming query with a query cost of 1540. I have tried creating appropriate indexes which reduced the query cost to 1234 but still, the query is expensive. Index Created : CREATE NONCLUSTERED drop INDEX [IX-AAD_RESP_NOW-AAD_DATE] ON [dbo].[MST_AR_AS_ON_DATE] ([AAD_RESP_NOW],[AAD_DATE]) INCLUDE ([AAD_LOC_CODE],[AAD_00TO30],[AAD_31TO60],[AAD_61TO90],[AAD_91TO120],[AAD_121TO150],[AAD_151TO180],[AAD_180PLUS]) GO Query: SELECT CAST(YEAR(A.AAD_DATE) AS VARCHAR(4))+' - '+RIGHT('00'+CAST(MONTH(A.AAD_DATE) AS VARCHAR(2)),2), 'Insurance', SUM(A.AAD_00TO30+A.AAD_31TO60+A.AAD_61TO90+A.AAD_91TO120+A.AAD_121TO150+A.AAD_151TO180+A.AAD_180PLUS) FROM MST_AR_AS_ON_DATE A WHERE A.AAD_RESP_NOW = (SELECT MAX(B.AAD_DATE) FROM MST_AR_AS_ON_DATE B WHERE MONTH(B.AAD_DATE) = MONTH(A.AAD_DATE) AND YEAR(B.AAD_DATE) = YEAR(A.AAD_DATE)) AND A.AAD_DATE >= '2019-01-01' AND A.AAD_DATE = (SELECT MAX(B.AAD_DATE) FROM MST_AR_AS_ON_DATE B WHERE MONTH(B.AAD_DATE) = MONTH(A.AAD_DATE) AND YEAR(B.AAD_DATE) = YEAR(A.AAD_DATE)) AND A.AAD_DATE >= '2019-01-01' AND A.AAD_DATE <= '2022-05-18 23:59:59' AND (AAD_RENDOC_ID = -1 OR -1 = -1 ) AND (AAD_LOC_CODE LIKE '%%' OR '' = 'ALL') GROUP BY A.AAD_DATE ORDER BY 1,2 DESC I am not a developer hence I am not able to optimise the query on query level. I will appreciate if anyone can guide me what changes are required to fine-tune this query. Thank you in advance. Please find estimated execution plan below: https://www.brentozar.com/pastetheplan/?id=r1xPQCKPq Also, Actual execution plan is uploaded in below link. https://www.brentozar.com/pastetheplan/?id=H1JG3q6_c

I am writing stored procedures with some non-trivial business logic. I am trying to unit test them, but the actual tests end up being quite long (shorter ones starting at 40-50 LoCs, using commonly 4 different tables), which doesn't seem very "unit". (Admittedly, I format my code in a way where it takes a lot of space.) In context of "normal" programming languages I've heard the advice to refactor the complex procedure into smaller chunks. But I don't want to do that here because: 1. I don't want to pollute "global namespace" by small routines called from one place only. 2. Passing around tables from and to stored procedures is cumbersome. 3. Custom functions can have negative effects on performance. Am I wrong about this reasoning? I am new to unit testing, so perhaps I am just writing my tests wrong? Is SQL longwinded language and thus it's unit tests are longer as well? *** (I am using SQL Server with tSQLt framework, but I believe the question is system-agnostic.)

I have an SSDT project containing tSQLt unit tests. I always find when working at home that publishing this and running all tests (from a post deploy script) is problematic (against both localdb and SQL Server developer edition). The publish hangs indefinitely and I eventually have to kill visual studio. The wait type is PREEMPTIVE_OS_AUTHORIZATIONOPS and an example of a statement hung waiting for this (from sys.dm_exec_sql_text) is (@r BIT OUTPUT) SELECT @r = CASE WHEN I.Version = I.ClrVersion THEN 1 ELSE 0 END FROM tSQLt.Info() AS I; I also reproduce this by calling SELECT tSQLt.Private::Info() Which is a simple method public static SqlString Info() { return (SqlString) Assembly.GetExecutingAssembly().GetName().Version.ToString(); } I assume that it is trying to contact the domain controller to establish that I have some permissions or other. I don't get this with other CLR assemblies and so suspect that this may be something to do with TSQLT not being a SAFE_ACCESS assembly (permission set is EXTERNAL_ACCESS). Anyone know what's going on here and how I can fix this and work disconnected from my company's network without encountering this?