SQL Server

Microsoft SQL Server 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 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.


  • Hot ideas
  • Top ideas
  • New ideas
  • My feedback
  1. An internal error happened while generating a new DBTS for database "test_db". Please retry the operation.

    This error happens during INSERT/UPDATE queries, even simple ones, if the server is busy for example restoring another database

    The SQL Server is the Enterprise version, it has the SP1 applied.

    SELECT @@VERSION produces: Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

    It runs on VMWare 5.5 build 4180647.

    The OS is Windows 2012 R2 Standard, no SP applied. The server is configured with 4 vCPUs and 80 GB of RAM. Disks are handled…

    11 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    8 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  2. QDS_LOADDB Wait type blocks all queries

    After restoring a database with Query Data Store enabled, all queries will encounter the QDS_LOADDB wait while the query store is starting up. They will not finish and no data can be retrieved from the database during this time. This is on Enterprise V 13.0.4422.0.

    11 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 6

    <=-=May 27 2017 7:48AM=-=>

    Thank you for taking the time to post this issue! We understand that this could be an important performance improvement.

    This happens because Query Store information is loaded in synchronous mode. In future releases, we will work on changing it to async mode. As a workaround try to apply trace flag number 7752
    Trace flags are described here (https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql)

    <=-=Nov 8 2017 10:13AM=-=>

    We have experienced the same issue. SQL logs showed ramp up in memory waits then a large spike QDS_LOADDB for 3 hours preventing any execution.
    Query Store Disabled for now.

  3. SSMS 17.3 excessive memory usage

    SSMS consumes large amounts of memory & CPU, even when idle. Disabling intellisense has no effect

    11 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    7 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  4. Partition Table using min/max functions and Top N - Index selection and performance

    Partitioned Tables performance issues - For select statements using Min and Max functions and Top N with ordering over an index.

    Poor performance is being detected for Queries on partitioned tables utilising the min and max functions and select Top N clause with "order by" matching columns of the index.

    The candidate index is either not being used or is being used to scan or seek all rows rather than a subset.

    11 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    2 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 125

    <=-=Aug 10 2007 9:17PM=-=>

    Hi Microsoft, please confirm this optimizer problem by running the scripts, it is self evident and can be optimised significantly better.

    Eg. For a minimum, the optimal plan is to obtain the minimum for each partition using the index and then taking the minimum of all the (partition) results. The same principle can apply to maximum, and for Top N over an index …

    The plan should stand out as optimal, because the number of reads over the index is very small, and there will be one set of page reads (usually only one page) for each partition…. you can use the assumption that the number of partitions is significantly smaller than the number of rows (or even count the number of partitions), to estimate the cost (page reads) – which comes out at about N pages where N is the number of partitions.…

  5. EXCEPTION_ACCESS_VIOLATION when returning inlined UDF with UserDataAccess from stored procedure

    SQL Server version: Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)
    Script to reproduce erorr:

    USE tempdb;
    GO

    DROP TABLE IF EXISTS dbo.testtbl;
    GO
    CREATE TABLE dbo.test
    tbl(val int NOT NULL);
    GO

    INSERT INTO dbo.test_tbl(val) VALUES (0);
    GO

    CREATE OR ALTER FUNCTION dbo.test_fn(@val int)

    RETURNS INT
    
    WITH SCHEMABINDING

    AS
    BEGIN

    IF EXISTS(SELECT val FROM dbo.test_tbl) -- UserDataAccess
    
    RETURN @val;
    RETURN 1;

    END
    GO

    CREATE OR ALTER PROCEDURE dbo.test_proc(@val int)
    AS

    RETURN dbo.test_fn(@val);
    

    GO

    EXEC dbo.test_proc @val = 10;
    GO

    10 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    1 comment  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  6. Non-yielding Scheduler on SQL Server 2016 (SP2-CU2) (KB4340355)

    Non-yielding Scheduler on SQL Server 2016 (SP2-CU2) (KB4340355)

    On a production server, with stable workaround, we have encountered a "non-yielding scheduler" error and system got frozen for 4 minutes, time needed to generate the stack dump:

    Call stack from the minidump:
    00 ntdll!NtSignalAndWaitForSingleObject+0xa
    01 KERNELBASE!SignalObjectAndWait+0xc8
    02 sqldk!SOSScheduler::Switch+0x106
    03 sqldk!SOS
    Scheduler::SuspendNonPreemptive+0xd3
    04 sqldk!WorkDispatcher::DequeueTask+0x59c
    05 sqldk!SOS_Scheduler::ProcessTasks+0x232
    06 sqldk!SchedulerManager::WorkerEntryPoint+0x2a1
    07 sqldk!SystemThread::RunWorker+0x8f
    08 sqldk!SystemThreadDispatcher::ProcessWorker+0x2de
    09 sqldk!SchedulerManager::ThreadEntryPoint+0x1d8
    0a kernel32!BaseThreadInitThunk+0x22
    0b ntdll!RtlUserThreadStart+0x34

    Call stack for the process not yielding:
    00 ntdll!NtGetContextThread
    01 KERNELBASE!GetThreadContext
    02 sqldk!SOSOS::GetThreadControlRegisters
    03 sqlmin!SystemThreadTable::MoveToNextRow
    04 sqlmin!SystemThreadTable::InternalGetRow
    05 sqlmin!CQScanTVFStreamNew::GetRow
    06 sqlmin!CQScanProfileNew::GetRowImp<1>
    07 sqlmin!CQScanCountStarNew::GetRowHelper
    08 sqlmin!CQScanStreamAggregateNew::GetCalculatedRow
    09 sqlmin!CQScanProfileNew::Open
    0a sqlmin!CQScanConcatNew::GetRow
    0b sqlmin!CQScanProfileNew::GetRowImp<1>
    0c

    10 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  7. VS2017 and SSDT publishing Graph Database objects created before schemas

    VS 15.7.4
    SSDT 15.1.61804.210

    We created a new SQL Server Database Project in VS2017 and imported a database that uses Graph DB features.

    Trying to publish the project we receive and error indicating one schema is missing (a custom schema that contains graph database objects).
    We dug into publish t-sql script and we found that CREATE TABLE statements for graph db edge tables were placed before CREATE SCHEMA statements, therefore, publish won’t ever complete successfully.

    To complete deploy we had to move CREATE SCHEMA statements above and run the script manually.

    10 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  2 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  8. Unable to script out ExternalDataSource of type BLOB_STORAGE.

    It appears SSMS v17.7 does not know how to script out the external data source. I am getting this error when attempting to script out an existing data source.

    TITLE: Microsoft SQL Server Management Studio

    Unexpected value for enumeration type 5. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17254.0+((SSMSRel17_4).180502-0908)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.WrongPropertyValueExceptionText&LinkId=20476

    Attempting to look at Properties in the right click context menu also produces an error

    TITLE: Microsoft SQL Server Management Studio

    Cannot show requested dialog.


    ADDITIONAL INFORMATION:

    Invalid DataSourceType (SqlManagerUI)

    Here is the version information:
    Microsoft SQL Server Management Studio 14.0.17254.0
    Microsoft Analysis Services Client Tools 14.0.1016.251
    Microsoft Data Access Components (MDAC)…

    10 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    started  ·  6 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  9. Merge statement Delete does not update indexed view in all cases

    A merge statement that contains both an update clause and a delete clause, does not always update indexed views that refer the table.

    Specifically, if the delete clause is the one that is triggered, and the update clause does not update any of the columns that are used by the indexed view, the deleted row is not removed from the indexed view.

    The indexed view is therefore logically inconsistent; it contains rows that are no longer part of the source table. An SQL script that demonstrates the bug is included.

    I have replicated the bug both on SQL Server 2014…

    10 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  1 comment  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  10. Incorrect Semi Join Cardinality Estimation

    Both "old" and "new" cardinality estimators produce an incorrect estimate for an apply semi join with predicates pushed below a union all on the inner side.

    The incorrect estimate is a fixed 100% of the input cardinality for the 120 model CE, and 90% for the 70 model.

    Full details at http://sqlperformance.com/2014/07/sql-plan/subquery-cardinality-estimation-bug

    10 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  11. SQLPackage.exe - Needs to be made into at least 3 cmdlets

    SQLPackage.exe - Needs to be made into at least 3 cmdlets (and possibly more; we have added ideas for additional cmdlets below). The first 3 cmdlets that need to be made into are:
    Export-SqlDatabase
    Import-SqlDatabase
    Compare-SqlDatabase

    Export-SqlDatabase
    At a minimum Export-SqlDatabase should take parameters such as -ServerInstance -Database -TargetFile -IncludeData (ExtractAllTableData).

    It would also be nice if these cmdlets would work 'smartly' from within the provider. For instance, if you were within the provider inside a database (PS SQLSERVER:\sql\localhost\default\databases\AdventureWorks> ) and wanted to run Export-SqlDatabase you should only have to supply a filename for your export; the cmdlet should infer…

    10 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 55

    <=-=Apr 5 2016 10:09AM=-=>

    first FIX DACfx then the important verbs /publish etc

    <=-=Apr 5 2016 12:30PM=-=>

    PowerShell wrappers around DacFX seems like a good idea. Some brainstormed thoughts:
    -The word “publish” is part of the DacFX lexicon. Should Import-SqlDatabase not be called Publish-SqlDatabase? And if so, shouldn’t it be called Publish-Dacpac. Strictly speaking its not the database that’s being published, its the dacpac. Regardess of this bullet point, I’ll refer to Import-SqlDatabase in the rest of this comment.
    -Similarly I think Export-SqlDatabase should be called Export-Dacpac
    -Love the idea of piping Export-SqlDatabase to Import-SqlDatabase
    -Import-SqlDatabase needs an option to only produce the change script, not execute it.
    -Compare-SqlDatabase should have the option to compare a dacpac to a dacpac
    -Generating a DriftReport feels to me like a different cmdlet rather than being an option of Compare-SqlDatabase

    <=-=Apr 6 2016 6:12AM=-=>

    I’ve created a wrapper that accomplishes Publish…

  12. cannot change GridResults background color

    I still cannot change the background color on the grid results pane in SSMS. Changing the foreground color works, and I can change the background color on the text editor without issues, but it does not work for grid results. This has been broken since SQL 2005. WTFFFFFFF

    10 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    2 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  13. Live Query Statistics only showing the first query in a batch

    In SSMS 13.0.16000.28, click Query, Include Live Query Statistics.

    Start a multi-batch query where each query takes a non-trivial amount of time, like this:

    DECLARE @DontGoToEngland TABLE (Stuffing NVARCHAR(MAX));
    INSERT INTO @DontGoToEngland
    SELECT text
    FROM sys.messages;
    INSERT INTO @DontGoToEngland
    SELECT text
    FROM sys.messages
    ORDER BY text;
    INSERT INTO @DontGoToEngland
    SELECT text
    FROM sys.messages
    ORDER BY text;

    Execute the query, and only the first query's Live Query Statistics shows up as the batch runs (in this case, the first insert.) The Live Query Statistics no longer updates after the first query finishes - until the entire batch finishes. The status bar…

    10 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    2 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 4

    <=-=Dec 4 2016 5:14PM=-=>

    Just to be clear – this is different than the fixed bug 1051306, which was about only showing the current query. (Now it just only shows the first.)

    <=-=Apr 26 2017 2:29PM=-=>

    This isn’t fixed in 14.0.17099.0 (the new SQL Server Management Studio 17 that went out today.)

  14. Debugging Stored Procedure in SQL Management Studio

    While debugging the yellow cursor-line will become misplaced (see attached image)

    10 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    unplanned  ·  6 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  15. LocalDb Data Defaults Data Directory to User Profile Root. Location cannot be changed via SSMS

    LocalDb for some reasons sets the default data directory to the root of the user profile directory. This means all database files, logs and backups clutter the root of this directory. There does not seem to be a way to change the default data locations. Trying to change the option in SSMS Server Properties -> Database Settings results in a RegCreateKeyEx() returned error 5, "Access is denied" error (Microsoft Sql Server, Error: 22002). Files by default should go into a sub directory of the user profile e.g Databases and this should be able to be changed via SSMS.

    10 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    6 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  16. Use More Than Density to Cost a Scan on the Inner Side of a Nested Loop with TOP

    Consider a simple query of the following form:

    SELECT o.ID
    FROM dbo.OUTER o
    WHERE NOT EXISTS
    (

    SELECT 1
    
    FROM dbo.INNER i
    WHERE o.ID = i.ID

    );

    Sometimes the query optimizer will implement the join as a left anti semi nested loop join
    with a TOP operator applied to the scan on the dbo.INNER table. The row goal introduced
    by TOP aggressively reduces the cost of the repeated scan on the dbo.INNER table.
    The density of the table is used in the calculation to reduce the cost of the scan.
    Using the density means that the query optimizer assumes that…

    10 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    1 comment  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  17. Keep system sessions from entering a single-user database

    I am super hesitant to put a database into singleuser mode without being in the database because system sessions (sessionid <50) will get into the database and I cannot get the sessions out. I can't kill them because they are system sessions and the KILL command doesn't work.

    This comes into play when I am in the database, set the database into single_user mode, get out of the database and have a slight delay and then try to do something with the database (such as backup the log with norecovery).

    10 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  4 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  18. sys.fn_hadr_backup_is_preferred_replica causes error 41070 in SQL Server 2017 CU15

    After installing CU15 on a SQL 2017 2-Node Always On Availability Group Cluster (Windows Server Failover Clustering Mode), sys.fnhadrbackupispreferred_replica throws the following exception when run against a database that is in an availability group:

    Msg 41070 - Configuration data for the availability group with Windows Server Failover Clustering (WSFC) resource ID 'GUID' is not found in the WSFC data store. The availability group may have been dropped, or a previous CREATE AVAILABILITY GROUP or DROP AVAILABILITY GROUP operation has failed. Please use DROP AVAILABILITY GROUP command to clean up previously failed operations before retrying the current…

    10 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    7 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  19. SQL Server 2019 CTP 2.3 / Issue with scope of set identity_insert on/off

    Hello!

    I have SQL Server 2019 CTP 2.3 installed, and I have detected a change in the behavior of:

    SET IDENTITY_INSERT tabname ON/OFF

    It appears that the scope has changed since SQL Server 2019 CTP 2.2 and SQL Server 2017:

    I am using MS ODBC 17 ...

    When executing some SQL from ODBC with SQLExecDirect() with parameters, I see in my Extended Events log that the spexecutesql stored procedure is used, and in such case, a prior SET IDENTITYINSERT ... ON has no effect on the SQL executed inside the stored procedure.

    By no effect I mean that…

    10 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    9 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  20. SSMS 17.9.1 Activity Monitor pauses when open Processes tab

    When running SSMS 17.9.1 Activity Monitor to SQL Server with around 2100 connections listed in sys.dmexecconnections and 1700 in sysprocesses and 1600 rows in sys.dmexecsessions with an active workload the query Activity Monitor pauses and tracing SSMS using the tracing in Tools\Binn\ManagementStudio\Ssms.exe.config shows a query timeout after 30 seconds:
    <system.diagnostics>

    &lt;sources&gt; 
    
    &lt;source name=&quot;global&quot; switchName=&quot;SourceSwitch&quot; switchType=&quot;System.Diagnostics.SourceSwitch&quot; &gt;
    &lt;listeners&gt;
    &lt;add name=&quot;logFile&quot; type=&quot;Microsoft.SqlServer.Diagnostics.STrace.LogFileTraceListener, Microsoft.SqlServer.Diagnostics.STrace, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91&quot; logPath=&quot;C:\temp\logs&quot; &gt;
    &lt;/add&gt;

    Capturing the query Activity Monitor runs and running the query directly in SQL Query Analyser completes after 38 seconds:
    WITH profiled_sessions as (

       SELECT DISTINCT session_id profiled_session_id from sys.dm_exec_query_profiles
    
    9 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    3 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  • Don't see your idea?

SQL Server

Categories

Feedback and Knowledge Base