data-konzept

'A few months ago I suggested that the following settings should be the default for most SQL Server instances:

• Set cost threshold for parallelism to 50

• Disable lightweight pooling if it is enabled

• Disable priority boost if it is enabled

• Set optimize for ad hoc workloads to enabled

• Set max server memory (MB) to a custom value consistent with Jonathan Kehayias’s algorithm

• Set backup compression default to enabled

• Set the power saving settings on Windows to high performance if possible

• Provide an option to flush the plan cache as needed

Over the next few posts I will dive into the why. This week we look at setting max server memory correctly.'...

https://bornsql.ca/blog/proposed-sql-server-defaults-max-server-memory/

'The pros and cons of parallelism have always been with us in SQL Server and I blogged about this a couple of years ago. This is an updated version of that post to include details of the new wait stat related to parallelism that was added in 2017 (CXCONSUMER), as well as to discuss the options available for cloud based SQL Server solutions.

There’s no doubt that parallelism in SQL is a great thing. It enables large queries to share the load across multiple processors and get the job done quicker.

However it’s important to understand that it has an overhead. There is extra effort involved in managing the separate streams of work and synchronising them back together to – for instance – present the results.'...

https://matthewmcgiffen.com/2019/04/03/maxdop-parallelism-and-the-cloud/

'Wanna make your SQL Server applications go faster? Here are 9 simple tips that will give you a better chance for success. They’re not carved in stone, but they’re a good place to start.

Query Design Tips

1. Code for readability first. The easier your query is for humans to understand, the more likely SQL Server will be able to understand what you’re trying to do, and the better execution plan you’re likely to get.

2. Avoid scalar user-defined functions. Yes, functions are great for reusable app code, but SQL Server scalar functions are notoriously slow. They run row-by-row against your result set (possibly much more than that, depending on the query), and queries that call scalar functions are single-threaded, regardless of how much work they’re doing. Your functions might go faster in SQL Server 2019, but until then, don’t gamble.

3. Avoid table variables (@tablename). They sound like they’re going to be faster than temp tables, but they get bad row estimates, which drives the rest of your query to get a bad execution plan instead. If you need to store data somewhere temporarily, embrace temp tables (#mytable.) They’re the better of those two choices.

4. Never take user input to build a query. Crafty users will bypass your security using a technique called SQL injection. I know, you think you’ve built in a lot of security, but your code has to be perfectly bug-free every time. The hacker only has to be right once.

When you’re ready to learn more about query design, pick up the book T-SQL Fundamentals by Itzik Ben-Gan. The title sounds deceivingly easy, but this book will take you through several years’ worth of learning, and is great for veterans as well as beginners.'...

https://www.brentozar.com/archive/2019/05/9-tips-for-faster-sql-server-applications/

'Mit der anstehenden Umsetzung des Datenschutz-Grundverordnung (DSGVO) (European General Data Protection Regulation/GDPR) im Mai 2018 schreibt es im Grunde das Gesetz vor, ein Sicherheitskonzept zu haben. Microsoft SQL Server, genau wie andere Datenbanksysteme, enthält die wichtigsten Werte, die es zu schützen gilt: die Daten selbst. Deshalb ist es Zeit für einen Artikel aus einer strategischeren Perspektive in Bezug darauf, wie ich SQL Server und seine Daten schütze.Beim Design für Sicherheit gibt es mehrere bekannte Sicherheitsprinzipien in der IT-Technologie. Die gängigsten, und das sind die, die ich beachte, wenn ich für Sicherheit designe, sind wahrscheinlich die folgenden:

'In this article, we will explore various SQL Convert Date formats to use in writing SQL queries.

We need to work with date type data in SQL. It can be a complicated thing to deal with, at times, for SQL Server developers. Suppose you have a Product table with a column timestamp. It creates a timestamp for each customer order. You might face the following issues with it

• You fail to insert data in the Product table because the application tries to insert data in a different date format

• Suppose you have data in a table in format YYYY-MM-DD hh:mm: ss. You have a daily Sales report, and in that, you want data group by date. You want to have data in the report in format YYYY-MM-DD

We do face many such scenarios when we do not have date format as per our requirement. We cannot change table properties to satisfy each requirement. In this case, we need to use the built-in-functions in SQL Server to give the required date format.'...

https://www.sqlshack.com/sql-convert-date-functions-and-formats/

'Earlier I blogged about SQL SERVER – Getting Started with Azure Data Studio. After reading the blog post, I receive an email from a reader asking about the primary difference between Azure Data Studio (ADS) and SQL Server Management Studio (SSMS). Well, before we start the conversation let us see the following image.

'...

https://blog.sqlauthority.com/2019/04/15/sql-server-difference-between-azure-data-studio-and-sql-server-management-studio/

'In my first post about my tuning series I wrote about the general methodology how tuning works: SSIS Performance Tuning – Methodology and general approach

Now I’d like to talk about what we need to collect, what the tools are and how they work. Before I start I would like to say a special thank you to my colleague Christian Jurjut, who invested a lot of time in analyzing this area and put together this fantastic guideline.

In order to achieve effective and goal-oriented performance tuning it is mandatory to know your system’s bottlenecks. In most cases the root causes for those bottlenecks lie deep in the system’s architecture and are not visible at first sight. So the identification can be very time consuming and, in worst case, goes round in circles and comes to no result. Fortunately there are a few tools which help us monitoring different parts of the system. The most important and helpful will be explained in the following.'...

https://nexxtjump.com/2012/06/12/ssis-performance-tuningmonitoring-data-collection/

'While troubleshooting SSIS package performance, having the ability to view the performance monitor counters can be handy for determining where bottlenecks may be occurring. Usually, however, this is a very reactive request. The package is slow, so perfmon counters are enabled. The package is then run and *hopefully* the issue reoccurs. A lesser known feature of the SSIS catalog from 2012 and up is the dm_execution_performance_counters function, which tracks these perfmon counters for you during package execution. This provides the ability to begin proactive monitoring of SSIS performance monitor counters, checking important perfmon metrics such as Buffers Spooled prior to being notified by management or users. It also provides an alternate way to troubleshoot and understand SSIS bottlenecks without needing to remote to the server and configure a separate perfmon trace. This blog post serves to walk through the dm_execution_performance_counters function, what it tracks, and how it can be utilized effectively within an environment.'...

https://blogs.msdn.microsoft.com/sql_pfe_blog/2017/03/15/ssis-capturing-perfmon-counters-during-package-execution/

'While troubleshooting SSIS package performance, having the ability to view the performance monitor counters can be handy for determining where bottlenecks may be occurring. Usually, however, this is a very reactive request. The package is slow, so perfmon counters are enabled. The package is then run and *hopefully* the issue reoccurs. A lesser known feature of the SSIS catalog from 2012 and up is the dm_execution_performance_counters function, which tracks these perfmon counters for you during package execution. This provides the ability to begin proactive monitoring of SSIS performance monitor counters, checking important perfmon metrics such as Buffers Spooled prior to being notified by management or users. It also provides an alternate way to troubleshoot and understand SSIS bottlenecks without needing to remote to the server and configure a separate perfmon trace. This blog post serves to walk through the dm_execution_performance_counters function, what it tracks, and how it can be utilized effectively within an environment.'...

https://blogs.msdn.microsoft.com/sql_pfe_blog/2017/03/15/ssis-capturing-perfmon-counters-during-package-execution/

'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/