Forum Widgets
Latest Discussions
sp_lock memory leak
I spotted this in the release notes for CU17 in SQL Server 2022: KB3616559 - "Fixes a performance issue that you might encounter only when sp_lock is called frequently from multiple connections, which might cause a memory leak. The memory isn't cleaned up until you restart the SQL Server service. Note: You need to turn on trace flag 15915." We actually have been having an issue that feels like a memory issue since upgrading to SQL 2022, and have engaged MSFT and a few other "expert" SQL support engagements and nobody could figure it out. It's an instance that is acceptable to restart SQL on every weekend, so we've just been doing that, but I'm wondering if this is our issue. I can't find any information from MSFT giving any information on this memory leak they are fixing, and if there is any way to tell by comparing anything before/after that trace flag being enabled to see if behavior changes. Has anyone happened to had this issue, and resolved it with this trace flag by chance? The symptoms we see are essentially everything looking healthy, but every query (including a simple "select 1") just takes 5-10ms more than our normal baseline to execute across the whole instance. And a SQL service restart always fixes it. I think sp_lock is probably called by some of the DB monitoring tools we use, so not something we can easily just stop doing. Thanks in advance for any info anyone may be able to share.SSMS "Intellisense" behaviour is driving me demented
This behaviour is doing my head in and I'm hoping that there is a simple way to change it?Just to give you an example, I am starting to write a little query to check the status of FullText Indexes on database objects. This is for illustration purposes only so don't tell me what "better" alternatives there are for doing this, I just want to illustrate the behaviour that's bugging me.So the query I would like to run is:SELECT [SO].[name], [FI].[is_enabled] FROM sys.objects [SO] inner join sys.fulltext_indexes [FI] on [FI].object_id = [SO].object_idThe behaviour that annoys me can be illustrated as follows. In SSMS, type this:SELECT * FROM sys.objects [SO] inner join sys.fulltext_indexes [FI] on [FI].object_id = [SO].object_idThen go back to the *, remove it and start typing [SO] in order to get intellisense to show you column names you can choose from. So you have typed [SO] and your cursor is right behind the closing bracket:SELECT [SO]<cursor here> FROM sys.objects [SO] inner join sys.fulltext_indexes [FI] on [FI].object_id = [SO].object_id Type the dot The statement changes to:SELECT [SOUNDEX]. FROM sys.objects [SO] inner join sys.fulltext_indexes [FI] on [FI].object_id = [SO].object_idI didn't want "soundex". I wanted intellisense to show me a list of column names in sys.objects, aliased to "[SO]" by me. It does that once I hit Ctrl+Z which removes the auto-inserted [SOUNDEX] and then when I hit the dot again it shows me the list of columns. So I pick [name] and start adding the next column by typing , [FI]. And here it goes again:SELECT [SO].[name], [FILE_ID]. FROM sys.objects [SO] inner join sys.fulltext_indexes [FI] on [FI].object_id = [SO].object_idI didn't want [FILE_ID]. I wanted [FI]. and a popup showing the the column names in sys.fulltext_indexes I can choose from.Sure, this is one heck of a "first world problem" but as a touch typist this is driving me around the bend. If there's a way to change this behaviour (without losing Intellisense altogether), please tell me how.
Changing dates on a sql select query to previous year
I want to be able look at the current years sales by month as in the current sql script. Then I want to look at the same information for the previous year as well. SELECT CASE EXTRACT(MONTH FROM t.transdate) WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN 'Mar' WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'Jun' WHEN 7 THEN 'Jul' WHEN 8 THEN 'Aug' WHEN 9 THEN 'Sep' WHEN 10 THEN 'Oct' WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' END AS "Month", SUM(sm.quantity * sm.exsell) AS "Monthly Sales", SUM(sm.quantity * sm.cost) AS "Sales Cost", SUM(sm.quantity * sm.exsell) - SUM(sm.quantity * sm.cost) AS "Gross Profit" FROM stockmovement sm JOIN transactions t ON t.transref = sm.transref JOIN item i ON i.itemref = sm.itemref JOIN maker m ON m.makerref = i.makerref WHERE t.transource = 'D' AND sm.itemref <> '100037' AND sm.itemref <> '176176' AND t.transdate < CAST(EXTRACT(YEAR FROM DATEADD(1 MONTH TO CURRENT_DATE)) || '-' || EXTRACT(MONTH FROM DATEADD(1 MONTH TO CURRENT_DATE)) || '-' || '01' AS DATE) AND t.transdate >= CAST(EXTRACT(YEAR FROM DATEADD(-1 YEAR TO CURRENT_DATE)) || '-' || EXTRACT(MONTH FROM DATEADD(-1 YEAR TO CURRENT_DATE)) || '-' || '01' AS DATE) GROUP BY EXTRACT(MONTH FROM t.transdate) ORDER BY EXTRACT(MONTH FROM t.transdate)
Insert & Update both implemented wrong semantically
Insert means to introduce something, adding/entering something new Update means to change something, change and existing value. Semantically and logically speaking, Insert should only be used to enter data into an empty or partially empty record/table. Update should only be used to change existing values within a record/table. Using a 5 column table as an example where column 1 is an email address Example1 INSERT INTO table (Column1,Column2,Column3) VALUES (value1,value2,value3) Or Example2 INSERT INTO table VALUES (value1,value2,value3,value4,value5) With example 1, 2 columns are left empty Semantically, insert should be used to populate columns for the first time INSERT INTO table (column4, column5) VALUES (value4,value5) where column1 = "EmailAddress" Then for example, UPDATE table SET COLUMN2 = "newValue" WHERE COLUMN1 = "EmailAddress" Updating (setting) an empty column should cause an error, cannot update an empty column SQL, a great idea, pity its implementation is tainted by bad design. SQL works, but its design/implementation of Insert & Update is semantically wrong. IMO As the saying goes, if it isn't broken, don't fix it. Who cares about semantics or logic.
SERVERPROPERTY('IsClustered') does not return the correct value in a cluster configuration
Hi all! I'm testing some t-sql SQL properties for a data collection project and I got a strange case with the query below: SELECT SERVERPROPERTY(''MachineName') AS [MachineName], SERVERPROPERTY('ServerName') AS [ServerName], SERVERPROPERTY('InstanceName') AS [Instance], SERVERPROPERTY('IsClustered') AS [IsClustered], SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS], SERVERPROPERTY('Edition') AS [Edition] I have two test instances of SQL Server 2019. They both belong to a cluster without shared storage, as the availability groups allow it: SQLAGCLU I don't understand why the query is returning "incorrect" properties. I expected them to have the value 1 for the "IsClustered" column. From the query that I use, I have no idea witch SQL instance are in a cluster... Some one can give a feedback about it? Thanks ALENHow to communicate with Linked Server encryption
I'm using SQL SERVER's Linked Server to link between databases. But there is a security problem with this method. So is there a way to encrypt the communication section when accessing other databases through Linked Server and importing data? Please let me know if you have any good method or experience applying it Thank you.SSAS 2022 Connections fail following restart
I'm using an application which has SSAS 2022 OLAP cubes at the back end. We are having an issue that whenever we restart the server or the service, the connections to the SQL Server that is the data source break. I suspect this is a consequence of SSAS CU1 behaviour where the connection string is encrypted, but - because they get encrypted - there's no way to identify what the change is. SSAS is on the same instance as the SQL Server. Before a restart, i've tried adjusting a few connection properties, notably Impersonation set to Service Account Trust Server Certifcate to True Encryption for data to Optional The connection works fine with these settings. However, post reboot I get a connection error whenver I try toprocess any objects: Errors in the back-end database access module. No provider was specified for the data source. We are using MSOLEDB19 so should be fine, but it seems that post reboot the encrypted connection is somehow misconfiguring. Appreciate any guidance on what could be happening here? I can't avoid restarting the server as org policy demands servers are rebooted every fortnight.Compatibility change from 110 to 160 doubles the CPU
We are running SQL Server 2022, when we change our database from compatibility level 110 to 160, the CPU utilization eventually doubles. After switching to compatibility 160 I do run update statistics FULL on the whole database but I am unable to figure out which queries are the ones that jump to using more CPUs than on compatibility level of 110. Our frequently used queries are dynamic SQL so I am having difficulty identifying their execution stats before and after. I need some help. Thank you.
sql-machine-learning-services setup: RegisterRext.exe aborts with “MPI installation was not found”
In a SQL Server 2022 environment I have a problem in setting up the sql-machine-learning-services. When Configure Python runtime with SQL Server, executing the RegisterRext.exe command results in an error message “MPI installation was not found” I have been following the steps at this site https://learn.microsoft.com/en-us/sql/machine-learning/install/sql-machine-learning-services-windows-install-sql-2022?view=sql-server-ver16 In The Python section, an error occures when executing .\RegisterRext.exe /configure /pythonhome:"C:\Program Files\Python312" /instance:"MSSQLSERVER" Error: …. Granting NT Service\MSSQLLaunchpad ReadAndExecute permission to C:\Program Files\Python312 ... Granted NT Service\MSSQLLaunchpad ReadAndExecute permission to C:\Program Files\Python312 Error: Exception occurred in Settings Install Failed to complete the operation successfully. Usage …. The logfile RegisterRExt.log shows the detailed error: Granted NT Service\MSSQLLaunchpad ReadAndExecute permission to C:\Program Files\Python312 -- DateTime=2024-01-07T08:41:21.2670000Z --Error: 0 : System.Exception: Exception occurred in Settings Install ---> System.IO.DirectoryNotFoundException: MPI installation was not found. -- bei Microsoft.SqlServer.Server.Settings.GetFirstValidPath(IEnumerable`1 paths, String exceptionString) -- bei Microsoft.SqlServer.Server.Settings..ctor(ParsedArguments parsedArgs, String instancePath, Boolean isXcopyInstall, Boolean isAppContainerEnabled, String sqlExtDataPath) -- bei Microsoft.SqlServer.Server.Settings.Install(ParsedArguments parsedArgs, String binnPath, Boolean isXcopyInstall, FileSecurity fileSecurity) -- --- Ende der internen Ausnahmestapelüberwachung --- -- bei Microsoft.SqlServer.Server.Settings.Install(ParsedArguments parsedArgs, String binnPath, Boolean isXcopyInstall, FileSecurity fileSecurity) -- bei Microsoft.SqlServer.Server.Program.Main(String[] args) -- DateTime=2024-01-07T08:41:21.2826203Z --Information: 0 : Failed to complete the operation successfully. Environment is Microsoft SQL Server Express (64-bit) ( SQL Server 2022 (RTM-GDR) (KB5032968) - 16.0.1110.1 (X64)) ,Windows 10 , Version 22H2 Python 3.12.1 After installing and setup MPI the error remained the same. I need help regarding this problem.
Getting SQL support from Microsoft
Any got a trick to open a ticket with MS for SQL support (Paid)? We have CSP licenses, but CSP will not take a SQL ticket. I tried online, but it makes you use a personal account, then errors out. When I phone it says you can only open SQL tickets with web. We just have a urgent problem, and I think it's simple for a SQL expert. Should not be this hard to get support. Used to be easy. (BTW I am the MSP - they cut off our old way of opening tickets)
