madeiradata / madeiratoolbox Goto Github PK
View Code? Open in Web Editor NEWOpen-source Repository of Useful Scripts and Solutions for Microsoft Data Platform Administrators
Home Page: https://www.madeiradata.com
License: MIT License
Open-source Repository of Useful Scripts and Solutions for Microsoft Data Platform Administrators
Home Page: https://www.madeiradata.com
License: MIT License
Relevant script: PUBLIC role with excessive permissions.sql
Problem: The REVOKE simulation feature is too dangerous to run on production systems.
Suggested solution: Optionally utilize the DBCC CLONEDATABASE
command to create a schema-only clone of the database(s) in question, set them to READ_WRITE
, and perform the REVOKE simulation in them to discover affected users.
Hi,
Is there any plan (or other script) that can check best practices for Azure SQL?
I've tried using this but it failed as AzureSql doesn't have msdb access
Hi
I try some run on different server and found that the result isn't correct.
I got:
@@ServerName: MSSQL01
@EffectiveMaxDOP: 2 (YES TRUE)
@NumaNodeCount: 1 (NO I GOT 2 NUMA... see the image)
but from SSMS I got a single one?
Which is the correct?
I see you count distinct on this...
And also from server log:
MaxDOP is already within recommended range: 8 (NO IT's 2)
I'm little confused,
Thanks Alen
We have a "condensed check" script of best practices that we're usually using for the SQL Server Review:
However, it's incomplete, and missing several checks, most of which exist in other scripts:
Feel free to also add more checks and list them here.
Prior to SQL Server 2019, this procedure did not apply to columnstore indexes, and therefore did not accept the data compression parameters COLUMNSTORE and COLUMNSTORE_ARCHIVE. Starting with SQL Server 2019, columnstore indexes can be used both as a source object for estimation, and as a requested compression type.
IMPORTANT
When Memory-Optimized TempDB Metadata is enabled in sql-server-2019, creation of columnstore indexes on temporary tables is not supported. Because of this limitation, sp_estimate_data_compression_savings is not supported with the COLUMNSTORE and COLUMNSTORE_ARCHIVE data compression parameters when Memory-Optimized TempDB Metadata is enabled.
Starting with sql-server-2019, sp_estimate_compression_savings
supports estimating both columnstore and columnstore archive compression. Unlike page and row compression, applying columnstore compression to an object requires creating a new columnstore index. For this reason, when using the COLUMNSTORE and COLUMNSTORE_ARCHIVE options of this procedure, the type of the source object provided to the procedure determines the type of columnstore index used for the compressed size estimate. The following table illustrates the reference objects used to estimate compression savings for each source object type when the @data_compression parameter is set to either COLUMNSTORE or COLUMNSTORE_ARCHIVE.
Source Object | Reference Object |
---|---|
Heap | Clustered columnstore index |
Clustered index | Clustered columnstore index |
Nonclustered index | Nonclustered columnstore index (including the key columns and any included columns of the provided nonclustered index, as well as the partition column of the table, if any) |
Nonclustered columnstore index | Nonclustered columnstore index (including the same columns as the provided nonclustered columnstore index) |
Clustered columnstore index | Clustered columnstore index |
NOTE
When estimating columnstore compression from a rowstore source object (clustered index, nonclustered index or heap), if there are any columns in the source object that have a data type that is not supported in a columnstore index, sp_estimate_compression_savings will fail with an error.
Similarly, when the @data_compression
parameter is set to NONE
, ROW
, or PAGE
and the source object is a columnstore index, the following table outlines the reference objects used.
Source Object | Reference Object |
---|---|
Clustered columnstore index | Heap |
Nonclustered columnstore index | Nonclustered index (including the columns contained in the nonclustered columnstore index as key columns, and the partition column of the table, if any, as an included column) |
NOTE
When estimating rowstore compression (NONE, ROW or PAGE) from a columnstore source object, be sure that the source index does not contain more than 32 columns as this is the limit supported in a rowstore (nonclustered) index.
Resources:
Great script but it recommends removing statistics that are not completely overlapping. If the "overlapped" statistic is a filtered index then the script will recommend removing the auto generated statistic but the statistic will then be auto-recreated again.
Adding " AND S.has_filter = 0" to the last where clause fixes this in a "sledgehammer" way.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.