Uncovering Hidden Bottlenecks in SQL Server Execution Plans
As someone learning SQL Server, I'm trying to deepen my understanding of execution plans and how SQL Server processes queries. This seems like a crucial topic for writing efficient and optimized SQL. Here are some points I’m curious about and would love to discuss: 1. Reading Execution Plans: - How do I interpret the graphical execution plans in SQL Server Management Studio (SSMS)? - What are the key operators I should focus on? 2. Query Optimization: - What common issues can I identify in an execution plan that indicate a poorly performing query? - Are there specific cases where SQL Server's query optimizer might make suboptimal decisions? 3. Indexes and Their Impact: - How do indexes influence execution plans? - What are the best practices for creating and maintaining indexes to improve performance? 4. Real-World Examples: - Are there any real-world scenarios or examples of optimizing queries based on execution plans? - What were the before-and-after results? 5. Tools and Resources: - Beyond SSMS, are there other tools or resources (e.g., books, blogs, videos) to better understand execution plans? I’d love to hear from the community about your experiences, tips, and insights regarding execution plans and query processing in SQL Server. All perspectives, whether from beginners or those with more experience, are welcome and can make this discussion valuable for everyone involved! Let’s dive into it!
SQL Server Config Manager Error "MMC could not create the snap-in"
Hi, I have seen this error elsewhere online. I have gone to mmc to enable the snap in and I still have had no fix. My computer is running Windows Server 2022, SQL Server Express 2022, and SSMS. I have reinstalled, repaired, and all of the other tricks. Help!
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.
Need help with an SQL query without using a cursor
Hi there to all SQL gurus So, here is the scenario. I have a #temp table in one of my SQL stored procedures which has only 2 columns, say Customer ID and Profile ID, and it has the below data Customer ID Profile ID 100001 ABCD001 100001 ABCD002 100002 ABCD001 100002 ABCD002 100003 ABCD001 I need to write a query which selects only the Profile ID which is mapped to all the Customer IDs. In this case Customer ID 100001 and 100002 have both ABCD001 and ABCD002, but Customer ID 100003 has only Profile ID ABCD001, so, the output of the SQL should have only ABCD001. How do I do this without using a CURSOR? Would a CTE help? I am not very familiar with CTE, so if the solution is using a CTE, please give your suggestions in more detail. Thanks in advance
Adventureworks data does not load in SSMS
Hello, I am a new member here so forgive me if I am not posting in the right spot. I have been trying to load data using a script for the AdventureWorks database. I used the creation script from the following link: https://learn.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver16&tabs=ssmsver After executing the script, I get "Query completed with errors", but I do not actually know what the errors are. The database was added and I see all of the tables, but when I query the tables, there is no data. For context, I am using a macOS and running a virtual machine with Windows 11 and SQL Server 2019. I am also using SSMS 2022.SSMS 20.1 constantly crashing (suspecting .NET issue)
I cannot make SSMS to work. After installing SSMS 20.1 (or any other version) it constantly crashes. I get various errors such as: Cannot create the window Exception has been thrown by the target of an invocation JIT compiler encountered an internal limitation Crashing after the splash screen without any error message Losing profile data Crashing after login Crashing after various operations (SELECT TOP 1000 from the context menu etc., when starting various dialogs etc.) Examining the event viewer I suspect a .NET issue. Some examples: Faulting application name: Ssms.exe, version: 20.1.10.0, time stamp: 0x660d7b89 Faulting module name: clrjit.dll, version: 4.8.9241.0, time stamp: 0x6604a357 Exception code: 0xc0000005 Fault offset: 0x00004964 Faulting process id: 0x3b28 Faulting application start time: 0x01dac5542e795318 Faulting application path: C:\Program Files (x86)\Microsoft SQL Server Management Studio 20\Common7\IDE\Ssms.exe Faulting module path: **C:\Windows\Microsoft.NET\Framework\v4.0.30319\clrjit.dll** Report Id: 9cf7bd0e-2bdc-48f9-ac31-575919dab13a Faulting package full name: Faulting package-relative application ID: Application: Ssms.exe Framework Version: v4.0.30319 Description: The process was terminated due to an unhandled exception. Exception Info: System.AccessViolationException at System.Windows.Media.Visual.GetDpi() at System.Windows.FrameworkElement.MeasureCore(System.Windows.Size) at System.Windows.UIElement.Measure(System.Windows.Size) at System.Windows.Interop.HwndSource.SetLayoutSize() at System.Windows.Interop.HwndSource.set_RootVisualInternal(System.Windows.Media.Visual) at System.Windows.Interop.HwndSource.set_RootVisual(System.Windows.Media.Visual) at System.Windows.Window.SetRootVisual() at System.Windows.Window.SetRootVisualAndUpdateSTC() at System.Windows.Window.SafeCreateWindowDuringShow() at System.Windows.Window.ShowHelper(System.Object) at System.Windows.Window.Show() at Microsoft.VisualStudio.PlatformUI.WpfHostPrivate+UIWPFElementContainer.ShowWindow() I also noticed that if I am unsuccessful the first time I try to run SSMS, then I'm doomed until I restart and try again. I already tried the following: I reinstalled my PC few times. Tried both with Windows 10 and Windows 11 fully updated, but still no luck when it comes to running SSMS correctly. I tried .NET Framework repair tool. No luck. I tried repairing SSMS. No luck. I tried uninstalling and installing .NET (Windows features On/Off). No luck. On a manual installation it says a current/newer version is already installed. Everything was fine until few months ago. Then SSMS started crashing. I suspect some Windows update may have caused this because now it doesn't work even on a freshly installed and updated PC. Any solution?
SQL Server installation error on Windows 11 Pro system
Hi All, I'm facing the decimal error code while installing the SQL Server 2022(Developer edition) on my local system. I tried the few of the methods suggested which are - 1. removing all components of previously installed instance. 2. Running setup.exe file with admin access. 3. Turn off the firewall. Didn't get expected results. It would be very helpful if someone support me. Thanks in advance. ERROR - exit code decimal 2068052377 Error Description: Invalid command line argument. Consult windows installer SDK for detailed command line help. Regards, ManuNeed help for building a storeprocedure
Hi all, Urgent help is required. There are two pages now in my application. One is buildingblock page and another is volume page. Now in the buildingblock page I am choosing building blocks(Origin/destination type). Under each BB there are locations which are called as OriginCX/Destination Cx based on which BB type it is. I am making all possible pairs of them like origin - destination combinations and then in volume page I am displaying them as records in a grid with some other input fields. Example scenarios which are NOT passing now- Consider choosing 1 OriginCx and 1destinationCx of each BB type and they are the same location then after going to Volume page we can find a single record. When we are saving the record data and coming back to BB page then if we are deselecting either one location and saving data in BB page. Then moving forward no alert is being shown that location has been modified Another scenario which is also failing now is - If I am choosing a single BB location maybe as origin/destination and saving in BB page and moving forward to volume then we can find a dropdown with all available cities location to choose. Here if I am choosing any city and saving the data then alert is coming as Location has been modified. This condition no alert should come. This is all of my business logic
