data-konzept

'Dynamic management views (DMVs) and dynamic management functions (DMFs) are system views and system functions that return metadata of the system state. On querying the related system objects, database administrators can understand the internals of SQL Server. It allows us to monitor the performance of the SQL Server instance, and diagnose issues with it.

SQL Server 2017 ships with a number of new and enhanced dynamic management views and dynamic management functions that will help DBAs monitor the health and performance of SQL Server instances. A few existing DMV’s such as sys.dm_os_sys_info and sys.dm_db_file_space_usage have been enhanced. Some have also been newly built and available only for SQL Server 2017.

The new or enhanced DMV’s/DMF’s fall into the three categories:

• Database related

  • sys.dm_db_log_stats

  • sys.dm_db_log_info

  • sys.dm_db_stats_histogram

  • sys.dm_db_file_space_usage

  • sys.sys.dm_db_tuning_recommendations

• Transaction related

  • sys.dm_tran_version_store_space_usage

• SQL Server Operating System related

  • sys.dm_os_host_info

  • sys.dm_os_sys_info

Further Reading

SQL Server 2017 new(or Enhanced) DMVs and DMFs'...

http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/2018/01/17/sql-server-2017-dmvs-and-dmfs-for-dbas/

'Database Console Command CHECKDB (DBCC CHECKDB)is used to check the integrity (physical & logical) of objects in a SQL Server database.The command is supported in databases that contain memory-optimized tables but the validation is only supported in disk-based tables. The DBCC repair option is unavailable in memory-optimized tables and therefore, leads to the need of regular database backup.In case an issue arises in a memory-optimized table, the data can be restored from the last backup done.

The various operations that are performed by CHECKDB are:

• DBCC CHECKALLOC execution on SQL database.

• DBCC CHECKTABLE execution on each table and view of SQL database.

• DBCC CHECKCATALOG execution on SQL database.

• Validation of content in the indexed view of the database.

• Validation of link-level consistency amidst the file directories and the table metadata.

• Validation of service broker data.

Since DBCC CHECKDB runs all the other commands it will not be necessary to run CHECKALLOC, CHECKTABLE and CHECKCATALOG commands separately.

DBCC CHECKDB Syntax

'...

http://www.sqlservercentral.com/blogs/sql-server-overview/2018/01/17/how-to-run-dbcc-checkdb-to-check-sql-database-integrity/

'We are excited to announce the January release of SQL Operations Studio is now available.

Download SQL Operations Studio and review the release notes to get started. SQL Operations Studio is a data management tool that enables you to work with SQL Server, Azure SQL DB, and SQL DW from Windows, macOS, and Linux. To learn more, visit our Github.

SQL Operations Studio was announced for public preview on November 15, 2017 at Connect(), and this January release is the second major update since the announcement. If you missed the December release announcement, you can learn more on the SQL Server blog.

The January release includes several major repo updates and feature releases, including:

• Enable the HotExit feature to automatically reopen unsaved files.

• Add the ability to access saved connections from Connection Dialog.

• Set the SQL editor tab color to match the Server Group color.

• Fix the broken Run Current Query command.

• Fix the broken pinned Windows Start Menu icon.

For a complete list of updates, please refer to the release notes.'...

https://blogs.technet.microsoft.com/dataplatforminsider/2018/01/17/the-january-release-of-sql-operations-studio-is-now-available/

'If you are running SQL Server 2008 through SQL Server 2017, you should be thinking about what you should be doing to protect your systems from the Meltdown and Spectre vulnerabilities. Microsoft has a number of KB articles that address this issue from several different perspectives. This is a good starting list:

SQL Server Guidance to protect against speculative execution side-channel vulnerabilities (SQL Server)

Windows Server guidance to protect against speculative execution side-channel vulnerabilities (Windows Server)

Windows Client Guidance for IT Pros to protect against speculative execution side-channel vulnerabilities (Windows Client)

The basic guidance is that depending on the environment where you are running SQL Server (on-premises or not, virtualized or not, in a cloud IaaS VM or not) and whether you are using any open extensibility interfaces or not (things like some types of CLR assemblies, some types of linked servers, etc.), you are going to want to strongly consider patching several layers of your system. These may include:

• Operating System patches

• Registry changes

• SQL Server patches

• BIOS/UEFI updates

• Possible changes in how/whether you use any open extensibility interfaces in SQL Server'...

https://www.sqlskills.com/blogs/glenn/checking-your-sql-server-instance-for-spectremeltdown-patches/?utm_source=DBW&utm_medium=pubemail

'SQL Server is great at a lot of things. I spend a lot of time blogging about all the cool things that SQL Server does. But (you knew there was a “but”, didn’t you) SQL Server isn’t great at everything. Lets write about something that SQL Server is bad at.

SQL Server sucks at parsing strings

That’s not hyperbole. If you’ve tried to write your own function to parse CSVs into a table, you know it’s painful. Judging by the number of comments on this other article about splitting CSVs, this is clearly a topic that people spend a lot of time on. Thankfully, SQL Server 2016 finally introduced a SPLIT_STRING() function that performs well and handles most use cases….but we’re not here to split CSVs.

SQL Server sucks at parsing strings

Yea, I really can’t say it enough. The string manipulation & analysis functions that SQL Server does provide tend to be clunky. I can never remember how they work. Why does CHARINDEX() take three parameters, but PATINDEX() only takes two parameters? Why do those functions only work left-to-right, but not right-to-left? Oh, there’s a REVERSE() function I can apply to the string, but if I reverse the string to search right-to-left, I have to reverse it back on output.

SQL Server sucks at parsing strings

I feel like any time I need to do anything remotely involved, I end up with nested functions. Sometimes a lot of nested functions. Sometimes you have to repeat the same bits of function multiple times. It can make your code hard to read, especially if you don’t already know what it’s supposed to be doing.

Why are we here again?

Oh, right, parsing file & directory names. As a DBA, there seems to be a constant trickle of automation where I need to parse a file name out of a full path. PowerShell does a much better job at this than T-SQL. Go ahead and write a comment below that I should use PowerShell (or Python, or .NET, or some other language) to parse file names. No, really… go ahead, I’ll wait…'...

https://am2.co/2018/01/parsing-file-names-paths-using-t-sql/

'Earlier I wrote a blog post about how to remove leading and trailing spaces with TRIM function. SQL SERVER – 2017 – How to Remove Leading and Trailing Spaces with TRIM Function? After writing this blog post lots of people asked me its impact on the performance of SQL Server.

Previously I had written similar blog posts, you can read them before reading this blog post further:

• SQL SERVER – TRIM Function to Remove Leading and Trailing Spaces of String – SQL in Sixty Seconds #040 – Video

• SQL SERVER – 2008 – Enhanced TRIM() Function – Remove Trailing Spaces, Leading Spaces, White Space, Tabs, Carriage Returns, Line Feeds

• SQL SERVER – 2008 – TRIM() Function – User-Defined Function

• SQL SERVER – TRIM() Function – UDF TRIM()

Previously we have seen that whenever we use any function on the column used in WHERE condition, the performance is degraded. Most of the time when we use a function on the WHERE clause SQL Server have to apply that function to the entire column before the where a condition is evaluated. This is the reason, we usually do not see seek on the column. When SQL Server has to apply a function on an entire column of SQL Server, it has to scan the column which is usually (most of the time, but not always) expensive than seeking operation.'...

https://blog.sqlauthority.com/2018/01/17/sql-server-performance-observation-trim-function/

'

'What are Orphaned Users

“Orphaned Users” is a common issue in SQL Server where a Database User is no longer associated with its relevant Server Login.

This often happens when the Server Login is deleted (even if  it’s recreated later), or when the database is moved or restored to a different SQL Server.
You can find some more info on it in this article from Microsoft.

The symptoms that you can expect from such a scenario is inability to use such database users.
Specifically, using the login to connect to the database that it’s supposed to be mapped to. It just doesn’t work.
You’ll receive the following error:

Login failed for user ‘MyUser’. (Microsoft SQL Server, Error: 18456)

The Login could exist on the server level.
The User also exists within the relevant database.
But it doesn’t work because the SID that the database user is mapped to, is different from the SID of the actual Server Login.

This is the real problem, because Users are mapped to their Logins by their SID, not by their names.
And every time you create a new Login, it receives a new, random SID.'...

http://www.sqlservercentral.com/blogs/eitan-blumin/2018/01/03/how-to-fix-orphaned-users-easily/?utm_source=SSC&utm_medium=pubemail

'Introduction

I've been searching for a more or less comprehensible and fast way to import and export binary files using the SQL Server 2005. I've figured out my own way, which may be not the best or fastest. There may be a bunch of ways to improve the process, but who's perfect after all, it works well for me, and suits my purposes.

Preparations

We begin with the file which contains the absolute path for the binary files. Assume the extension of the binaries is .dat and let the play begin. I use the GNU find utility to get the names of the files.'...

http://www.sqlservercentral.com/articles/BCP/63560/?utm_source=SSC&utm_medium=pubemail

'Introduction: As a Microsoft Premier Field Engineer, I get to work with amazing colleagues who create incredible customer solutions. Patrick Keisler ( blog | |  ) is a long time SQL Server professional, who also works as a PFE supporting customers throughout Europe. He recently created a very handy "SQL Server Morning Health Check" solution on short notice for one of his customers. The solution is very helpful and can be added easily to your morning routine as a DBA to monitor your SQL Server environment. Patrick, thanks for sharing!

SQL Server Morning Health Checks (Created by Patrick Keisler)

A few weeks ago, a customer asked me to help develop a way to improve their morning routine of checking the health of each SQL Server. This daily task would typically take about an hour for the DBA to complete. The solution I provided him, reduced that task to under one minute.

The DBA needed me to answer these questions for each SQL Server:

1.       What is the uptime of each SQL Server?

2.       What is the status of each database?

3.       What is the status of each Availability Group?

4.       What is the backup status of each database?

5.       What is the available disk space?

6.       Are there any SQL Agent failed jobs in the last 24 hours?

7.       What errors appeared in the SQL errorlog in the last 24 hours?

In addition, the customer asked to display the results using the typical stop light colors (red, yellow, and green) to represent the condition of each server.'...

https://blogs.msdn.microsoft.com/samlester/2017/12/29/sql-server-dba-morning-health-checks/