Information Systems software coding for a proprietry Learning Management System (LMS) with complete integration and stringent Active Directory hybrid CISCO Systems/IEEE port map stack to route The University of Western Australia and Harvard University from a hardware SQL Server with RAID-configured Virtual SQL Servers in the cloud-hybrid Multipoint-protocol virtualisation of a composite Internet of Things (IoT) to relay packet signals in real-time with Hbb Broadcasting frequency signals routed through ezy-proxies and our respective Universities for Institution-wide, minimal labour hour information systems administration to maximise positive outcomes of student, faculty, library and student administration for protocol pedagogy and intranet-directed Strategic Management of student and faculty support and services as an alternative to reduce discrete-object administrative and learning material delivery inefficiencies as has been found with third-party LMS such as WebCT and Blackboard through our Universities electronic auto-compiling object errors as is a current area of research in software API universal framework alignment, and primary objective labour hours for increased Research and Institution-wide revenue raising of objective reduction in student intake population numbers to benefit both student study and reading and also commercial intellectual property of private-sponsored research at The University of Western Australia and Harvard University. I am also interested in

SQL Server 2016 introduced Query Store, and SQL Server 2017 CU3 brought us aggregated wait stats at the query level. This is great information for data driven DBAs who need to be able to track plan changes over time, etc.

With that in mind, an additional data collector for wait stats per database would be a great product feature, independent from Query Store data collection.

We all know the limitations of the wait stats DMVs now:

- They're cumulative over time
- They're instance-wide
- There's a lot of potential noise from wait types that aren't registered

If you wanna figure out what's going on with them, you have to log them to a table yourself, or spend a bunch of money on a monitoring tool.

SQL Server should have a database-level option to store wait stats at a user-defined interval, for a user-defined period of time, the same way that Query Store works now. There could be additional options to only log significant waits based on occurrences and duration, etc. To make it extra useful, data could be centralized in msdb (or via another system view, like `sys.all_wait_stats` or something).

This would be an invaluable feedback mechanism for SQL Server users at all levels, especially now that Availability Groups and cloud migrations are more and more common. Admins with SQL Server responsibility need historical data like this to gauge workload efficacy on different hardware.

Thanks for reading!

Feature request: expose information about what indexes the optimizer considered while compiling an execution plan. Specifically, the index names and WHY the index was rejected for that particular query. This could potentially be included in per-operator execution plan properties (see attached mockup), an extended event target, or some other avenue.

Reason: many developers and DBAs wonder why certain indexes on their tables aren't being chosen for specific queries. This gives them better tools to figure that out and make better use of the SQL Server product.

I've blogged about this feature request in more detail here: http://joshthecoder.com/2018/06/21/feature-request-index-rejection-reasons.html

This request was inspired by a feature of the RavenDB document database: https://ayende.com/blog/183425-C/ravendb-4-1-features-explain-that-choice

Just upgraded to 2016 SP2 (13.0.5026.0) and receiving frequent error messages logged from change tracking auto cleanup: Error 22123, severity 16, state 1 Change Tracking autocleanup is blocked on side table of "<***>". If the failure persists, check if the table "<***>" is blocked by any process . Routinely for different tables, not repeatedly from the same table.

I suspect this was added as part of the changes to auto cleanup released in SP2, and is raised when a table is blocked and marked for retry. Can anyone please confirm this?

Additionally, this seems to me like more of an informational status than a user correctable error, doesn't it? I would not think this would be an issue requiring intervention unless it was happening repeatedly and often on the same table.

Finally, according to sys.messages 22123 is NOT set to be event_logged... but it IS logging to the even log each time.

Hi Msft Team!
I'm writing to you to give you a recommendation in SQL Server Always on Feature. Every time that I need to perform a manual failover I need to check some things to avoid any issue. Those are:
1) Nt\authority system account permissions (Otherwise the failover will not be successful)
2) Current transactions running (Otherwise.. All the transactions that are running since long time will be killed on the primary which means that the secondary will turned into recovery state until those transactions finishes)
3) Any delay on long send queue size/Harden/redo queue size /Redone (Example always on session blocked by a read operation on the secondary node) dashboard counters which means that the database after the failover will turn into recovery state
4) High number of VLF delaying the failover process
5) Always on turned into control flow mode because the maximum number of messages was reached
etc etc etc

So, is it possible if you can create a report before executing the failover through the Failover GUI to do a quick health check making sure that the failover will be successful??

Hope this helps!
Thank you!
Regards!

Deploy issue for SSDT database extended property / external tables

Summary:
Deployment of an extended property 'MS_DESCRIPTION' on an external table fails, if the Extended property is scripted together with the external table.

Deployment of an Extended property 'MS_DESCRIPTION' on an external table succeeds, if the Extended property is added in a postdeployment script.

If something is changed, redeployment of the database fails, because the extended property 'MS_Description' can't be deleted.

The following code is produced:
EXECUTE sp_dropextendedproperty @name = N'MS_Description';

This code would be correct:

EXEC sys.sp_dropextendedproperty
@name = N'MS_Description',
@level0type = 'Schema', @level0name = N'schemaName',
@level1type = 'Table', @level1name = N'externalTableName';

Temporal Comparison/Relation Predicates:
CONTAINS, OVERLAPS, EQUALS, PRECEDES, SUCCEEDS, IMMEDIATELY PRECEDES, and IMMEDIATELY SUCCEEDS

Having these predicates would reduce a lot of the effort and pitfalls involved in writing temporally sensitive queries.

I don't know the formal syntax proposed by ISO, but simply having these predicates can reduce up to four separate and hard-to-read expressions into a single one.

Ex:
WHERE (dt1_start, dt1_end) CONTAINS (dt2_start, dt2_end) -- span dt1 is completely within span dt2
WHERE NOT (dt1_start, dt1_end) OVERLAPS (dt2_start, dt2_end) -- no part of span dt1 overlaps span dt2
... etc.

As an addendum, it'd also be nice if the SQL Server teams would put more priority on keeping SQL Server up to speed with more of the ISO standards. There are a lot of other very nice capabilities that have been put forward but are not supported.

An HTML file uploaded to SSRS 2016 SP1 would be identified as mimetype text/html. From the Report Manager portal you could click the link and it would be rendered in a browser window.

After installing 2016 sp2 the mime type for the exact same file is now application/octet-stream. When clicking the link for this file from report manager, it prompts for download instead of opening in a browser window.

This occurs in I.E, Edge, FF and Chrome.

The Use case is uploading an HTML file to include a link/redirect to our data dictionary website to keep our users inside of the report manager portal instead of requiring them to have a bookmark, shortcut etc.

Observe:

declare @xml xml=convert(xml, '<Level1 Id="42">
<Level2 Id="Foo">
<Level3 Id="Bar" />
</Level2>
</Level1>');

SELECT y.value('@Id', 'int') thing FROM @xml.nodes('/Level1') x(y) -- 42
SELECT y.value('../@Id', 'int') thing FROM @xml.nodes('/Level1/Level2') x(y) -- 42
SELECT y.value('../../@Id', 'int') thing FROM @xml.nodes('/Level1/Level2/Level3') x(y) -- "Conversion failed when converting the nvarchar value 'Foo' to data type int"

This only seems to happen when (a) you're going up more than one level in the hierarchy; (b) at least one of the intervening nodes has an attribute with the same name; (c) the type of said attribute is not compatible with the one you're trying to reference (e.g., it will return the correct value if you make all the Id attributes INTs.)

If anyone else runs into this, it can be worked around by casting the result of value(): SELECT CAST(y.value('../../@Id', 'varchar(max)') as int) thing FROM @xml.nodes('/Level1/Level2/Level3') x(y)

...but it would still be nice to see this fixed :)

`OPTION (RECOMPILE)` is needed to get a filtered index to match when the predicate is using variables or parameters.

Recently I had a need to perform conditional logic in a stored proc dependant on whether or not a predicate (matched by a filtered index) contains any rows.

1st attempt

IF EXISTS(SELECT ...)

But no way of applying a query hint to the query in an IF that I can see so no way of adding OPTION (RECOMPILE).

2nd attempt

SELECT @RowsExist = CASE WHEN EXISTS(SELECT ...) THEN 1 ELSE 0 END OPTION (RECOMPILE)

But this doesn't work either as the OPTION (RECOMPILE) doesn't give the parameter embedding optimisation behaviour so the filtered index still not matched.

3rd attempt

Declare a table variable - insert into that the top 1 row with the predicate along with OPTION (RECOMPILE) and finally the filtered index is hit.

This is all quite arcane and cumbersome.