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.
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)How 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.
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.
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)
Password Expiration Behavior
If I set check_expiration = on for a SQL authenticated login, will the user get prompted to change their password either: after it expires just before it expires not at all I can't force a login's password to EXPIRED or else I'd test it myself and I've not found any documentation on what happens once IsExpired flips to 1.
SQL suppres create info items in MS Event Viewer
Hello, in MS Event Viewer are stored thousand of information events. Stored are tents of events in every seconds (see example screenshot). Why is happened this so often, please? How to suppress (lot of information) such items in MS Event Viewer from SQL, please? Thank you in advance for answers.Restricting Access to Underlying Tables in Different Databases While Allowing View Access
Hi Experts, I am looking to get assistance with the concept of ownership chaining in the SQL Server Database 2016. We have a data warehouse database that is maintained by an ETL Tool. We have segregated objects (e.g. Views, store procedure) from the data warehouse database to the another database on the same server. The non-data warehouse database which references to the Data warehouse database has very complex model of permissions and roles. I am unable to grant access to the objects mainly views in the non data warehouse database without granting select/View definition permissions on the data warehouse database. I'd like to know if it is possible to grant access to the views in the non-data warehouse database without granting direct access on the actual objects like Tables in the data warehouse database. Using windows Microsoft SQL Server 2019 (RTM-CU22-GDR)
