data-konzept

'Takeaway: I provide a script that looks at the procedure cache and reports tables that are never joined to other tables.'...

http://michaeljswart.com/2019/03/lonely-tables-in-sql-server/

'You are never too old to learn an important lesson. Like most programmers, I read programming language documentation for one of two reasons. 1. You have just found out about something new 2. You find out that you were wrong about something you thought you had read the first time, probably 10 years ago. Today’s blog came from an idea that arose from a mistake I made where I made a faulty assumption, and kinda exploded from there into multiple possibilities. This mistake was followed up by reading the documentation for the statement I used incorrectly.

One of my favorite SQL commands has been QUOTENAME. A common programming need when generating code is the need to surround a string value with quotes, and escape any characters that are the same as you are surrounding the string with, with doubles (and if you need doubles of the character in the string, you then need four of the characters.) For example, to surround the following string with single quotes (‘)'...

https://www.red-gate.com/simple-talk/blogs/how-to-and-not-to-escape-a-string-in-tsql/

'In the course of monitoring performance or troubleshooting a problem such as system slowness, it may be necessary to find or capture queries that have high duration, high CPU, or generate significant I/O during execution. You can use the DMVs or Query Store to get information about query performance, but the information in both sources is an aggregate. The DMVs represent average CPU, I/O, duration, etc. for a query only for as long as it’s been in cache. Query Store also provides average metrics for numerous resources, but it’s aggregated over a defined window of time (e.g. 30 minutes or one hour). There are of course 3rd party monitoring solutions that are more than capable of giving you all of this and more (like SentryOne), but for this post I wanted to focus on native tools.

If you want to understand query performance for individual executions to pinpoint the exact query or set of queries which might be a problem, the easiest option is to use Extended Events. And one of the quickest ways to get started is to use XEvent Profiler, which is available through SQL Server Management Studio (starting in version 17.3)'...

https://sqlperformance.com/2019/02/extended-events/capture-queries-sql-server

'With Power BI Desktop, you can access SAP Business Warehouse (BW) data.

For information about how SAP customers can benefit from connecting Power BI to their existing SAP Business Warehouse (BW) systems, see the Power BI and SAP BW whitepaper. For details about using DirectQuery with SAP BW, see the article DirectQuery and SAP Business Warehouse (BW)'...

https://docs.microsoft.com/en-us/power-bi/desktop-sap-bw-connector

'Power BI reports can connect to a number of data sources. Depending on how data is used, different data sources are available. Data can be imported or data can be queried directly using DirectQuery or a live connection to SQL Server Analysis Services.

These data sources are specific to Power BI reports used within Power BI Report Server. For information about data sources supported with paginated reports (.rdl), see Data Sources Supported by Reporting Services.'...

https://docs.microsoft.com/en-us/power-bi/report-server/data-sources

'If you’re even thinking about experimenting with, let alone actively using, Azure Data Studio, you need to plan on installing a few extensions. Buck Woody has a great list that you should look through in this blog post. If you’re just getting started with Azure Data Studio, I have an introduction here.

Depending on the extension, this could be a simple as a mouse click. However, not all the extensions are that easy. Let’s explore this just a little so when you do start using Azure Data Studio, things are easy.'...

https://www.scarydba.com/2019/02/18/installing-extensions-to-azure-data-studio/

'SQL Server 2019 preview builds on previous releases to grow SQL Server as a platform that gives you choices of development languages, data types, on-premises or cloud, and operating systems. This article summarizes what is new for SQL Server 2019 preview. For more information and known issues, see the SQL Server 2019 preview Release Notes.'...

https://docs.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-ver15?view=sqlallproducts-allversions

'There are multiple ways to achieve this and there are also some storage types you should be aware

There is a good doc about this at https://docs.microsoft.com/en-us/azure/sql-database/sql-database-file-space-management

Understanding the following storage space quantities are important for managing the file space of a database.

• Data space used

  • The amount of space used to store database data in 8 KB pages. Generally, space used increases (decreases) on inserts (deletes). In some cases, the space used does not change on inserts or deletes depending on the amount and pattern of data involved in the operation and any fragmentation. For example, deleting one row from every data page does not necessarily decrease the space used.

• Data space allocated

  • The amount of formatted file space made available for storing database data. The amount of space allocated grows automatically, but never decreases after deletes. This behavior ensures that future inserts are faster since space does not need to be reformatted.

• Data space allocated but unused

  • The difference between the amount of data space allocated and data space used. This quantity represents the maximum amount of free space that can be reclaimed by shrinking database data files.

• Data max size

  • The maximum amount of space that can be used for storing database data. The amount of data space allocated cannot grow beyond the data max size.'...

https://blogs.msdn.microsoft.com/azuresqldbsupport/2019/02/08/how-to-get-azure-sql-database-size/

'Accelerated Database Recovery (ADR) is a new SQL database engine feature that greatly improves database availability, especially in the presence of long running transactions, by redesigning the SQL database engine recovery process. ADR is currently available for single databases and pooled databases in Azure SQL Database, and databases in Azure SQL Data Warehouse. The primary benefits of ADR are:

• Fast and consistent database recovery

  With ADR, long running transactions do not impact the overall recovery time, enabling fast and consistent database recovery irrespective of the number of active transactions in the system or their sizes.

• Instantaneous transaction rollback

  With ADR, transaction rollback is instantaneous, irrespective of the time that the transaction has been active or the number of updates that has performed.

• Aggressive log truncation

  With ADR, the transaction log is aggressively truncated, even in the presence of active long running transactions, which prevents it from growing out of control.'...

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-accelerated-database-recovery