SQL: documentation and scripts

J’utilise souvent les mêmes scripts pour les certaines opérations SQL. Au lieu de partager les scripts sur ce blogue, j’ai décidé de faire un billet, que je vais mettre à jour au fil du temps, qui contient la liste de tous les articles et scripts SQL que je trouve utile. 

Index et Statistics

• Index
• How to identify and monitor unused indexes
• Finding and eliminating duplicate or overlapping indexes
• Azure SQL: How to maintain indexes and statistics
• Check index fragmentation of a specified database 
• How to find the size of all the indexes in a database
• List all indexes in SQL Server database (& index columns)
• Index compression
• Query all table data and index compression
• Azure SQL: Creating my own sp_estimate_data_compression_savings
• Estimate compression for all tables and indexes with both Row and Page

• Table
• Get size of all tables in a database
• Bad habits : Counting rows the hard way

Security

• How to view the roles and permissions granted to any database user
• Query that returns the members of the databases roles (sys.database_role_members)
• Script pour obtenir les permissions (mathdax)
• Determining effective permissions

Architecture

• Query Processing Architecture Guide
• Index Architecture and Design Guide
• Memory Management Architecture Guide
• Pages and Extents Architecture Guide
• SQL Server Transaction Locking and Row Versioning Guide
• SQL Server Transaction Log Architecture and Management Guide
• Thread and Task Architecture Guide

Performance

Slow in the Application, Fast in SSMS?

• Resource Governor
• Resource Governor

• SQL Profiler
• How to Identify Slow Running Queries with SQL Profiler

• Queue Store
• Monitoring performance by using the Query Store

• Execution Plans
• Execution Plan Basics
• Execution Plan attributes
• Searching the SQL Server query plan cache
• The case of NULL query plan

• Memory
• SQL Server, Buffer Manager Object
• Calculate Buffer cache hit ratio
• Great SQL Server Debates: Buffer Cache Hit Ratio

• Scheduler
  
• Thread and Task Architecture Guide
• Configure the max degree of parallelism Server Configuration Option
• Configure the max worker threads Server Configuration Option
• Task Scheduling and CPU Deep Explanation in SQL Server