SQL Server
Microsoft SQL Server 2017 powers your entire data estate by supporting structured and unstructured data sources. It builds on previous versions of SQL Server, which have been industry leading for four years in a row and a leader in TPC-E. It scales to petabytes of data and allows customers to process big data through PolyBase using T-SQL over any data. SQL Server has also been the least vulnerable database during the last seven years. SQL Server 2017 brings data insights with business intelligence capabilities that provide analytics at a fraction of the cost on any device along with advanced analytics with support for R and Python.
More details about SQL Server are available in the SQL Server documentation.
If you have a technical issue, please open a post on the developer forums through Stack Overflow or MSDN.
-
Query Store SELECT Performance
Running a query against a large Query Store repository (10 GB) can be really slow if filtering on runtime stats intervals.
It looks like an index is missing on sys.query_store_runtime_stats (runtime_stats_interval_id).
20 votesUpvotes: 3
<=-=May 27 2017 6:38AM=-=>Thank you for taking the time to post this issue! We understand that this could be an important performance improvement.
Performance tuning is of such scenarios is not a trivial task. Change of such feature as Query Store can speed up one scenario and lead to degradation in others. We�ll look more into this particular use case and see what we can do. -
SQL Server Assertion: File: <lobss.cpp>, line = 725 Failed Assertion = '0' Should never happen.
DBCC CHECKDB reports no errors, I rebuilt the indexes just to be sure. It happens when running this query:
WITH t1 AS
(
SELECT [Endpoints].[Name] AS [f1],[MatchLocations].[Location] AS [f2],ISNULL(SUM([Matches].[LastCount]), 0) AS [f3],ROW_NUMBER() OVER (ORDER BY [Endpoints].[Name] ASC) AS [f4]
FROM MatchLocations
INNER JOIN [Matches] ON ([MatchLocations].[Id] = [Matches].[MatchLocationId])
INNER JOIN [Endpoints] ON ([Endpoints].[Id] = [MatchLocations].[EndpointId])
GROUP BY [Endpoints].[Name],[MatchLocations].[Location]
)
SELECT [t1].[f1] AS [t1_f1],[t1].[f2] AS [t1_f2],[t1].[f3] AS [t1_f3]
FROM t1
WHERE ([t1].[f4] BETWEEN 1 AND 100)
ORDER BY [t1].[f4] ASCWhen the SUM and GROUP BY are removed, it runs just fine. It happens from time to time on some databases,…
19 votes -
sql 2017 "cube processing task" ssis adomd client error
A SQL Server 2017 Cumulative Update (somewhere between RTM and CU5) has broken 'Cube Process Tasks' in Integration Services (SSIS). Packages that run fine on SQL 2017 RTM (containing a Cube process Task) FAIL on CU5 and also CU6 with an error related to ADOMDClientUI. The specific error reported in the SSIS log is "Process Cube:Error: Could not load file or assembly 'Microsoft.AnalysisServices.AdomdClientUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91 or one of its components. The system cannot find the file specified."
19 votes -
SSMS screen is not correctly redrawn when switching tabs
Sometimes when switching between tabs in SSMS the screen is not correctly refreshed (redrawn).
The result is a corrupt screen partly showing the previous tab contents and partly showing the newly active tab contents.
In particular this happens when using "Database Diagrams" tabs.This has been a permanent bug through a number of the latest SSMS releases including this release:
Release 16.5.1
File version: 13.0.16100.119 votesUpvotes: 6
<=-=Dec 12 2016 1:42AM=-=>This has been reported more times than you have used SSMS. MS are too incompetent and lazy to fix it.
<=-=Dec 12 2016 1:46AM=-=>Here is the link to another which also has a link to another report
https://connect.microsoft.com/SQLServer/Feedback/Details/3109897
please click on the I CAN TOO in the repros section
<=-=Dec 21 2016 7:39AM=-=>I can replicate this on multiple different computers, each with freshly installed Windows (either 8.1 or 10) and latest of all drivers. It happens both with Intel integrated graphics (for me is HD 4600) and NVidia cards. It’s clearly a bug in how you’re using the Visual Studio shell.
This is absolutely HORRIBLE!! When closing a tab, then attempting to execute the query in a tab that was already open and is now focused (because you just closed one), you see the wrong text in the tab, and…
-
SSMS 17 Tools > Options > Environment > Fonts and Colors missing Display items
In a generic SSMS 17.2 install on a Windows 7 64-bit workstation, if I open Tools > Options > Environment > Fonts and Colors, I only get 3 items in the 'Display items:' list: Plain Text, Selected Text, and Inactive Selected Text. I have no options to change anything else. In my SSMS 2016 install, I have over 200 items in the 'Display items:' pick list. Comparison screenshot here: https://imgur.com/a/XiGMm
I have tried resetting all settings to default in the Import and Export Settings Wizard. No change.
19 votesCould you check if “C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\1033\Microsoft.DataWarehouse.VsIntegration.rll” is missing from your machine?
If it is, you may try two things (either one) to alleviate the pain:
1) Locate ssms.pkgundef (typically at “C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\ssms.pkgundef”)
Edit it and add a line line this at the bottom:
[$RootKey$\FontAndColors\Data Warehouse Designer]
2) Grab the missing file from a machine running the same version of SSMS (it could be a temporary VM with just that binary)It would be helpful if somebody could share (OneDrive or equivalent) the content of the TEMP\SSMSSetup folder (that’s the history of the SSMS setup): it may reveal useful info for us.
Note: there may be sensitive information in those log files (machine names, path names, etc…), so use your best judgement.-Matteo
-
Key Lookup against Columnstore Index Causes Slow Query Execution
On tables with a clustered columnstore index and a b-tree index on the same table, some parameter sets get an execution plan with an index seek against the b-tree, and then key lookup (in row mode) against the columnstore. This is extremely expensive, and most of the queries against larger columnstores never complete. The remedy is to hint the columnstore index in the query. I can supply plans as needed, but apparently can't upload here.
18 votes -
MSOLEDBSQL
When trying to use the new MSOELDBSQL driver as a linked server, any attempt to run a query as simple as
SELECT * FROM SERVER.master.sys.databasesresults in the error message "Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.", even in the trivial case where you set up a loopback server to the same instance.
There is a workaround: if you first say BEGIN DISTRIBUTED TRANSACTION, you can access the linked server.
18 votes -
SSAS Supported Compatibility level is actually showing default Compatibility in SSMS 17.3
In SSMS 17.3 with Analysis Services Client tools 14.0.1008.227 when you click on server properties for an SSAS 2017 instance it shows the "Suppported Compatibility level" as 1200 when it should show 1100,1103,1200,1400.
If you run a DISCOVER_XML_METADATA command against the SSAS 2017 instance you can see the correct data returned for the default and supported compatibility modes, it appears that the SSMS UI has just mapped this incorrectly.
<ddl400:DefaultCompatibilityLevel>1200</ddl400:DefaultCompatibilityLevel>
<ddl600:SupportedCompatibilityLevels>1100,1103,1200,1400</ddl600:SupportedCompatibilityLevels>18 votesUpvotes: 5
-
SSMS 18.2 crash on Extended Events properties form
Opening a "New session…." (not the wizard) of extended events (or a previously existing one) and browsing the pages (Events, Data Storage, Advanced) hangs the windows and/or directly crashes/terminates SSMS with no error message, specially on the Events page.
Application logs shows the crash as an unhandled exception:
Log Name: Application
Source: Application Error
Event ID: 1000
Task Category: (100)
Level: Error
Description:
Faulting application name: Ssms.exe, version: 2019.150.18142.0, time stamp: 0x5d3573be
Faulting module name: System.Windows.Forms.ni.dll, version: 4.8.3752.0, time stamp: 0x5c7a2429
Exception code: 0xc0000005
Fault offset: 0x00c412df
Faulting process id: 0x24a4
Faulting application start time: 0x01d5433d9e072f9d
Faulting application path: C:\Program…17 votes -
Index rebuild removes persisted sample percent
The UPDATE STATISTICS statement has a (relatively) new PERSIST_SAMPLE_PERCENT option, but when rebuilding an index which previously had statistics updated with this value set the persisted sample percent is reset back to default. I would expect the rebuild to create statistics with 100% sampling (which it does), but leave the persisted sample percent intact so that automatic statistics updates would use the previously specified value.
17 votes -
Fix Export/Import of Registered Servers WITH Passwords
I just did an Export of Registered Servers from one machine, running SSMS 17.5, followed by an IMPORT of that file in a machine running SSMS 17.6. Upon exporting, I left the check box blank for "Do not include user names and passwords in the export file". Yes, I am aware that it is a security risk, but I don't want to have to re-enter all my passwords again.
Upon import, I got about a dozen errors stating "Key not valid for use in specified state." (I'm in Connecticut. Does it only work in Washington state?)
Then it says, "Import…17 votes -
Full text search host restarts repeatedly under Linux (SQL Server 2019 rc 1)
Under Linux, after enabling full text search in a table and during the population of the index, one sees frequent messages in the log
Error '0x80004005' occurred during full-text index population for table or indexed view 'mytable' (table or indexed view ID '629173587', database ID '5'), full-text key value '5567999'. Attempt will be made to reindex it.
In the errorlog there are many repeated messages like
The fulltext filter daemon host (FDHost) process has stopped abnormally. This can occur if an incorrectly configured or malfunctioning linguistic component, such as a wordbreaker, stemmer or filter has caused an irrecoverable error during…16 votes -
xp_delete_file on SQL Server 2017/Linux
The xp_delete_file system stored procedure for deleting bakup file does not work on Linux client OS.
Wee tested on Microsoft SQL Server 2017 (RTM-CU1) (KB4038634) - 14.0.3006.16 (X64) Enterprise Edition (64-bit) on Linux (Ubuntu 16.04.3 LTS)Script we used :
DECLARE @BACKUP_DIR NVARCHAR(256);
EXEC master.dbo.xp_instance_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'Software\Microsoft\MSSQLServer\MSSQLServer',
@value_name = 'BackupDirectory',
@BackupDirectory = @BACKUP_DIR OUTPUT;
SELECT @BACKUP_DIR
EXEC master.sys.xp_delete_file 0, @BACKUP_DIR, N'bak', N'21111111';
GOError message :
Msg 22049, Niveau 15, État 0, Ligne 67
Error executing extended stored procedure: Invalid Parameter16 votes -
SSDT 17.2 for VS 2015 - SSIS 2016 packages can't open Analysis Services Processing tasks
I upgraded an SSIS project from 2014 to 2016 using SSDT 14.0.61707.300 in Visual Studio 2015. Upon doing so, whenever I open a Analysis Services Processing Task, I get the following error:
TITLE: Analysis Services Processing Task
------------------------------
Method not found: 'Microsoft.AnalysisServices.ErrorConfiguration Microsoft.AnalysisServices.Commands.BatchCommand.get_ErrorConfiguration()'.
------------------------------If I revert back down to 2014, I can open it just fine. If I create a new AS Processing Task in the same package or new package in the same solution, I get the same error. If I upgrade the SSIS project to SQL2017, it magically works. The issue is only when the target version…
16 votesUpvotes: 13
<=-=Dec 4 2017 3:53AM=-=>Hi Gabe,
I’m having a similar problem, but not only with upgraded packages.
I’m having problems also if I create a completely new package
with Integration Services 2016 in Visual Studio 2015 (SDDT version installed is 17.2).
It works if I use Integration Services 2014 or 2017.I’ve not yet found solution to that issue.
Please let me know if you identify a solution to your issue
-
Performance Dashboard / Expensive Queries / copy query text
When using the reports in the SSMS Performance Dashboard, you are unable to copy the query text from any of the "expensive queries" reports. Make it so you can either click to view the query in a new query window, or right-click the query to copy the text for pasting into a new window.
16 votes -
dark theme
please fix the dark theme problem. thankyou
15 votesCan you provide additional details? This feedback is not specific enough to take action.
-
service broker target not functional on extended events
I filed a question on StackOverflow, if SQL Server 2017 supports a service broker service as a target for extended events. It seems to be configurable, but no events are written to the service broker queue.
Any ideas?
15 votes -
SSIS 2016 packages abort with �Unexpected Termination� message
We have a straight forward implementation in deploying the SSIS 2016 packages (migrated from SSIS 2008 R2) onto the SSISDB and then calling these packages through a SQL Agent job.
The job calls the parent wrapper package and this in turn will call the necessary child packages based of the files that has to be loaded.However, when we execute the same package multiple times, the execution of the child package fails with the status �Unexpected Termination�.
We do not find any error messages within the Execution reports of the SSIS package. This issue will be resolved once we redeploy…15 votesUpvotes: 2
-
SQL 2016/17 Assertion Check Bug
Running the code below will result in a severe error and stack dump, full error message below. Had others test in both 2016 and 2017 with same results. Looks like it's partially an implicit conversion issue, if you explicitly cast to DATE inside the EOMONTH no errors. It also causes the same error if you insert into a table with a datatype of DATE. It will crash even if no data is returned.
Location: e:\b\s3\sources\sql\ntdbms\storeng\dfs\access\rowbucketprocessor.inl:658
Expression: comp_setop_types(*pValueInfo, *pParamInfo) <= 0
SPID: 82
Process ID: 3000
Msg 3624, Level 20, State 1, Line 38
A system assertion check has failed. Check…14 votes -
SSMS - Right-click menu option to "Design" tables configured as Temporal Tables is missing
I am using the new Temporal Table feature on certain tables in my database.
When I navigate to such tables via SSMS Object Explorer, I have found that the "Design" right-click menu option is not present for Temporal Tables. (Or as SSMS labels them, "System-Versioned" tables.)
The "Design" option is, however, still available for NON-temporal tables.
14 votesUpvotes: 0
<=-=Mar 7 2017 2:10AM=-=>Thank you for taking time to post this issue!
I can confirm that Design option in SSMS Object Explorer is not available for temporal (system-versioned) tables.
Unfortunately, we do not plan to include a fix for this issue in the upcoming release.
We might include it as an improvement in a future release, though.
How important for you is to have this option available for temporal tables?
- Don't see your idea?