Azure PostgreSQL Lesson Learned #3: Fix FATAL: sorry, too many clients already
We encountered a support case involving Azure Database for PostgreSQL Flexible Server where the application started failing with connection errors. This blog explains the root cause, resolution steps, and best practices to prevent similar issues.
Teams is KILLING my Mac Every Day #2
The original thread from shilocase has been locked (https://techcommunity.microsoft.com/t5/microsoft-teams/teams-is-killing-my-mac-every-day/m-p/2790094) so I'm forced to start a new one. I have the same issue like many others, it has been described over and over again. I'll repost the solution here , kudos go to https://answers.microsoft.com/en-us/profile/3c53a3ef-4f6e-46c9-afeb-517cf6ed488c I'm sitting on a Intel Mac. The solution that helped me is described on page 21 here: https://answers.microsoft.com/en-us/msteams/forum/all/ms-teams-helper-using-up-100-of-cpu-macbook/9305b96e-5381-43cf-88c4-d3ba39b37a58?page=21 I deleted the this file: /Applications/Microsoft Teams.app/Contents/Resources/app.asar.unpacked/node_modules/media-hid/build/Release/media-hid.node and now teams is working like a charm. Even my fan is gone and I enjoy the silence. No side effects This needs to be done after each update again it seems. This might even be some hint for the developers that trying to figure out the issue over the last months (years?). So if anyone at Microsoft is listening please be so kind and forward this. Regards and hope it helps you too.
SQL queries have become extremely slow compared to nearly identical queries on the same tables
Hi all. I've been drafting a SQL query from within MS SSMS, as a .SQL file, and then running it from within SSMS, on the same system that is running the 2019 developer edition server. The query file contains around 100 or so queries, virtually all UPDATE queries, which populate rows in a SUMMARYTABLE with results taken from the WORKTABLE. I've noticed that recently, the last 10% or so of these queries have become exceptionally slow - around 500 times slower than comparable queries. Here is an example of one of the fast ones, and one of the slow ones. The first takes less than a second: UPDATE database1.dbo.SUMMARYTABLE SET database1.dbo.SUMMARYTABLE.SalesTierCount = (select count(*) from database1.dbo.WORKTABLE as T1 WHERE T1.Manager = database1.dbo.SUMMARYTABLE.Manager and T1.SalesTier =1 and T1.Zdate < database1.dbo.SUMMARYTABLE.Zdate) the second one takes about seven minutes: UPDATE database1.dbo.SUMMARYTABLE SET database1.dbo.SUMMARYTABLE.LifetimeCount = (select count(*) from database1.dbo.WORKTABLE as T1 WHERE T1.retailer = database1.dbo.SUMMARYTABLE.retailer and T1.SalesTier =1 and T1.Zdate < database1.dbo.SUMMARYTABLE.Zdate) Does anyone have any suggestions for rectifying this extremely slow issue? 2 of the 3 WHERE/AND clauses are identical. The third clause differs, however both queries query the same type of data for that clause - both are varchar(50), in both tables. This is a link to the execution plan for both queries: https://www.brentozar.com/pastetheplan/?id=rypvD7Zb3 The final part of both plans shows an INDEX SCAN (non-clustered). The first, fast query, has the "Number of rows read" of around 200,000, the second, around 436 million. The total time to run the entire .SQL query has blown out from a minute or so, to nearly 4 hours! Any suggestions greatly appreciated.View is taking more time and consuming 100% cpu utilization
We are using views on tables having huge records to display some information in our application but it is consuming 100% CPU utilization and taking more time to process the records. SELECT COUNT(*) FROM FRA_ITEM_FIELD_DEF--44 SELECT COUNT(*) FROM FRA_STAT_ITEM--59635520(59M) SELECT COUNT(*) FROM FRA_STAT_FIELD--536719680(536M) Available Indexes: FRA_STAT_ITEM: CREATE UNIQUE INDEX FRA_STITM_U1 ON FRA_STAT_ITEM (ID_ORIGINAL,ITEM_TYPE,SEQUENCE_NO) GO CREATE INDEX FRA_STITM_U2 ON FRA_STAT_ITEM (REFERENCE) GO FRA_STAT_FIELD: CREATE INDEX FRA_STFLD_OPT1 ON FRA_STAT_FIELD (ID_STAT_ITEM,ID_FIELD_DEF,TEXT_VALUE,DATETIME_VALUE,NUMBER_VALUE,DECIMAL_VALUE) GO FRA_STAT_FIELD: CREATE INDEX FRA_STFLD_OPT2 ON FRA_STAT_FIELD (ID_FIELD_DEF) INCLUDE (ID_STAT_ITEM,DATETIME_VALUE) GO CREATE INDEX FRA_STFLD_OPT3 ON FRA_STAT_FIELD (ID_FIELD_DEF) INCLUDE (ID_STAT_ITEM,TEXT_VALUE) GO Reported View: CREATE OR ALTER VIEW FRAUD_ALERT_STAT_HIT_VIEW AS SELECT DISTINCT STAT.ID AS ID, STAT.COMPANY AS COMPANY, STAT.TEXT_VALUE AS ACCOUNT, STAT.ITEM_TYPE AS FRAUD_ITEM_TYPE, STAT.DATETIME_VALUE AS VALUE_DATE, STAT.CREATION_TIME AS CREATION_TIME, STAT.PAYLOAD AS PAYLOAD, STAT.REFERENCE AS REFERENCE, STAT.SEQUENCE_NO AS SEQUENCE_NO, STAT.ID_MANDATOR AS ID_MANDATOR, STAT.ALERTED AS ALERTED, STAT.STATUS AS STATUS FROM ( SELECT DISTINCT FSI.ID, FSI.ITEM_TYPE, FSI1.DATETIME_VALUE, FSI2.TEXT_VALUE, FSI.COMPANY, FSI.CREATION_TIME, FSI.PAYLOAD, FSI.REFERENCE, FSI.SEQUENCE_NO, FSI.ID_MANDATOR, FSI.ALERTED, FSI.STATUS FROM FRA_STAT_ITEM FSI CROSS JOIN FRA_STAT_FIELD FSI1 CROSS JOIN FRA_STAT_FIELD FSI2 WHERE FSI1.ID_STAT_ITEM=FSI.ID AND FSI1.ID_FIELD_DEF IN (SELECT ID FROM FRA_ITEM_FIELD_DEF WHERE MAPPING='executionDate') AND FSI2.ID_STAT_ITEM=FSI.ID AND FSI2.ID_FIELD_DEF IN (SELECT ID FROM FRA_ITEM_FIELD_DEF WHERE MAPPING='debitAccount') UNION SELECT DISTINCT FSI.ID, FSI.ITEM_TYPE, FSI1.DATETIME_VALUE, FSI3.TEXT_VALUE, FSI.COMPANY, FSI.CREATION_TIME, FSI.PAYLOAD, FSI.REFERENCE, FSI.SEQUENCE_NO, FSI.ID_MANDATOR, FSI.ALERTED, FSI.STATUS FROM FRA_STAT_ITEM FSI CROSS JOIN FRA_STAT_FIELD FSI1 CROSS JOIN FRA_STAT_FIELD FSI3 WHERE FSI1.ID_STAT_ITEM=FSI.ID AND FSI1.ID_FIELD_DEF IN (SELECT ID FROM FRA_ITEM_FIELD_DEF WHERE MAPPING='executionDate') AND FSI3.ID_STAT_ITEM=FSI.ID AND FSI3.ID_FIELD_DEF IN (SELECT ID FROM FRA_ITEM_FIELD_DEF WHERE MAPPING='creditAccount') ) STAT GO Query to fetch and display record in GUI: WITH query AS (select ROW_NUMBER() OVER (order by fraudstats0_.id desc) as __hibernate_row_nr__, fraudstats0_.id as id64_, fraudstats0_.version as version64_, fraudstats0_.alert_type as alert3_64_, fraudstats0_.benef_cust_id as benef4_64_, fraudstats0_.blocked as blocked64_, fraudstats0_.company as company64_, fraudstats0_.creation_time as creation7_64_, fraudstats0_.id_mandator as id8_64_, fraudstats0_.ordering_cust_id as ordering9_64_, fraudstats0_.id_original as id10_64_, fraudstats0_.reference as reference64_, fraudstats0_.sequence_no as sequence12_64_, fraudstats0_.item_type as item13_64_, fraudstats0_.alerted as alerted64_, fraudstats0_.last_evaluation as last15_64_, fraudstats0_.last_eval_time as last16_64_, fraudstats0_.last_eval_user as last17_64_, fraudstats0_.id_live_item as id18_64_, fraudstats0_.payload as payload64_, fraudstats0_.status as status64_ from fra_stat_item fraudstats0_ where fraudstats0_.id in (select fraudstats1_.id from fraud_alert_stat_hit_view fraudstats1_ where fraudstats1_.fraud_item_type='profile.batch.trn.alert' and fraudstats1_.value_date>=@P0 and (fraudstats1_.account in (@P1 , @P2 , @P3) or fraudstats1_.account in (@P4 , @P5 , @P6))) ) SELECT * FROM query WHERE __hibernate_row_nr__ BETWEEN @P7 AND @P8 At present, Archiving/Purging the table is not possible by structure. Could anyone share some suggestion on fine tuning the view?Performance issue when two or more tables are included in the same sheet
Is there any known performance issues when there are two or more tables in a same sheet? Whenever I have more than 1 table in the same sheet then system takes a long time to insert rows or columns in those sheets. I try to avoid having multiple tables in one sheet. But from a user experience perspective sometimes when each of the tables are too small, it doesn't make sense to keep them in two separate sheets. If you could share some suggestions or thoughts, it would be great.
Performance issues in Excel 2016 after resent Update
I've had a few apparent issues after the resent update to Windows and Office 365. I have a Workbook with several worksheets that use VBA and Userforms to to collect and organize data. One issue was with VLOOKUP. When is would attempt to copy the formula to another sell the formula would update to the next row of information, but the value in the cell would remain unchanged. I learned from the Community that there are setting that can affect this and there was. This setting was apparently changed during the update process for everything was working find before hand. Now I discover that the VBA Userforms are running SUPPER SLOWWWW :-(. They perform the task to completion, but data-entry is pointless. Anyone else experiencing anything similar?
